Oracle
Oracle 統一審計更新表
我正在嘗試審核使用者何時更新 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'''