Privilege Auditing

 

select username, owner, action_name, priv_used
from dba_audit_object;

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]
[WHENVER [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

Shortcuts

Sometimes you may want to audit a group of options on all objects of a specific type. For instance, you want to audit CREATE, DROP and TRUNCATE on all tables by user JUDY whenever they are not successful. You could issue:

audit create table by judy whenever not successful;
audit drop table by judy whenever not successful;
audit truncate table by judy whenever not successful;

Or you could just issue:

audit table by judy whenever not successful;

This will audit the statements CREATE, DROP and TRUNCATE only, not other statements such as INSERT, DELTE, ALTER, etc. Therefore it is important to remember which of the operations are audited and which are not. The shortcuts and their actions have been given in table 8.3 below. The general syntax is:

 

AUDIT  <shortcut> ……

The available shortcuts have been described in Table 8.3 below.

IF THE SHORTCUT IS	THEN THE AUDITING OPTIONS ARE SET FOR
CLUSTER	CREATE CLUSTER AUDIT CLUSTER DROP CLUSTER TRUNCATE CLUSTER
CONTEXT	CREATE CONTEXT DROP CONTEXT
DATABASE LINK	CREATE DATABASE LINK DROP DATABASE LINK
DIMENSION	CREATE DIMENSION ALTER DIMENSION DROP DIMENSION
DIRECTORY	CREATE DIRECTORY DROP DIRECTORY
INDEX	CREATE INDEX ALTER INDEX DROP INDEX ANALYZE ANY
NOT EXISTS	This is a special case of shortcut. If this is set, any SQL statement failing due to the non-existence of an object triggers and audit record.

 

IF THE SHORTCUT IS	THEN THE AUDITING OPTIONS ARE SET FOR
PROCEDURE	CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE DROP FUNCTION DROP LIBRARY DROP PACKAGE DROP PROCEDURE JAVA STORED PROCEDURES
PROFILE	CREATE PROFILE ALTER PROFILE DROP PROFILE
PUBLIC DATABASE LINK	CREATE PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK
PUBLIC SYNONYM	CREATE PUBLIC SYNONYM DROP PUBLIC SYNONYM
ROLE	CREATE ROLE ALTER ROLE DROP ROLE SET ROLE

 

IF THE SHORTCUT IS	THEN THE AUDITING OPTIONS ARE SET FOR
ROLLBACK SEGMENT	CREATE ROLLBACK SEGMENT ALTER ROLLBACK SEGMENT DROP ROLLBACK SEGMENT
SEQUENCE	CREATE SEQUENCE DROP SEQUENCE
SESSION	The connections and disconnections by users are audited.
SYNONYM	CREATE SYNONYM DROP SYNONYM
SYSTEM AUDIT	This is also a special case of auditing. When a user issues the AUDIT command itself, or NOAUDIT, this setting triggers an audit record to be generated.
SYSTEM GRANT	Whenever the user grants a system privilege to another user or itself, this setting triggers an audit trail.
TABLE	CREATE TABLE DROP TABLE TRUNCATE TABLE
TABLESPACE	CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE

 

IF THE SHORTCUT IS	THEN THE AUDITING OPTIONS ARE SET FOR
TRIGGER	CREATE TRIGGER ALTER TRIGGER   Important: Only the statements that enable or disable a trigger by the ALTER TRIGGER command are logged, not altering the trigger body.   DROP TRIGGER ALTER TABLE   Only the trigger related statements in ALTER TABLE, such as adding or dropping triggers are audited.
TYPE	CREATE TYPE CREATE TYPE BODY ALTER TYPE DROP TYPE DROP TYPE BODY
USER	CREATE USER ALTER USER DROP USER
VIEW	CREATE VIEW DROP VIEW

Table 8.3 Shortcuts and their Auditing Actions

Oracle also provides another type of shortcut to audit some commonly used system privileges. They are

 

referred to as DBA, RESOURCE and CONNECT to record groups of privileges.

Note: The words CONNECT, RESOURCE, and DBA should not be confused with the database roles of the same names. These are just shortcuts to a group of privileges.

The following example shows how the shortcuts are used:

audit connect;

This will enable auditing for CREATE SESSION, since CONNECT is a shortcut for it. The shortcut RESOURCE enables auditing on the following:

• ALTER SYSTEM
   

• TABLE
   

• CLUSTER
   

• TABLESPACE
   

• ROLLBACK SEGMENT
   

• TYPE
   

• SYNONYM
   

• VIEW
   

• SEQUENCE
   

• DATABASE LINK
   

• PROCEDURE

So when you use AUDIT RESOURCE, the auditing is enabled for all objects mentioned above.

The third shortcut, DBA does the same as enabling auditing on the following:

• SYSTEM AUDIT
   

• USER