Unfortunately, this comes with a severe performance drawback, as indexed access to the hidden columns gets difficult if not impossible. Let's investigate the issue with an example:
Create the test table FGAC_TEST_TAB:
pkthe surrogate keythe_secretdata we only want to show to privileged usersthe_choiceindicator whether the data is secret (=0) or not (=1)
-- create test table
create table fgac_test_tab
(pk number
,the_secret varchar2(10)
,the_choice number(1,0)
);
-- add the primary key constraint
alter table fgac_test_tab
add constraint fgac_test_tab_pk
primary key (pk);
-- add an index on the secret for fast access
create index fgac_test_tab_secret_ind
on fgac_test_tab (the_secret);
Then we generate test data: 10 rows, every second is set to "secret" using the modulo function.
insert into fgac_test_tab
select level, 'KEY'||level, mod(level, 2)
from dual
connect by level <= 10;
-- verify the contents of the test table
select * from fgac_test_tab;
PK THE_SECRET THE_CHOICE
---------------------- ---------- ----------------------
1 KEY1 1
2 KEY2 0
3 KEY3 1
4 KEY4 0
5 KEY5 1
6 KEY6 0
7 KEY7 1
8 KEY8 0
9 KEY9 1
10 KEY10 0
10 rows selected
We only want to see the value of column
the_secret if the_choice equals 1. Therefore we create a function that generates the predicate to be used by FGAC.create or replace function the_choice_policy
(object_schema in varchar2
,object_name in varchar2
) return varchar2
is
begin
if ( object_name = 'FGAC_TEST_TAB' ) then
-- in a "real" system, we would use an
-- application context as well to decide
-- if the policy should apply the predicate or not
return 'the_choice = 1';
else
return '';
end if;
end the_choice_policy;
/
-- test the policy by executing it
-- - ANY_TABLE should not result in a predicate being applied
select the_choice_policy('ANY_SCHEMA','ANY_TABLE') from dual;
THE_CHOICE_POLICY('ANY_SCHEMA','ANY_TABLE')
-------------------------------------------
1 rows selected
-- - FGAC_TEST_TAB should result in the predicate being applied
select the_choice_policy('ANY_SCHEMA','FGAC_TEST_TAB') from dual;
THE_CHOICE_POLICY('ANY_SCHEMA','FGAC_TEST_TAB')
-----------------------------------------------
the_choice = 1
1 rows selected
Next, we (re-)create the FGAC policy on our test table. The individual options are explained within the code comments.
You might need to grant the execute privilege on the package dbms_rls to your user.
begin
begin
dbms_rls.drop_policy
(object_schema => null -- current schema
,object_name => 'fgac_test_tab'
,policy_name => 'the_choice_policy'
);
exception
when others then null;
-- do nothing if an exception is thrown,
-- the policy just didn't exist yet
end;
dbms_rls.add_policy
(object_schema => null
-- null = current schema
,object_name => 'fgac_test_tab'
-- add the policy on our test table
,policy_name => 'the_choice_policy'
-- arbitrary policy name. must be unique per object.
,function_schema => null
-- the schema where the policy function is defined (null = current schema)
,policy_function => 'the_choice_policy'
-- name of the policy function
,statement_types => 'select'
-- only apply the policy on select
,policy_type => dbms_rls.static
-- in our case, the predicate is always the same - no re-parsing required
,sec_relevant_cols => 'the_secret'
-- policy only applies for column "the_secret"
,sec_relevant_cols_opt => dbms_rls.all_rows
-- we want to see all rows, where the predicate of the policy result in true,
-- we get "null" instead of the actual value in the result set of a query
);
end;
/
Let's check the contents of our test table again. What do we get now? The policy works:
the_secret cannot be seen on all rows where the policy predicate returns false (the_choice <> 1).select *
from fgac_test_tab
/
PK THE_SECRET THE_CHOICE
---------------------- ---------- ----------------------
1 KEY1 1
2 0
3 KEY3 1
4 0
5 KEY5 1
6 0
7 KEY7 1
8 0
9 KEY9 1
10 0
10 rows selected
Can we query the data of a row whose secret we know, but are not allowed to see?
select *
from fgac_test_tab
where the_secret = 'KEY2'
/
PK THE_SECRET THE_CHOICE
---------------------- ---------- ----------------------
0 rows selected
No! As the column is nullified, it does not match our known secret anymore. This behaviour is what we want - otherwise the user could still reveal information he is not allowed to know, as he can make the association of the secret to the values he is allowed to see.
Let's query a row we're allowed to see:
select *
from fgac_test_tab
where the_secret = 'KEY3'
/
PK THE_SECRET THE_CHOICE
---------------------- ---------- ----------------------
3 KEY3 1
1 rows selected
We get the row. Quickly. But what is the access path Oracle uses? Does Oracle use the index we have created on the column
the_secret?You might need to create the plan table first: use the script ?/rdbms/admin/utlxplan.sql.
explain plan for
select *
from fgac_test_tab
where the_secret = 'KEY3'
/
select *
from table (dbms_xplan.display())
/
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2668243172
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 264 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FGAC_TEST_TAB | 8 | 264 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CASE "THE_CHOICE" WHEN 1 THEN "THE_SECRET" ELSE NULL END
='KEY3')
14 rows selected
Uups, Oracle performs a full table scan... No problem with our test table as it only contains a few rows. But image a table with some hundred thousand or even millions of rows!
Why does Oracle not use our index? Because the policy predicate is actually merged into the query predicate we have specified (see the execution plan predicate information).
This results in:
case the_choice when 1 then the_secret else null end = 'KEY3'.The index is not being used because the column value is modified by the CASE function before the comparison takes place.
So what about a function based index then?
create index fgac_test_tab_funct_ind
on fgac_test_tab
(case the_choice when 1 then the_secret else null end);
/
explain plan for
select *
from fgac_test_tab
where the_secret = 'KEY3'
/
select *
from table (dbms_xplan.display())
/
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 380986055
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 264 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGAC_TEST_TAB | 8 | 264 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FGAC_TEST_TAB_FUNCT_IND | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "THE_CHOICE" WHEN 1 THEN "THE_SECRET" ELSE NULL END ='KEY3')
14 rows selected
Yes! The function based index is being used.
Unfortunately, this solution does not help in most real life situations. If the predicate is not always the same, we cannot create a function based index.
It should be possible to force Oracle to use two filters. The first one should apply the actual predicate the user has specified and then a second one should apply the policy. Unfortunately I have not found a way to achieve this. If you know, please let me know by posting a comment!
Use the following SQL to see the predicates that have been applied to the latest queries:
select distinct policy, predicate, sql_text
from v$vpd_policy p, v$sql s
where s.child_address = p.address
/
POLICY
------------------------------
PREDICATE
------------------------------
SQL_TEXT
-------------------------------------------------------------------------
THE_CHOICE_POLICY
the_choice = 1
explain plan for select * from fgac_test_tab where the_secret = 'KEY3'
No comments:
Post a Comment