Introduction
This article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It will also use a couple of good example cases to illustrate how useful Oracle audit can be to an organization.
The Issues
There are a number of basic issues that should be considered when contemplating using Oracle's auditing features. These are as follows:
* Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
* When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
* How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
* What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
* Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
* System triggers
These were introduced with Oracle 8 and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
* Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
* Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
* System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
* Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
* Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
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.
Showing posts with label Auditing. Show all posts
Showing posts with label Auditing. Show all posts
Wednesday, January 9, 2008
Wednesday, October 10, 2007
Connect /as sysdba Fails With ORA-09925 for non Software Owners Which Are dba Group Members when Instance Is Down
Subject: Connect /as sysdba Fails With ORA-09925 for non Software Owners Which Are dba Group Members when Instance Is Down
Doc ID: Note:392643.1 Type: PROBLEM
Last Revision Date: 08-MAY-2007 Status: PUBLISHED
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2 to 10.2.0.3
This problem can occur on any platform.
Symptoms
When a user belonging to the dba group but which is not the actual software owner tries to connect to the database as sysdba when the db is stopped, the following message is raised :
ERROR:
ORA-09925: Unable to create audit trail file
SVR4 Error: 13: Permission denied
Additional information: 9925
Permissions on the software binaries are set as required, as checked in $ORACLE_HOME/bin:
$ ls -al oracle*
-rwsr-s--x 1 ora10g dba 118487160 Sep 1 2005 oracle
-rwxr-xr-x 1 ora10g dba 0 Aug 13 2005 oracleO
Cause
The permissions on the adump directory were not set to accept file creation from other users than the software owner. In 10gR2, the adump directory is the new default directory for audit files (instead of $ORACLE_HOME/rdbms/audit). As such, access to this directory is needed in order to connect / as sysdba because these connections are audited by default.
Solution
1. check if the
$ORACLE_BASE/admin//adump
directory exists and create it if not
2. change permissions on the directory to 775.
Comments: If you consider this to introduce a security risk then please only stop / start the database as the oracle software owner. Also note that during db startup naturally the audit_dump_dest parameter is not yet initialized so it can only write this specific audit files to the default destination. Also, writing these files is a mandatory requirement for NCSC C2 security evaluation criteria and therefore it cannot be turned off.
References
Note 103964.1 - How to Audit Connect Internal Using Oracle Server
Errors
ORA-9925 "Unable to create audit trail
Keywords
'START~DATABASE' 'AUDIT~TRAIL' 'PERMISSION~DENIED' 'CONNECT~/~AS~SYSDBA'
Doc ID: Note:392643.1 Type: PROBLEM
Last Revision Date: 08-MAY-2007 Status: PUBLISHED
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2 to 10.2.0.3
This problem can occur on any platform.
Symptoms
When a user belonging to the dba group but which is not the actual software owner tries to connect to the database as sysdba when the db is stopped, the following message is raised :
ERROR:
ORA-09925: Unable to create audit trail file
SVR4 Error: 13: Permission denied
Additional information: 9925
Permissions on the software binaries are set as required, as checked in $ORACLE_HOME/bin:
$ ls -al oracle*
-rwsr-s--x 1 ora10g dba 118487160 Sep 1 2005 oracle
-rwxr-xr-x 1 ora10g dba 0 Aug 13 2005 oracleO
Cause
The permissions on the adump directory were not set to accept file creation from other users than the software owner. In 10gR2, the adump directory is the new default directory for audit files (instead of $ORACLE_HOME/rdbms/audit). As such, access to this directory is needed in order to connect / as sysdba because these connections are audited by default.
Solution
1. check if the
$ORACLE_BASE/admin/
directory exists and create it if not
2. change permissions on the directory to 775.
Comments: If you consider this to introduce a security risk then please only stop / start the database as the oracle software owner. Also note that during db startup naturally the audit_dump_dest parameter is not yet initialized so it can only write this specific audit files to the default destination. Also, writing these files is a mandatory requirement for NCSC C2 security evaluation criteria and therefore it cannot be turned off.
References
Note 103964.1 - How to Audit Connect Internal Using Oracle Server
Errors
ORA-9925 "Unable to create audit trail
Keywords
'START~DATABASE' 'AUDIT~TRAIL' 'PERMISSION~DENIED' 'CONNECT~/~AS~SYSDBA'
How to Audit Connect AS SYSDBA Using Oracle Server
Subject: How to Audit Connect AS SYSDBA Using Oracle Server
Doc ID: Note:103964.1 Type: BULLETIN
Last Revision Date: 09-MAY-2007 Status: PUBLISHED
"Checked for relevance on 09-May-2007"
Purpose:
~~~~~~~~
How to monitor audit Connect as Sysdba ? It is not possible to audit SYSDBA
connections in the same way as the other Oracle users.
There are no tables within Oracle to write this audit information.
This bulletin describes how to audit these connections to Oracle as an
administrative user for Unix and Windows systems.
Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer.
The administrative user connections are written to the operating system audit
trail as opposed to the database audit trail (sys.aud$ table).
Unix Systems
------------
On Unix Systems, the administrative user connections are logged to special log files
created by Oracle and stored in $ORACLE_HOME/rdbms/audit directory.
At a minimum, a new file is created for each startup and shutdown event.
A UNIX cron job can be created to delete these files if necessary.
Explanation
-----------
Administrative user connections are not written to database tables as these
connections are needed in order to start and stop the database. The files where
the administrative user connections are written to are stored externally from
the database so they can be accessed when the database is down. For example,
the administrative user connect to startup the database cannot be audited
(written) to a database table so auditing must be done externally to the
database, as opposed to other audited activity (as example: successfull/
unsuccessfull executions of specified SQL statements, auditing privileges or
objects).
The administrative user connections are always audited regardless of the init.ora
parameter audit_trail.
This feature was created in order to comply with the NCSC C2 security
evaluation criteria. NCSC deemed that an "oper" user should not be able to
disable this level of auditing.
Example:
~~~~~~~~
-> Unix systems:
$ ls -al $ORACLE_HOME/rdbms/audit
Result:
total 24
drwxrwxr-x 2 server sdb 4096 Nov 12 06:00 ./
drwxrwxr-x 13 server sdb 2048 Jul 17 14:51 ../
-rw-r----- 1 server sdb 562 Nov 11 06:00 ora_17254.aud
-rw-r----- 1 server sdb 562 Nov 12 06:00 ora_27213.aud
$ cat $ORACLE_HOME/rdbms/audit/ora_27213.aud
Result:
Audit file /ots1/app/oracle/product/8.1.7/rdbms/audit/ora_27213.aud
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
ORACLE_HOME = /ots1/app/oracle/product/8.1.7
System name: SunOS
Node name: nlsu22
Release: 5.6
Version: Generic_105181-25
Machine: sun4u
Instance name: v817
Redo thread mounted by this instance: 0
Oracle process number: 0
27213
Mon Nov 12 06:00:41 2001
ACTION : 'connect internal' OSPRIV : DBA
CLIENT USER: server
CLIENT TERMINAL:
STATUS: SUCCEEDED ( 0 )
-> Windows NT/2000/2003/XP
Go for: Start -> Settings -> Control Panel -> Services (Windows NT)
Start -> Settings -> Control Panel ->
Administrative Tools -> Services -> Event viewer->
Application log (Windows 2000/2003/XP)
Open an Oracle event (shown for Windows 2000):
Information 22-11-2001 9:38:11 Oracle.o817
content:
Audit trail: ACTION:'connect INTERNAL' OSPRIV: OPER CLIENT
USER: SYSTEM CLIENT TERMINAL: SROO-NL STATUS
SUCCEEDED (0).
Note
~~~~
In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:
Note 174340.1 : Audit SYS user Operations
Related Documents:
~~~~~~~~~~~~~~~~~~
Note 50508.1 : ALERT: "CONNECT INTERNAL" Syntax to be DeSupported
Note 41800.1 : Quick Reference to Auditing Information
Note 174340.1 : Audit SYS user Operations
Doc ID: Note:103964.1 Type: BULLETIN
Last Revision Date: 09-MAY-2007 Status: PUBLISHED
"Checked for relevance on 09-May-2007"
Purpose:
~~~~~~~~
How to monitor audit Connect as Sysdba ? It is not possible to audit SYSDBA
connections in the same way as the other Oracle users.
There are no tables within Oracle to write this audit information.
This bulletin describes how to audit these connections to Oracle as an
administrative user for Unix and Windows systems.
Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer.
The administrative user connections are written to the operating system audit
trail as opposed to the database audit trail (sys.aud$ table).
Unix Systems
------------
On Unix Systems, the administrative user connections are logged to special log files
created by Oracle and stored in $ORACLE_HOME/rdbms/audit directory.
At a minimum, a new file is created for each startup and shutdown event.
A UNIX cron job can be created to delete these files if necessary.
Explanation
-----------
Administrative user connections are not written to database tables as these
connections are needed in order to start and stop the database. The files where
the administrative user connections are written to are stored externally from
the database so they can be accessed when the database is down. For example,
the administrative user connect to startup the database cannot be audited
(written) to a database table so auditing must be done externally to the
database, as opposed to other audited activity (as example: successfull/
unsuccessfull executions of specified SQL statements, auditing privileges or
objects).
The administrative user connections are always audited regardless of the init.ora
parameter audit_trail.
This feature was created in order to comply with the NCSC C2 security
evaluation criteria. NCSC deemed that an "oper" user should not be able to
disable this level of auditing.
Example:
~~~~~~~~
-> Unix systems:
$ ls -al $ORACLE_HOME/rdbms/audit
Result:
total 24
drwxrwxr-x 2 server sdb 4096 Nov 12 06:00 ./
drwxrwxr-x 13 server sdb 2048 Jul 17 14:51 ../
-rw-r----- 1 server sdb 562 Nov 11 06:00 ora_17254.aud
-rw-r----- 1 server sdb 562 Nov 12 06:00 ora_27213.aud
$ cat $ORACLE_HOME/rdbms/audit/ora_27213.aud
Result:
Audit file /ots1/app/oracle/product/8.1.7/rdbms/audit/ora_27213.aud
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
ORACLE_HOME = /ots1/app/oracle/product/8.1.7
System name: SunOS
Node name: nlsu22
Release: 5.6
Version: Generic_105181-25
Machine: sun4u
Instance name: v817
Redo thread mounted by this instance: 0
Oracle process number: 0
27213
Mon Nov 12 06:00:41 2001
ACTION : 'connect internal' OSPRIV : DBA
CLIENT USER: server
CLIENT TERMINAL:
STATUS: SUCCEEDED ( 0 )
-> Windows NT/2000/2003/XP
Go for: Start -> Settings -> Control Panel -> Services (Windows NT)
Start -> Settings -> Control Panel ->
Administrative Tools -> Services -> Event viewer->
Application log (Windows 2000/2003/XP)
Open an Oracle event (shown for Windows 2000):
Information 22-11-2001 9:38:11 Oracle.o817
content:
Audit trail: ACTION:'connect INTERNAL' OSPRIV: OPER CLIENT
USER: SYSTEM CLIENT TERMINAL: SROO-NL STATUS
SUCCEEDED (0).
Note
~~~~
In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:
Note 174340.1 : Audit SYS user Operations
Related Documents:
~~~~~~~~~~~~~~~~~~
Note 50508.1 : ALERT: "CONNECT INTERNAL" Syntax to be DeSupported
Note 41800.1 : Quick Reference to Auditing Information
Note 174340.1 : Audit SYS user Operations
Setting up, Interpreting Auditing Using the Windows Event Viewer
Subject: Setting up, Interpreting Auditing Using the Windows Event Viewer
Doc ID: Note:99137.1 Type: BULLETIN
Last Revision Date: 06-JUN-2007 Status: PUBLISHED
"Checked for relevance on 15-May-2007"
Purpose:
~~~~~~~~
This document explains how to setup and interpret Operating System Auditing of
Oracle Database activity by using the Windows Event Viewer.
Some examples are :
'Instance O901 has been terminated',
'All processes in instance v1020 stopped',
'Shutdown normal performed on instance v1020',
'Audit trail:ACTION :'shutdown' ',
'Initializing PGA fro process RECO in instance v1020',
'Audit trail: ACTION : 'CONNECT' DATABASE USER: '/' PRIVILEGE : SYSDBA
CLIENT USER: pipopc\pipo CLIENT TERMINAL: pipopc STATUS: 0 .',
'AUDIT trail: 'startup'AUDIT_TRAIL:os',
'Audit trail: SESSIONID:"471" ENTRYID:"1" STATEMENT:"1" USERID:"SCOTT"
TERMINAL:"PIPOPC" ACTION:"100" RETURNCODE:"0" COMMENT$TEXT:
"Authenticated by DATABASE; Client address:
(ADDRESS=(PROTOCOL=tcp)(HOST=123.123.123.123)(PORT=1084))"
OSUSERID:"Administrator" PRIV$USED:5'
Scope & Application:
~~~~~~~~~~~~~~~~~~~~
Intended audience: NOVICE
A Quick Start in Auditing
Refer to the references mentioned below for further information about the many
auditing possibilities within Oracle. This note just gives the user an impression
and some examples of how to audit and interpret when sending auditing information
to the Windows Event Log.
SETTING UP AND INTERPRETING AUDITING USING THE WINDOWS EVENT VIEWER
-------------------------------------------------------------------
1. Setting up auditing.
This section explains how to prepare your database for auditing:
Step 1
------
Verify the Event Log Service is enabled on the machine where your Oracle
database resides:
Start -> Settings -> Control Panel -> Services (windows NT)
Start -> Settings -> Control Panel -> Administrative Tools
-> Services (Windows 2000/2003/XP)
Note on XP/2003 the Event Log Service cannot be stopped.
Service Status Startup
------- ------ -------
EventLog Started Automatic
Step 2
------
Add the following line to your INIT.ORA (instance parameter file located by
default under %ORACLE_HOME%\database) to activate auditing:
AUDIT_TRAIL = OS
or in case an spfile is in use issue:
SQL> connect / as sysdba
Connected.
SQL> alter system set audit_trail=os scope=spfile;
AUDIT_TRAIL enables or disables the writing of records to the audit trail.
Audited records are not written if the value is NONE or if the parameter
is not present. The OS option enables system-wide auditing and causes
audited records to be written to the operating system's audit trail.
Some auditing (amongst others: administrative user connections)
are ALWAYS RECORDED.
Note that on Windows you cannot and need not set related parameter audit_file_dest as the
records go top the Event Log instead of the filesystem on this platform (Remark 1).
The DB option enables system-wide auditing and causes audited records to
be written to the database audit trail (the SYS.AUD$ table). If set to
DB most audit records will go to table AUD$, except for some mandatory records
that will always go to the Event Log (Remark 2). The values TRUE and FALSE are also
supported for backwards compatibility. TRUE is equivalent to DB, and FALSE is
equivalent to NONE.
The SQL AUDIT statements can subsequently be used to audit specific Statement,
Privileges or Object auditing events.
Remark 1: Audit information cannot be spooled to a file on Windows. The
AUDIT_FILE_DEST parameter is NOT supported on the Windows platform
since auditing information is sent to the Event Log and can be
viewed with the Event Viewer. Therefore, this parameter should
not be added to the "INIT.ORA" (or spfile). When you add this
parameter and then bounce the database for the parameter to take
effect, you will get:
LRM-00101: UNKNOWN PARAMETER NAME 'AUDIT_FILE_DEST'
ORA-01078: FAILURE IN PROCESSING SYSTEM PARAMETERS
Remark 2: Some auditing (amongst others: administrative user connections)
are ALWAYS RECORDED independent whether init.ora parameter
audit_trail=os is set or not and they will go to the Event Log.
Remark 3: Setting AUDIT_TRAIL to OS , depending on the amount of audited events
can cause significantly more records to be written to the Event Log.
This can eventually fill up the Event Log file, so take action to
periodically cleanup the Event Log.
Step 3
------
Audit trail table and views are created automatically when you the script
"CATALOG.SQL" is run at database creation time.
The database audit trail (SYS.AUD$) is a single table in each Oracle database
data dictionary.
To help you view meaningfull auditing information in table SYS.AUD$, several
predefined views are provided. They must be created for you to use auditing,
you can later delete them if you decide not to use auditing (not recommended).
However, for the scope of this article they are less important as we focus
on audit_trail = OS and in that case the records go to the Event Log.
Creating the Audit Trail Views:
- Run the script "CATAUDIT.SQL" located in
%ORACLE_HOME%\RDBMS\ADMIN connected as SYSDBA from sqlplus :
SQL> @?\RDBMS\ADMIN\CATAUDIT.SQL
Deleting the Audit Trail Views (this is not recommended):
- Run the script "CATNOAUD.SQL" located in
%ORACLE_HOME%\RDBMS\ADMIN connected as SYSDBA from sqlplus :
SQL> @?\RDBMS\ADMIN\CATNOAUD.SQL
These are some useful table/views created by "CATAUDIT.SQL" when auditing
using the Windows Event Viewer:
AUDIT_ACTIONS Table/Synonym Descriptions for audit trail action types
============= ============= ========================================
ALL_DEF_AUDIT_OPTS View This single row view contains default
object-auditing options that will be
applied when objects are created
DBA_OBJ_AUDIT_OPTS View Lists auditing options for all
objects owned by a user
DBA_STMT_AUDIT_OPTS View Contains information which describes
current system auditing options
across the system and by user
DBA_PRIV_AUDIT_OPTS View Describes current system privileges
being audited across the system and by
user
V$SESSION View Lists session information for each
current session
SYSTEM_PRIVILEGE_MAP View Contains information about system
privilege codes
Step 4
------
After verifying all steps above, you are ready to configure the details on
auditing.
Remark 1: Auditing incurs overhead.
Although auditing is relatively inexpensive, limit the number of audited
events as much as possible. This will minimize the performance impact on the
execution of statements that are audited and minimize the size of the audit
trail. Setup a detailed, well planned auditing strategy before you start
auditing.
Remark 2 : Events Audited by Default
Whether database auditing is enabled or disabled, Oracle will always audit
certain database actions into the OS audit trail. These events include the
following:
-instance shutdown
-connections to the database with administrator privileges (SYSOPER/SYSDBA)
(See also later in this Note)
Examples:
--------
Let's start auditing with two examples:
-Statement Privilege Auditing
As user SYSTEM from SQL*Plus -> AUDIT CREATE SESSION by scott;
-Object Auditing
As user SYSTEM from SQL*Plus -> AUDIT DELETE on scott.emp;
Note:
-You cannot audit the SYS user, except for the connections this user makes
which is audited by default (CONNECT SYS AS SYSDBA or SYSOPER).
-Enabling/disabling an audit option does not influence the currently active
sessions, the audit option will be enabled/disabled at the next logon.
Check the audit options set previously:
-SQL*Plus -> SELECT audit_option, success,failure FROM dba_stmt_audit_opts
WHERE user_name = 'SCOTT';
AUDIT_OPTION SUCCESS FAILURE
------------ ------- -------
CREATE SESSION BY ACCESS BY ACCESS
The same result will be obtained by the following query:
SELECT privilege,success,failure FROM dba_priv_audit_opts WHERE
user_name = 'SCOTT';
-SQL*Plus -> SELECT object_name,object_type,del FROM dba_obj_audit_opts WHERE
owner='SCOTT' AND object_name = 'EMP';
OBJECT_NAME OBJECT_TYPE DEL
----------- ----------- ---
EMP TABLE S/S
Generate some audit entries:
-with SQL*Plus connect as user SCOTT -> SCOTT/password@net_service_name
-as user SCOTT -> DELETE FROM emp WHERE empno=7902;
COMMIT;
-with SQL*Plus connect as user SYSTEM -> SYSTEM/password@net_service_name
-as user SYSTEM -> DELETE FROM scott.emp WHERE empno=7900;
COMMIT;
=> (keep the following)
Events Audited by Default
-------------------------
Whether database auditing is enabled or disabled, Oracle will always audit
certain database actions into the OS audit trail. These events include the
following:
-instance shutdown
-connections to the database with administrator privileges (SYSOPER/SYSDBA)
Interpreting the audited options within the Windows Event Viewer.
-----------------------------------------------------------------
This section explains how to interprete the entries in the Event Viewer:
Let's open the Event Viewer Application Log:
- Start -> Control Panel -> Administrative Tools -> Event Viewer
From the File Menu choose the Application Log (Windows XP/2003)
- Start -> Settings -> Control Panel ->
Administrative Tools -> Services -> Event viewer->
Application log (Windows 2000)
Now you will see several entries present, the ones we are looking for are the
ones with the value Oracle.your_SID in the Source Column. You can choose to
filter out only the entries that are of your intrest. From the View Menu,
choose "Filter Events" and in the Filter Dialog Box pick Oracle.your_SID as
the source. Click OK.
Now we can focus on the Oracle audit entries for our database.
An entry in the Event Viewer consists of the following items:
Date -> Date of the Event
Time -> Time of the Event
Source -> The software that logged the event, which can be either an
application or a component of the system, such as a driver.
In our case this refers to the Oracle database and Instance
name ex. Oracle.your_SID
Category -> A classification of the event, as defined by the source.
For Oracle this is always set to 'None'.
Event -> Shows an Event number to identify the specific event.
See Note 67868.1 Windows NT Event Log messages for the Oracle
Database Server Service
User -> Operating System User
Computer -> The exact name of the computer where the logged event occured.
As already mentioned earlier some database activities are monitored by
default (See 'Events Audited by Default' above).
The Event Viewer will contain several entries for each instance startup. You
will notice when examining the event details (Double click the Event). For
example, the initialization of the SGA (System Global Area) for your
instance/database and the initialization of each background process for that
same instance.
The database activity we wanted to monitor/audit as demonstrated in Step 4,
can be found among the events with Event ID 34 (=Audit Trail, see also
Note 67868.1 Windows NT Event Log messages for the Oracle Database Server
Service).
There should be three entries with this Event ID in the format:
Date Time Source Category Event User Computer
---- ---- ------ -------- ----- ---- --------
dd/mm/yy hh:mi:ss AM/PM Oracle.your_SID None 34 N/A machine_name
Let's have a look at two of them.
Just double click the Event in the Event Viewer Application Log to get
the description:
1)Audit trail: SESSIONID:"471" ENTRYID:"1" STATEMENT:"1" USERID:"SCOTT"
TERMINAL:"XPERTWNT" ACTION:"100" RETURNCODE:"0" COMMENT$TEXT:"Authenticated
by DATABASE; Client address:(ADDRESS=(PROTOCOL=tcp)(HOST=171.16.129.1)
(PORT=1084))" OSUSERID:"Administrator" PRIV$USED:5
2)Audit trail: SESSIONID:"472" ENTRYID:"1" STATEMENT:"5" USERID:"SYSTEM"
TERMINAL:"XPERTWNT" ACTION:"103" RETURNCODE:"0" OBJ$CREATOR:"SCOTT"
OBJ$NAME:"EMP" SES$ACTIONS:"---S------------" SES$TID:"10922"
OS$USERID:"Administrator" PRIV$USED:50
What do these entries tell us?
Included you will find a list of some parameters you will encounter when
examining the details of an Event Entry (Double click the Event). Most of the
parameters can be traced down in the Oracle Database by querying the
appropriate Data Dictionary views. Where applicable, the Oracle views are
mentioned. You will also find included a reference to the Oracle View
DBA_AUDIT_TRAIL which is used when setting AUDIT_TRAIL to DB in your
"INIT.ORA":
SESSIONID = Numeric ID for each Oracle session as in
DBA_AUDIT_TRAIL.SESSIONID and V$SESSION.AUDSID
ENTRYID = Numeric ID for each audit trail entry in the session
(sequence starting with 1) as in DBA_AUDIT_TRAIL.ENTRYID
STATEMENT = Numeric ID for each statement run (a statement may cause
many actions) as in DBA_AUDIT_TRAIL.STEMENTID. There is no
correlation for STATEMENTID anywhere in the Oracle Data
Dictionary. This means you cannot trace down the SQL
statement executed.
USERID = Name (not ID number) of the user whose actions were audited
as in DBA_AUDIT_TRAIL.USERNAME and V$SESSION.USERNAME
TERMINAL = Identifier for the user's terminal as in
DBA_AUDIT_TRAIL.TERMINAL and V$SESSION.TERMINAL
ACTION = Numeric action type code. The corresponding name of the
action type (CREATE TABLE, INSERT,...) as in
DBA_AUDIT_TRAIL.ACTION and AUDIT_ACTIONS.ACTION
RETURNCODE = Oracle Server message code generated by the action as in
DBA_AUDIT_TRAIL.RETURNCODE. Zero if the action succeeded,
the Oracle error if failed, the returncode corresponds
to the 'ORA-' error, for example returncode 1017 means the
same as: ORA-1017 "invalid username/password; logon denied"
COMMENT$TEXT = Text comment on the audit trail entry, providing more
information about the statement audited. Also indicates how
the user was authenticated. The method can be one of the
following:
DATABASE -> authenticated by password
NETWORK -> authenticated by Net8 or Advanced Security
option
PROXY -> authenticated by another user. The name of the
proxy user follows the method type as in
DBA_AUDIT_TRAIL.COMMENT_TEXT
OBJ$CREATOR = Creator of the object affected by the action as in
DBA_AUDIT_TRAIL.OWNER and DBA_OBJECTS.OWNER
OBJ$NAME = Name of the object affected by the action as in
DBA_AUDIT_TRAIL.OBJ_NAME and DBA_OBJECTS.OBJECT_NAME
SES$ACTIONS = Session summary (a string of 16 characters, one for each
action type in the order ALTER,AUDIT,COMMENT,DELETE,GRANT,
INDEX,INSERT,LOCK,RENAME,SELECT,UPDATE,REFERENCES, and
EXECUTE. Positions 14, 15, and 16 are reserved for future
use. The characters are: -for none, S for success, F for
failure, and B for both) as in
DBA_AUDIT_TRAIL.SES_ACTIONS
SES$TID = Object ID of the object affected by the action as in
AUD$.SES$TID and DBA_OBJECTS.OBJECT_ID
OS$USERID = Operating System logon user name of the user whose actions
were audited as in DBA_AUDIT_TRAIL.OS_USER_NAME and
V$SESSION.OSUSER
PRIV$USED = System privilege used to execute the action as in
DBA_AUDIT_TRAIL.PRIV_USED and SYSTEM_PRIVILEGE_MAP.PRIVILEGE
How can we project this in the real world?
1)If the user that caused the first event entry is still connected we can
look up the necessary information from within Oracle.
You can look up/verify the values of the parameters SESSIONID,USERID,
TERMINAL and OS$USERID by querying the dynamic view V$SESSION :
SELECT sid,serial#,audsid,username,osuser,terminal FROM V$SESSION WHERE
username='SCOTT';
SID SERIAL# AUDSID USERNAME OSUSER TERMINAL
--- ------- ------ -------- ------------- --------
11 431 471 SCOTT Administrator XPERTWNT
|-> SESSIONID in Event description
|-> USERID in Event description
|-> OS$USERID in Event description
|-> TERMINAL in Event description
SELECT * FROM AUDIT_ACTIONS WHERE action= 100; (100 -> ACTION)
ACTION NAME
------ ----
100 LOGON
|-> ACTION in Event description
SELECT * FROM SYSTEM_PRIVILEGE_MAP WHERE privilege = -5; (5 -> PRIV$USED;
pay attention to the minus sign in front of the privilege search value in
the select otherwise no results will be returned)
PRIVILEGE NAME
--------- ----
-5 CREATE SESSION
|-> PRIV$USED in Event description
The RETURNCODE in this case is 0, which means in fact Oracle Server Error
Message 0 -> ORA-00000 : normal, successful completion.
This event was created due to the 'AUDIT SESSION by scott' earlier.
2)If the user that caused the second event entry is still connected we can
look up the necessary information from within Oracle.
You can look up/verify the values of the parameters SESSIONID,USERID,
TERMINAL and OS$USERID by querying the dynamic view V$SESSION :
SID SERIAL# AUDSID USERNAME OSUSER TERMINAL
--- ------- ------ -------- ------------- --------
11 433 472 SYSTEM Administrator XPERTWNT
|-> SESSIONID in Event description
|-> USERID in Event description
|-> OS$USERID in Event description
|-> TERMINAL in Event description
SELECT * FROM AUDIT_ACTIONS WHERE action= 103; (103 -> ACTION)
ACTION NAME
------ ----
103 SESSION REC
|-> ACTION in Event description
SELECT * FROM SYSTEM_PRIVILEGE_MAP WHERE privilege = -50; (50 -> PRIV$USED;
pay attention to the minus sign in front of the privilege search value in
the select otherwise no results will be returned)
PRIVILEGE NAME
--------- ----
-50 DELETE ANY TABLE
|-> PRIV$USED in Event description
SELECT owner,object_name,object_id FROM DBA_OBJECTS WHERE OBJECT_NAME='EMP';
OWNER OBJECT_NAME OBJECT_ID
----- ----------- ---------
SCOTT EMP 10922
|-> OBJ$CREATOR in Event description
|-> OBJ$NAME in Event description
|-> SES$TID in Event description
Now a brief explanation about the SES$ACTIONS in the Event description:
SES$ACTIONS:"---S------------"
As already explained above this is the session summary displayed in the format
of a 16 character string one for each action type in the order (1)ALTER,
(2)AUDIT,(3)COMMENT,(4)DELETE,(5)GRANT,(6)INDEX,(7)INSERT,(8)LOCK,(9)RENAME,
(10)SELECT,(11)UPDATE,(12)REFERENCES, and (13)EXECUTE.
This means the 'S' of SUCCESS on the fourth place which is a DELETE.
The RETURNCODE in this case is 0, which means in fact Oracle Server Error
Message 0 -> ORA-00000 : normal, successful completion.
This event was created due to the 'AUDIT delete on scott.emp' earlier.
References:
~~~~~~~~~~~
SQL Reference (any version) - AUDIT statement (provides detailed explanation, reference tables)
Note 67868.1 Windows NT Event Log messages for the Oracle Database Server Service
Bug 790056 LRM-00101: UNKNOWN PARAMETER NAME 'AUDIT_FILE_DEST' WHEN CONFIG AUDITING ON NT
Note 103964.1 How to Audit Connect AS SYSDBA Using Oracle Server
Doc ID: Note:99137.1 Type: BULLETIN
Last Revision Date: 06-JUN-2007 Status: PUBLISHED
"Checked for relevance on 15-May-2007"
Purpose:
~~~~~~~~
This document explains how to setup and interpret Operating System Auditing of
Oracle Database activity by using the Windows Event Viewer.
Some examples are :
'Instance O901 has been terminated',
'All processes in instance v1020 stopped',
'Shutdown normal performed on instance v1020',
'Audit trail:ACTION :'shutdown' ',
'Initializing PGA fro process RECO in instance v1020',
'Audit trail: ACTION : 'CONNECT' DATABASE USER: '/' PRIVILEGE : SYSDBA
CLIENT USER: pipopc\pipo CLIENT TERMINAL: pipopc STATUS: 0 .',
'AUDIT trail: 'startup'AUDIT_TRAIL:os',
'Audit trail: SESSIONID:"471" ENTRYID:"1" STATEMENT:"1" USERID:"SCOTT"
TERMINAL:"PIPOPC" ACTION:"100" RETURNCODE:"0" COMMENT$TEXT:
"Authenticated by DATABASE; Client address:
(ADDRESS=(PROTOCOL=tcp)(HOST=123.123.123.123)(PORT=1084))"
OSUSERID:"Administrator" PRIV$USED:5'
Scope & Application:
~~~~~~~~~~~~~~~~~~~~
Intended audience: NOVICE
A Quick Start in Auditing
Refer to the references mentioned below for further information about the many
auditing possibilities within Oracle. This note just gives the user an impression
and some examples of how to audit and interpret when sending auditing information
to the Windows Event Log.
SETTING UP AND INTERPRETING AUDITING USING THE WINDOWS EVENT VIEWER
-------------------------------------------------------------------
1. Setting up auditing.
This section explains how to prepare your database for auditing:
Step 1
------
Verify the Event Log Service is enabled on the machine where your Oracle
database resides:
Start -> Settings -> Control Panel -> Services (windows NT)
Start -> Settings -> Control Panel -> Administrative Tools
-> Services (Windows 2000/2003/XP)
Note on XP/2003 the Event Log Service cannot be stopped.
Service Status Startup
------- ------ -------
EventLog Started Automatic
Step 2
------
Add the following line to your INIT.ORA (instance parameter file located by
default under %ORACLE_HOME%\database) to activate auditing:
AUDIT_TRAIL = OS
or in case an spfile is in use issue:
SQL> connect / as sysdba
Connected.
SQL> alter system set audit_trail=os scope=spfile;
AUDIT_TRAIL enables or disables the writing of records to the audit trail.
Audited records are not written if the value is NONE or if the parameter
is not present. The OS option enables system-wide auditing and causes
audited records to be written to the operating system's audit trail.
Some auditing (amongst others: administrative user connections)
are ALWAYS RECORDED.
Note that on Windows you cannot and need not set related parameter audit_file_dest as the
records go top the Event Log instead of the filesystem on this platform (Remark 1).
The DB option enables system-wide auditing and causes audited records to
be written to the database audit trail (the SYS.AUD$ table). If set to
DB most audit records will go to table AUD$, except for some mandatory records
that will always go to the Event Log (Remark 2). The values TRUE and FALSE are also
supported for backwards compatibility. TRUE is equivalent to DB, and FALSE is
equivalent to NONE.
The SQL AUDIT statements can subsequently be used to audit specific Statement,
Privileges or Object auditing events.
Remark 1: Audit information cannot be spooled to a file on Windows. The
AUDIT_FILE_DEST parameter is NOT supported on the Windows platform
since auditing information is sent to the Event Log and can be
viewed with the Event Viewer. Therefore, this parameter should
not be added to the "INIT.ORA" (or spfile). When you add this
parameter and then bounce the database for the parameter to take
effect, you will get:
LRM-00101: UNKNOWN PARAMETER NAME 'AUDIT_FILE_DEST'
ORA-01078: FAILURE IN PROCESSING SYSTEM PARAMETERS
Remark 2: Some auditing (amongst others: administrative user connections)
are ALWAYS RECORDED independent whether init.ora parameter
audit_trail=os is set or not and they will go to the Event Log.
Remark 3: Setting AUDIT_TRAIL to OS , depending on the amount of audited events
can cause significantly more records to be written to the Event Log.
This can eventually fill up the Event Log file, so take action to
periodically cleanup the Event Log.
Step 3
------
Audit trail table and views are created automatically when you the script
"CATALOG.SQL" is run at database creation time.
The database audit trail (SYS.AUD$) is a single table in each Oracle database
data dictionary.
To help you view meaningfull auditing information in table SYS.AUD$, several
predefined views are provided. They must be created for you to use auditing,
you can later delete them if you decide not to use auditing (not recommended).
However, for the scope of this article they are less important as we focus
on audit_trail = OS and in that case the records go to the Event Log.
Creating the Audit Trail Views:
- Run the script "CATAUDIT.SQL" located in
%ORACLE_HOME%\RDBMS\ADMIN connected as SYSDBA from sqlplus :
SQL> @?\RDBMS\ADMIN\CATAUDIT.SQL
Deleting the Audit Trail Views (this is not recommended):
- Run the script "CATNOAUD.SQL" located in
%ORACLE_HOME%\RDBMS\ADMIN connected as SYSDBA from sqlplus :
SQL> @?\RDBMS\ADMIN\CATNOAUD.SQL
These are some useful table/views created by "CATAUDIT.SQL" when auditing
using the Windows Event Viewer:
AUDIT_ACTIONS Table/Synonym Descriptions for audit trail action types
============= ============= ========================================
ALL_DEF_AUDIT_OPTS View This single row view contains default
object-auditing options that will be
applied when objects are created
DBA_OBJ_AUDIT_OPTS View Lists auditing options for all
objects owned by a user
DBA_STMT_AUDIT_OPTS View Contains information which describes
current system auditing options
across the system and by user
DBA_PRIV_AUDIT_OPTS View Describes current system privileges
being audited across the system and by
user
V$SESSION View Lists session information for each
current session
SYSTEM_PRIVILEGE_MAP View Contains information about system
privilege codes
Step 4
------
After verifying all steps above, you are ready to configure the details on
auditing.
Remark 1: Auditing incurs overhead.
Although auditing is relatively inexpensive, limit the number of audited
events as much as possible. This will minimize the performance impact on the
execution of statements that are audited and minimize the size of the audit
trail. Setup a detailed, well planned auditing strategy before you start
auditing.
Remark 2 : Events Audited by Default
Whether database auditing is enabled or disabled, Oracle will always audit
certain database actions into the OS audit trail. These events include the
following:
-instance shutdown
-connections to the database with administrator privileges (SYSOPER/SYSDBA)
(See also later in this Note)
Examples:
--------
Let's start auditing with two examples:
-Statement Privilege Auditing
As user SYSTEM from SQL*Plus -> AUDIT CREATE SESSION by scott;
-Object Auditing
As user SYSTEM from SQL*Plus -> AUDIT DELETE on scott.emp;
Note:
-You cannot audit the SYS user, except for the connections this user makes
which is audited by default (CONNECT SYS AS SYSDBA or SYSOPER).
-Enabling/disabling an audit option does not influence the currently active
sessions, the audit option will be enabled/disabled at the next logon.
Check the audit options set previously:
-SQL*Plus -> SELECT audit_option, success,failure FROM dba_stmt_audit_opts
WHERE user_name = 'SCOTT';
AUDIT_OPTION SUCCESS FAILURE
------------ ------- -------
CREATE SESSION BY ACCESS BY ACCESS
The same result will be obtained by the following query:
SELECT privilege,success,failure FROM dba_priv_audit_opts WHERE
user_name = 'SCOTT';
-SQL*Plus -> SELECT object_name,object_type,del FROM dba_obj_audit_opts WHERE
owner='SCOTT' AND object_name = 'EMP';
OBJECT_NAME OBJECT_TYPE DEL
----------- ----------- ---
EMP TABLE S/S
Generate some audit entries:
-with SQL*Plus connect as user SCOTT -> SCOTT/password@net_service_name
-as user SCOTT -> DELETE FROM emp WHERE empno=7902;
COMMIT;
-with SQL*Plus connect as user SYSTEM -> SYSTEM/password@net_service_name
-as user SYSTEM -> DELETE FROM scott.emp WHERE empno=7900;
COMMIT;
=> (keep the following)
Events Audited by Default
-------------------------
Whether database auditing is enabled or disabled, Oracle will always audit
certain database actions into the OS audit trail. These events include the
following:
-instance shutdown
-connections to the database with administrator privileges (SYSOPER/SYSDBA)
Interpreting the audited options within the Windows Event Viewer.
-----------------------------------------------------------------
This section explains how to interprete the entries in the Event Viewer:
Let's open the Event Viewer Application Log:
- Start -> Control Panel -> Administrative Tools -> Event Viewer
From the File Menu choose the Application Log (Windows XP/2003)
- Start -> Settings -> Control Panel ->
Administrative Tools -> Services -> Event viewer->
Application log (Windows 2000)
Now you will see several entries present, the ones we are looking for are the
ones with the value Oracle.your_SID in the Source Column. You can choose to
filter out only the entries that are of your intrest. From the View Menu,
choose "Filter Events" and in the Filter Dialog Box pick Oracle.your_SID as
the source. Click OK.
Now we can focus on the Oracle audit entries for our database.
An entry in the Event Viewer consists of the following items:
Date -> Date of the Event
Time -> Time of the Event
Source -> The software that logged the event, which can be either an
application or a component of the system, such as a driver.
In our case this refers to the Oracle database and Instance
name ex. Oracle.your_SID
Category -> A classification of the event, as defined by the source.
For Oracle this is always set to 'None'.
Event -> Shows an Event number to identify the specific event.
See Note 67868.1 Windows NT Event Log messages for the Oracle
Database Server Service
User -> Operating System User
Computer -> The exact name of the computer where the logged event occured.
As already mentioned earlier some database activities are monitored by
default (See 'Events Audited by Default' above).
The Event Viewer will contain several entries for each instance startup. You
will notice when examining the event details (Double click the Event). For
example, the initialization of the SGA (System Global Area) for your
instance/database and the initialization of each background process for that
same instance.
The database activity we wanted to monitor/audit as demonstrated in Step 4,
can be found among the events with Event ID 34 (=Audit Trail, see also
Note 67868.1 Windows NT Event Log messages for the Oracle Database Server
Service).
There should be three entries with this Event ID in the format:
Date Time Source Category Event User Computer
---- ---- ------ -------- ----- ---- --------
dd/mm/yy hh:mi:ss AM/PM Oracle.your_SID None 34 N/A machine_name
Let's have a look at two of them.
Just double click the Event in the Event Viewer Application Log to get
the description:
1)Audit trail: SESSIONID:"471" ENTRYID:"1" STATEMENT:"1" USERID:"SCOTT"
TERMINAL:"XPERTWNT" ACTION:"100" RETURNCODE:"0" COMMENT$TEXT:"Authenticated
by DATABASE; Client address:(ADDRESS=(PROTOCOL=tcp)(HOST=171.16.129.1)
(PORT=1084))" OSUSERID:"Administrator" PRIV$USED:5
2)Audit trail: SESSIONID:"472" ENTRYID:"1" STATEMENT:"5" USERID:"SYSTEM"
TERMINAL:"XPERTWNT" ACTION:"103" RETURNCODE:"0" OBJ$CREATOR:"SCOTT"
OBJ$NAME:"EMP" SES$ACTIONS:"---S------------" SES$TID:"10922"
OS$USERID:"Administrator" PRIV$USED:50
What do these entries tell us?
Included you will find a list of some parameters you will encounter when
examining the details of an Event Entry (Double click the Event). Most of the
parameters can be traced down in the Oracle Database by querying the
appropriate Data Dictionary views. Where applicable, the Oracle views are
mentioned. You will also find included a reference to the Oracle View
DBA_AUDIT_TRAIL which is used when setting AUDIT_TRAIL to DB in your
"INIT.ORA":
SESSIONID = Numeric ID for each Oracle session as in
DBA_AUDIT_TRAIL.SESSIONID and V$SESSION.AUDSID
ENTRYID = Numeric ID for each audit trail entry in the session
(sequence starting with 1) as in DBA_AUDIT_TRAIL.ENTRYID
STATEMENT = Numeric ID for each statement run (a statement may cause
many actions) as in DBA_AUDIT_TRAIL.STEMENTID. There is no
correlation for STATEMENTID anywhere in the Oracle Data
Dictionary. This means you cannot trace down the SQL
statement executed.
USERID = Name (not ID number) of the user whose actions were audited
as in DBA_AUDIT_TRAIL.USERNAME and V$SESSION.USERNAME
TERMINAL = Identifier for the user's terminal as in
DBA_AUDIT_TRAIL.TERMINAL and V$SESSION.TERMINAL
ACTION = Numeric action type code. The corresponding name of the
action type (CREATE TABLE, INSERT,...) as in
DBA_AUDIT_TRAIL.ACTION and AUDIT_ACTIONS.ACTION
RETURNCODE = Oracle Server message code generated by the action as in
DBA_AUDIT_TRAIL.RETURNCODE. Zero if the action succeeded,
the Oracle error if failed, the returncode corresponds
to the 'ORA-' error, for example returncode 1017 means the
same as: ORA-1017 "invalid username/password; logon denied"
COMMENT$TEXT = Text comment on the audit trail entry, providing more
information about the statement audited. Also indicates how
the user was authenticated. The method can be one of the
following:
DATABASE -> authenticated by password
NETWORK -> authenticated by Net8 or Advanced Security
option
PROXY -> authenticated by another user. The name of the
proxy user follows the method type as in
DBA_AUDIT_TRAIL.COMMENT_TEXT
OBJ$CREATOR = Creator of the object affected by the action as in
DBA_AUDIT_TRAIL.OWNER and DBA_OBJECTS.OWNER
OBJ$NAME = Name of the object affected by the action as in
DBA_AUDIT_TRAIL.OBJ_NAME and DBA_OBJECTS.OBJECT_NAME
SES$ACTIONS = Session summary (a string of 16 characters, one for each
action type in the order ALTER,AUDIT,COMMENT,DELETE,GRANT,
INDEX,INSERT,LOCK,RENAME,SELECT,UPDATE,REFERENCES, and
EXECUTE. Positions 14, 15, and 16 are reserved for future
use. The characters are: -for none, S for success, F for
failure, and B for both) as in
DBA_AUDIT_TRAIL.SES_ACTIONS
SES$TID = Object ID of the object affected by the action as in
AUD$.SES$TID and DBA_OBJECTS.OBJECT_ID
OS$USERID = Operating System logon user name of the user whose actions
were audited as in DBA_AUDIT_TRAIL.OS_USER_NAME and
V$SESSION.OSUSER
PRIV$USED = System privilege used to execute the action as in
DBA_AUDIT_TRAIL.PRIV_USED and SYSTEM_PRIVILEGE_MAP.PRIVILEGE
How can we project this in the real world?
1)If the user that caused the first event entry is still connected we can
look up the necessary information from within Oracle.
You can look up/verify the values of the parameters SESSIONID,USERID,
TERMINAL and OS$USERID by querying the dynamic view V$SESSION :
SELECT sid,serial#,audsid,username,osuser,terminal FROM V$SESSION WHERE
username='SCOTT';
SID SERIAL# AUDSID USERNAME OSUSER TERMINAL
--- ------- ------ -------- ------------- --------
11 431 471 SCOTT Administrator XPERTWNT
|-> SESSIONID in Event description
|-> USERID in Event description
|-> OS$USERID in Event description
|-> TERMINAL in Event description
SELECT * FROM AUDIT_ACTIONS WHERE action= 100; (100 -> ACTION)
ACTION NAME
------ ----
100 LOGON
|-> ACTION in Event description
SELECT * FROM SYSTEM_PRIVILEGE_MAP WHERE privilege = -5; (5 -> PRIV$USED;
pay attention to the minus sign in front of the privilege search value in
the select otherwise no results will be returned)
PRIVILEGE NAME
--------- ----
-5 CREATE SESSION
|-> PRIV$USED in Event description
The RETURNCODE in this case is 0, which means in fact Oracle Server Error
Message 0 -> ORA-00000 : normal, successful completion.
This event was created due to the 'AUDIT SESSION by scott' earlier.
2)If the user that caused the second event entry is still connected we can
look up the necessary information from within Oracle.
You can look up/verify the values of the parameters SESSIONID,USERID,
TERMINAL and OS$USERID by querying the dynamic view V$SESSION :
SID SERIAL# AUDSID USERNAME OSUSER TERMINAL
--- ------- ------ -------- ------------- --------
11 433 472 SYSTEM Administrator XPERTWNT
|-> SESSIONID in Event description
|-> USERID in Event description
|-> OS$USERID in Event description
|-> TERMINAL in Event description
SELECT * FROM AUDIT_ACTIONS WHERE action= 103; (103 -> ACTION)
ACTION NAME
------ ----
103 SESSION REC
|-> ACTION in Event description
SELECT * FROM SYSTEM_PRIVILEGE_MAP WHERE privilege = -50; (50 -> PRIV$USED;
pay attention to the minus sign in front of the privilege search value in
the select otherwise no results will be returned)
PRIVILEGE NAME
--------- ----
-50 DELETE ANY TABLE
|-> PRIV$USED in Event description
SELECT owner,object_name,object_id FROM DBA_OBJECTS WHERE OBJECT_NAME='EMP';
OWNER OBJECT_NAME OBJECT_ID
----- ----------- ---------
SCOTT EMP 10922
|-> OBJ$CREATOR in Event description
|-> OBJ$NAME in Event description
|-> SES$TID in Event description
Now a brief explanation about the SES$ACTIONS in the Event description:
SES$ACTIONS:"---S------------"
As already explained above this is the session summary displayed in the format
of a 16 character string one for each action type in the order (1)ALTER,
(2)AUDIT,(3)COMMENT,(4)DELETE,(5)GRANT,(6)INDEX,(7)INSERT,(8)LOCK,(9)RENAME,
(10)SELECT,(11)UPDATE,(12)REFERENCES, and (13)EXECUTE.
This means the 'S' of SUCCESS on the fourth place which is a DELETE.
The RETURNCODE in this case is 0, which means in fact Oracle Server Error
Message 0 -> ORA-00000 : normal, successful completion.
This event was created due to the 'AUDIT delete on scott.emp' earlier.
References:
~~~~~~~~~~~
SQL Reference (any version) - AUDIT statement (provides detailed explanation, reference tables)
Note 67868.1 Windows NT Event Log messages for the Oracle Database Server Service
Bug 790056 LRM-00101: UNKNOWN PARAMETER NAME 'AUDIT_FILE_DEST' WHEN CONFIG AUDITING ON NT
Note 103964.1 How to Audit Connect AS SYSDBA Using Oracle Server
Some examples about auditing and output of auditing
Subject: Some examples about auditing and output of auditing
Doc ID: Note:167293.1 Type: BULLETIN
Last Revision Date: 04-JUN-2007 Status: PUBLISHED
"Checked for relevance on 27-Mar-2007"
Purpose:
~~~~~~~~
Purpose of the document is to show some examples of auditing output as most
articles do not show examples
How to set up auditing can be seen in for example:
Note 41800.1 Quick Reference to Auditing Information
Scope & Application:
~~~~~~~~~~~~~~~~~~~~
This article intends to demonstrate some examples of output of auditing.
The article is divided in
1. Auditing administrative connections
2. Auditing on object Level
3. Auditing on privilege Level
4. Auditing statements
Shown is for each section what can be audited, how to check what is audited and
some examples of the output of auditing.
Auditing:
~~~~~~~~~
Auditing can be set on statement level, object level and privilege level.
Also administrative connections are logged.
1. Auditing administrative connections
--------------------------------------
Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA.
There are some good examples of output of audited administrative connections
for Unix and for NT in:
Note 103964.1: RDBMS: How to Audit Connect Internal Using Oracle Server
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:
Note 174340.1 : Audit SYS user Operations
2. Auditing on object Level
---------------------------
Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.
Object auditing options are set for all users of the database and cannot be set
for individual users.
What options can be set? This can be seen from all_def_audit_opts.
Example object auditing options
-------------------------------
SQL> connect system/manager
SQL> select * from all_def_audit_opts;
Result:
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
These correspond to the following object options respectively:
alter, audit, comment, delete, grant, index, insert, lock, rename,
select, update, reference and execute .
All except reference and execute can be applied to tables, otherwise
you will get the error ora-1982 "invalid auditing option for tables".
Example of auditing scott.emp
-----------------------------
SQL> connect system/manager
SQL> audit select on scott.emp by session;
=> Audit succeeded.
Check: Which objects are audited
SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';
result:
OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-
Generate some audit information:
SQL> connect scott/tiger
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect t/tl
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/manager
SQL> select * from scott.emp;
=> all scott.emp's rows are shown
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> /
SQL> select username, priv_used, ses_actions from dba_audit_object
where obj_name='EMP' and owner='SCOTT';
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- ----------------
SYSTEM SELECT ANY TABLE ---------S---
T ---------F---
SCOTT ---------S---
3. Auditing on privilege Level
------------------------------
All system privileges can be audited.
The different privileges can be selected from system_privilege_map.
If you attempt to use a value that does not belong to the list, you get the
following error:
SQL> audit drop snapshot by access;
audit drop snapshot by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option
Example: auditable system privileges
------------------------------------
SQL> connect system/manager
SQL> select * from system_privilege_map;
Result (this result is version dependant and still going up):
PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
..... not entire result is shown ......
-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP
Example of auditing CREATE TABLE by scott and system
----------------------------------------------------
SQL> connect system/manager
SQL> audit create table by scott, system;
=> Audit succeeded.
This means each create table is audited for system and scott once per session.
Should each create table be audited then the clause 'by access' must be added.
check: Which privileges are audited
SQL> col user_name format a7
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from sys.dba_priv_audit_opts;
Result:
USER_NA PRIVILEGE SUCCESS FAILURE
------- --------------- ---------- ----------
SCOTT CREATE TABLE BY ACCESS BY ACCESS
SYSTEM CREATE TABLE BY ACCESS BY ACCESS
Generate some audit information:
SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
SQL> connect t/tl
SQL> create table tsc (c1 number);
=> table created
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> create table scott.t1(c1 number)
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> /
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
1 CREATE TABLE SCOTT
1 CREATE TABLE SYSTEM
4. Auditing statements
----------------------
Statement auditing is the selective auditing of related groups of statements for
a type of database structure or type of schema object for ddl (data definition
language) or dml (data manipulation language) statements.
For example when 'audit table' is audited create, alter and drop table
statements are audited.
When 'audit select table' is audited all select statements from table, view,
snapshot.. will be audited.
The difference with privilege auditing is that in the latter case a system
privilege is audited. For example the 'audit create table' will only audit
create table and not alter or drop table statements.
When similar statement and privilege options are set only one audit record is
generated.
Example: auditable statements
-----------------------------
The statements taht can be adited can be seen from stmt_audit_option_map.
SQL> connect system/manager
SQL> select * from stmt_audit_option_map;
Result (this result is version dependant and still going up):
OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
..... not entire result is shown ......
221 CONTEXT 0
234 ON COMMIT REFRESH 0
235 EXEMPT ACCESS POLICY 0
236 RESUMABLE 0
237 SELECT ANY DICTIONARY 0
238 DEBUG CONNECT SESSION 0
239 DEBUG CONNECT USER 0
240 DEBUG CONNECT ANY 0
241 DEBUG ANY PROCEDURE 0
242 DEBUG PROCEDURE 0
165 rows selected.
SQL> select * from stmt_audit_option_map
where name like '%TABLE%';
Result:
OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
8 TABLE 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
40 CREATE TABLE 0
41 CREATE ANY TABLE 0
42 ALTER ANY TABLE 0
43 BACKUP ANY TABLE 0
44 DROP ANY TABLE 0
45 LOCK ANY TABLE 0
46 COMMENT ANY TABLE 0
47 SELECT ANY TABLE 0
48 INSERT ANY TABLE 0
49 UPDATE ANY TABLE 0
50 DELETE ANY TABLE 0
54 ALTER TABLE 0
57 LOCK TABLE 0
58 COMMENT TABLE 0
65 SELECT TABLE 0
66 INSERT TABLE 0
67 UPDATE TABLE 0
68 DELETE TABLE 0
69 GRANT TABLE 0
Example of auditing TABLE by scott and system
---------------------------------------------
SQL> connect system/manager
SQL> audit table by scott, system;
=> Audit succeeded.
check: Which statements are audited
SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a9
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;
Result:
USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE
-------- ------ --------- ---------- ----------
SYSTEM TABLE BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS
Generate some audit information:
SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table t1;
=> table dropped
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> table created
SQL> drop table no;
=> *
ERROR at line 1:
ORA-00942: table or view does not exist
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
12 DROP TABLE SCOTT
1 CREATE TABLE SYSTEM
12 DROP TABLE SYSTEM
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT
SYSTEM CREATE ANY TABLE
SYSTEM
Example of auditing SELECT TABLE by scott and system
----------------------------------------------------
SQL> connect system/manager
SQL> audit select table by scott, system;
=> Audit succeeded.
Check: Which statements are audited
SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a13
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;
Result:
USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE
-------- ------ ------------- ---------- ----------
SYSTEM SELECT TABLE BY SESSION BY SESSION
SCOTT SELECT TABLE BY SESSION BY SESSION
Generate some audit information:
SQL> connect scott/tiger
SQL> delete from emp where ename='KING';
SQL> connect system/manager
SQL> insert into scott.emp (empno, ename) values (1, 'TEST');
SQL> select * from scott.emp;
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM SELECT ANY TABLE ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
103 SESSION REC SCOTT
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
Related Documents:
~~~~~~~~~~~~~~~~~~
Note 41800.1 : QREF: Quick Reference to Auditing Information
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 : How to Audit Connect Internal Using Oracle Server
Note 1020945.6: How to Setup Auditing
Note 1068714.6: How does the NOAUDIT option work
Note 99786.1 : How to Audit User Connection, Disconnection Date and Time
Note 174340.1 : Audit SYS user Operations (9.2 and up)
Doc ID: Note:167293.1 Type: BULLETIN
Last Revision Date: 04-JUN-2007 Status: PUBLISHED
"Checked for relevance on 27-Mar-2007"
Purpose:
~~~~~~~~
Purpose of the document is to show some examples of auditing output as most
articles do not show examples
How to set up auditing can be seen in for example:
Note 41800.1 Quick Reference to Auditing Information
Scope & Application:
~~~~~~~~~~~~~~~~~~~~
This article intends to demonstrate some examples of output of auditing.
The article is divided in
1. Auditing administrative connections
2. Auditing on object Level
3. Auditing on privilege Level
4. Auditing statements
Shown is for each section what can be audited, how to check what is audited and
some examples of the output of auditing.
Auditing:
~~~~~~~~~
Auditing can be set on statement level, object level and privilege level.
Also administrative connections are logged.
1. Auditing administrative connections
--------------------------------------
Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA.
There are some good examples of output of audited administrative connections
for Unix and for NT in:
Note 103964.1: RDBMS: How to Audit Connect Internal Using Oracle Server
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:
Note 174340.1 : Audit SYS user Operations
2. Auditing on object Level
---------------------------
Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.
Object auditing options are set for all users of the database and cannot be set
for individual users.
What options can be set? This can be seen from all_def_audit_opts.
Example object auditing options
-------------------------------
SQL> connect system/manager
SQL> select * from all_def_audit_opts;
Result:
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
These correspond to the following object options respectively:
alter, audit, comment, delete, grant, index, insert, lock, rename,
select, update, reference and execute .
All except reference and execute can be applied to tables, otherwise
you will get the error ora-1982 "invalid auditing option for tables".
Example of auditing scott.emp
-----------------------------
SQL> connect system/manager
SQL> audit select on scott.emp by session;
=> Audit succeeded.
Check: Which objects are audited
SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';
result:
OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-
Generate some audit information:
SQL> connect scott/tiger
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect t/tl
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/manager
SQL> select * from scott.emp;
=> all scott.emp's rows are shown
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> /
SQL> select username, priv_used, ses_actions from dba_audit_object
where obj_name='EMP' and owner='SCOTT';
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- ----------------
SYSTEM SELECT ANY TABLE ---------S---
T ---------F---
SCOTT ---------S---
3. Auditing on privilege Level
------------------------------
All system privileges can be audited.
The different privileges can be selected from system_privilege_map.
If you attempt to use a value that does not belong to the list, you get the
following error:
SQL> audit drop snapshot by access;
audit drop snapshot by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option
Example: auditable system privileges
------------------------------------
SQL> connect system/manager
SQL> select * from system_privilege_map;
Result (this result is version dependant and still going up):
PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
..... not entire result is shown ......
-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP
Example of auditing CREATE TABLE by scott and system
----------------------------------------------------
SQL> connect system/manager
SQL> audit create table by scott, system;
=> Audit succeeded.
This means each create table is audited for system and scott once per session.
Should each create table be audited then the clause 'by access' must be added.
check: Which privileges are audited
SQL> col user_name format a7
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from sys.dba_priv_audit_opts;
Result:
USER_NA PRIVILEGE SUCCESS FAILURE
------- --------------- ---------- ----------
SCOTT CREATE TABLE BY ACCESS BY ACCESS
SYSTEM CREATE TABLE BY ACCESS BY ACCESS
Generate some audit information:
SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
SQL> connect t/tl
SQL> create table tsc (c1 number);
=> table created
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> create table scott.t1(c1 number)
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table tsc (c1 number);
=> table created
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> /
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
1 CREATE TABLE SCOTT
1 CREATE TABLE SYSTEM
4. Auditing statements
----------------------
Statement auditing is the selective auditing of related groups of statements for
a type of database structure or type of schema object for ddl (data definition
language) or dml (data manipulation language) statements.
For example when 'audit table' is audited create, alter and drop table
statements are audited.
When 'audit select table' is audited all select statements from table, view,
snapshot.. will be audited.
The difference with privilege auditing is that in the latter case a system
privilege is audited. For example the 'audit create table' will only audit
create table and not alter or drop table statements.
When similar statement and privilege options are set only one audit record is
generated.
Example: auditable statements
-----------------------------
The statements taht can be adited can be seen from stmt_audit_option_map.
SQL> connect system/manager
SQL> select * from stmt_audit_option_map;
Result (this result is version dependant and still going up):
OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
..... not entire result is shown ......
221 CONTEXT 0
234 ON COMMIT REFRESH 0
235 EXEMPT ACCESS POLICY 0
236 RESUMABLE 0
237 SELECT ANY DICTIONARY 0
238 DEBUG CONNECT SESSION 0
239 DEBUG CONNECT USER 0
240 DEBUG CONNECT ANY 0
241 DEBUG ANY PROCEDURE 0
242 DEBUG PROCEDURE 0
165 rows selected.
SQL> select * from stmt_audit_option_map
where name like '%TABLE%';
Result:
OPTION# NAME PROPERTY
---------- ---------------------------------------- ----------
8 TABLE 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
40 CREATE TABLE 0
41 CREATE ANY TABLE 0
42 ALTER ANY TABLE 0
43 BACKUP ANY TABLE 0
44 DROP ANY TABLE 0
45 LOCK ANY TABLE 0
46 COMMENT ANY TABLE 0
47 SELECT ANY TABLE 0
48 INSERT ANY TABLE 0
49 UPDATE ANY TABLE 0
50 DELETE ANY TABLE 0
54 ALTER TABLE 0
57 LOCK TABLE 0
58 COMMENT TABLE 0
65 SELECT TABLE 0
66 INSERT TABLE 0
67 UPDATE TABLE 0
68 DELETE TABLE 0
69 GRANT TABLE 0
Example of auditing TABLE by scott and system
---------------------------------------------
SQL> connect system/manager
SQL> audit table by scott, system;
=> Audit succeeded.
check: Which statements are audited
SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a9
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;
Result:
USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE
-------- ------ --------- ---------- ----------
SYSTEM TABLE BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS
Generate some audit information:
SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table t1;
=> table dropped
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> table created
SQL> drop table no;
=> *
ERROR at line 1:
ORA-00942: table or view does not exist
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
1 CREATE TABLE SCOTT
12 DROP TABLE SCOTT
1 CREATE TABLE SYSTEM
12 DROP TABLE SYSTEM
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT CREATE TABLE
SCOTT
SYSTEM CREATE ANY TABLE
SYSTEM
Example of auditing SELECT TABLE by scott and system
----------------------------------------------------
SQL> connect system/manager
SQL> audit select table by scott, system;
=> Audit succeeded.
Check: Which statements are audited
SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a13
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;
Result:
USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE
-------- ------ ------------- ---------- ----------
SYSTEM SELECT TABLE BY SESSION BY SESSION
SCOTT SELECT TABLE BY SESSION BY SESSION
Generate some audit information:
SQL> connect scott/tiger
SQL> delete from emp where ename='KING';
SQL> connect system/manager
SQL> insert into scott.emp (empno, ename) values (1, 'TEST');
SQL> select * from scott.emp;
Results of auditing:
SQL> connect system/manager
SQL> col username format a8
SQL> col priv_used format 999
SQL> select username, priv_used, ses_actions from dba_audit_object;
Result:
USERNAME PRIV_USED SES_ACTIONS
-------- ---------------------------------------- -------------------
SCOTT ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM SELECT ANY TABLE ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SCOTT ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SYSTEM ---------S------
SQL> connect system/manager
SQL> select action, action_name, username
from dba_audit_trail ;
Result:
ACTION ACTION_NAME USERNAME
---------- --------------------------- --------
103 SESSION REC SCOTT
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
Related Documents:
~~~~~~~~~~~~~~~~~~
Note 41800.1 : QREF: Quick Reference to Auditing Information
Note 99137.1 : Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 : How to Audit Connect Internal Using Oracle Server
Note 1020945.6: How to Setup Auditing
Note 1068714.6: How does the NOAUDIT option work
Note 99786.1 : How to Audit User Connection, Disconnection Date and Time
Note 174340.1 : Audit SYS user Operations (9.2 and up)
Quick Reference to Auditing Information
Subject: Quick Reference to Auditing Information
Doc ID: Note:41800.1 Type: REFERENCE
Last Revision Date: 09-MAY-2007 Status: PUBLISHED
"Checked for relevance on 09-May-2007"
*** Please note this is a quick reference page and so does NOT contain
full explanations of the various statements and views.
Database Audit mode
~~~~~~~~~~~~~~~~~~~
show parameter audit
AUDIT_TRAIL DB , OS or NONE See
AUDIT_FILE_DEST File location See
What Statements are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]
select * from dba_stmt_audit_opts where USER_NAME='...';
Columns are:
AUDIT_OPTION from STMT_AUDIT_OPTION_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""
What Privileges are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]
select * from dba_priv_audit_opts where USER_NAME='...';
Columns are:
PRIVILEGE from SYSTEM_PRIVILEGE_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""
What Objects are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set Auditing:
AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS]
[WHENEVER {NOT} SUCCESSFUL]
select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...';
select * from all_def_audit_opts;
Columns are:
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
X/Y - is no option set X is when successful
S set by session Y is when Unsuccessful
A set by access
Audit results
~~~~~~~~~~~~~
Raw results go to DBA_AUDIT_TRAIL (view on SYS.AUD$ table).
Main where columns are: USERNAME, TIMESTAMP, OWNER
For underlying results see:
Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;
Auditing administrative connections
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER)
are always logged regardless of audit setting. On UNIX platforms these
are logged to *.aud files in $ORACLE_HOME/rdbms/audit regardless of any
init.ora parameter settings. See Note 103964.1 for more details.
Related Articles and Examples:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note 99137.1 Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 1020945.6 How to Setup Auditing
Note 1068714.6 How does the NOAUDIT option work
Note 99786.1 How to Audit User Connection, Disconnection Date and Time
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 249438.1 10G: New Value DB_EXTENDED for the AUDIT_TRAIL init.ora
Note 287436.1 SCRIPT Generate AUDIT and NOAUDIT Statements for Current Audit Settings
Doc ID: Note:41800.1 Type: REFERENCE
Last Revision Date: 09-MAY-2007 Status: PUBLISHED
"Checked for relevance on 09-May-2007"
*** Please note this is a quick reference page and so does NOT contain
full explanations of the various statements and views.
Database Audit mode
~~~~~~~~~~~~~~~~~~~
show parameter audit
AUDIT_TRAIL DB , OS or NONE See
AUDIT_FILE_DEST File location See
What Statements are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]
select * from dba_stmt_audit_opts where USER_NAME='...';
Columns are:
AUDIT_OPTION from STMT_AUDIT_OPTION_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""
What Privileges are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set audit:
AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]
select * from dba_priv_audit_opts where USER_NAME='...';
Columns are:
PRIVILEGE from SYSTEM_PRIVILEGE_MAP
SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
FAILURE ""
What Objects are being audited ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To set Auditing:
AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS]
[WHENEVER {NOT} SUCCESSFUL]
select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...';
select * from all_def_audit_opts;
Columns are:
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
X/Y - is no option set X is when successful
S set by session Y is when Unsuccessful
A set by access
Audit results
~~~~~~~~~~~~~
Raw results go to DBA_AUDIT_TRAIL (view on SYS.AUD$ table).
Main where columns are: USERNAME, TIMESTAMP, OWNER
For underlying results see:
Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;
Auditing administrative connections
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER)
are always logged regardless of audit setting. On UNIX platforms these
are logged to *.aud files in $ORACLE_HOME/rdbms/audit regardless of any
init.ora parameter settings. See Note 103964.1 for more details.
Related Articles and Examples:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note 99137.1 Setting up, Interpreting Auditing Using Windows NT Event Viewer
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 1020945.6 How to Setup Auditing
Note 1068714.6 How does the NOAUDIT option work
Note 99786.1 How to Audit User Connection, Disconnection Date and Time
Note 103964.1 How to Audit Connect Internal Using Oracle Server
Note 249438.1 10G: New Value DB_EXTENDED for the AUDIT_TRAIL init.ora
Note 287436.1 SCRIPT Generate AUDIT and NOAUDIT Statements for Current Audit Settings
AUDIT_SYS_OPERATIONS Set To FALSE Yet Audit Files Are Generated
Subject: AUDIT_SYS_OPERATIONS Set To FALSE Yet Audit Files Are Generated
Doc ID: Note:308066.1 Type: PROBLEM
Last Revision Date: 04-JUL-2007 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.1.0.3
This problem can occur on any platform.
Symptoms
Users find audit file are generated at Audit_File_Dest location. They can see database instance has the following setting .
1. audit_sys_operations=FALSE
2. audit_file_dest=/oracle10g/10.1.0/rdbms/audit
3. audit_trail=NONE
Although the audit_sys_operations is set to FALSE, the audit files were still been generated audit_file_dest.
AUDIT FILE ( *.aud ) output includes
Wed May 4 09:23:30 2005
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle10g
CLIENT TERMINAL: pts/3
STATUS: 0
Cause
Expected behavior. Regardless of whether database auditing is enabled, Oracle always audits certain database-related operations and writes them to the operating system audit file.
Solution
These operations include the following operations are audited:
Connections to the instance with administrator privileges
An audit record is generated that lists the operating system user connecting to Oracle as SYSOPER
or SYSDBA. This provides for accountability of users with administrative privileges. Full auditing
for these users can be enabled as explained in "Auditing Administrative Users".
Database startup
An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is stored in the operating system audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup helps detect when an administrator has restarted a database with database auditing disabled (thus enabling the administrator to perform unaudited actions).
Database shutdown
An audit record is generated that lists the operating system user shutting down the instance, the
user's terminal identifier, and the date and time stamp.
References
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#13370
http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/auditing.htm#1008218
Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 - Actions Audited By Default
Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 - Records Always in the Operating System Audit Trail
Keywords
'AUDIT_TRAIL' 'AUDIT_FILE_DEST' 'AUDIT_SYS_OPERATIONS'
Doc ID: Note:308066.1 Type: PROBLEM
Last Revision Date: 04-JUL-2007 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.1.0.3
This problem can occur on any platform.
Symptoms
Users find audit file are generated at Audit_File_Dest location. They can see database instance has the following setting .
1. audit_sys_operations=FALSE
2. audit_file_dest=/oracle10g/10.1.0/rdbms/audit
3. audit_trail=NONE
Although the audit_sys_operations is set to FALSE, the audit files were still been generated audit_file_dest.
AUDIT FILE ( *.aud ) output includes
Wed May 4 09:23:30 2005
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle10g
CLIENT TERMINAL: pts/3
STATUS: 0
Cause
Expected behavior. Regardless of whether database auditing is enabled, Oracle always audits certain database-related operations and writes them to the operating system audit file.
Solution
These operations include the following operations are audited:
Connections to the instance with administrator privileges
An audit record is generated that lists the operating system user connecting to Oracle as SYSOPER
or SYSDBA. This provides for accountability of users with administrative privileges. Full auditing
for these users can be enabled as explained in "Auditing Administrative Users".
Database startup
An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is stored in the operating system audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup helps detect when an administrator has restarted a database with database auditing disabled (thus enabling the administrator to perform unaudited actions).
Database shutdown
An audit record is generated that lists the operating system user shutting down the instance, the
user's terminal identifier, and the date and time stamp.
References
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#13370
http://download-west.oracle.com/docs/cd/B14117_01/network.101/b10773/auditing.htm#1008218
Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 - Actions Audited By Default
Oracle® Database Security Guide 10g Release 1 (10.1) Part Number B10773-01 - Records Always in the Operating System Audit Trail
Keywords
'AUDIT_TRAIL' 'AUDIT_FILE_DEST' 'AUDIT_SYS_OPERATIONS'
How to Audit Potential Attempts to Break a Username/Password?
Subject: How to Audit Potential Attempts to Break a Username/Password
Doc ID: Note:221944.1 Type: FAQ
Last Revision Date: 12-MAR-2007 Status: PUBLISHED
Purpose:
~~~~~~~~
How to enable audit to trace a user/terminal who is trying to break in
username and password in the database? This is also a good way to Audit
unsuccessful database login attempts.
Solution:
~~~~~~~~~
Do the following steps to enable auditing:
1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter.
AUDIT_TRAIL can be set to one of the following values:
DB or TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
OS enables systemwide auditing where audited records are written to the
operating system's audit trail.
NONE or FALSE disables auditing.
Example:
--------
AUDIT_TRAIL = TRUE
2. Stop/Start the instance to make the parameter effective.
3. Enable the following audit option
SQL>AUDIT ALL BY ACCESS WHENEVER NOT SUCCESSFUL
4. Then query AUD$ as the following example
SQL> select returncode, action#, userid, userhost, terminal from aud$
RETURNCODE ACTION# USERID USERHOST TERMINAL
---------- ---------- -------- -------------------- --------------------
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR
The return code 1017 means ORA-1017 "invalid username/password; logon denied"
indicating that host WPRATA-BR tried to break in username/password.
References:
~~~~~~~~~~~
Note 1020945.6 How to Setup Auditing
Note 167293.1 Some examples about auditing and output of auditing
Note 30690.1 Init.ora Parameter "AUDIT_TRAIL" Reference Note
Note 352389.1 Finding the source of failed login attempts.
Doc ID: Note:221944.1 Type: FAQ
Last Revision Date: 12-MAR-2007 Status: PUBLISHED
Purpose:
~~~~~~~~
How to enable audit to trace a user/terminal who is trying to break in
username and password in the database? This is also a good way to Audit
unsuccessful database login attempts.
Solution:
~~~~~~~~~
Do the following steps to enable auditing:
1. Modify the "init
directory to enable the AUDIT_TRAIL parameter.
AUDIT_TRAIL can be set to one of the following values:
DB or TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
OS enables systemwide auditing where audited records are written to the
operating system's audit trail.
NONE or FALSE disables auditing.
Example:
--------
AUDIT_TRAIL = TRUE
2. Stop/Start the instance to make the parameter effective.
3. Enable the following audit option
SQL>AUDIT ALL BY ACCESS WHENEVER NOT SUCCESSFUL
4. Then query AUD$ as the following example
SQL> select returncode, action#, userid, userhost, terminal from aud$
RETURNCODE ACTION# USERID USERHOST TERMINAL
---------- ---------- -------- -------------------- --------------------
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR
1017 100 SCOTT WPRATA-BR
The return code 1017 means ORA-1017 "invalid username/password; logon denied"
indicating that host WPRATA-BR tried to break in username/password.
References:
~~~~~~~~~~~
Note 1020945.6 How to Setup Auditing
Note 167293.1 Some examples about auditing and output of auditing
Note 30690.1 Init.ora Parameter "AUDIT_TRAIL" Reference Note
Note 352389.1 Finding the source of failed login attempts.
Tuesday, July 24, 2007
Auduiting
There are a number of basic issues that should be considered when contemplating using Oracle's auditing features. These are as follows:
• Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
• When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
• How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
• What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
• Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
• System triggers
These were introduced with Oracle 8 and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
• Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
• Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
• System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
• Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
• Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
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.
• Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
• When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
• How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
• What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
• Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
• System triggers
These were introduced with Oracle 8 and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
• Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
• Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
• System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
• Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
• Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
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.
Subscribe to:
Posts (Atom)