Fine-grained audits in reality (1)

Fine-grained audit in
Reality (1)





Author: Arup Nanda





understand how to use the fine-grained auditing features of an Oracle database to track read-only access to specific rows in a table-and more information





traditional Oracle Database audit options allow you to keep track of what users do on an object at a macro level-for example, if you audit a SELECT statement on a table, you can track who selected the data from the table. But you don't know what they've chosen. Use data manipulation statements-such as inserts, updates, or delete-you can capture any changes by using triggers or by using the Oracle Logminer utility to analyze the archive log. Because simple SELECT statements do not manipulate data, they do not start triggers and are not recorded in archived logs that can be mined later, so these two techniques cannot meet the requirements where the SELECT statement is concerned.





oracle9i Database has introduced a new feature called Fine-grained Auditing (FGA) that has changed the situation. This feature allows you to audit a single SELECT statement in conjunction with the exact statement that the user submits. In addition to simply tracking statements, FGA provides a way to simulate a trigger for a SELECT statement by executing a piece of code each time the user selects a particular dataset. In this series of articles that are divided into three parts, I will explain how to use FGA to solve practical problems. The main content of this first part is to construct the basic FGA system.





Sample Installation





Our example is based on a banking system that has traditionally provided an audit trail of user access to specific data through application-level audits. However, as long as users access data from outside the application using tools such as Sql*plus, the system does not meet the requirements. In this article, I will explain how you, as a DBA, can use FGA to capture the task of capturing a user's SELECT access to a particular row, regardless of the tool or mechanism in which it is accessed.





in our example, the database has a table named ACCOUNTS, which is owned by the pattern BANK and is structured as follows:








Name Null? Type------------------------------------acct_no NOT null numbercust_id NOT NULL numberbalance number (15,2)











to construct a system that can audit any user selected in this table, you need to define a FGA policy for the table as follows:








begindbms_fga.add_policy (object_schema=> ' BANK ',object_name=> ' ACCOUNTS ',policy_name=> ') ACCOUNTS_ ACCESS ');











This code must be executed by a user with DBMS_FGA permission to execute the package. However, in order to improve security, it is recommended that you do not grant execution permissions to the user BANK (the owner of the table that will be audited), but you should grant permissions to a secure user (such as Secman), who should perform the process of adding a policy.





after the policy is defined, when the user queries the table in the usual manner, as follows:








select * from Bank.accounts;











Audit trail to record this operation. You can use the following statement to view the thread:








Select timestamp, Db_user,os_user,object_schema,object_name,sql_textfrom 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 the new view named Dba_fga_audit_trail, which records fine-grained access information. It shows the time stamp of the audit event, the database user ID of the queried person, the operating system user ID, the name and owner of the table used in the query, and finally the exact query statement. It is not possible to get this information before Oracle9i Database, but with the introduction of FGA, it becomes easy to get this information.





in Oracle9i Database, FGA can only capture SELECT statements. Using Oracle Database 10G,FGA can also handle DML statements-insert, UPDATE, and delete-to make them a complete audit feature. In the 3rd part of this series, I'll explain these new features in more detail.





audit lists and audit conditions





Let's examine the previous example in more detail. We require an audit of any SELECT statements that are used on the table. In reality, however, it may not be necessary to do so, and this may make the audit table for storing threads unbearable. When a user selects a balance column that contains sensitive information, the bank may need to audit, but the user may not need to audit when they select the account number for a particular customer. Column BALANCE (select it to trigger an audit) is called an audit column, in which case the parameters of the Dbms_fga.add_policy procedure specify the column as follows:








audit_column => ' BALANCE '











If you record an audit trail every time a user selects from a table, the size of the thread grows, resulting in space and management issues, so you may want to audit only when certain conditions are met, rather than auditing each time. Perhaps only when a user visits a very wealthy householder account, the bank needs an audit-for example, if the user chooses an account with a balance of 11,000 dollars or more. This type of condition is called an audit condition and passed as a parameter to the Dbms_fga.add_policy procedure, as follows:








audit_condition => ' BALANCE >= 11000 '











Let's see how these two parameters work. The format of the policy definition is now similar to the following:








begindbms_fga.add_policy (object_schema=> ' BANK ',object_name=> ' ACCOUNTS ',policy_name=> ') ACCOUNTS_ ACCESS ', Audit_column => ' BALANCE ', audit_condition => ' BALANCE >= ');











in this case, the operation is audited only if the user selects the column BALANCE and the retrieved row contains a balance greater than or equal to $11,000. If one of these two conditions is not true, the operation is not written to the audit trail. The example in table 1 shows the various scenarios in which the operation is audited and when the operation is not audited.





Optimizer mode





FGA requires cost-based optimization (CBO) to work properly. In rule-based optimization, whenever a user chooses from a table, regardless of whether or not a column is selected, an audit trail is always generated, increasing the likelihood of misleading items appearing. In order for FGA to work correctly, there should be no rule hints in SQL statements, in addition to enabling the CBO at the instance level, and all tables in the query must be parsed with at least evaluation options.