===========================================================================================================================================
Fine-Grained Auditing Part-I: Reference : from Arup Nanda on http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html
——————————————————————————————————————————————-
(*) Traditional Oracle Database auditing options let you track the actions users perform on objects at the macro level for example, if you audit for SELECT
statements on a table, you can track who selected data from the table. However, you don’t know what they selected. With data-manipulating statements such as
INSERT, UPDATE, or DELETE you can capture any changes by using triggers or by using the Oracle LogMiner utility to analyze the archived logs. Because simple
SELECT statements are non-data-manipulating, they neither fire a trigger nor go into archived logs that you can mine later, so these two techniques fall
short where SELECT statements are concerned.
(*) Oracle 9i introduced a new feature called fine-grained auditing (FGA), which changed all that. This feature lets you audit individual SELECT statements
along with exact statements issued by users. In addition to simply tracking statements, FGA provides a way to simulate a trigger for SELECT statements by
executing a code whenever a user selects a particular set of data.
——————-
***EXAMPLE SETUP***
——————-
SQL> DESC BANK.ACCOUNTS
Name Null? Type
—————- ——– ————
ACCT_NO NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
BALANCE NUMBER(15,2)
To construct a system that can audit anyone selecting from this table, you need to define an FGA policy on the table, as follows:
begin
dbms_fga.add_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
policy_name=>’ACCOUNTS_ACCESS’
);
end;
This code has to be executed by a user who has execute privilege for the package DBMS_FGA. However, for added security, it’s advisable not to grant execute
privilege to the user BANK, the owner of the table to be audited; rather, you should grant it to a secured user named, say, SECMAN, who should execute the
procedure to add the policy.
After you have defined the policy, when a user queries the table in the usual way, as follows:
SQL> select * from bank.accounts;
The audit trail records this action. You can see the trail by issuing:
SQL> 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 the new view named DBA_FGA_AUDIT_TRAIL, which records the fine-grained access information. Among other things, it shows the time stamp of the audited
event, the database user ID of the person who made the query; the OS user ID; the name and owner of the table used in the query; and, finally, the exact
query. This kind of information was impossible to get prior to Oracle9i Database, but with the introduction of FGA, it became trivial.
***Important***
As of Oracle9i Database, FGA could capture only SELECT statements. With Oracle Database 10g, FGA can also handle DML statements INSERT, UPDATE and DELETE
making it a complete auditing feature. In Part 3 of this series, I’ll explain these new functions in detail.
———————————————-
***** Audit Columns and Audit Conditions *****
———————————————-
Let’s examine the previous example in more detail. We asked that any SELECT statement used on the table be audited. In real life, however, this is probably
not necessary, and it may overwhelm the audit table that stores the trail. The bank may need to audit when a user selects a balance column, which contains
sensitive information, but may not need to audit when a user selects the account number for a particular customer. The column BALANCE, whose selection
triggers an audit, is known as the audit column, and in this case, the parameter to the dbms_fga.add_policy procedure specifies it as follows:
audit_column => ‘BALANCE’
If audit trails are recorded each time a user selects from a table, the trails will grow in size, causing space and administration problems, so you may want
to conduct an audit only if certain conditions are met, not every time. Perhaps the bank needs an audit only if users access the accounts of extremely
wealthy account holders for instance, only if a user selects an account with a balance of $11,000 or more. This type of condition is known as an audit
condition and is 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 policy definition now looks like this:
begin
dbms_fga.add_policy (
object_schema=>’BANK’,
object_name=>’ACCOUNTS’,
policy_name=>’ACCOUNTS_ACCESS’,
audit_column=>’BALANCE’,
audit_condition=>’BALANCE >= 11000′
);
end;
In this case, only if the user selects the column BALANCE and if the rows retrieved contain a balance more than or equal to $11,000 is the action audited. If
either of these conditions is not true, the action is not written to the audit trail. The examples in Table 1 illustrate various scenarios for when an action
will be audited and when not.
Table 1:
SQL Statement Audit State
—————————————————– ————————————————————————–
select balance from accounts; Audited. The user selects the audit column BALANCE, specified when the policy was added.
select * from accounts; Audited. Even though the user does not specify the column BALANCE explicitly, the * implicitly selects
it.
select cust_id from accounts where balance < 10000; Audited. Even though the user does not specify the column BALANCE explicitly, the where clause
implicitly selects it.
select cust_id from accounts; Not audited. The user does not select the column BALANCE.
select count(*) from accounts; Not audited. The user does not explicitly or implicitly select column BALANCE
————————–
***** Optimizer Mode *****
————————–
FGA requires cost-based optimization (CBO) with statistics in order to work correctly. Under rule-based optimization, audit trails are always generated
whenever a user selects from a table, regardless of whether the relevant columns are selected or not, increasing the chance of false-positive entries. For
FGA to work properly, in addition to CBO being enabled at the instance level, there should be no RULE hint in the SQL statements and all the tables in the
query must be analyzed at least with the estimate option.
———————————
***** Managing FGA Policies *****
———————————
Earlier you saw how to add an FGA policy. To drop a policy, you can use the following:
begin
dbms_fga.drop_policy (
object_schema => ‘BANK’,
object_name => ‘ACCOUNTS’,
policy_name => ‘ACCOUNTS_ACCESS’
);
end;
There is no out-of-the-box solution for changing a policy. To change any parameters of the policy, you must drop the policy and add it again with the changed
parameters.
Sometimes you may need to disable audit collections temporarily for example, if you want to move the trail table to a different tablespace or want to delete
it. You can disable an FGA policy as follows:
begin
dbms_fga.enable_policy (
object_schema => ‘BANK’,
object_name => ‘ACCOUNTS’,
policy_name => ‘ACCOUNTS_ACCESS’,
enable => FALSE
);
end;
To re-enable it, use the same function but with the parameter enable set to TRUE.
——————————
***** The Handler Module *****
——————————
The power of FGA doesn’t stop at merely recording events in audit trails; FGA can also optionally execute procedures. A procedure could perform an action
such as sending an e-mail alert to an auditor when a user selects a certain row from a table, or it could write to a different audit trail. This stored code
segment, which could be a stand-alone procedure or a procedure within a package, is known as the handler module for a policy. It does not have to be in the
same schema as the base table itself; in fact, for security reasons, you may want to deliberately place it in a separate schema. Because the procedure
executes whenever a SELECT occurs, much like a trigger firing on a DML statement, you can also think of it as a SELECT statement trigger. The following
parameters specify a handler module is specified for the policy:
handler_schema The schema that owns the data procedure
handler_module The procedure name
The handler module can also take a package name instead of a procedure name. In that case, the parameter handler_module is specified in package.procedure
format.
————————————-
***** FGA Data Dictionary Views *****
————————————-
(1) DBA_AUDIT_POLICIES:
The definition of an FGA policy resides in the data-dictionary view DBA_AUDIT_POLICIES. Table 2 includes a brief description of some of the important columns
of this view. See following Table 2 for important columns:
OBJECT_SCHEMA : The owner of the table or view on which the FGA policy is defined
OBJECT_NAME : Name of the table or view
POLICY_NAME : Name of the policy-for example, ACCOUNTS_ACCESS
POLICY_TEXT : The audit condition specified while adding the policy-for example, BALANCE >= 11000
POLICY_COLUMN : The audit column-for example, BALANCE
ENABLED : YES if enabled; NO otherwise
PF_SCHEMA : The schema that owns the policy’s handler module, if one exists
PF_PACKAGE : The package name of the handler module, if one exists
PF_FUNCTION : The procedure name of the handler module, if one exists
(2) The audit trails are collected in the SYS-owned table FGA_LOG$. As with any SYS-owned raw table, some views on this table present the information in a
user-friendly manner. DBA_FGA_AUDIT_TRAIL is a view on the table. See Following TABLE : 3 for important columns.
SESSION_ID : The Audit Session Identifier; not the same as the Session Identifier in the V$SESSION view
TIMESTAMP : The time stamp for when the audit record was generated
DB_USER : The database user who issued the query
OS_USER : The operating system user
USERHOST : The host name of the machine from which the user connected
CLIENT_ID : The client identifier, if set by a call to the packaged procedure dbms_session.set_identifier
EXT_NAME : The name of an externally authenticated client, such as an LDAP user
OBJECT_SCHEMA : The owner of the table on which access triggered the audit
OBJECT_NAME : The name of the table on which a SELECT operation triggered the audit
POLICY_NAME : The name of the policy that triggered the audit (If a table has multiple policies defined on it, each one will insert a record. In that case,
this column shows which rows were inserted by which policy.)
SCN : The Oracle System Change Number at which the audit was recorded
SQL_TEXT : The SQL statement issued by the user
SQL_BIND : Bind variables used by the SQL statement, if any.
One important column is SQL_BIND, which specifies the values of the bind variables used in a query–knowledge that greatly enhances the tool’s power.
Another important column is SCN, which records the System Change Number when a particular query occurs. This information is useful for identifying what a
user saw at a specific time, not what the value is now, using Flashback Queries which can show the data at a specified SCN value.
————————————-
***** Policies on Views and FGA *****
————————————-
Now let’s see how you can use FGA on views. Assume a view VW_ACCOUNTS is defined on the ACCOUNTS table as follows:
create view vw_accounts as select * from accounts;
Now, if a user selects from the view instead of from the table:
select * from vw_accounts;
you’ll see the following audit trail:
select object_name, sql_text from dba_fga_audit_trail;
OBJECT_NAME SQL_TEXT
———– ————————————————-
ACCOUNTS select * from vw_accounts
Note that the name of the base table, not the view, appears in the OBJECT_NAME column, because the selection from the view selects from the base table.
However, the SQL_TEXT column records the actual statement the user issued, and that is exactly what you want to know.
If you want to audit queries only on views, not on tables, you can set the policy up on a view itself. You do this by passing the name of the view instead of
that of the table to the parameter object_name in the packaged procedure DBMS_FGA.ADD_POLICY. The OBJECT_NAME column in DBA_FGA_AUDIT_TRAIL will then display
the name of the view, and there will be no additional record for a table access
—————————–
***** Other Uses Of FGA *****
—————————–
In addition to recording the select access to tables, FGA is useful for some other situations:
(1) You can use FGA on a data warehouse to capture all the statements occurring on a particular table, view, or materialized view, which is helpful for
planning indexes. You don’t need to go to the V$SQL view to get this information. Even if a SQL statement has have been aged out of V$SQL, it will always be
available in an FGA audit trail.
(2) Because FGA captures bind variables, it can help you understand the pattern of bind-variable values, which is helpful for designing histogram collections
and so on.
(3) As mentioned earlier, the handler module can send out alerts to auditors or DBAs, which is helpful for tracking rogue applications.
(4) Because FGA can act as a trigger for SELECT statements, you can use it whenever you require such functionality.