Auditing in Oracle needs to know - part3

Next is privilege level auditing,

B. Auditing on privilege Level
All system privileges can be audited. The different privileges can be selected from system_privilege_map. Statement and privilege auditing are seperated in the oracle documentation and data dictionary views . However they use identical syntax and considering them as identical will simplify things greater. For example ,

SQL> enabe audit drop any table;

the reflect will populate both in DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS .
First note that the AUDIT syntax does not make a distinction between statement
and privilege options. Is “CREATE SESSION” a statement or a privilege option?
What about “DROP ANY TABLE?” According to the Oracle views, each appears
to be both, but only one audit trail record results from each audited action.

Example: auditable system privileges

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from system_privilege_map;
PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE

..... not entire result is shown ......

-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP

We are giving alter system privileges to test user and enabling audit on it,

SQL> grant alter system to test;

Grant succeeded.

SQL> audit alter system by access;

Audit succeeded.

SQL> select sid, serial# from v$session where username='TEST';

SID SERIAL#
---------- ----------
149 1568

SQL> conn test
Enter password:
Connected.
SQL> alter system kill session '149,1568' immediate;

System altered.

SQL> conn /as sysdba
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME                      ACTION_NAME
------------------------------ ----------------------------
TEST                               ALTER SYSTEM
TEST                                LOGOFF BY CLEANUP
TEST                                 LOGOFF

SQL>

C. Auditing on object Level

Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.

Object auditing options are set for all users of the database and cannot be set
for individual users.What options can be set? This can be seen from all_def_audit_opts.

Example object auditing options

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from all_def_audit_opts;

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

These correspond to the following object options respectively:

alter, audit, comment, delete, grant, index, insert, lock, rename, select, update, reference and execute .

All except reference and execute can be applied to tables, otherwise you will get the error ora-1982 "invalid auditing option for tables".

SQL> audit select on scott.emp by session;

Audit succeeded.

Check: Which objects are audited

SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

Generate some audit information:

SQL> connect scott/scott
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect hr/hr
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/system
SQL> select * from scott.emp;
=> all scott.emp's rows are shown

Results of auditing:

SQL> connect system/system
SQL> select username, priv_used, ses_actions from dba_audit_object where obj_name='EMP' and owner='SCOTT';
USERNAME    PRIV_USED                  SES_ACTIONS
----------       -------------------           -------------------
SCOTT                                            ---------S------
MAHI            SELECT ANY TABLE      ---------S------
HR                                                  ---------F------
SYSTEM         SELECT ANY TABLE      ---------S------