Oracle

Oracle 統一審計更新表

  • October 15, 2017

我正在嘗試審核使用者何時更新 Oracle 中的表。我正在執行的腳本如下。所有其他審計工作正常,但不是 UPDATE 策略。我創建了兩個策略來嘗試兩種不同的方法來擷取它。

-- 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;

以下是執行腳本時的 Spool 輸出:

> 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

如果您能夠與此連接:

CONN SDEV350User/userpw;

然後SYS_CONTEXT('USERENV', 'SESSION_USER')返回SDEV350USER,而不是SDEV350User,因此您的策略不起作用。

您的其他政策也不起作用。ORA_SECURECONFIG這些事件會被審計,因為它們是預設啟用的內置策略的一部分。

只需登錄SDEV350User並執行以下命令即可驗證:

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

代替:

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

和:

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

引用自:https://dba.stackexchange.com/questions/188515