Oracle Security Checking Auditing Objects Settings 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.
 

---

Another way to look up this information is by creating a view on the view dba_obj_audit_opts to filter out the objects for which the audit is not set. This view is defined as follows:

* dba_obj_audit_opts_filtered

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

/*

This shows the audit options set on objects only
For those objects where an option is set, not all.
(c) 2003, Arup Nanda. All Rights Reserved.
*/

create or replace view dba_obj_audit_opts_filtered
as
select * from dba_obj_audit_opts
where ALT != '-/-'
or AUD != '-/-'
or COM != '-/-'
or DEL != '-/-'
or GRA != '-/-'
or IND != '-/-'
or INS != '-/-'
or LOC != '-/-'
or REN != '-/-'
or SEL != '-/-'
or UPD != '-/-'
or REF != '-/-'
or EXE != '-/-'
or CRE != '-/-'
or REA != '-/-'
or WRI != '-/-'
/

The advantage of this view is its foundation as the original view. Since it does not depend on the data dictionary internals, the applicability is somewhat improved across different versions as well as its supportability in the future versions.

However, there is a serious flaw in this view due to inconsistency in the Oracle database software. Selecting from the view dba_obj_audit_opts for some Index Organized Tables, we can see that the audits options are not populated by hyphens, but by nulls. To check this, issue this query:

select *
from dba_obj_audit_opts
where alt = chr(0)||'/'||chr(0)
/

The result comes back as the follows. Only part of the result is shown here.

OWNER
------------------------------
OBJECT_NAME                    OBJECT_TY
------------------------------ ---------
ALT AUD COM DEL GRA IND INS LOC REN SEL
--- --- --- --- --- --- --- --- --- ---
UPD REF EXE CRE REA WRI
--- --- --- --- --- ---
SYS_IOT_OVER_31407             TABLE
 /   /   /   /   /   /   /   /   /   /
 /   /   /   /   /   /

QS_WS
SYS_IOT_OVER_31434             TABLE
 /   /   /   /   /   /   /   /   /   /
 /   /   /   /   /   /

QS_WS
SYS_IOT_OVER_31452             TABLE
 /   /   /   /   /   /   /   /   /   /
 /   /   /   /   /   /

Note how the auditing options are set as “NULL/NULL”, not “-/-“ as in other cases. In the view dba_obj_audit_opts, since we are looking for non-matches with “-/-“, this view will show us the above records too. Therefore, either the view defined by us can be modified to exclude null values or the earlier view only_audits can be used instead.

dba_priv_audit_opts

The view described earlier shows the auditing options set for database objects like tables, views, etc. The other audit options set on system privileges such as CREATE TABLE, etc. are recorded in another view, dba_priv_audit_opts. An example may make it clear. Suppose we audit the ALTER SESSION statements issued by user JUDY. We would issue:

audit alter session by judy;

Then, we could query the view dba_priv_audit_opts to find out what audit options have been set.

select * from dba_priv_audit_opts;

The output comes back as:

USER_NAME PROXY_NAME PRIVILEGE        
--------- ---------- ------------------------------
SUCCESS              FAILURE
-------------------- ------------------------------
JUDY                 ALTER SESSION                     BY ACCESS            BY ACCESS

Note the dissimilarity with the earlier view for objects. This view does not display all the privileges, but only the ones that have been marked for audit. This facility makes it easier to quickly check the audit settings of any system privilege. The following section describes the columns of this view.

COLUMN NAME	DESCRIPTION
USER_NAME	The name of the user for which the privilege auditing is enabled.
PROXY_NAME	If the auditing is enabled for a proxy user, not a real one, this column shows that information.
PRIVILEGE	The privilege for which the auditing is set.
SUCCESS	SET or NOT SET, depending on the auditing action established for successful execution.
FAILURE	Similarly, SET or NOT SET, depending on unsuccessful execution.

If the audit option is not set, the columns SUCCESS and FAILURE will display "NOT SET."

Special Case DDL Operations

Let’s issue the following. Our intention is to audit all tables created by user JUDY.

audit create table by judy by session;

When we check the view:

select * from
dba_priv_audit_opts
where user_name = 'JUDY';

We get:

USER_NAME  PROXY_NAME PRIVILEGE      SUCCESS    FAILURE
---------- ---------- -------------- ---------- ---------
JUDY                  CREATE TABLE   BY ACCESS  BY ACCESS

Note a very important point here. The auditing option was specified as BY SESSION; but the columns show BY ACCESS, not the expected "BY SESSION". Could this be a bug?

Consider the situation for a moment. When the table CLAIMS is created, it's created by default in the current user's schema. The user could drop it and then create a table called CLAIMS again, in the same session. The table created the second time, although it bears the same name, is a different table. Therefore, an audit record should be generated for each occurrence of the creation of the table CLAIMS, not just one for both occurrences. Oracle takes this into account and marks the audit options as BY ACCESS even though the user specified it as BY SESSION. This occurs for all the DDL operations, not just CREATE TABLE.

* For DDL operations, the audit options are always set as BY ACCESS, even though the user has specified BY SESSION.

dba_stmt_audit_opts

This view records the audit settings for individual statements, which may or may not be privileges.

To find out what setting for statement auditing exists in a database, use the query:

select * from dba_stmt_audit_opts;

The output comes back as:

USER_NAME  PROXY_NAME AUDIT_OPTION     SUCCESS    FAILURE
---------- ---------- ---------------- ---------- -------
JUDY                  CREATE SESSION   BY ACCESS  NOT SET
NATHAN                CREATE SESSION   BY ACCESS  NOT SET

Doesn’t this result look deceptively similar to the other view we just saw, dba_priv_audit_opts? All the columns are the same, except AUDIT_OPTION. Actually they appear very similar, but hold different information as explained in the following section.

Difference between Statement and Privilege Auditing

It is important to understand the difference between the statement level audit and the privilege level audit described in these two different views. Most of the information seems to be in both views and therefore may be confusing to the casual observer. However, they record very different types of information.

Some statements can also be privileges. One such example is CREAT TABLE, which can be granted as a privilege to a user. But the user can also issue a statement such as "CREATE TABLE MYTAB …". In the first case, the privilege is granted and the action, if audited, is shown in dba_priv_audit_opts view. In the second case, the audit record is shown in the view dba_stmt_audit_opts.

Some SQL statements are only statements; not privileges. The following table 8.5 contains a list of such statements.

ALTER SEQUENCE

ALTER TABLE

CLUSTER

COMMENT TABLE

CONTEXT

CREATE DIRECTORY

DATABASE LINK

DEBUG PROCEDURE

DELETE TABLE

DIMENSION

DIRECTORY

DROP DIRECTORY

EXECUTE LIBRARY

EXECUTE PROCEDURE

EXISTS

GRANT DIRECTORY

GRANT LIBRARY

GRANT PROCEDURE

GRANT SEQUENCE

GRANT TABLE

GRANT TYPE

INDEX

INSERT TABLE

LOCK TABLE

MATERIALIZED VIEW

NETWORK

NOT EXISTS

PROCEDURE

PROFILE

PUBLIC DATABASE LINK

PUBLIC SYNONYM

ROLE

ROLLBACK SEGMENT

SELECT SEQUENCE

SELECT TABLE

SEQUENCE

SYNONYM

SYSTEM AUDIT

SYSTEM GRANT

TABLE

TABLESPACE

TRIGGER

TYPE

UPDATE TABLE

USER

VIEW

 Table 8.5 Statements that are not auditable as privileges.

For instance, we can audit TABLE by the user CLAIM_SCHEMA as follows:

audit table by claim_schema by session;

We will see this in the statement audit options view:

select * from dba_stmt_audit_opts;

Selecting the same information from dba_priv_audit_opts will not return any rows.

Similarly, there are SQL statements that can only be privileges, not statements issued by users. The following Table 8.6 has a list of those statements. The audit options on these statements are seen in the view dba_priv_audit_opts.

ADMINISTER DATABASE TRIGGER

ADMINISTER RESOURCE MANAGER

ADMINISTER SECURITY

ALTER ANY EVALUATION CONTEXT

ALTER ANY INDEXTYPE

ALTER ANY OPERATOR

ALTER ANY RULE

ALTER ANY RULE SET

ALTER ANY SECURITY PROFILE

AUDIT SYSTEM

CREATE ANY EVALUATION CONTEXT

CREATE ANY INDEXTYPE

CREATE ANY OPERATOR

CREATE ANY RULE

CREATE ANY RULE SET

CREATE ANY SECURITY PROFILE

CREATE EVALUATION CONTEXT

CREATE INDEXTYPE

CREATE OPERATOR

CREATE RULE

CREATE RULE SET

CREATE SECURITY PROFILE

DROP ANY EVALUATION CONTEXT

DROP ANY INDEXTYPE

DROP ANY OPERATOR

DROP ANY RULE

DROP ANY RULE SET

DROP ANY SECURITY PROFILE

EXECUTE ANY EVALUATION CONTEXT

EXECUTE ANY INDEXTYPE

EXECUTE ANY OPERATOR

EXECUTE ANY RULE

EXECUTE ANY RULE SET

UNDER ANY TABLE

UNDER ANY TYPE

UNDER ANY VIEW

Table 8.6 Privileges, not Statements

Using multiple Statement/Privileges

You cannot mix statements and privileges in a single audit statement. For instance, the following is correct, where two statements have been used.

audit alter sequence, alter table by claim_schema, judy by access;

Or this one, where two privileges have been used:

audit administer database trigger, alter any role by claim_schema, judy by access;

But this one is not:

audit administer database trigger, alter table by claim_schema, judy by access;

This is because, “database trigger” is a pure privilege and “alter table” is a pure statement.
 

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.