Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 7, 2010

Setting default auditing as recommended by Oracle

By James Koopmann

If 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

the Welcome screen

3.  From the Operations window select Configure Database Options and click Next

the Operations window

4.  From the database Window, select the current database instance to configure (db11) and click Next

the database Window

5.  From the Database Content window click Next

the Database Content window

6.  From the Security Settings window, select Keep the enhanced 11g default security settings (recommended) and click Next

the Security Settings window

7.  From the connection Mode window, select the mode for the database and then click Finish

the connection Mode window

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.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM