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/adumpChecking 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