Some examples about auditing and output of auditing

Subject: Some examples about auditing and output of auditing
Doc ID: Note:167293.1 Type: BULLETIN
Last Revision Date: 04-JUN-2007 Status: PUBLISHED


"Checked for relevance on 27-Mar-2007"
Purpose:
~~~~~~~~
Purpose of the document is to show some examples of auditing output as most
articles do not show examples
How to set up auditing can be seen in for example:
Note 41800.1 Quick Reference to Auditing Information


Scope & Application:
~~~~~~~~~~~~~~~~~~~~
This article intends to demonstrate some examples of output of auditing.
The article is divided in

1. Auditing administrative connections
2. Auditing on object Level
3. Auditing on privilege Level
4. Auditing statements

Shown is for each section what can be audited, how to check what is audited and
some examples of the output of auditing.


Auditing:
~~~~~~~~~
Auditing can be set on statement level, object level and privilege level.
Also administrative connections are logged.

1. Auditing administrative connections
--------------------------------------
Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA.
There are some good examples of output of audited administrative connections
for Unix and for NT in:

Note 103964.1: RDBMS: How to Audit Connect Internal Using Oracle Server
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer

In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:

Note 174340.1 : Audit SYS user Operations


2. 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> connect system/manager
SQL> select * from all_def_audit_opts;

Result:

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".

Example of auditing scott.emp
-----------------------------

SQL> connect system/manager
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';

result:

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/tiger
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect t/tl
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;
=> all scott.emp's rows are shown


Results of auditing:

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 owner='SCOTT';

Result:


USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- ----------------
SYSTEM SELECT ANY TABLE ---------S---
T ---------F---
SCOTT ---------S---


3. Auditing on privilege Level
------------------------------

All system privileges can be audited.
The different privileges can be selected from system_privilege_map.
If you attempt to use a value that does not belong to the list, you get the
following error:

SQL> audit drop snapshot by access;
audit drop snapshot by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option

Example: auditable system privileges
------------------------------------
SQL> connect system/manager
SQL> select * from system_privilege_map;

Result (this result is version dependant and still going up):

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


Example of auditing CREATE TABLE by scott and system
----------------------------------------------------

SQL> connect system/manager
SQL> audit create table by scott, system;
=> Audit succeeded.

This means each create table is audited for system and scott once per session.
Should each create table be audited then the clause 'by access' must be added.

check: Which privileges are audited

SQL> col user_name format a7
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from sys.dba_priv_audit_opts;

Result:

USER_NA PRIVILEGE SUCCESS FAILURE
------- --------------- ---------- ----------
SCOTT CREATE TABLE BY ACCESS BY ACCESS
SYSTEM CREATE TABLE BY ACCESS BY ACCESS

Generate some audit information:

SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
SQL> connect t/tl
SQL> create table tsc (c1 number);
=> table created
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> create table scott.t1(c1 number)
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created

Results of auditing:

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;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
1 CREATE TABLE SCOTT
1 CREATE TABLE SYSTEM



4. Auditing statements
----------------------
Statement auditing is the selective auditing of related groups of statements for
a type of database structure or type of schema object for ddl (data definition
language) or dml (data manipulation language) statements.
For example when 'audit table' is audited create, alter and drop table
statements are audited.
When 'audit select table' is audited all select statements from table, view,
snapshot.. will be audited.

The difference with privilege auditing is that in the latter case a system
privilege is audited. For example the 'audit create table' will only audit
create table and not alter or drop table statements.
When similar statement and privilege options are set only one audit record is
generated.

Example: auditable statements
-----------------------------
The statements taht can be adited can be seen from stmt_audit_option_map.

SQL> connect system/manager
SQL> select * from stmt_audit_option_map;

Result (this result is version dependant and still going up):

OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
..... not entire result is shown ......
221 CONTEXT 0
234 ON COMMIT REFRESH 0
235 EXEMPT ACCESS POLICY 0
236 RESUMABLE 0
237 SELECT ANY DICTIONARY 0
238 DEBUG CONNECT SESSION 0
239 DEBUG CONNECT USER 0
240 DEBUG CONNECT ANY 0
241 DEBUG ANY PROCEDURE 0
242 DEBUG PROCEDURE 0

165 rows selected.

SQL> select * from stmt_audit_option_map
where name like '%TABLE%';

Result:

OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
8 TABLE 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
40 CREATE TABLE 0
41 CREATE ANY TABLE 0
42 ALTER ANY TABLE 0
43 BACKUP ANY TABLE 0
44 DROP ANY TABLE 0
45 LOCK ANY TABLE 0
46 COMMENT ANY TABLE 0
47 SELECT ANY TABLE 0
48 INSERT ANY TABLE 0
49 UPDATE ANY TABLE 0
50 DELETE ANY TABLE 0
54 ALTER TABLE 0
57 LOCK TABLE 0
58 COMMENT TABLE 0
65 SELECT TABLE 0
66 INSERT TABLE 0
67 UPDATE TABLE 0
68 DELETE TABLE 0
69 GRANT TABLE 0


Example of auditing TABLE by scott and system
---------------------------------------------

SQL> connect system/manager
SQL> audit table by scott, system;
=> Audit succeeded.


check: Which statements are audited

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a9
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;

Result:

USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE
-------- ------ --------- ---------- ----------
SYSTEM TABLE BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS

Generate some audit information:

SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table t1;
=> table dropped
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> table created
SQL> drop table no;
=> *
ERROR at line 1:
ORA-00942: table or view does not exist

Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
12 DROP TABLE SCOTT
1 CREATE TABLE SYSTEM
12 DROP TABLE SYSTEM

SQL> select username, priv_used, ses_actions from dba_audit_object;

Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT
SYSTEM CREATE ANY TABLE
SYSTEM

Example of auditing SELECT TABLE by scott and system
----------------------------------------------------

SQL> connect system/manager
SQL> audit select table by scott, system;
=> Audit succeeded.


Check: Which statements are audited

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a13
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;

Result:

USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE
-------- ------ ------------- ---------- ----------
SYSTEM SELECT TABLE BY SESSION BY SESSION
SCOTT SELECT TABLE BY SESSION BY SESSION

Generate some audit information:

SQL> connect scott/tiger
SQL> delete from emp where ename='KING';

SQL> connect system/manager
SQL> insert into scott.emp (empno, ename) values (1, 'TEST');
SQL> select * from scott.emp;

Results of auditing:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;


Result:

USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM SELECT ANY TABLE ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------


SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Result:

ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
103 SESSION REC SCOTT
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM



Related Documents:
~~~~~~~~~~~~~~~~~~
Note 41800.1 : QREF: Quick Reference to Auditing Information
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 : How to Audit Connect Internal Using Oracle Server
Note 1020945.6: How to Setup Auditing
Note 1068714.6: How does the NOAUDIT option work
Note 99786.1 : How to Audit User Connection, Disconnection Date and Time
Note 174340.1 : Audit SYS user Operations (9.2 and up)