Auditing SOA Server DML Usage (Insert/Update/Delete operations)

 

1. alter system set audit_trail=db,extended scope=spfile;

2. restart the database

3. Login as scott/tiger
audit update , insert, delete on dept;

4. select * from DBA_OBJ_AUDIT_OPTS;
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
--------- --------- --------- ---------
SCOTT                          DEPT
TABLE                   -/-       -/-       -/-       S/S       -/-
-/-       S/S       -/-       -/-       -/-       S/S       -/- -/-
-/-       -/-       -/-       -/-

5. Now run some sql ,
insert into dept values(60,'SUPPORT1','BANGALORE1')

6. Run the following sql , to check the sql ,
select ntimestamp# , sqltext , sqlbind  from sys.aud$ order by ntimestamp# desc ;

7. To remove the audit , login as scott/tiger user ,
noaudit update , insert, delete on dept;

8.  select * from DBA_OBJ_AUDIT_OPTS;
To capture the sql for a schema ,

1. Login as sys or system user and run the following sql
AUDIT update table , insert table , delete table ,  EXECUTE PROCEDURE by dev1117_soainfra;

2. select * from DBA_STMT_AUDIT_OPTS where user_name = 'DEV1117_SOAINFRA'

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------
DEV1117_SOAINFRA
EXECUTE PROCEDURE                        BY SESSION BY SESSION
DEV1117_SOAINFRA
DELETE TABLE                             BY SESSION BY SESSION
DEV1117_SOAINFRA
UPDATE TABLE                             BY SESSION BY SESSION
DEV1117_SOAINFRA
INSERT TABLE                             BY SESSION BY SESSION

3. Now run sql server and you will see all the dml statemnets captured
select ntimestamp# , sqltext , sqlbind  from sys.aud$ order by ntimestamp# desc ;

4. To remove the auditing run ,
NOAUDIT update table , insert table , delete table ,  EXECUTE PROCEDURE by dev1117_soainfra;

Restart the SOA Server so that the audit logs are disabled.

http://stackoverflow.com/questions/13100054/oracle-database-audit-doesnt-show-sql-text-and-bind-values