Realistic fine-grained audit (2)
Manage FGA strategy
You have seen how to add an FGA policy. 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;
For changes in the policy, there is no such solution. To change any parameters in the policy, you must delete the policy and add a policy using the changed parameter.
Sometimes you may need to temporarily disable audit collection - For example, if you want to move the clue table to different tablespaces or to delete the clue table. You can disable FGA policies as follows:
Begin
DBMS_FGA.enable_policy (
Object_schema => 'Bank',
Object_name => 'Accounts',
Policy_name => 'Accounts_Access',
enable => false
);
END;
To re-enable it, you can use the same function, but set the parameter enable to true.
Processor module
The FGA features are not just the events in the audit clue; FGA can also be arbitrarily executed. The process can perform an operation, such as sending an email warning to the auditor when the user selects a particular line from the table, or can be written to different audit clues. This storage code segment can be an independent process or a process in which the process is called a processor module. In fact, due to safety reasons, it does not have to be in the same mode as the base Table itself, you may want to set it in different modes. Since the process is executed as long as the process occurs, it is very similar to the trigger that the DML statement starts, you can also see it as a SELECT statement trigger. The following parameters specify to assign a processor module to the policy:
Handler_schema has a pattern of data processing Handler_Module procedure name
The processor module can also use the name of the package instead of the process name. In this case, the parameter handler_module is specified in the format of package.procedure.
FGA data dictionary view
Definition of the FGA policy is located in Data Dictionary View DBA_Audit_Policies. Table 2 contains a short description of some important columns in this view.
The audit clue collected in the table FGA_LOG $ with SYS. For any original table owned by SYS, some views on this table displays information in a user-friendly way. DBA_FGA_AUDIT_TRAIL is a view on the table. Table 3 contains a short description of important columns in this view.
An important column is SQL_BIND, which specifies the value of the bound variable used in the query - this is a significantly enhanced information of the tool function.
Another important column is SCN, which logs the system to change the system when a specific query occurs. This information is used to identify what the user sees at a specific time, rather than the current value, which uses a flashback query, which can display the data when the specified SCN value. I will explain this powerful feature in Part 2 of this series.
View and FGA
So far I have discussed the application of FGA in the table; now let us see how to use FGA on the view. Assume that the view is defined on the Accounts table vW_accounts as follows:
Create View vw_accounts as self * from account;
Now, if the user selects from the view instead of from the table:
Select * from vw_accounts; you will see the following audit clues:
SELECT Object_name, SQL_Text from DBA_FGA_AUDIT_TRAIL;
Object_name sql_text
------------------------------------------------- ------------
Accounts SELECT * FROM VW_ACCOUNTS
Note that the base table name is not that the view name appears in the object_name column, because the selection in the view is selected from the base table. However, the SQL_Text column records the actual statement submitted by the user, and this is exactly what you want to know.
Next step
Read more about DBMS_FGA Packages Access Oracle Database Main Page Access Oracle Platform Security Home
If you only want to audit the query of the view rather than the table's query, you can establish a strategy to the view itself. This work can be done by passing the view name instead of the table to the parameter Object_name in DBMS_FGA.ADD_POLICY. The Object_name column in DBA_FGA_AUDIT_TRAIL will then display the name of the view and does not appear additional records for table access.
Other use
In addition to the selection access to the table, FGA can also be used for some other cases:
You can use FGA to capture all statements that occur on specific tables, views, or physical graphs, which helps schedule indexes. You don't need to get this information to the V $ SQL view. Even if the SQL statement has exceeded V $ SQL's deadline, it will always be provided in the FGA audit clue. Since the FGA captures the binding variable, it can help you understand the schema of the binding variable value, which helps to design a histogram collection. The foregoing has been mentioned that the processor module can send a warning to the auditor or DBA, which helps to track malicious applications. Since the FGA can act as a SELECT statement trigger, you can use it at any time that you need this feature.
in conclusion
FGA enables you to support privacy and functionality in the Oracle database. Since the audit occurs instead of the application instead of the application, the operation is permitted by the user's access method (by tools or applications such as SQL * PLUS), it is allowed to perform very simple settings.
Next time I will discuss advanced FGA technology and new features in Oracle Database 10g, which make the FGA's functionality is extremely powerful, suitable for all types of audits.
Arup Nanda (arup@proligence.com) is the chief database designer of IntelliClaim, which is located in the Connecticut Norwalk, providing high security and rules based on health care insurance claim management. He is the winner of the 2003 Oracle DBA Award and cooperates with others to publish the upcoming Oracle Privacy Safety Audit (Rampant Techpress Publishing, 2003).
Table 1: Demonstrate when audit operations and how to do not audit operation
SQL statement audit status Select Balance from Accounts; Audit. The user selects the audit column Balance specified when adding a policy. SELECT * ACCOUNTS; audit. Even if the user does not specify the column balance, * also implies it. Select Cust_ID from Accounts WHERE Balance <10000; audit. Even if the user doesn't make a clear specified column Balance, the WHERE clause also implies it. SELECT CUST_ID from Accounts; not audited. The user did not select column Balance. Select count (*) from accounts; not audited. The user does not make a clear or implicitly selected column Balance. Table 2: Data Dictionary View DBA_AUDIT_POLICIES Important Columns
OBJECT_SCHEMA its name defines the name of the owner of the policy POLICY_NAME OBJECT_NAME table or view table or view FGA policy - for example, ACCOUNTS_ACCESSPOLICY_TEXT specified when you add a policy audit conditions - for example, BALANCE> = 11000POLICY_COLUMN audit column - for example, BALANCEENABLED if Enabled is YES, otherwise the NO PF_SCHEMA has a policy processor module mode (if present) PF_PACKAGE processor module's package name (if present) PF_Function processor module process name (if present)
Table 3: Important columns in dba_fga_audit_trail view
Session_ID Auditing Session Identifier; Different TimeStamp auditing records in the V $ SESSION view Different TimeStamp audit records Generate time tag DB_USER Essay query database User OS_USER Operating System UserHost User Connected Machine Host Name Client_ID Customer Identifier (if by For the call of the package process DBMS_SESSION.SET_IDENTIFIER, the client name is set. The policy name (if multiple policies are defined for the table, each policy will insert a record. In this case, the column shows which row is inserted.) SCN recorded an audit Oracle system change number SQL_Text SQL statement SQL_BIND submitted by the user (if there is only the SQL statement)
