FGAC / EXEMPT and resulting Audit records – More explored

In my previous post I demonstrated, the potential for excessive audit record generation in Oracle 11g. In this post, I’ll explore the effect of a “Keyed function call” from view (or select statements) on generation of BY ACCESS audit records.

I’ll use the same setup as described in the previous post for this demonstration too.

Setup:

In addition to previous setup, we’ll create a function T_ROW that selects and returns value of T.C2 for given key T.C1.

Here is the function…
CREATE OR REPLACE
  FUNCTION t_row(
      in_id NUMBER)
    RETURN VARCHAR2
  AS
    v2 VARCHAR2 (100);
  BEGIN
    SELECT t.c2 INTO v2 FROM hr.t WHERE t.c1 = in_id;
    RETURN v2;
  END;

We’ll modify the view, to select a column using the function, show below…

create or replace view view_t as
SELECT t.”C1″,
  t.”C2″,
  (SELECT COUNT(*) FROM t t1 WHERE t1.c1 = t.c1
  ) scalar_cnt,
  t_row(t.c1) func_value
FROM t;

Let us verify the result-set of the view.
SQL> revoke exempt access policy from hr;
 
Revoke succeeded.
 
SQL> connect a/a;
Connected.
SQL> set line 200 ;
SQL> set wrap off;
SQL> col func_value format a20;
SQL> select * from hr.view_t;
 
        C1 C2           SCALAR_CNT FUNC_VALUE
———- ———— ———- ——————–
         1 AAAA                  1 AAAA
         2 AAAA                  1
         3 AAAA                  1 AAAA
         4 AAAA                  1
         5 AAAA                  1 AAAA
         6 AAAA                  1
         7 AAAA                  1 AAAA
         8 AAAA                  1
         9 AAAA                  1 AAAA
        10 AAAA                  1
 
10 rows selected.
 
SQL>

Check the result set above and let us review the cause of this behavior.

User A has been granted EXEMPT ACCESS POLICY system privilege. So when user A, selects from HR.VIEW_T which directly accesses table T (twice) the FGAC policy function is by-passed, returning all 10 rows.

However, since the column FUNC_VALUE is derived from a call to function HR.T_ROW, which executes under the privilege of user HR (check definer rights versus invoker rights) and user HR cannot bypass FGAC policy (though HR owns all the objects) only rows that satisfy the FGAC policy are returned. For a quick review of FGAC policy function defined effect, click here.

Review Audit Trail:

Now with this set up let us check the audit record generated by user A when accessing the view HR.VIEW_T.

SQL> select * from hr.view_t;
 
Execution Plan
———————————————————-
Plan hash value: 3335345748
 
—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |    10 |    80 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL | T    |    10 |    80 |     3   (0)| 00:00:01 |
—————————————————————————
 
Predicate Information (identified by operation id):
—————————————————
 
   2 – filter(“T1”.”C1″=:B1)

Here table HR.T is accessed twice (using FTS), and for each access and audit record is generated for each access. One of the audit record is for EXPLAIN plan.
SQL> select username, count(*) from dba_audit_trail group by username;
 
USERNAME                         COUNT(*)
—————————— ———-
A                                       3
 
SQL>

Now let us see what happens, if we add an index on T.C1 and use the index for accessing the view HR.VIEW_T.

We’ll create an index, add primary key and gather statistics.
SQL> create index t_indx on t (c1);
Index created.
 
SQL> alter table t add (constraint t_pk primary key (c1) using index t_indx enable validate);
Table altered.
 
SQL> insert into t select level + 10 c1, ‘BBBBB’ from dual connect by level < 10001;
10000 rows created.
 
SQL> commit;
Commit complete.
 
SQL>  analyze table t compute statistics ;
Table analyzed.
 
SQL> analyze table t compute statistics for all indexed columns;
Table analyzed.
 
SQL>

Let us check the explain plan and audit records when user A selects from HR.VIEW_T.
SQL> select * from hr.view_t
10010 rows selected.
 
Execution Plan
———————————————————-
Plan hash value: 700470346
 
—————————————————————————-
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT  |        | 10010 | 80080 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |        |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_INDX |     1 |     3 |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL| T      | 10010 | 80080 |     9   (0)| 00:00:01 |
—————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   2 – access(“T1”.”C1″=:B1)
 
 
Statistics
———————————————————-
      11595  recursive calls
        127  db block gets
      32080  consistent gets
         13  physical reads
      23228  redo size
     175242  bytes sent via SQL*Net to client
       7757  bytes received via SQL*Net from client
        669  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
      10010  rows processed
 
SQL>

The audit records generated is still 3.
SQL> select count(*) from dba_audit_trail;
 
  COUNT(*)
———-
         5
 
SQL>

 
The experiment above demonstrates that in release 11.1.0.7 of Oracle 11g, audit records generated due to EXEMPT ACCESS POLICY (per access) with FGAC policy in effect, is managed as expected.

However, under certain conditions I have experienced excessive audit record generation.

I’ll explore that more in future postings.
 
Till then BYE!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s