Auditing to Secure Oracle Database XE

In this conclusion to a ten-part article series on securing Oracle Database XE, you will learn about statement auditing, privilege auditing, and more. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Statement Auditing

No matter what type of auditing you wish to do, you use the AUDIT command to turn on auditing and NOAUDIT to turn off auditing. For statement auditing, the format of the AUDIT command looks something like the following:

AUDIT sql_statement_clause BY {SESSION | ACCESS}
     WHENEVER [NOT] SUCCESSFUL;

The sql_statement_clause  contains a number of different pieces of information, such as the type of SQL statement you want to audit and who you are auditing. In addition, you want to either audit the action every time it happens ( BY ACCESS ) or only once
( BY SESSION ). The default is BY SESSION .

Sometimes you want to audit successful actions—statements that did not generate an error message. For these statements, you add WHENEVER SUCCESSFUL . Other times you only care if the commands using the audited statements fail, either due to privilege violations, running out of space in the tablespace, or syntax errors. For these you use WHENEVER NOT SUCCESSFUL .

For most categories of auditing methods, you can specify ALL instead of individual statement types or objects if you truly want all types of access to a table or any privileges by a certain user to be audited.

The types of statements you can audit, with the statements that are covered in each category, are listed in Table 31-13. If ALL is specified, any statement in this list is audited. However, the types of statements in Table 31-14 do not fall into the ALL category when enabling auditing; they must be explicitly specified in any AUDIT commands.

Table 31-13. Auditable Statements Included in the ALL Category  

Statement Option

SQL Operations

CLUSTER

CREATE, ALTER, DROP, or TRUNCATEa cluster

CONTEXT

CREATEor DROPa CONTEXT

DATABASE LINK

CREATEor DROPa database link

DIMENSION

CREATE, ALTER, or DROPa dimension

DIRECTORY

CREATEor DROPa dimension

INDEX

CREATE, ALTER, or DROPan index

MATERIALIZED VIEW

CREATE, ALTER, or DROPa materialized view

NOT EXISTS

Failure of SQL statement due to nonexistent referenced objects

PROCEDURE

CREATEor DROP FUNCTION, LIBRARY, PACKAGE, PACKAGE BODY, or PROCEDURE

PROFILE

CREATE, ALTER, or DROPa profile

PUBLIC DATABASE LINK

CREATEor DROPa public database link

PUBLIC SYNONYM

CREATEor DROPa public synonym

 

 

Table 31-13. Auditable Statements Included in the ALL Category (Continued)

Statement Option SQL Operations
ROLE CREATE , ALTER , DROP , or SET a role
ROLLBACK SEGMENT CREATE , ALTER , or DROP a rollback segment
SEQUENCE CREATE or DROP a sequence
SESSION Logons and logoffs
SYNONYM CREATE or DROP synonyms
SYSTEM AUDIT AUDIT or NOAUDIT of system privileges
SYSTEM GRANT GRANT or REVOKE system privileges and roles
TABLE CREATE , DROP , or TRUNCATE a table
TABLESPACE CREATE , ALTER , or DROP a tablespace
TRIGGER

CREATE , ALTER (enable/disable), DROP triggers; ALTER TABLE with eitENABLE ALL TRIGGERS or DISABLE ALL TRIGGERS her

TYPE CREATE , ALTER , or DROP types and type bodies
USER CREATE , ALTER , or DROP a user
VIEW CREATE or DROP a view

 

 

Table 31-14. Explicitly Specified Statement Types

 

 

Statement Option SQL Operations
ALTER SEQUENCE Any ALTER SEQUENCE command
ALTER TABLE Any ALTER TABLE command
COMMENT TABLE

Add a comment to a table, view, materialized view, or any of their columns

DELETE TABLE Delete rows from a table or view
EXECUTE PROCEDURE

Execute a procedure, function, or any variables or cursors within a package

GRANT DIRECTORY GRANT or REVOKE a privilege on a DIRECTORY object
GRANT PROCEDURE GRANT or REVOKE a privilege on a procedure, function, or package
GRANT SEQUENCE GRANT or REVOKE a privilege on a sequence
GRANT TABLE GRANT or REVOKE a privilege on a table, view, or materialized view
GRANT TYPE GRANT or REVOKE a privilege on a TYPE
INSERT TABLE INSERT INTO a table or view
LOCK TABLE LOCK TABLE command on a table or view
SELECT SEQUENCE Any command referencing the sequence’s CURRVAL or NEXTVAL
SELECT TABLE SELECT FROM a table, view, or materialized view
UPDATE TABLE Execute UPDATE on a table or view

 

 

Some examples will help make all these options a lot clearer. In our sample database, the user KELLYC has all privileges on the tables in the HR schema and other schemas. KELLYC is allowed to create indexes on some of these tables, but we want to know when the indexes are created in case we have some performance issues related to execution plans changing. We can audit index creation by KELLYC with the following command:

SQL> audit index by kellyc whenever successful;
Audit succeeded.

Later that day, KELLYC creates an index on the HR.JOBS table:

SQL> create index job_title_idx on hr.jobs(job_title);
Index created.

Checking the audit trail in the data dictionary view DBA_AUDIT_TRAIL , we see that KELLYC did indeed create an index at 9:21 p.m. on September 17:

SQL> select username, to_char(timestamp,’MM/DD/YY HH24:MI’) Timestamp,
 
2      obj_name, action_name, sql_text from dba_audit_trail
 
3  where username = ‘KELLYC';

 

 

USERNAME

TIMESTAMP OBJ_NAME

ACTION_NAME

SQL_TEXT

 

 

 

 

KELLYC

09/17/06 21:21 JOB_TITLE_IDX

CREATE INDEX

create index j ob_title_idx on

 

 

 

hr.jobs(job_ti tle)

 

 

1 row selected.

To turn off auditing for KELLYC for indexing operations, use the NOAUDIT command, as follows:

SQL> noaudit index by kellyc;
Noaudit succeeded.

You also may wish to routinely audit both successful and unsuccessful logins. This requires two AUDIT commands:

SQL> audit session whenever successful ; Audit succeeded.
SQL> audit session whenever not successful; Audit succeeded.

Statement auditing also includes startup and shutdown operations. Although you can audit the command SHUTDOWN IMMEDIATE in the SYS.AUD$ table, it is not possible to audit the STARTUP command in SYS.AUD$ because the database has to be started before rows can be added to this table. For these cases, you can look in the directory $ORACLE_HOME/rdbms/audit to see a record of a startup operation performed by a system administrator.

{mospagebreak title=Privilege Auditing}

Auditing system privileges using the AUDIT command has the same basic syntax as statement auditing, except that system privileges are specified in the sql_statement_clause  instead of statements.

For example, you may wish to grant the ALTER TABLESPACE privilege to all your DBAs but you want to generate an audit record when this happens. The command to enable auditing on this privilege looks similar to statement auditing:

SQL> audit alter tablespace by access whenever successful;
Audit succeeded.

Every time the ALTER TABLESPACE privilege is successfully used, a row is added to SYS.AUD$ .

Special auditing is available to enable you to track system administrators’ use of the SYSDBA and SYSOPER privileges. To enable this extra level of auditing, set the initialization parameter AUDIT_SYS_OPERATIONS to TRUE . The audit records are sent to the same location as the operating system audit records; therefore, this location is operating-system-dependent. All SQL statements executed while using one of these privileges, as well as any SQL statements executed as the user SYS , are sent to this operating system audit location.

Schema Object Auditing

Auditing access to various schema objects using the AUDIT command looks similar to statement and privilege auditing:

AUDIT command looks similar to statement and privilege auditing: Auditing access to various schema objects using the AUDIT command looks similar to statement and privilege auditing:

AUDIT schema_object_clause BY {SESSION | ACCESS}
     WHENEVER [NOT] SUCCESSFUL;

The schema_object_clause  specifies a type of object access and the object being accessed. You can audit 13 different types of operations on specific objects; they are listed in Table 31-15.

Table 31-15. Object Auditing Options  

Object Option

Description

ALTER

Alters a table, sequence, or materialized view

AUDIT

Audits commands on any object

COMMENT

Adds comments to tables, views, or materialized views

DELETE

Deletes rows from a table, view, or materialized view

FLASHBACK

Performs flashback operation on a table or view

GRANT

Grants privileges on any type of object

INDEX

Creates an index on a table or materialized view

INSERT

Inserts rows into a table, view, or materialized view

LOCK

Locks a table, view, or materialized view

READ

Performs a read operation on the contents of a DIRECTORYobject

RENAME

Renames a table, view, or procedure

SELECT

Selects rows from a table, view, sequence, or materialized view

UPDATE

Updates a table, view, or materialized view

 

 

If you wish to audit all INSERT and UPDATE commands on the HR.JOBS table, regardless of who is doing the update, every time the action occurs, you can use the AUDIT command as follows:

SQL> audit insert, update on hr.jobs by access whenever successful;
Audit successful.

{mospagebreak title=Protecting the Audit Trail}

The audit trail itself needs to be protected, especially if nonsystem users must access the table SYS.AUD$. The built-in role DELETE_ANY_CATALOG is one of the ways that non-SYS users can have access to the audit trail (e.g., to archive and truncate the audit trail to ensure that it does not impact the space requirements for other objects in the SYS tablespace).

To set up auditing on the audit trail itself, connect as SYSDBA and run the following command:

SQL> audit all on sys.aud$ by access;
Audit succeeded.

Now all actions against the table SYS.AUD$ , including SELECT , INSERT , UPDATE , and DELETE , will be recorded in SYS.AUD$ itself. But, you may ask, what if someone deletes the audit records identifying access to the table SYS.AUD$ ? The rows in the table are deleted, but then another row is inserted, recording the deletion of the rows. Therefore, there will always be some evidence of activity, intentional or accidental, against the SYS.AUD$ table. In addition, if AUDIT_SYS_OPERATIONS is set to TRUE , any sessions using AS SYSDBA or AS SYSOPER , or connecting as SYS itself will be logged into the operating system audit location, which presumably even the Oracle DBAs would not have access to. As a result, you have many safeguards in place to ensure that you record all privileged activity in the database, along with any attempts to hide this activity.

Summary

As a DBA, you want to make sure that your application environment is secure. This chapter provided you with the tools to enhance and refine the security options available in Oracle Database XE. While you can protect your enterprise data using Oracle’s built-in security, you are free to add another layer of protection in your Web-based PHP applications as well. Any robust security policy implements more than one layer of security to ensure that users are who they say they are (authentication), and that they are allowed to access various resources in your environment (authorization).

In the next chapter, we tie together your PHP applications with Oracle and show you how easy it is to connect to Oracle Database XE, query and modify database tables, retrieve database metadata, and format your database’s data to look good in a PHP application.  

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye