I am trying to audit when a user UPDATEs a table in Oracle. The script I am running is below. All of the other auditing works fine, but not the UPDATE policies. I created two policies to try two different methods for catching it.
-- statements needed to show commands as run and to make the tables readable
SET ECHO ON;
SET LINESIZE 200;
COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A15
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A20
-- create user with required permissions (a&b)
CREATE USER schemer IDENTIFIED BY power QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE ANY TABLE, INSERT ANY TABLE TO schemer;
CREATE USER SDEV350User IDENTIFIED BY userpw QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION, CREATE ANY TABLE, DROP ANY TABLE, CREATE USER, DROP USER, UPDATE ANY TABLE, SELECT ANY TABLE,INSERT ANY TABLE TO SDEV350User;
CONN schemer/power;
CREATE TABLE imwatchingyou (
numberofeyes NUMBER,
CONSTRAINT imwatchingyou_pk PRIMARY KEY (numberofeyes)
);
INSERT INTO schemer.imwatchingyou VALUES (2);
CREATE TABLE fodder_table (
fodder_row NUMBER
);
-- policies to audit each of the five possible privileges (c)
CONN / as sysdba;
-- policy to audit creation of any table by user SDEV350User
CREATE AUDIT POLICY create_table_policy
PRIVILEGES CREATE ANY TABLE
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY create_table_policy;
-- policy to audit drop of any table by user SDEV350User
CREATE AUDIT POLICY drop_table_policy
PRIVILEGES DROP ANY TABLE
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY drop_table_policy;
-- policy to audit creation of any user by SDEV350User
CREATE AUDIT POLICY create_user_policy
PRIVILEGES CREATE USER
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY create_user_policy;
-- policy to audit drop of any user by SDEV350User
CREATE AUDIT POLICY drop_user_policy
PRIVILEGES DROP USER
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY drop_user_policy;
-- policy to audit update of any table by user SDEV350User
CREATE AUDIT POLICY update_table_policy
PRIVILEGES UPDATE ANY TABLE
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY update_table_policy;
CREATE AUDIT POLICY update_table_policy_2
ACTIONS DELETE,
INSERT,
UPDATE,
SELECT,
ALL,
SELECT
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
EVALUATE PER STATEMENT;
AUDIT POLICY update_table_policy_2;
-- auditable actions(d)
CONN SDEV350User/userpw;
-- create table audit scenario
CREATE TABLE gonego (
rowgonego NUMBER
);
-- drop table audit scenario
DROP TABLE schemer.fodder_table;
-- create user audit scenario
CREATE USER fodder IDENTIFIED BY dropme;
-- drop user audit scenario
DROP USER fodder CASCADE;
-- update table audit scenario
UPDATE schemer.imwatchingyou
SET numberofeyes = 4;
COMMIT;
-- output of the audit trail showing that each action was audited (d)
CONN / as sysdba;
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;
SELECT
event_timestamp,
dbusername,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = 'SDEV350USER'
ORDER BY event_timestamp;
HERE IS THE OUTPUT TO SPOOL WHEN RUNNING THE SCRIPT:
> SQL> @hw4-2 SQL> -- statements needed to show commands as run and to
> make the tables readable SQL> SET ECHO ON; SQL> SET LINESIZE 200; SQL>
> COLUMN event_timestamp FORMAT A30 SQL> COLUMN dbusername FORMAT A15
> SQL> COLUMN action_name FORMAT A20 SQL> COLUMN object_schema FORMAT
> A15 SQL> COLUMN object_name FORMAT A20 SQL> SQL> -- create user with
> required permissions (a&b) SQL> CREATE USER schemer IDENTIFIED BY
> power QUOTA UNLIMITED ON users;
>
> User created.
>
> SQL> GRANT CREATE SESSION, CREATE ANY TABLE, INSERT ANY TABLE TO
> schemer;
>
> Grant succeeded.
>
> SQL> CREATE USER SDEV350User IDENTIFIED BY userpw QUOTA UNLIMITED ON
> USERS;
>
> User created.
>
> SQL> GRANT CREATE SESSION, CREATE ANY TABLE, DROP ANY TABLE, CREATE
> USER, DROP USER, UPDATE ANY TABLE, SELECT ANY TABLE,INSERT ANY TABLE
> TO SDEV350User;
>
> Grant succeeded.
>
> SQL> SQL> CONN schemer/power; Connected. SQL> CREATE TABLE
> imwatchingyou ( 2 numberofeyes NUMBER, 3 CONSTRAINT
> imwatchingyou_pk PRIMARY KEY (numberofeyes) 4 );
>
> Table created.
>
> SQL> INSERT INTO schemer.imwatchingyou VALUES (2);
>
> 1 row created.
>
> SQL> CREATE TABLE fodder_table ( 2 fodder_row NUMBER 3 );
>
> Table created.
>
> SQL> SQL> -- policies to audit each of the five possible privileges
> (c) SQL> CONN / as sysdba; Connected. SQL> -- policy to audit creation
> of any table by user SDEV350User SQL> CREATE AUDIT POLICY
> create_table_policy 2 PRIVILEGES CREATE ANY TABLE 3 WHEN
> 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User''' 4
> EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY create_table_policy;
>
> Audit succeeded.
>
> SQL> SQL> -- policy to audit drop of any table by user SDEV350User
> SQL> CREATE AUDIT POLICY drop_table_policy 2 PRIVILEGES DROP ANY
> TABLE 3 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') =
> ''SDEV350User''' 4 EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY drop_table_policy;
>
> Audit succeeded.
>
> SQL> SQL> -- policy to audit creation of any user by SDEV350User SQL>
> CREATE AUDIT POLICY create_user_policy 2 PRIVILEGES CREATE USER
> 3 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') =
> ''SDEV350User''' 4 EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY create_user_policy;
>
> Audit succeeded.
>
> SQL> SQL> -- policy to audit drop of any user by SDEV350User SQL>
> CREATE AUDIT POLICY drop_user_policy 2 PRIVILEGES DROP USER 3
> WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''
> 4 EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY drop_user_policy;
>
> Audit succeeded.
>
> SQL> SQL> -- policy to audit update of any table by user SDEV350User
> SQL> CREATE AUDIT POLICY update_table_policy 2 PRIVILEGES UPDATE
> ANY TABLE 3 WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') =
> ''SDEV350User''' 4 EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY update_table_policy;
>
> Audit succeeded.
>
> SQL> SQL> CREATE AUDIT POLICY update_table_policy_2 2 ACTIONS
> DELETE, 3 INSERT, 4 UPDATE, 5 SELECT,
> 6 ALL, 7 SELECT 8 WHEN
> 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User''' 9
> EVALUATE PER STATEMENT;
>
> Audit policy created.
>
> SQL> AUDIT POLICY update_table_policy_2;
>
> Audit succeeded.
>
> SQL> SQL> -- auditable actions(d) SQL> CONN SDEV350User/userpw;
> Connected. SQL> -- create table audit scenario SQL> CREATE TABLE
> gonego ( 2 rowgonego NUMBER 3 );
>
> Table created.
>
> SQL> SQL> -- drop table audit scenario SQL> DROP TABLE
> schemer.fodder_table;
>
> Table dropped.
>
> SQL> SQL> -- create user audit scenario SQL> CREATE USER fodder
> IDENTIFIED BY dropme;
>
> User created.
>
> SQL> SQL> -- drop user audit scenario SQL> DROP USER fodder CASCADE;
>
> User dropped.
>
> SQL> SQL> -- update table audit scenario SQL> UPDATE
> schemer.imwatchingyou 2 SET numberofeyes = 4;
>
> 1 row updated.
>
> SQL> SQL> COMMIT;
>
> Commit complete.
>
> SQL> SQL> -- output of the audit trail showing that each action was
> audited (d) SQL> CONN / as sysdba; Connected. SQL> EXEC
> DBMS_AUDIT_MGMT.flush_unified_audit_trail;
>
> PL/SQL procedure successfully completed.
>
> SQL> SELECT 2 event_timestamp, 3 dbusername, 4
> action_name, 5 object_schema, 6 object_name 7 FROM
> unified_audit_trail 8 WHERE dbusername = 'SDEV350USER' 9 ORDER
> BY event_timestamp;
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.22.05.615000 AM SDEV350USER CREATE TABLE
> SDEV350USER GONEGO
> 15-OCT-17 10.22.05.662000 AM SDEV350USER DROP TABLE
> SCHEMER FODDER_TABLE
> 15-OCT-17 10.22.05.693000 AM SDEV350USER CREATE USER
> FODDER
> 15-OCT-17 10.22.05.787000 AM SDEV350USER DROP USER
> FODDER
>
>
> SQL> @scratch SQL> CONN / as sysdba; Connected. SQL> SQL> SELECT 2
> event_timestamp, 3 dbusername, 4 action_name, 5
> object_schema, 6 object_name 7 FROM unified_audit_trail 8
> WHERE object_schema = 'SCHEMER' 9 ORDER BY event_timestamp;
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.22.05.256000 AM SCHEMER CREATE TABLE
> SCHEMER IMWATCHINGYOU
> 15-OCT-17 10.22.05.287000 AM SCHEMER CREATE TABLE
> SCHEMER FODDER_TABLE
> 15-OCT-17 10.22.05.662000 AM SDEV350USER DROP TABLE
> SCHEMER FODDER_TABLE
>
>
> SQL> SQL> SELECT 2 event_timestamp, 3 dbusername, 4
> action_name, 5 object_schema, 6 object_name 7 FROM
> unified_audit_trail 8 WHERE dbusername = 'SDEV350USER' 9 ORDER
> BY event_timestamp;
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.22.05.615000 AM SDEV350USER CREATE TABLE
> SDEV350USER GONEGO
> 15-OCT-17 10.22.05.662000 AM SDEV350USER DROP TABLE
> SCHEMER FODDER_TABLE
> 15-OCT-17 10.22.05.693000 AM SDEV350USER CREATE USER
> FODDER
> 15-OCT-17 10.22.05.787000 AM SDEV350USER DROP USER
> FODDER
>
>
> SQL> SQL> SELECT 2 event_timestamp, 3 dbusername, 4
> action_name, 5 object_schema, 6 object_name 7 FROM
> unified_audit_trail 8 ORDER BY event_timestamp;
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.21.40.319000 AM SYS EXECUTE
> SYS DBMS_AUDIT_MGMT
> 15-OCT-17 10.22.05.256000 AM SCHEMER CREATE TABLE
> SCHEMER IMWATCHINGYOU
> 15-OCT-17 10.22.05.287000 AM SCHEMER CREATE TABLE
> SCHEMER FODDER_TABLE
> 15-OCT-17 10.22.05.335000 AM SYS CREATE AUDIT POLICY
> SYS CREATE_TABLE_POLICY
> 15-OCT-17 10.22.05.350000 AM SYS AUDIT
> SYS CREATE_TABLE_POLICY
> 15-OCT-17 10.22.05.365000 AM SYS CREATE AUDIT POLICY
> SYS DROP_TABLE_POLICY
> 15-OCT-17 10.22.05.381000 AM SYS AUDIT
> SYS DROP_TABLE_POLICY
> 15-OCT-17 10.22.05.397000 AM SYS CREATE AUDIT POLICY
> SYS CREATE_USER_POLICY
> 15-OCT-17 10.22.05.412000 AM SYS AUDIT
> SYS CREATE_USER_POLICY
> 15-OCT-17 10.22.05.428000 AM SYS CREATE AUDIT POLICY
> SYS DROP_USER_POLICY
> 15-OCT-17 10.22.05.443000 AM SYS AUDIT
> SYS DROP_USER_POLICY
>
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.22.05.475000 AM SYS CREATE AUDIT POLICY
> SYS UPDATE_TABLE_POLICY
> 15-OCT-17 10.22.05.490000 AM SYS AUDIT
> SYS UPDATE_TABLE_POLICY
> 15-OCT-17 10.22.05.522000 AM SYS CREATE AUDIT POLICY
> SYS UPDATE_TABLE_POLICY_
>
> 2
>
> 15-OCT-17 10.22.05.538000 AM SYS AUDIT
> SYS UPDATE_TABLE_POLICY_
>
> 2
>
> 15-OCT-17 10.22.05.615000 AM SDEV350USER CREATE TABLE
> SDEV350USER GONEGO
> 15-OCT-17 10.22.05.662000 AM SDEV350USER DROP TABLE
> SCHEMER FODDER_TABLE
> 15-OCT-17 10.22.05.693000 AM SDEV350USER CREATE USER
> FODDER
>
>
> EVENT_TIMESTAMP DBUSERNAME ACTION_NAME
> OBJECT_SCHEMA OBJECT_NAME
>
> ------------------------------ --------------- -------------------- --------------- -------------------- 15-OCT-17 10.22.05.787000 AM SDEV350USER DROP USER
> FODDER
> 15-OCT-17 10.22.05.850000 AM SYS EXECUTE
> SYS DBMS_AUDIT_MGMT
>
>
> 20 rows selected.
>
> SQL> spool out
Best Answer
If you are able to connect with this:
Then
SYS_CONTEXT('USERENV', 'SESSION_USER')returnsSDEV350USER, and notSDEV350User, so your policies do not work.Your other policies are non-functional as well. Those events are audited because they are part of the built-in
ORA_SECURECONFIGpolicy which is enabled by default.Just log in with
SDEV350Userand run the below to verify:select sys_context('userenv', 'session_user') from dual;Replace:
with: