Thursday, April 4, 2019

How to audit specific users' actions on other schemas' objects


This was executed on Oracle 12.2

To audit actions by users on another schema's objects, execute this:
audit insert table,update table,select table, delete table by scott by access; 

Verify that the auditing is in place:
SELECT audit_option,success,failure
FROM DBA_STMT_AUDIT_OPTS
WHERE USER_NAME='SCOTT';

You can see that the actions we want to audit for user SCOTT is indeed set:

audit_option success failure
SELECT TABLE BY ACCESS BY ACCESS
INSERT TABLE BY ACCESS BY ACCESS
UPDATE TABLE BY ACCESS BY ACCESS
DELETE TABLE BY ACCESS BY ACCESS

This will generate audit entries whenever scott tries to perform queries or DML on other schemas' tables.

In my case, I tested this by logging on to the database as user scott, and executed a delete statement against a test table called "yy", owned by the SALES schema:

sqlplus / as sysdba
grant create synonym to scott;

sqlplus scott/tiger@testdb01
SQL> create synonym yy for sales.yy;

Synonym created.

SQL> delete from yy where antall in (1,2);

7 rows deleted.

SQL> commit;

Commit complete.

This particular database writes its audit information to the operating system, as indicated by the audit_trail parameter:
sqlplus / as sysdba
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

My audit_file_dest resides inside the ADR directory structure:
show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/oracle/admin/testdb01/adump
Checking the /u01/oracle/admin/testdb01/adump I found the file and it showed:
Thu Apr  4 16:32:56 2019 +02:00
LENGTH: "276"
SESSIONID:[8] "14060442" 
ENTRYID:[2] "67" 
STATEMENT:[2] "14" 
USERID:[6] "SCOTT" 
USERHOST:[18] "MYDOMAIN\MYPC123" 
TERMINAL:[12] "MYPC123" 
ACTION:[1] "7" 
RETURNCODE:[1] "0" 
OBJ$CREATOR:[11] "SALES" 
OBJ$NAME:[2] "YY" 
OS$USERID:[6] "vegardk" 
DBID:[10] "2425899399"

The action codes are important. Number 7 indicates a DELETE statement, which is what I expected to find.
The entire list can be found here

No comments:

Post a Comment