AboutIf you don’t know where to start when setting Oracle auditing parameters, just start with what Oracle recommends. In this article, we set the default auditing as recommended by Oracle.
Most of us probably give no thought to the auditing being done within an Oracle database. If we just fly through database creation, we may not ever know that auditing is actually activated. On the other hand, if we have an upgraded database or don’t know what parameters have been set, but want to just deploy what Oracle recommends, then this article is a good place to start. Within the 2-Day+ Security Guide, Oracle shows us, through the database creation assistant (DBCA) how to set up and use default auditing for SQL and privileges. Now while I decided to step through the process here, as outlined in the 2-Day+ Security Guide, you may or may not notice that what I found was that there are a couple of misplaced steps but nothing major. So, the steps would be as follows to enable default auditing:
1. Start Database Configuration Assistant:
#> $ORACLE_HOME/bin/dbca
2. From the Welcome screen click Next
3. From the Operations window select Configure Database Options and click Next
4. From the database Window, select the current database instance to configure (db11) and click Next
5. From the Database Content window click Next
6. From the Security Settings window, select Keep the enhanced 11g default security settings (recommended) and click Next
7. From the connection Mode window, select the mode for the database and then click Finish
When setting default security settings as recommended by Oracle, Oracle will now audit some of the security-relevant SQL statements and privileges and set the AUDIT_TRAIL parameter to DB. In addition, Oracle will audit the AUDIT ROLE SQL statement by default with the following privileges being audited:
ALTER ANY PROCEDURE CREATE ANY LIBRARY DROP ANY TABLE ALTER ANY TABLE CREATE ANY PROCEDURE DROP PROFILE ALTER DATABASE CREATE ANY TABLE DROP USER ALTER PROFILE CREATE EXTERNAL JOB EXEMPT ACCESS POLICY ALTER SYSTEM CREATE PUBLIC DB LINK GRANT ANY OBJECT PRIVILEGE ALTER USER CREATE SESSION GRANT ANY PRIVILEGE AUDIT SYSTEM CREATE USER GRANT ANY ROLE CREATE ANY JOB DROP ANY PROCEDURE Statements with BY ACCESS clause
Auditing is now available for the auditing of both DDL and DML statements. DDL statements such as CREATE, DROP, ALTER, etc. can now be audited by enabling the auditing of a specific table through the AUDIT TABLE command. Likewise, DML statements such as INSERT, SELECT, DELETE, etc. can be captured either broadly (for all users) or narrowly (a specific set of users). So, as a very simplistic example, if I audited the sys.t1 table like the following I would capture audit records:
SQL> connect / as sysdba
Connected.
SQL> AUDIT SELECT ON sys.t1 BY ACCESS;
Audit succeeded.
SQL> connect scott/tiger
Connected.
SQL> select count(*) from sys.t1;
COUNT(*)
----------
0
SQL> connect / as sysdba
Connected.
SQL> select username,obj_name,action_name
from dba_audit_trail where username = 'SCOTT';
USERNAME OBJ_NAME ACTION_NAME
--------------- -------- -----------
SCOTT LOGON
SCOTT T1 SELECT
SCOTT LOGOFF
The above was an example of auditing for a specific object (table). It is just as effective, if a broad and far-reaching net is needed, to issue auditing on a specific privilege such as the SELECT ANY TABLE statement. As an example, if you wanted to audit the use of the system privilege DELETE ANY TABLE you could issue the following command:
SQL> AUDIT DELETE ANY TABLE;
If you have activity in a multitier environment, you can audit the activities of a client by specifying a proxy in the audit statement—actively auditing actions performed on behalf of a client by a middle-tier application. For instance, you could issue a command such as the following for a SELECT statement issued by client myclient by the proxy appserve:
SQL> AUDIT SELECT TABLE BY myclient ON BEHALF OF appserve;
Likewise, network activity can also be audited through the use of the following command:
SQL> AUDIT NETWORK;
While Oracle strongly recommends enabling auditing its effectiveness is only as good, or bad, as the database traffic being sent throughout the enterprise. If the database traffic is simplistic then a default security auditing such as defined by Oracle may be good enough. However, if you have multiple applications, maybe dozens of DBAs and developers accessing the system, or even off-shore consultants accessing the databases then auditing can get a bit sticky and confusing. Knowing what is in place, what it is able to capture, and being intelligent about altering the auditing process is key to compliancy—remembering all along that auditing can create a performance problem within the database. What seems like a simple task (starting to audit) is really laden with many questions. You may say you're auditing your database but proving its effectiveness is a whole other story. Stay tuned as this series of the 2-Day Security Guide comes to a close with the next article and will then venture off into the Security Guide and the Advanced Security Administrators Guide for more answers, configurations, and real world examples.