An auditor needs to check the Oracle database in order to gather some useful and important information to conduct his audit. In fact, an auditor will ask the system administrator to run the commands and collect the information on behalf of him. The following are the basic commands that you need to know before starting the proper audit of the audit trail.
To get a list of all users in the database:
SQL. Select *from all_users;
Another useful command is to check the list of privileges assigned to a user. You have to run this command after logging in with the user that you want to audit for privileges.
SQL> select username, privilege from user_sys_privs;
Note: all the commands used in this article works with Oracle 10g.
Normally, you need to enable oracle audit in order to audit an Oracle object (e.g. table, users). Here, the term “audit” means to generate logs for users’ activities such as running a query or creating a table. So, to get audit data you need to enable it at the first hand. But, there are some activities that generate log files regardless of you have enabled the audit or not and those activities are:
-
When an administrator log in to the database
-
When Oracle starts up
-
When oracle shutdown.
Types of audit: Standard audit and fine grained audit.
Where to store audit files?
You can store your standard audit trail (log files) either in database or in operation systems. If you select your log to be stored in database, then it will be stored to dba_audit_trail ( sys.aud$ file). In case of fine grained audit the log will be written in the dba_fga_trail (sys.log4) and the dba_common_audit_trail table. The database audit trail is basically a single table which is known as sys.aud$ in the SYS schema on the data dictionary of the database.
Types of Oracle auditing
The four types of auditing that are configurable in an Oracle database are:
Statement: all the SQL statements run by a user can be audited
Privilege: this enables the auditing of create, update delete statements
Schema object: this is done to a specific object such as activities to a table.
Fine grained auditing: the is done at the granular level of the database such as when any action takes place on a specific content on a row of a table.
To list active option for auditing privileges ,objects and statements use the following:
Select *from dba_stmt_audit_opts;
Select *from dba_priv_audit_opts;
Select *from dba_obj_audit_opts’
When an administrator enables auditing in his database, he needs to be careful to avoid the audit logs filling up the database tablespace. Check the following tables:
Dba_audit_exists
Dba_audit_object
Dba_audit_session
Dba_audit_statement
Dba_audit_trail
How to audit the audit trail?
Audit trail is the log that an auditor check to find that things are going on well and there is no suspicious activities. What if that log or audit trail get tempered by someone? To prevent this happening, the audit trails need to be secured so that some log gets generated when someone modifies it. To secure the log:
SQL> audit delete, insert, update on sys.aud$ by access;
Before you enable a particular user to be audited you need to decide the actions that you want to monitor. For example, you want the user Scott is being audited for all his action, then use the following command:
SQL> audit all by scott by access;
If you want to audit only the table viewing activities:
SQL> audit select table by scott by access;
To check that if Scott is changing any data use:
Audit update table, delete table, insert table by Scott by access;
So, the following is the list of actions to enable auditing features in Oracle:
1. Set the audit trail.
2. Enable the audit option to the users or objects that you want to monitor
3. Select which action to be audited (e.g. all actions or just the viewing option_
4. Protect your audit trail.
Check the audit trail parameters?
Before setting audit trail check its parameters using the following SQL statement:
SQL> show parameter audit
Or
SQL> show parameter audit trail
If your audit trail location is set as db, then it is already set. But if it is set to “none” then you need to change it. The paragraph below will show you step-by-step procedures to enable the initialization parameter of the trail.
How to enable audit in Oracle 10g?
To enable audit in your database at first use the following command to check the initialization parameter
Show parameter audit
And then
ALTER SYSTEM SET audit trail=db SCOPE=SPFILE;
If the command runs successfully, you will see the message “system altered”
Next, stop the database. Connect as sysdba
Command: Connect sys as sysdba
It will take a few minutes to shut down your database.
Once you get the confirmation message of the shutdown, you type the command startup.
When your database will be up, run the command: show parameter audit to see if the audit trail location has been set to db.
To check if the SYS user is being audited use the following:
Show parameter audit_sys_operations
The value is true means that SYS user is being audited.
If the value is false or none and you want this user to be monitored, then you need to enable auditing the SYS user with the help of following statement:
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT=’Begin auditing SYS’
SCOPE=SPFILE;
Next, run the following command:
Audit all by sys by access;
Remember to restart your database to take effect the changed you made.
To view the log of a user session from the dba_audit_session table:
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
LOGOFF_LWRITE, LOGOFF_DLOCK
FROM DBA_AUDIT_SESSION;
Apart from this standard auditing, you can implement fine-grained auditing in order to oversee the activities in your data itself. You can find about this granular level of Oracle audit on database security page in their website.
