12 Configuring and Administering Auditing

12.2.1 Keeping Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as far as possible. Doing so minimizes the performance impact on the execution of audited statements and the size of the audit trail, making it easier to analyze and understand.

Use the following general guidelines when devising an auditing strategy:

• Evaluate the purpose for auditing.

  After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

  For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

• Audit knowledgeably.

  Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

  For example, if you are auditing to gather information about database activity, then determine exactly what types of activities you want to track, audit only the activities of interest, and audit only for the amount of time necessary to gather the information that you desire. As another example, do not audit objects if you are only interested in logical I/O information for each session.

12.2.2 Auditing Normal Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:

• Audit only pertinent actions.

  To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.

• Archive audit records and purge the audit trail.

  After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.

• Privacy considerations

  Privacy regulations often lead to additional business privacy policies. Most privacy laws require businesses to monitor access to personally identifiable information (PII), and such monitoring is implemented by auditing. A business-level privacy policy should address all relevant aspects of data access and user accountability, including technical, legal, and company-policy concerns.

12.2.3 Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:

• First audit generally, and then specifically.

  When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to draw conclusions about the origin of the suspicious database activity.

• Protect the audit trail.

  When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.

  
  

  See Also: "Protecting the Standard Audit Trail"

  
  

12.2.4 Auditing Administrative Users

Sessions for users who connect as SYS can be fully audited, including all users connecting as SYSDBA or SYSOPER. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify whether such users are to be audited. For example, the following setting specifies that SYS is to be audited:

AUDIT_SYS_OPERATIONS = TRUE

The default value, FALSE, disables SYS auditing.

All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$ (also viewable as DBA_AUDIT_TRAIL).

• In Windows, for example, when AUDIT_TRAIL=OS, audit records are written as events to the Event Viewer log file. If either XML or XML,EXTENDED is specified, then audit records are written in the XML format.

  
  

  Notes: DB_UNIQUE_NAME is defined as the globally unique name for the database in Oracle Database Reference. Refer to this book for other details and naming rules. The adump directory is the first default location used if the AUDIT_FILE_DEST init.ora parameter is not set or does not point to a valid directory. If writing to that first default location fails, then the $ORACLE_HOME/rdbms/audit directory is used as the backup default destination. If that attempt fails, then the audited operation fails and a message is written to the alert log. When AUDIT_TRAIL=OS, audit filenames will continue to be of the following form: <short_form_of_process_name>_<processid>.aud For example, the short process name ora is used for dedicated server processes, and the names s001, s002, and so on are used for shared server processes. When AUDIT_TRAIL=XML, the same audit filenames have the extension xml instead of aud.

  
  

• If the AUDIT_FILE_DEST parameter is not specified, then the default location is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump in Solaris and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump in Windows.

• For other operating systems, refer to their audit trail documentation.

All SYS-issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT / AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='myname';

When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follows:

Thu Jan 24 12:58:00 2002
ACTION: 'CONNECT'
DATABASE USER: '/'
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'alter system flush shared_pool'
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'update salary set base=1000 where name='myname''
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: jeff
CLIENT TERMINAL: pts/2
STATUS: 0

Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs, who are regular database users with the DBA role, or by means of a DBA role (for example, mydba or jr_dba) that your organization customizes.

12.2.5 Using Triggers

You can often use triggers to record additional customized information that is not automatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could define a trigger on the EMP table to generate an audit record whenever the salary of an employee is increased by more than 10 percent. You can include selected information, such as the values of SALARY before and after it was changed:

CREATE TRIGGER audit_emp_salaries
AFTER INSERT OR DELETE OR UPDATE ON employee_salaries
for each row
begin
if (:new.salary> :old.salary * 1.10)
      then
      insert into emp_salary_audit values (
      :employee_no,
      :old.salary,
      :new.salary,
      user,
      sysdate);
      endif;
end;

Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.

However, while Oracle triggers can readily monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly and, in some cases, uncertain. Triggers do not enable businesses to capture the statement executed as well as the result set from a query. They also do not enable users to define their own alert action in addition to simply inserting an audit record into the audit trail.

For these capabilities, use fine-grained auditing, which provides an extensible auditing mechanism supporting definition of key conditions for granular audit as well as an event handler to actively alert administrators to misuse of data access rights. Refer to Fine-Grained Auditing.

12.2.6 Deciding Whether to Use the Database or Operating System Audit Trail

The data dictionary of every Oracle database has a table named SYS.AUD$, commonly referred to as the database audit trail. Depending on configuration choices, this table can reside in different schema, such as the traditional SYS schema in the SYSTEM tablespace. For example, when Oracle Label Security is active, the AUD$ table is moved to the SYSTEM schema, with SYS.AUD$ becoming a synonym to SYSTEM.AUD$. The database audit trail is viewable as DBA_AUDIT_TRAIL, and its entries arise from auditing database statements, privileges, or schema objects.

You can optionally choose to store the database audit information in an operating system file. If auditing facility of your operating system writes audit records to a file that Oracle can also write to, then you can direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the Application Event Log, viewable by the Event Viewer.

Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.

Using the database audit trail offers the following advantages:

• You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary, such as DBA_AUDIT_TRAIL.

• You can use Oracle tools (such as Oracle Reports) or third-party tools to generate audit reports.

Using the operating system audit trail offers the following advantages:

• Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Greater availability is another advantage to operating system storage for audit records, in that they remain available even if the database is temporarily inaccessible.

• If init.ora specifies AUDIT_TRAIL=XML, then audit records are written to the operating system as XML files. A new dynamic view, V$XML_AUDIT_TRAIL, makes such XML audit records available to DBAs through SQL query, providing enhanced usability. Querying this view causes all XML files (all files with a .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

• DBA_COMMON_AUDIT_TRAIL also includes the standard and fine grained audit trails as written to database tables, as well as the XML-format audit trail records. The DBA_COMMON_AUDIT_TRAIL view includes the contents of the V$XML_AUDIT_TRAIL dynamic view for standard and fine-grained audit records. DBA_COMMON_AUDIT_TRAIL also includes the standard and fine grained audit trails as written to database tables, as well as the XML-format standard, fine grained, SYS and mandatory audit trails.

• Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle and other applications. Examining system activity can become more efficient with all audit records in one place. Using XML audit records permits use of any standard XML editing tool to review or extract information from those records.

12.3.1 Database Audit Trail Contents

The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle Database data dictionary. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.

Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The partial list in the following section shows columns that always appear in the audit trail: if the data they represent is available, then that data populates the corresponding column. (For certain columns, this list has the column name as it displays in the audit record, shown here inside parentheses.) The operating system audit trail has only those columns marked Yes in the corresponding column.

Footnote 1: Asterisked (*) columns in Table 12-1 appear in the audit records only if your database initialization file, init.ora, specifies AUDIT_TRAIL=DB,EXTENDED or AUDIT_TRAIL=XML,EXTENDED. Also, for an array, the values recorded are only the last set of bind values.

Footnote 2: Process number is populated as ProcessId on Unix systems. In Windows systems, the label is ProcessId:ThreadId (or ProcessId if it is not running as a thread).

If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, then an audited action cannot complete. Instead, it causes an error message and is not done. In most cases, using an operating system log as the audit trail destination allows such an action to complete.

The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an UPDATE statement is audited. However, this specialized type of auditing can be performed using fine-grained auditing methods.

The DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

You can use the Flashback Query feature to show the old and new values of the updated rows, subject to any auditing policy presently in force. The current policies are enforced even if the flashback is to an old query that was originally subject to a different policy. Current business access rules always apply.

12.3.2 Audit Information Stored in an Operating System File

The operating system file that contains the audit trail can include any of the following data:

• Audit records generated by the operating system

• Database audit trail records

• Database actions that are always audited

• Audit records for administrative users (SYS)

Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:

12.4.1 Enabling and Disabling Standard Auditing

Any authorized database user can set statement, privilege, and object auditing options at any time. However, Oracle Database does not generate audit information for the standard database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.

This section discusses the initialization parameters that enable and disable standard auditing.

AUDIT_SYSLOG_LEVEL=local1.warning

12.4.2 Standard Auditing in a Multitier Environment

In a multitier environment, Oracle preserves the identity of the client through all tiers, which enables auditing of actions taken on behalf of the client. To do such auditing, you use the BY proxy clause in your AUDIT statement.

This clause allows to do the following:

• Audit SQL statements issued by the specified proxy on its own behalf

• Audit statements executed on behalf of a specified user or users

• Audit all statements executed on behalf of any user

The following example audits SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve.

AUDIT SELECT TABLE
    BY appserve ON BEHALF OF jackson; 

12.4.3 Enabling Standard Auditing Options

You specify one of the four standard auditing options using the AUDIT statement:

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege. To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

• BY SESSION/BY ACCESS

  BY SESSION causes Oracle Database to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

  
  

  Note: If AUDIT_TRAIL=OS or AUDIT_TRAIL=XML, then multiple records may still be written to the audit trail when BY SESSION is specified. Multiple records occur because while Oracle Database can write to the operating system file, the database cannot read it to detect that an audit entry already exists for the action.

  
  

• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

  WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

The implications of your choice of auditing options and the specification of AUDIT statement clauses are discussed in subsequent sections.

A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options. Existing sessions continue using the audit options in place at session creation.

AUDIT SESSION;

AUDIT SESSION
BY jeff, lori;

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
      BY ACCESS
      WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ON jeff.emp;

AUDIT SELECT, INSERT, DELETE
     ON jward.dept
     BY ACCESS
     WHENEVER SUCCESSFUL;

AUDIT SELECT
     ON DEFAULT
     WHENEVER NOT SUCCESSFUL;

12.4.4 Disabling Standard Audit Options

The NOAUDIT statement turns off the various audit options of Oracle Database 10g. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY user or BY proxy option to specify a list of users to limit the scope of the statement and privilege audit options.

You can use a NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, then the auditing option is disabled entirely, for both successful and unsuccessful cases.

The BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT statement. Audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.

NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
    EXECUTE PROCEDURE;

NOAUDIT ALL;

NOAUDIT ALL PRIVILEGES;

NOAUDIT DELETE
   ON emp;
NOAUDIT SELECT, INSERT, DELETE
   ON jward.dept;

NOAUDIT ALL
   ON emp;

NOAUDIT ALL
   ON DEFAULT;

NOAUDIT NETWORK;

12.4.5 Controlling the Growth and Size of the Standard Audit Trail

If the audit trail is full and no more audit records can be inserted, then audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:

• The number of audit options turned on

• The frequency of execution of audited statements

To control the growth of the audit trail, you can use the following methods:

• Enable and disable database auditing. If it is enabled, then audit records are generated and stored in the audit trail. If it is disabled, then audit records are not generated.

• Be very selective about the audit options that are turned on. If more selective auditing is performed, then useless or unnecessary audit information is not generated and stored in the audit trail.

• Tightly control the ability to perform object auditing. This can be done in two different ways:

  • A security administrator owns all objects and the AUDIT ANY system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.

  • All objects are contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not granted to the corresponding user) and the security administrator is the only user granted the AUDIT ANY system privilege.

  In both scenarios, object auditing is controlled entirely by the security administrator.

The maximum size of the database audit trail (SYS.AUD$ table) is determined by the default storage parameters of the SYSTEM tablespace, in which it is stored.

DELETE FROM SYS.AUD$;

DELETE FROM SYS.AUD$
     WHERE obj$name='EMP';

12.4.6 Protecting the Standard Audit Trail

When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.

Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.

12.4.7 Auditing the Standard Audit Trail

If an application needs to give SYS.AUD$ access to regular users (non-SYSDBA users), then such access needs to be audited.

To do so, you turn on the relevant auditing options for SYS.AUD$, which work a little differently because they are auditing actions on the audit trail(aud$) itself:

1. CONNECT sys/passw AS SYSDBA

2. Issue the following command:

   AUDIT SELECT, INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
   
   

Please note that this command will AUDIT actions performed by non-SYSDBA users only.

If a regular user has SELECT, UPDATE, INSERT, and DELETE privileges on SYS.AUD$ and executes a SELECT operation, then the audit trail will have a record of that operation. That is, SYS.AUD$ will have a row identifying the SELECT action on itself, as say row1.

If a user later tries to DELETE this row1 from SYS.AUD$, then the DELETE will succeed, because the user has the privilege to perform this action. However, this DELETE action on SYS.AUD$ is also recorded in the audit trail. Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions. A logfile for an illustrative test case appears at the end of this chapter, at The SYS.AUD$ Auditing Table: Example.

12.5.1 Audit Trail Views

The following views are created upon installation:

STMT_AUDIT_OPTION_MAP

AUDIT_ACTIONS

ALL_DEF_AUDIT_OPTS

DBA_STMT_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS

DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL

DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT

DBA_AUDIT_SESSION
USER_AUDIT_SESSION 

DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT

DBA_AUDIT_EXISTS

DBA_AUDIT_POLICIES

DBA_FGA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

12.5.2 Using Audit Trail Views to Investigate Suspicious Activities

This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.

You would like to audit the database for the following suspicious activities:

• Passwords, tablespace settings, and quotas for some database users are altered without authorization.

• A high number of deadlocks occur, most likely because of users acquiring exclusive table locks.

• Rows are arbitrarily deleted from the emp table in jeff's schema.

You suspect the users jward and swilliams of several of these detrimental actions.

To investigate, you issue the following statements (in the order specified):

AUDIT ALTER, INDEX, RENAME ON DEFAULT
    BY SESSION;
CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp;
AUDIT SESSION BY jward, swilliams;
AUDIT ALTER USER;
AUDIT LOCK TABLE
    BY ACCESS
    WHENEVER SUCCESSFUL;
AUDIT DELETE ON jeff.emp
    BY ACCESS
    WHENEVER SUCCESSFUL;

The following statements are subsequently issued by the user jward:

ALTER USER tsmith QUOTA 0 ON users;
DROP USER djones;

The following statements are subsequently issued by the user swilliams:

LOCK TABLE jeff.emp IN EXCLUSIVE MODE;
DELETE FROM jeff.emp WHERE mgr = 7698;
ALTER TABLE jeff.emp ALLOCATE EXTENT (SIZE 100K);
CREATE INDEX jeff.ename_index ON jeff.emp (ename);
CREATE PROCEDURE jeff.fire_employee (empid NUMBER) AS
  BEGIN
    DELETE FROM jeff.emp WHERE empno = empid;
  END;
/

EXECUTE jeff.fire_employee(7902);

The following sections display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:

• Listing Active Statement Audit Options

• Listing Active Privilege Audit Options

• Listing Active Object Audit Options for Specific Objects

• Listing Default Object Audit Options

• Listing Audit Records

• Listing Audit Records for the AUDIT SESSION Option

SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   SESSION              BY SESSION      BY SESSION
SWILLIAMS               SESSION              BY SESSION      BY SESSION
                        LOCK TABLE           BY ACCESS       NOT SET

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------   ----------
ALTER USER          BY SESSION           BY SESSION

SELECT * FROM DBA_OBJ_AUDIT_OPTS
    WHERE OWNER = 'JEFF' AND OBJECT_NAME LIKE 'EMP%';

OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
----- ----------- --------- --- --- --- --- --- --- --- --- ...
JEFF EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...
JEFF EMPLOYEE    VIEW      -/- -/- -/- A/- -/- S/S -/- -/- ...

SELECT * FROM ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /-  -/-

SELECT * FROM DBA_AUDIT_OBJECT;

SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
    LOGOFF_LWRITE, LOGOFF_DLOCK
    FROM DBA_AUDIT_SESSION;

USERNAME   LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
---------- --------- ---------- ---------- ---------- ----------
JWARD      02-AUG-91         53          2         24          0 
SWILLIAMS  02-AUG-91       3337        256        630          0 

12.5.3 Deleting the Audit Trail Views

If you disable auditing and no longer need the audit trail views, then delete them by connecting to the database as SYS and running the script file CATNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating-system-dependent.

12.5.4 The SYS.AUD$ Auditing Table: Example

The code in this section illustrates the auditing of changes made to SYS.AUD$.

SQL> @t
SQL> 
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> column username format a10
SQL> column owner  format a10
SQL> column obj_name format a6
SQL> column action_name format a17
SQL> SET ECHO ON
SQL> 
SQL> connect sys/newdbapassword as sysdba
Connected.
SQL> grant select, insert, update, delete on sys.aud$ to jeff;

Grant succeeded.

SQL> grant select on dba_audit_trail to jeff;

Grant succeeded.

SQL> audit select, update, delete on sys.aud$ by access;

Audit succeeded.

SQL> truncate table sys.aud$;

Table truncated.

SQL> 
SQL> connect jeff/wolf
Connected.
SQL> select count(*) from emp

  COUNT(*)
----------
         0

1 row selected.

SQL> 
SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
          8          1 JEFF      SELECT                     0 SYS        AUD$


1 row selected.

SQL> 
SQL> update sys.aud$ set userid = 0;

2 rows updated.

SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
          8          1 0          SELECT                     0 SYS        AUD$

          9          2 0          SELECT                     0 SYS        AUD$

         10          3 JEFF       UPDATE                     0 SYS        AUD$

3 rows selected.

SQL> 
SQL> delete from sys.aud$;

3 rows deleted.

SQL> select statementid,entryid,username,action_name,returncode,owner,
  2  obj_name,substr(priv_used,1,8) priv,  SES_ACTIONS
  3  from dba_audit_trail
  4  order by sessionid,entryid;

STATEMENTID    ENTRYID USERNAME   ACTION_NAME       RETURNCODE OWNER      OBJ_NA
----------- ---------- ---------- ----------------- ---------- ---------- ------
PRIV     SES_ACTIONS
-------- -------------------
         10          3 JEFF      UPDATE                     0 SYS        AUD$

         12          5 JEFF      DELETE                     0 SYS        AUD$

2 rows selected.

SQL> 
SQL> connect sys/newdbapassword as sysdba
Connected.
SQL> noaudit insert, select, update, delete on sys.aud$;

Noaudit succeeded.

SQL> 
SQL> spool off

12.6.1 Policies in Fine-Grained Auditing

Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.

For example, most companies logically want to limit access to the specifications or test results for a product under development, and prefer that salary information remain private. Auditors need enough detail to be able to determine the data that was accessed.

Knowing only that SELECT privilege was used by a specific user on a particular table is not specific enough to provide accountability. A central tax authority has similar privacy concerns, needing to track access to tax returns so that employees do not snoop. Similarly, government agencies that use informants need detailed tracking of access to the database containing their identities. Such agencies also need enough detail to determine the data that was accessed, and not just information that the SELECT privilege was used by JEFF on the TAXPAYERS or INFORMANTS table.

PROCEDURE fname ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...

12.6.2 An Added Benefit to Fine-Grained Auditing

In general, fine-grained auditing policies are based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle Database can run a user-defined audit event handler, if specified in the policy, using autonomous transactions to process the event.

Fine-grained auditing can be implemented in user applications using the DBMS_FGA package or by using database triggers.

The following example shows how you can audit statements (INSERT, UPDATE, DELETE, and SELECT) on table hr.emp to monitor any query that accesses the salary column of the employee records that belong to sales department:

DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary'
statement_types => 'insert,update,delete,select');

Then, any of the following SQL statements will cause the database to log an audit event record.

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;

SELECT salary FROM hr.emp WHERE dept = 'SALES';

DELETE from hr.emp where salary >1000000

With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

Consider the following commands:

/* create audit event handler */
CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS
BEGIN
UTIL_ALERT_PAGER(schema1, table1, policy1);      -- send an alert note to my pager
END;

/* add the policy */
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary',
handler_schema => 'sec',
handler_module => 'log_id',
enable               =>  TRUE);

After the first row of interest is fetched, the event is recorded, and the SEC.LOG_ID audit function is run. The audit event record generated is stored in DBA_FGA_AUDIT_TRAIL, which is fga_log$ in the SYS schema in the SYSTEM tablespace. This table has reserved columns (such as SQL_TEXT and SQL_BIND) for recording SQL text, policy name, and other information. The SQLBIND and SQLTEXT values are recorded in the LSQLTEXT and LSQLBIND columns of fga_log$ only if the policy specifies audit_trail = DBMS_FGA.DB + DBMS_FGA.EXTENDED. If the policy specifies AUDIT_TRAIL=DBMS_FGA.XML, then the audit records would be written to XML-formatted OS files.

12.7.1 ADD_POLICY Procedure

This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view object is 256.

DBMS_FGA.ADD_POLICY(
   object_schema   VARCHAR2, 
   object_name     VARCHAR2, 
   policy_name     VARCHAR2, 
   audit_condition VARCHAR2, 
   audit_column    VARCHAR2, 
   handler_schema  VARCHAR2, 
   handler_module  VARCHAR2, 
   enable          BOOLEAN, 
   statement_types VARCHAR2,
   audit_trail     BINARY_INTEGER IN DEFAULT,
   audit_column_opts BINARY_INTEGER IN DEFAULT);

DBMS_FGA.ADD_POLICY (object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS); 

12.7.2 DISABLE_POLICY Procedure

This procedure disables an audit policy.

DBMS_FGA.DISABLE_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 ); 

12.7.3 DROP_POLICY Procedure

This procedure drops an audit policy.

DBMS_FGA.DROP_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 );

12.7.4 ENABLE_POLICY Procedure

This procedure enables an audit policy.

DBMS_FGA.ENABLE_POLICY(
   object_schema  VARCHAR2,
   object_name    VARCHAR2,
   policy_name    VARCHAR2,
   enable         BOOLEAN);