Oracle FGA

chenoracle發表於2017-07-12
Oracle FGA(Fine-Grained Auditing) 


一:建立FGA;
Example 9-21 Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy

SQL>
BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'chk_hr_employees',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',
   audit_trail        =>  DBMS_FGA.DB+DBMS_FGA.EXTENDED);
END;
/

二:檢視已經建立的FGA;
At this point, if you query the DBA_AUDIT_POLICIES view, you will find the new policy listed:
SQL> SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES;
POLICY_NAME
------------------------------
CHK_HR_EMPLOYEES

三:生成審計資料
Afterwards, any of the following SQL statements log an audit event record.
SQL> SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500;
SQL> SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50;
SQL> DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000;
SQL> select * from employees;

四:檢視審計資訊
SQL> select lsqltext from sys.fga_log$;
LSQLTEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500
SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50
DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000
select * from employees

SQL> select SQL_TEXT from dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500
SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50
DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000
select * from employees

五:刪除chk_hr_employees審計
SQL> exec DBMS_FGA.DROP_POLICY(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'chk_hr_employees');
---SQL> exec DBMS_FGA.DISABLE_POLICY_POLICY(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'chk_hr_employees');
---SQL> exec DBMS_FGA.ENABLE_POLICY(object_schema=>'HR',object_name=>'EMPLOYEES',policy_name=>'chk_hr_employees');


詳細資訊見官方文件:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_fga.htm#ARPLS015


歡迎關注我的微信公眾號"IT小Chen",共同學習,共同成長!!!

Oracle FGA

Oracle FGA



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2141988/,如需轉載,請註明出處,否則將追究法律責任。

相關文章