Oracle FGA Audit

Oracle FGA Audit

You may be familiar with trigger, but Oracle also has a function named FGA. It works like trigger, but it is more powerful. its full name is Fine-Grained Audit, which is a special method of Audit. to use FGA, you only need to call the Oracle package DBMS_FGA.ADD_POLICY to create some policies (audit policies. each policy can only be used for one table or view. after the policy is created, DML operations (select, insert, update, and delete) on tables or views can be recorded. Of course, you can also add some filter conditions to only monitor some special operations.

Supplement: the so-called audit is to record any of your operations. If your operations (executing DML statements) meet the specified conditions, then you execute the SQL statements, some tables under the sys user will be recorded, and other information will be stored, such as the execution time, user name, tool used, and machine name. FGA is available in oracle 9i, but can only audit select statements in 9i. all DML operations can be audited from 10 GB

Significant differences between FGA and Triger:

1. FGA uses an autonomous transaction. Even if the DML operation is rolled back, it still does not execute rollback. The trigger will be rolled back.

2. During the update operation, the trigger can record the old value before the update and the new value after the update, while the FGA does not record the old value.

1. Usage of DBMS_FGA.ADD_POLICY

Audit policy creation syntax

DBMS_FGA.ADD_POLICY (

Object_schema VARCHAR2, -- schema name, table or view owner

Object_name VARCHAR2, -- Object Name, table or view name

Policy_name VARCHAR2, -- Audit Policy Name, which is the same as other objects in the database and must have a unique and unique name.

Audit_condition VARCHAR2, -- filtering conditions, such as which operations that meet the conditions can be selected to be recorded

Audit_column VARCHAR2, -- a column in the table, you can only record operations on a column in the table. If not specified, all columns are audited.

Handler_schema VARCHAR2, -- is the owner of the following handler_module. In fact, it can only be the user who creates the policy, and the above object_schema can be any user

Handler_module VARCHAR2, -- it can be a stored procedure or function, but it is executed when any qualified operation is detected.

Enable BOOLEAN, -- true or false indicates that the policy is enabled or disabled. If it is false, the audit is not performed.

Statement_types VARCHAR2, -- indicates which operations will be audited. You can fill in one or more operations in select, insert, update, and delete.

Audit_trail BINARY_INTEGER in default, -- there is a parameter db. xml indicates that the audited information is saved to the database or saved to the disk as an xml file.

Audit_column_opts BINARY_INTEGER in default); -- this option takes effect only when a column is specified IN audt_column. it has two options: any_columns and all_columns. Suppose the table has two columns: eno and ename, and the two columns are specified in audit_column, if any_columns is selected, any column in the operation will be recorded. If all_columns is specified, only one SQL statement can operate both columns at the same time.

 

For example, if you create a table: create table temp (eno int, ename varchar2 (30); create a policy for this table

Each policy can only target one table or view, and one table or view may correspond to multiple policies. When a table is deleted, the policy is deleted by default.

BEGIN
SYS. DBMS_FGA.ADD_POLICY (
Object_schema => 'arwen'
, Object_name => 'temp'
, Policy_name => 'fga _ TEMP'

, Audit_condition => NULL
, Audit_column => eno, ename
, Handler_schema => null
, Handler_module => null

, Enable => TRUE
, Statement_types => 'select, INSERT, UPDATE, delete'
, Audit_trail => SYS. DBMS_FGA.DB + SYS. DBMS_FGA.EXTENDED

-- DBMS_FGA.DB indicates that the record will be saved to the database, and DBMS_FGA.EXTENDED indicates that if the SQL statement contains a bound variable, it will also be recorded.

-- If you select audit_trail => SYS. DBMS_FGA.DB in this way, the bound variables are not recorded.

-- SYS. DBMS_FGA.DB + SYS. DBMS_FGA.EXTENDED is changed to SYS. DBMS_FGA.XML + SYS. DBMS_FGA.EXTENDED to save the record as an xml file.

-- The directory where the xml file is located can be viewed through show parameter AUDIT_FILE_DEST. If you want to change the directory alter system set AUDIT_FILE_DEST = directory_path DEFERRED;

, Audit_column_opts => SYS. DBMS_FGA.ALL_COLUMNS)
END;

 

View the created policy object and the operations recorded when the audit conditions are met

We can use select * from dba_objects to view objects such as tables and views. After a policy is created, we can use SELECT * FROM DBA_AUDIT_POLICIES to view the objects.

If we perform DML operations on table temp, the information will be operated to the table under the sys user, Select * from sys. dba_fga_audit_trail can be found. (Note that only the preceding settings record the information.

Save it to the database for query. If it is saved to an xml file, Select * from V $ XML_AUDIT_TRAIL)

 

2. Delete the audit policy. If you delete the policy created above

Begin

SYS. DBMS_FGA.DROP_POLICY (

Object_schema => 'arwen'

, Object_name => 'temp'

, Policy_name => 'fga _ TEMP'

);

End;

3. Use handler_module

 

If you want to perform further processing when auditing some operations, such as writing the information to your own log, or sending an email to notify the relevant personnel. you must use the handler_module function when creating a policy, and specify a stored procedure for corresponding processing. suppose I create a stored procedure temp_handler

Create or replace procedure temp_handler

(V_object_schema VARCHAR2

, V_object_name VARCHAR2

, V_policy_name VARCHAR2

)

IS

V_temp varchar2 (30 );

Begin

Null;

End temp_handler;

-- The stored procedure here is a bit special. It must contain the v_object_schema VARCHAR2, v_object_name VARCHAR2, v_policy_name VARCHAR2 parameters. if you write a general stored procedure directly, an error occurs. the stored procedure is called in the policy.

 

BEGIN
SYS. DBMS_FGA.ADD_POLICY (
Object_schema => 'arwen'
, Object_name => 'temp'
, Policy_name => 'fga _ TEMP'

, Audit_condition => NULL
, Audit_column => eno, ename
, Handler_schema => 'arwen' -- note that the user here can only create this policy. If it is another user name, an error occurs.
, Handler_module => 'temp _ handler'

, Enable => TRUE
, Statement_types => 'select, INSERT, UPDATE, delete'
, Audit_trail => SYS. DBMS_FGA.DB + SYS. DBMS_FGA.EXTENDED

, Audit_column_opts => SYS. DBMS_FGA.ALL_COLUMNS)
END;