Test Case 1 of DBMS_FGA package in Oracle fine-grained audit (FGA)

Oracle fine-grained audit (FGA) DBMS_FGA package experiment case 1 starts from Oracle9i, DBMS_FGA can be used to audit select statements of a specified table, but can only audit select statements in 9i, DBMS_FGA can be used to demonstrate the audit function of select statements under the audit function of DML in 10 Gb. www.2cto.com ① suppose we have table t, the record is [SQL] sys @ ORCL> select * from hr. t; id name ---------- ------------------ 1 a 2 B 3 c 4 d 5 e ② analyze the table and use the CBO optimization mode [SQL] sys @ ORCL> analyze table hr. t compute statistics; Table analyzed. ③ check previous audit policies and audit logs [SQL] sys @ ORCL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME from dba_audit_policies; no rows selected sys @ ORCL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, SQL _TEXT from orders; no rows selected dba_audit_policies: used to store audit policy policies: it is used to store audit logs, that is, the SQL statements are executed through the above queries. We can also see that there were no audit policies or audit logs www.2cto.com previously. Now we are at hr. add an audit policy to table t [SQL] sys @ ORCL> begin 2 dbms_fga.add_policy (object_schema => 'hr', 3 object_na Me => 'T', 4 policy_name => 'chk _ t', 5 audit_condition => 'id = 2', 6 audit_column => 'name ', 7 enable => TRUE, 8 statement_types => 'select, INSERT, UPDATE, DELETE '9); 10 end; 11/PL/SQL procedure successfully completed. ⑤ query dba_audit_policies again to find that this audit policy has [SQL] sys @ ORCL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, ENABLED from dba_audit_policies; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENA ------------------ ------------ ------------------------------ ---------------------------- --- Hr t CHK_T YES 6 Let's see how the audit policy takes effect. Suppose we execute the following statement [SQL] u1 @ ORCL> select count (*) from hr. t where id = 1; COUNT (*) ---------- 1 You can see that the returned result set of this query obviously does not have the row id = 2. Therefore, this query cannot be audited u1 @ ORCL> select count (*) from hr. t where id = 2; COUNT (*) ---------- 1 someone said this statement contains id = 2, so it should be audited. However, one of our audit conditions is that the audit column must have name count (*) Not including name, so this statement is still not audited u1 @ ORCL> sel Ect count (*) from hr. t where name = 'B'; COUNT (*) ---------- 1 this statement is audited because it already contains the column name in where, while name = B is exactly the row with id = 2, so id = 2 is the implicitly contained u1 @ ORCL> select * from hr. t where id = 2; id name ---------- ------------------ 2 B the statement must be audited, and all columns must contain the name of the audit column, the predicate condition is exactly id = 2 u1 @ ORCL> select id from hr. t where id = 3; ID ---------- 3 is certainly not audited. A typical statement does not meet u1 @ ORCL> select name from hr. t where name = 'B'; NAME -------------------- the statement B Also audited. Why? This statement contains the audit column and implicitly contains id = 2 (because id = 2 and name = B are in the same row) u1 @ ORCL> select name from hr. t where name = 'D'; NAME -------------------- d is not audited, because although the audit column conditions are met, however, the statement does not explicitly or implicitly contain id = 2. However, if this statement is audited in your environment, make sure that the table has been analyzed, whether the query is based on the preceding CBO optimization plan. The preceding audited statements should be three: [SQL] sys @ ORCL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, SQL _TEXT from dba_fga_audit_trail; objec polic SQL _TEXT ----------------------------------------------- ------------- Hr t CHK_T select count (*) from hr. t where name = 'B' HR T CHK_T select * from hr. t where id = 2 hr t CHK_T select name from hr. t where name = 'B' for more and more audit records, we must manually maintain and delete records with no reference value, you can run the following query to delete audit records (delete any table permission or execute it under sys): [SQL] delete from sys. fga_log $ or delete from Dba_Fga_Audit_Trail here, everyone should understand how to audit the Select statement of the table, like the common audit and DML trigger, to audit too many tables, it will seriously affect the performance. However, under certain circumstances, if you want to track the Select statement of a table for optimization, it is still possible that the audit sys will not be audited if the relevant precautions are not submitted, I used sys in my first experiment.