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 one.
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 are 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 to be 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 that 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 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 Sonar 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 3 where we’ll show you how to conduct Unified Auditing.
Try Imperva for Free
Protect your business for 30 days on Imperva.
