Oracle FGA的示例分析

蜗牛互联网技术资讯2021-12-132230

小编给大家分享一下Oracle FGA的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

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