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!!!!
No comments:
Post a Comment