Section 6.1. Introduction to FGA

6.1. Introduction to FGA

To help you make the most of FGA, we will start this chapter with a review of general auditing requirements, and then move on to using FGA in a simple example.

For FGA to work correctly, your database must be in cost-based optimizer (CBO) mode; the queries must be using the cost-based optimizer (i.e., they must not be using RULE hints); and the tables (or views) in the query must have been analyzed, at least with estimates. If these conditions are not met, FGA might produce false positives , that is, might write an audit trail even if the necessary conditions have not been met. Note that Oracle Database 10g uses the cost-based optimizer by default, as described later.

6.1.1. What Is Auditing?

One of the basic requirements of computer security is accountability , that is, tracking the activities of users in such a way that you will be able to later associate specific actions with specific users. Oracle's way of achieving accountability is through its auditing feature, which tracks users' actionsin other words, who did what. Suppose that a user Scott issues this statement:

     SELECT * FROM emp;

With proper auditing settings, the database will record the fact that Scott selected something from the table EMP, along with a host of other informationthe time of the access, the particular terminal he used, and more. This information, known as an audit trail, can be used to investigate Scott's actions, if need be, in the future. Because the audit trail is owned by user SYS, ordinary users cannot alter the contents of the table, and hence cannot hide their tracks. The traditional Oracle audit trail is stored in the database table AUD$, owned by SYS, and information in it is made available through data dictionary views such as DBA_AUDIT_TRAIL. Audit trails can also be written to operating system files instead of to database tables.

By default, the statements issued by the user SYS are not audited. If you want to audit SYS objects as well, you have to set the database initialization parameter AUDIT_SYS_OPERATIONS to TRUE. However, in that case, the audit records are written to operating system files, not to database tables. As a general rule of thumb, you should never issue regular DML statements as SYS.

Regular auditing, however, has a serious limitationit records the fact that Scott selected something from table EMP, but not what he selected. And in most cases, the question of what is as important as who. For example, in the case of a financial services company, the financial data of customers must be protected from prying eyes. To ensure privacy, the company might want to establish a trail of people seeing sensitive customer records. In this case, not only the identity of the person selecting the data, but the exact records she viewed must be recorded. Regular auditing, however, does not capture the exact records that users viewed, just the fact that they viewed something from the table.

One approach to capturing information about DML statements that change data is to build triggers on a table. However, most DBAs also want to capture information when someone selects from (as well as changes) a table. Because SELECT statements cannot have triggers associated with them, you cannot record such access information via DML row triggers. In Oracle versions prior to Oracle9i Database, there was no facility at all that could be used to capture SELECT statements.

Starting with Oracle9i Database, however, FGA fills this gap in functionality. Using this feature, you can track table activity without having to write or maintain triggers. Oracle records FGA auditing information in a different table from the one used by traditional auditingthat table is FGA_LOG$ in the SYS schema; the data dictionary view on the table is DBA_FGA_AUDIT_TRAIL.

You will find toward the end of this chapter a comparison of the various types of auditing now available in Oracle; this will help you determine which approach to use to meet your various application auditing requirements.

6.1.2. Why Learn About FGA?

There are many reasons why FGA is a very helpful tool for DBAs. We'll take a quick look at these reasons here and employ them throughout the chapter.

Enhance security

Security is, of course, the primary purpose of FGA. The ability to record the activities of users against the database is an important security characteristic, and FGA is the best way to record information about these activities. (In certain cases, it is the only way, as traditional auditing does not capture such information as the exact query issued by a user.)

Analyze SQL execution

FGA reveals who did what. Seeing the actual SQL statements issued by users provides a great tool for DBAs to see different types of statements issued from the applications from specific users and at certain times. Such information is helpful in deciding on indexing schemes or analyzing frequencies. It's even more useful in Decision Support Systems where queries are generally ad hoc in nature and can't be predicted in advance. Because it also shows other relevant information, such as timestamp and originating terminal, and because all of the information is in a table, problem diagnosis and access analysis become very easy.

Optimize performance when bind variables are used

FGA audit trails also show bind-variable values, which are used widely in any well-designed application. How do you know what different values are passed during the application run? This information can help you decide more quickly and easily whether you need to define indexes on a table. The FGA audit trails will reveal the kind of information that will help you make such decisions.

Emulate SELECT triggers

Although FGA was not designed specifically for this purpose, a hidden gem is that FGA effectively allows you to define or emulate "SELECT triggers," which are otherwise not available in Oracle. FGA lets you specify execution of a PL/SQL procedure when data is selected, as well as when it is manipulated via INSERT, UPDATE, and DELETE statements. FGA can mimic a SELECT trigger by automatically executing a stored procedure when a user issues SELECT. You can further control execution by placing conditions (e.g., columns selected, value of columns referenced) upon its execution.

6.1.3. A Simple Example

Let's move on to show a simple example of using FGA. This example will demonstrate the basic features and functionality of FGA. Suppose that in the HR schema of the Human Resources departmental database I have defined the EMP table as follows:

     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)

To satisfy security and privacy requirements, I need to audit any queries against this table. We can't use database triggers or traditional auditing, so I turn to FGA. My first step is to create a policy, which I can do using the ADD_POLICY program provided in DBMS_FGA as follows:

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

You will need to have EXECUTE privileges on the DBMS_FGA package or to be connected as a SYS account to run code like that shown in the preceding example.

The FGA policy, in general, controls how the statements executed against this table will be audited. It determines the conditions under which auditing should be triggered and the actions to be taken. In the preceding example, I create a policy called EMP_SEL and apply it to the table EMP under the schema HR. The policy name must be unique in the database instance. You can define up to 256 separate policies on a single table. Support for multiple policies gives you a high degree of flexibility and eases management of the policies. I'll offer more details on policy definition and management later in the chapter.

Although both fine-grained auditing (FGA) and row-level security (RLS) use the term policy, policies work quite differently for each feature, as I explain in this chapter and in Chapter 5. One similarity is that, like its namesake in RLS, a policy in FGA is not a "schema object"that is, no user owns it. Anyone with the EXECUTE privilege on the DBMS_FGA package can create a policy and drop one created by a different user. So, it is a good practice to revoke the EXECUTE privilege from the PUBLIC role, if granted. Grant the EXECUTE privilege on this package only to people who actually need it.

Adding a policy is all that's needed to place the table under surveillance; now anyone querying this table will have his actions recorded. So, if user Scott issues the following statement:

     SELECT salary       FROM hr.emp      WHERE empid = 1;

it will be recorded in the audit trail, SYS.FGA_LOG$.

To see the audit information, execute a query against the DBA_FGA_AUDIT_TRAIL data dictionary view. (You will, of course, need SELECT privileges on this view, either through a role or a directly granted privilege, like SELECT ANY DICTIONARY.) Here is an example:

     SELECT db_user, sql_text       FROM dba_fga_audit_trail      WHERE object_schema = 'HR' AND object_name = 'EMP';

And the output given the previous query against EMP is:

     DB_USER SQL_TEXT     ------- -----------------------------------------------     SCOTT   select salary from hr.emp where empid = 1 

And there it is, in broad daylightnot only the fact that Scott selected from this table, but the exact SQL statement that was executed.

If you have an FGA policy on a table, you cannot reorganize the table online using Oracle's built-in DBMS_REDEFINITION package. An attempt to redefine a table under FGA will result in the error ORA-12090: cannot online redefine table. So, be sure to consider a table's future reorganization needs when you put an FGA policy on it.

6.1.4. Auditing Differences Between Oracle Database Versions

There are a few differences between how both auditing and fine-grained auditing work in Oracle9i Database and Oracle Database 10g. The major differences are listed here. Later sections, in particular "FGA in Oracle Database 10g," provide more-detailed information on using these features.

• In Oracle Database 10g, the AUDIT statement , which is used in regular auditing to record the fact that someone selected from a particular table, has been extended to capture information about the SQL statement, something that was not available in earlier releases. (Although it may seem that this enhancement makes FGA and regular auditing in Oracle Database 10g nearly identical, the functionalities are quite different, as we'll explore later in this chapter.)

• In Oracle9i Database, only SELECT statements are audited by the FGA feature, not the DML statements INSERT, UPDATE, and DELETE. In that release, the only way to capture what has changed is to build triggers on those statements and write to a log table. In Oracle Database 10g, on the other hand, FGA can capture these DML statements as well. Although triggers are needed less often in Oracle Database 10g, it sometimes makes sense to use them in place of FGA. Both approaches have their advantages and disadvantages, as we'll explore later in this chapter.

• In Oracle9i Database, if any column of the list of columns you provide when you add a policy is referenced, FGA is invoked. In Oracle Database 10g, you can choose whether auditing is to be done when any one of the columns is referenced or only when all of the columns are referenced.

6.1.5. What Else Does FGA Capture?

In the example query from the audit trial, I selected only the user who issued the SELECT statement and the SQL statement that was actually issued by the user. FGA records more information in the audit trail, most important of which is the time of the action. The TIMESTAMP column in the DBA_FGA_AUDIT_TRAIL view records the timestamp, which you will probably want to view using this format so you can see the full timestamp:

     TO_CHAR(TIMESTAMP,'mm/dd/yyyy hh24:mi:ss') 

There are a number of other important columns that can be used to further establish the user's identity and provide details on the action that was audited, all of which help in accountability and analysis. Key columns in this view include:

DB_USER

User who issued the statement.

SQL_TEXT

SQL statement the user issued.

TIMESTAMP

Time the user performed the action.

OS_USER

Operating system user who connected to the database.

USERHOST

Terminal or client machine from which the user is connected.

EXT_NAME

Sometimes the user is externally authenticatedfor example, via LDAP; in such cases, the username in the external authentication mechanism is relevant and is captured in this column.

SQL_BIND

Values of the bind variables used in the query, if any.

6.1.6. Using FGA with Flashback Query

Consider the following scenario to understand why using FGA with Oracle's flashback query feature might be useful. Suppose that I (the DBA) look at the audit trail and notice that this SQL statement was issued by the user Scott:

     SELECT salary FROM hr.emp WHERE empid = 100;

I happen to be represented by the employee number 100, and I realize with a shock that Scott has taken a peek at my salary! Now, Scott has always wanted my job, so it's no surprise that he checked out my salary. But then I think to myself: I just got a series of raises, changing my salary from 12,000 to 13,000, then 14,000, and finally 15,000, which gives me pause to wonder: did Scott see my new or old salary? And if he saw the old one, which one did he actually see12,000, 13,000, or 14,000? If I now issue the same query Scott issued, I will see the current, updated value15,000not the old value that was present when he issued his own query.

Fortunately, Oracle9i Database offers a feature known as flashback query to help solve this dilemma. Using this feature you can select a value as of a specific time in the past, even though that value may have been updated and committed. Let's see how it works.

Suppose the salary was 12,000 at first.

     SQL> SELECT salary FROM emp WHERE empid = 100;         SALARY     ----------          12000

Then it was changed to 13,000 on June 10, as per a raise approved by my boss, and entered into the HR database.

     SQL> UPDATE emp set salary = 13000 WHERE empid = 100;     1 row updated.     SQL> COMMIT;

I was not, however, happy with the raise. On June 11th, I renegotiated, and it was raised to 14,000.

     SQL> UPDATE emp set salary = 14000 WHERE empid = 100;     1 row updated.     SQL> COMMIT;

Still, I wanted more. (What can I say? I have lots of expenses to consider.) After a long discussion with my boss, I finally convinced her that I am worth my weight in gold. On June 12th, the value was changed to 15,000.

     SQL> UPDATE emp set salary = 15000 WHERE empid = 100;     1 row updated.     SQL> COMMIT;

Now, on June 13th, the salary is 15,000. If I then want to see the value as of June 9th, I will have to use the flashback query feature. So I issue a query in the following form using the AS OF syntax:

     SQL> SELECT salary FROM emp AS OF TIMESTAMP       2  TO_TIMESTAMP('6/9/2004 01:00:00','MM/DD/YYYY HH24:MI:SS')       3  WHERE empid = 100;         SALARY     ----------          12000

Similarly, using other appropriate timestamp values, I can then find out the values of the column at other times as well.

When you specify a timestamp in the AS OF clause after the table name, Oracle gets the value from its undo segments, not from the actual tableprovided that the undo segment is big enough and that the pre-change value of the column is available in it. In this example, I assume that the undo segment is expected to hold four days worth of changes. You can set or change this number using the database initialization parameter UNDO_RETENTION, which is specified in seconds. The value I have selectedfour daysis not unusual for a slowly changing database like the HR one I'm using in this example, but in the case of a rapidly changing OLTP databases, such as a reservation system, I would need to specify a larger value.

When a database change is made, Oracle records an incremental counter, the System Change Number (SCN), which uniquely represents a change. Because each change is tracked with an SCN number, Oracle can get the old value by first finding its SCN number and then grabbing it from the undo segment for that SCN number.

In my example, I specified a timestamp, not an SCN number. How does Oracle associate an SCN number with a timestamp, and vice versa? This is done through a table maintained by the SMON process, SMON_SCN_TIME, which records a timestamp and its associated SCN. Note, however, that the SCNs are recorded in intervals of five minutes. Here is an example of using the table.

     SELECT time_dp, scn_bas FROM sys.smon_scn_time

The output is:

     TIME_DP                SCN_BAS     ------------------- ----------     06/26/2004 15:29:26 1167826228     06/26/2004 15:34:33 1167826655     06/26/2004 15:39:41 1167827058     06/26/2004 15:44:48 1167827476     ... and so on ...

Note the five-minute gaps in the list of timestamps. If a timestamp is specified in the AS OF clause of the flashback query , Oracle assumes that for that five-minute interval, the SCN number is the same. For instance, in the above output, the query shows the SCN number 1167826228 at 15:29:26, and 1167826655 at 15:34:33, and assumes that the SCN is always 1167826228 for the five minutes between them. That's obviously not true: the SCN has increased from 1167826228 to 1167826655, a jump of 427 for the five-minute period, which must have gradually increased as the result of a lot of changes not recorded in the table SMON_SCN_TIME. Because the flashback query does not get any more granular than the SCN number, it assumes the SCN number to be constant in that period. Therefore, it looks up the same SCN number1167826228at 15:29:26, at 15:30:00, and so on until 15:34:33. So if you specify any of these timestamps in the AS OF clause of the flashback query, Oracle returns the same data, because it looks it up against the same SCN number.

Using the timestamp in a flashback query will return the results within a five-minute interval, not to a more granular time frame. To get to a specific timestamp, use the AS OF SCN clause instead.

To get these actual values, you need to specify the SCN number directly. In the previous case, you can issue the query:

     SELECT salary       FROM emp AS OF SCN 14122310350      WHERE empid = 100;

This gets the value as of the SCN 1167826230, which will return the correct value as of that specific SCN, rather than rounding off in a five-minute interval.

The SCN number is a key component of the fine-grained audit trails. SCNs allow you to flash back tables to determine pre-changed values as of a certain time. In the view DBA_FGA_AUDIT_TRAIL, the column SCN represents the SCN during the time of audit trail generation. To find out the value of the column SALARY that the user Scott actually saw, you could use the following query to find out the SCN during the time Scott queried the record.

     SELECT SCN       FROM dba_fga_audit_trail      WHERE object_schema = 'HR' AND object_name = 'EMP';

Suppose that this query returns 14122310350. You could then specify:

     SELECT salary       FROM emp AS OF SCN 14122310350      WHERE empid = 100;

to see the exact value of the column SALARY that he saw.

Having a record of what the user saw at the time he issued the query is important to assign accountability, so the role of the SCN information in FGA audit trails is vital. Note, however, that the amount of undo information available to you depends on the size of the undo tablespace and the setting of the UNDO_RETENTION_PERIOD database initialization parameter. To be effective, you should issue the flashback pretty quickly. If you don't, the data may be overwritten, thus nullifying the chance of an accurate flashback.

If it's important to be as accurate as possible in determining the value of a column as of a specific time, do not use a timestamp in the AS OF clause ; use the SCN instead.