Oracle Security Privilege Auditing 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. 

---

Privilege Auditing

Suppose a user ALLPOWERFUL has been granted the system privilege CREATE ANY TABLE. Using that privilege, the user creates a table claim_master in CLAIM_SCHEMA's schema. This is a special kind of activity – the object claim_master is owned by CLAIM_SCHEMA but is created by ALLPOWERFUL. The Privilege Auditing records these types of statements in the audit trail. A simple example is shown below. The following query is used to start auditing whenever the user ALLPOWERFUL uses the CREATE ANY TABLE system privilege.

audit create any table by allpowerful by session;

The user issues the following command to create a table CLAIM_MASER in the CLAIM_SCHEMA's schema. It uses the system privilege CREATE ANY TABLE, not CREATE TABLE.

create table claim_schema.claim_master (………);

The user then issues the following statement to create a table claim_master in its own schema:

create table claim_schema.claim_master (………);

In this second statement, the user does not use CREATE ANY TABLE, but CREATE TABLE.

If the auditing options are set for CREATE ANY TABLE and not CREATE TABLE, only the first statement will be audited, not the second.

Let’s see the effects of the privileges used in auditing. In this example the user CLAIM_SCHEMA creates a table as:

create table sometab1 (col1 number);

Now, the user ALLPOWERFUL creates another table in CLAIM_SCHEMA’s schema as follows.

create table claim_schema.sometab2 (col1 number);

Since the user has the CREATE ANY TABLE system privilege, he can create a table in any other user’s schema.

Note the important point here. The owner of both these new tables is CLAIM_SCHEMA, although one is created by ALLPOWERFUL. Since we have set up auditing on the privilege CREATE ANY TABLE and that was precisely what the user used, the audit records for these two actions will be different.

Next, the user ALLPOWERFUL creates yet another table in his own schema.

create table sometab3 (col1 number);

This statement does not need the system privilege CREATE ANY TABLE, but just CREATE TABLE.

In each of these cases the auditing actions will be different. Let’s see how. Check the audit records generated by issuing the following query:

select username, owner, action_name, priv_used
from dba_audit_object
where obj_name like 'SOMETAB%'
/

And the result comes back as:

USERNAME        OWNER           ACTION_NAME     PRIV_USED
--------------- --------------- --------------- ----------------
CLAIM_SCHEMA    CLAIM_SCHEMA    CREATE TABLE    CREATE TABLE
ALLPOWERFUL     CLAIM_SCHEMA    CREATE TABLE    CREATE ANY TABLE
ALLPOWERFUL     ALLPOWERFUL     CREATE TABLE    CREATE TABLE

We have several important points to be noted on the results.

• In the first record, the USERNAME column is CLAIM_SCHEMA, as the user himself issued the statement to create the object in his own schema. Therefore the value of the OWNER column is also CLAIM_SCHEMA.

• In the second record, however, the USERNAME column shows ALLPOWERFUL, the user who actually issued the statement. The object was created in the schema of CLAIM_SCHEMA; so the OWNER is different.

• The user CLAIM_SCHEMA created the table using the privilege CREATE TABLE, therefore the column PRIV_USED reflects that. In the second case, the user ALLPOWERFUL used the privilege CREATE ANY TABLE, the same is reflected in the appropriate column.

• In the third case, the user ALLPOWERFUL used the privilege CREATE TABLE to create the table; so the PRIV_USED column recorded that.

Even though the results were similar, the auditing process captured the exact way these objects were created. This is a very powerful feature to capture the actions of the users regardless of what objects resulted. The model can also be used to check if a user with sweeping privileges, such as SELECT ANY TABLE, attempted to select data from other tables. The username will be recorded in the audit tables.

This can be used to enforce HIPAA mandated security requirements effectively. As we discussed in the earlier chapters, we determined that no user, except SYS and SYSTEM, should have sweeping privileges. But if one does, for business reasons, auditing can easily point out the objects that were accessed using these privileges and then a case can be made to assign specific grants.

The general syntax of the Privilege Auditing is:

AUDIT
<privilege_list>|ALL PRIVILEGES
[BY <username>|<proxyuser>]
[ON BEHALF OF <userlist>|ANY] Useful only if the Proxyuser is used.
[BY SESSION|ACCESS]
[WHENEVER [NOT] SUCCESSFUL]

where:

• privilege_list is the list of privileges that can be audited. If more than one privilege is given, they are separated by commas. The allowable privileges are shown in Table 8.2. This is available in the view system_privilege_map.

• ALL - If all the privileges used by a user are to be audited, then the key clause ALL PRIVILEGES can be used.

• username is the name of the user for which the audit is enabled.

Examples

To enable auditing on CREATE TABLE and CREATE PROCEDURE by user CLAIM_SCHEMA, use the statement:

Audit create table, create procedure by claim_schema by session;

To enable auditing of all privileges possible for the same user, use the following:

audit all privileges by claim_schema by session;

If the auditing on CREATE ANY TABLE is enabled for all users, the BY <username> clause should be omitted, as in:

audit create table by access whenever successful;

This will audit the statement on all users. The following statement creates an audit record when user Judy wants to connect but was not successful due to some problem, such as invalid password.

audit create session by judy by session whenever not successful;

Table 8.2 below shows the list of allowed privileges that can be specified in the <privilege_list>.

ADMINISTER DATABASE TRIGGER

ADMINISTER RESOURCE MANAGER

ADMINISTER SECURITY

ALTER ANY CLUSTER

ALTER ANY DIMENSION

ALTER ANY EVALUATION CONTEXT

ALTER ANY INDEX

ALTER ANY INDEXTYPE

ALTER ANY LIBRARY

ALTER ANY OPERATOR

ALTER ANY OUTLINE

ALTER ANY PROCEDURE

ALTER ANY ROLE

ALTER ANY RULE

ALTER ANY RULE SET

ALTER ANY SECURITY PROFILE

ALTER ANY SEQUENCE

ALTER ANY SNAPSHOT

ALTER ANY TABLE

ALTER ANY TRIGGER

ALTER ANY TYPE

ALTER DATABASE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLLBACK SEGMENT

ALTER SESSION

ALTER SYSTEM

ALTER TABLESPACE

ALTER USER

ANALYZE ANY

AUDIT ANY

AUDIT SYSTEM

BACKUP ANY TABLE

BECOME USER

COMMENT ANY TABLE

CREATE ANY CLUSTER

CREATE ANY CONTEXT

CREATE ANY DIMENSION

CREATE ANY DIRECTORY

CREATE ANY EVALUATION CONTEXT

CREATE ANY INDEX

CREATE ANY INDEXTYPE

CREATE ANY LIBRARY

CREATE ANY OPERATOR

CREATE ANY OUTLINE

CREATE ANY PROCEDURE

CREATE ANY RULE

CREATE ANY RULE SET

CREATE ANY SECURITY PROFILE

CREATE ANY SEQUENCE

CREATE ANY SNAPSHOT

CREATE ANY SYNONYM

CREATE ANY TABLE

CREATE ANY TRIGGER

CREATE ANY TYPE

CREATE ANY VIEW

CREATE CLUSTER

CREATE DATABASE LINK

CREATE DIMENSION

CREATE EVALUATION CONTEXT

CREATE INDEXTYPE

CREATE LIBRARY

CREATE OPERATOR

CREATE PROCEDURE

CREATE PROFILE

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE RULE

CREATE RULE SET

CREATE SECURITY PROFILE

CREATE SEQUENCE

CREATE SESSION

CREATE SNAPSHOT

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE USER

CREATE VIEW

DEBUG ANY PROCEDURE

DEBUG CONNECT ANY

DEBUG CONNECT SESSION

DEBUG CONNECT USER

DELETE ANY TABLE

DEQUEUE ANY QUEUE

DROP ANY CLUSTER

DROP ANY CONTEXT

DROP ANY DIMENSION

DROP ANY DIRECTORY

DROP ANY EVALUATION CONTEXT

DROP ANY INDEX

DROP ANY INDEXTYPE

DROP ANY LIBRARY

DROP ANY OPERATOR

DROP ANY OUTLINE

DROP ANY PROCEDURE

DROP ANY ROLE

DROP ANY RULE

DROP ANY RULE SET

DROP ANY SECURITY PROFILE

DROP ANY SEQUENCE

DROP ANY SNAPSHOT

DROP ANY SYNONYM

DROP ANY TABLE

DROP ANY TRIGGER

DROP ANY TYPE

DROP ANY VIEW

DROP PROFILE

DROP PUBLIC DATABASE LINK

DROP PUBLIC SYNONYM

DROP ROLLBACK SEGMENT

DROP TABLESPACE

DROP USER

ENQUEUE ANY QUEUE

EXECUTE ANY EVALUATION CONTEXT

EXECUTE ANY INDEXTYPE

EXECUTE ANY LIBRARY

EXECUTE ANY OPERATOR

EXECUTE ANY PROCEDURE

EXECUTE ANY RULE

EXECUTE ANY RULE SET

EXECUTE ANY TYPE

EXEMPT ACCESS POLICY

FLASHBACK ANY TABLE

FORCE ANY TRANSACTION

FORCE TRANSACTION

GLOBAL QUERY REWRITE

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

INSERT ANY TABLE

LOCK ANY TABLE

MANAGE ANY QUEUE

MANAGE TABLESPACE

ON COMMIT REFRESH

QUERY REWRITE

READUP

READUP DBHIGH

RESTRICTED SESSION

RESUMABLE

SELECT ANY DICTIONARY

SELECT ANY SEQUENCE

SELECT ANY TABLE

SYSDBA

SYSOPER

UNDER ANY TABLE

UNDER ANY TYPE

UNDER ANY VIEW

UNLIMITED TABLESPACE

UPDATE ANY TABLE

WRITEDOWN

WRITEDOWN DBLOW

WRITEUP

WRITEUP DBHIGH

Table 8.2 System Privilege Auditing Options

 

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.