simple oracle auditing concepts – for the new oracle dba





disclaimer:i am not the original author

Oracle.Audit – Introduction to Simple Oracle Auditing

Audit within Oracle is broken into three areas:

  • statement auditing such as CREATE TABLE or CREATE SESSION,
  • privilege auditing such as ALTER USER
  • object level auditing such as SELECT TABLE

The audit trail can be either written to the database or to the operating system. Writing the audit trail to the operating system is, in some senses, more secure but the implementation is not available on all platforms and is platform specific. In this article we will concentrate on using the database to store the audit trail.

Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $­ORACLE_HOME/dbs

audit_trail = db

The database now needs to be restarted. A simple check will show that audit is indeed now turned on.

SQL> select name,value from v$­parameter
2 where name like ‘audit%’;

NAME VALUE
—————————— ——————————
audit_trail DB
audit_file_dest ?/rdbms/audit

SQL>

No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $­ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file. On Windows these events appear in the Event Viewer. To check if any privilege or statement audit actions are enabled, do the following:

SQL> select * from dba_stmt_audit_opts
2 union
3 select * from dba_priv_audit_opts;

no rows selected

SQL>

To find out what objects are being audited, query the view dba_obj_audit_opts.

The Worked Examples

Let us now work through our two example cases and see what can be learned. First, turn on audit for the access attempts to the database:

SQL> audit create session;

Audit succeeded.

SQL>

The above command will capture access by all users by access and whether successful or unsuccessful. The default for this command is by access.

Note: The format of all audit commands from the Oracle documentation is as follows:

audit {statement_option|privilege_option} [by user] [by
{session|access}] [ whenever {successful|unsuccessful}]

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:

SQL> select *
2 from dba_sys_privs
3 where privilege like ‘%AUDIT%’;

GRANTEE PRIVILEGE ADM
—————————— —————————————- —
CTXSYS AUDIT ANY NO
CTXSYS AUDIT SYSTEM NO
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
IMP_FULL_DATABASE AUDIT ANY NO
MDSYS AUDIT ANY YES
MDSYS AUDIT SYSTEM YES
WKSYS AUDIT ANY NO
WKSYS AUDIT SYSTEM NO

9 rows selected.

SQL>

The above results are for a 9i database, the default users MDSYS, CTXSYS and WKSYS would likely be good targets for attackers, as any audit actions could be turned off as one of these users to hide any actions undertaken.

Now that audit will capture all access attempts, we need to wait for some users to log in to do work. So while they do that, let’s set up the audit to capture alterations to the schema. For the sake of brevity, in this example, not all schema object changes will be captured. Changes to tables, indexes, clusters, views, sequences, procedures, triggers, libraries and many more can be captured. In this example, audit will be enabled on an example set. Turning on the audit can be performed as a two-stage process, generate the audit commands and then run them as follows:

set head off
set feed off
set pages 0
spool aud.lis
select ‘audit ‘||name||’;’
from system_privilege_map
where (name like ‘CREATE%TABLE%’
or name like ‘CREATE%INDEX%’
or name like ‘CREATE%CLUSTER%’
or name like ‘CREATE%SEQUENCE%’
or name like ‘CREATE%PROCEDURE%’
or name like ‘CREATE%TRIGGER%’
or name like ‘CREATE%LIBRARY%’)
union
select ‘audit ‘||name||’;’
from system_privilege_map
where (name like ‘ALTER%TABLE%’
or name like ‘ALTER%INDEX%’
or name like ‘ALTER%CLUSTER%’
or name like ‘ALTER%SEQUENCE%’
or name like ‘ALTER%PROCEDURE%’
or name like ‘ALTER%TRIGGER%’
or name like ‘ALTER%LIBRARY%’)
union
select ‘audit ‘||name||’;’
from system_privilege_map
where (name like ‘DROP%TABLE%’
or name like ‘DROP%INDEX%’
or name like ‘DROP%CLUSTER%’
or name like ‘DROP%SEQUENCE%’
or name like ‘DROP%PROCEDURE%’
or name like ‘DROP%TRIGGER%’
or name like ‘DROP%LIBRARY%’)
union
select ‘audit ‘||name||’;’
from system_privilege_map
where (name like ‘EXECUTE%INDEX%’
or name like ‘EXECUTE%PROCEDURE%’
or name like ‘EXECUTE%LIBRARY%’)
/
spool off
@@aud.lis

This will generate a set of audit commands that can be captured to a spool file, which is then run to enable the audit commands.

Another solution would be to audit the actual permissions granted to users by generating the audit commands from the database view dba_sys_privs. While this may seem to be a better solution and potentially involve less audit commands, it would not allow for the case when new permissions are granted to users. In this case, audit would also need to be enabled at the time the privileges are granted.

Now that all of the sample audit is now enabled, the settings can be viewed with this SQL:

1 select audit_option,success,failure
2 from dba_stmt_audit_opts
3 union
4 select privilege,success,failure
5* from dba_priv_audit_opts
SQL> /

AUDIT_OPTION SUCCESS FAILURE
—————————————- ———- ———-
ALTER ANY CLUSTER BY ACCESS BY ACCESS
ALTER ANY INDEX BY ACCESS BY ACCESS
ALTER ANY INDEXTYPE BY ACCESS BY ACCESS
ALTER ANY LIBRARY BY ACCESS BY ACCESS
?
EXECUTE ANY LIBRARY BY SESSION BY SESSION
EXECUTE ANY PROCEDURE BY SESSION BY SESSION

38 rows selected.

SQL>

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$­. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

The AUD$­ table is rare, as it is the only SYS owned table from which Oracle allows records to be deleted. If the audit trail is turned on and written to the database, then the numbers of records in this table need to be monitored carefully to ensure it doesn’t grow too fast and fill the system tablespace. A purging strategy needs to be adopted to keep the size of the table in check and, if needed, to archive off audit trail records for future reference. One tactic could be to copy the records to summary tables that allow specific checks for abuse to be performed offline. These summary tables can be in a separate database for added security. Once copied, sys.aud$­ can be truncated.

SYS.AUD$­ can be moved to a different tablespace other than SYSTEM but check with Oracle support first, as this action is no longer supported.

Only users who have been granted specific access to SYS.AUD$­ can access the table to read, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$­ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Back to the examples, our users have been logging on and working throughout the day and created some audit records. These audit records can be viewed in a number of ways:

  • By selecting from SYS.AUD$­ – This is the raw audit trail
  • By selecting from dba_audit_trail – This is a DBA view showing the raw audit trail.
  • By selecting from dba_audit_session – This view shows just log-on and log-off actions.

A simple piece of SQL can show details of the connection attempts:

SQL> get check_create_session
1 —
2 — check_create_session.sql
3 —
4 col username for a15
5 col terminal for a6
6 col timestamp for a15
7 col logoff_time for a15
8 col action_name for a8
9 col returncode for 9999
10 select username,
11 terminal,
12 action_name,
13 to_char(timestamp,’DDMMYYYY:HHMISS’) timestamp,
14 to_char(logoff_time,’DDMMYYYY:HHMISS’) logoff_time,
15 returncode
16* from dba_audit_session
SQL> /
USERNAME TERMIN ACTION_N TIMESTAMP LOGOFF_TIME RETURNCODE
————— —— ——– ————— ————— ———-
SYS pts/1 LOGOFF 09042003:051046 09042003:051641 0
ZULIA pts/1 LOGON 09042003:051641 1017
SYS pts/1 LOGOFF 09042003:051649 09042003:053032 0
SYS pts/2 LOGOFF 09042003:052622 09042003:053408 0
ZULIA pts/1 LOGON 09042003:053032 1017

There are a number of simple abuses that can be checked for in the area of user access to the database. As examples for this paper we will look at the following:

  • Failed log-on attempts

This can indicate fat fingers or attackers’ attempts to gain unauthorized access the database. The following SQL highlights this:

SQL> select count(*),username,terminal,to_char(timestamp,’DD-MON-YYYY’)
2 from dba_audit_session
3 where returncode<>0
4 group by username,terminal,to_char(timestamp,’DD-MON-YYYY’);

COUNT(*) USERNAME TERMIN TO_CHAR(TIM
———- ————— —— ———–
1 BILL pts/3 09-APR-2003
3 FRED pts/3 09-APR-2003
4 ZULIA pts/1 09-APR-2003

SQL>

This shows two possible abuses, the first is the user Zulia attempting to log on and failing four times on the same day. This could be a forgotten password or it could be someone trying to guess his or her password. A change to the SQL as follows gives a bit more detail:

SQL> select count(*),username,terminal,to_char(timestamp,’DD-MON-YYYY’),returncode
2 from dba_audit_session
3 group by username,terminal,to_char(timestamp,’DD-MON-YYYY’),returncode;

COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE
———- ————— —— ———– ———-
1 BILL pts/3 09-APR-2003 1017
1 EMIL pts/1 09-APR-2003 0
1 EMIL pts/2 09-APR-2003 0
1 EMIL pts/3 09-APR-2003 0
1 EMIL pts/4 09-APR-2003 0
3 FRED pts/3 09-APR-2003 1017
3 SYS pts/1 09-APR-2003 0
1 SYS pts/2 09-APR-2003 0
1 SYSTEM pts/5 09-APR-2003 0
4 ZULIA pts/1 09-APR-2003 1017
1 ZULIA pts/1 09-APR-2003 0

11 rows selected.

SQL>

This reveals that the user successfully logged on on the same terminal on the same day. A number of failed log-ons should be agreed as part of these checks and the above SQL run every day. Those users with failure numbers above the threshold should be investigated.

  • Attempts to access the database with non-existent users

One interesting extension to the above SQL is to find attempts to log in where the user doesn’t exist. An audit record is still created in this case. The following SQL illustrates:

SQL> select username,terminal,to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’)
2 from dba_audit_session
3 where returncode<>0
4 and not exists (select ‘x’
5 from dba_users
6 where dba_users.username=dba_audit_session.username)
SQL> /

USERNAME TERMIN TO_CHAR(TIMESTAMP,’D
————— —— ——————–
FRED pts/3 09-APR-2003 17:31:47
FRED pts/3 09-APR-2003 17:32:02
FRED pts/3 09-APR-2003 17:32:15
BILL pts/3 09-APR-2003 17:33:01

SQL>

This is probably abuse. All attempts to log on with a user that doesn’t exist should be checked each day and investigated.

  • Attempts to access the database at unusual hours

Checks should be made for any attempts to access the database outside of working hours. These accesses could be genuine overtime work or maintenance but they could just as easily be unauthorized access attempts and should be checked as follows:

SQL> select username,
2 terminal,
3 action_name,
4 returncode,
5 to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’),
6 to_char(logoff_time,’DD-MON-YYYY HH24:MI:SS’)
7 from dba_audit_session
8 where to_date(to_char(timestamp,’HH24:MI:SS’),’HH24:MI:SS’) <
to_date(’08:00:00′,’HH24:MI:SS’)
9 or to_date(to_char(timestamp,’HH24:MI:SS’),’HH24:MI:SS’) >
to_date(’19:30:00′,’HH24:MI:SS’)
SQL> /

USERNAME TERMIN ACTION_N RETURNCODE TO_CHAR(TIMESTAMP,’D TO_CHAR(LOGOFF_TIME,
———- —— ——– ———- ——————– ——————–
SYS pts/1 LOGOFF 0 09-APR-2003 20:10:46 09-APR-2003 20:16:41
SYSTEM pts/5 LOGOFF 0 09-APR-2003 21:49:20 09-APR-2003 21:49:50
ZULIA pts/5 LOGON 0 09-APR-2003 21:49:50
EMIL APOLLO LOGON 0 09-APR-2003 22:49:12

SQL>

The above SQL shows any connections before 8:00 AM and after 7:30 PM. Any connections, particularly those made by privileged users such as SYS and SYSTEM, should be investigated. Particular attention can be made to the location from which the access was made. For instance, if privileged access is made from machines that are not in the administrator department, the administrator needs to find out why.

  • Check for users sharing database accounts

The following SQL looks for users who are potentially sharing database accounts:

SQL> select count(distinct(terminal)),username
2 from dba_audit_session
3 having count(distinct(terminal))>1
4 group by username
SQL> /

COUNT(DISTINCT(TERMINAL)) USERNAME
————————- ———-
4 EMIL
3 SYS
3 ZULIA
SQL>

This shows that three users have accessed their accounts from more than one location. A further check could be to add a time component to see if they are accessed simultaneously and also to restrict the check per day. The above SQL gives some idea of the potential without complicating it too much. Again, these accounts and users should be investigated.

  • Multiple access attempts for different users from the same terminal

The final example checks to find where multiple database accounts have been used from the same terminal. The SQL is again simple and could be extended to group by day and also to print out the users per terminal. This is a simple test to illustrate the abuse idea:

SQL> select count(distinct(username)),terminal
2 from dba_audit_session
3 having count(distinct(username))>1
4 group by terminal
SQL> /

COUNT(DISTINCT(USERNAME)) TERMIN
————————- ——
3 pts/1
2 pts/2
3 pts/3
3 pts/5

SQL>

This could indicate someone trying to gain access by trying many accounts and passwords, or it could indicate legitimate users sharing accounts for certain aspects of their work. In either case, the admin should investigate further.

There are, of course, many other scenarios that could indicate possible abuses. Checking for those is as simple as the cases depicted above. It will be left to the reader to experiment. Let me know what you find useful.

The second example case that audit actions were set for is to detect changes made to the database schema. This could include new objects being added or attempts to change existing objects within the database.

A simple piece of SQL will show any audit trail items that relate to objects being created or changed as follows:

col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,’DD-MON-YYYY HH24:MI’) timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>’CREATE SESSION’
/
SQL> @check_obj.sql

ZULIA CREATE TABLE STEAL_SALARY 09-APR-2003 20:07 0
PETE CREATE PROCEDURE HACK 09-APR-2003 20:42 0

This simple example shows that the user ZULIA has created a table and the user PETE has been writing PL/SQL procedures. Any changes such as this that are found should be investigated in a production database. Many more specific abuses can be checked for in relation to object and schema changes but, in general, no user should be able to alter the database schema in a production database. As a result, the check can remain pretty simple.

Protecting the Database Against These Abuses

The two examples given are just two of many possible scenarios that could be detected using Oracle’s auditing facilities. Turning on and managing audit is one of the first steps to securing the database. Using audit should be part of an overall organization security plan and policy that includes Oracle. The database should be audited regularly for misconfiguration or known vulnerabilities that could allow security breaches to take place.

Because of its complex nature and vast number of different ways it can be used and configured, the best approach to securing Oracle will always be to follow the principle of least privilege. Once the database is part of the overall security plan and is configured correctly and checked regularly, then auditing it should be considered an important part of the strategy.

In general, do not grant any privileges to general users in a production database, remove most of the PUBLIC privileges and delete or lock and change the passwords of any default accounts. Ensure that users obey password policies and that the password management features of Oracle are employed.

It is important that the audit actions are planned from a performance and usability point of view and that the audit trail is managed. It is also important that the audit trail data is understood in terms of detecting abuse.

The author’s recent book by the SANS Institute "Oracle security step- by-step – A survival guide for Oracle security" gives excellent guidelines on how to configure Oracle securely.

Conclusions

Oracle’s auditing features are very powerful and sometimes seem very complex. As we saw in the introduction, there is more than one option available for auditing an Oracle database. It is possible to audit almost everything in the Oracle RDBMS with the standard features but not at the row level. If a high-level audit is needed, use the standard features to get a view of overall activity and then home in on the area of concern in more detail.

Because it is possible to audit almost any type of action in an Oracle database using the standard audit features, the reader should experiment with the most useful audit actions for their organization. Keep it simple and do not try to use everything. Above all, predetermine what data will be generated in the audit trail and the abuses that can be checked for. Write reports to check the audit trail and purge it regularly. Finally, monitor the reports each day and take the appropriate action.

For more detailed auditing, use database triggers and fine grained auditing. Keep in mind that both of these methods need programming skills to implement and report on, so they should be considered carefully. A lot of useful information can be gathered without resorting to row-level audit. Above all, employ least privilege principle to avoid any users making changes or reading data that they should not.

Author: admin