We have been performing some tests on VPD and figured that there is a big difference if we apply the policy as static when compared to dynamic. Default I think is dynamic.
That causes the underlying function to be parsed as many times as the underlying object is accessed.
This is an overhead and in our test cases, it results in 25% degradation. Hence it might help to create the policy as static and not dynamic

Tom -- I have a materialized who is using a table on which we have a security policy using RLS.
When I tried to refresh(complete) this materialzed view using dbms_refresh.snapshot --it gives me an error saying fine grain access control voilated --probably it was ora-1531.But when I disabled the policy ,then refreshed this view it worked. After refresh , I enabled the policy.
Is it the right way to do this refresh or I am doing something which I should not.

Tom,
Thanks for the solution.

I would like to setup this security for an application
which makes connection to the database from Visual Basic.
I would like to set the context by calling the context setup procedure from the logon trigger itself. But my problem is getting context value from the user as part of the connection.
Something like this...
connect scott/tiger 10
where 10 is the parameter value for the set_dept_ctx procedure which will be called from the LOGON trigger.

The question is:
1. Is it possible or correct approach to set the context from the LOGON trigger. If yes how do I pass a value as part of the connection to the database so that the LOGON trigger can grap that value.

My main purpose is to make the context procedure transparent to the application. If I can set this from the LOGON trigger then the application does not need to know or call the context procedure to set the context value after making connection to the database.

Thanks
Pushparaj

and -- if the user gives you the value, I'm confused? Why bother at all then?
I mean -- I'll just pass you the value of the dept's I want to see, so you
really won't be precluding me from seeing any at all???

Tom,

The user will be told with a value from his manager
on what he has to work on and other than that particular data he should not see or know what are the other data resides in the table.

For example, Manager asks user A to work on DEPTNO 10
and so the user A should only see DEPTNO 10 data.
User B is asked to work on DEPTNO 20 and the USER B should
only see DEPTNO 20 data. So actually the Manager directs the person manually on what to see and work on. The users
does not know about the data in the table.

The users also have permission to logon to the database by other method other than through the application. like sqlplus. That's why I would like find out the best possible way to set the context value.

Thanks
Pushparaj

Hi Tom,

I'm very impressed with the whole FGAC / VPD features within 9i, and use them a lot for our systems. They make implementing a security system very easy to manage / maintain and you don't need to bother about whether an app references a table or view (and what predicate to put on it of course!).

One of the things I was wondering though, it used to be the case that a good mechanism for "securing" an app was to only give access to views / stored procs instead of direct access to the tables. Is this kind of thing still recommended with the advent of FGAC?

Also, a similar question, but, if I did give an app access to a view on a table only rather than direct access to the table, and of course included the security predicate in the view, presumably this would have a performance benefit as opposed to a table policy (if only for the context switch to the PL/SQL)?

What's your recommended approach for apps where row-level
security is required?

Thanks in Advance

Thanks Tom,

I suppose, like you say, it's a trade off. I almost consider FGAC (i.e. single table policy) to be like the forms DEFAULT_WHERE clause... (stick with me on this one!), in that you get : select * from t and then try and modify the where clause.

The problem is just like you get in forms when the "security" information is actually in another table and is better derived by a direct join (i.e. the from clause) rather than a IN / EXISTS, you would base the block on a view rather than the table itself. In this case, wouldn't a view mechanism be better from a performance point of view with the security info in the predicate?

Also, a related question, I've done quite a bit of work now with app contexts, and understand the concept of global app contexts (setting the session_id etc.) but can't quite figure out under what circumstances this would actually be beneficial? I understand that it can negate the need to pass loads of information between web pages with disconnected sessions, say, and simply have to pass a session ID around, but is this the only situation? What circumstances have you used global app contexts?

Look forward to hearing your reply

Tom,

Its great pleasure to read through the discussions on your website. Each one of them is a goldmine!!

I have a simple question:
Do materialized views work with FGAC? Will FGAC simply ignore the presence of MV and always retrieve data from tables?

thanks.

is there any way to set multiple values for a context varable.
for example i have the following situatoin and for each patient i have to have a list of users who can access them .
Privacy 1
Basic Information = Assigned doctor and Superuser of Assigned Division like SURGICAL DIVISION
ALL THE COLUMNS = Assigned Caseworker and Super Business User of Assigned Division
Privacy 2
Basic Information = Assigned Division
ALL THE COLUMNS Detail = Assigned Division
Privacy 3
Basic Information = Assigned Division and Super User of Assigned Division
ALL THE COLUMNS Detail = Assigned Division and Super User of Assigned Division
Privacy 4
Basic Information = Assigned Division and OTHER Division
ALL THE COLUMNS Detail = Assigned Division and Super User of OTHER Division
Privacy 5
Basic Information = Assigned Division and OTHER Division
ALL THE COLUMNS Detail = Assigned Division and OTHER Division
Privacy 6
Basic Information = All Users
ALL THE COLUMNS Detail = Assigned Division and Super User of OTHER Division
Privacy 7
Basic Information = All Users
ALL THE COLUMNS = Assigned Division and OTHER Division
can we use context here or what is the best solution.
thanks

Yes I'm not clear in my question.
Those 1..9 are the values for my privacy indicator column.
Based on those values and its definition i have a set of user groups who can access the record with the privacy indicator value.
since there are going to be multiple values for a user group list im not able to fit the same into the context method.
How do a set access to user groups based on a table value.
Thanks again

Say for example , when the user got assigned to new cases when they are logged in . Since the authentoication is done thru context , it sets the values when the user is logged in.
Is there any way to refresh the context values when we are using the same session .

Thank You very much for the explanation on setting the multiple values for a context.

If i reset the context values , will it automatically reset the user sessions.

If i reset the context values , will it automatically reset the user sessions.

I'm not sure how this second posting got posted.Sorry for that.Your Answer is very usefuol and I'm working on this.I will be back if i have any more questions.
Thanks Again and sorry for the confusion.

PROCEDURE set_security(userlist VARCHAR2,Caselist VARCHAR2) as
Cursor cursor_case is select * from header;
Begin
bwhere := 0 ;
For thecase in cursor_case
Loop
If theprivate = 9 then
bwhere := bwhere ||','||thecase;
Elsif theprivate = 7 then
bwhere := bwhere ||','||thecase;
Elsif theprivate = 6 then
bwhere := bwhere ||','||thecase;
Elsif theprivate = 5 then
-- some code
Elsif theprivate = 4 then
--code
bwhere := bwhere ||','||thecase;
Elsif theprivate = 3 then
-- some code
bwhere := bwhere ||','||thecase;
Elsif theprivate = 1 then
bwhere := bwhere ||','||thecase;
end if;
End loop;

-- DBMS_SESSION.SET_CONTEXT('camp_CTX', 'header', bwhere);

End set_security;
END;

I want to set all the values which are returned by a bwhere like this (1.23,234,45,567,56776) as a context value.
So that when i query header table i will get where caseid in (1.23,234,45,567,56776).
I don't understand how this I works here .

dbms_session.set_context( 'my_ctx', 'privacy_indicator_column_' I, '' );

Is there a way to apply VPD dynamically? I mean can I use DBMS_RLS.ADD_POLICY at run time instead of having it attached in the design time?

We have a product which we would like to install on different databases ( Oracle , Sybase etc) , if we have VPD attached on Table during design time , we cannot install our server model on the Sybase db for example, we are looking for a way to have it attached at run time.
Any suggestion.

Thanks

Tariq

Sorry I was not too clear in explining my problem. When we create a policy we do following on a table/view at dsign time
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'APPLID',
object_name => 'EMP',
policy_name => 'DEPT_POLICY',
function_schema => 'APPLID',
policy_function => 'DEPT_POLICY_FUNCTION',
statement_types => 'SELECT'
);
END;

So policies are attached to the table. Is it possible to add the policy on runtime , ie some thing prior to running any DML policies are attached to the table and then VPD add the predicate on the table? My problem is that, some of our clients are intersted in VPD and some not and also for some clients we install our product on Sybase or DB2 databases so we cannot use VPD on these databases. I was looking for some kind of mechanism where I can turn VPD on and off based on type of installtion withought changing/adding lot of code

Hope this made it clearer to you.

Tariq

yes thats the word "univeral installation", this is what I want to achieve, i think now its clear to me, I can have a SQL file of VPD code which installation can call when user select VPD option for Oracle db.

Thanks

Tariq

My appologies if you think this is an additional question. The normal working day in the UK never corresponds with the time you have available to take questions. It is several months (and I visit this site several times a week) since you have had your 'open for business' sign up over here!

My related question is is it possible to apply VPD at arms length. We have an OLTP system that uses complex VPD based 'rules' to filter the data our users can see.

We need to build a system that allows us to submit scheduled jobs to a common dbms_job queue that run with the VPD pivileges of the user submitting the job. Our initial idea involved constructing a table with the Oracle USER and function to be executed stored. Our dbms_job would wake up at scheduled intervals and interrogate the table and execute the functions listed. However we need to ensure the job only sees those records the USER is allowed to see.

Hi Tom,

Do we usually create a VPD policy for INSERT operations?
I understand the policy for SEL/UPD/DEL but couldn't think
of an example for INS. I may just be missing something.

As always, you have been very helpful to us. Thanks.

Makes perfect sense to me now. Thanks a lot Tom.

Tom,

Our requirement is as follows:

It is a 3 - tier application, where all users connect to the database thru a jdbc call using the same application schema in the database.

We like to implement the VPD using the application login ids..These logins pass thru' our security shell for necessary validation. How to implement VPD in this kind of scenario? i.e policy implementation based on the application login ids..

Kindly give an example for our better understanding..

Regards

Your discussions on FGAC/VPD are just so much valuable. Thanks Tom!

I have a scnerio here where users can access a common table but can access only the data that belongs to their specific Org_Id (Organization). Each Org will be using a common user. APPS is the super user that can see data for all Orgs.

Here's what I've done : eg Org HR Org_Id = 84

1. Gave grants to HR_USER schema to access the common table APPS.RECEIPTS

2. Updated the APPS.HR_ORGS table to establish the relationship between Org_id and 'HR_USER' schema.
Update APPS.HR_ORG
set org_user = 'HR_USER'
where org_id = 84;
3. Created function to get the Org_id based on user
CREATE OR REPLACE FUNCTION APPS.get_org
RETURN NUMBER
AUTHID DEFINER -- defined to use APPS privileges
IS

CURSOR user_cur IS
SELECT org_id
FROM hr_orgs
WHERE org_user = (SELECT user
FROM dual);

v_org_id NUMBER;

BEGIN

OPEN user_cur;
FETCH user_cur INTO v_org_id;
CLOSE user_cur;

RETURN v_org_id;

END get_org;
/


4. Created function to set the predicate :
CREATE OR REPLACE FUNCTION APPS.sec_function( p_schema in varchar2, p_object in varchar2 )
RETURN varchar2
AS
BEGIN
if ( user = 'APPS' ) then
return '';
else
return 'org_id = apps.get_org';
end if;
END;

5. Created security policy on APPS.RECEIPTS :
begin
dbms_rls.add_policy
( object_schema => 'APPS',
object_name => 'RECEIPTS',
policy_name => 'RCPT_POLICY',
function_schema => 'APPS',
policy_function => 'SEC_FUNCTION',
statement_types => 'select, insert, update, delete' ,
update_check => TRUE );
end;

This works and seems pretty slick to me. No views needed.... However, I would like to have an opinion from you :
1. Is this the best was to handle this ?
2. For function APPS.get_org should I be using
select SYS_CONTEXT('USERENV', 'session_user')
from dual ;
3. Any advantage in using Application contexts ?
4. Should I specify the policy to be static ?

Appreciate your help on this.

Tom, as always your logic is excellent! I like your idea, but I tried something different, just for the heck :
I created a security_pkg AUTHID DEFINER with the predicate sec_function and set_context procedure. Instead of calling set_context from a schema logon trigger, I tried calling it from the predicate function and got the error 'ORA-28112 failed to execute policy function'. Why and how to debug ?

FUNCTION SEC_FUNCTION( p_schema IN VARCHAR2
, p_object IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
IF ( user = 'APPS' ) then
return NULL;
ELSE
IF sys_context( 'org_context', 'org_id') IS NULL
THEN
APPS.security_pkg.set_context;
END IF;
RETURN 'org_id = sys_context ( ''org_context'', ''org_id'' )';
END IF;
END;

If I comment out the set_context call and instead do RETURN 'org_id = sys_context ''dnr_org_context'', ''org_id'' )';
I don't get the error, but of course the logic won't work...

ctl-f for 28112


but you could just:

SQL> exec dbms_output.put_line( sec_function( '', '' ) );

and see what you see (perhaps you created the context wrong and didn't:

create context org_context using apps.security_pkg;
 

Oops, Tom I forgot to mention that I created the context as :
CREATE CONTEXT org_context USING security_pkg.set_context;

Thanks.

Yep, just figured that out. My boo-boo - I could have looked up the syntax - Sorry. Thanks so much, Tom!

This actually works ! You are the best ! I tried your suggestion calling the set_context from an AFTER LOGON ON SCHEMA trigger and that worked too... :)

Tom, I'm trying to avoid Triggers just so that I don't have to give the org_user schemas CREATE ANY TRIGGER grants and create the trigger in every user_schema. Having the logic in the sec_function eliminates the trigger issue and also eliminates the multiple parse problem, right? What do you say - couldn't I use this method instead ?

Tom, that works like a charm. Thanks so much!

Tom,

What system table/view can I query to list the Contexts a schema owns : eg CREATE CONTEXT my_context USING <owner>.my_security_fn

I tried looking up session_context, v$context .. but could not find my_context listed. Thanks for the help.

Yes, that's exactly what I wanted. THANK YOU!

Tom,

I would like to write a one time script to dynamically assign a security policy to all the tables for a given schema using table name as bind variable. Could you please help me out.
declare
cursor c is
select
object_name
from all_objects
where owner = 'ORG_FG'
and object_type = 'TABLE'
;
type t_tab is table of c%rowtype;
l_tab t_tab;
lsql varchar2(2000);

begin
dbms_output.put_line ('Selecting ORG_FG tables ...' );
open c;
fetch c bulk collect into l_tab;
close c;
dbms_output.put_line( l_tab.count || ' rows selected' );
for i in 1 .. l_tab.count
loop
dbms_output.put_line( l_tab(i).object_name );
lsql := ' SYS.DBMS_RLS.ADD_POLICY (
object_schema => ''ORG_FG''
,object_name => :l_tab(i).object_name
,policy_name => ''FG_ORG_POLICY''
,function_schema => ''APPS''
,policy_function => ''ORG_SECURITY_PKG.ORG_PREDICATE''
,statement_types => ''SELECT,INSERT,UPDATE,DELETE''
,update_check => TRUE
,enable => TRUE ); ';
dbms_output.put_line( substr(lsql,1,255) );
EXECUTE IMMEDIATE lsql using l_tab(i).object_name;

end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Output is :
Selecting ORG_FG tables ...
9 rows selected
FG_ADJUSTMENTS
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'ORG_FG'
,object_name => :l_tab(i).object_name
,policy_name => 'FG_ORG_POLICY'

ORA-00900: invalid SQL statement


Appreciate your help.

Tom,

I got a little further and resolved the 'Invalid sql statement' error by enclosing lsql in a begin-end. But now I get execute immediate error :
PLS-00224: object 'L_TAB' must be of type function or array to be used this way

lsql := ' BEGIN SYS.DBMS_RLS.ADD_POLICY (
object_schema => ''ORG_FG''
,object_name => :l_tab(i).object_name
,policy_name => ''FG_ORG_POLICY''
,function_schema => ''APPS''
,policy_function => ''ORG_SECURITY_PKG.ORG_PREDICATE''
,statement_types => ''SELECT,INSERT,UPDATE,DELETE''
,update_check => TRUE
,enable => TRUE ); END;';

EXECUTE IMMEDIATE lsql using l_tab(i).object_name;

Appreciate your help. Thanks!

Excellent - so simple, it never occurred to me ! Thank you so much!

Hi Tom,

We have implemented VPD in our development database 10g successfully and it is working fine. Recently one of the developer found a problem regarding performance.

A sql query which is taking 78 msec on sqlplus when vpd is not enabled and it is taking 17 seconds when VPD is enabled.

Two tables of this query have policies on them. I have attached the policy predicates into the query without enabling VPD and still it just takes 100 msecs, when I looked at the explain plan of VPD enabled session I found a full table scan on one of the table which has policy on it. Explain plan of the same query with out VPD is doing unique index scan. Here is my sql.

SELECT dc.doc_id
, dc.name fileName
, tt.name fileType
, dc.doc_desc comments
, lp.name phaseName
, pt.Name processName
, lph.short_name parentName
, dc.pi_id piId
, lp.short_name phaseShortName
, gui_util.get_file_ext(dc.name) file_ext
, (dbms_lob.getlength(dc.doc)/1024) fileSize
,dc.update_dt
,dc.VERSION_NBR version
FROM
docs dc
,process_instances pi
,process_templates pt
,lease_phases lp
,lease_Phases lph
,task_templates tt
where
lp.parent_lph_id = lph.lph_id (+)
and lp.lph_id(+) = pi.lph_id
and pt.pt_id(+) = pi.pt_id
and tt.task_id(+) = dc.task_id
and pi.pi_id(+)= dc.pi_id
and (dc.project_nbr=:1 or dc.lease_nbr=:2)
and UPPER (nvl(dc.name,'%')) LIKE UPPER ('%'||:3||'%')
and UPPER (nvl(tt.name,'%')) LIKE UPPER ('%'||:4||'%')
and UPPER (nvl(dc.doc_desc,'%')) LIKE UPPER ('%'||:5||'%')
and lp.name like ('%'||:6||'%')
and pt.Name like ('%'||:7||'%')
and dc.deleted_ind = 'N'
ORDER BY
trunc(dc.update_dt) desc, dc.name

Docs and Process_instances tables have Policies on them.

I have traced the session while VPD is enabled and I looked at the execution plan having full table scan on process_instances table which has around 2 million records.

I'm really confused with this behaviour I request your help to give me some input on debugging this issue.

Thanking you,
Regards
Sarma

Hi Tom,

I did similar to what you have mentioned I did not put predicate in the from clause as you have mentioned but in the where clause as following.

SELECT dc.doc_id, dc.NAME filename, tt.NAME filetype, dc.doc_desc comments,
lp.NAME phasename, pt.NAME processname, lph.short_name parentname,
dc.pi_id piid, lp.short_name phaseshortname,
gui_util.get_file_ext (dc.NAME) file_ext,
(DBMS_LOB.getlength (dc.doc) / 1024) filesize, dc.update_dt,
dc.version_nbr VERSION
FROM docs dc,
process_instances pi,
process_templates pt,
lease_phases lp,
lease_phases lph,
task_templates tt
WHERE lp.parent_lph_id = lph.lph_id(+)
AND lp.lph_id(+) = pi.lph_id
AND pt.pt_id(+) = pi.pt_id
AND tt.task_id(+) = dc.task_id
AND pi.pi_id(+) = dc.pi_id
AND exists (
SELECT 1
FROM project_lease_access pla
WHERE pla.project_nbr = pi.project_nbr
AND ( ( NVL(pla.secondary_uid, pla.primary_uid) = '123.456@7899.gov''
OR 'RS' IN ('RSS','RBA')
AND pla.rgn_code = 5
)
OR
(
NVL(pla.agency_secure_ind, 'N') = 'N'
AND (
NVL(pla.project_open_ind, 'N') = 'N'
AND pi.lph_id > 3
)
OR
(
NVL(pla.project_open_ind, 'N') = 'Y'
AND pi.lph_id > 0
)
)
)
)
AND (dc.project_nbr = :1 OR dc.lease_nbr = :2)
AND ((exists
(SELECT 1
FROM process_instances pi
WHERE pi.pi_id = dc.pi_id
)) OR EXISTS (
SELECT 1
FROM comm_log cl
WHERE cl.cl_id = dc.cl_id
)
OR dc.sfo_id > 0
OR dc.ref_doc_type = 'NBCSCHED')
AND
((exists (
SELECT 1
FROM project_lease_access pla
WHERE pla.project_nbr = dc.project_nbr
AND (pla.project_open_ind = 'Y'
OR (pla.primary_uid = '123.456@7899.gov'--SYS_CONTEXT(:C1, :C3)
OR pla.secondary_uid = 'glenn.deacon@gsa.gov'--SYS_CONTEXT(:C1, :C3)
OR ('RS' IN ('RSS','RBA') AND pla.rgn_code = 5)
)
)
))
OR
dc.doc_public_ind = 'Y'
)
AND UPPER (NVL (dc.NAME, '%')) LIKE UPPER ('%' || :3 || '%')
AND UPPER (NVL (tt.NAME, '%')) LIKE UPPER ('%' || :4 || '%')
AND UPPER (NVL (dc.doc_desc, '%')) LIKE UPPER ('%' || :5 || '%')
AND lp.NAME LIKE ('%' || :6 || '%')
AND pt.NAME LIKE ('%' || :7 || '%')
AND dc.deleted_ind = 'N'
ORDER BY TRUNC (dc.update_dt) DESC, dc.NAME

and here is my explain plan when VPD is not enabled. No full table scan on Process_instances table what so ever.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 30
SORT ORDER BY 1 492 30
FILTER
NESTED LOOPS 1 492 27
NESTED LOOPS OUTER 1 455 26
NESTED LOOPS 1 444 25
NESTED LOOPS OUTER 1 413 24
FILTER
NESTED LOOPS OUTER 1 391 22
TABLE ACCESS FULL ELEASEDEV.DOCS 1 358 21
TABLE ACCESS BY INDEX ROWID ELEASEDEV.TASK_TEMPLATES 1 33 1
INDEX UNIQUE SCAN ELEASEDEV.TASK_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROCESS_INSTANCES 1 22 2
INDEX UNIQUE SCAN ELEASEDEV.PI_PK 1 1
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 31 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 11 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROCESS_TEMPLATES 1 37 1
INDEX UNIQUE SCAN ELEASEDEV.PT_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 140 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1
INDEX UNIQUE SCAN ELEASEDEV.PI_PK 1 6 2
INDEX UNIQUE SCAN ELEASEDEV.CL_PK 1 4 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 138 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1

Here is the explain plan of the same when VPD is enabled with full table scan on process_instances.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 4026605
SORT ORDER BY 1 483 4026605
FILTER
FILTER
NESTED LOOPS OUTER 1 483 4026604
NESTED LOOPS OUTER 1 450 4026603
NESTED LOOPS 1 439 4026602
NESTED LOOPS 1 402 4026601
HASH JOIN OUTER 1 371 4026600
TABLE ACCESS FULL ELEASEDEV.DOCS 1 358 21
VIEW ELEASEDEV.PROCESS_INSTANCES 4 52 4026578
FILTER
TABLE ACCESS FULL ELEASEDEV.PROCESS_INSTANCES 2 M 50 M 5355
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 140 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 31 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROCESS_TEMPLATES 1 37 1
INDEX UNIQUE SCAN ELEASEDEV.PT_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 11 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.TASK_TEMPLATES 1 33 1
INDEX UNIQUE SCAN ELEASEDEV.TASK_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 138 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1
NESTED LOOPS 1 158 5
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROCESS_INSTANCES 1 18 3
INDEX UNIQUE SCAN ELEASEDEV.PI_PK 1 2
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 140 2
INDEX RANGE SCAN ELEASEDEV.PLR_UK 1 1
NESTED LOOPS 1 152 3
TABLE ACCESS BY INDEX ROWID ELEASEDEV.COMM_LOG 1 12 1
INDEX UNIQUE SCAN ELEASEDEV.CL_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 140 2
INDEX RANGE SCAN ELEASEDEV.PLR_UK 1 1

I ran trace and explain plan is the same I don't know why it is doing full table scan when I enable VPD any way
let me try appending the predicate in the from clause as you have mentioned.

We have implemented VPD to such an extent we cannot revert it and we are in testing phase and going into production with in a month's time so really don't know what to do with this critical performance problem.

Thanks for your input.

Regards
Sarma

Hi Tom,

I have put the VPD predicate in the from clause as you have mentioned and I am able to reproduce the problem and also I see full table scan on Process_instances table.

I really appreciate your input in this regard.

I am still trying to figure out why is it doing a full table scan instead of unique index scan.

Here is my entire query:

SELECT dc.doc_id, dc.NAME filename, tt.NAME filetype, dc.doc_desc comments,
lp.NAME phasename, pt.NAME processname, lph.short_name parentname,
dc.pi_id piid, lp.short_name phaseshortname,
gui_util.get_file_ext (dc.NAME) file_ext,
(DBMS_LOB.getlength (dc.doc) / 1024) filesize, dc.update_dt,
dc.version_nbr VERSION
FROM (select * from docs
where ((exists
(SELECT 1
FROM process_instances pi
WHERE pi.pi_id = docs.pi_id
)) OR EXISTS (
SELECT 1
FROM comm_log cl
WHERE cl.cl_id = docs.cl_id
)
OR docs.sfo_id > 0
OR docs.ref_doc_type = 'NBCSCHED')
AND
((exists (
SELECT 1
FROM project_lease_access pla
WHERE pla.project_nbr = docs.project_nbr
AND (pla.project_open_ind = 'Y'
OR (pla.primary_uid = 123.456@789.gov'
OR pla.secondary_uid = 'glenn.deacon@gsa.gov'
OR ('RS' IN ('RSS','RBA') AND pla.rgn_code = 5)
)
)
))
OR
docs.doc_public_ind = 'Y'
)) dc,
(select * from process_instances
where exists (
SELECT 1
FROM project_lease_access pla
WHERE pla.project_nbr = process_instances.project_nbr
AND ( ( NVL(pla.secondary_uid, pla.primary_uid) = 123.456@gov'
OR 'RS' IN ('RSS','RBA')
AND pla.rgn_code = 5
)
OR
(
NVL(pla.agency_secure_ind, 'N') = 'N'
AND (
NVL(pla.project_open_ind, 'N') = 'N'
AND process_instances.lph_id > 3
)
OR
(
NVL(pla.project_open_ind, 'N') = 'Y'
AND process_instances.lph_id > 0
)
)
)
)) pi,
--docs dc,
--process_instances pi,
process_templates pt,
lease_phases lp,
lease_phases lph,
task_templates tt
WHERE lp.parent_lph_id = lph.lph_id(+)
AND lp.lph_id(+) = pi.lph_id
AND pt.pt_id(+) = pi.pt_id
AND tt.task_id(+) = dc.task_id
AND pi.pi_id(+) = dc.pi_id
AND (dc.project_nbr = :1 OR dc.lease_nbr = :2)
AND UPPER (NVL (dc.NAME, '%')) LIKE UPPER ('%' || :3 || '%')
AND UPPER (NVL (tt.NAME, '%')) LIKE UPPER ('%' || :4 || '%')
AND UPPER (NVL (dc.doc_desc, '%')) LIKE UPPER ('%' || :5 || '%')
AND lp.NAME LIKE ('%' || :6 || '%')
AND pt.NAME LIKE ('%' || :7 || '%')
AND dc.deleted_ind = 'N'
ORDER BY TRUNC (dc.update_dt) DESC, dc.NAME

Here is the explain plan any hints would be really greatful.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 4026436
SORT ORDER BY 1 483 4026436
FILTER
FILTER
NESTED LOOPS OUTER 1 483 4026435
NESTED LOOPS OUTER 1 450 4026434
NESTED LOOPS 1 439 4026433
NESTED LOOPS 1 402 4026432
HASH JOIN OUTER 1 371 4026431
TABLE ACCESS FULL ELEASEDEV.DOCS 1 358 21
VIEW 4 52 4026410
FILTER
TABLE ACCESS FULL ELEASEDEV.PROCESS_INSTANCES 2 M 50 M 5355
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 140 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 31 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROCESS_TEMPLATES 1 37 1
INDEX UNIQUE SCAN ELEASEDEV.PT_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.LEASE_PHASES 1 11 1
INDEX UNIQUE SCAN ELEASEDEV.LPH_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.TASK_TEMPLATES 1 33 1
INDEX UNIQUE SCAN ELEASEDEV.TASK_PK 1 0
TABLE ACCESS BY INDEX ROWID ELEASEDEV.PROJECT_LEASE_ACCESS 1 138 2
INDEX RANGE SCAN ELEASEDEV.PLR_PROJ_FK_I 1 1
INDEX UNIQUE SCAN ELEASEDEV.PI_PK 1 6 2
INDEX UNIQUE SCAN ELEASEDEV.CL_PK 1 4 0
Thank you once again for your input.

Regards
Sarma

Hi Tom,

Thanks for your input. I have changed my query to limit on Process_instances explicitly and it worked but I do not understand why is it doing a full table scan even after we joined dc.project_nbr = pi.project_nbr and also limiting by having c.project_nbr = :1.

I presume that VPD adds predicate into the from clause where process_instances table is not getting filtered so we have to add extra condition to limit Process_instances does this make sense?

Any way thanks for your great help.

Sarma

Here is the changed query.


SELECT dc.doc_id, dc.NAME filename, tt.NAME filetype, dc.doc_desc comments,
lp.NAME phasename, pt.NAME processname, lph.short_name parentname,
dc.pi_id piid, lp.short_name phaseshortname,
gui_util.get_file_ext (dc.NAME) file_ext,
(DBMS_LOB.getlength (dc.doc) / 1024) filesize, dc.update_dt,
dc.version_nbr VERSION
FROM docs dc,
process_instances pi,
process_templates pt,
lease_phases lp,
lease_phases lph,
task_templates tt
WHERE lp.parent_lph_id = lph.lph_id(+)
AND lp.lph_id(+) = pi.lph_id
AND pt.pt_id(+) = pi.pt_id
AND tt.task_id(+) = dc.task_id
AND pi.pi_id(+) = dc.pi_id
AND (dc.project_nbr = :1 OR dc.lease_nbr = :2)
AND UPPER (NVL (dc.NAME, '%')) LIKE UPPER ('%' || :3 || '%')
AND UPPER (NVL (tt.NAME, '%')) LIKE UPPER ('%' || :4 || '%')
AND UPPER (NVL (dc.doc_desc, '%')) LIKE UPPER ('%' || :5 || '%')
AND lp.NAME LIKE ('%' || :6 || '%')
AND pt.NAME LIKE ('%' || :7 || '%')
AND dc.deleted_ind = 'N'
AND pi.project_nbr = :0 -- added this condition
ORDER BY TRUNC (dc.update_dt) DESC, dc.NAME

You can use either of the methods to see exactly how the query was rewritten by VPD.

(1) Check the view V$VPD_POLICY, which will tell you exactly what predicate was applied to the query. Here is an example from my database:

ADDRESS PARADDR SQL_HASH CHILD_NUMBER
---------------- ---------------- ---------- ------------
OBJECT_OWNER OBJECT_NAME
------------------------------ ------------------------------
POLICY_GROUP POLICY
------------------------------ ------------------------------
POLICY_FUNCTION_OWNER
------------------------------
PREDICATE
----------------------------------------------------------------------------

C000000635C0A2D8 C00000061D3F3548 4152967964 0
CRMMART D_GST_PROF
SYS_DEFAULT SVO_GST_PROF_POLICY
CRMMART
UPDATE_DATA_SRC_ID NOT IN (10,11,12)

Here I see that the predicate applied was "UPDATE_DATA_SRC_ID NOT IN (10,11,12)", which helps me decide if I need to crate an index.

2. A better approach is to set an event in the session and then executing the query.

ALTER SESSION SET EVENTS '10730 trace name context forever, level 12';

... issue your normal query now ...

Exit and check in the user_dump_dest directory. You will see a trace file where the exact SQL statement (after VPD operated on it) in plain text.

Hope this helps.

Hi Arup Nanda,

Thanks for the information.

Regards
Sarma

Tom,

I am trying to understand how RLS and MV work together. An MV is a table really. When rewrite occurs, Oracle chooses to use the MV instead of the base tables. Now normally we apply RLS to the base tables and not to the MV (reference to Note 301442.1 which states that no RLS should be applied to MV)

So when the query rewrite occurs, how does Oracle enforce RLS? The policy functions will just not be in picture since we are not going to the base tables at all.

Thanks

Tom,

Thank you for the explanation. It certainly makes a lot of sense that the RLS rewrites the query first with the appropriate where clause added and then a suitable MV is "searched" for, if one is found then it is used..

Now since MV are summaries, most of them use sum and avg and count etc (we will leave aside join only MVs).

How could any user with RLS ever be able to use such MVs? The MV was pre-calculated, there is no way now to say that I need a of sum of only those rows from the MV which match my RLS user's criteria.

As an example, if I have an MV with a count(*) from the table, an RLS user will never be able to use it.

I have been working on a bug with Oracle Tech support in which RLS is actually lost when MV query rewrite occurs - Oracle returns all rows from the MV to the RLS user (9.2.0.7 on Solaris). While working on this SR, I am starting to get the feeling (with my limited knowledge on MV and RLS) that in a database where RLS is heavily used, the MVs will be hardly of any use.

Your expertise will certainly help me a lot here. In our environment, RLS is working very satisfactorily for a couple years, but now we have a need to deploy MV's if possible.

Hi Tom,

Thanks again for all the information. We are facing a similar issue. We are getting a 68112 while executing a procedure through DBMS_JOB. The aforementioned procedure then calls another procedure which works on a table that has FGAC implemented and also on other system tables.

DBMS_JOB --> Proc1 --> pkg.Proc2 --> tab1,tab2,sys.tab3, system.tab4...etc

The procedure executes successfully through sqlplus but not in pl/sql which means that it should be a roles and privelages issue. But even after putting 'set role all' in the first procedure (Proc1), it does not execute.
And then, the 68112 does not generate a trace file???

Thank you Tom.

Sorry...I meant 28112. So then we should be individually granting access to each object used.

But why would the trace file be not getting generated? I don't think I have provided you enough information to answer that, but any pointers?

Thanks again for the answer on other issue of 'set role all'.

I am trying to improve the performance of a data warehouse application. I'd like to use materialized views to precompute a lot of different rollup totals. The gotcha seems to be that we're using FGAC on most of the tables. Building an mview on a table with an FGAC table yields ORA-30372, "fine grain access policy conflicts with materialized view". I could augment the policy procedure to return a NULL policy when a refresh is done, but I'd still want the non-null policy for other customers using the database. (In other words, I only refresh a single customer's data at a time. I don't mind disabling the policy while refreshing their data, but I don't want it off for all customers.)

In your answer of August 25, 2004, you indicate that FGAC and mviews are compatible. Can you elaborate on a strategy to deal with my current challege in light of your answer?

This is what I'd like to do:

CREATE MATERIALIZED VIEW mv_totals
AS
SELECT customer_id,
spend_year,
spend_month
sum(amount) AS amount
FROM bux
GROUP BY customer_id,
spend_year,
spend_month;

The policy is:

CREATE OR REPLACE PACKAGE BODY policy IS
FUNCTION customer_policy (p_object IN VARCHAR2) RETURN VARCHAR2
IS
l_customer_id VARCHAR2(50);
BEGIN

-- attach the correct schema to get the context name
l_customer_id := SYS_CONTEXT('ctx', 'customer_id');

IF (l_customer_id IS NULL) THEN
RETURN '1=0';
ELSE
RETURN 'customer_id = ' || l_customer_id;
END IF;

END customer_policy;
END policy;
/

It seems to me that customer_id is not getting aggregated away, but perhaps I am misunderstanding you.

Is there an undocumented parameter one can set to make it return a string, say "****" instead of NULL, for secured-columns ?

Thank you

Message to OJ. We faced the same problem when a packaged procedure was being called from DBMS_JOB. Try setting context from the procedure that is being called by DBMS_JOB. Trace file wasn't generated in our case either.

In your response of August 3, 2006, you say:
ouch - that really isn't what your policy is is it???? ouch ouch ouch ouch ouch - that hurts.

why not just:
return 'customer_id = sys_context( ''ctx'', ''customer_id'' )';

unless you have an absolutely trivial number of customer ids!


I am thankful to Jonathan Lewis (in his CBO book) who points out the value of literals in the case of data warehouse applications. In my own case, I implemented your suggestion and noticed, when examining some performance problems, that SYS_CONTEXT was skewing all the cardinality numbers in the plans. I made numerous attempts to get better cardinality estimates, to no avail. Going back to "return 'customer_id = ' || TO_CHAR(l_customer_id);", and cardinalities are correct once again. At least in 9i.

We use materialized views in conjunction with FGAC. The refreshes are part of a long-running batch process that is submitted to an Oracle queue (using Advanced Queuing), then picked up and run.

The policy on the base tables for the mview is simple:

CREATE OR REPLACE PACKAGE BODY custpolicy IS
   FUNCTION customer_policy (p_schema IN VARCHAR2, p_object IN VARCHAR2) RETURN VARCHAR2
   IS
 PRAGMA AUTONOMOUS_TRANSACTION;
   l_customer_id    PLS_INTEGER;
   l_mview_refresh  VARCHAR2(100) DEFAULT '';
 l_policy VARCHAR2(50);
 l_sidser varchar2(30);
   BEGIN
   
    l_mview_refresh := SYS_CONTEXT(p_schema || '_mview_ctx', 'MVIEW_REFRESH');

    
    IF (l_mview_refresh = 'TRUE') THEN
        l_policy := NULL; 
  ELSE
  l_customer_id := SYS_CONTEXT(p_schema||'_ctx', 'customer_id');
  l_policy :=  'customer_id = ' || NVL(TO_CHAR(l_customer_id), '-1' );
    END IF;

  select TO_CHAR(sid) || ',' || TO_CHAR(serial#) into l_sidser
  FROM V$session
  WHERE sid = (select sid from v$mystat where rownum=1);

  /* create table poltab (pid number not null primary key, pdate date, ptext varchar2(2000));
     create sequence poltab_seq; 
  */
  insert into poltab(pid,pdate,ptext) 
  values (poltab_seq.nextval, sysdate, 'p_schema: ' || p_schema ||
    ' p_object: ' || p_object ||
   ' current_user: ' || sys_context('userenv','current_user') ||
   ' current_schema: ' || sys_context('userenv','current_schema') ||
   ' USER: ' || USER ||
   ' session_user: ' || sys_context('userenv','session_user') ||
   ' sid,ser: ' || l_sidser ||
   ' mview_refresh: ' || l_mview_refresh ||
   ' policy: ''' || l_policy || '''');
 COMMIT;

 RETURN l_policy;
    
   END customer_policy;

END custpolicy;;
/

(Note that the autonomous transaction and poltab insert was added as a debugging aid.)

Prior to beginning the refresh, the batch process sets the context so that MVIEW_REFRESH = TRUE. This is verified in the logging output from the batch process. However, the DBMS_MVIEW.REFRESH ends up running in a different session (as verified by comparing the sid/serial# from the policy with the sid/serial# from the batch process). Thus, the MVIEW_REFRESH context variable is unset, the policy returns "customer_id = -1", and we receive ORA-30372 (access policy conflicts with mview refresh).

What a difficult to diagnose condition, especially since the problem does not appear when the batch process is invoked from SQL*Plus.

How do I get around this? I can put them in their own schema, but running as a background queue process, can I be sure that the policy will have expected values for USER, etc?

I hit upon an answer to the previous question while combing the documentation, once again, for a clue. Before we were using atomic_refresh=false, since we don't use dbms_mview.refresh with a list of mviews anyway. However, atomic_refresh=false makes use of the job system, hence a separate session. Setting atomic_refresh to true keeps the refresh in the session that sets the mview_refresh context flag. This isn't 100% obvious from the documentation, but filed away for future reference.

Hi Tom,

I am in a process of weighing pros and cons of the application schema setup.

Requirements : We need to setup schema for our new customers in a very short span of time. Our application uses a lot of packages and between the customers we'll have their data different but the packages would be same, so I was thinking of the following solutions and wanted to get your expert feedback before deciding on anything.

Proposed Solution A:
1. Create a separate schema for each customer without packages
2. Create a common schema with packages and create views in that schema to point to the tables of the customers,
but this logic would involve recreating the views with the new customers.

Proposed Solution B:
1. Create one schema with all partioned tables with a partition for each customer, but this would involve changing of our packages to incorporate the partition key in all SQL statements

Q1 : In this case what should be our policy on indexes ?
(I think we should keep the local indexes same as they prevail in the application as of now together with partition key)

Proposed solution C:
1. Use VPD, is that an option in this case and I have a very macro-level idea of VPD

Do you suggest any other solutions other than mentioned above and What could be the best option among all given the following facts

1. Source is same throughout for all customers and I do not think we should be replicating application the source for each customer.
2. The setup has to happen in 10 minutes and I think we can create our objects in 10 minutes.
3. Tomorrow, if the customer is not happy with the application and wants to quit, so we should be able to either delete its data and tables or we can re-use it for other customers.

Thanks

Thanks for your prompt reply.

Q1 : If I take the VPD implementation as suggested by you, then I could as well keep the partitioned data and packages in one schema and I do not need grant on the tables/views for the packages, Right ?

Q2 : Do we need separate license for using VPD, even if we have license for partitioning ?

Q3 : Do we need to make any changes in the packages for VPD other than Creating Views and Triggers ?

Thanks

Thanks a lot, with that info I have now confidence to move forward.

Hi Tom, I have the following question. How could I know what is the actual policy used for some session?

For example, If I execute:

SQL> select policy_name, pf_owner
  2  from all_policies ;

POLICY_NAME                PF_OWNER
-------------------------- ------------------------------
TEST_POL_1                 USER_1
TEST_POL_2                 USER_2

2 rows selected.

But I need to know what policy is actually used on some session. In my actual session, how could I know If I'm using the policy TEST_POL_1 or TEST_POL_2 ??

Thanks a lot!

Thanks Tom, but how could I now if some policy is setted for my current session without known the name of the objects?

Thanks!

Hi,
I find the responses made above related to VPD very informative.
In relation to the same, i had one question always in mind stemming from the difference in databse connection and session.
If i set some values for some name in the context through one session and do a commit will those be visible to other sessions as well.
i.e. through one session if i set value 'ABC'
and then do a commit; then will the value 'ABC' reflected to another session and have some adverse effects related to security ?.
I understand this should not happen as these values are not shared across the sessions (even though we use the same namespace).

Hi Tom,

We have Oracle 10gR2.

If I have number of users (USER1, USER2...) assigned to a DB role, and all users are having same policy for a table.
Can this be defined with context as below?

SYS_CONTEXT('USERENV','SESSION_USER')='ROLE_NAME' Or
We need to define (with each user individually) as

IF SYS_CONTEXT('USERENV','SESSION_USER')='USER1' THEN
...
ELSIF SYS_CONTEXT('USERENV','SESSION_USER')='USER2' THEN
......
...
END IF;

Regards,

Hi, Tom. I'm trying to get the user by reading a cookie in the VPD funcion directly (cause we are in a three tier model, and we've several applications, java, discoverer, forms, reports...), all connected with the same database user, but when I put owa_cookie.get_all in the VPD function, it compile, but seems that it not works, throw an exception.
Is possible to do that, to read a cookie en the VPD function?
What can I do in a VPD function, has any constraints?

thank you..

Hi Tom,
We have created a VPD security on a table. We have a discoverer report from custom folder that uses this table. We pass a date parameter from discoverer desktop/plus/viewer to the customer folder query using 2 functions set_parameter and get_parameter. Problem is this report does not return any data after creating the security policy. But if we remove the policy we see data. Can you give me an example of how I can create a logon trigger for this scenario and if it will work at all?

Here is the policy created:

BEGIN
DBMS_RLS.ADD_POLICY
(OBJECT_SCHEMA => 'AR',
OBJECT_NAME => 'HZ_PARTIES',
POLICY_NAME => 'XXFA_POLICY',
FUNCTION_SCHEMA => 'APPS',
POLICY_FUNCTION => 'XXFA_ACCESS_FUNC',
STATEMENT_TYPES => 'SELECT',
update_check => true,
enable => true,
static_policy =>FALSE,
policy_type => dbms_rls.dynamic
END;

and function XXFA_ACCESS_FUNC returns a value that will be places as a where condition to the table. Value will be either "attribute_category IS NOT NULL" or "attribute_category IS NULL"

I have a vendor's web application ( java ) . No control over application code .. The middleware gets connected to db as via one db user.
Application code does not set client identifier ,...

Is it possible to audit the real ( app) user's actions ? Is it possible to have a VPD policy attached to this user?

Hello Tom,

in one of the answers above (Followup May 18, 2005 - 1pm Central time zone) you suggested putting a call to the 'set_context'-procedure for the context into the elaboration code of the context-management-package.
This is the first time I see that sort of thing mentioned and I'm quite curious about it.

I've got an application with a _lot_ of tables with only few restricted by VPD. I see in AWR that a substantial amount of cpu is used for the set_context-Procedure. It would be neat to have only that sessions call the 'set' which really need it. As long as a session works with the whole lot of unrestricted tables it should not be bothered with the VPD stuff.

So the question is:
Is it really safe to put the 'set' in the elaboration part of the package? Will it get called exactly when its needed, not more often, not less?
Why is no one propagating this method: Are there any pit falls, something I do not see? Wherever I look they talk about logon-Triggers etc. pp. This is so much neater. Shouldn't this be the default approach whenever one does not need input parameters for the 'set'-procedure? (when input parameters are needed this is obviously not an option, but then logon-triggers aren't anyway).

Your advice is appreciated,
Kathrin

Tom,
In taking your suggestion about using a view with SYS_CONTEXT to bind the predicate, I created a view based on a context. I pass a comma-separated list of values to the context and have the view separate the comma separated list of values out.

In my policy predicate function, I reference the view as "RETURN 'id IN (SELECT * FROM id_list)' ". The problem I encounter is the optimizer only estimates 1 row from this inline view and therefore, performs a MERGE JOIN CARTESIAN (MJC) which seems to have a dramatic impact on performance. The inline view can return anywhere from 1 to 50 values.

I tried using the CARDINALITY & DYNAMIC_SAMPLING hint in the 'SELECT * FROM id_list' query, but this did not prevent the MJC.

Is there anything I can do to prevent this from performing a MERGE JOIN CARTESIAN?

Thanks.

My goal is to avoid concatenating strings at the RETURN clause of the security function.
Here is the scenario:
- DATA_TABLE_1 contains column COL_01
- DATA_TABLE_2 contains column COL_02
- SECURITY_TABLE_1 contains column OPRID, COL_01
- SECURITY_TABLE_2 contains column OPRID, COL_02
The RETURN clauses of the security function should be:
The question is, to use binding should I:
1. In a single security function for both data tables do:
CASE
WHEN object_name = 'DATA_TABLE_1' THEN
RETURN 'col_01 IN (SELECT col_01 FROM SECURITY_TABLE_1 WHERE oprid = SYS_CONTEXT(''my_ctx'',''real_name''))'
WHEN object_name = 'DATA_TABLE_2' THEN
'col_02 IN (SELECT col_02 FROM SECURITY_TABLE_1 WHERE oprid = SYS_CONTEXT(''my_ctx'',''real_name''))'
2. Create two different functions for the two data tables
3. Other
Thank you in advance.

1. There is indeed in the security function some code pertaining to the user (like evaluating is this a superuser, what is the client identifier etc.). Should all of this code be moved to an LOGON trigger and put in a CONTEXT (any other options?), instead of being evaluated over and over in the security function?
2. Aside from the writing more code with a single function, does the CASE statement with two RETURNs (I missed the second RETURN above) in it eliminate the reparsing? I am really lost the reparsing of the RETURN clause of a function.
Thanks a lot, since I had a lot of trouble finding something on the WEB about predicate with a subquery.

Pedro

if (not_initialized)
then
   call_init_routine();
   not_initialized := false;
end if;
... rest of code...

I gave the binding a thought and this is what I came up with.
1. For the single security function scenario, I think I could write the RETURN clause as:
RETURN v$column_name_01 || ' IN (SELECT ' || v$column_name_01 || ' FROM ' || v$table_name_01 || ' WHERE oprid = SYS_CONTEXT(''my_ctx'',''real_name''))'
without affecting the performance since for a particular table the WHERE predicate will not change.
2. I have to deal with 2 types of users, application users from the WEB (recognized via SYS_CONTEXT('my_ctx','real_name') ) and database users (recognized via SYS_CONTEXT('USERENV','SESSION_USER'))
The question is where should this recognition step be placed:
2.1 In the WHERE predicate RETURN ..... || ' WHERE oprid = NVL(SYS_CONTEXT(''my_ctx'',''real_name''), SYS_CONTEXT(''USERENV'',''SESSION_USER'')) )'
2.2 In the LOGON trigger in the form of DBMS_SESSION.SET_CONTEXT( 'my_ctx', 'real_name', database_user_name)
2.3 In the BEGIN part of the security package in the form of DBMS_SESSION.SET_CONTEXT( 'my_ctx', 'real_name', database_user_name) (since that would be executed only once, to me this is an equivalent solution to 2.2)
2.4 Any of the above
2.5 None of the above
Thanks for all your help

I really appreciate all of your time and help. I decided to go with the global variable approach taking at the same care of the "what if the web application failed to set the 'real name' for any reason" (excellent point here).
Thank you very much, for Oracle - there is no place like asktom

Pedro

Hi Tom,
Thanks for the very helpful answers you have provided to this question so far!
We have recently implementing VPD in our application. We have a schema shared by multiple applications, and different rules that apply to different users.. So we defined the policies in different policy groups, and a driving context to enforce the appropriate policy group depending on the user type.
Everything was fine until we got error ORA-28123: Driving context 'APP_CTX,PG' contains invalid group 'PG_purchasing'.
I finally understood that this exception is raised if the driving context value does not match an existing policy group for the object. But I don't understand why.. it doesn't seem logical to me. here's why:

I have 2 applications: inventory_app and purchasing_app. Both access table suppliers. inventory_app should have access to all suppliers while purchasing_app is restricted based on department. So i create a policy for purchasing_app and add it to policy group PG_purchasing. I don't add any policy for the inventory_app on this table.
Now when a user logs in to the inventory_app, the policy group PG_inventory is in effect, and it has all the policies needed for this app, but no policy defined for the suppliers table. So when this user attempts to access the suppliers table, we get ORA-028123, although PG_inventory is a valid group in the system.. it just doesn't have a policy defined for this table in particular.
To solve this issue, I would have to add PG_inventory to the suppliers table, but no policy needs to be defined for it.
I find this solution inefficient, especially for users with more powerful access rights to data, like application admin users, for whom we would have to add the policy group on almost all tables. In other words, if a table has one policy that needs to be applied for one group, we now have to add this table to all other policy groups to make it accessible.
I also find this non-scalable, because whenever a new policy group is added, we have to go back to all the tables and add them to the group. On the other hand, a table with zero policies defined, will be accessible by the same user who can't access suppliers, which i find inconsistent.

Sorry for the long "review". I just don't want to be implementing policy groups in the wrong fashion.

Thank you for your help!
Rima

Hi Tom,

In one of our application (ORA1) we are trying to create a MV on table present in other DB ORA2. ORA2 has RLS policies implemented therefore Refresh is giving error ORA-30372 (FGAC conflict with MV). This MV is going to be fast refresh so in future will be using MView logs but during creation it is selecting from main table having RLS policies. I can't change the Policies code, its vendor code therefore I tried disabling it but It didn't worked, gave ORA-30372.
Used dbms_rls.enable_policy(with enable=>FALSE) to disble the policy and DBA_POLICY is reflecting that policy is disabled but still I am not able to complete refresh/create Mview in ORA1 DB.
Does this mean we can't disable the policy or its not having any effect when Mview is complete refreshed from other DB (ORA1). So how can we temporarily disable the policies (I tried creating MView even with prebuilt option but even thats not working)
ORA1-11g
ORA2-10g
Sorry for so much text....

There is a enhancement which mandates use of "USING TRUSTED CONSTRAINTS" in 11.2g which is backported in 11.1.0.7 through patch 8338443. Used this clause and it worked fine.

The requirement arised because one app is for normal users thus mandates vpd and other app is for managers thus no VPD.

I am using procedure to generate a list for a given employee . If the given argument is leaf level employee , I just list the employee , if not I list the employee and their reports. 

How can I achieve this functionality using VPD or OLS ?


SQL> show user
USER is "SCOTT"

SQL> CREATE or REPLACE
  2  PROCEDURE P_GEN_EMP_LIST(
  3      P_MGR IN NUMBER ,
  4      P_REFCURSOR OUT SYS_REFCURSOR )
  5  AS
  6  BEGIN
  7    OPEN P_REFCURSOR FOR SELECT ename , empno, mgr --, LEVEL lvl
  8    FROM emp START WITH empno = P_MGR CONNECT BY PRIOR empno = mgr ORDER SIBLINGS BY mgr;
  9  END P_GEN_EMP_LIST;
 10  /

Procedure created.

SQL>
SQL> variable x refcursor;
SQL> variable n number ;
SQL> exec :n := 7566;

PL/SQL procedure successfully completed.

SQL> exec p_gen_emp_list ( :n , :x) ;

PL/SQL procedure successfully completed.

SQL> print x ;

ENAME           EMPNO        MGR
---------- ---------- ----------
JONES            7566       7839
SCOTT            7788       7566
ADAMS            7876       7788
FORD             7902       7566
SMITH            7369       7902

SQL> exec :n := 7788

PL/SQL procedure successfully completed.

SQL> exec p_gen_emp_list ( :n , :x) ;

PL/SQL procedure successfully completed.

SQL> print x ;

ENAME           EMPNO        MGR
---------- ---------- ----------
SCOTT            7788       7566
ADAMS            7876       7788

SQL>


SQL> exec :n := 7876;

PL/SQL procedure successfully completed.

SQL> exec p_gen_emp_list ( :n , :x) ;

PL/SQL procedure successfully completed.

SQL> print x ;

ENAME           EMPNO        MGR
---------- ---------- ----------
ADAMS            7876       7788

SQL>

We are developing an application that connects to server. There will be so many users connecting to that application server and that app server manages oracle connection for each user that requested some data. So that one session can be used by any user. Hence we used set context as package and called it just before every single request that came from users.
After a while we are getting ORA error on app server start up. It says maximum number of session has been exceeded or something like that. Could it be possible that setting context with every single query is making this. Thanks in advance.

Hi Tom,

We are building a SaaS (Software as a Service) application which would be accessed by multiple clients. To enable this feature we used the concept of VPD where the client_id is the segregating criteria between each clients. Whenever the application accepts a new request as part of operation there are few triggers fired which update other tables in the database.

The process is that when a new cleint comes onboard we need to migrte the clients old data for which we are using a custom tool from another vendor. In the meantime whenever the old clients who have already been migrated use the system the requests from them will fire the triggers. We don't want the triggers to fie during the data migration and hence we are currently disabling all the triggers and stoping all clients from logging in into system during the migration process.

This as you can imagine becomes very inconvinient for the clients and they start complaining. Is there a way we can do the data migration for client B with out firing the triggers and let client A do the normal processing which will fire the triggers.

One of the options we have looked at is using GoldenGate where we do the data migration in different servers and then let Oracle sync the changes between both the live and standby databases. This is more costly option for us right now and we wanted to see if there are any solutions which will enable us to achieve this without costing us too much :-).

With Best Wishes
Sagi

Hi Tom,

regarding VPD policy and Replciation using Streams.

DB version - 10.2.0.4

- we have table t1 at source DB which is to be replicated to target DB using Oracle streams
- as a workaround row level trigger created on t1 and then table t2 was populated in the source DB.
- then table t2 is replicated to target DB using Oracle Streams.

WHat is the best way?
a) Place policy on the view rather on the table. and avoid use of trigger, data duplication etc etc?
b) any other best way.

regards

Thanks Tom.

I am just wondering what would have made the people to choose the approach by making use of triger, data duplication etc etc...
When there exists such simple solution. ( by making use of view)


I will get deep of it.. on how to make use view in this case.

regards

What is the difference if in policy function I write

if (user = 'SCOTT') then
return '1=1';
end if;

and

if (user = 'SCOTT') then
return null;
end if;


I am creating a mview on a table and few columns are masked on that table. Oracle doesn't allow to create one if policy function has return '1=1' in it, but if I replace it with the second option, it works fine.
Please suggest

Hi Tom

We have an installation with VPD, where we tried to use the approach suggested here.
We are facing problems with ORA-02070, when we are “exporting” data by inserting via a db-link.
I think it all boils down to the fact that the SYS_CONTEXT can’t be evaluated when used via a db-link:

insert into tata@SKOLE348(id)
select empno from scott.emp where deptno = sys_context( 'dept_ctx', 'deptno' );

Will give the ORA-02070.

For now we have to use a policy function like
if ( user= 'SCOTT' ) then
return 'deptno = ' || sys_context( 'dept_ctx', 'deptno' )
else
return null;
end if;

This is causing problems due to the missing BIND of the deptno.

Can you provide a workaround to this problem?