9i and 10 Gb fine-grained Audit

9i fine-grained Audit

Table query statements cannot be detected using common triggers. Unless database audit is enabled, a dbms_fga package is provided starting from Oracle9i, you can audit a single table online and query audit materials. This is fine-grained audit. However, the audit process of this package requires the database to run in the CBO optimization mode. If not, unexpected results may occur. Therefore, we need to analyze the audit table.
The following describes how to use this package. There are four processes in this package. First, we will introduce the first process in this package:
Procedure add_policy (object_schema in varchar2: = NULL,
Object_name in varchar2,
Policy_name in varchar2,
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. The default value is the current user.
Object_name: name of the object to be audited
Policy_name: Policy name for this audit. Each audit has a name, which is different from other audit policies.
Audit_condition: Audit condition (predicate verb), all by default. If a = 1 is specified, when the returned result set explicitly contains (specifies this field) or implicitly contains (does not specify this field) when a = 1, the query statement is audited.
Audit_column: indicates the columns to be audited. All columns are audited by default. If a column is specified, the column is audited only when select or where is specified, if select * is used, this column is implicitly specified, because * itself contains any column.
NOTE: If both audit_column and audit_condition are specified, the audit can be performed only when both conditions are met. That is to say, the two conditions are a parallel relationship rather than a or relationship.
Handler_schema: We can also specify to execute a stored procedure for a specified user during policy execution. Here, it is the owner of the stored procedure.
Handler_module: Specifies the name of the stored procedure to be executed when the policy is executed.
Enable: Determine whether the audit policy takes effect immediately.
Obviously, the role of this package is to add an audit policy. object_schema, object_name, and policy_name uniquely determine an audit policy. The only difference is that policy_name cannot uniquely determine a Policy Name and different objects, the policy name can be the same.
Let's take a look at the second process.
Procedure drop_policy (object_schema in varchar2: = NULL,
Object_name in varchar2,
Policy_name in varchar2 );
Object_schema, object_name, and policy_name have the same meaning as the first package. You only need to specify these three parameters to delete a specified audit policy.
The third and fourth processes are similar to enable_policy and disable_policy. The second process is to disable or enable audit policies based on object_schema, object_name, and policy_name, this method is useful for using the same audit policy multiple times. You do not need to delete the audit policy and create a new one. If you do not need it, you only need to disable it. When you use it, you only need to enable it.
If handler_schema and handler_module are enabled, a stored procedure under the handler_schema user is required. Assume that the name is sp_chk_mytable. The procedure can be similar:
Create procedure sp_chk_mytable (
P_object_schema varchar2,
P_object_name varchar2,
P_policy_name varchar2)
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 then
Sp_write_log ('audit exception: '| sqlerrm );
End sp_chk_mytable;
Of course, you also need to create an audit $ proc table before creating this process, because it is not the focus of our discussion. This is not much to say. The stored procedure can be rewritten as required, to obtain information.
Here we will use an example to describe how to use the dbms_fga package. In this example, the execution of the specified stored procedure is not included. That is to say, only the table audit is used as an example.
First, let's assume that we have a table named test, which contains records
SQL> select * From Manager. test;
A B
----
1 2
2 3
3 4
4 5
5 6
6 7
6 rows selected
Analyze the table and use the CBO optimization mode.
SQL> analyze table test compute statistics;
Table analyzed
Check previous audit policies 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 is used to store audit policies. Its base table is sys. FGA $, while dba_fga_audit_trail is used to store audit logs. That is, the SQL statements are executed, and its base table is sys. fga_log $, whose logs can be deleted manually. Through the above query, we can also see that there were no audit policies or audit logs in the past
Now, we add an Audit Policy in the manager. test table.
SQL> begin
2 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 successfully completed
We can query dba_audit_policies to find this audit policy.
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
Let's take a look at how the audit policy takes effect. Assuming that we execute the following statements, these statements are generally representative:
SQL> select count (*) from test where a = 2;
Count (*)
------------
1
As you can see, the returned result set of this query obviously does not have rows a = 1. Therefore, this query cannot be audited.
SQL> select count (*) from test where a = 1;
Count (*)
----------
1
Some people say that this statement contains a = 1, so it should be audited. Otherwise, another audit condition is that the audit column must have B, count (*) this statement is not audited because it does not contain B.
SQL> select count (*) from test where B = 2;
Count (*)
----------
1
This statement is audited because column B is already included in the WHERE clause, and column B = 2 is exactly the row of column A = 1, so column A = 1 is implicitly included.
SQL> select * from test where a = 1;
A B
----
1 2
This statement must be audited. All columns must contain audit Column B, and the predicate condition is exactly a = 1.
SQL> select a from test where a = 2;
A
--
2
This statement is certainly not audited, and a typical statement is not satisfied.
SQL> select B from test where B = 2;
B
--
2
This statement is also audited. Why? This statement contains the audit column and implicitly contains a = 1 (because a = 1 is the same row as B = 2)
SQL> select B from test where B = 4;
B
--
4
This statement is not audited, because although the audit column conditions are met, but it does not explicitly or implicitly contain a = 1, but if in your environment, if this statement is audited, make sure that you have analyzed the table and whether the query is a CBO optimization plan used.
Based on the above results, the preceding three statements should be audited. Check whether the statement is correct.
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 select * from test where a = 1
Manager test chk_test select B from test where B = 2
Manager test chk_test select count (*) from test where B = 2
For more and more audit records, we must manually maintain them and delete records with no reference value. We can run the following query to delete audit records (delete any table permission or execute it under sys):
Delete from SYS. fga_log $
Or
Delete from dba_fga_audit_trail
At this point, we should understand how to audit the SELECT statement of a table. Like common auditing and DML triggers, auditing too many tables will seriously affect the performance. However, under certain circumstances, it is still possible to track the SELECT statement of a table to facilitate optimization.

 

10 Gb fine-grained Audit

In Oracle 9i Database, this policy can only audit select statements. However, in Oracle Database 10g, you can extend it to include insert, update, and delete. You can achieve this by specifying a new parameter:

Statement_types => 'insert, update, delete, select'

This parameter enables audit for all included statement types. You may even consider creating separate policies for each statement type, which allows you to enable and disable policies at will-in particular, control the creation of audit trails to manage the space they occupy.

 

Reference

Http://otn.oracle.com/global/cn/oramag/webcolumns/2003/techarticles/nanda_fga.html

Http://otn.oracle.com/global/cn/pub/articles/nanda_fga_pt2.html

Http://otn.oracle.com/global/cn/pub/articles/nanda_fga_pt3.html