Database Support: Security DB Auditing, Consider

Dear Readers,

My name is Franz Devantier, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g) Security DBA. I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks.

I will make a deal with you: If you refer me to a company that needs database support, from a few hours per week to full time, and I am able to sign a contract with them.

Then I will give you 10% of the monthly contract or deal price every month. When the contract ends, and we re-sign the contract, I will again give you 10% of the monthly contract price. This will go on until the company no longer employs or contracts me or my agents to look after their databases.

I can do this, because that 10% is my marketing budget. When we re-sign the contract, in the future, it may depend on you giving the thumbs up again, and that is worth 10% of the monthly contract price, to be given to you as commission.

Contact: Franz

Security DB Auditing, Considerations - Part 3

Records Always in the Operating System and Syslog Audit Trail

There are some database-related actions that are always recorded in the operating system, and the syslog audit trails, regardless of whether the database auditing is enabled or not. This is referred to as mandatory auditing. The following actions are always recorded:

-> Instance Startup: An audit record is generated that includes the operating system user that started the instance, the terminal identifier of the user, and the date and time of the startup. This information is recorded in the operating system or syslog audit trails. The database audit trail is only available after the database has successfully started up.

-> At instance shutdown, an audit record is again generated with the details of the operating system user shutting down the instance, the terminal identifier of the user, and the date and time stamp.

-> With connections that are made to the database with administrative privileges, and audit record is generated that details the operating system user. This record provides accountability regarding users connected with administrative privileges.

Some operating systems don’t make an audit trail accessible to the Oracle Database. In cases like this, the audit trail records are placed in the Oracle audit trail file, in the same directory as the background process trace files, and in a similar format.

When Are Audit Records Created?

Standard auditing for the entire database is either enabled or disabled by the Database Security Administrator. If it is disabled, then no audit records are recorded.

Fine-Grained Auditing (FGA) uses audit policies that are applied to individual objects. Therefore standard audit settings that are either set on or off for the entire database, do not affect FGA.

If the database auditing is enabled on the database level, then individual audit options become effective. These individual audit options (not FGA) can be set by any authorized database user for database objects that he owns. When auditing is enabled for a certain action, and that action occurs, then an audit record is generated during the execute phase of the actionable statement. SQL statements inside PL/SQL program units are individually audited, when they are executed.

The generation and insertion of an audit trail record is independent of a user transaction being committed. So even if the transaction is rolled back, the audit trail remains intact. Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. Setting or changing audit options in a session, does not take effect in that session. The modified privilege auditing and statement auditing will take effect, once the current session has finished and a new session is created. However changes to schema object audit options become effective immediately and will come into effect in the current session.

Operations by the SYS user and by users who have connected through the SYSDBA or SYSOPER construct can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter.

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_sys_operations boolean FALSE

SQL> alter system set AUDIT_SYS_OPERATIONS=TRUE scope=spfile;

System altered.

(restart the database)

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_sys_operations boolean TRUE

SQL> create pfile from spfile;

File created.

SQL>

We find this entry in the initSID.ora file:

*.audit_sys_operations=TRUE

Every successful top-level SQL statement that is directly issued from SYS is audited. This specialized form of auditing will audit all actions performed by every user with the SYSDBA privilege, and it writes only to the operating system location. It is not dependent on the standard auditing parameter AUDIT_TRAIL.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_trail string DB, EXTENDED

SQL>

Storing these records in a location separate from the usual database audit trail in the SYS schema provides for greater auditing security.

Statement Auditing

Statement auditing audits a particular type of statement, that is not related to a specific named structure or schema object.

-> DDL statements: for example; AUDIT TABLE, will audit all CREATE and DROP TABLE statements.

-> DML statements: for example; AUDIT SELECT TABLE, will audit all SELECT … FROM TABLE/VIEW statements, regardless of the table or view statement being executed.

Statement auditing can be broad or focused. For example you can audit the activities of all database users, or only a selected list of users.

Privilege Auditing

You would want to define a selection of privileges that you like to audit. Here is my set of system privileges that I would like to be audited, before we get to any specific objects to audit.

AUDIT ADMINISTER ANY SQL TUNING SET;

AUDIT ADMINISTER DATABASE TRIGGER;

AUDIT ADMINISTER SQL MANAGEMENT OBJECT;

AUDIT ADMINISTER SQL TUNING SET;

AUDIT ADVISOR;

AUDIT ALTER ANY ASSEMBLY;

AUDIT ALTER ANY CLUSTER;

AUDIT ALTER ANY CUBE;

AUDIT ALTER ANY CUBE DIMENSION;

AUDIT ALTER ANY DIMENSION;

AUDIT ALTER ANY EDITION;

AUDIT ALTER ANY EVALUATION CONTEXT;

AUDIT ALTER ANY INDEX;

AUDIT ALTER ANY INDEXTYPE;

AUDIT ALTER ANY LIBRARY;

AUDIT ALTER ANY MATERIALIZED VIEW;

AUDIT ALTER ANY MINING MODEL;

AUDIT ALTER ANY OPERATOR;

AUDIT ALTER ANY OUTLINE;

AUDIT ALTER ANY PROCEDURE;

AUDIT ALTER ANY ROLE;

AUDIT ALTER ANY RULE;

AUDIT ALTER ANY RULE SET;

AUDIT ALTER ANY SEQUENCE;

AUDIT ALTER ANY SQL PROFILE;

AUDIT ALTER ANY TABLE;

AUDIT ALTER ANY TRIGGER;

AUDIT ALTER ANY TYPE;

AUDIT ALTER DATABASE;

AUDIT ALTER DATABASE LINK;

AUDIT ALTER PROFILE;

AUDIT ALTER PUBLIC DATABASE LINK;

AUDIT ALTER RESOURCE COST;

AUDIT ALTER ROLLBACK SEGMENT;

AUDIT ALTER SESSION;

AUDIT ALTER SYSTEM;

AUDIT ALTER TABLESPACE;

AUDIT ALTER USER;

AUDIT ANALYZE ANY;

AUDIT ANALYZE ANY DICTIONARY;

AUDIT AUDIT ANY;

AUDIT AUDIT SYSTEM;

AUDIT BACKUP ANY TABLE;

AUDIT BECOME USER;

AUDIT CHANGE NOTIFICATION;

AUDIT COMMENT ANY MINING MODEL;

AUDIT COMMENT ANY TABLE;

AUDIT CREATE ANY ASSEMBLY;

AUDIT CREATE ANY CLUSTER;

AUDIT CREATE ANY CONTEXT;

AUDIT CREATE ANY CUBE;

AUDIT CREATE ANY CUBE BUILD PROCESS;

AUDIT CREATE ANY CUBE DIMENSION;

AUDIT CREATE ANY DIMENSION;

AUDIT CREATE ANY DIRECTORY;

AUDIT CREATE ANY EDITION;

AUDIT CREATE ANY EVALUATION CONTEXT;

AUDIT CREATE ANY INDEX;

AUDIT CREATE ANY INDEXTYPE;

AUDIT CREATE ANY JOB;

AUDIT CREATE ANY LIBRARY;

AUDIT CREATE ANY MATERIALIZED VIEW;

AUDIT CREATE ANY MEASURE FOLDER;

AUDIT CREATE ANY MINING MODEL;

AUDIT CREATE ANY OPERATOR;

AUDIT CREATE ANY OUTLINE;

AUDIT CREATE ANY PROCEDURE;

AUDIT CREATE ANY RULE;

AUDIT CREATE ANY RULE SET;

AUDIT CREATE ANY SEQUENCE;

AUDIT CREATE ANY SQL PROFILE;

AUDIT CREATE ANY SYNONYM;

AUDIT CREATE ANY TABLE;

AUDIT CREATE ANY TRIGGER;

AUDIT CREATE ANY TYPE;

AUDIT CREATE ANY VIEW;

AUDIT CREATE ASSEMBLY;

AUDIT CREATE CLUSTER;

AUDIT CREATE CUBE;

AUDIT CREATE CUBE BUILD PROCESS;

AUDIT CREATE CUBE DIMENSION;

AUDIT CREATE DATABASE LINK;

AUDIT CREATE DIMENSION;

AUDIT CREATE EVALUATION CONTEXT;

AUDIT CREATE EXTERNAL JOB;

AUDIT CREATE INDEXTYPE;

AUDIT CREATE JOB;

AUDIT CREATE LIBRARY;

AUDIT CREATE MATERIALIZED VIEW;

AUDIT CREATE MEASURE FOLDER;

AUDIT CREATE MINING MODEL;

AUDIT CREATE OPERATOR;

AUDIT CREATE PROCEDURE;

AUDIT CREATE PROFILE;

AUDIT CREATE PUBLIC DATABASE LINK;

AUDIT CREATE PUBLIC SYNONYM;

AUDIT CREATE ROLE;

AUDIT CREATE ROLLBACK SEGMENT;

AUDIT CREATE RULE;

AUDIT CREATE RULE SET;

AUDIT CREATE SEQUENCE;

AUDIT CREATE SESSION;

AUDIT CREATE SYNONYM;

AUDIT CREATE TABLE;

AUDIT CREATE TABLESPACE;

AUDIT CREATE TRIGGER;

AUDIT CREATE TYPE;

AUDIT CREATE USER;

AUDIT CREATE VIEW;

AUDIT DEBUG ANY PROCEDURE;

AUDIT DEBUG CONNECT SESSION;

AUDIT DELETE ANY CUBE DIMENSION;

AUDIT DELETE ANY MEASURE FOLDER;

AUDIT DELETE ANY TABLE;

AUDIT DEQUEUE ANY QUEUE;

AUDIT DROP ANY ASSEMBLY;

AUDIT DROP ANY CLUSTER;

AUDIT DROP ANY CONTEXT;

AUDIT DROP ANY CUBE;

AUDIT DROP ANY CUBE BUILD PROCESS;

AUDIT DROP ANY CUBE DIMENSION;

AUDIT DROP ANY DIMENSION;

AUDIT DROP ANY DIRECTORY;

AUDIT DROP ANY EDITION;

AUDIT DROP ANY EVALUATION CONTEXT;

AUDIT DROP ANY INDEX;

AUDIT DROP ANY INDEXTYPE;

AUDIT DROP ANY LIBRARY;

AUDIT DROP ANY MATERIALIZED VIEW;

AUDIT DROP ANY MEASURE FOLDER;

AUDIT DROP ANY MINING MODEL;

AUDIT DROP ANY OPERATOR;

AUDIT DROP ANY OUTLINE;

AUDIT DROP ANY PROCEDURE;

AUDIT DROP ANY ROLE;

AUDIT DROP ANY RULE;

AUDIT DROP ANY RULE SET;

AUDIT DROP ANY SEQUENCE;

AUDIT DROP ANY SQL PROFILE;

AUDIT DROP ANY SYNONYM;

AUDIT DROP ANY TABLE;

AUDIT DROP ANY TRIGGER;

AUDIT DROP ANY TYPE;

AUDIT DROP ANY VIEW;

AUDIT DROP PROFILE;

AUDIT DROP PUBLIC DATABASE LINK;

AUDIT DROP PUBLIC SYNONYM;

AUDIT DROP ROLLBACK SEGMENT;

AUDIT DROP TABLESPACE;

AUDIT DROP USER;

AUDIT ENQUEUE ANY QUEUE;

AUDIT EXECUTE ANY ASSEMBLY;

AUDIT EXECUTE ANY CLASS;

AUDIT EXECUTE ANY EVALUATION CONTEXT;

AUDIT EXECUTE ANY INDEXTYPE;

AUDIT EXECUTE ANY LIBRARY;

AUDIT EXECUTE ANY OPERATOR;

AUDIT EXECUTE ANY PROCEDURE;

AUDIT EXECUTE ANY PROGRAM;

AUDIT EXECUTE ANY RULE;

AUDIT EXECUTE ANY RULE SET;

AUDIT EXECUTE ANY TYPE;

AUDIT EXECUTE ASSEMBLY;

AUDIT EXEMPT ACCESS POLICY;

AUDIT EXEMPT IDENTITY POLICY;

AUDIT EXPORT FULL DATABASE;

AUDIT FLASHBACK ANY TABLE;

AUDIT FLASHBACK ARCHIVE ADMINISTER;

AUDIT FORCE ANY TRANSACTION;

AUDIT FORCE TRANSACTION;

AUDIT GLOBAL QUERY REWRITE;

AUDIT GRANT ANY OBJECT PRIVILEGE;

AUDIT GRANT ANY PRIVILEGE;

AUDIT GRANT ANY ROLE;

AUDIT IMPORT FULL DATABASE;

AUDIT INSERT ANY CUBE DIMENSION;

AUDIT INSERT ANY MEASURE FOLDER;

AUDIT INSERT ANY TABLE;

AUDIT LOCK ANY TABLE;

AUDIT MANAGE ANY FILE GROUP;

AUDIT MANAGE ANY QUEUE;

AUDIT MANAGE FILE GROUP;

AUDIT MANAGE SCHEDULER;

AUDIT MANAGE TABLESPACE;

AUDIT MERGE ANY VIEW;

AUDIT ON COMMIT REFRESH;

AUDIT QUERY REWRITE;

AUDIT READ ANY FILE GROUP;

AUDIT RESTRICTED SESSION;

AUDIT RESUMABLE;

AUDIT SELECT ANY CUBE;

AUDIT SELECT ANY CUBE DIMENSION;

AUDIT SELECT ANY DICTIONARY;

AUDIT SELECT ANY MINING MODEL;

AUDIT SELECT ANY SEQUENCE;

AUDIT SELECT ANY TABLE;

AUDIT SELECT ANY TRANSACTION;

AUDIT SYSDBA;

AUDIT SYSOPER;

AUDIT UNDER ANY TABLE;

AUDIT UNDER ANY TYPE;

AUDIT UNDER ANY VIEW;

AUDIT UNLIMITED TABLESPACE;

AUDIT UPDATE ANY CUBE;

AUDIT UPDATE ANY CUBE BUILD PROCESS;

AUDIT UPDATE ANY CUBE DIMENSION;

AUDIT UPDATE ANY TABLE;

Privilege auditing audits statements that use a system privilege, for example SELECT ANY TABLE. When AUDIT SELECT ANY TABLE is in force, all statements issued by users with the SELECT ANY TABLE privilege are audited.

Privilege auditing does not occur, if the action is already permitted by the existing owner and schema object privileges. Privilege auditing is triggered only if they are insufficient, that is, only if what makes the action possible is a system privilege.

Privilege auditing is more focused than statement auditing, because each privilege auditing option audits only specific types of statements, and not a related list of statements. For example, the statement auditing clause, TABLE, audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements. However the privilege auditing option CREATE TABLE, audits only CREATE TABLE statements. This is because only the CREATE TABLE statement requires the CREATE TABLE privilege.

Schema Object Auditing

Schema object auditing can audit all SELECT and DML statements permitted by schema object privileges. For example SELECT or DELETE statements on a given schema table. The GRANT and REVOKE statements that control those privileges are also audited.

You can audit many schema objects, like statements that reference tables, views, stored procedures, functions, packages, sequences. However you can’t audit the individual procedures within a package, but only the package itself.

Statements that reference clusters, database links, indexes, and synonyms are not audited directly. You can indirectly audit access to these schema objects if you audit the operations that affect the base table involved.

It is important to note that schema object audit options, apply to all users of the database. You can’t limit them to a specific list of users. You can also set default schema object audit options, for all auditable schema objects.

Schema Object Audit Options for Views, Procedures, and Other Elements

Views and procedures, including stored procedures, functions, packages and triggers, reference underlying schema objects. So there is a dependency between the views/procedures and the underlying schema objects. The auditing of views and procedures are therefore subject to the enabled audit options on the base schema objects that are referenced.

This could work like this in practice:

SQL> AUDIT SELECT on employees;

SQL> CREATE VIEW emp_dept AS

SELECT e.emp_id, e.last_name, d.dept_id

FROM employess e, departments d

WHERE e.dept_id = d.dept_id;

SQL> AUDIT SELECT ON emp_dept;

SQL> SELECT * FROM emp_dept;

Now we can see that two audit records were generated for the select from emp_dept. The first audit record would be for the query on the emp_dept view. The second audit record would be for the query on the base table employees. This was accessed indirectly through the emp_dept view. However the indirect query on the base table departments does not generate an audit record, because the SELECT audit option for this table is not enabled. Both of the above audit records reference the user who queried the emp_dept view.

Important to note that the audit options for a view or procedure are determined when the view or procedure is first placed and used in the shared pool. These audit options will remain set until the view or procedure is flushed or aged out of the shared pool and subsequently reloaded into the shared pool.

Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded. However if a view or procedure is in the shared pool cache, then any changes to the audit options of the base schema objects do not affect the status of the views or procedures in the shared pool.

Auditing Actions Enabled by Oracle Database 10G

Object or Element	Newly Auditable Actions
Materialized Views	DELETE, INSERT, SELECT, UPDATE, and FLASHBACK
Tables & views	FLASHBACK
Library	EXECUTE
Java source	EXECUTE
Queue	ENQUEUE and DEQUEUE