This is the first of three articles on the topic of Oracle auditing. It is relevant to Oracle 10g, 11g, and 12c, although Unified Auditing in 12c makes some of this content irrelevant (if you choose to use Pure Unified Auditing).
Unified Auditing will be covered in the third part of this series.
Standard Auditing
Standard auditing allows you to audit activities based on activity type, object, privilege, or user. There are two stages involved when using standard auditing. The first is enabling the audit mechanism and the second is defining the audit categories – i.e. define what activity should produce records in the audit trail.
To enable (or disable) the audit trail you need to set the AUDIT_TRAIL initialization parameter. You then need to restart the database. For example:
SQL> alter system set audit_trail=db scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2097152000 bytes Fixed Size 1979968 bytes Variable Size 1124075968 bytes Database Buffers 956301312 bytes Redo Buffers 14794752 bytes Database mounted. Database opened.
Turning on the audit facility lets Oracle start recording audit records based on the AUDIT specifications you give it. The audit trail is either a data dictionary table, a set of operating system files, or a set of records sent to another system using syslog. You control where the audit records end up through the value you use for the AUDIT_TRAIL parameter. If you set the parameter to DB or DB_EXTENDED then the audit trail is maintained in the AUD$ system table. If you set the parameter to OS, XML, or XML_EXTENDED, then the audit trail is maintained by the operating system.
Database (DB) Auditing
The difference between DB and DB_EXTENDED is that in DB_EXTENDED you also get the full SQL text and the bind values for each audit record. With DB, the audit record only includes the command performed and not the full text. This is also the difference between XML and XML_EXTENDED. With either option, the audit trail is written to operating system files in the form of XML documents, but with DB_EXTENDED the SQL text and bind values are part of each audit record. When you choose OS the audit trail is still in the operating system but is not an XML format, just a text delimited format.
First set the audit trail to DB as shown above and audit all activity on. SCOTT.EMP:
SQL> audit all on scott.emp by access; Audit succeeded.
If you now perform any action on this table:
SQL> update emp set sal=sal*0.95 where job='MANAGER'; 3 rows updated.
You will get the following record in the audit trail:
select username, owner, obj_name, action_name, sql_text from dba_audit_trail USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT ----------- -------- ----------- ----------------- ------------- SCOTT SCOTT EMP UPDATE (null)
If, however, you change the audit trail to DB_EXTENDED and restart the database:
SQL> alter system set audit_trail='db_extended' scope=spfile; System altered.
The same activity will produce a record with the text:
select username, owner, obj_name, action_name, sql_text from dba_audit_trail USERNAME OWNER OBJ_NAME ACTION_NAME SQL_TEXT ----------- -------- ----------- ----------------- ------------- SCOTT SCOTT EMP UPDATE update emp set sal=sal*0.95 where job='MANAGER'
You can also use the following syntax:
SQL> alter system set audit_trail='db',’extended’ scope=spfile; System altered.
Or:
SQL> alter system set audit_trail=db,extended scope=spfile; System altered.
But you can’t use ‘DB,EXTENDED’ – which can be a little confusing at first:
SQL> alter system set audit_trail='db,extended' scope=spfile; alter system set audit_trail='db,extended' scope=spfile * ERROR at line 1: ORA-00096: invalid value xml,extended for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db
Operating System (OS) Auditing
To set the audit trail to OS, use the following and then restart the database:
SQL> alter system set audit_trail='os' scope=spfile; System altered.
The audit record will be part of an operating system file normally in the adump directory and each audit file will include records of the form (in Unix):
[oracle10@rh4u2x32p adump]$ cat ora_19158.aud Audit file /home/oracle10/admin/on0r4231/adump/ora_19158.aud Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle10/oracle/product/10.2.0/db_1 System name: Linux Node name: rh4u2x32p Release: 2.6.9-22.ELsmp Version: #1 SMP Mon Sep 19 18:32:14 EDT 2005 Machine: i686 Instance name: on0r4231 Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 19158, image: oracle@rh4u2x32p (TNS V1-V3) Wed Mar 5 22:24:03 2008 SESSIONID: "360568" ENTRYID: "1" STATEMENT: "1" USERID: "SCOTT" USERHOST: "rh4u2x32p" TERMINAL: "pts/4" ACTION: "100" RETURNCODE: "0" COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle10" PRIV$USED: 5 Wed Mar 5 22:24:10 2008 SESSIONID: "360568" ENTRYID: "2" STATEMENT: "7" USERID: "SCOTT" USERHOST: "rh4u2x32p" TERMINAL: "pts/4" ACTION: "6" RETURNCODE: "0" OBJ$CREATOR: "SCOTT" OBJ$NAME: "EMP" OS$USERID: "oracle10" Wed Mar 5 22:24:12 2008 SESSIONID: "360568" ENTRYID: "1" ACTION: "101" RETURNCODE: "0" LOGOFF$PREAD: "28" LOGOFF$LREAD: "287" LOGOFF$LWRITE: "18" LOGOFF$DEAD: "0" SESSIONCPU: "13"
To know what the ACTION (e.g. the value 101 above) means, check the AUDIT_ACTIONS data dictionary table.
On Unix, setting AUDIT_TRAIL=OS has another related function. You can set the AUDIT_SYSLOG_LEVEL initialization parameter with the form
The location of the audit files (when using OS files) is governed by the AUDIT_FILE_DEST initialization parameter, e.g.:
########################################### # Security and Auditing ########################################### audit_file_dest=/home/oracle10/admin/on0r4231/adump
To set the value from within the database use alter system set audit_file_dest =
To see the location of the audit directory:
SQL> show parameter audit_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ON0RON00\ADUMP
Oracle Auditing Qualifiers
When you audit, you can use WHENEVER SUCCESSFUL, WHENEVER NOT SUCCESSFUL or both – this is an audit qualifier. If you specify WHENEVER SUCCESSFUL you will only get an audit record when the activity being audited was successful. It is also common to audit WHENEVER NOT SUCCESSFUL – a failure to execute a command can indicate a security issue. The default is to create an audit record in both cases – this is what happens when you don’t specify anything.
The second qualifier available in any audit command allows you to specify whether you want an audit record per access or per session. If you specify BY ACCESS, Oracle creates one audit record for each access. If you specify BY SESSION, Oracle writes a single audit record per combination of session, object and SQL statement type. If the same statement is repeated multiple times within the same session there is no indication of this fact. Let’s look at a quick example.
First, audit all activity on SCOTT.EMP and request that an audit record be written per access:
SQL> audit all on scott.emp by access; Audit succeeded.
Connect and do the following:
SQL> connect scott
Enter password: *****
Connected.
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
1 row selected.
SQL> update emp set sal=6000 where sal=5000;
1 row updated.
Let’s look at the relevant records in the audit trail:
3/31/2008 7:04:16 PM SCOTT UPDATE update emp set sal=6000 where sal=5000 0 3/31/2008 7:04:02 PM SCOTT SELECT select max(sal) from emp 0
Now change the audit definition:
SQL> audit all on scott.emp by session; Audit succeeded.
Do the same sort of activities:
SQL> select max(sal) from emp;
MAX(SAL)
----------
6000
1 row selected.
SQL> update emp set sal=6500 where sal=6000;
1 row updated.
The audit trail looks very different this time:
3/31/2008 7:20:36 PM SCOTT SESSION REC select max(sal) from emp 0
There’s only one record even though there were two activities. The action name for this is SESSION REC. This indicates that this audit record represents all activities on the object. Don’t look at the SQL Text – it is meaningless when you use BY SESSION (it is simply the SQL text for the first access). What’s more important is to look at is the SES_ACTIONS column:
select timestamp,username,action_name,ses_actions from dba_audit_trail where obj_name='EMP' and owner='SCOTT' 3/31/2008 7:20:36 PM SCOTT SESSION REC ---------SS-----
The SES_ACTIONS column is a “bit map” of sorts – each position represents a certain activity type as shown in Table 9.1. In the example above you can see that the tenth and eleventh positions are marked with an “S”. “S” stands for success, “F” stands for failure, and “B” stands for both. Issue an update that fails and one insert that fails:
SQL> update emp set empno=5555 where empno=4444; update emp set empno=5555 where empno=4444 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.PK_EMP) violated SQL> insert into emp values(4444,'foo','bar',9999,'02-NOV-02',0,0,20); insert into emp values(4444,'foo','bar',9999,'02-NOV-02',0,0,20) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.PK_EMP) violated
The audit record shows S for SELECT, B for UPDATE and F for INSERT:
3/31/2008 7:20:36 PM SCOTT SESSION REC ------F--SB-----
Since there is only one audit record where everything is encoded, don’t expect meaningful values for any column that can change such as SQL text or return code.
When you ask for BY SESSION, Oracle creates a single record by session id and object id (AUD$.OBJ$CREATOR and OBJ$NAME) and per system privilege used. If any of these are different a new audit record is created. For example, if you drop a table and recreate it within the same session then multiple records with the same type will be written because the object ID has changed. Finally, BY SESSION auditing only applies to database audit trails – not to operating system audit trails. If you use the BY SESSION qualifier, the operating system files will still have one record per access – this is because Oracle can’t afford to read the files and update these records – something that it can easily do when the data is stored in a database. Note that the main advantage of BY SESSION auditing is not in performance. The main advantage is that if you’re only required to know what type of activities were performed by each user or session (as opposed to what precise SQL was sent, how many times each command was run, etc.), then BY SESSION auditing is far more compact, easier to manage and does not require you to deal with what may be millions of audit records. This qualifier simply gives you a tool to audit at the granularity level that best match your requirements.
Defining What to Audit
You can audit based on three separate categories (or any combination thereof) – statements, privileges and objects. When using statement auditing you specify which statements you want to be audited and an audit record is created for the execution of one of those statement types – e.g. a CREATE TABLE. The audit command requires you to specify which statement to audit, e.g.:
SQL> audit create table by scott; Audit succeeded. SQL> audit create table; Audit succeeded. To audit all statements: SQL> audit all; Audit succeeded. To audit a set of statements: SQL> audit create table, create procedure; Audit succeeded. Or, use statement groups as a shorthand – for example, rather than specify: SQL> audit alter user; Audit succeeded. SQL> audit drop user; Audit succeeded. SQL> audit create user; Audit succeeded.
You can do:
SQL> audit user; Audit succeeded.
You can use audit qualifiers to limit the cases when audit records are created, e.g.:
SQL> audit create table by access; Audit succeeded. SQL> audit create table by access whenever successful; Audit succeeded.
Finally, you can qualify the user for which statement auditing applies:
SQL> audit create table by scott; Audit succeeded. SQL> audit create table by scott by access; Audit succeeded.
Note that the user qualifier is not a fourth type by which you audit – it is a qualifier that you can add when using one the three audit specifications.
AUDIT SESSION allows you to audit creation of sessions. An audit record is created for each connection to the instance. It inserts a record into the audit trail at connection time and updates the audit record at disconnect time. When in the database, the record includes the connection time, the disconnection time, I/O processing time, etc. This audit option is of course only available BY SESSION. You can qualify AUDIT SESSION by user, e.g.:
SQL> audit session by scott; Audit succeeded.
AUDIT NOT EXIST allows you to audit statement failures that occur because the target object does not exist, e.g.:
SQL> audit not exists by access; Audit succeeded.
Now access an object that does not exist:
SQL> select * from foobar;
select * from foobar
*
ERROR at line 1:
ORA-00942: table or view does not exist
An audit record is generated. For example, if using XML, EXTENDED:
1 16219456 7 2 2008-04-02T08:30:27.243616 SCOTT oracle10 trex 21919 pts/2 0 SCOTT FOOBAR 3 942 select * from foobar
When using object auditing you specify that an audit record should be created whenever a certain action is done on a certain object. You specify objects and statements, and you can use the standard qualifiers, but you can’t qualify by user. For example:
SQL> audit select on scott.emp by access;
Audit succeeded.
SQL> audit update on scott.emp by access;
Audit succeeded.
SQL> audit all on scott.emp by access;
Audit succeeded.
SQL> audit insert, update, delete on scott.emp by session;
Audit succeeded.
SQL> audit select on scott.emp by scott;
audit select on scott.emp by scott
*
ERROR at line 1:
ORA-01708: ACCESS or SESSION expected
This is useful for objects that already exist – but how do you handle objects that have not been created yet? It isn’t feasible to tie any object creation to a new AUDIT call and do this manually. Instead, use ON DEFAULT. For example, to specify that any new object created should have an audit trail for INSERT, UPDATE and DELETE:
SQL> audit update,insert,delete ON DEFAULT by access; Audit succeeded.
Any new object will be “born” with these audit definitions (assuming that this object can be audited for these activities). To view what the defaults are:
SQL> select * from all_def_audit_opts; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/- -/- 1 row selected.
What this “bitmap shows is that any new object will automatically be audited for DELETE (DEL) – by access for success (the left side of A/A) and by access for failure (the right side of A/A), and the same for INSERT (INS) and UPDATE (UPD). This is a cumulative definition – form example:
SQL> audit update on default by session whenever successful; Audit succeeded. SQL> select * from all_def_audit_opts; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- A/A -/- -/- A/A -/- -/- -/- S/A -/- -/- -/- -/-
You can audit views as well as base tables. If you audit both the view and the base table, and access the view, then you’ll get multiple records. For example, create a view on EMP and then audit both the view and the base table:
SQL> create view emp_details as 2 select EMPNO,ENAME,JOB,MGR,HIREDATE,DEPTNO 3 from emp; View created. SQL> audit select on emp by access; Audit succeeded. SQL> audit select on emp_details by access; Audit succeeded.
If you then query the view you will get two audit records.
SQL> select count(*) from emp_details;
COUNT(*)
----------
18219
select USERNAME,TIMESTAMP,ACTION_NAME,OWNER,OBJ_NAME,SQL_TEXT from dba_audit_trail
SCOTT 4/2/2008 11:42:18 AM SELECT SCOTT EMP select count(*) from emp_details
SCOTT 4/2/2008 11:42:18 AM SELECT SCOTT EMP_DETAILS select count(*) from emp_details
When using privilege auditing you define the privileges that, when used, cause an audit record to be created. An audit record is created only when the privilege is used – regardless of which statement was used. For example, if you audit based on the use of a CREATE ANY TABLE privilege, then an audit record is only created when a user does a CREATE TABLE outside their schema – but if they create a table in their own schema (and utilize the CREATE TABLE privilege and not the CREATE ANY TABLE privilege) then an audit record is not written. For example:
SQL> connect sys as sysdba Enter password: Connected. SQL> grant create any table to scott; Grant succeeded. SQL> audit create any table by scott by access; Audit succeeded. SQL> connect scott Enter password: ***** Connected. SQL> create table TAB_WONT_SHOW(i int); Table created. SQL> create table HR.TAB_WILL_SHOW(i int); Table created.
The first CREATE TABLE does not use the CREATE ANY TABLE privilege so it will not show up in the audit trail. The PRIV_USED column in DBA_AUDIT_TRAIL shows you the privilege used:
SQL> select USERNAME,OWNER,OBJ_NAME,ACTION_NAME,PRIV_USED from dba_audit_trail; USERNAME OWNER ------------------------------ ------------------------------ OBJ_NAME -------------------------------------------------------------------------------- ACTION_NAME PRIV_USED ---------------------------- ---------------------------------------- SCOTT HR TAB_WILL_SHOW CREATE TABLE CREATE ANY TABLE
To audit all privileges (in this case with a user qualifier):
audit all privileges by scott by access;
Seeing What is Being Audited
There are three views that show you what is currently being audited by the standard audit facility. DBA_STMT_AUDIT_OPTS shows you what statement auditing is enabled, DBA_PRIV_AUDIT_OPTS shows you what privilege auditing is enabled, and DBA_OBJ_AUDIT_OPTS shows you what object auditing is enabled.
NOAUDIT
Reverse audit commands using NOAUDIT. For example, if you execute AUDIT ALL and then decide this is not required, you run NOAUDIT ALL. You don’t use the access/session qualifier when you use NOAUDIT, for example:
SQL> noaudit create table by access; noaudit create table by access ERROR at line 1: ORA-01718: BY ACCESS | SESSION clause not allowed for NOAUDIT SQL> noaudit create table; Noaudit succeeded.
You can use the WHENEVER qualifier to change the cases in which an audit record is created:
SCOTT EMP_DETAILS VIEW -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A S/A -/- -/- -/- -/- -/- -/- SQL> noaudit select on scott.emp_details whenever successful; Noaudit succeeded. SCOTT EMP_DETAILS VIEW -/- -/- -/- A/A -/- -/- A/A -/- -/- -/A S/A -/- -/- -/- -/- -/- -/-
What is very confusing for most people who start using standard auditing is that the AUDIT and NOAUDIT statements are not cumulative. Oracle does not generate a single concise profile of what to audit and adjusts it when you use AUDIT and NOAUDIT. The clearest example of this occurs when you execute NOAUDIT ALL. Contrary to what you may expect, this will not stop any auditing. It will only stop global AUDIT statements such as AUDIT ALL or AUDIT NOT EXIST. But if for example you have the following:
SQL> select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- SCOTT CREATE ANY TABLE BY ACCESS BY ACCESS SCOTT CREATE SESSION BY ACCESS BY ACCESS And you execute: SQL> noaudit all; Noaudit succeeded.
You will still be auditing:
SQL> select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- SCOTT CREATE ANY TABLE BY ACCESS BY ACCESS SCOTT CREATE SESSION BY ACCESS BY ACCESS
Oracle handles each of your audit specifications individually. Therefore, when you want to disable certain audits, you need to reverse your calls to AUDIT with equivalent calls to NOAUDIT. In the previous example, you have to explicitly call:
SQL> noaudit create session by scott; Noaudit succeeded. SQL> noaudit create any table by scott; Noaudit succeeded.
So now:
SQL> select * from dba_stmt_audit_opts; no rows selected
This is even true when you use user names as qualifiers, e.g:
SQL> select * from dba_stmt_audit_opts; no rows selected SQL> audit create session by scott; Audit succeeded. SQL> select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- SCOTT CREATE SESSION BY ACCESS BY ACCESS SQL> noaudit create session; Noaudit succeeded. SQL> select * from dba_stmt_audit_opts; USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- SCOTT CREATE SESSION BY ACCESS BY ACCESS SQL> noaudit create session by scott; Noaudit succeeded. SQL> select * from dba_stmt_audit_opts; no rows selected
Summary
Standard auditing is the workhorse of Oracle Auditing (until Unified Auditing) and is both extremely robust and also quite complex. As a result, it requires a skill set and experience that many organizations lack – one of the reasons causing the emergence of database security tools. This is made even more complex with all the various Database as a Service offerings since the audit records “go a different way” (e.g., to CloudWatch in Amazon RDS). This complexity at two dimensions is what prompted us at Imperva to provide a comprehensive, yet simple database security solution to cover both on-premise and cloud deployments of Oracle and other databases.
Talk to us to learn more, or if you’re ready to keep reading, continue to Part 2 where we’ll show you how to do Mandatory and Fine-Grained Oracle Auditing.
Try Imperva for Free
Protect your business for 30 days on Imperva.
