技術專欄
首先要提醒的是Traditional auditing (pre 12c) 在 Oracle 23ai 版本上已經不再支持 [1]，因此當您嘗試在 Oracle 23ai 版本上執行以下 Traditional auditing 語法時，將提示 ORA-46401 錯誤：
|
SQL> AUDIT ALL BY audit_test BY ACCESS;
Error starting at line : 1 in command - AUDIT ALL BY audit_test BY ACCESS Error report - ORA-46401: No new traditional AUDIT configuration is allowed. Traditional auditing is desupported, and you should use unified auditing in its place. |
目前在 Oracle 23ai 版本上引入了 column-level 的審計功能，您可以對 table 和 view 的單欄位來創建審計策略，這樣設計的好處是可以只針對感興趣的欄位來進行審計，以避免在 audit trail 中記錄不需要的內容。
針對 table 和 view 欄位的以下操作可以進行審計 [2]：
|
ALL, ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, SELECT, UPDATE |
請參考以下演示：
1. 創建測試表
|
drop table if exists audit_test_tab purge;
create table audit_test_tab ( id number generated always as identity, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10) ); |
2. 創建一個審計策略，如下：
|
noaudit policy test_audit_policy; drop audit policy test_audit_policy;
create audit policy test_audit_policy actions update(col1, col2) on admin.audit_test_tab, select(col2) on admin.audit_test_tab container = current;
audit policy test_audit_policy; |
此審計策略將記錄用戶對 COL1 與 COL2 欄位的 UPDATE 操作，以及對 COL2 欄位的 SELECT 操作。
3. 對該表格進行以下操作
|
-- 以下操作不會產生審計記錄，因為不被審計策略所規範 insert into audit_test_tab (col1, col2) values ('apple2', 'banana2');
update audit_test_tab set col3 = 'pear' where col3 is null;
commit;
select id from audit_test_tab;
-- 以下操作會產生審計記錄 update admin.audit_test_tab set col1 = 'apple3' where col1 = 'apple2';
update admin.audit_test_tab set col2 = 'banana3' where col2 = 'banana2';
select col2 from admin.audit_test_tab; |
4. 查看審計記錄
|
column event_timestamp format a30 column dbusername format a10 column action_name format a20 column object_schema format a10 column object_name format a20 column sql_text format a40
select event_timestamp, dbusername, action_name, object_schema, object_name, sql_text from unified_audit_trail where object_name = 'AUDIT_TEST_TAB' order BY event_timestamp;
EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCHEMA OBJECT_NAME SQL_TEXT 11-JUL-24 04.17.58.371126000 PM ADMIN UPDATE ADMIN AUDIT_TEST_TAB "update admin.audit_test_tab set col1 = :"SYS_B_0" where col1 = :"SYS_B_1" 11-JUL-24 04.17.58.459981000 PM ADMIN UPDATE ADMIN AUDIT_TEST_TAB "update admin.audit_test_tab set col2 = :"SYS_B_0" where col2 = :"SYS_B_1" 11-JUL-24 04.17.58.990472000 PM ADMIN SELECT ADMIN AUDIT_TEST_TAB select col2 from admin.audit_test_tab |
