【聲明】文章僅供學(xué)習(xí)交流，觀點(diǎn)代表個(gè)人，與任何公司無關(guān)。
編輯|SQL和數(shù)據(jù)庫技術(shù)(ID:SQLplusDB)
收集Oracle數(shù)據(jù)庫內(nèi)存相關(guān)的信息
【Oracle】ORA-32017和ORA-00384錯(cuò)誤處理
【Oracle】設(shè)置FGA(Fine-Grained Audit)細(xì)粒度審計(jì)
FGA(Fine-Grained Audit)細(xì)粒度審計(jì)是Oracle提供的一種數(shù)據(jù)庫審計(jì)方法，用于創(chuàng)建定制的審計(jì)設(shè)置。
可以通過調(diào)用Oracle的包DBMS_FGA.ADD_POLICY創(chuàng)建policy(審計(jì)策略)。
下面是一個(gè)簡單的測試。
- 設(shè)置FGA審計(jì)策略
conn scott/tiger
BEGIN DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_ENAME',
audit_column => 'ENAME',
enable => TRUE,
statement_types => 'DELETE,UPDATE,INSERT,SELECT',
audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts =>DBMS_FGA.ANY_COLUMNS);
END;
/
２．查看設(shè)置的審計(jì)策略
set pages 1000
set line 200
set trims on
col OBJECT_SCHEMA for a12
col OBJECT_NAME for a15
col POLICY_NAME for a30
col POLICY_COLUMN for a25
col AUDIT_TRAIL for a12
select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,ENABLED,SEL,INS,UPD,DEL,AUDIT_TRAIL,POLICY_COLUMN
from ALL_AUDIT_POLICIES
order by OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME;
- 重啟數(shù)據(jù)庫
conn / as sysdba
shutdown immediate
startup
4.執(zhí)行 expdp導(dǎo)出數(shù)據(jù)（成功導(dǎo)出12條數(shù)據(jù)）
expdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log REUSE_DUMPFILES=Y TABLES=SCOTT.EMP
- 刪除數(shù)據(jù)trunccate
conn scott/tiger
truncate table emp;
6.執(zhí)行impdp導(dǎo)入數(shù)據(jù)（成功導(dǎo)入12條數(shù)據(jù)）
impdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY
SQL> host impdp scott/tiger directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY
Import: Release 19.0.0.0.0 - Production on 星期日 1月 21 09:41:07 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
連接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
已成功加載/卸載了主表 "SCOTT"."SYS_IMPORT_TABLE_01"
啟動 "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=DUMP_DIR DUMPFILE=emp.dmp LOGFILE=DUMP_DIR:emp.log TABLES=SCOTT.EMP CONTENT=DATA_ONLY
處理對象類型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 導(dǎo)入了 "SCOTT"."EMP" 9.132 KB 12 行
作業(yè) "SCOTT"."SYS_IMPORT_TABLE_01" 已于 星期日 1月 21 09:41:15 2024 elapsed 0 00:00:07 成功完成
7.確認(rèn)FGA_LOG$的審計(jì)結(jié)果（FG審計(jì)log中輸出import的操作記錄）
conn sys/PW
set lin 2000
set pages 1000
col DB_USER format a10
col OS_USER format a10
col POLICY_NAME format a20
col SQL_TEXT format a100
select to_char(TIMESTAMP,'YYYYMMDDHH24MISS'),DB_USER,OS_USER,POLICY_NAME,SQL_TEXT
from dba_fga_audit_trail
order by timestamp desc;
結(jié)果例：
SQL> select to_char(TIMESTAMP,'YYYYMMDDHH24MISS'),DB_USER,OS_USER,POLICY_NAME,SQL_TEXT
2 from dba_fga_audit_trail
3 order by timestamp desc;
TO_CHAR(TIMESTAMP,'YYYYMMDDH DB_USER OS_USER POLICY_NAME SQL_TEXT
---------------------------- ---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
20240121094115 SCOTT OracleServ EMP_ENAME INSERT /*+ APPEND PARALLEL("EMP",1)+*/ INTO RELATIONAL("SCOTT"."EMP" NOT XMLTYPE) ("EMPNO","ENAME","
iceORCL JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
FROM "ET$01EF9D620001" KU$
8.測試結(jié)束，刪除策略。文章來源：http://www.zghlxwxcb.cn/news/detail-819776.html
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_ENAME');
END;
/
參考
32 Auditing Specific Activities with Fine-Grained Auditing
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/auditing-specific-activities-fine-grained-auditing1.html#GUID-B706FF6F-13A6-4944-AFCB-29971F5076FD文章來源地址http://www.zghlxwxcb.cn/news/detail-819776.html
到了這里，關(guān)于【Oracle】設(shè)置FGA(Fine-Grained Audit)細(xì)粒度審計(jì)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
