9i fine grain audit
The query statement of the table is not detected by a normal trigger unless the database audit is turned on, but from ORACLE9I, a DBMS_FGA package is provided, and a single table can be audited and query the audit information. This is fine-grained audit. But the audit process of this package requires the database to run in CBO optimization mode. If not, there may be unexpected results, which requires an analysis of the auditing table. The following will be a simple introduction to this package, this package has four processes, first introduces the first process in this package: procedure add_policy (Object_schema in varcha2: = null, object_name in varchar2, policy_name in varcha2, audit_condition iN VARCHAR2: = '1 = 1', audit_column iN VARCHAR2: = NULL, handler_schema iN VARCHAR2: = NULL, handler_module iN VARCHAR2: = NULL, enable iN BOOLEAN: = TRUE); object_schema: name of the user to be audited, default This user object_name: The name of the object to be audited policy_name: This audit strategy name, each audit has a name, distinguishable, distinguished, Audit, Audit_Condition: Audit Conditions (the phonet), the default, all, if specified A = 1 Then when the result of the returned result is explicitly included (specifying this field) or implicitly containing (not specifying this field) A = 1 line, the query statement will be audited. Audit_column: Represents the audit those columns, default all, if you specify the column, then only the select or where specifies the column, it is only audited, if it is SELECT * is also implicitly specified, because * itself contains any column . Note: If the audit_column and audit_condition are specified, it must be satisfied, that is, the two conditions are one and the relationship rather than or the relationship. Handler_schema: We can also specify when performing a policy, which is a stored procedure for a specified user. Here is the owner of the stored procedure. . Obviously, the role of the package is to add an audit strategy, object_schema, object_name uniquely determine an audit policy with policy_name, and light looks that policy_name cannot uniquely determine a policy name, different objects, and the policy name can be the same. Let us look at the second procedure PROCEDURE drop_policy (object_schema IN VARCHAR2: = NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2); meaning object_schema, object_name policy_name and with a first packet, as long as the three specified parameters, can be uniquely Delete a specified audit policy.
Third, the second process enable_policy, the second process of disable_policy is similar to the Object_Schema, Object_Name and Policy_name, which is useful for the audit strategy, so it is useful for multiple use of the same audit strategy, and it is not necessary to delete it. When you don't have to use disable, you can use it, as long as enable, if you enable your handler_schema with your handler_module, you need a stored procedure under the handler_schema user, assume that the name is sp_chk_mytable, this process can be similar to: Create PROCEDURE sp_chk_mytable (p_object_schema VARCHAR2, p_object_name VARCHAR2, p_policy_name VARCHAR2) AS BEGIN INSERT INTO audit $ proc (login_user, audit_time, ip_address, audsid, object_schema, object_name, policy_name) VALUES (ora_login_user, sysdate, sys_context ( 'USERENV', 'iP_ADDRESS') , userenv ( 'SESSIONID'), p_object_schema, p_object_name, p_policy_name); EXCEPTION WHEN OTHERS THENsp_write_log ( 'Audit Exception:' || SQLERRM); END sp_chk_mytable; of course, before creating the need for a further process audit $ proc table, Because it is not the focus of our discussion, this will not say much, the stored procedure can be rewritten according to your own requirements in order to obtain information. Here we will use an example to illustrate the use of the DBMS_FGA package, in this example, does not include the execution specified stored procedure, that is, only the audit of the table is an example.
First, we assume that we have table test, including recorded as SQL> Select * from manager.test; A b - --1 22 33 44 55 66 76 Rows SELECTED analysis of the table, letting it use CBO optimization mode SQL> Analyze Table test compute statistics; Table analyzed to check previous audit strategy and audit logs SQL> select t.object_schema, t.object_name, t.policy_name from dba_audit_policies t; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME --------------- -------------------------------------------- ----- ------------------------- SQL> SELECT T.Object_SChema, T.Object_name, T.Policy_Name, T.SQL_Text from DBA_FGA_Audit_TRAIL T; Object_Schema Object_name policy_name SQL_Text ----------------------------------- ------------------------------------------------------------------------------------------- ------ DBA_AUDIT_POLICIES Used to store audit strategies, the base table is Sys.fga $, and DBA_FGA_AUDIT_TRAIL is used to store audit logs, which is the SQL statement, and its base Table is SYS. FGA_LOG $, its log can Hand-deleted. Through the above queries, we can also see that there is no audit strategy, no audit logs now, we add an audit strategy in the Manager.Test table SQL> begin2 dbms_fga.add_policy (Object_schema => 'Manager', 3 Object_name => 'test', 4 policy_name => 'chK_test', 5 audit_condition => 'a = 1', 6 audit_column => 'b', 7 enable => true); 8 end; 9 / pl / sql procedure successful solte we again Query DBA_Audit_Policies, you can find this audit strategy. SQL> SELECT T.Object_SChema, T. Object_name, T.Policy_Name, T.Enabled from dba_audit_policies t; Object_schema Object_name policy_name enabled -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- Manager test chK_test yes then let us see how the audit strategy takes effect, assuming that we have implemented some statements, these statements There is a very common representative: SQL> Select count (*) from test where a = 2; count (*) ------------ 1 can be seen, the result of the query returns clearly A = 1 line, so the query cannot be audited.
SQL> Select Count (*) from test where a = 1; count (*) ---------- 1 Some people say this statement contains A = 1, it is audited, in fact, our One of the audit conditions is that the audit column must have B, and count (*) is not to include B, so the statement is still not audited. SQL> SELECT Count (*) from test where b = 2; count (*) ---------- 1 This statement is audited, because already included column B in WHERE, and b = 2 is just It is a row of A = 1, so A = 1 is implicitly included. SQL> Select * from test where a = 1; a b - --1 2 This statement is definitely audited, all columns must include audit columns B, predicate conditions are just A = 1. SQL> SELECT A from test where a - 2 This statement is definitely not audited, typical not satisfies SQL> SELECT B from test where b = 2; b - 2 This statement is also audited ,why? This statement includes audit columns, and implicitly contains A = 1 (because A = 1 and B = 2 is the same line) SQL> Select B from test where b = 4; b - 4 This statement is not audited, because though Meet the conditions of the audit column, but there is no explicit or implicitly containing A = 1, but if this statement is audited, if this statement is audited, please confirm whether it is analyzed, whether the query is used by CBO Optimize the plan. Based on the above results, the above statements should be audited 3, check the SQL> select t.object_schema, t.object_name, t.policy_name, t.sql_text from Dba_Fga_Audit_Trail t; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT ------- ------------------------------------ ------------------------------------------- Manager test chK_test selection * from Test WHERE A = 1Manager Test Chk_Test Select B from test hak_test select count (*) from test where b = 2 For more and more audit records, we must manually maintain, delete records without reference value, we You can run the following query to delete the audit record (if you need Delete Any Table, execute under SYS): delete from sys.fga_log $ Ordelete from dba_fga_audit_trail here, everyone knows how to audit the SELECT statement of the audit table, with ordinary audit Like the DML trigger, the audit is performed over many tables will seriously affect performance. However, in a specific case, if you want to track the SELECT statement that is easy to optimize, it is still possible. 10g fine granular audit
In Oracle 9i Database, this policy can only audit the SELECT statement. However, in Oracle Database 10g, you can extend it so that it contains INSERT, UPDATE, and DELETE. You can implement this purpose by specifying a new parameter: statement_types => 'INSERT, UPDATE, DELETE, SELECT'
This parameter will enable audits on all included statements. You may even consider creating a separate policy for each statement type, which will allow you to enable and disable policies - especially, controlling the creation of audit clues to manage their occupied space.
reference
Http://otn.racle.com/global/cn/oramag/webcolumns/2003/techarticles/nanda_fga.html
Http://otn.racle.com/global/cn/pub/articles/nanda_fga_pt2.html
Http://otn.oracle.com/global/cn/pub/articles/nanda_fga_pt3.html
