This is the second 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 and Standard Auditing was covered in Part 1.

Mandatory Auditing

In all initiatives you will be required to audit privileged user activity – and who is more privileged than SYS or other users with SYSDBA privileges? You can enable standard auditing and you will still not get any audit records related to SYS activity. On the flip side, you can disable all auditing using NOAUDIT, but if you’re auditing administrators then all SYS activities will still be logged. Also, irrespective of where you choose to store the standard audit trail (DB or OS files) and what format you choose (e.g. XML), SYS auditing is always generated in files using a fixed format or sent to operating system log daemons such as syslog on Unix and the event log on Windows.

Some activities are always audited – even if you don’t explicitly ask for them. These activities include all database startups, all database shutdowns and all sysdba or sysoper logins. This is called mandatory auditing. On Unix these audit records go into the adump directory (by default) and on Windows these audit records are written to the Windows event log regardless of what you set the AUDIT_TRAIL parameter to.

Another set of activities that is always recorded regardless of your configuration is all the changes to the audit trail itself. Any DML statements performed on SYS.AUD$ and SYS.FGA_LOG$ (see the next chapter) are logged to SYS.AUD$.

Syslog Auditing

Syslog is a standard for forwarding log messages in an IP network. The term “syslog” is often used for both the actual syslog protocol, as well as for the application or library sending syslog messages. Syslog is often used for system management and security auditing. It is supported by a wide variety of devices and receivers across multiple platforms and is therefore often used to integrate log data from many different types of systems into a central repository.

On Unix you can send administrator audit records (as well as standard audit records) to syslog. This is the Unix equivalent to the Windows event log. The advantage of using syslog is that the adump files can be modified by the instance account owner whereas syslog files are usually owned by root. Moreover, syslog is often used by Security Event Managers (SEMs) and other log aggregators and correlation engines.

The syslog protocol is a client/server protocol. The syslog client sends a small textual message (less than 1024 bytes) to the syslog server. The receiver is commonly called “syslogd”, “syslog daemon” or “syslog server”. Syslog messages can be sent via UDP and/or TCP. The data is sent in cleartext, but wrappers such as Stunnel, sslio or sslwrap can be used to encrypt syslog messages. When you configure Oracle to use syslog, Oracle calls a syslog library to write its audit records. From a syslog perspective Oracle is acting as a client and syslogd is acting as the server.

Before you configure syslog auditing you should know what you have configured in /etc/syslog.conf. Syslog.conf controls how the syslog daemon logs events. Every syslog message has two attributes – a facility and a priority.  Syslogd.conf defines what to do with messages per facility and priority. A sample config file is shown below:

# cat /etc/syslog.conf

# Log all kernel messages to the console.

# Logging much else clutters up the screen.

kern.*                                                 /dev/console




# Log anything (except mail) of level info or higher.

# Don't log private authentication messages!

*.info;mail.none;authpriv.none;cron.none                /var/log/messages




# The authpriv file has restricted access.

authpriv.*                                              /var/log/secure




# Log all the mail messages in one place.

mail.*                                                  -/var/log/maillog





# Log cron stuff

cron.*                                                  /var/log/cron




# Everybody gets emergency messages

*.emerg                                                 *




# Save news errors of level crit and higher in a special file.

uucp,news.crit                                          /var/log/spooler




# Save boot messages also to boot.log

local7.*                                                /var/log/boot.log

Each line in this file starts with a selector. A selector specifies a combination of a facility and a priority and then specifies where the messages are to be written. In this example, each line specifies a file, but syslog can also redirect messages to remote syslog servers. For example, the first line specifies that all messages sent to the KERN facility are logged to the console no matter what their priority. The next line specifies that all messages sent to any facility with the info priority, and that all messages sent to the mail, cron or authpriv facilities with no priority, are written to /var/log/messages. Priorities (also called severities) include EMERG, ALERT, CRIT, ERR, WARNING, NOTICE, INFO and DEBUG. Facilities include AUTH, AUTHPRIV, CRON, DAEMON, FTP, KERN, LPR, MAIL, MARK, NEWS, SECURITY, SYSLOG, USER, UUCP and LOCAL0 – LOCAL7. To configure syslog auditing for Oracle you simply specify which facility and which priority Oracle should use for its messages.

Audit records in Oracle are written to syslog in two cases. For standard auditing, you can set AUDIT_TRAIL to OS and then set AUDIT_SYSLOG_LEVEL to a value specifying a facility and a priority. For administrator auditing, (AUDIT_SYS_OPERATIONS = TRUE) just set the AUDIT_SYSLOG_LEVEL. For example, if your syslog.conf has a line of the form:

*.info;mail.none        /var/adm/syslog/syslog.log

Logon to the database, set the two initialization parameters and restart the database:

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 17:55:24 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set audit_sys_operations=true scope=spfile;

System altered.

SQL> alter system set audit_syslog_level='local1.info' scope=spfile;

System altered.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size                  2124664 bytes

Variable Size             406848648 bytes

Database Buffers          109051904 bytes

Redo Buffers                3911680 bytes

Database mounted.

Database opened.

If you made a mistake in the facility or priority you get the following error:

SQL> startup

ORA-32028: Syslog facility or level not recognized

At this point, all SYS activity will be recorded by syslog, e.g.:

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 18:41:12 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> delete from aud$;

3460 rows deleted.

In the syslog file you get an audit record of the form:

Apr  4 09:44:59 vireo Oracle Audit: SESSIONID: " 6784" 

ENTRYID: "1" STATEMENT: "6" USERID: "SYS" USERHOST: "vireo" 

TERMINAL: "pts/2" ACTION: "7" RETURNCODE: "0" OBJ$CREATOR: "SYS" OBJ$NAME: "AUD$" SES$TID: "4564" OS$USERID: "oracle11"


Fine Grained Auditing

 

Fine-grained auditing (FGA) was introduced as a granular audit option for SELECT statements in Oracle 9i. It was then enhanced in 10g to also include DML. FGA was added to the Oracle database feature set, not as a replacement for standard auditing, but as a way to address audit requirements that explicitly specify conditions for which an audit record needs to be created. Such requirements can sometimes be articulated based on data within certain columns or simply by which columns are being accessed. Using standard auditing you can specify what you want audited based on commands and objects. It’s an “all or nothing” specification – you can’t specify that you only want audit records if the user accessed certain rows or certain columns. FGA augments this capability by allowing you to specify granular conditions which determine whether or not an audit record should be written to the audit trail.

FGA is delivered through the DBMS_FGA package, and setting up FGA policies requires EXECUTE privileges on this package. The audit records are written to a different table than standard auditing or to operating system files. When written to a database table, they are written to SYS.FGA_LOG$. You can then use the DBA_FGA_AUDIT_TRAIL view to look at the audit records.

One of the nice things with FGA is that you don’t have a two-step initiation process. You don’t need to set an initialization parameter, and you don’t need to restart the database – you just have to define policies.

Let’s look at a simple example of the mechanics involved. In this example, you won’t add a granular audit specification yet, so this type of auditing can be accomplished even with standard auditing. You’ll just define an audit trail on the SCOTT.EMP table – regardless of the access (SELECT or DML).

Define a FGA audit policy:

SQL> begin

  2 dbms_fga.add_policy(

  3 object_schema=>'SCOTT',

  4 object_name=>'EMP',

  5 policy_name=>'EMP_ACCESS'

  6 );

  7 end;

  8 /

 

PL/SQL procedure successfully completed.

This tells Oracle to audit the SCOTT.EMP table. Since there is no condition, Oracle will audit all DML and SELECT access. Connect to the database and select from the table:

SQL> connect scott

Enter password: *****

Connected.

SQL> select * from emp where deptno=10;

    EMPNO ENAME      JOB MGR HIREDATE         SAL COMM DEPTNO

———- ———- ——— ———- ——— ———- ———- ———-

     7782 CLARK      MANAGER 7839 09-JUN-81       2450 10

     7839 KING       PRESIDENT     17-NOV-81 5000                   10

     7934 MILLER     CLERK 7782 23-JAN-82       1300 10

3 rows selected.

Now look at the audit record that is produced:

DB_USER     OS_USER     POLICY_NAME TIMESTAMP              SQL_TEXT 

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

 SCOTT       L3AD029\RBNatan  EMP_ACCESS 9/19/2007 12:08:06 AM  select * from emp where deptno=10 

FGA only audits DML and SELECT. If you were to alter the table, or do any other DDL statement, it would not appear in the FGA trail. SELECTs are audited even if access is done through views, for example:

SQL> create view v_emp as select * from emp;

View created.




SQL> select * from v_emp where deptno=20;

     EMPNO ENAME      JOB MGR HIREDATE         SAL COMM DEPTNO

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

      7566 JONES      MANAGER 7839 02-APR-81    3599.75 20

      7788 SCOTT      ANALYST 7566 19-APR-87       3630 20

      7876 ADAMS      CLERK 7788 23-MAY-87       1331 20

      7902 FORD       ANALYST 7566 03-DEC-81       3630 20

      4444 RON        DEV 7902 01-NOV-02     1161.6 100 20

5 rows selected.


select db_user,os_user,policy_name,timestamp,object_name,sql_text from dba_fga_audit_trail where policy_name='EMP_ACCESS‘

DB_USER     OS_USER     POLICY_NAME TIMESTAMP              OBJECT_NAME SQL_TEXT             

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

 SCOTT       L3AD029\RBNatan  EMP_ACCESS 9/19/2007 12:08:06 AM  EMP select * from emp where deptno=10   

 SCOTT       L3AD029\RBNatan  EMP_ACCESS 9/19/2007 12:15:46 AM  EMP             select * from v_emp where deptno=20 

FGA knows that the object accessed was EMP, so the value in OBJECT_NAME is EMP, but the SQL is recorded correctly as the access to the view. Of course, this is a very simplistic policy – the real value of FGA is in the granular policies you will learn about in the next HOWTO.

FGA policies

You create FGA policies using the DBMS_FGA.ADD_POLICY procedure.

For example, if only the SAL and the COMM columns are sensitive in the EMP table, and you want to audit any changes or reads of salary or commission data, execute:

SQL> begin

  2 dbms_fga.add_policy(

  3 object_schema=>'SCOTT',

  4 object_name=>'EMP',

  5 policy_name=>'EMP_ACCESS',

  6 audit_column=>'SAL,COMM');

  7 end;

  8 /




PL/SQL procedure successfully completed.

When you have more than one sensitive column you can control whether you want to audit access to any of the sensitive columns or if you only want to audit access that involves all of these columns. The default is to audit any access that involves any of these columns. To explicitly set the behavior use one of:

SQL> begin

  2 dbms_fga.add_policy(

  3 object_schema=>'SCOTT',

  4 object_name=>'EMP',

  5 policy_name=>'EMP_ACCESS',

  6 audit_column=>'SAL,COMM',

  7 audit_column_opts=>DBMS_FGA.ANY_COLUMNS);

  8 end;

9  /




PL/SQL procedure successfully completed.

Or

SQL> begin

 2 dbms_fga.add_policy(

 3 object_schema=>’SCOTT’,

 4 object_name=>’EMP’,

 5 policy_name=>’EMP_ACCESS’,

 6 audit_column=>’SAL,COMM’,

 7 audit_column_opts=>DBMS_FGA.ALL_COLUMNS);

 8 end;

 9 /

PL/SQL procedure successfully completed.

You can control which statements this policy applies to. For example, if you want to specify that only inserts and updates are audited you can set:

begin

dbms_fga.add_policy(

object_schema=>'SCOTT',

object_name=>'EMP',

policy_name=>'EMP_ACCESS',

audit_column=>'SAL,COMM',

statement_types=>'INSERT, UPDATE',

audit_column_opts=>DBMS_FGA.ANY_COLUMNS);

end;

/

Contrary to standard auditing where you set the location of the audit trail up-front, you specify the location of the audit trail when you define the policy. Use the AUDIT_TRAIL parameter to control where the audit records are written. For example, if you want the audit records for this policy to be written to FGA_LOG$ and you want the audit trail to include the SQL text and the bind values, use:

begin

dbms_fga.add_policy(

object_schema=>’SCOTT’,

object_name=>’EMP’,

policy_name=>’EMP_ACCESS’,

audit_column=>’SAL,COMM’,

statement_types=>’INSERT, UPDATE’,

audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED);

end;

/

If you want the audit records to be written to XML files and to include the SQL text and bind values:

begin

dbms_fga.add_policy(

object_schema=>'SCOTT',

object_name=>'EMP',

policy_name=>'EMP_ACCESS',

audit_column=>'SAL,COMM',

statement_types=>'INSERT, UPDATE',

audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

audit_trail=>DBMS_FGA.XML + DBMS_FGA.EXTENDED);

end;

/

FGA Conditions

You’ve already seen some qualifiers that give you more granular control over what gets audited using FGA – for example, at a column level. The most powerful qualifier that allows you to fine-tune the auditing policy at a row-level is the audit_condition qualifier. This is especially powerful for SELECT statements – and this is what FGA was born to do (back in 9i). For example, if you want to audit all SELECT statements that access data involving highly paid individuals:

begin

dbms_fga.add_policy(

object_schema=>'SCOTT',

object_name=>'EMP',

policy_name=>'EMP_ACCESS',

audit_column=>'SAL',

audit_condition=>'SAL>9999',

statement_types=>'SELECT',

audit_column_opts=>DBMS_FGA.ANY_COLUMNS,

audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED);

end;

/

If the table contains this data:

      7369 SMITH      MANAGER 7902 17-DEC-80      11000 1500 20

      7499 ALLEN      SALESMAN 7698 20-FEB-81       1600 300 30

      7521 WARD       SALESMAN 7698 22-FEB-81       1250 500 30

      7566 JONES      MANAGER 7839 02-APR-81       2975 20

      7654 MARTIN     SALESMAN 7698 28-SEP-81       1250 1400 30

      7698 BLAKE      MANAGER 7839 01-MAY-81       2850 30

      7782 CLARK      MANAGER 7839 09-JUN-81       2450 10

      7788 SCOTT      ANALYST 7566 19-APR-87       3000 20

      7839 KING       PRESIDENT     17-NOV-81 5000                   10

      7844 TURNER     SALESMAN 7698 08-SEP-81       1500 0 30

      7876 ADAMS      CLERK 7788 23-MAY-87       1100 20

And you perform the following two SELECT statements:

SQL> select * from emp where EMPNO=7369;




     EMPNO ENAME      JOB MGR HIREDATE         SAL COMM DEPTNO

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

      7369 SMITH      MANAGER 7902 17-DEC-80      11000 1500 20




SQL> select * from emp where EMPNO=7499;




     EMPNO ENAME      JOB MGR HIREDATE         SAL COMM DEPTNO

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

      7499 ALLEN      SALESMAN 7698 20-FEB-81       1600 300 30

You only get one audit record:

SQL> select SQL_TEXT from dba_fga_audit_trail;




SQL_TEXT

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

select * from emp where EMPNO=7369

FGA is very flexible; the audit condition is a PL/SQL expression which allows you to implement pretty much any audit requirement on DML and SELECT at a row level. A NULL as the audit_condition is interpreted as a null condition and will match every row. Do not use a condition such as 1=1 and do not use an empty string as a condition.

Summary

While standard auditing is the workhorse of Oracle Auditing (until Unified Auditing), mandatory auditing is always used, and fine-grained auditing is used for specific needs pertaining to data access and modification.  All three are robust but also quite complex. As a result, it requires a skill set and experience that many organizations lack – one of the reasons for the emergence of Database Security tooling. This is made even more complex with 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 jSonar to provide a coherent and simple database security solution, all delivered as SaaS, to cover both on-prem and cloud deployments of Oracle and other databases.