Tag Archives: audit

audit, Oracle

Removal of all Oracle RDBMS audit records:

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 on the database. However, this audit trail can consume huge amounts of disk space and it is necessary to remove this information once it has become obsolete. This article covers the removal of all audit records within an Oracle RDBMS and will work regardless of OS type.

NOTE: This article covers the removal of audit records from the Oracle database, the initialization parameter of AUDIT_TRAIL must be set to DB in order for this procedure to work correctly.

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

2. Logon to your Oracle database as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun May 09 13:11:07 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. Select from one of the audit tables to see how much audit data has been collected.

SQL> select count(*) from dba_audit_session;

COUNT(*)
———-
1364166

SQL>

4. As SYSDBA delete all audit trail records with the command: delete from sys.aud$;

SQL> DELETE FROM SYS.AUD$;

1364166 rows deleted.

SQL> commit;

Commit complete.

SQL>

5. Now perform a select from dba_audit_session view.

SQL> select count(*) from dba_audit_session;

COUNT(*)
———-
0

SQL>

This completes the removal of old audit records from within the Oracle database.

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

11g, audit, Oracle, Oracle 10g, oracle 9i

Removal of Oracle auditing on specific privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which privilege and user in the database they suspect is causing a problem, thus they only wish to monitor that privilege. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific privilege on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Mar 6 10:22:512010

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>

4. In this procedure we will audit the create table privilege by the user LJCATT, execute the following command to begin the auditing process for our specified object: audit create table by {schema.object_name};

SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the privilege ‘create table ‘ by LJCATT is setup for auditing.

SQL> select user_name, privilege from DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE TABLE

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all privilege auditing. To only remove auditing on LJCATT privileges replace the line

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘||user_name as stmt from sys.dba_obj_audit_opts where USER_NAME=’LJCATT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL
Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit ‘||privilege||’ by ‘|| user_name as stmt from
sys.dba_priv_audit_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed, re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_PRIV_AUDIT_OPTS;

SQL> select * from DBA_PRIV_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a privilege in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

audit, Oracle, Oracle 10g, oracle 9i

Removal of Oracle auditing on specific object:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which object in the database they suspect is causing a problem, thus they only wish to monitor that object. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific object on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definition for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Feb 27 06:38:462010

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>

4. In this procedure we will audit the table account LJCATT.TEST_AUDIT, execute the following command to begin the auditing process for our specified object: audit insert, update, delete on
{schema.object_name} by access;

SQL> audit insert, update, delete on ljcatt.test_audit by access;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_OBJ_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the object is setup for auditing.

SQL> select * from DBA_OBJ_AUDIT_OPTS;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— —————–
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
— — — — — — — — — — — — — — — — —
LJCATT TEST_AUDIT TABLE
-/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/- -/- -/- -/-

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

NOTE: This script will remove all object auditing. To only remove auditing on LJCATT.TEST_AUDIT table replace the line

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)

with

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts
where owner=’LJCATT’ and OBJECT_NAME=’TEST_AUDIT’)

———————————————————————–
Beginning removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

———————————————————————–
End removal of Oracle auditing definitions for a specific object PL/SQL Block
———————————————————————–
———————————————————————–
OUPUT
———————————————————————–

SQL> declare
2
3 v_ct number;
4
5 begin
6
7 for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt
from sys.dba_obj_audit
_opts)
8 loop
9 execute immediate(v_stmt.stmt);
10 end loop;
11
12
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
End of OUPUT
———————————————————————–

7. Once completed re-execute the following SQL to verify that the auditing definition have been removed from the system: select * from DBA_OBJ_AUDIT_OPTS;

SQL> select * from DBA_OBJ_AUDIT_OPTS;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific object in the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

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

11g, audit, Oracle

Removal of Oracle auditing on specific user account:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which user account they suspect is causing a problem, thus they only wish to monitor that account. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific user account on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jan 15 19:18:09 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>

4. In this procedure we will audit the account LJCATT, execute the following command to begin the auditing process for your specified user account: audit session by {user_name};

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the account is setup for auditing.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
— —-
LJCATT ALTER SYSTEM
LJCATT AUDIT SYSTEM
LJCATT CREATE SESSION

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

————————————————————–
Beginning removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

——————————————————————
End removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————
——————————————————————
OUPUT
——————————————————————

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 execute immediate(v_stmt.stmt);
12 end loop;
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
——————————————————————
End of OUPUT
——————————————————————

7. Once completed re-execute the following SQL to verify that the auditing definitions have been removed from the system: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific user account in the RDBMS.

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

11g, audit, Oracle

Viewing all auditing occurring within an Oracle RDBMS:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which providing the administrator the ability to find suspicious activity. This article covers the display of all auditing which is currently defined in an Oracle RDBMS. This procedure will work regardless of OS type.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire 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 AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Dec 17 07:11:59 2009

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>

4. Execute the following PL/SQL block to display all auditing which is currently defined in your ORACLE RDBMS.

————————————————————–
Beginning of PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–

———————————————————————–
Beginning of PL/SQL Block output
———————————————————————–

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audit_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /
audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–

This completes viewing of all auditing occurring within an Oracle RDBMS.

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

11g, audit, Oracle, Oracle 10g, oracle 9i

Auditing the use of an Oracle privilege:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In this article we will show how to audit use of a system privilege by a particular user in the Oracle RDBMS. 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 Sat Nov 14 13:28:31 2009

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. In this procedure we will audit the creation of tables by the user LJCATT. To perform this auditing, we will use the command: audit create table by {user_name};

SQL> connect / as sysdba
Connected.
SQL> audit create table by ljcatt;

Audit succeeded.

SQL>

7. Perform a select on the view DBA_PRIV_AUDIT_OPTS, to verify that auditing is enabled.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-

LJCATT CREATE TABLE

SQL>

8. Logon as the user you are auditing and create a table.

SQL> connect ljcatt
Enter password:
Connected.
SQL> create table test_audit(col_a varchar2(30));

Table created.

SQL>

9. Logon with sysdba privileges and verify that the create table action was recorded in you DBA_AUDIT_TRAIL view. As seen below the audit record for the create table privilege was recorded.

SQL> connect / as sysdba
Connected.
SQL>
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAM
———- ———-
LJCATT CREATE TABLE

This completes auditing of a system privilege by a user account within the Oracle RDBMS.

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

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing of user’s connections

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article, we will discuss the auditing of connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 2009

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>

2. Ensure that audit_trail and audit_sys_operations parameters are set to TRUE.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

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

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

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

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle auditing insert, update, and delete on a table

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article we will discuss the auditing of insert, update, and deletes by user account access.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 03 19:18:21 2009

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>

2. Ensure that audit_trail is set to DB_EXTENDED and audit_sys_operations parameter is set to TRUE. Note: It is important to realize that setting the AUDIT_TRAIL to DB_EXTENDED will dramatically increase storage, so monitor this closely.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Execute the audit command below to begin monitoring changes to the table SCOTT.DEPT.

SQL> audit insert, update, delete on scott.dept by session;

Audit succeeded.

SQL>

6. Connect to the database as the user LJCATT and perform a insert, update and delete operation on the table scott.dept.

SQL> connect ljcatt/ljcatt
Connected.
SQL> insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
;

1 row created.

SQL> commit;

Commit complete.

SQL> update scott.dept set loc=’WASHINGTON’ where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from scott.dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

7. Login as sysdba again.

SQL> connect sys as sysdba
SQL>

8. Execute the following SQL to extract the statements executed by LJCATT.
“select timestamp, sql_text from dba_audit_object where username=’LJCATT’;”

SQL> select timestamp, sql_text from dba_audit_object where username=’LJCATT’;

TIMESTAMP SQL_TEXT
——— ——————————————————-
27-AUG-09 update scott.dept set loc=’WASHINGTON’ where deptno=50
27-AUG-09 insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
27-AUG-09 delete from scott.dept where deptno=50

SQL>

9. That completes the use of Oracle Auditing to monitor the changes to the Oracle database by users.

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