11g, Oracle

Auditing the connections for a particular user:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity. In this article we will show how to audit a particular user’s connectivity in an Oracle database. This procedure will work on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Feb 12 04:31:43 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ———————-
Audit_trail string DB
SQL>

4. If the parameter is not set to DB and you are using a spfile, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database add the string AUDIT_TRIAL=DB and start the database.
NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. Execute the following command to begin auditing of connection by a particular user: audit session by {user_name}

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

7. Perform the following select from the view DBA_PRIV_AUDIT_OPTS to see that auditing is enabled: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE SESSION

SQL>

8. Connect as the user you are auditing.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

9. Reconnect as sysdba.

SQL> connect / as sysdba
Connected.
SQL>

10 Perform a select against the view dba_audit_trail to determine if the connection was recorded.

SQL> select username, returncode, action_name from dba_audit_trail;

USERNAME RETURNCODE ACTION_NAME
—————————— ———- —————————-
LJCATT 0 SESSION REC
LJCATT 0 LOGOFF
SQL>

As seen in the output above, the session reconnection and then log off where both successfully record with a return code of zero. If either action failed, the return code would show the error number produced. This completes auditing of a particular user account in Oracle RDBMS.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Leave a Reply