Further talk about fine-grained audit (DBMS_FGA)

Let's talk about fine-grained audit (DBMS_FGA) 1. the DBMS_FGA package is effective in the cost-based optimizer. unnecessary audit records may be generated in the rule-based optimizer. (Because audit monitoring can occur before row filtering) whether it is a rule-based or cost-based optimizer, you can use the DBA_FGA_AUDIT_TRAIL view to analyze the SQL text and its corresponding binding variables. 2. DBMS_FGA subroutine ADD_POLICY DISABLE_POLICY DROP_POLICY ENABLE_POLICY ADD_POLICY the maximum number of FGA audit policies on any table or view object is 256. syntax: DBMS_FGA.ADD_POLICY (object_schema VARCHAR2, -- Mode of the object to be audited (if it is NULL, the default mode is the current Login User) object_name VARCHAR2, -- Name of the object to be audited: policy_name VARCHAR2; -- Unique Audit Policy Name Udit_condition VARCHAR2, -- monitoring condition. if it is NULL, it indicates that all data is monitored, that is, the condition is TRUE audit_column VARCHAR2, -- check the accessed columns. it can contain OLS hidden columns or object type columns. the default value is NULL. handler_schema VARCHAR2 is used to audit all columns. the default value is NULL. The current mode handler_module VARCHAR2 is used. -- the name of the event handle function. if necessary, it also contains the package name. this function is called only when the first row of the query matches the audit condition. -- If the storage fails with an exception, the user's SQL statement will also fail enable BOOLEAN. -- TRUE indicates that this policy is enabled (TRUE by default) statement_types VARCHAR2, -- Audit SQL statement type. only INSERT, UPDATE, DELETE, and SELECT types are supported. audit_trail BIN ARY_INTEGER in default, -- the destination of fine-grained audit (DB or XML). It also specifies whether to fill the LSQLTEXT and LSQLBIND of fga_log $. Audit_column_opts BINARY_INTEGER in default); -- determines whether a statement is audited when querying any or all column references specified by the audit_column parameter. for example: [SQL] DBMS_FGA.ADD_POLICY (object_schema => 'Scott ', object_name => 'emp', policy_name => 'mydomainy1', audit_condition => 'sal <100 ', audit_column => 'comm, sal ', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'insert, updat', audit_trail => DBMS_FGA.XML + DBMS_FGA. EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS); Description: 1. the FGA policy should not apply to columns of external rows, such as the LOB column (which is separately stored by the LOB column ). 2. each audit policy is applied to the query separately. each audit policy generates a maximum of one audit record no matter how many rows meet the audit conditions. 3. if the FGA policy defined in a table receives a fast path insertion (direct path loading) or vector update, the HINT is automatically disabled before any such operation. disable the HINT to allow audit to happen according to the audit policy. 4. the audit condition must be a BOOL expression. You can use functions such as USER or SYS_CONTEXT. and or cannot be used to connect two conditions. it cannot contain sequences or subqueries. you cannot use CURRENT_ SQL, CURRENT_ SQL _LENGTH, and CURRENT_BIND when using the SYS_CONTEXT function. you cannot use pseudo columns (LEVEL, PRIOR, ROWNUM, etc.) if the condition is '1 = 1', all statement types of the specified column are forcibly audited. if this parameter is NULL, audit occurs when no rows are processed. 5. the audit function (handler_module) is an alarm mechanism provided by the Administrator. You can create an email sending function to send alerts to the Administrator ). for example, function: PROCEDURE fname (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2)... 6. if the audit trail contains XML, fine-grained audit records are written to the operating system file in XML format and stored in the directory specified by the AUDIT_FILE_DEST parameter. AUDIT_FILE_DEST default path is $ ORACLE_BASE/admin/$ DB_UNIQUE_NAME/adump (UNIX) $ ORACLE_BASE \ admin \ $ DB_UNIQUE_NAME \ adump (WINDOWS) If Audit If the trail contains a database, the fine-grained audit record is written to the table SYS. FGA_LOG $ medium. if it is in a read-only database, it is written to the XML file by default, regardless of the settings of this parameter. if the audit trail contains EXTENDED, the queried SQL text and SQL binding information are included in the audit trail. note that SQL text may contain sensitive information. such as credit card numbers. several settings of the audit_trail parameter: DBMS_FGA.DB, DBMS_FGA.DB + DBMS_FGA.EXTENDED, DBMS_FGA.XML, DBMS_FGA.XML + DBMS_FGA.EXTENDED. this parameter appears in the ALL_AUDIT_POLICIES view. you can run the command to change the AUDIT_FILE_DEST value: alter system set AUDIT_FILE_DEST = '<New Directory>' DEFERRED7. in many platforms, the XML file format audited is <process_name> _ <ProcessId>. xml, such as ora_2111.xml. in WINDOWS, the format is <process_name >_< ThreadId>. xml.8. when the query references all columns in the specified audit_column parameter, audit_column_opts = DBMS_FGA.ANY_COLUMNS. When the query references all columns, audit_column_opts = DBMS_FGA.ALL_COLUMNS defaults to bytes. when audit_column_opts is set to DBMS_FGA.ALL_COLUMNS, an SQL statement is audited and all columns need to be displayed. See the columns specified by audit_column. if a query statement queries columns from different table aliases, the statement is not audited. (select. COL1 from tab a) if the audit_trail parameter is set to DBMS_FGA.XML + DBMS _ FGA. EXTENDED, the fine-grained audit records will be written to the XML file of the operating system. It is safer to write audit records to the operating system files than to write them to the database. A new dynamic view V $ XML_AUDIT_TRAIL is used to query all the XML files audited (displayed as Relational Tables after parsing) DISABLE_POLICY disable policy syntax DBMS_FGA.DISABLE_POLICY (object_schema VARCHAR2, -- Audit mode object (if it is NULL, the default mode is the current logon user) object_name VARCHAR2, -- Audit Object Name policy_name VARCHAR2); -- Audit Policy unique name example: [SQL] DBMS_FGA.DISABLE_POLICY (object_schema => 'Scott ', object_name => 'emp', policy_name => 'mysql1'); DROP_PO LICY deletion policy syntax DBMS_FGA.DROP_POLICY (object_schema VARCHAR2, -- Audit mode object (if it is NULL, the default mode is the current Login User) object_name VARCHAR2, -- Audit Object Name policy_name VARCHAR2 ); -- Unique name of the Audit Policy: [SQL] DBMS_FGA.DROP_POLICY (object_schema => 'Scott ', object_name => 'emp', policy_name => 'mypolicy1'); Description: before deleting a policy, we recommend that you use COMMIT because this operation hides the DDL operation. If you use DBMS_FGA.ADD_POLICY to remove object_name or delete the user who created the policy, the policy is automatically deleted by default. ENABLE_POLICY enable policy syntax DBMS_FGA.ENABLE_POLICY (object_schema VARCHAR2, -- Audit mode object (if it is NULL, the default mode is the current Login User) object_name VARCHAR2, -- Audit Object Name policy_name VARCHAR2, -- the unique Audit Policy Name enable BOOLEAN); -- the default value is TRUE, that is, the policy example is Enabled: [SQL] DBMS_FGA.ENABLE_POLICY (object_schema => 'Scott ', object_name => 'emp ', policy_name => 'mydomainy1 ', enable => TRUE );