0

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
0

If you are able to connect with this:

CONN SDEV350User/userpw;

Then SYS_CONTEXT('USERENV', 'SESSION_USER') returns SDEV350USER, and not SDEV350User, 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_SECURECONFIG policy which is enabled by default.

Just log in with SDEV350User and run the below to verify:

select sys_context('userenv', 'session_user') from dual;

Replace:

'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350User'''

with:

'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SDEV350USER'''
  • Thanks, so much! That one really had me going. If I hadn't seen anything being audited, then I would have thought about that, but since some rows were being returned I thought the others were working. – xygenyx Oct 15 '17 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.