Oracle Security the dbms_fga Package Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

The dbms_fga Package

The central mechanism for the FGA is implemented in the package dbms_fga, where all the APIs are defined. Typically, a user other than SYS is given the responsibility of maintaining these policies. With the convention followed earlier, we will go with the user SECUSER, who is entrusted with much of the security features championed in this book. The following statement grants the user SECUSER enough authority to create and maintain the auditing facility.

grant execute on dbms_fga to secuser
/

The biggest problem with this package is that the polices are not like regular objects with owners. While a user with execute permission on this package can create policies, he or she can drop policies created by another user, too. This makes it extremely important to secure this package and limit the use to only a few users who are called to define the policies, such as SECUSER, a special user used in examples throughout this book.

Now, let’s explore the procedures in the package in detail.

add_policy

We saw earlier how to add a policy to a table or view, but only three parameters of the procedure were used. There are several other very important parameters, as described below.

Audit Condition

HIPAA requirements generally call for specific auditing, not a broad record-everything policy. For instance, you may want to audit only when someone selects claims for amounts more than $500. If the user selects any claim below that amount, it is not worth keeping an auditing record.

FGA can easily handle this. The parameter audit_condition that was not used in the earlier example can be used to trigger auditing selectively. To satisfy the requirement, we could specify the parameter

audit_condition=>'CLAIM_AMOUNT>500’

in the dbms_fga.add_policy procedure. By default, all access queries are audited, regardless of the data returned. The above parameter will restrict the auditing information. Even if the query does not specify the clause, if any of the rows returned contain a claim amount of more than 500, the auditing is triggered.

The parameter can also be provided in multiple conditions, e.g.

audit_condition=>'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500'

Here the auditing will be triggered if either of the conditions CLAIM_AMOUNT>500 or PAID_AMOUNT>500 is satisfied in the retrieved data.

Relevant Columns

Once again, HIPAA requirements could determine the change in strategy. Typically, the law might ask that access only to certain columns be audited, for example to sensitive columns containing Personal Health Information. In your case, the columns are perhaps Social Security Number (SSN), procedures conducted on the patient in that claim, etc. The requirement might state to audit the query only if these columns are accessed. The parameter in the function ADD_POLICY that does this is set as shown below.

audit_column => 'SSN, PROC_CODE'

In this example, the audit records are triggered only if the columns SSN and PROC_CODE are selected in the query. These columns are referred to as Relevant Columns. By default, all the columns of the object are relevant. Specifying this parameter can limit the amount of audit generated.

Based on these two parameters, a complete example of adding a policy is shown below.

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE’

  );
end;
/

Note the columns referenced in the audit_column and audit_condition parameters are different. This might create some confusion on how various situations are handled. It can be understood by using a few simple rules.

* If the query selects the columns mentioned in the audit_column parameter, then check the next step; otherwise, this is not a candidate for auditing.

* If the query selects rows that satisfy the clause in the audit_condition, then the statement is audited; otherwise, this is not audited.

Sometimes the query might reference the columns implicitly, not explicitly naming them. The auditing is triggered in that case, too. For instance, the following query is audited based on the policy defined earlier.

select * from claims;

Even though the columns are not named, the fact that all columns are selected triggers the audit. Another case is

select claim_id from claims where ssn = ‘123456789’;

The following table shows the expected audit triggering in different scenarios.

USER SELECTED RELEVANT COLUMNS	THE AUDIT CONDITION IS EVALUATED TO TRUE	IS THE ACTION AUDITED?
Yes	Yes	Yes
Yes	No	No
No	Yes	No
No	No	No

For instance, in the following example, assume the policy has been defined with the two important parameters as:

      audit_condition=>
        'CLAIM_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE’

The user issues several statements as shown in the following table. The issuance of an audit record and the reason for it is explained after each statement.

1
SELECT CLAIM_AMOUNT
FROM CLAIMS
WHERE CLAIM_ID = 123456
This returns a row where claim amount is 600

Not Audited. Although the audit condition is true (CLAIM_AMOUNT > 500), the query did not specify any of the relevant columns, SSN and PROC_CODE. So it’s not audited.

2
SELECT SSN
FROM CLAIMS
WHERE CLAIM_ID = 123456

The value of claim amount is 600 for this claim.

Audited. The audit condition is true (CLAIM_AMOUNT > 500), the query contained the relevant columns SSN or PROC_CODE. So it is audited.

3
SELECT PROC_CODE
FROM CLAIMS
WHERE CLAIM_ID = 456789

This returns a row where claim amount is 400

Not Audited. Although the selected columns are in the relevant list, the audit condition is false (CLAIM_AMOUNT is not greater than 500). So it is not audited.

4
SELECT CLAIM_AMOUNT
FROM CLAIMS
WHERE CLAIM_ID = 456789

This returns a row where claim amount is 400

Not Audited. Neither the audit condition is evaluated to true (CLAIM_AMOUNT is not greater than 500) nor the relevant columns are in the query. This is not audited.

5
SELECT *
FROM CLAIMS
WHERE CLAIM_ID = 123456

The claim amount of this claim is 600

Audited. Although the relevant columns SSN or PROC_CODE are not explicitly specified, the SELECT * selects them, so the columns are implicitly selected. Hence it’s audited.

6
SELECT COUNT(*)
FROM CLAIMS

Not Audited. The relevant columns are not selected, not even implicitly; hence the action is not audited.

Table 11.3 Effects of Statements on Auditing

Hopefully this table will provide clear examples for when the auditing engages and when it does not. Following this table, you can devise a fine-grained auditing system for your database with the specific requirements in mind.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.