Setting default auditing as recommended by Oracle

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles