Section 6.2. Customizing FGA

6.2. Customizing FGA

Consider the code we used in the previous section to allow a table to take advantage of FGA:

     BEGIN        DBMS_FGA.add_policy (object_schema      => 'HR',                             object_name        => 'EMP',                             policy_name        => 'EMP_SEL'                            );     END;

This example illustrates the most rudimentary form of the policy. In reality, you will have to customize FGA to handle your own particular auditing needs. In the following sections, I'll show how policies can be customized.

6.2.1. Specifying Audit Columns

If we record information every time someone selects anything from a table, the audit trail will become very large, making it difficult to manage. You may want to limit recording of accesses to only a specific set of columns. Let's revisit the description of the table EMP.

     SQL> DESC emp      Name              Null?    Type      ----------------- -------- ------------      EMPID             NOT NULL NUMBER(4)      EMPNAME                    VARCHAR2(10)      JOB                        VARCHAR2(9)      MGR                        NUMBER(4)      HIREDATE                   DATE      SALARY                     NUMBER(7,2)      COMM                       NUMBER(7,2)      DEPTNO                     NUMBER(2)

Examining the columns, you'll notice that some columns may be considered more important to audit than others. For example, you may want to make sure that all accesses to the column SALARY are logged, but you might not want to audit such columns as HIREDATE quite so stringently. In this example, let's assume that I want to audit accesses only to SALARY and COMM, not to all columns. I can do so by specifying a value for the ADD_POLICY procedure's audit_column parameter as follows.

     BEGIN        DBMS_FGA.add_policy (object_schema      => 'HR',                             object_name        => 'EMP',                             policy_name        => 'EMP_SEL',                                                          audit_column       => 'SALARY, COMM'                            );     END;

This causes the trail to be generated only if the user selects the SALARY or COMM columns. If she selects only ENAME, the trail is not recorded.

What I've described applies not only to columns named explicitly in the query, but also to columns referenced implicitly. For instance the query:

     SELECT * FROM hr.emp;

selects all columns from the table EMP, including COMM and SALARY. Hence, the action is logged. Even though the columns are not explicitly mentioned, they are referenced implicitly.

In Oracle9i Database, if any column of the list of columns mentioned in the audit_column parameter is referenced, FGA is invoked. In Oracle Database 10g, however, there is an option to specify if auditing is to be done when any one of the columns is referenced or if all of the columns must be referenced. That option is described later in this chapter in the "FGA for Oracle Database 10g" section.

6.2.2. Specifying Audit Conditions

Suppose that your organization is one of those global corporations with 50,000 or more employees scattered across the world. With different labor laws and varying pay cycles, the HR database hums with activity that's more akin to that of an OLTP database. In such a case, if you try to log every access to the columns COMM and SALARY, your audit trail will quickly balloon to an unmanageable size. While thinking about solutions, you may want to limit the recording of access to high-profile cases onlyfor example, when someone chooses to see salaries of 150,000 or more or when someone sees your salary. You can set this kind of limitation in your FGA policy using a condition. To code the above condition, you will have to use a special parameter named audit_condition while invoking the procedure. If you already have the policy defined, you need to drop it by specifying:

     BEGIN        DBMS_FGA.drop_policy (object_schema      => 'ARUP',                              object_name        => 'EMP',                              policy_name        => 'EMP_SEL'                             );     END;

Then create the policy as follows:

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_SEL',                             audit_column         => 'SALARY, COMM',                                                          audit_condition      => 'SALARY >= 150000 OR EMPID = 100'                            );     END;

Here the parameter audit_condition is used to limit the audit trail so that it is generated only when the value of the SALARY column exceeds 150,000 or when the EMPID value is 100. If a user selects a record for someone whose salary is 149,999, for example, the action is not audited. Note that both situations must be true for the audit record to be generated: the user must select the relevant columns and the audit condition must be satisfied. If the user does not specifically mention the SALARY or COMM columns in the query, the trail is not generated even if the record being accessed has a value of 150,000 or more in the SALARY column. For instance, let's say that Jake's salary is 16,000 and his EMPID is 52. A user who simply wants to find his manager's name issues this query:

     SELECT mgr     FROM emp     WHERE empid = 52;

Because the user has not selected the SALARY or COMM columns, the action is not audited. However the query:

     SELECT mgr     FROM emp     WHERE salary > 160000;

generates a trail. In this case, because the SALARY column is present in the WHERE clause, the user has implicitly selected it, so the relevant column condition is fulfilled. The SALARY of the records retrieved is more than 150,000, so the audit condition is fulfilled. Because both events have occurred, the audit trail is triggered.

Both eventsthe audit condition evaluating to true and the user selecting the relevant columnsmust occur for the audit trail to be generated. If only one of the events occurs, an audit entry will not be written.

The audit condition need not reference the columns of the table on which the policy is defined; it can reference other values such as pseudo-columns as well. This becomes useful if you want to audit only a certain set of users, not all. Suppose that I want to record accesses to table EMP made by Scott; I could define the policy as:

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_SEL',                             audit_column         => 'SALARY, COMM',                                                          audit_condition      => 'USER=''SCOTT'''                            );     END;

Here I have chosen to audit only the user Scott. I can easily change the condition to whatever we need; for example, specifying USER IN (''SCOTT'', ''FRED'') enables auditing on users Scott and Fred.

I might also want to audit everything that's accessed after working hours. I would specify a policy like this one:

     BEGIN        DBMS_FGA.add_policy           (object_schema        => 'HR',            object_name          => 'EMP',            policy_name          => 'EMP_AH',            audit_column         => 'SALARY, COMM',                        audit_condition      =>            'to_number(to_char(sysdate,''hh24'')) not between 09 and 17'           );     END;

Here I am auditing all accesses to the columns SALARY and COMM by all users not between 9 A.M. and 5 P.M. Because all these policies are named differently and all are defined on the table EMP, I can identify the records coming from each policy afterward from the audit trail. For instance, to identify the user who accessed EMP records after working hours, I would issue:

     SELECT db_user, ....     FROM dba_fga_audit_trail     WHERE policy_name = 'EMP_AH';

You can define any number of other audit conditions to satisfy the unique auditing needs of your own database.

6.2.3. Recording Bind Variables

Now I have laid a nice trap for Scott, the user who has been accessing the salaries of various highly paid executives of our organization (and my salary, as well). In the future, every time Scott queries a salary, that fact will be recorded in the audit trails.

Suppose, however, that after all this careful planning, Scott smells foul and somehow realizes what I've done. Being exceptionally clever, he decides to vary his SELECT statement, using a bind variable, in hopes of avoiding the audit, like this:

     SQL> variable EMPID number     SQL> execute :EMPID := 100     SQL> SELECT salary FROM emp WHERE empid = :EMPID;

His attempt will fail, because FGA captures the values of bind variables , in addition to the SQL text issued. The recorded values can be seen in the column SQL_BIND in the view DBA_FGA_AUDIT_TRAIL. In the above case, this is what will be recorded:

     SQL> SELECT sql_text,sql_bind FROM dba_fga_audit_trail;     SQL_TEXT                                       SQL_BIND     ---------------------------------------------- -----------------------     select * from hr.emp where empid = :empid      #1(3):100

Notice how the bind variable is captured in the format:

     #1(3):100

where:

#1

indicates the first bind variable. If the query had more than one bind variable, the others would have been shown as #2, #3, and so on.

(3)

Indicates the actual length of the value of the bind variable. In this example, Scott used 100 as the value, so the length is 3.

:100

Indicates the actual value of the bind variable, which, in this case, is 100.

The SQL_BIND column contains a string of values if more than one bind variable is used. For instance, if the query had been:

     SQL> VARIABLE empid number     SQL> VARIABLE sal number     SQL> BEGIN       2>    :empid := 100;       3>    :sal := 150000;       4> END;       5> /     PL/SQL procedure successfully completed.     SQL> SELECT * from hr.emp WHERE empid = :empid OR salary > :sal;

the SQL_BIND column would look like this:

     #1(3):100 #2(5):15000

The bind variables are clearly identified by their positions and their values, along with the lengths of the values.

The captured values of bind variables are very important, not just for accountability reasons, but also to analyze the pattern of data access, which is hard to examine otherwise. Suppose that you want to find out the best indexing scheme for a data warehouse system. All you have to do is to turn on the FGA audit trail for the objects for some time, and then analyze the values of the SQL_TEXT and SQL_BIND columns in the audit trail to get an idea about the types of values that users are searching for. This information can be obtained from V$SQL as well, but that view selects from the shared pool, which may age statements out as time progresses. The FGA trails, on the other hand, persist in the underlying FGA_LOG$ table, unless the DBA explicitly deletes or truncates it. Thus, the FGA trails provide a more reliable mechanism for capturing queries and values of the bind variables. You'll find such information very helpful in developing an indexing and partitioning strategy.

6.2.3.1. Turning off bind variable capture

In some cases, you may have no need for the SQL text and the values of the bind variables in the audit trails , and you may choose to turn off the logging of these values to save space. To do so (i.e., not record the bind variable values), set the audit_trail parameter in the ADD_POLICY procedure in the DBMS_FGA package to DB (which stops collecting the SQL text and bind variable information) instead of DB_EXTENDED (which collects them). The default is DB_EXTENDED. Because I want to turn off the SQL text and bind variable collection in this example, our PL/SQL block will look like this (notice that I set audit_trail to the constant DBMS_FGA.DB):

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_SEL',                             audit_column         => 'SALARY, COMM',                             audit_condition      => 'SALARY >= 150000 OR EMPID = 100',                             audit_trail          => DBMS_FGA.db                            );     END;     /

6.2.4. Specifying a Handler Module

So far, you have seen how FGA can log the fact that someone SELECTed (in Oracle9i Database) from a table or performed some type of DML (in Oracle Database 10g) into an FGA audit trail table, FGA_LOG$. FGA also performs another important function: it can optionally execute a PL/SQL stored program unit such as a stored PL/SQL procedure or a Java method. If the stored procedure, in turn, encapsulates a shell or OS program, it can execute that as well. This stored program unit is known as the handler module. In the previous example, where I built the mechanism to audit accesses to the EMP table, you can optionally specify a stored procedurestandalone or packagedto be executed as well. For instance, suppose that I have a stored procedure named myproc that is owned by user FGA_ADMIN; simply call the ADD_POLICY procedure with two new parameters as follows:

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_SEL',                             audit_column         => 'SALARY, COMM',                             audit_condition      => 'SALARY >= 150000 OR EMPID = 100',                             handler_schema       => 'FGA_ADMIN',                             handler_module       => 'MYPROC'                            );     END;

Whenever the policy's audit conditions are satisfied and the relevant columns are referenced, two things happen: the action is recorded in the audit trails, and the myproc procedure in the FGA_ADMIN schema is executed. The procedure is automatically executed as an autonomous transaction every time the audit trails are written. This means that any changes made by the handler module will be committed or rolled back without affecting the transaction in the session that fired the handler module. It will also not interfere with the auditing activity.

If the handler module fails for any reason, FGA does not report an error when you select data from the table. Instead, it simply and silently stops retrieving the rows for which the handler module fails. This is a very tricky situation because you will never know that a handler module failed. Not all rows will be returned, producing erroneous results. This makes it extremely important that you thoroughly test your handler modules .

The handler module is very useful when you want to write to your own tables, not just the regular audit trail tables. On the surface, it may seem that you would simply be replicating functionality that is already available. The following sections will, however, make clear that you can add significant value with this option.

6.2.4.1. Drawbacks with the default FGA approach

Remember that in fine-grained auditing the audit trails are written to a table called FGA_LOG$ in the SYS schema in the SYSTEM tablespace . There are three potential problems with this approach:

1. Because this table contains all types of access to all of the tables being audited by FGA, the contents are very sensitive and must be preserved. However, SYS or any other user with the DBA role can easily delete rows from this table or truncate it, thus removing the FGA trail. For very security-conscious organizations (particularly those governed by regulatory frameworks [e.g., HIPAA, Sarbanes-Oxley, Visa Cardholder Information Security Policy]), this potential for tampering is unacceptable. Securing the audit trail is critical, and the default auditing option does not provide a mechanism to do that.

2. Because the audit trails are generated not only for DML statements but also for SELECT statements, FGA may quickly generate a lot of entries. How serious a problem this is depends upon how frequently the database is accessed and how many auditing options are set. In an OLTP database, the trail (located in the SYS schema and residing in the SYSTEM tablespace) may substantially inflate the SYSTEM tablespace. Even if the table is truncated after periodic archiving, the extended datafiles may not be able to shrink back to their original size, so you can end up with a SYSTEM tablespace that contains lots of unused space.

   An alternative approach is to create a user-defined audit trail and place it in a user tablespace where it can be controlled as conveniently as any other tablespace. You can also specifically design a table of this kind to optimize performance and archival needsfor example, you can partition it to provide the most flexible management.

3. This approach only stores the audit entries; it does not send an immediate alert, notifying anyone that a problem has occurred. Oracle does not support the ability to place triggers on the audit trail table to send emails or alerts. However, in situations where you need to perform a predefined action when audit conditions are satisfied, an automatically executed stored program unit might be helpful. For example, you may want to send an alert via Oracle Advanced Queuing or through Oracle Streams to an enforcement officer when someone selects the salary of the highest-paid executives in your organization. Alternatively, you might want to send an email if a particular action occurs, or just record such instances of high-profile data access in a special table.

6.2.4.2. Creating a user-defined audit facility

I can handle these potential issues by creating a user-defined audit facility. First, I need to create a table to hold the entries. This table could be in any schema, but for security reasons, I want to put it in a schema that will not be used otherwise. So I will use the same schemaFGA_ADMINwe used earlier. Here is an example of such a table:

     /* File on web: cr_flagged_access.sql */       1  CREATE TABLE flagged_access       2  (       3     fgasid            NUMBER(20),       4     entryid           NUMBER(20),       5     audit_date        DATE,       6     fga_policy        VARCHAR2(30),       7     db_user           VARCHAR(30),       8     os_user           VARCHAR2(30),       9     authent_type      VARCHAR2(30),      10     client_id         VARCHAR2(100),      11     client_info       VARCHAR2(64),      12     host_name         VARCHAR2(54),      13     instance_id       NUMBER(2),      14     ip                VARCHAR2(30),      15     term              VARCHAR2(30),      16     schema_owner      VARCHAR2(20),      17     table_name        VARCHAR2(30),      18     sql_text          VARCHAR2(64),      19     SCN               NUMBER(10)      20  )      21  TABLESPACE audit_ts      22  PARTITION BY RANGE (audit_date)      23  (      24     PARTITION y04m01 VALUES LESS THAN      25        (TO_DATE('02/01/2004','mm/dd/yyyy')),      26     PARTITION y04m02 VALUES LESS THAN      27        (TO_DATE('03/01/2004','mm/dd/yyyy')),      28     PARTITION y04m03 VALUES LESS THAN      29        (TO_DATE('04/01/2004','mm/dd/yyyy')),      30     PARTITION y04m04 VALUES LESS THAN      31        (TO_DATE('05/01/2004','mm/dd/yyyy')),      32     PARTITION y04m05 VALUES LESS THAN      33        (TO_DATE('06/01/2004','mm/dd/yyyy')),      34     PARTITION y04m06 VALUES LESS THAN      35        (TO_DATE('07/01/2004','mm/dd/yyyy')),      36     PARTITION y04m07 VALUES LESS THAN      37        (TO_DATE('08/01/2004','mm/dd/yyyy')),      38     PARTITION y04m08 VALUES LESS THAN      39        (TO_DATE('09/01/2004','mm/dd/yyyy')),      40     PARTITION def VALUES LESS THAN      41        (MAXVALUE)      42* );

My purging strategy for this table is rather simpleevery month, I transfer all audit data that is older than one month to a different offline storage location and delete it from the table. Accordingly, I have range-partitioned the table on the AUDIT_TABLE column with one partition per month. When the time comes to purge, I just have to convert the partition to a table using the ALTER TABLE...EXCHANGE PARTITION command and then transfer the contents to tape using Oracle's Transportable Tablespace feature . After that, I simply drop the partition. Every month I need to create new partitions for the upcoming months.

My next task is to create a procedure to populate this table. This could also be performed under the same secure schema as the table (e.g., FGA_ADMIN). This procedure calls the built-in function DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, so I need to grant the EXECUTE privilege on this package explicitly. Connected as SYS, I issue:

     GRANT EXECUTE ON dbms_flashback TO fga_admin;

Now, I create the procedure.

     /* File on web: access_flagger.sql *      1  CREATE OR REPLACE PROCEDURE access_flagger      2  (      3     p_table_owner    IN VARCHAR2,      4     p_table_name     IN VARCHAR2,      5     p_fga_policy     IN VARCHAR2      6  )      7  IS      8     l_fgasid       NUMBER(20);      9     l_entryid      NUMBER(20);     10     l_term         VARCHAR2(2000);     11     l_db_user      VARCHAR2(30);     12     l_os_user      VARCHAR2(30);     13     l_authent_type VARCHAR2(2000);     14     l_client_id    VARCHAR2(100);     15     l_client_info  VARCHAR2(64);     16     l_host_name    VARCHAR2(30);     17     l_instance_id  NUMBER(2);     18     l_ip           VARCHAR2(30);     19     l_sql_text     VARCHAR2(4000);     20     l_scn          NUMBER;     21  BEGIN     22     l_fgasid       := sys_context('USERENV','SESSIONID');     23     l_entryid      := sys_context('USERENV','ENTRYID');     24     l_term         := sys_context('USERENV','TERMINAL');     25     l_db_user      := sys_context('USERENV','SESSION_USER');     26     l_os_user      := sys_context('USERENV','OS_USER');     27     l_authent_type := sys_context('USERENV','AUTHENTICATION_TYPE');     28     l_client_id    := sys_context('USERENV','CLIENT_IDENTIFIER');     29     l_client_info  := sys_context('USERENV','CLIENT_INFO');     30     l_host_name    := sys_context('USERENV','HOST');     31     l_instance_id  := sys_context('USERENV','INSTANCE');     32     l_ip           := sys_context('USERENV','IP_ADDRESS');     33     l_sql_text     := sys_context('USERENV','CURRENT_SQL');     34     l_scn          := SYS.DBMS_FLASHBACK.get_system_change_number;     35     INSERT INTOS flagged_access     36     (     37        fgasid,     38        entryid,     39        audit_date,     40        fga_policy,     41        db_user,     42        os_user,     43        authent_type,     44        client_id,     45        client_info,     46        host_name,     47        instance_id,     48        ip,     49        term,     50        schema_owner,     51        table_name,     52        sql_text,     53        scn     54     )     55     VALUES     56     (     57        l_fgasid,     58        l_entryid,     59        sysdate,     60        p_fga_policy,     61        l_db_user,     62        l_os_user,     63        l_authent_type,     64        l_client_id,     65        l_client_info,     66        l_host_name,     67        l_instance_id,     68        l_ip,     69        l_term,     70        p_table_owner,     71        p_table_name,     72        l_sql_text,     73        l_scn     74     );     75* END;     76 /

The following table summarizes the key points about this code.

Lines	Description
35	Note the input parameters used. In a handler module, you must use only the exact parameters shown here, and no others, and they must be specifically in this orderthe owner of the table, the table name, and the name of the FGA policy. Of course, the names of the parameters can be anything, but their position describes their contents.
2234	These lines show various types of information about the users' actions and sessions. They can be extended to cover as much information as the USERENV context can provide.
34	The current System Change Number is obtained from the function call and will be recorded in the audit trail.
35	Here all the values collected so far are inserted into the FLAGGED_ACCESS table created earlier.

Because of an Oracle bug, the function call SYS_CONTEXT('USERENV','SESSIONID') always returns 0 inside the FGA handler. This has been fixed in Oracle Database 10g Release 2. As of the writing of this book, there was no backported patch for versions prior to that release, so line 2 above will always have the value 0, unless a patch for this bug becomes available and is applied.

Finally, I have to add this procedure to the FGA policy so that it will be called automatically whenever the audit conditions are satisfied.

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_SEL',                             audit_column         => 'SALARY, COMM',                             audit_condition      => 'SALARY >= 150000 OR EMPID = 100',                             handler_schema       => 'FGA_ADMIN',                             handler_module       => 'ACCESS_FLAGGER'                            );     END;     /

From this point on, every time the audit conditions are satisfied, a record is written to the FGA_LOG$ table as well as to the FLAGGED_ACCESS table. In other words, the presence of a user-defined audit handler does not stop the audit records from being written to the normal audit logs. The reason is simple: a handler module is not just for creating a user-defined audit trail; it is also used to call a stored procedure automatically that can do a variety of thingssend email, raise an alert, update some flag, and so on. So, the system audit logs must be maintained in any case.

If your stored information needs to be even more secure, you can create an identical table in a remote database and set up a one-way, read-only snapshot replication between them. Any records inserted into the FLAGGED_ACCESS table on the local database will create an identical row on the remote table. This process is still susceptible to abuse by a DBA who could delete the snapshot logs. However, in a relatively low-activity database, the refresh interval can be set to a low value and the values can be transmitted as soon as they materialize in the source database, which leaves very little time for the DBA to delete the records. Unfortunately, this is the only solution available to us, because distributed transactions do not work inside the handler module, making it impossible to insert directly into the remote table.

Even if the records are deleted, the values can still be obtained from the archived log files using Oracle's Log Miner. This protects the FGA trails in very secure environments.

Besides using the handler module to create a user-defined audit trail, you can also use it to perform other actionsfor example, automatically sending an email to an auditor when highly sensitive data is selected after working hours.