Oracle Security Checking the Settings for Auditing Objects Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Checking the Settings for Auditing Objects

The data dictionary view dba_obj_audit_opts records all the objects and the audit settings on all the possible statements that could be made against them. Please note that this records all the objects, whether or not there is an audit event set. Therefore, this view tends to return a large number of records for each object in the database.

A smaller view, user_obj_audit_opts holds the same structure, but only for the user logged in.

The view shows the audit options set for the object, one column per each type of statement. For instance, if we decide to audit all ALTER statements on table CLAIMS owned user CLAIM_SCHEMA, we would issue:

AUDIT ALTER ON CLAIM_SCHEMA.CLAIMS BY SESSION;

After this, we can see what audit options exist for this object by issuing:

select ALT from dba_obj_audit_opts
where owner = 'CLAIM_SCHEMA'
and object_name = 'CLAIMS';

The result comes back as:

ALT
---
S/S

Note the column name, ALT, that signifies the audit options for the ALTER statements on the object. A separate column exists for each of the statements. A complete list is provided in Table 8.5, where the columns of this view have been described.

Note the value is shown as "S/S". It means that the options are set to record the statement when the statement is successful (the letter S in the first position) and unsuccessful (the other letter S in the second position). Since we did not specify which to audit, the default of both was applied.

If we decide not to audit the object, we can specify:

noaudit alter on claim_schema.claims;

Now, if we check the settings again:

select ALT from dba_obj_audit_opts
where owner = 'CLAIM_SCHEMA'
and object_name = 'CLAIMS';

The result comes back as:

ALT
---
-/-

Note, the values have hyphens in them, meaning the audit action is not set. If while setting the audit, we specify to audit only when the attempt is unsuccessful, we can set:

audit alter on claim_schema.claims by session whenever not successful;
select ALT from dba_obj_audit_opts
where owner = 'CLAIM_SCHEMA'
and object_name = 'CLAIMS';

ALT
---
-/S

Note the first position, which shows the setting for auditing successful attempts, shows a hyphen ("-"), since we did not set it. The second position, for unsuccessful attempts, shows "S", meaning it has been set.

Another type of auditing is set for ACCESS as in:

audit select on claim_schema.claims by access;

In this case the values in this column are recorded as A, as shown in the following query:

select SEL from dba_obj_audit_opts
where owner = 'CLAIM_SCHEMA'
and object_name = 'CLAIMS';

The result comes back as:

SEL
---
A/A

It shows the first letter A, for successful attempts and the second letter A, for unsuccessful ones, exactly like the BY SESSION option.

Table 8.5 below shows the columns of this view and their description.

COLUMN NAME	DESCRIPTION
OWNER	The owner of the object on which this auditing option is set.
OBJECT_NAME	The name of the object.
OBJECT_TYPE	The type of the object, e.g. TABLE, VIEW, PROCEDURE, etc.
ALT	If the auditing options for ALTER statements on the object is set. The format is as described earlier, <frequency>/<frequency>, where <frequency> is either ‘A’ (for BY ACCESS) or ‘S’ (for BY SESSION). If not set, that particular value is a hyphen “-“. The left character represents successful execution and the right one failure.
AUD	Auditing option for the issuance of the AUDIT command on that object.
COM	Auditing options for creation of comments on this object.
DEL	Auditing options for DELETE on this object.
GRA	Auditing options for GRANTs performed on this object.
IND	Auditing options for INDEX operations on this object.
INS	Auditing options for INSERTs to this object.
LOC	Whenever a lock is attained on this object, this auditing option specifies whether a record is written or not.
REN	Auditing option when the object is renamed.
SEL	Auditing option when the object is SELECTed from.
UPD	Auditing option when an UPDATE is issued on the object.
REF	If the table is a referenced in a foreign key relationship, then this audit option can be set.
EXE	Auditing EXECUTE operations on the object. It is valid for only procedures, packages, and type bodies.
CRE	Auditing options when a CREATE statement is issued for that object.
REA	Auditing option for READ on the object. Relevant only for directory objects.
WRI	Auditing options for writing operations.

Table 8.5 Columns of the view dba_obj_audit_opts

An Alternative View

The biggest problem with this view is the way the information is presented. The information is shown in fields as letters and this view contains a line for each of the objects in the database, whether or not it has auditing enabled. To search if there is auditing enabled for an object for any of the options, you would have to write a query similar to the following:

select *
from dba_obj_audit_opts
where owner = 'claim_schema'
and object_name = 'claims'
and object_type = 'table'
and alt != '-/-'
and aud != '-/-'
….
and so on for all the columns.

This query is rather complicated and cumbersome to write, prone to errors, and inconvenient. Upon selecting the view definition query from the view dba_views for the view dba_obj_audit_opts, we can see that it is based on the individual data dictionary tables that store the objects, e.g. tab$ for tables, procedure$ for procedures, etc. The column AUDIT$ in all these data dictionary tables stores the information of the audit setting, if set. For instance, if the audit option by access is set for ALTERs on table CLAIM_SCHEMA, the column AUDIT$ for that table in the table tab$ is set to

AA------------------------------

Note how the first two characters are ”A” and “A” and the rest are all hyphens, indicating that the audit option on ALTER on this table is set by access (as shown by A), for both successful and unsuccessful (A in both places) and no other audit option is set (as shown by hyphens).

This information can be used to our advantage. Here we will create another view, called only_obj_audit_opts, which will contain records only if the audit options are present. The structure is identical to the dba_obj_audit_opts view, but the records are filtered. This new view is created with the following listing. It is an adaptation of the original view with our changes highlighted.

* present_dba_obj_audit_opts.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

create or replace view present_dba_obj_audit_opts
(
   username,
   object_name,
   object_type,
   ALT, /* Alter */
   AUD, /* Audit */
   COM, /* Comment */
   DEL, /* Delete */
   GRA, /* Grant */
   IND, /* Index */
   INS, /* Insert */
   LOC, /* Lock */
   REN, /* Rename */
   SEL, /* Select */
   UPD, /* Update */
   REF, /* Reference */
   EXE, /* Execute */
   CRE, /* Create */
   REA, /* Read */
   WRI  /* Write */
)
as
select u.name, o.name, 'TABLE',
   substr(t.audit$,1,1)||'/'||substr(t.audit$,2,1),
   substr(t.audit$,3,1)||'/'||substr(t.audit$,4,1),
   substr(t.audit$,5,1)||'/'||substr(t.audit$,6,1),
   substr(t.audit$,7,1)||'/'||substr(t.audit$,8,1),
   substr(t.audit$,9,1)||'/'||substr(t.audit$,10,1),
    substr(t.audit$,11,1)||'/'||substr(t.audit$,12,1),
   substr(t.audit$,13,1)||'/'||substr(t.audit$,14,1),
   substr(t.audit$,15,1)||'/'||substr(t.audit$,16,1),
   substr(t.audit$,17,1)||'/'||substr(t.audit$,18,1),
   substr(t.audit$,19,1)||'/'||substr(t.audit$,20,1),
   substr(t.audit$,21,1)||'/'||substr(t.audit$,22,1),
   substr(t.audit$,23,1)||'/'||substr(t.audit$,24,1),
   substr(t.audit$,25,1)||'/'||substr(t.audit$,26,1),
   substr(t.audit$,27,1)||'/'||substr(t.audit$,28,1),
   substr(t.audit$,29,1)||'/'||substr(t.audit$,30,1),
   substr(t.audit$,31,1)||'/'||substr(t.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.tab$ t
where o.type# = 2
  and not (o.owner# = 0 and o.name = '_default_auditing_options_')
  and o.owner# = u.user#
  and o.obj# = t.obj#
  and (t.audit$ like '%S%' or t.audit$ like '%A%')
union all
select u.name, o.name, 'VIEW',
   substr(v.audit$,1,1)||'/'||substr(v.audit$,2,1),
   substr(v.audit$,3,1)||'/'||substr(v.audit$,4,1),
   substr(v.audit$,5,1)||'/'||substr(v.audit$,6,1),
   substr(v.audit$,7,1)||'/'||substr(v.audit$,8,1),
   substr(v.audit$,9,1)||'/'||substr(v.audit$,10,1),      substr(v.audit$,11,1)||'/'||substr(v.audit$,12,1),
   substr(v.audit$,13,1)||'/'||substr(v.audit$,14,1),
   substr(v.audit$,15,1)||'/'||substr(v.audit$,16,1),
   substr(v.audit$,17,1)||'/'||substr(v.audit$,18,1),
   substr(v.audit$,19,1)||'/'||substr(v.audit$,20,1),
   substr(v.audit$,21,1)||'/'||substr(v.audit$,22,1),
   substr(v.audit$,23,1)||'/'||substr(v.audit$,24,1),
   substr(v.audit$,25,1)||'/'||substr(v.audit$,26,1),
   substr(v.audit$,27,1)||'/'||substr(v.audit$,28,1),
   substr(v.audit$,29,1)||'/'||substr(v.audit$,30,1),
   substr(v.audit$,31,1)||'/'||substr(v.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.view$ v
where o.type# = 4
  and o.owner# = u.user#
  and o.obj# = v.obj#
  and (v.audit$ like '%S%' or v.audit$ like '%A%')
union all
select u.name, o.name, 'SEQUENCE',
   substr(s.audit$,1,1)||'/'||substr(s.audit$,2,1),
   substr(s.audit$,3,1)||'/'||substr(s.audit$,4,1),
   substr(s.audit$,5,1)||'/'||substr(s.audit$,6,1),
   substr(s.audit$,7,1)||'/'||substr(s.audit$,8,1),
   substr(s.audit$,9,1)||'/'||substr(s.audit$,10,1),
   substr(s.audit$,11,1)||'/'||substr(s.audit$,12,1),
   substr(s.audit$,13,1)||'/'||substr(s.audit$,14,1),
   substr(s.audit$,15,1)||'/'||substr(s.audit$,16,1),
   substr(s.audit$,17,1)||'/'||substr(s.audit$,18,1),
   substr(s.audit$,19,1)||'/'||substr(s.audit$,20,1),
   substr(s.audit$,21,1)||'/'||substr(s.audit$,22,1),
   substr(s.audit$,23,1)||'/'||substr(s.audit$,24,1),
   substr(s.audit$,25,1)||'/'||substr(s.audit$,26,1),
   substr(s.audit$,27,1)||'/'||substr(s.audit$,28,1),
   substr(s.audit$,29,1)||'/'||substr(s.audit$,30,1),
   substr(s.audit$,31,1)||'/'||substr(s.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.seq$ s
where o.type# = 6
  and o.owner# = u.user#
  and o.obj# = s.obj#
  and (s.audit$ like '%S%' or s.audit$ like '%A%')
union all
select u.name, o.name, 'PROCEDURE',
   substr(p.audit$,1,1)||'/'||substr(p.audit$,2,1),
   substr(p.audit$,3,1)||'/'||substr(p.audit$,4,1),
   substr(p.audit$,5,1)||'/'||substr(p.audit$,6,1),
   substr(p.audit$,7,1)||'/'||substr(p.audit$,8,1),
   substr(p.audit$,9,1)||'/'||substr(p.audit$,10,1),
   substr(p.audit$,11,1)||'/'||substr(p.audit$,12,1),
   substr(p.audit$,13,1)||'/'||substr(p.audit$,14,1),
   substr(p.audit$,15,1)||'/'||substr(p.audit$,16,1),
   substr(p.audit$,17,1)||'/'||substr(p.audit$,18,1),
   substr(p.audit$,19,1)||'/'||substr(p.audit$,20,1),
   substr(p.audit$,21,1)||'/'||substr(p.audit$,22,1),
   substr(p.audit$,23,1)||'/'||substr(p.audit$,24,1),
   substr(p.audit$,25,1)||'/'||substr(p.audit$,26,1),
   substr(p.audit$,27,1)||'/'||substr(p.audit$,28,1),
   substr(p.audit$,29,1)||'/'||substr(p.audit$,30,1),
   substr(p.audit$,31,1)||'/'||substr(p.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.library$ p
where o.type# = 22
  and o.owner# = u.user#
  and o.obj# = p.obj#
  and (p.audit$ like '%S%' or p.audit$ like '%A%')
union all
select u.name, o.name, 'PROCEDURE',
   substr(p.audit$,1,1)||'/'||substr(p.audit$,2,1),
   substr(p.audit$,3,1)||'/'||substr(p.audit$,4,1),
   substr(p.audit$,5,1)||'/'||substr(p.audit$,6,1),
   substr(p.audit$,7,1)||'/'||substr(p.audit$,8,1),
   substr(p.audit$,9,1)||'/'||substr(p.audit$,10,1),
   substr(p.audit$,11,1)||'/'||substr(p.audit$,12,1),
   substr(p.audit$,13,1)||'/'||substr(p.audit$,14,1),
   substr(p.audit$,15,1)||'/'||substr(p.audit$,16,1),
   substr(p.audit$,17,1)||'/'||substr(p.audit$,18,1),
   substr(p.audit$,19,1)||'/'||substr(p.audit$,20,1),
   substr(p.audit$,21,1)||'/'||substr(p.audit$,22,1),
   substr(p.audit$,23,1)||'/'||substr(p.audit$,24,1),
   substr(p.audit$,25,1)||'/'||substr(p.audit$,26,1),
   substr(p.audit$,27,1)||'/'||substr(p.audit$,28,1),
   substr(p.audit$,29,1)||'/'||substr(p.audit$,30,1),
   substr(p.audit$,31,1)||'/'||substr(p.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.procedure$ p
where o.type# >= 7 and o.type# <= 9
  and o.owner# = u.user#
  and o.obj# = p.obj#
  and (p.audit$ like '%S%' or p.audit$ like '%A%')
union all
select u.name, o.name, 'TYPE',
   substr(t.audit$,1,1)||'/'||substr(t.audit$,2,1),
   substr(t.audit$,3,1)||'/'||substr(t.audit$,4,1),
   substr(t.audit$,5,1)||'/'||substr(t.audit$,6,1),
   substr(t.audit$,7,1)||'/'||substr(t.audit$,8,1),
   substr(t.audit$,9,1)||'/'||substr(t.audit$,10,1),
   substr(t.audit$,11,1)||'/'||substr(t.audit$,12,1),
   substr(t.audit$,13,1)||'/'||substr(t.audit$,14,1),
   substr(t.audit$,15,1)||'/'||substr(t.audit$,16,1),
   substr(t.audit$,17,1)||'/'||substr(t.audit$,18,1),
   substr(t.audit$,19,1)||'/'||substr(t.audit$,20,1),
   substr(t.audit$,21,1)||'/'||substr(t.audit$,22,1),
   substr(t.audit$,23,1)||'/'||substr(t.audit$,24,1),
   substr(t.audit$,25,1)||'/'||substr(t.audit$,26,1),
   substr(t.audit$,27,1)||'/'||substr(t.audit$,28,1),
   substr(t.audit$,29,1)||'/'||substr(t.audit$,30,1),
   substr(t.audit$,31,1)||'/'||substr(t.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.type_misc$ t
where o.type# = 13
  and o.owner# = u.user#
  and o.obj# = t.obj#
  and (t.audit$ like '%S%' or t.audit$ like '%A%')
union all
select u.name, o.name, 'DIRECTORY',
   substr(t.audit$,1,1)||'/'||substr(t.audit$,2,1),
   substr(t.audit$,3,1)||'/'||substr(t.audit$,4,1),
   substr(t.audit$,5,1)||'/'||substr(t.audit$,6,1),
   substr(t.audit$,7,1)||'/'||substr(t.audit$,8,1),
   substr(t.audit$,9,1)||'/'||substr(t.audit$,10,1),
   substr(t.audit$,11,1)||'/'||substr(t.audit$,12,1),
   substr(t.audit$,13,1)||'/'||substr(t.audit$,14,1),
   substr(t.audit$,15,1)||'/'||substr(t.audit$,16,1),
   substr(t.audit$,17,1)||'/'||substr(t.audit$,18,1),
   substr(t.audit$,19,1)||'/'||substr(t.audit$,20,1),
   substr(t.audit$,21,1)||'/'||substr(t.audit$,22,1),
   substr(t.audit$,23,1)||'/'||substr(t.audit$,24,1),
   substr(t.audit$,25,1)||'/'||substr(t.audit$,26,1),
   substr(t.audit$,27,1)||'/'||substr(t.audit$,28,1),
   substr(t.audit$,29,1)||'/'||substr(t.audit$,30,1),
substr(t.audit$,31,1)||'/'||substr(t.audit$,32,1)
from sys.obj$ o, sys.user$ u, sys.dir$ t
where o.type# = 23
  and o.owner# = u.user#
  and o.obj# = t.obj#
  and (t.audit$ like '%S%' or t.audit$ like '%A%')
/

This new view present_dba_obj_audit_opts will show records for only those objects for which the auditing options are enabled, not all the objects. This will help tremendously while checking quickly for a setting in audit.

A word of caution is in order here. This view is based on Oracle's unpublished data dictionary contents and is not supported by Oracle. The contents may change without notice during database upgrades and therefore this view may not work as per expectations. Please use this at your own risk. The authors do not take any responsibility in supporting this view.
 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.