Example Analysis of Oracle FGA

Editor to share with you the example analysis of Oracle FGA, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle FGA (Fine-Grained Auditing)

One: create FGA

Example 9-21 Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy

SQL >

BEGIN

DBMS_FGA.ADD_POLICY (

Object_schema = > 'HR'

Object_name = > 'EMPLOYEES'

Policy_name = > 'chk_hr_employees'

Enable = > TRUE

Statement_types = > 'INSERT, UPDATE, SELECT, DELETE'

Audit_trail = > DBMS_FGA.DB+DBMS_FGA.EXTENDED)

END

/

Second: view the FGA that has been created

At this point, if you query the DBA_AUDIT_POLICIES view, you will find the new policy listed:

SQL > SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES

POLICY_NAME

-

CHK_HR_EMPLOYEES

Third: generate audit data

Afterwards, any of the following SQL statements log an audit event record.

SQL > SELECT COUNT (*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500

SQL > SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50

SQL > DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000

SQL > select * from employees

Four: view audit information

SQL > select lsqltext from sys.fga_log$

LSQLTEXT

SELECT COUNT (*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500

SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50

DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000

Select * from employees

SQL > select SQL_TEXT from dba_fga_audit_trail

SQL_TEXT

SELECT COUNT (*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500

SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50

DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000

Select * from employees

Fifth: delete chk_hr_employees audit

SQL > exec DBMS_FGA.DROP_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'chk_hr_employees')

-SQL > exec DBMS_FGA.DISABLE_POLICY_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'chk_hr_employees')

-SQL > exec DBMS_FGA.ENABLE_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'chk_hr_employees')

The above is all the content of this article "sample Analysis of Oracle FGA". 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!