Thanks for the question, Ken.

Asked: May 31, 2000 - 5:01 pm UTC

Last updated: March 13, 2009 - 11:46 am UTC

Version: version 8.1.6

Viewed 1000+ times

You Asked

Tom,

I just finished reading your excellent article on Fine Grained
Access Control and I have played with it some on my own. With
regard to the "Important Caveat" section of the article, is
there a way to force a cursor reparse? In other words, is there
any way to change a predicate during a given session? I realize
there are reasons to not do this. However, with a very very
large B2B web application it would be simpler to create a
limited number of database userids (e.g., one per company)and
then change the predicate based upon the application (not
database) userid. Creating and maintaining thousands of
database userids is very labor intensive. Having hundreds or
thousands of different database connections through an
application server may be problematic, also. Any ideas or
options?

and Tom said...

It depends on the programatic environment and how you program to it.

From that important caveat section:

...
I should mention that there are cases where changing the predicate in the middle of a session may be desirable. The client applications that access objects that employ policies that can change predicates in the middle of a session must be coded in a specific fashion to take advantage of this. For example, in PLSQL we would have to code the application using dynamic sql entirely to avoid the cursor caching. If you are employing this dynamic predicate method then you should bear in mind that the results will depend on how the client application is coded, therefore you should not be enforcing a security policy with this use of this feature. We will not be discussing this possible use of the DBMS_RLS feature but rather will concentrate on its intended use to secure data.
...

So, for example, if your application logic is written in PLSQL, you would have to use a construct like:

declare
type rc is ref cursor;
l_cursor rc;
l_rec emp%rowtype;
begin
open l_cursor for 'select * from emp';
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
...
end loop;
close l_cursor;
end;

instead of

begin
for x in ( select * from emp ) loop
...
end loop;
end;

Else PLSQL will cache the parsed cursor (in the for loop example) for your session. We would never get a chance to reparse it and hence the RLS function would never get called again for it. To avoid that, we do not allow plsql to cache the cursor simply by hiding the cursor from view.

If I was using Pro*C, hold_cursor=NO on the precompile line should be sufficient to avoid this issue as well.

If I was using Java, closing the prepared statements/ callable statements and reparsing them should be sufficient.

Rating

(24 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A Twist...

David Thompson, October 11, 2001 - 8:23 pm UTC

We have a similar situation where we are using connection pooling from ASP/VB objects from the web. In essence every page hit grabs an Oracle connection from the pool and uses it. So our connections are not only using the same user, but the same session! This can be a real problem if not designed and built correctly.

We also us the SYS_CONTEXT and RLS policy stuff to identify predicates that determine what a user can/not see. If the predicates are cached, we might be returning the wrong data to a user!!! Real problem.

So, we added something to our "Who am I" procedure (a PL/SQL procedure to identify the person and context who is currently using the session). This procedure is the FIRST call made EVERYTIME a user pulls a session from the pool. Since no DML statements would be in limbo at this time a call to the DBMS_RLS.REFRESH_POLICY(...) procedure is safe (I think).

DBMS_RLS.REFRESH_POLICY basically flushes the cache of parsed SQL statements in the sqlarea. After calling this procedure for each policy in the database, any future queries against a policy driven table would be forced to be reparsed. I know this might seem to be a brutal approach but it guarantees all our SQL statements will have the policy applied correctly.

DBMS_RLS.REFRESH_POLICY does do a commit so we had to make sure that all transactions have either been committed or rolled back before we put the session back in the pool. We also coded it such that the procedure is called only when the "context" changes. Thus reducing the number of calls to the procedure.

This seems to work well for us. Any feedback would be appreciated....

October 12, 2001 - 8:13 am UTC

My comments on the above.

In 815 and 816 -- that would work and would be necessary HOWEVER, the hit on the shared pool is terrible. I personally would prefer to log off/log in, you might even find performance to be BETTER (that shared pool hit is HUGE in terms of CPU and latching -- simply HUGE).

In 817, you'll find that the cursors will automatically be flushed in a SESSION when the contex is change. This effectively corrects this.

To get the full story (44 pages of it), I have a chapter on RLS in my book that covers this caveat above -- the fix in 817 for it -- but also covers the other gotcha with cached cursors that still exists in 817.

8.1.7.2.0 - Caching dynamic SQL

David Thompson, October 12, 2001 - 9:12 am UTC

Tom,

I will buy your book today and that might solve my problems. But I found just the opposite: the following works in 8.1.6 but not 8.1.7!

RDBMS Version: 8.1.7.2.0

DBMS_RLS: Statements are caching

I am using the DBMS_RLS procedures to set contexts and then use them in policy statments to reduce the number of rows returned.

It seems that, within the same session, if I change a context variable and execute a dynamic SQL statement, the statement is parsed and executed correctly the first three times. After that, the statement is cached and any changes to the context variables have no effect on the dynamically parsed SQL.

Here is an example:

declare
cnt number;
l_user VARCHAR2(30);
begin
for x in 1 .. 4 loop
rts_security.set_user('UATESTER9');
execute immediate 'select sys_context(''rts_security'',''userid'') , count(*) from mtp_oper_co ' into l_user,cnt;
dbms_output.put_line (x||'-'||l_user||':'||cnt);
rts_security.set_user('SYSADMIN');
execute immediate 'select sys_context(''rts_security'',''userid'') , count(*) from mtp_oper_co ' into l_user,cnt;
dbms_output.put_line (x||'-'||l_user||':'||cnt);
end loop;
end;

Here is the output:

1-UATESTER9:1
1-SYSADMIN:3
2-UATESTER9:1
2-SYSADMIN:3
3-UATESTER9:1
3-SYSADMIN:3
4-UATESTER9:3
4-SYSADMIN:3

Notice on the forth try, the count for UATESTER9 is not set at 3. Any future attempts will return 3 for this session...!>!>!>?!?!?!

October 12, 2001 - 12:45 pm UTC

I have to assume some logic in your predicate function is bad then.  I assume this for two reasons:

1) I cannot reproduce your situation

2) Using dynamic sql like that OBVIATES the cursor cache, those cursors are NOT cached (dynamic sql cannot be cached by PLSQL).  Using dynamic sql and such was a workaround for this specific issue in 815 and 816.  (in fact, the following test case works in 815..817 the same except when we use static sql).

Here is a test case -- can you tweak it to replicate in your environment the above issue?  Else post all of the code in your security policy for us to look at:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace context rts_security using rts_security
  2  /

Context created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure rts_security( p_name in varchar2, p_val in varchar2 )
  2  as
  3  begin
  4      dbms_session.set_context( 'rts_security', p_name, p_val );
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function rls_examp
  2  ( p_schema in varchar2, p_object in varchar2 )
  3  return varchar2
  4  as
  5  begin
  6      if ( sys_context('rts_security','userid') = 'UATESTER9' )
  7      then
  8          return 'rownum = 1';
  9      else
 10          return '1=1';
 11      end if;
 12  end;
 13  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2     dbms_rls.add_policy
  3     ( object_name => 'T',
  4       policy_name => 'T_POLICY',
  5       policy_function => 'rls_examp',
  6       statement_types => 'select, insert',
  7       update_check    => TRUE );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2    cnt       number;
  3    l_user VARCHAR2(30);
  4  begin
  5    for x in 1 .. 40
  6    loop
  7      rts_security('userid','UATESTER9');
  8      execute immediate 'select sys_context(''rts_security'',''userid'') , count(*)
  9                           from t ' into l_user,cnt;
 10      dbms_output.put_line (x||'-'||l_user||':'||cnt);
 11      rts_security('userid','SYSADMIN');
 12      execute immediate 'select sys_context(''rts_security'',''userid'') , count(*)
 13                           from t ' into l_user,cnt;
 14      dbms_output.put_line (x||'-'||l_user||':'||cnt);
 15    end loop;
 16  end;
 17  /
1-UATESTER9:1
1-SYSADMIN:3
2-UATESTER9:1
2-SYSADMIN:3
3-UATESTER9:1
3-SYSADMIN:3
4-UATESTER9:1
4-SYSADMIN:3
5-UATESTER9:1
5-SYSADMIN:3
6-UATESTER9:1
6-SYSADMIN:3
7-UATESTER9:1
7-SYSADMIN:3
8-UATESTER9:1
8-SYSADMIN:3
..... (always 1, 3, 1, 3...)

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2    cnt       number;
  3    l_user VARCHAR2(30);
  4  begin
  5    for x in 1 .. 40
  6    loop
  7          for j in 1..2
  8          loop
  9          if ( j = 1 ) then
 10                          rts_security('userid','UATESTER9');
 11                  else
 12                  rts_security('userid','SYSADMIN');
 13                  end if;
 14          select sys_context('rts_security','userid') , count(*)
 15                    into l_user,cnt
 16            from t;
 17  
 18                  dbms_output.put_line (x||'-'||l_user||':'||cnt);
 19          end loop;
 20    end loop;
 21  end;
 22  /
1-UATESTER9:1
1-SYSADMIN:3
2-UATESTER9:1
2-SYSADMIN:3
3-UATESTER9:1
3-SYSADMIN:3
4-UATESTER9:1
4-SYSADMIN:3
5-UATESTER9:1
5-SYSADMIN:3
6-UATESTER9:1
6-SYSADMIN:3
7-UATESTER9:1
7-SYSADMIN:3
8-UATESTER9:1
8-SYSADMIN:3

(always 1,3,1,3,...)

The problem manifests itself in 815 and 816 only with the LAST block of code -- whereby the SAME exact static sql statement would be "frozen" as regards the predicate function.  In 815, 816 -- it behaves differently:

ops$tkyte@ORA815.US.ORACLE.COM> declare
  2    cnt       number;
  3    l_user VARCHAR2(30);
  4  begin
  5    for x in 1 .. 40
  6    loop
  7          for j in 1..2
  8          loop
  9          if ( j = 1 ) then
 10                          rts_security('userid','UATESTER9');
 11                  else
 12                  rts_security('userid','SYSADMIN');
 13                  end if;
 14          select sys_context('rts_security','userid') , count(*)
 15                    into l_user,cnt
 16            from t;
 17  
 18                  dbms_output.put_line (x||'-'||l_user||':'||cnt);
 19          end loop;
 20    end loop;
 21  end;
 22  /
1-UATESTER9:1
1-SYSADMIN:1
2-UATESTER9:1
2-SYSADMIN:1
3-UATESTER9:1
3-SYSADMIN:1
4-UATESTER9:1
4-SYSADMIN:1
5-UATESTER9:1
5-SYSADMIN:1

(always 1 for that SPECIFIC query)

vishnu

vis, October 18, 2001 - 10:25 am UTC

Hi Tom,
I don't want to burden you with another question on RLS.My requirement is simple ..I have one table called formulae with fields...formulae_id,name,whocan_see_it..depending on the log in only those records should be seen by them ..As i said i want to buy yr book on RLS for complicated things ..Can u pl give me book details and the solution please...
vishnu

Returning a NULL policy

David Thompson, February 12, 2002 - 5:44 pm UTC

Tom,

You are correct!!

Long time since my response to this but, as it turns out, if you changed your policy statement to return a NULL instead of 1=1 you would have seen the same results as I did. I corrected my policy statement to NEVER return a NULL value and now it works fine!!!! Try your example again but return a NULL instead of 1=1 and you should see the same results I did...

Thanks for your help!

Problem with cursor caching

Bharath, May 30, 2003 - 12:34 pm UTC

We are using RLS in our app:Due to some circumstance we have end up using dbms_rls.refresh_policy() to overcome cursor sharing problem.The reason we didnt used context variable is we have to the change in application code(front end).Bcos of context var is for each session.
But based on your feedback on DBMS_RLS.REFRESH_POLICY it's terribel thing to do?Is it something equal to alter system flush shared pool.

Is there any other method that i can use to overcome the cursor sharing problem.

May 30, 2003 - 1:51 pm UTC

not unless you explain in a tad more detail what the problem IS exactly.

problem with cursor sharing

Bharath, May 30, 2003 - 2:28 pm UTC

our dynamic predicate is based on role from the table(role_xref) which will be changed outside the appilcation by another common security module.if user login with role A and in the middle of his session if Admin changes his roles then prodicate policy has to be changed immediately.If we change the role in the middle of the session and due to cursor cache some the sql in PLSql still uses old predicate not the new one.If i want to overcome these i have two options
1,Contect Var
2,dbms_rls.refresh_policy

I cant use contect Var bcos i need to change the context thru the application which user A is accessing .In order to overcome this problem we have used dbms_rls.refresh_policy to reparse.we are doing this in the application where we can change the role of the user.Does it make sense?

May 30, 2003 - 2:45 pm UTC

then you will have to flush the cached cursors as you are.

if you change the context in a session, we'll invalidate just that sessions cached cursors.

If you change a value in a table and want the currently logged in sessions to see that change right away, you'll need to do what you are doing. there is no other alternative.

Problem with cursor caching

Bharath, May 30, 2003 - 3:01 pm UTC

Thanks for Quick reply.
I want to know what is the difference between changing the context and dbms_rls.Both will make sure that sql will reparsed when it excutes next time.The way dbms_rls.refresh is doing is removing the entry(only sql which refering the refreshed policy) from V$open_cursor(Cached cursor) what is happening when i change the session_conext ?

May 31, 2003 - 11:24 am UTC

dbms_rls.refresh invalidates all related cached sql in the shared pool.

a context change would invalidate cursors for a single session only.

So, if you can accomplish your goals via a context change, it is more efficient then doing it for the entire system.

In your case, you cannot.

Invalidate session cached cursor

Bharath, May 30, 2003 - 3:46 pm UTC

>>we'll invalidate just that sessions cached cursors.

What do you mean by Invalidate seesion cached cursors.

I want to know whether session cached cursor parameter has any impact on FGAC.

May 31, 2003 - 11:28 am UTC

we'll invalidate the cursors in your session that might be affected by a change in a context setting (eg: any cursors that rely on FGAC)

sorry for the confusion -- it has nothing to do with session cached cursors.

Finally

bharath, June 02, 2003 - 8:44 am UTC

>>sorry for the confusion -- it has nothing to do with >>session cached cursors

if session cached cursor cache the closed cursor right.

Question 1.Will it create a problem if predicate changes inside the session and bcos of cursor cache(closed cursor cache) it wont get reparse when it's get executed.

Question 2.Will context changes or dbms_rls.refresh invalidate the session cached cursor .so when it executes it will reparse.

June 02, 2003 - 9:10 am UTC

the database will handle the changing predicate for you.

it'll reparse.

is it possible to add a order by clause using FGAC

umesh, June 26, 2003 - 12:48 am UTC

Tom
I have a query which says
select * from t1
order by col1;

can i use FGAC and make it

select * from t1
order by col1,col2;

June 26, 2003 - 9:07 am UTC

Create database user for every web user who access the web application

Jennifer Chen, June 18, 2004 - 3:41 pm UTC

Hi Tom,

Recently, I read your archived presentation, which states:

"Fine Grained Access Control takes the security logic out of the application logic. The application developer can concentrate on the application itself, not the logic of accessing the underlying data to keep it secure. Since Fine Grained Access Control is done entirely in the server the applications immediately inherit this logic. In the past, the application developers had to encode the logic into the application making the applications harder to develop initially and making them especially hard to maintain. If the application is responsible for mediating access to the data and you access the same data from many locations in the application, a simple change to your security policy may affect dozens of application modules. Using Fine Grained Access Control, a change in a security policy affects no application modules".

I have anticipated several web application developments in the past, and the most them coded a lot of lines to handle user password, which apps the user can access, and what data user read/modify, etc. Usually, there is only one apps database user connect to application via JDBC/ODBC in a connection pool. If we want to track down who/when did what, we have to write a home grown audit trail

We are now doing R&D to try to develop our new application. I would like to get your opinions on the subject. Is it feasible to create a database user for each web user who will be granted the access to our application? What is the best practice to take advantage what Oracle already has, such as user management, Fine Grained Access Control, and Audit Trail, etc. Does Oracle have the flaxibility for us to customized some of these supplied packages?

Thanks in advance for sharing your thoughts.

June 18, 2004 - 4:32 pm UTC

</code> http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=proxy+authentication&book=&preference= <code>

proxy authentication facilitates this, yes.

Session based FGAC in conjunction with PQ

A reader, December 29, 2004 - 8:09 am UTC

hi tom,

we want to use FGAC based on session contexts as well as partitioning and PQ.

do we have to be aware of any issues with that (e.g. do PQ slaves "share" the master's context settings)?

are there any other features you would take "special care" of when used in conjunction with FGAC (due to the fact that FGAC got added)?

December 29, 2004 - 10:16 am UTC

the rewrite (and hence access to sys_context) takes place before the PQ stuff kicks in - so it is OK.

Just beware of things like "a full database export 'is not'" (eg: yes, I've seen a DBA get burned thinking they had the full thing, not realizing that FGAC filtered the data....)

Mostly "of course" side effects -- "of course that would happen, the rules are in place".

The only SQL I can think of that is not FGAC friendly is merge.

A reader, December 29, 2004 - 1:57 pm UTC

thank you. but what 's the problem with FGAC and merge?

December 29, 2004 - 7:32 pm UTC

merge won't do FGAC protected tables.

very interesting to know

A reader, December 30, 2004 - 5:33 pm UTC

there 's no such restriction mentioned for 'merge' in SQL reference of 9i.

is it a bug (maybe in documentation)?

December 30, 2004 - 6:42 pm UTC

concur, i will file doc bug with following test case:

ops$tkyte@ORA9IR2> create or replace
  2  function security_policy_function
  3  ( p_schema in varchar2, p_object in varchar2 )
  4  return varchar2
  5  as
  6  begin
  7          return '';
  8  end;
  9  /
 
Function created.
 
ops$tkyte@ORA9IR2> create table t( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'T',
  5       policy_name     => 'X',
  6       function_schema => user,
  7       policy_function => 'security_policy_function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE,
 10       enable          => TRUE
 11     );
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> merge into t
  2  using ( select 1 x, 2 y from dual ) d
  3  on ( t.x = d.x )
  4  when matched then update set y = d.y
  5  when not matched then insert (x,y) values (d.x,d.y);
merge into t
           *
ERROR at line 1:
ORA-28132: Merge into syntax does not support security policies.

* happy new year *

A reader, January 01, 2005 - 6:46 am UTC

so you 've been once again THE source of information ;o)
thank you very much for that hint.

ORA-28132

Laxman Kondal, March 31, 2005 - 9:17 am UTC

Hi Tom

We have to use merge functionality for uploading data from low to high side and FGAC is in place.

Is there any work around (ORA-28132) - may be just for merge period and what kind of privilege user have to have before calling merge or just no way to do merge if FGAC is in place.

Doing row by row, which you described in another thread, is one way but its slow.

This I have never done it and not sure at all - Whats the effect of putting tablespace in readonly mode and disable FGAC just to upload data using merge.

Thanks.

Regards

March 31, 2005 - 9:34 am UTC

You would/could

a) not FGAC the table
b) FGAC a view of the table

that is, revoke on the table T all grants.
rename table T to T_TABLE
create a view T as select * from T_TABLE;
grant on T
put the policy on T

Now you as an admin have the ability to merge into T_TABLE without FGAC.

does that work for you?

ORA-28132

Laxman Kondal, March 31, 2005 - 9:47 am UTC

Hi Tom

Thanks for you professional advice, you always have the way to do things in Oracle and thats why 'WE' look forward to hear from you.

You have this and the other one with update and insert and now I will have to do my work to get this either way.

Thanks as always you are the source of 'professional' knowledge.

Regards.

restricting columns to users,

A reader, February 24, 2006 - 5:04 pm UTC

I have a requirement where the users should not be able to SELECT columns. However, they can specify the columns in the WHERE clause.

For example: they can say select count(*) from tab where c1 > 1; but they should get an error or warning when they say select c1,c2 from tab where c1 > 1.

Is this possible?

Thanks,

JR, December 13, 2006 - 9:08 am UTC

Merge issue with ORA-28132

cedric rollo, April 18, 2008 - 3:35 am UTC

Hi,

I dont understand why my MERGE statement issue with an oracle error.

I grant the user with the EXEMPT ACCESS POLICY but nothing change..

SQL> Merge INTO sinistre
  2     using (select si.rowid rid,
  3                                                        sods.no_ident_formul sono_ident_formul,
  4                                                        sods.cd_ty_formul socd_ty_formul,
  5                                                        sods.cd_obj_princ socd_obj_princ,
  6                                                        sods.zn_obj_assu_second sozn_obj_assu_second,
  7                                                        sods.cd_obj_princ_sin socd_obj_princ_sin
  8                                from    sinistre_ods sods, sinistre si
  9                                        where sods.cd_cent=si.cd_cent
 10                                                and sods.no_even=si.no_even
 11                                                and sods.no_sin_reduit_fd=si.no_sin_reduit_fd
 12                                                and sods.dt_chrono_even=si.dt_chrono_even) s
 13     on (sinistre.rowid = s.rid)
 14     when matched then
 15        update set      sinistre.no_ident_formul = s.sono_ident_formul,
 16                                                        sinistre.cd_ty_formul =s.socd_ty_formul,
 17                                                        sinistre.cd_obj_princ=s.socd_obj_princ,
 18                                                        sinistre.zn_obj_assu_second=s.sozn_obj_assu_second,
 19                                                        sinistre.cd_obj_princ_sin=s.socd_obj_princ_sin,
 20                                                        sinistre.dt_rattpge_decis='01/04/2008'
 21     when not matched /* never happens */ then insert (cd_cent) values (null);
Merge INTO sinistre
           *
ERREUR à la ligne 1 :
ORA-28132: La syntaxe de merge into ne prend pas en charge les règles de
sécurité.


Ecoulé : 00 :00 :00.10
SQL>


Thanks a lot.

Bye

April 18, 2008 - 10:17 am UTC

since you don't want to insert - why are you even using merge (on a table with a security policy.... there is a policy, error is about there being a policy)

just update the join.

Creation Time of a Policy

Yogesh Purabiya, September 24, 2008 - 6:06 am UTC

How to find out the date & time when the policy was created / implemented ?

MERGE and FGAC on 10.2

Karsten Spang, March 02, 2009 - 6:35 am UTC

I have been experimenting a bit, and it seems that under 10.2, you can actually use MERGE on a table with FGAC, if the predicate function returns NULL.

Can you confirm that this is intended behavior in 10.2 and above, or is it possible that another patch level may break this again?

My script:

select * from v$version where banner like 'Oracle Database%';

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

create table x (a number,b number);

create or replace function pol(a varchar2,b varchar2)
return varchar2
is
begin
return null;
end;
/

exec dbms_rls.add_policy(object_name=>'X',policy_name=>'POL',policy_function=>'POL');

insert into x values (1,2);

commit;

merge into x
using (select 1 a,3 b from dual) s
on (s.a=x.a)
when matched then update set x.b=s.b
when not matched then
insert (a,b) values (s.a,s.b);

1 row merged

commit;

create or replace function pol(a varchar2,b varchar2)
return varchar2
is
begin
return 'a=1';
end;
/

merge into x
using (select 1 a,3 b from dual) s
on (s.a=x.a)
when matched then update set x.b=s.b
when not matched then
insert (a,b) values (s.a,s.b);

ORA-28132: Merge into syntax does not support security policies.

March 03, 2009 - 3:21 pm UTC

11gr1

ops$tkyte%ORA11GR1> create table x (a number,b number);

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace function pol(a varchar2,b varchar2)
  2  return varchar2
  3  is
  4  begin
  5  return null;
  6  end;
  7  /

Function created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec dbms_rls.add_policy(object_name=>'X',policy_name=>'POL',policy_function=>'POL');

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into x values (1,2);

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> merge into x
  2  using (select 1 a,3 b from dual) s
  3  on (s.a=x.a)
  4  when matched then update set x.b=s.b
  5  when not matched then
  6  insert (a,b) values (s.a,s.b);
merge into x
           *
ERROR at line 1:
ORA-28132: Merge into syntax does not support security policies.

Grant, March 13, 2009 - 11:38 am UTC

I'm using 10.2.0.4 and policies with the merge statement appear to work. Is this a bug is my release?

SQL>select * from v$version where banner like 'Oracle Database%';

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Elapsed: 00:00:00.01
SQL>
SQL>create table x (a number,b number);

Table created.

Elapsed: 00:00:00.03
SQL>
SQL>
SQL>create or replace function pol(a varchar2,b varchar2)
2 return varchar2
3 is
4 begin
5 return null;
6 end;
7 /

Function created.

Elapsed: 00:00:00.01
SQL>
SQL>exec dbms_rls.add_policy(object_name=>'X',policy_name=>'POL',policy_function=>'POL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL>insert into x values (1,2);

1 row created.

Elapsed: 00:00:00.01
SQL>
SQL>commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL>merge into x
2 using (select 1 a,3 b from dual) s
3 on (s.a=x.a)
4 when matched then update set x.b=s.b
5 when not matched then
6 insert (a,b) values (s.a,s.b);

1 row merged.

March 13, 2009 - 11:46 am UTC

yes, a policy that returns NULL is accidentally allowed to be used in 10g, it is fixed in 11g (where the policy prevents merge from working as documented and demonstrated right above - hit "page up")

merge with VPD

a reader, October 17, 2011 - 4:53 pm UTC

Just a small update,
as the documentation states ( at
http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/vpd.htm#sthref1357 ), merge works with VPD in 11.2.02.
Tried, works.
Cheers!

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

A Twist...

8.1.7.2.0 - Caching dynamic SQL

vishnu

Returning a NULL policy

Problem with cursor caching

problem with cursor sharing

Problem with cursor caching

Invalidate session cached cursor

Finally

is it possible to add a order by clause using FGAC

Create database user for every web user who access the web application

Session based FGAC in conjunction with PQ

very interesting to know

* happy new year *

ORA-28132

ORA-28132

restricting columns to users,

Merge issue with ORA-28132

Creation Time of a Policy

MERGE and FGAC on 10.2

merge with VPD

More to Explore

Security

Fine Grained Access Control

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

A Twist...

8.1.7.2.0 - Caching dynamic SQL

vishnu

Returning a NULL policy

Problem with cursor caching

problem with cursor sharing

Problem with cursor caching

Invalidate session cached cursor

Finally

is it possible to add a order by clause using FGAC

Create database user for every web user who access the web application

Session based FGAC in conjunction with PQ

very interesting to know

*** happy new year ***

ORA-28132

ORA-28132

restricting columns to users,

Merge issue with ORA-28132

Creation Time of a Policy

MERGE and FGAC on 10.2

merge with VPD

More to Explore

Security

* happy new year *