Oracle Database Audit usage example

This section is translated from Oracle metalink doc: 167293.1. An example is provided to illustrate ORACLE audit usage.

ORACLE audits can be performed at the statement, object, and permission levels. Similarly, SYSDBA and SYSOPER user behavior can also be audited (from ORACLE 9i Release 2, 9.2.0.1, SYS users can audit by setting the AUDIT_SYS_OPERATIONS parameter ).

1. Object-level Audit

Objects that can be audited include tables, views, sequence generators, packages, and stored procedures. Due to the dependency of objects, multiple audit information may be generated for the same event. For example, if a function is associated with a view, a view is associated with a table.

Object-level audit can only target users of the entire database, but cannot audit a user. To view the Audit options of Object-level audit, You can query the ALL_DEF_AUDIT_OPTS view.

SQL> connect system/manager

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRAINDINS LOC REN SEL UPD REF EXE

---------------------------------------

-/--/--/--/--/--/--/--/--/--/--/--/--/-

The following example is an audit of SCOTT. EMP:

SQL> connect system/manager

SQL> audit select on SCOTT. emp by session;

Check whether the audit information has been recorded:

SQL> col owner format a7

SQL> col object_name format a7

SQL> select * from dba_obj_audit_opts

Where wner = 'Scott 'and OBJECT_NAME = 'emp ';

OWNEROBJECT _ OBJECT_TY ALT AUD COM DEL GRAINDINS LOC REN SEL UPD REF EXE

----------------------------------------------------------

SCOTTEMPTABLE-/-S -/--/--/-

The following statements can generate some audit information:

SQL> connect scott/tiger

SQL> select * from emp;

SQL> connect TEST/TEST

SQL> select * from scott. emp;

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> connect system/manager

SQL> select * from scott. emp;

Audit results

SQL> connect system/manager

SQL> col username format a8

SQL> col priv_used format 999

SQL>/

SQL> select username, priv_used, ses_actions from

Dba_audit_object

Where obj_name = 'emp' and wner = 'Scott ';

Result

USERNAMEPRIV_USEDSES_ACTIONS

-----------------------------------------------------------

SCOTT---------S ------

TEST

Systemselect any --------- S ------

2. Audit at the permission level

All system permissions can be audited. All system permissions can be queried from SYSTEM_PRIVILEGE_MAP. If you want to audit a permission that does not belong to this view, an error occurs:

SQL> audit drop snapshot by access;

Audit drop snapshot by access

*

ERROR at line 1:

ORA-00956: missing or invalid auditing option

SQL> connect system/manager

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

.....

-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

1 2 3 4 5 Next Page