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:

#> $ORACLE_HOME/bin/dbca

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