Oracle FGA strategy: fine-grained audits

1, FGA strategy (fine-grained audit)

DBMS_FGA is a package for the SYS user

(1) Increase FGA strategy

--Audit form

GRANT Resource,connect to bank identified by bank;

CREATE TABLE BANK. ACCOUNTS

（

Acct_no number PRIMARY KEY,

cust_id number not NULL,

BALANCE number (15,2) NULL

）；

INSERT into bank.accounts values (1,1,10000);

INSERT into bank.accounts values (2,2,20000);

Commit

Begin

Dbms_fga.drop_policy (

Object_schema=> ' BANK ',

Object_name=> ' ACCOUNTS ',

Policy_name=> ' accounts_access ');

Dbms_fga.add_policy (

Object_schema=> ' BANK ',

Object_name=> ' ACCOUNTS ',

Policy_name=> ' accounts_access ');

End

/

SELECT * from Bank.accounts;

Select Timestamp, db_user,os_user,object_schema,object_name,sql_text from Dba_fga_audit_trail;

--Audit lists and audit conditions, add in Add_policy

--Audit_column => ' BALANCE '

--audit_condition => ' BALANCE >= 11000 '

Begin

Dbms_fga.drop_policy (

Object_schema=> ' BANK ',

Object_name=> ' ACCOUNTS ',

Policy_name=> ' accounts_access ');

Dbms_fga.add_policy (

Object_schema=> ' BANK ',

Object_name=> ' ACCOUNTS ',

Audit_column => ' BALANCE ',

Audit_condition => ' BALANCE >= 11000 ',

Policy_name=> ' accounts_access ');

End

/

Select BALANCE from Bank.accounts;

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45449.htm

Select Timestamp, db_user,os_user,object_schema,object_name,sql_text from Dba_fga_audit_trail;

(2) Management FGA strategy

-To delete a policy, you can use the following statement:

Begin

Dbms_fga.drop_policy (

Object_schema => ' BANK ',

object_name => ' ACCOUNTS ',

Policy_name => ' accounts_access '

）；

End

/

--there is no alternative solution for changing the policy. To change any of the parameters in the policy, you must delete the policy and then add the policy using the changed parameter.

--You need to temporarily disable audit collections-for example, if you want to move a thread table to a different table space or to delete a lead table. You can disable the FGA policy in the following ways:

Begin

Dbms_fga.enable_policy (

Object_schema => ' BANK ',

object_name => ' ACCOUNTS ',

Policy_name => ' accounts_access ',

Enable => FALSE

）；

End

/

--re-enable very simple enable =>; TRUE

--demonstrates when to audit the operation and when not to audit the various situations SQL statement audit status

Select Balance from bank.accounts; For audit. The user has selected the Audit column BALANCE that was specified when the policy was added.

SELECT * from Bank.accounts; For audit. It is implicitly selected even if the user does not explicitly specify the column balance,*.

Select cust_id from bank.accounts where balance < 10000; For audit. It is implicitly selected even if the user does not explicitly specify a column balance,where clause.

Select cust_id from Bank.accounts; Audits are not performed. The user did not select column BALANCE.

Select COUNT (*) from bank.accounts; Audits are not performed. The user does not explicitly or implicitly select column BALANCE.

(3) Processor module

-FGA's function is not only to record the events in the audit trail; FGA can also execute the process arbitrarily.

-A procedure can perform an action, such as sending an e-mail alert to an auditor when a user selects a specific row from a table, or writing to a different audit trail.

-This storage snippet can be either a stand-alone process or a process in a package called a policy's processor module.

--for security reasons, it doesn't have to be in the same mode as the base table itself, and you might want to deliberately place it in a different pattern.

-Because the process executes as long as the SELECT appears, very similar to the trigger that the DML statement starts, you can also view it as a SELECT statement trigger.

--The following parameter specifies that a processor module be assigned to a policy:

--Handler_schema The pattern of owning the data process

--Handler_module Process name

-the processor module can also take the name of the package instead of the procedure name. In this case, the parameter handler_module is specified in the package.procedure format.

(4) FGA Data dictionary View

--the definition of FGA policy is in the data dictionary view dba_audit_policies.

-Audit trail collection is in the table fga_log$ owned by SYS. For any original tables owned by SYS, some views on this table display information in a user-friendly manner. Dba_fga_audit_trail is a view on the table.

-An important column is Sql_bind, which specifies the value of the binding variable used in the query-a message that significantly enhances the functionality of the tool.

-Another important column is the SCN, which records the system change number when a particular query occurs.

-This information is used to identify what the user sees at a particular time, rather than the current value, and it uses a flashback query that displays data at the specified SCN value.