Fine-grained audit in reality (1)
Author: Arup Nanda Learn how to use the properties of fine-grained auditing Oracle database to keep track of a particular row of a table read-only access - as well as more traditional Oracle database audit option allows you to track at the macro level of the user performed on the object Operation - For example, if you audite the SELECT statement to a table, you can track who select data from the table. However, you don't know what they have chosen. Using data manipulation statements - such as INSERT, UPDATE or DELETE - you can analyze any changes by using triggers or using the Oracle Logminer utility to capture any changes. Because simple SELECT statements are not manipulated, they neither start the trigger, nor will they record those archive logs that can be minat after that, so the two technologies cannot meet the requirements in the place involving the SELECT statement. Oracle9i Database has introduced a new feature called fine granular audit (FGA), which changed this situation. This feature allows you to audit a single SELECT statement to the exact statements submitted by the user. In addition to simply tracking the statement, the FGA provides a method to simulate a trigger for a SELECT statement by performing a code when each user selects a particular data set. In this series of articles divided into three parts, I will show how to use FGA to solve practical problems. The main content of this first part is to build a basic FGA system. Example Installing Our example Based on a banking system, the audit clue that users access to a specific data is provided by the application level audit. However, as long as the user accesses data from a tool such as SQL * Plus, the system cannot meet the requirements. In this article, I will illustrate how you can use FGA to complete the task of capturing the user's SELECT access, no matter what the tool or mechanism of access is. In our example, the database has a table named Accounts, with the mode Bank, the structure is as follows: Name Null? Type
---------------------- ------------
ACCT_NO NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
Balance Number (15, 2)
In order to construct a system that can audit any user selected in this table, you need to define the FGA strategy for the table as follows: Begin
DBMS_FGA.ADD_POLICY (
Object_schema => 'Bank',
Object_name => 'Accounts',
Policy_name => 'Accounts_Access'
);
END;
This code must be performed by a user with executing package DBMS_FGA privileges. However, in order to improve security, it is recommended not to grant the user Bank (the owner of the audit will be subject to the owner of the auditing); and the permissions should be granted a secure user (such as Secman), which should perform the process of adding a policy. After defining a policy, when the user queries the table in a usual manner, as shown below: SELECT * from Bank.accounts;
The audit clue records this operation. You can use the following statements to view clues: SELECT TIMESTAMP,
DB_USER,
Os_user,
Object_schema,
Object_name,
SQL_Text
From dba_fga_audit_trail; TimeStamp DB_USER OS_USER Object_ Object_n SQL_Text
---------------------------------------------------------------------------------------------------------------------------------------------------------- ------------
22-Sep-03 Bank Ananda Bank Accounts Select * from Accounts
Note that a new view called DBA_FGA_AUDIT_TRAIL, which records fine-grained access information. The time tag of the audit event, the query database user ID, the operating system user ID, the name of the table used in the query, and the last list of exact query statements. This information cannot be obtained before Oracle9i Database, but with the launch of FGA, it is easy to obtain. In Oracle9i Database, FGA can only capture the SELECT statement. With Oracle Database 10g, FGA can also handle DML statements - INSERT, UPDATE, and DELETE - make it a complete audit feature. In Part 3 of this series, I will explain these new features in detail. The audit column and audit conditions let us check the previous examples in more detail. We ask the audit any Select statement to the table. But in reality, it may not be necessary to do this, and this may cause the audit table of the storage clue. When the user chooses the balance column containing sensitive information, the bank may need to perform audits, but when the user selects a specific customer account, it may not need to perform audits. BALANCE (Select It Trigger Audit) is called audit column, in which case the parameter of the dbms_fga.add_policy process specifies the following: audit_column => 'balance'
If each user is selected from the table, the audit clue is recorded, the size of the clues will grow, lead to space and management issues, so you may want to audit only when you meet specific conditions, rather than auditing each time. Perhaps only when users have access to extremely rich account-owner accounts, banks need to audit - for example, only when the user chooses the balance of $ 11,000 or more accounts, it is necessary to audit. This type of condition is called audit conditions and is passed to the DBMS_FGA.Add_Policy process as a parameter, as shown below: audit_condition => 'Balance> = 11000'
Let us see how these two parameters work. Now the form of policy definition is similar to: Begin
DBMS_FGA.ADD_POLICY (
Object_schema => 'Bank',
Object_name => 'Accounts',
Policy_name => 'Accounts_Access',
Audit_column => 'balance',
Audit_condition => 'balance> = 11000'
);
END;
