Tag Archives: activity

audit, Oracle

Auditing the changes to a single object:

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 activities on a certain object within the 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 Wed Apr 07 20: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. In this procedure we will audit the table ljcatt.test for any select, update, delete or insert operations that may occur. To do this we will user the following command: audit select, update, delete, insert on {schema}.{object_name} by access;

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

Audit succeeded.

SQL>

7. Perform a select on the database view dba_obj_audit_opts and you can see that auditing is enabled for the object test with actions DEL – delete, INS – insert, SEL – select, and UPD – update being monitored.

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 TABLE
-/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-

SQL>

8. Now connect as ljcatt and perform an insert, update, select and delete from your table.

SQL> connect ljcatt
Enter password:
Connected.
SQL>
SQL> insert into ljcatt.test(testing) values(‘testing’);

1 row created.

SQL> update ljcatt.test set testing=’testing_2′ where testing=’testing’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ljcatt.test;

TESTING
———-
testing_2

SQL>

SQL> delete from ljcatt.test;

1 rows deleted.

SQL> commit;

Commit complete.

SQL>

9. Perform the following select from the dba_audit_objects and you can see the audit trail from above actions.

SQL> column username format a10
SQL> column action_name format a10
SQL> column obj_name format a6
SQL> select username, action_name, obj_name from sys.dba_audit_object where username=’LJCATT’;

USERNAME ACTION_NAM OBJ_NA
———- ———- ——
LJCATT INSERT TEST
LJCATT UPDATE TEST
LJCATT SELECT TEST
LJCATT DELETE TEST

SQL>

This completes the auditing of a single object within the Oracle RDBMS.

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

Oracle, Oracle 10g, SQL

Gathering SQL statements in Oracle 10g

Leave a comment

Oracle 10g allows for increase amount of information to be extracted about SQL statements executed against the database since last instance startup. In Oracle 10g we have the new V$BIND_VALUE_CAPTURE, which allows you see the bind variables attached to most SQL statements executed in the database. Additionally, the view V$SQL has several improvements over it’s 9i counterpart to include: Full contents of the SQL statement; last execution time in date format; and actually username of parsing user. This article will cover how to extract previously and currently executed SQL statements with a few different definable variables from you Oracle 10g database.

1. Logon to your Oracle 10g database as sysdba.

MyLinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 16 13:30:13 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. To extract all SQL executed since the last system startup, execute the following PL/SQL block. NOTE: This may contain a lot of information, so you should spool the contents of this execution to an OS layer file with the command: spool ./

/********************
// Larry Catt
// 15 AUG 2009
// All SQL since instance startup.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare
v_ct number;
v_bind varchar2(4000);
begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

3. The following PL/SQL block will give you all the SQL for a particular user defined by the variable V_USER.

/********************
// Larry Catt
// 15 AUG 2009
// ALL SQL from a particular user defined as the variable V_USER.
//
// 10g version
/***********************/

set serveroutput on size 1000000
declare
v_ct number;
v_bind varchar2(4000);
v_user varchar2(30):=’SYS’;

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where parsing_schema_name= v_user order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);
end loop;
end loop;
end;
/

4. The following PL/SQL block will give you all the SQL statements executed during the time range of variables V_START_TIME and V_END_TIME.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements executed between the times V_START_TIME and V_END_TIME.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);
v_start_time date := to_date(’15-AUG-2009 13:15:00′,’DD-MON-YYYY HH24:MI:SS’);
v_end_time date:=to_date(’15-AUG-2009 15:15:00′,’DD-MON-YYYY HH24:MI:SS’);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where LAST_ACTIVE_TIME between v_start_time and
v_end_time order by LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

5. The following PL/SQL block will give you all the SQL statements currently being executed in the system.

/********************
// Larry Catt
// 15 Aug 2009
// All SQL statements currently executing.
//
// 10g version
/***********************/

set serveroutput on size 1000000

declare

v_ct number;
v_bind varchar2(4000);

begin

for v_sql in(select sql_id, PARSING_SCHEMA_NAME, SQL_TEXT,
to_char(LAST_ACTIVE_TIME, ‘DD-MON-YYYY HH:MI:SS’) as v_time
from v$sql where address in
(select sql_address from v$session where status=’ACTIVE’) order by
LAST_ACTIVE_TIME)
loop

dbms_output.put_line(‘##########################’);
dbms_output.put_line(‘#’);
dbms_output.put_line(v_sql.parsing_schema_name);
dbms_output.put_line(v_sql.sql_text);
dbms_output.put_line(v_sql.v_time);

for v_b in(select VALUE_STRING from V$SQL_BIND_CAPTURE where sql_id=v_sql.sql_id
order by position)
loop

dbms_output.put_line(v_b.value_string);

end loop;
end loop;
end;
/

6. This completes extraction of Oracle SQL statements in Oracle 10g.

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