Wanted to add something to it
January 29, 2003 - 8pm Central time zone
Reviewer: Jai Bathija from California, USA
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
Policy and Refresh of Materialized view
January 29, 2003 - 10pm Central time zone
Reviewer: Ajeet from Cincinnati,OH
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.
Followup January 30, 2003 - 8am Central time zone:
[tkyte@tkyte-pc-isdn tkyte]$ oerr ora 1531
01531, 00000, "a database already open by the instance"
doubt it was that. when you know what it was and BETTER YET, when you have a small example (like I
do), we'll take a look at it.
VPD
January 30, 2003 - 9pm Central time zone
Reviewer: Pushparaj Arulappan from NJ, USA
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
Followup January 31, 2003 - 7am Central time zone:
you won't be doing it that way -- you cannot "pass a parameter upon startup"
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??? but anyway -- in a logon trigger, most people do a lookup into tables they own and
control to find the value that needs be set.
the only way to make it transparent to the application is to, well, make it transparent -- to not
need ANY inputs from the user whatsoever (table driven for example)..
vpd
January 31, 2003 - 10am Central time zone
Reviewer: Pushparaj Arulappan from NJ, USA
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
Followup January 31, 2003 - 10am Central time zone:
so the user says "hah hah, mgr told me 10, I want to see 20. I'll just tell the application 20,
now I'm in"
that is my point. why bother having a door if you are going to leave it open?
The manager needs to have a small application written for them. This application will allow the
manager to insert/update records in a "user to deptno" table. Mgr wants employee "BOB" to work on
deptno 10 -- fine, mgr goes to application and updates BOB's record in this table to 10.
Your on-logon trigger trusts this table to have the proper values -- reads it by username and sets
the context. There you go. No matter what environment they log in from -- BOB gets the right
deptno.
Your approach, of the user telling you the deptno they are allowed to see, is like giving someone
the ability to tell the bank teller "how much money they have in the bank".
Global Contexts.
April 2, 2003 - 8am Central time zone
Reviewer: Martin from UK
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
Followup April 2, 2003 - 8am Central time zone:
It is cumulative!
PLSQL is just an application development language -- with lots of security features to boot (really
nice that users do not need insert/update/delete/select on the base tables). PLSQL lets you have
the "super user" concept you get with many Ntier applications -- but lets the end user log in as
themself (so your procedure KNOWS who is running the procedure).
So, just as I would say your security logic doesn't belong in the middle tier, the security logic
doesn't belong in your PLSQL either. Your PLSQL | JAVA code shouldn't be looking at "who is logged
in, where are they logged in from, what application are they user, what app role did they request,
etc" to figure out what data they have access to -- the data can take care of that.
Consider that you do not access table T in just one procedure, but perhaps dozens. You want to
implement the security as few times as possible -- one being optimal. FGAC lets you do that.
Thanks
April 2, 2003 - 8am Central time zone
Reviewer: Martin from UK
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
Followup April 2, 2003 - 9am Central time zone:
on the web -- when the app context takes measurable time to load. Suppose you needed to do an LDAP
lookup in order to instantiate the context with the proper information. Rather then hitting your
ldap server once per page, you hit it once - when they log in. You would use it when the cost of
setting the app context is sufficiently high enough to mandate it.
FGAC and Materialized view
August 25, 2004 - 10am Central time zone
Reviewer: Salim Adenwala from NJ, USA
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.
Followup August 25, 2004 - 10am Central time zone:
"do they work" -- yes
"can we always rewrite" -- no, not if the FGAC policy prevents us from doing so.
if the necessary information for filtering the data is in the MV and the FGAC policy can be applied
to the MV safely, yes, it'll "work"
i have another question
October 19, 2004 - 6pm Central time zone
Reviewer: A reader
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
Followup October 19, 2004 - 9pm Central time zone:
they are simply "name" and "value" pairs.
not sure what to make of your privacy 1 .. 7 bits of information. not really sure what it all
means.
multiple values for a context
October 20, 2004 - 10am Central time zone
Reviewer: A reader
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
Followup October 20, 2004 - 11am Central time zone:
for i in 1 .. 9
loop
if (i_should_not_have_this)
then
dbms_session.set_context( 'my_ctx', 'privacy_indicator_column_' I, '' );
else
dbms_session.set_context( 'my_ctx', 'privacy_indicator_column_' I, 'Y' );
end if;
end loop;
now,
(select to_number(substr(attribute, 18))
from session_context
where attribute like 'PRIVACY_INDICATOR_%'
and value = 'Y')
will return them "all"
Is there any way to refresh the context values.
October 20, 2004 - 4pm Central time zone
Reviewer: A reader
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.
Followup October 20, 2004 - 5pm Central time zone:
you just "set them" again?
how to refresh the context values
October 20, 2004 - 5pm Central time zone
Reviewer: A reader
If i reset the context values , will it automatically reset the user sessions.
Followup October 20, 2004 - 8pm Central time zone:
don't know what you mean.
a context value is just a variable/value pair in a named space in a session. don't know what you
mean by "reset the users sessions"
how to refresh the context values
October 21, 2004 - 10am Central time zone
Reviewer: A reader
If i reset the context values , will it automatically reset the user sessions.
Followup October 21, 2004 - 2pm Central time zone:
the users sessions WHAT?
no matter how many times you say it -- unless you radically change the wording, i'm not going to
understand it.
Sorry
October 21, 2004 - 2pm Central time zone
Reviewer: A reader
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.
multiple values
October 21, 2004 - 5pm Central time zone
Reviewer: A reader
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, '' );
Followup October 22, 2004 - 3pm Central time zone:
did you not see my example above? with the subquery? it shows you exactly how to do this.
Alternatively, you could of course do this:
bwhere := 'column_name in ( NULL';
loop
if theprivate = 9 then
bwhere := bwhere ||
', sys_context( ''my_ctx'', ''theprivate_' || i || ''' )';
dbms_session.set_context( 'my_ctx', 'theprivate_' || i, 9 );
i := i+1;
elsif ....
.....
you end up with a predicate like:
where column_name in (NULL, sys_context('my_ctx',theprivate_1'),
sys_context('my_ctx',theprivate_2'), ..... )
or, you could just:
bwhere := column_name in (sys_context('my_ctx',theprivate_1'),
sys_context('my_ctx',theprivate_2'),
....
sys_context('my_ctx',theprivate_9') )
since you only have 9 after all and
for i in 1 .. 9
loop
dbms_session.set_context( 'my_ctx', 'theprivate_' || i, null );
end loop;
for x in your_query
loop
dbms_session.set_context( 'my_ctx', 'theprivate_' || x.theprivate, x.theprivate );
end loop;
there -- at least three different ways
Applying VPD dynamically
March 1, 2005 - 12pm Central time zone
Reviewer: Tariq from Boston
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
Followup March 1, 2005 - 12pm Central time zone:
Not sure what you mean?
"if we have VPD attached on Table during design time , we cannot
install our server model on the Sybase db for example"
what does "attached during design time" mean?
VPD design time
March 1, 2005 - 1pm Central time zone
Reviewer: A reader
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
Followup March 1, 2005 - 2pm Central time zone:
nope, clear as mud still.
"At design time" -- not sure what you mean by this and why it affects SQL Server?
What is "design time" and why does it affect a database?
That above is just a snippet of code. Until it runs in an Oracle database it is nothing more than
a snippet of code -- it is not "attached" to anything.
seems like you are trying to build a univeral installation script -- I wonder how you can do that
since your "vpd" implementation in the other database is going to be "very different"?
"univeral installation",
March 1, 2005 - 2pm Central time zone
Reviewer: A reader
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
Can contexts be used to solve my current problem.
March 30, 2005 - 10am Central time zone
Reviewer: Steve Kiteley from UK
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.
Followup March 30, 2005 - 10am Central time zone:
The way I would think to do this would be to have a 'privileged user' that is allowed to tell your
policy function who the real user is.
So, you have some policy code that looks at the user and builds a predicate for them. This policy
code needs change from saying something like:
if ( user = 'BILL' )
to
if ( user = 'BILL' or
(user=PRIV_USER and sys_context('my_ctx','real_name')='BILL') )
then
....
Now, your privileged user is given execute on a procedure (no one else is) that can set the
"real_name".
Perhaps just instead of using the username, you use:
nvl(sys_context('my_ctx','real_name'),user)
that is, if the real_name is set (must be privileged user) use it, else use the user.
VPD Policy on Insert?
April 8, 2005 - 9am Central time zone
Reviewer: Rich from Chicago, IL
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.
Followup April 8, 2005 - 9am Central time zone:
You can see all employees.
Howerver, you can only create employees that work for you.
for example. Yes, there are cases where it makes sense to have an insert policy, sort of like a
view with a "with check option", you can only create data that fits a certain policy.
VPD on Insert
April 8, 2005 - 9am Central time zone
Reviewer: Rich from Chicago, IL
Makes perfect sense to me now. Thanks a lot Tom.
VPD
April 18, 2005 - 5am Central time zone
Reviewer: reader from India
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
Followup April 18, 2005 - 7am Central time zone:
You might be interested in the David Knox security book (links i like tab on top of page).
Basically, I would encourage you to look at n-tier proxy authentication and evaluate that. Let the
middle tier authenticate as "itself" but when a user wants to do something, create a session on
their behalf (does not require a new CONNECTION to the database, just creates a new session in the
current connection). That would let you have end to end identification and authorization.
Else, the middle tier is going to have to call some routine you write that sets an application
context with the users identity and that is what you would use. eg:
create context our_ctx using identify_thyself
/
create procedure identify_thyself( p_user_identity in varchar2 )
as
begin
if ( USER = 'MIDDLE_TIER_ACCOUNT' )
then
dbms_session.set_context( 'our_ctx', 'their_identity', p_user_identity);
else
raise_application_error( -20001, 'Umm, no way' );
end if;
end;
/
your middle tier, after grabbing a connection, would call that -- and your VPD policy would use
that to drive the predicate, if not set, return the predicate 1=0 or just raise an error and fail
the sql outright.
VPD question
May 17, 2005 - 5pm Central time zone
Reviewer: Anne from MN
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.
Followup May 17, 2005 - 6pm Central time zone:
1) no....
2) won't return the same thing
3) YES
4) you could, since it won't change during the session (you won't change the user name during the
session)
Ok, here is how it could be approached
a) logon trigger, set the context:
create procedure set_context
as
l_org_id hr_orgs.org_id%type;
begin
SELECT org_id
INTO l_org_id
FROM hr_orgs
WHERE org_user = user;
dbms_session.set_context( 'your_context', 'org_id', l_org_id );
end;
/
call that from a logon trigger -- or have the application call it to set the context for the
session.
do not use the open/fetch/close trick -- you WANT (need) to know and NEED to fail if:
a) no data found
b) too many rows
so, select into is the only way to go.
the predicate function would be:
AS
BEGIN
if ( user = 'APPS' ) then
return '';
else
return 'org_id = sys_context( ''your_context'', ''org_id'' )';
end if;
END;
that way, org_id = sys_context is treated like a bind variable, you won't hit issues with your
plsql function getting called over and over and over in a single query (the get_org function). and
you read the org table once at session startup, so you don't hit it on each and every parse.
VPD question
May 18, 2005 - 10am Central time zone
Reviewer: Anne from MN
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...
Followup May 18, 2005 - 11am Central time zone:
http://asktom.oracle.com/~tkyte/article2/index.html 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;
VPD question
May 18, 2005 - 10am Central time zone
Reviewer: Anne from MN
Oops, Tom I forgot to mention that I created the context as :
CREATE CONTEXT org_context USING security_pkg.set_context;
Thanks.
Followup May 18, 2005 - 11am Central time zone:
that's wrong, it should be the OWNER.PACKAGE -- not PACKAGE.PROCEDURE
that allows the context to be set by a schema named security_pkg that owned a procedure
set_context.
VPD question
May 18, 2005 - 12pm Central time zone
Reviewer: Anne from MN
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 ?
Followup May 18, 2005 - 1pm Central time zone:
calling sys_context from plsql (but NOT SQL) is relatively expensive. Rather do it ONCE per
session, not per parse. So.... use a package with elaboration code that sets the context the first
time the package is touched:
create or replace context my_ctx using set_it
/
create or replace procedure set_it
as
begin
dbms_session.set_context( 'my_ctx', 'x', 'X' );
end;
/
create or replace package rls_helper
as
function where_clause return varchar2;
end;
/
create or replace package body rls_helper
as
function where_clause return varchar2
is
begin
return 'x = sys_context(''my_ctx'',''x'')';
end;
begin
SET_IT;
end;
/
that is start up code that'll be run during the package getting loaded in the session, only once.
create or replace
function my_security_function( p_schema in varchar2,
p_object in varchar2 ) return varchar2
as
begin
if ( user = 'SYS' ) then
return '';
else
return rls_helper.where_clause;
end if;
end;
/
VPD
May 18, 2005 - 4pm Central time zone
Reviewer: Anne from MN
Tom, that works like a charm. Thanks so much!
How to find application context
May 19, 2005 - 10am Central time zone
Reviewer: Anne from MN
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.
Followup May 19, 2005 - 11am Central time zone:
dba_context shows the context name and the package/procedure it is associated with.
the context is "public", doesn't have an owner after creation. but you can probably use the schema
column of dba_context as the "owner" as the package/procedure the context is associated with sort
of denotes the "ownership"
How to find application context
May 19, 2005 - 1pm Central time zone
Reviewer: ANNE from MN
Yes, that's exactly what I wanted. THANK YOU!
Dynamically assign policy
June 14, 2005 - 10am Central time zone
Reviewer: Anne from MN, USA
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.
Followup June 14, 2005 - 1pm Central time zone:
why are you dynamically executing anything:
psuedo code:
begin
for x in ( select * from user_tables )
loop
dbms_rls.add_policy( object_schema=> user, object_name => x.table_name ...)
end loop;
end;
/
????
Dynamically assign policy
June 14, 2005 - 11am Central time zone
Reviewer: Anne from MN, USA.
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!
Followup June 14, 2005 - 1pm Central time zone:
do not USE dyanamic SQL -- you don't need it!
Dynamically assign policy
June 14, 2005 - 2pm Central time zone
Reviewer: Anne from MN, USA.
Excellent - so simple, it never occurred to me ! Thank you so much!
VPD Performance Question
August 20, 2005 - 2am Central time zone
Reviewer: Sarma from Reston, VA
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
Followup August 20, 2005 - 5pm Central time zone:
take your query and put the vpd predicates in it, that is, take:
FROM
docs dc
,process_instances pi
,process_templates pt
,lease_phases lp
,lease_Phases lph
,task_templates tt
and rewrite that part as:
FROM
(select * from docs where ..... )dc
,(select * from process_instances where....) pi
.....
and tune it the way you would anything else -- look at the resulting query and it might even be
obvious why it takes "a long time"
VPD Performance
August 22, 2005 - 11am Central time zone
Reviewer: Sarma from Reston, VA
<code>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
Followup August 23, 2005 - 8am Central time zone:
put it where I put it, that is what vpd does.
you are in testing so you can find things that will cause you not to go into production with the
existing code. that is why you are testing.
VPD Performance
August 22, 2005 - 12pm Central time zone
Reviewer: Sarma from Reston, VA
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
Followup August 24, 2005 - 3am Central time zone:
you'll have to play with tuning your query -- I can say that a VPD policy that has to "walk" so
many tables is questionable. I try to have everything needed in the table itself (or at most one
table away).
VPD Performance
August 22, 2005 - 2pm Central time zone
Reviewer: Sarma from Reston, VA
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
To check the rewritten query by VPD ...
August 24, 2005 - 12pm Central time zone
Reviewer: Arup Nanda from Danbury, CT, USA
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.
VPD Performance
August 24, 2005 - 7pm Central time zone
Reviewer: Sarma from Reston, VA
Hi Arup Nanda,
Thanks for the information.
Regards
Sarma
RLS and MV
June 9, 2006 - 12pm Central time zone
Reviewer: reader from NJ
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
Followup June 9, 2006 - 1pm Central time zone:
You have a policy that returns a predicate "where ...." for each of t1, t2
You have tables T1, T2 ...
You create a MV MV on t1, t2.
You query:
select * from t1, t2 where....
that becomes:
select * from (select * from t1 "where ... ") t1, (select * from t2 "where..." )
where ...
it is AS IF that is what you typed in. IF that rewritten query can use the MV's (eg: the columns
accessed in the predicates added to t1 and t2 are still valid if you rewrite against the MV), it'll
happen.
If not, It won't
All RLS is doing (row level security) is adding predicates. It is AS IF you typed that query in in
the first place
And if a MV rewrite can happen against the new query - great, just like indexes are transparently
used.
Further on MV and RLS
June 14, 2006 - 11pm Central time zone
Reviewer: A reader from NJ, USA
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.
Followup June 15, 2006 - 8am Central time zone:
predicate is "where deptno = sys_context( 'my_ctx', 'deptno')"
That is, you are limited to seeing rows in YOUR deptno only.
MV is:
select deptno, count(*), ....
from emp
group by deptno;
And you query:
select count(*) from emp;
.... for example... as long as sufficient columns remain in the mv.
We have a similar problem altogether
July 12, 2006 - 8pm Central time zone
Reviewer: Oj from Carson City,NV
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???
Followup July 12, 2006 - 8pm Central time zone:
you are inside a definers rights environment - no roles, you cannot turn them on.
and you mean 28112?
We have a similar problem
July 13, 2006 - 11am Central time zone
Reviewer: oj from Carson City,NV
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'.
Followup July 13, 2006 - 1pm Central time zone:
you are correct - insufficient data.
the policy function must be getting invoked then. but it is not doing what you "expect" (and that
must be what you mean by "it doesn't work")
Mviews and FGAC
August 3, 2006 - 9am Central time zone
Reviewer: Basil
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?
Followup August 3, 2006 - 10am Central time zone:
say you have a policy that says "where deptno = sys_context()"
anything that aggregates away the deptno column - well, that mv is useless (the level of detail
needed to rewrite the query against the MV just isn't there)
so, before we even look at this - your MV's - are they aggregating away needed information?
(but the error message for this is pretty clear... it is not that you disable the policy, but
rather the policy accomodates the refresh process by NOT HIDING data)
August 3, 2006 - 10am Central time zone
Reviewer: Basil
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.
Followup August 3, 2006 - 10am Central time zone:
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!
you need to make it so that the policy says "I shall return null when I am being used in the
refresh"
probably easiest way is to say "owner of table sees all data in table, owner of table does the
refresh, return null for owner of table"
Can VPD for column-level returns string instead of NULL ?
August 3, 2006 - 10am Central time zone
Reviewer: Alex from CA
Is there an undocumented parameter one can set to make it return a string, say "****" instead of
NULL, for secured-columns ?
Thank you
Followup August 3, 2006 - 11am Central time zone:
beyond NVL - nope
We have a similar problem
August 3, 2006 - 3pm Central time zone
Reviewer: Rinkesh Patel from Reno, NV USA
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.
FGAC Policies using SYS_CONTEXT
May 18, 2007 - 12pm Central time zone
Reviewer: Basil
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.
FGAC, Refresh, Queues, and Jobs, Oh My
June 6, 2007 - 2pm Central time zone
Reviewer: Basil
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?
Followup June 6, 2007 - 9pm Central time zone:
... especially since the problem does not appear when the batch process is invoked from SQL*Plus. ...
i do not understand what that means.
I'm not sure what is trying to be accomplished here - can you explain that (rather than show something that doesn't work... tell us what you need to have happen)
The answer
June 6, 2007 - 2pm Central time zone
Reviewer: Basil
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.
Application schema
June 8, 2007 - 9pm Central time zone
Reviewer: VKOUL from WA USA
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
Followup June 9, 2007 - 10am Central time zone:
solution a: I don't see how that solves anything - the packages would point to the view when they are compiled - creating new views would do what?
solution b: not really - there is this thing called virtual private database whereby you could add a policy to the tables that returns a where clause "where customer_id = sys_context(...)" and a trigger to populate it. All you would need to do is populate the customer id in the environment when you log in.
rename table to base_table;
create view table as select * from base_table;
grant on table;
alter table base_table add customer_id number;
create trigger to maintain customer_id
create policy to add "where customer_id = sys_context()"
Now, select * from table would really be:
select (* but not customer_id) from bast_table where customer_id = sys_context
solution c: see b
You would/could list partition the data and upon getting a new customer, add a new partition for their customer id to the tables - done.
Application schema
June 9, 2007 - 6pm Central time zone
Reviewer: VKOUL from WA USA
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
Followup June 10, 2007 - 9am Central time zone:
q1) you would use the same granting mechanism you have today, correct
q2) it is part of enterprise edition, it comes with enterprise.
q3) You would be renaming the tables, creating views of them, adding triggers to maintain a column, implementing vpd to select out the right rows based on this (presumed to be) new column. There would be no change to existing code.
Application Schema
June 10, 2007 - 12pm Central time zone
Reviewer: VKOUL from WA USA
Thanks a lot, with that info I have now confidence to move forward.
January 15, 2008 - 5pm Central time zone
Reviewer: A reader
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!
Followup January 15, 2008 - 5pm Central time zone:
why couldn't you be using BOTH?
You use a policy when you access an object protected by that policy. So query out what object that policy protects and tell us if you accessed that object.
January 16, 2008 - 6am Central time zone
Reviewer: A reader
Thanks Tom, but how could I now if some policy is setted for my current session without known the name of the objects?
Thanks!
Followup January 16, 2008 - 3pm Central time zone:
policies are not "setted" for your session, they are invoked when you access an object - your question does not compute for me
You'd have to know the objects you accessed in order to determine if any policies where invoked.
values set in the context being used for some other users session
February 24, 2008 - 6pm Central time zone
Reviewer: PLS newbie from London UK
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).
Followup February 25, 2008 - 2am Central time zone:
application contexts by default are private to a session.
you may create GLOBAL APPLICATION contexts to be shared across sessions, but you have to explicitly do that (and no commit is needed, just set the value and they would be visible)
VPD for Roles
November 4, 2008 - 7am Central time zone
Reviewer: A reader
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,
Followup November 11, 2008 - 12pm Central time zone:
are you trying to find out if role "X" is enabled?
if so, just select from session_roles where role = 'whatever role you are interested in'
read a cookie in a VPD function
June 10, 2009 - 7am Central time zone
Reviewer: jai from Spain
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..
Followup June 10, 2009 - 12pm Central time zone:
unless you are using mod_plsql, the owa functions are meaningless.
the cookies are SET in the PLSQL cgi environment by mod_plsql, if you are not using mod_plsql - they cannot "magically" appear there.
Your APPLICATION must identify themselves to the database if your code in the database wants to know who they are. Your java code, your forms, everything - they would need to send you this bit of information.
VPD Date Issue
October 29, 2009 - 7pm Central time zone
Reviewer: Newuser from Sanfransciso, CA USA
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"
Followup October 30, 2009 - 9am Central time zone:
you would need to give the full example. the policy should not impact this at all unless the policy itself does not permit that user to see any data.
perhaps you have a case whereby no data is returned for that user given that date range.
test it in sqlplus - run the query as VPD would rewrite it, and run the query with VPD. show us the code behind vpd, prove to us that it should return data.
|