Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use the FGA function in Oracle", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn how to use the FGA function in Oracle "this article.

You may be familiar with trigger, but Oracle also has a function called FGA, which is similar to trigger, but more powerful. Its full name is Fine-Grained Audit, which is a special way of Audit. To use FGA, simply call Oracle's package DBMS _ FGA.ADD_POLICY to create some policy (audit policies). Each policy can only be for one table or view. After establishing the policy, all DML actions on the table or view (select,insert,update,delete can be recorded, of course, you can also add some filter criteria to monitor only some special actions.

Add: the so-called audit is to record any of your actions. If your actions (executing DML statements) meet the specified conditions, the sql statements you execute will be recorded in some tables under sys users, as well as your other information, such as execution time, user name, through what tool, machine name and so on.

FGA is available in oracle 9i, but only select statements can be audited in 9i. All DML operations can be audited from 10g.

1. The usage of package DBMS_FGA.ADD_POLICY

Syntax for creating an audit policy

DBMS_FGA.ADD_POLICY (

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

Object_name VARCHAR2,-- object name, table or view name

Policy_name VARCHAR2,-- audit policy name, which, like other objects in the database, requires a unique name that is not duplicated.

Audit_condition VARCHAR2,-- filter criteria such as which qualified actions can be selected to be recorded

Audit_column VARCHAR2,-- A column in a table that records operations on only one column in the table. If you do not specify all columns that represent the audit

Handler_schema VARCHAR2, which is the owner of the handler_module below, can only be the user who created the policy, and the object_schema above can be any user

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

Enable BOOLEAN,-- true or false indicates that policy is on or off, while false means no audit

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

Audit_trail BINARY_INTEGER IN DEFAULT,-- has a parameter db,xml indicating that the audited information is saved to the database or saved to disk in the form of a xml file

Audit_column_opts BINARY_INTEGER IN DEFAULT);-- this option actually works only if a column is specified in audt_column. It has two options of any_columns,all_columns. If there are two columns of eno,ename in the table and these two columns are specified in the audit_column, then selecting any_columns means that any one of the columns will be recorded as long as the operation is performed. If all_columns is specified here, it means that only one sql statement operates on both columns.

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

Each policy can only be for one table or view, and a table or view may correspond to multiple policies. When the 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 it will also be recorded if there are bound variables in the sql statement.

If this is the case, audit_trail = > SYS.DBMS_FGA.DB means that the binding variable will not be recorded.

Changing SYS.DBMS_FGA.DB+SYS.DBMS_FGA.EXTENDED to SYS.DBMS_FGA.XML+SYS.DBMS_FGA.EXTENDED means that the record is saved as a 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 actions recorded when the audit criteria are met

We can view table,view and other objects through select * from dba_objects. After creating a similar policy, we can view it through SELECT * FROM DBA_AUDIT_POLICIES.

If we DML the table temp, that information will be manipulated to the table under the sys user and can be found by Select* from sys.dba_fga_audit_trail. (note that only the previous setting will record the information

Save to the database to check this, if you save to the xml file can Select * from V$XML_AUDIT_TRAIL)

two。 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 do further processing when you audit something, such as writing information to a log you created, or sending an email to notify the person concerned. It is necessary to use the function of handler_module when creating policy and specify a stored procedure to do the 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 have three parameters: v_object_schema VARCHAR2, v_object_name VARCHAR2 and v_policy_name VARCHAR2. If you write a general stored procedure directly, you will make an error. Call a stored procedure in policy and write like this

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 be the user who created this policy. If it is another user name, there will be an error.

, 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

Tables or views related to FGA:

Select * from fga$

Select * from fga_log$

Select * from fgacol$

Select * from dba_fga_audit_trail

Select * from dba_common_audit_trail

Select * from dba_audit_policies

Select * from dba_fga_audit_trail

Packages or procedures related to FGA:

Dbms_fga.add_policy

Dbms_fga.drop_policy

You can view the relevant documentation for the use of tables, views, package columns, or parameters.

The above is all the contents of the article "how to use FGA in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.