You can improve the export speed using the schema SYS. SYS is the only user who's free of policies.

This also guarantees that ALL rows are exported - this is probably important to you :)

We had VPD implemented, the cpu usage seems higher and also the users have longer waits.In general system was very slow.
We drop the VPD, the problem seems to vanish..
We have been trying to do trace/tkprof, elapsed time and what not to figure out the cause of the problem.

Can someone shed a light on the issue or how to figure out the problem?
Thanks

I read from documentation that these two are not compatible (PQ and FGAC) when using Application context. Is there a way to avoid this?

It is NOT a problem with FGAC, sys_context, and PARALLEL query.  Here is an example that shows this works just fine (bigger_table is a table with 37+ million rows in it, empid is NOT null -- has values 1, 2, 3, .... 37+ million.  I used empid+0 to obviate the index I have on empid -- to help PQ get choosen for sure)

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace context my_ctx using my_ctx_proc
  2  /
Context created.

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

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function 
my_security_function( p_schema in varchar2,
  2                   p_object in varchar2 )
  3  return varchar2
  4  as
  5  begin
  6      return 'empid+0 = to_number( sys_context( ''my_ctx'', ''empid'' ) )';
  7  end;
  8  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2      begin
  3              dbms_rls.drop_policy( user, 'BIGGER_TABLE', 'MY_POLICY' );
  4      exception
  5              when others then null;
  6      end;
  7      dbms_rls.add_policy
  8      ( object_schema => user,
  9        object_name => 'BIGGER_TABLE',
 10        policy_name => 'MY_POLICY',
 11        function_schema => user,
 12        policy_function => 'MY_SECURITY_FUNCTION',
 13        statement_types => 'select, insert, update, delete',
 14        update_check => true );
 15  end;
 16  /
PL/SQL procedure successfully completed.


ops$tkyte@ORA817DEV.US.ORACLE.COM> exec my_ctx_proc( 'empid', '1' );
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from bigger_table;

     EMPID     DEPTID
---------- ----------
         1          1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'BIGGER_TABLE'



ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ FULL(bigger_table) PARALLEL(bigger_table,8) */ * from bigger_table
  2  /

     EMPID     DEPTID
---------- ----------
         1          1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2203 Card=62500 Bytes=1625000)
   1    0   TABLE ACCESS* (FULL) OF 'BIGGER_TABLE' (Cost=2203 Card=62500 Bytes=1625000)                        :Q32000


   1 PARALLEL_TO_SERIAL            SELECT /*+ Q32000 NO_EXPAND ROWID(A1) */ A1.
                                   "EMPID",A1."DEPTID" FROM "OPS$TKYTE"."BIGGER
                                   _TABLE" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
                                    A1



Now, if the context values had not been passed down to the PQ slaves here, all of the rows would have come back OR none of the rows would have come back.  Instead only THE right row came back.  

Sys_context is treated like a bind variable in a query -- its value is BOUND in just like any other value would be.  It is true the using SYS_CONTEXT in FUNCTIONS in parallel query is not advisable (as it doesn't work), but in this case the predicate policy function is called long before PQ kicks in -- it rewrites the query which is then sent to the optimizer which then decided to use PQ and rewrote the SYS_CONTEXT calls as bind variables. 

So, it works just fine, security is NOT compromised -- you get the right answer -- with PQ. 

To carry out performance analysis of SQL statements executing within FGAC, is it possible to view the modified statement; that is, after the FGAC predicates have been added?

Also, am I to understand that export should be performed while connected as the sys user in FGAC environments?

Sorry, but you lost me there. Could you be a bit more specific about "printing of trace information using utl_file".

Also, I don't want to push the point, but can you direct me to some references on methods for actually getting the final query text after the FGAC predicates have been added?

Using FGAC we can restrict the rows that a user sees depending on some predicate which we define. However, I want to restrict the values in a column to be visible or not depending on a predicate.

For example, suppose in a world-wide manufacturing system users have responsibilities according to which countries they control.

We might want to let them see parts made anywhere in the world but only see pricing information for parts manufactured in the countries for which they are responsible. So the price column would be populated only for countries they control and null otherwise.

How would you go about implementing such a scheme within FGAC?

Thanks for your ideas Tom. My plan had actually been to use a view with the "price" column wrapped in a decode statement with a function.

When you talk of an optimal solution being to "inline" the function I assume you mean replacing the function by a case statement, which would be possible only if all the data necessary to determine access rights was already contained in the view columns. Correct?

Unfortunately this is not really possible as this information is stored in another table and there may be multiple rows per user, only one of which will be relevant depending on the contents of the view row.

So rather than using a function, which would involve accessing the other table for each row in the view, I was thinking of using context variables to indicate access rights and accessing these in the case statement instead.

That way, the other table is accessed once only in the logon trigger and the context variable(s) set. I expect that accessing the context variable(s) in the case statement will be more efficient than a function which accesses the physical table. Any thoughts or comments?

Which begs another question: in general, what overhead is involved in accessing context variables?

not correct.  with scalar subqueries you can get quite sophisticated:

ops$tkyte@ORA920> create table security_tab
  2  ( username varchar2(30),
  3    empno    number,
  4    primary key(username,empno)
  5  )
  6  organization index
  7  /

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into security_tab
  2  select user, empno from emp where mod(empno,2) = 0;

10 rows created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select empno, ename, SAL the_real_deal,
  2         case when (select username
  3                      from security_tab
  4                     where username = USER
  5                       and empno = EMP.EMPNO) = user
  6              then sal
  7              else null
  8          end and_the_function_says
  9    from emp
 10  /

     EMPNO ENAME      THE_REAL_DEAL AND_THE_FUNCTION_SAYS
---------- ---------- ------------- ---------------------
      7369 SMITH                800
      7499 ALLEN               1600
      7521 WARD                1250
      7566 JONES               2975                  2975
      7654 MARTIN              1250                  1250
      7698 BLAKE               2850                  2850
      7782 CLARK               2450                  2450
      7788 SCOTT               3000                  3000
      7839 KING                5000
      7844 TURNER              1500                  1500
      7876 ADAMS               1100                  1100
      7900 JAMES                950                   950
      7902 FORD                3000                  3000
      7934 MILLER              1300                  1300

14 rows selected.

ops$tkyte@ORA920>


But if you can use SYS_CONTEXT that is penultimate. 

select * from emp where ename = sys_context( 'x','y' )

is the same as:

select * from emp where ename = :x

they are bind variables. 

As you stated, we could also write something like the following with f containing the code from the case statement.

select empno, ename, SAL the_real_deal,
decode (f(user, empno)), 1, sal, null ) fn
from emp

How much more efficient is the case statement implementation? And why?

I would have thought that both implementations would still have to issue the subquery once for each row of the view.

I take my hat off to you sir.

Hi Tom,

quick question : If I reference the same table multiple times within a given SQL statement, and the table has a policy on it, is the policy applied multiple times, or is oracle clever enough to apply it once "across the board"? The event 10730 testing i've done seems to imply the policy gets applied n times.

Thanks in advance

Hi Tom,

In your reply above you stated that one "cannot currently use FGAC to implement column wise security".

I was reading "Oracle 9I Database Summary, An Oracle White Paper" (May 2002) recently wherein the section on Deep Data Protection states that "application logic no longer needs to enforce which rows and columns of a data table are seen by the application, it is automatically and transparently limited by the security policy."

Are they actually refering to some form of FGAC column-wise security here or are they just referring to a combination of FGAC and the normal security privileges?

Do you know of any plans to introduce column-wise security to FGAC in the future?

Thanks.

I was thinking more along the lines of the data in the column rather than the column itself. That is, a particular column is populated only if you have a specific privilege on the row in question.

To carry out performance analysis of SQL statements executing within FGAC, is it possible to view the modified statement(ie.like in TKPROF or V$SQl); that is, after the FGAC predicates have
been added?
Version:8.1.7

I tried Generating TKPROF and tried Shared pool content, i couldnt see the modifed sql.I want to know where exactly oracle will append the modified predicate.

Is the sql statement visible in 9i ? Thanks Tom.

Will this will help in 8.1.7

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

Hi Tom,

I tried to implement FGAC on a table in oracle 8.1.6 using the example that you have posted on this site. I was successful in doing so. Thanks for the wonderful example and explaination. Now I understood how to implement FGAC and what are their advantages. As of now everything is ok. Now I take a export backup of whole database as normal user and not sysdba. It gave me an error when I tried to export the table which had policy on it. The error is given below:

EXP-00079: Data in table "testtable" is protected. Conventional path may only be exporting partial table.

I know that user with sysdba privilege bypass the policies set for any user while backing up. I want to know how oracle internally handles this scenario.

Your comments will be greatly appreciated

Hi Tom,
I executed the example that you gave in your section titled as "Important Caveat" for Example 1 in the following article:
</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

In my testing, the DUMP_T procedure call at the end was always producing rows for both the cases (whereas in your article you mention that the first query will return zero rows).
Is this because of a change in behaviour in Oracle9i (9.2.0.3)?
Below are the results of test runs:

======================================================
KKISHORE @ ORA9203.WORLD> 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( 'myctx', 'x' ) is not null )
7 then
8 return 'x > 0';
9 else
10 return '1=0';
11 end if;
12 end;
13 /

Function created.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> create table t ( x int );

Table created.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> insert into t values ( 1234 );

1 row created.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> begin
2 dbms_rls.add_policy
3 ( object_schema => user, object_name => 'T',
4 policy_name => 'T_POLICY', function_schema => user,
5 policy_function => 'rls_examp', statement_types => 'select' );
6 end;
7 /

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> create or replace procedure set_ctx( p_val in varchar2 )
2 as
3 begin
4 dbms_session.set_context( 'myctx', 'x', p_val );
5 end;
6 /

Procedure created.

KKISHORE @ ORA9203.WORLD> create or replace context myctx using set_ctx;

Context created.

KKISHORE @ ORA9203.WORLD> exec set_ctx( null );

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> select * from t;

no rows selected

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> exec set_ctx( 1 ) ;

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> select * from t;

X
----------
1234

1 row selected.

KKISHORE @ ORA9203.WORLD> create or replace procedure dump_t
2 ( some_input in number default NULL )
3 as
4 begin
5 dbms_output.put_line
6 ( '*** Output from SELECT * FROM T' );
7
8 for x in (select * from t ) loop
9 dbms_output.put_line( x.x );
10 end loop;
11
12
13 if ( some_input is not null )
14 then
15 dbms_output.put_line
16 ( '*** Output from another SELECT * FROM T' );
17
18 for x in (select * from t ) loop
19 dbms_output.put_line( x.x );
20 end loop;
21 end if;
22 end;
23 /

Procedure created.

KKISHORE @ ORA9203.WORLD> set serveroutput on
KKISHORE @ ORA9203.WORLD> exec set_ctx( NULL )

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec dump_t
*** Output from SELECT * FROM T

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec set_ctx( 1 )

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec dump_t( 0 )
*** Output from SELECT * FROM T
1234
*** Output from another SELECT * FROM T
1234

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD>
KKISHORE @ ORA9203.WORLD> alter session set session_cached_cursors = 100 ;

Session altered.

KKISHORE @ ORA9203.WORLD> exec set_ctx( null );

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec dump_t
*** Output from SELECT * FROM T

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec set_ctx( 1 )

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD> exec dump_t( 0 )
*** Output from SELECT * FROM T
1234
*** Output from another SELECT * FROM T
1234

PL/SQL procedure successfully completed.

KKISHORE @ ORA9203.WORLD>

But if the predicate is changing based on, for example, time of the day, the problem persists. It's still not desirable to change the predicates in the middle of the session.

Right?

Hi Tom,

In your reply above to this topic you stated that "selective encryption" can be used "to obscure columns" in a query result.

Can you describe how this works please?

Sorry, I don't understand how this would work. Is it possible to point me to an example?

Hi Tom
Currently, do we have data centers that store multiple
customer data in the same Oracle database using FGAC?
I had two doubts:

1. If only one customer wants to roll back the data (say
there were some errors in the data) to a particular time
how would that be achieved? (would you just export/import
logged in as that customer's user identity or is their
a better way
2. For sys FGAC is not applicable. So is that a concern
for the customer because now we have one person who has
access to multiple customer's (potentially competing)
data and could create problems (e.g. by cross selling
info etc.)

Thanx!

"1) not feasible unless the application does it. tell me, REALLY, how often has this happened in a hosted environment? "

Well, I can imagine that this happens in a non-hosted
environment. Customer wants to rollback because she has
entered a bunch of wrong data for example. I am not a DBA
but I am sure you can tell whether this is a real world
scenario. If it is , then surely this can happen in a
hosted environment as well? And very likely, you will
have to selectively rollback the customer's data only.
2. Well, the difference is that separate dbas can be managing different customers - one guy does not have
access to both databases which probably creates new
security concerns. The above scenario is similar
to giving sys password of multiple legal entities to one
person.Imagine Oracle and MS have their information in their databases. Now for some reason they go with the
CoolHostingServices (cost cutting, say.) Would they
be comfortable knowing that there is a single DBA who
can see information belonging to both companies? Perhaps
not.

Thanx for an awesome site - Love your book and waiting
for the new one!

"1) and in this environment -- do you really do a full PITR for that database for
that person? what about the other dozens of persons work??????????"

That is a good point :) So how do you recover from these
kinds of issues? Has this ever happenned to you - that
you had to rollback the database by one or more incorrect transactions in Oracle?

Also, may be workspaces may help here if this is anticipated perhaps?

"2) if you do not trust your asp, you've got much larger problems. you are
giving them your data for goodness sake. physical separation in an environment
where YOU do not control who works where doesn't buy you anything.

would you really set up an ASP environment where by you had to have separate dba teams for EACH CUSTOMER?
"

Another good point - hmm...the last point is very valid -
by the very nature of ASP, you will have one DBA managing
more than one customer...

Thanx!!!

Hi, Tom,
We are going to use the same policy function for several tables.

For example

the first one:
exec dbms_rls.add_policy
( object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'POLICE_FOR_SCOTT',
function_schema => 'SCOTT',
policy_function => 'RLS_EXAMPLE',
statement_types => 'SELECT',
static_policy => TRUE);

the second one:
exec dbms_rls.add_policy
( object_schema => 'SCOTT',
object_name => 'DEPT',
policy_name => 'POLICE_FOR_SCOTT',
function_schema => 'SCOTT',
policy_function => 'RLS_EXAMPLE',
statement_types => 'SELECT',
static_policy => TRUE);

The function's body:
create or replace function rls_example
(p_schema in varchar2, p_object in varchar2)
return varchar2
as
begin
if p_object='EMP' then
return <predicate for EMP>;
elsif p_object='DEPT' then
return <predicate for DEPT>;
else
<something other predicate>;
end if;
end;

As far as I understood from Oracle DOCs:
1) If I have (..., static_policy => FALSE), I don't have problems describing you in section "Important Caveat" (because of automatic reparsing).
2) If I have (..., static_policy => TRUE), I don't have problems describing you in section "Important Caveat", only if policy function returns the same predicate for the session.

But I'm going to use (..., static_policy => TRUE) to avoid excessive reparsing and executing policy function. And it will be one policy function for many tables. And this function will return the same predicate for the same table during user's session.

Please, any comments. Am I right in my reasoning?
Thanks.

is there a way to attach comments to policies, and or policy functions, or some other way of reference to business logic implemented in them?
it would be very convenient to be able access the business logic attached to a particular object.
thanks for all the good job!

not really.
we don't want to create any additional structure it needs extra resources for maintenance and security issues. it would be handy to have comments attached to either or both policy and associated function. for now we are using convention of placing comments in the security function in a format 'function_name->'...comment text...
then by querying structures all_policies and user_source with protected object owner/name we are able to pull out policy information and function comments.
SELECT
S.TEXT POLICY_COMMENT,
P.OBJECT_OWNER || '.'|| P.OBJECT_NAME OBJECT,
P.PF_OWNER || '.' || P.PACKAGE || '.'
|| P.FUNCTION AS POLICY_FUNCTION,
ENABLE ENABLED,
SEL,
INS,
UPD,
DEL
FROM ALL_POLICIES P,
USER_SOURCE S
WHERE UPPER(P.OBJECT_OWNER) = UPPER(v_OWNER) AND
UPPER(P.OBJECT_NAME) = UPPER(v_ONAME) AND
S.TYPE = 'PACKAGE BODY' AND
S.NAME = P.PACKAGE AND
S.TEXT LIKE '%' || P.FUNCTION || '->%';
note our security functions are all in packages therefore we are looking only for particular type of source code.
it seems a little odd, however comments are stored along with function, so that maintenance and security taken care of.
regarding performance issues, on VPD it always comes down to indexing and normalization.
thanks,
vadim

Tom,
We've implemented FGAC on a secure government project with lots of access restrictions. We've continuously added more and more text to the predicate, and now we're at the point where the predicate is over 4000 characters long. Since the varchar2 return value from a PL/SQL packaged function can be no longer than 4000 chars, the predicate function raises an exception that causes a policy error.

How can we address this limitation? Does Oracle support returning a CLOB from a predicate function? We've considered moving some of the predicate text (queries) into separate functions and making calls to those functions from within the predicate text, but we're concerned about hurting performance. As you can imagine, a predicate that is 4000 chars long already impacts performance.

Any suggestions would be appreciated.

Thanks, Kurt

hi Tom,
how can one implement column-level access ?

anyone can see a any employee info BUT only bosses can see
the salary ?

Thanks

>>In 9i and before, you would use a view to selectively hide the data.
Ha ! Thanks !!
That's exactly what I thought to be the easiest and had built into my web app...almost all parameterized views and use context everywhere. time to buy meself a brew...:)

Tom et al.,

In the article </code> http://asktom.oracle.com/~tkyte/article2/index.html <code>referenced in one of your follow-ups, you include an example predicate function, hr_predicate_pkg.select_function. Line 16 reads, "if ( g_sel_pred is NULL )." Why evaluate g_sel_pred and return it as-is if it's not null? Is there any chance that it would evaluate to anything other than NULL? Can it be persistant between DML statements that force re-execution of the predicate function? I ask because I've seen evidence of exactly that in my predicate function testing. It appears that you are using the convention "g_" for variables that will be "global" and "l_" for those that will be local... how does a variable declared locally in a package body appear "global" to subsequent executions of the same function? Must be some basic PL/SQL behavior knowledge that I'm missing.

SQL> create or replace package body hr_predicate_pkg
  2  as
  3
  4  g_app_ctx constant varchar2(30) default 'Hr_App_Ctx';
  5
  6  g_sel_pred varchar2(1024) default NULL;
  7  g_upd_pred varchar2(1024) default NULL;
  8  g_ins_del_pred varchar2(1024) default NULL;
  9
 10
 11  function select_function( p_schema in varchar2,
 12                    p_object in varchar2 ) return varchar2


the g_ variables are defined outside of the procedures/functions in the package.  They are session persistent, global variables in that package body.

Any variable defined in a package body OUTSIDE of a function/procedure is global to that body and session persistent.

any variable defined in a package SPEC is global to anyone with execute on that package and is session persistent. 

How does policy group differ from policy that we create and attach to a table? Thanks.

Based on your response:

"and in 9i, you can grant the EXTREMELY powerful privilege EXEMPT ACCESS POLICY to any DBA, giving the same bypass ability."

I granted the privilege (EXEMPT ACCESS POLICY) to user system and run an export of another owner's tables with DIRECT=N. I still see EXP-00079 & EXP-00091. Are there other privleges that need to be granted also?
I tested this in 9.2.0.4. DIRECT=Y doesn't generate an error.

When i execute this code , I'm getting the foolowing error.

The following error has occurred:

ORA-06550: line 8, column 58:
PLS-00302: component 'ALL_ROWS' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

What could be the reason.Am I missing something here.
I'm able to add policy using DBMS_RLS.ADD_POLICY to other tables.

BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'emp',
policy_name => 'sp_job',
function_schema => 'scott',
policy_function => 'pf_job',
sec_relevant_cols => 'sal,comm',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

Thanks

No I'm using just 9.2.0.5.0.
So I may to be able to use this feature.Is there any other way to mask columns in this version with out blocking the entire record.

The correct version is
"Oracle9i Enterprise Edition Release 9.2.0.5.0 "

Im trying the protect the base table and there are many screens and reports which uses the base table.So i cannot go for another layer of views.
I'm interested in protecting the base table.
thanks

We have user X owning several tables for which policies are defined. The policies are in various packages owned by X.
I want to exempt access policy for user Y. I granted exempt access policy to Y. Since the DML is thru making calls to packages owned by X, the exempt policy is not working.
I also tried the same by granting dba role to Y after reading one of your follow ups .
We are 9.2.0.4

I tried doing that. It works !
But i am not able to avoid recursive sql that it executes.
Is there a way to avoid it ?

This question might be stupid to ask to you. My security function gives me error "ORA-01722: invalid number", any reason why, here is the security function which I am calling in my dbms_rls.add_policy 

CREATE OR REPLACE FUNCTION By_Office
(p_schema  IN VARCHAR2 DEFAULT NULL,
 p_object  IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
AS
BEGIN

 RETURN ' office_id IN   sys_context(''ctx_sfid'',''office_id'')';

END;
/

My set context procedure build a string of offices a user belong to. A user can belong to one office or more than one offices, for one office I am creating a string of office seperated by comma, ie to say :

SQL> conn stece/welcome1@sfidtest
Connected.
SQL> select sys_context('ctx_sfid','office_id') from dual;

SYS_CONTEXT('CTX_SFID','OFFICE_ID')
--------------------------------------------------------------------------------
2,1


When I try to run a select on my view , I am getting following error:

SQL>  select * from sec_admin.stand_office;
 select * from sec_admin.stand_office
                                    *
ERROR at line 1:
ORA-01722: invalid number

I think my By_Office function does not return the valid predicate. Is there a way to check the predicate or do I have to change my RETURN STRING? 

ops$tkyte@ORA9IR2> /
select * from all_users where user_id in '1,2'
                                         *
ERROR at line 1:
ORA-01722: invalid number


you cannot put a single string in there to represent a "list" in an "in" statement.


you should probably put those values into a session based global temporary table and return:

where column in ( select * from that_gtt )

use dbms_stats to set the table stats on that_gtt so the optimizer knows there are about N rows in there on average...
 

One thing I forgot to mentioned is that my view is as follows:

SQL> desc stand_office;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 OFFICE_ID                                 NOT NULL NUMBER
 FOREST_ID                                 NOT NULL NUMBER
 COMP_ID                                   NOT NULL NUMBER
 STAND_ID                                  NOT NULL NUMBER
 STAND_NAME                                         VARCHAR2(100)

where office_id is a number field. 

Tom,

Your suggestion to use global table for the session would probably not work in my case. I am storing office_ids in the Oracle Directory ( in a seperate container in the OID) and users are enterprise users and users are mapped to different offices. I have a PL/SQL package which set offices in the Application Context. This PL/SQL is being called from a logon trigger when user login to the database.

Could I use dynamic sql in the RETURN statement of my security function "by_office" to build my list of offices ?

I am using local context , i used following syntax to create the application context.

create or replace context sfid_Ctx using PKG_CLIENT_INFO ACCESSED LOCALLY;

Hello Tom,
we implemented FGAC as well, but there is a question I keep asking myself:
The business logic that manages the rights a user has on the data is on the database in the FGAC specific functions.
So if a user can read a row, but not update it, how can the Front end know that *before* the Front end executes the specific update statement?
For example:
1) user A can read row X but not update it.
2) user A has a Form in his Front end, and can load the Form with the data of that row.
3) user A now changes a lot of stuff in the Form (the telephone number is changed, he now has a new address, ...)
4) user A clicks "Save", the update statement is executed, but the Policy exception is thrown.

Can I make the user aware that anything he enters after step 2) can not be saved, without duplicating the FCAG functions code on the Front end side?

Thank you very much Tom, you are really great help.

I am also wrestling with the best way to pass database security to a front-end application, similar to Santoro's question. If we protect the database with real users, and apply row level, and even column level security to our tables: How to let the UI (web app in my case) know that a column is not updateable? Seems like I'll have to repeat the logic application-side.

> it is not that the policy will raise an error, it is that
> 0 rows will be updated.
> you'd have to test if you can update.

This is what I am actually doing.
On the Front end side i do something like

UPDATE table SET table_id=table_id WHERE table_id=the_retrieved_id;

after every SELECT.

if the update returns 0 rows I can not update the row. Looks a bit ugly to me...
Makes me think there has to be a better way to do it.

> tell me, are you "client server" or are you "stateless web
> based"

We are in a client-server environment.

> and what is your lost update protection strategy?

What do you mean with "lost update protection"?
If an update goes wrong we just tell that to the user, if a specific exception is thrown (a check constraint or something like that) we show him an error message ("the birth date is in the future!") to the user and tell him to correct the problem (if he can).

> we can modify that to make it so you can tell if you can
> update or not.

Tom,

I have three tables A, B and C and I want to implement FGAC on C. My application context value is stored in A so I created a view based on A,B and C and I have created the policy on the view and it works OK if I select rows on the view. How can I implement FGAC on the table C itself where C is the child of B and B is the child of A and application context is the PK of A?

I donot see a way to restrict row level access on C. Does this mean I should not give access on C to any user?

Thanks

Ravi

Technically, yes you are right, Application Context is the PK of table A. About the performance, you said I must see it , does this mean that if I have to join several table in subquery and every join table have several thousands /millions of record, VPD policy will degrade the performance?

Thanks

Thanks for the suggestion, however, when I created a predicate and my select uses the parent table, I am getting

ORA-28108: circular security policies detected

When I looked into the metalink, they suggested that this problem occurs when the predicate contains the table and query which are also included in the statement.

So I have created the view on the actual table ( ie C in my case) and then build the policy on the view itself and I was able to solve the problem.

This suggested that I cannot implement my VPD security all the time directly on the table itself.

Either I should create a view or denormalized the table. Is this the right assumption in your view?

Thanks

Hi..
Good to see the move for ' fresh blood '

but
"return more then 1 predicate per session should be avoided at all cost." having said this in your article.?
How does the same can be implemented in a JDBC Connection pool
I have an application where users details are stored in a table. and based on this details I need to code my FGAC.
The end users from the web site will be connecting through my pooled connections which is alive for a long time.
And many users connect thru the same session.
in that case I need to differentiate the users based on there role and user id.
and your solution
"raise_application_error( -20001, ' Role Already Set ' );"
cannot be implemented.

Do you have any links or source/example where I can have a look at and understand how FGAC works in case of connection pools ..? My case my app server has 10 alive connection through out and many users connect through that.
for Each user i need to set the context and get the records accordingly.
Thanks for your help

SCOTT@isro-14-dec>select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

5 rows selected.

SCOTT@isro-14-dec>exec set_approle_gritctx(30);

PL/SQL procedure successfully completed.

SCOTT@isro-14-dec>select * from incident_report;

INCIDENT_ID INCI_DESC INCI_DATE INCI_LOSS INCI_TYPE
----------- ------------------------- --------- --------------- --------------------------------------------------
2 asdf1 14-DEC-04 101 as1
7 asdf6 19-DEC-04 106 as6
12 asdf11 24-DEC-04 111 as11
17 asdf16 29-DEC-04 116 as16
22 asdf21 03-JAN-05 121 as21
27 asdf26 08-JAN-05 126 as26
32 asdf31 13-JAN-05 131 as31
37 asdf36 18-JAN-05 136 as36
42 asdf41 23-JAN-05 141 as41
47 asdf46 28-JAN-05 146 as46
...
20 rows selected.


SCOTT@isro-14-dec>exec set_approle_gritctx(20);

PL/SQL procedure successfully completed.

SCOTT@isro-14-dec>/

INCIDENT_ID INCI_DESC INCI_DATE INCI_LOSS INCI_TYPE
----------- ------------------------- --------- --------------- --------------------------------------------------
2 asdf1 14-DEC-04 101 as1
7 asdf6 19-DEC-04 106 as6
12 asdf11 24-DEC-04 111 as11
17 asdf16 29-DEC-04 116 as16
22 asdf21 03-JAN-05 121 as21
27 asdf26 08-JAN-05 126 as26
32 asdf31 13-JAN-05 131 as31
37 asdf36 18-JAN-05 136 as36
42 asdf41 23-JAN-05 141 as41
47 asdf46 28-JAN-05 146 as46
...
20 rows selected.


SCOTT@isro-14-dec>exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\Documents and Settings\512084>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 14 12:42:29 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SCOTT@isro-14-dec>exec set_approle_gritctx(20);

PL/SQL procedure successfully completed.

SCOTT@isro-14-dec>col INCI_LOSS format a15
SCOTT@isro-14-dec>col INCI_DESC format a25
SCOTT@isro-14-dec>select * from incident_report;

INCIDENT_ID INCI_DESC INCI_DATE INCI_LOSS INCI_TYPE
----------- ------------------------- --------- --------------- --------------------------------------------------
1 asdf0 13-DEC-04 100 as0
6 asdf5 18-DEC-04 105 as5
11 asdf10 23-DEC-04 110 as10
16 asdf15 28-DEC-04 115 as15
21 asdf20 02-JAN-05 120 as20
26 asdf25 07-JAN-05 125 as25
31 asdf30 12-JAN-05 130 as30
36 asdf35 17-JAN-05 135 as35
41 asdf40 22-JAN-05 140 as40
46 asdf45 27-JAN-05 145 as45
51 asdf50 01-FEB-05 150 as50
...
21 rows selected.

SCOTT@isro-14-dec>

That was a mistake in the code which generated the predicate.
I am Sorry to waste your time and space.

hi tom,

i want to introduce FGAC into a new application. therefore i've read some documentation as well as the related chapter of your book. then i've tried to observe the cursor caching issues with table based FGAG (with 9.2.0.6.0 on Win XP) you 've explained there.

i've found my test case to behave a little bit different than described (and don't know why):

ORCL9I.TEST>@d:\testfgac
ORCL9I.TEST>drop table MyTab ;

Tabelle wurde gel÷scht.

ORCL9I.TEST>drop table MyRule ;

Tabelle wurde gel÷scht.

ORCL9I.TEST>drop table MyLog ;

Tabelle wurde gel÷scht.

ORCL9I.TEST>create table MyTab( Num number ) ;

Tabelle wurde angelegt.

ORCL9I.TEST>create table MyRule( Txt varchar2( 50 ) ) ;

Tabelle wurde angelegt.

ORCL9I.TEST>create table MyLog( TS date, Txt varchar2( 50 ) ) ;

Tabelle wurde angelegt.

ORCL9I.TEST>
ORCL9I.TEST>insert into MyTab( Num ) values( 1 ) ;

1 Zeile wurde erstellt.

ORCL9I.TEST>insert into MyRule( Txt ) values( '1=1' ) ;

1 Zeile wurde erstellt.

ORCL9I.TEST>create unique index MyRuleIdx on MyRule( case when 1=1 then 1 end ) ;
2
ORCL9I.TEST>create or replace function MyPolicyFct( p_cSchema varchar2, p_cObject varchar2 )
2 return varchar2
3 is
4 v_cTxt MyRule.Txt%type ;
5 begin
6 begin
7 select Txt into v_cTxt from MyRule ;
8 exception
9 when NO_DATA_FOUND then null ;
10 end ;
11
12 insert into MyLog( TS, Txt ) values( sysdate, v_cTxt ) ;
13 return v_cTxt ;
14 end MyPolicyFct ;
15 /

Funktion wurde erstellt.

ORCL9I.TEST>
ORCL9I.TEST>begin
2 DBMS_RLS.Add_Policy( Object_Schema => 'TEST',
3 Object_Name => 'MyTab',
4 Policy_Name => 'MyTabPolicy',
5 Function_Schema => 'TEST',
6 Policy_Function => 'MyPolicyFct',
7 Statement_Types => 'select, update, delete',
8 Update_Check => TRUE,
9 Enable => TRUE ) ;
10 end ;
11 /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

ORCL9I.TEST>
ORCL9I.TEST>select to_char( TS, 'dd.mm.yyyy hh24:mi:ss' ) TS, Txt from MyLog order by TS ;

Es wurden keine Zeilen ausgewõhlt

ORCL9I.TEST>select * from MyTab ;

NUM
----------
1

ORCL9I.TEST>select to_char( TS, 'dd.mm.yyyy hh24:mi:ss' ) TS, Txt from MyLog order by TS ;

TS TXT
------------------- --------------------------------------------------
29.12.2004 13:28:13 1=1
29.12.2004 13:28:13 1=1

ORCL9I.TEST>
ORCL9I.TEST>update MyRule set Txt = '1=0' ;

1 Zeile wurde aktualisiert.

ORCL9I.TEST>commit ;

Transaktion mit COMMIT abgeschlossen.

ORCL9I.TEST>
ORCL9I.TEST>begin DBMS_Lock.Sleep( 1 ) ; end ;
2 /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

ORCL9I.TEST>select * from MyTab ;

Es wurden keine Zeilen ausgewõhlt

ORCL9I.TEST>select to_char( TS, 'dd.mm.yyyy hh24:mi:ss' ) TS, Txt from MyLog order by TS ;

TS TXT
------------------- --------------------------------------------------
29.12.2004 13:28:13 1=1
29.12.2004 13:28:13 1=1
29.12.2004 13:28:14 1=0
29.12.2004 13:28:14 1=0

ORCL9I.TEST>
ORCL9I.TEST>begin DBMS_Lock.Sleep( 1 ) ; end ;
2 /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

ORCL9I.TEST>select * from MyTab ;

Es wurden keine Zeilen ausgewõhlt

ORCL9I.TEST>select to_char( TS, 'dd.mm.yyyy hh24:mi:ss' ) TS, Txt from MyLog order by TS ;

TS TXT
------------------- --------------------------------------------------
29.12.2004 13:28:13 1=1
29.12.2004 13:28:13 1=1
29.12.2004 13:28:14 1=0
29.12.2004 13:28:14 1=0
29.12.2004 13:28:15 1=0
29.12.2004 13:28:15 1=0

6 Zeilen ausgewõhlt.

ORCL9I.TEST>
ORCL9I.TEST>select name, value from V$Parameter where name like '%cursor%' ;

NAME VALUE
---------------------------------------------------------------- -------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
cursor_space_for_time FALSE
session_cached_cursors 0
cursor_sharing EXACT
open_cursors 300

it seems as if the policy function is called twice per statement call (instead of just once per statement).

could you please have a look at this and tell me what 's wrong?

yes, you wrote in your book that queries get reparsed with 8.1.7+ if FGAC is involved and an associated context gets modified. but didn't you point out there too that cursor caching comes back into play if the predicate is derived from table content?

that was what i couldn't "verify". i expected the query to be executed many times with *unchanged* predicate, since parsing (invocation of policy function) should have taken place only *ONCE* per statement. i've found the policy function to be called *TWICE* per statement *CALL* instead ...

what did i miss or mix?

with this parameter set to TRUE policy function is invoked only once.

but why is it called twice with 'static_policy' left to default FALSE?

am i right to take your "but I didn't see any inserts/modifications? and remember -- things change over time...." as a "FGAC behaviour just changed between 8i and 9i"? with 8i you got the results i expected with 9i, right?

meanwhile i 've achieved the same with parameter 'static_policy'. but one question remains: why is the policy function invoked twice per statement call in case 'static_policy' is left to default FALSE?

so you wouldn't create a TAR for that?

i've read the documentation for 'static_policy' once again (9i):

<quote>
The default is FALSE. If it is set to TRUE, the server assumes that the policy function for the static policy produces the same predicate string for anyone accessing the object, except for SYS or the privilege user who has the EXEMPT ACCESS POLICY privilege.
</quote>

from that one could assume that the policy function is called for each and every statement call with FGAC and 'static_policy' set to FALSE.

but since, on the other hand, policy functions get invoked at parse time does FALSE mean that those statements get PARSED TWICE too (potential serious performance impact with FGAC applied)?

Tom,

Thanks for all of your input. I have a question and I'm not finding any documentation for help. If a table policy is not set for insert, do insert triggers abide by the FGAC policies if the trigger insert or update against the table?

Say on table_1, I place a policy on select, update, and delete (not insert) which checks the value of col_1. col_1 is not populated with the insert. I place an after statement insert trigger to update col_1 as part of the insert transaction. A user bound by FGAC inserts a row into table_1. To my surprise col_1 is populated, but I don't understand why.

The sequence of event to my understanding has been

1. insert into table with col_1 null
2. after statement trigger fires
3. col_1 value is updated. This action should fail since col_1 is null and my current user depends on the value of col_1 for visibility.

Does the absence policies on inserts cause Oracle to ignore FGAC during all insert triggers with regards to the inserting table?

Thanks

Hi

I am reading about VPD and its implementation in Oracle 10g.

I am very very confused between application context and fine grain access control. It seems to me they are the same thing but the documentation seperates them in two different areas, it looks like application context is an extension of FGAC. But it seems to me they are the same,

FGAC uses contexts, policies
Application contexts uses contexts, policies

So what´s the difference between them?!

Hi

Since they are very similar in the sense of hiding data from users which would be more advisable to use? Performance wise?

Hi

in 10g security guide chapter 14 it says this at the beginning

Application context can be used for the following purposes:

Enforce fine-grained access control
....

Doesnt that part make both application context and fgac very similar? In fact the example showing application context shows how we can hide data from users using it.

That´s why I am so confused what is better!

Hi Tom,

Lets say we have a content management system where record level access can be defined on an ad-hoc basis (a record can be noted as accessible to any particular user in no particular pattern).

I have to associate a user (by id, empno, etc.) with a record to allow them access. If the employee identifier is only used to grant access, would performance be better if a Collection were used to identify which employees have access to the record or am I better off creating a many to many association table?

The list of employees who have access to a particular record does not constitute any meaningful attribute of the record (they are not the author lets say) and so will not be used in other queries beyond the VPD predicate.

Thanks,
Rich

I want to integrate FGAC and Encryption to move to take it "to the next level", but I am struggling with a good method. Let's assume we have the following situation.
I have an Oracle 10g Release 2 instance.
I have my employee table emp.
create table emp as(emp_id number,
emp_name varchar2(40),
emp_ssn varchar2(32));
Into my emp table I insert the following row.
insert into emp values(1,'Shawn',encrypt('123-45-6789','MAGICKEY'));

My encrypt function is simply your example for encrypting using dbms_crypto which accepts the string to encrypt, the ssn in this case, and my key.

Now here comes the fun part. When the database administrator executes select * from emp, he should see the encrypted value for emp_ssn. When the employee Shawn executes select * from emp, he should see the unencrypted value for emp_ssn. We have used FGAC in conjunction with application contexts to assign users various security levels when they login to the database. We have used these security levels to control which rows they can see within the database. However, in addition to controling whether or not a user can see a row, we need to control whether they can view certain fields in plain text or simply the encrypted text. I have considered creating a view of emp that would use our application context and a decode or case statement to determine whether to return the encrypted string or not and have users simply query the view. However, I'm not certain if this is the best method to achieve my goal.
Create view statement:
create or replace view emp_vw as select emp_id, emp_name, decode(sys_context('emp_ctx','level'),'5',decrypt(emp_ssn,'MAGICKEY'),emp_ssn) from emp;

Any thoughts or suggestions?

Do you have a small example for transparent data encryption? I've looked around your site some, but I've not found the standard Tom Kyte example. If you do not already have one, can you create one in the next couple of days when you have time and post it?

Thanks.
Shawn

We are in the process of prototyping encryption/data hiding and looking at what we can do and how we might go about setting it up. We did put together a nice prototype using TDE and VPD to hide and encrypt data in Oracle 10.2. However, our app uses WebLogic and WebLogic currently only supports upto Oracle 10.1.
So, would it make sense to create a table, emp, in which we store the emp_ssn encrypted. Then we create a view on emp called emp_vw which calls the decrypt function and displays the ssn in unencrypted format. We use a VPD on emp_vw to control which users can view the unencrypted version of emp_ssn.
Does this sound like a feasible solution in 10.1?

"how do you do the key management, what is the goal behind encrypting in your case?"

The goal is to protect data from theft. We have a large consumer data repository which contains data that must be secured. I am envisioning encryption as a method to protect the data in the case that someone steals backup tapes or copies of OS files.
I am not sure what the end vision of how we will do key management is. I think that others are envisioning a set of encrypt and decrypt routines that are executable by a select few users that "knows" where to go get the key from. Much of this is a work in progress as we gear up to implement better security. If we find we are heading in a bad direction or that we want to head in a different direction, I do not see why those adjustments can't be made.
I'm trying to come up with a good way to do TDE without actually having TDE available to us.

Thanks. That is pretty much what I was thinking, but I wanted to make sure that I wasn't heading into a "just because you can, doesn't mean you should..."

Hi Tom,
How r u?
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



Create Table Unit (Unit_Cd Number(3), Unit_Name Varchar2(10),
Reporting_Unit_Cd Number(3),
Unit_Type Varchar2(3),
Address varchar2(50)
)
/

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (1,'Unit1',NULL,'CP','Unit1Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (10,'Unit10',1,'ACP','Unit10Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (11,'Unit11',10,'PS','Unit11Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (12,'Unit12',10,'PS','Unit12Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (20,'Unit20',1,'ACP','Unit20Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (21,'Unit21',20,'PS','Unit21Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (22,'Unit22',20,'PS','Unit22Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (30,'Unit30',1,'ACP','Unit30Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (31,'Unit31',30,'PS','Unit31Address');

Insert into Unit (Unit_Cd, UNit_Name, Reporting_Unit_cd, Unit_Type, Address)
Values (32,'Unit32',30,'PS','Unit32Address');


Create Table Staff (
Emp_id varchar2(8),
Name varchar2(15),
Unit_Cd Number(3)
)
/

Insert into Staff (Emp_ID,Name,Unit_Cd)
values (100,'Raju',10)
/

Insert into Staff (Emp_ID,Name,Unit_Cd)
values (101,'Ramu',20)
/
Insert into Staff (Emp_ID,Name,Unit_Cd)
values (102,'Rao',11)
/
Insert into Staff (Emp_ID,Name,Unit_Cd)
values (103,'suman',21)
/
Insert into Staff (Emp_ID,Name,Unit_Cd)
values (100,'Kris',22)
/
Insert into Staff (Emp_ID,Name,Unit_Cd)
values (100,'Tom',12)
/


SCOTT@ testdb 30-JAN-06>SELECT * FROM UNIT;

UNIT_CD UNIT_NAME REPORTING_UNIT_CD UNI ADDRESS
---------- ---------- ----------------- --- --------------------------------------------------
10 Unit10 1 ACP Unit10Address
11 Unit11 10 PS Unit11Address
12 Unit12 10 PS Unit12Address
20 Unit20 1 ACP Unit20Address
21 Unit21 20 PS Unit21Address
22 Unit22 20 PS Unit22Address
30 Unit30 1 ACP Unit30Address
31 Unit31 30 PS Unit31Address
32 Unit32 30 PS Unit32Address

9 rows selected.

SCOTT@ testdb 30-JAN-06>SELECT * FROM STAFF;

EMP_ID NAME UNIT_CD
-------- --------------- ----------
100 Raju 10
101 Ramu 20
102 Rao 11
103 suman 21
104 Kris 22
105 Tom 12

6 rows selected.

SCOTT@ testdb 30-JAN-06>create or replace context Unit_Ctx using SET_UNIT
2 /

Context created.

SCOTT@ testdb 30-JAN-06>create or replace function Fun_Sec_Policy
( p_schema in varchar2,
p_object in varchar2 )
return varchar2
as
begin


if ( sys_context( 'Unit_Ctx', 'Unitcd' ) is NOT NULL )
then
return 'unit_cd = sys_context(''Unit_Ctx'',''Unitcd'')';
else
return '';
end if;

end;
/

Function created.

SCOTT@ testdb 30-JAN-06>Create or replace procedure set_unit ( p_empid varchar2)
2 as
3 v_unitcd NUMBER(3);
4 v_unittype VARCHAR2(3);
5
6 begin
7
8 SELECT unit_cd, Unit_type into
9 v_unitcd, v_unittype
10 FROM
11 UNIT
12 WHERE unit_cd =
13 (select unit_cd from staff where emp_id=p_empid);
14
15 dbms_session.set_context( 'Unit_Ctx', 'unitcd', v_unitcd );
16 dbms_session.set_context( 'Unit_Ctx', 'unitype', v_unittype );
17
18 EXCEPTION
19 WHEN No_data_found THEN
20 DBMS_OUTPUT.PUT_LINE('No data found in set unit');
21
22 end;
23 /

Procedure created.

SCOTT@ testdb 30-JAN-06>begin
2 dbms_rls.add_policy
3 ( object_schema => 'SCOTT',
4 object_name => 'UNIT',
5 policy_name => 'Fun_Sec_Policy',
6 function_schema => 'SCOTT',
7 policy_function => 'Fun_Sec_Policy',
8 statement_types => 'select' );
9 end;
10 /

PL/SQL procedure successfully completed.

SCOTT@ testdb 30-JAN-06>Exec set_unit(100);

PL/SQL procedure successfully completed.

SCOTT@ testdb 30-JAN-06>select * from unit;

UNIT_CD UNIT_NAME REPORTING_UNIT_CD UNI ADDRESS
---------- ---------- ----------------- --- --------------------------------------------------
20 Unit20 1 ACP Unit20Address

SCOTT@ testdb 30-JAN-06>Exec set_unit(101);

PL/SQL procedure successfully completed.

SCOTT@ testdb 30-JAN-06>select * from unit;

UNIT_CD UNIT_NAME REPORTING_UNIT_CD UNI ADDRESS
---------- ---------- ----------------- --- --------------------------------------------------
20 Unit20 1 ACP Unit20Address

SCOTT@ testdb 30-JAN-06>Exec set_unit(102);

PL/SQL procedure successfully completed.

SCOTT@ testdb 30-JAN-06>select * from unit;

UNIT_CD UNIT_NAME REPORTING_UNIT_CD UNI ADDRESS
---------- ---------- ----------------- --- --------------------------------------------------
20 Unit20 1 ACP Unit20Address



SCOTT@ testdb 30-JAN-06>desc dba_context
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAMESPACE NOT NULL VARCHAR2(30)
SCHEMA NOT NULL VARCHAR2(30)
PACKAGE NOT NULL VARCHAR2(30)
TYPE VARCHAR2(22)

SCOTT@ testdb 30-JAN-06>select * from dba_context;

NAMESPACE SCHEMA PACKAGE TYPE
------------------------------ ------------------------------ ------------------------------ ----------------------
UNIT_CTX SCOTT SET_UNIT ACCESSED LOCALLY

11 rows selected.

SCOTT@ testdb 30-JAN-06>SELECT * FROM V$CONTEXT;

NAMESPACE ATTRIBUTE
------------------------------ ------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
UNIT_CTX UNITYPE
ACP

UNIT_CTX UNITCD
20


SCOTT@ testdb 30-JAN-06>EXEC SET_UNIT(100);

PL/SQL procedure successfully completed.

SCOTT@ testdb 30-JAN-06>SELECT * FROM V$CONTEXT;

NAMESPACE ATTRIBUTE
------------------------------ ------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
UNIT_CTX UNITYPE
ACP

UNIT_CTX UNITCD
20

Where I did wrong... Why the context is not setting....

Thanxk in Adv.

Sanjeev Vibuthi

Hi Tom

Sorry i did a mistake here... Employee code is unique ... It is 104,105....

Actually my requirement is :

Unit Master
----------------------------
Unit_Cd Number(3),
Unit_Name Varchar2(10),
Reporting_Unit_Cd Number(3),
Unit_Type Varchar2(3),
Address varchar2(50)

Staff Table
Emp_id varchar2(8), PK
Name varchar2(15),
Unit_Cd Number(3) FK to Unit Master

User Master
User ID varchar2(10),
Emp ID varchar2(8)


We have around 1600 Units, Each Unit reports to Other Unit ( like Emp - Manager)...
We have some employees and each employee is having one User id...

If a Employee Login with User ID,
I have to findout his Unit code first, then I have to check Unit type. If unit_type='PS' then
display his Unit Code and Unit Name (where unit_cd=<unit_cd>),
if unit_type<>'PS' then Display the Units reporting to him (where reporting_unit_cd=<unit_cd>)

I know this i can implement with procedures, but i want to use FGAC to implement... can i use FGAC to implement this logic..


Thankx in Adv.

Sanjeev Vibuthi

Tom,

What is your opinion?

HIGH: Oracle PL/SQL Gateway Security Bypass
Affected:
Oracle PL/SQL Gateway present in Oracle Application Server, Oracle HTTP Server and Oracle Internet Application Server

Description:
NGSSoftware has reported that Oracle PL/SQL gateway
contains a flaw in processing user-input that can be exploited to gain access to the restricted packages and procedures. This can result in the backend database server compromise via HTTP (a common configuration).
The flaw can be triggered by a specially crafted query with an unmatched right parenthesis ")".

Status: Oracle has been informed of this high rated flaw but has still not announced patches. NGSSoftware has published workarounds using "mod_rewrite" Apache module that is a part of the Oracle HTTP server.

References:
NGSSoftware Advisory
</code> http://archives.neohapsis.com/archives/bugtraq/2006-01/0397.html

SecurityFocus BID

http://www.securityfocus.com/bid/16384 <code>

Regards,
Dawar

create table dept_access (dept_no varchar2(10),mgr_no varchar2(10));
insert into dept_access values ('AB1000','KING');
insert into dept_access values ('AB2000','KING');
insert into dept_access values ('AB1000','MARTIN');
insert into dept_access values ('AB3000','MARTIN');
commit;

create context dept_tst using tst_dept_ctx ;
create or replace procedure tst_dept_ctx is
l_str varchar2(3200);
l_ret varchar2(3200);
begin
for d_rec in (select dept_no from dept_access )
---where mgr_no=USER)---Changed for this test
loop
l_str := l_str || d_rec.dept_no ||',' ;
end loop ;
if l_str is NULL
then
l_ret := '1=2';
else
l_ret := 'DEPT_NO IN (' || RTRIM(l_str,',')||')';
dbms_session.set_context('DEPT_TST','dep_lst',l_ret);
dbms_output.put_line(l_ret);
end if ;
end;

exec tst_dept_ctx
I get the following output :
DEPT_NO IN (AB1000,AB1000,AB2000,AB1000,AB3000)
My policy fails because it should return --
DEPT_NO IN ('AB1000','AB1000','AB2000','AB1000','AB3000')

What manipulation do I have to do in my string (l_ret := 'DEPT_NO IN (' || RTRIM(l_str,',')||')'; )
to get this output .

THANK YOU !

I have tried my best to give my scripts so that you can guide me.
----
grant connect,resource to tst_rls identified by rls;
grant execute on dbms_rls to tst_rls;
grant execute on dbms_session to tst_rls;
grant create any context to tst_rls;
grant connect to martin identified by martin ;

sqlplus > connect tst_rls/rls
@$ORACLE_HOME/sqlplus/demo/demobld.sql
CREATE TABLE EMP_ACCESS(USERNAME VARCHAR2(30), DEPTNO NUMBER);
insert into emp_access values ('MARTIN',10);
insert into emp_access values ('MARTIN',20);
insert into emp_access values ('KING',10);
insert into emp_access values ('MARTIN',30);
commit;
create context tst_dept using tst_dept_ctx;

create or replace procedure tst_dept_ctx is
l_str varchar2(3200);
l_ret varchar2(3200);
begin
for d_rec in (select deptno from emp_access where username=USER)
loop
l_str := l_str || d_rec.deptno ||',' ;
end loop ;
if l_str is NULL
then
l_ret := '1=2';
else
l_ret := 'DEPTNO IN (' || RTRIM(l_str,',')||')';
dbms_session.set_context('tst_dept','D_LIST',l_ret);
end if ;
end;

create or replace function auth_emps (p_schema_name in varchar2 ,p_object_name in varchar2 )
return varchar2
is
l_deptno number;
l_return_val varchar2(2000);
begin
if (p_schema_name = USER )
then
l_return_val := NULL ;
else
l_return_val := sys_context('tst_dept_ctx', 'D_list');
end if;
return l_return_val;
end;


begin
dbms_rls.add_policy(object_schema=>'TST_RLS',
object_name =>'EMP',
policy_name => 'First_policy',
function_schema=>'TST_RLS',
policy_function=>'AUTH_EMPS',
statement_types=>'SELECT');
end;
/

grant execute on tst_dept_ctx to public ; -- any harm in doing public
grant select on emp to public; -- any harm in doing public
grant select on emp_access to public ; --any harm

as sys
create or replace trigger on_logon
after logon
on database
begin
tst_rls.tst_dept_ctx ;
end;

sqlplus martin/martin

select * from session_context;

NAMESPACE ATTRIBUTE VALUE
TST_DEPT D_LIST DEPTNO IN (10,20,30)
1 row selected

Your comment – “ouch - that would hurt, all of
those unique SQLs. I can imagine this would generate thousands of unique sql
statements over time couldn't it? “

Please help me in understanding and finding a better way .
How can these be converted to bind ?

THANKS !

The code which has been corrected by you was copied from a book --"PL/SQL for Oracle DBA's"

I would appreciate your comment on the grants to public in the above post.

THANKS !

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,

Tom,

We are implementing VPD and are using context sensitive policies which trigger on sys_context('USERENV','CLIENT_IDENTIFIER') (10.2 EI)

The result of VPD is

SQL> update t_alert_al set al_expirationdate=sysdate where st001_id='0001.01' and rownum=1;

0 rows updated.

SQL> update t_alert_al set al_expirationdate=sysdate where st001_id='0002.01' and rownum=1;

1 row updated.

This is the expected result, the user is only allowed to update st001_id='0002.01' records.
However instead off 0 rows updated for the "failed" update we would like to trigger an error message in order to send it to the middle tier. 

I tried with before update triggers > that doesn' t work since the by vdp added predicate assures there is no update going on, because

SQL> update t_alert_al set al_expirationdate=sysdate where st001_id='0001.01' and rownum=1;

gives me in the set autotrace of 10.2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - filter('0001.01'='0002.01')
   5 - access("ST001_ID"='0001.01')

I tried to trap an error from v$vpd_policy ... no succeeded either.

Is there any way ? I start thinking there is none ? Can you confirm me that either give me any hint ?

Regards, thank you
Guy Lambregts 

Hi Tom,

In implementing FGAC as per example in your book, I am facing some hurdles please:

1) I want to set multiple Roles for a UserName. So is it possible for the Context to have multiple rows like this:

HR_APP_CTX.ROLNAME = HR_REP
HR_APP_CTX.USERNAME = BLAKE

HR_APP_CTX.ROLNAME = FM_REP
HR_APP_CTX.USERNAME = BLAKE

In my application, the procedure set_app_role will read a table like this:
create table roles(
username varchar2(30),
rolename varchar2(30));
insert into roles values('BLAKE','EMP');
insert into roles values('BLAKE','HR_REP');
insert into roles values('BLAKE','FM_REP');
insert into roles values('ADAM','EMP');

select * from roles;
USERNAME ROLENAME
------------------------------ -----------
BLAKE EMP
BLAKE HR_REP
BLAKE FM_REP
ADAM EMP

2) The example demonstrates the following:
tkyte@TKYTE816> begin
2 dbms_session.set_context( 'Hr_App_Ctx',
3 'RoleName', 'MGR' );
4 end;
5 /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_SESSION”, line 58
ORA-06512: at line 2

Can't I simply modify the logic of procedure set_app_role (after all it is owned by tkyte schema) and then run this procedure again:
tkyte@TKYTE816> exec set_app_role
The problem seems that we didn't have any security policy on the procedure!

3) I want to use Shared User Accounts. Does that mean that there will be schema tkyte, which will own all tables, and a seperate schema schema_a that will be used by application to connect to?

The 'On logon' trigger should fire ONLY WHEN a user(or application) connects to schema_a. Right? Procedure set_app_role accepts an input parameter p_username (lets suppose we don't want to specify a default value for this). Then I don't want that a user connecting to schema_b should also have to pass a username to the on-logon-trigger. I want to implement FGAC only for the schemas tkyte and schema_a!!

Thanks a lot!

2) The book demonstrates what happens when any other procedure(other than set_app_role) attempts to set our context. It will give error "ORA-01031: insufficient privileges ". This is ok and shows what makes an application context secure and trustworthy.

My question is:
What if a malicious user logs in to tkyte schema(user that owns procedure set_app_role) and simply changes the code in set_app_role(so that the "trusted?" procedure now sets the context arbitrarily i.e. not based on data in emp_base_table) The problem seems that this procedure set_app_role is NOT secure. Although we have not granted any privilage on this procedure to any other schemas(including the database schema the application will use to connect to the database) but the database schema that owns the procedure has ALL access on it. The schema that owns this procedure can change it anytime! and so the said malicious user can log on to tkyte schema and change the procedure set_app_role, as per his convenience. Now when the said malicious user logs in next time, then he has all the access he wanted.

In fact the above applies to the base table emp_base_table as well. We're attaching the security policy to table emp, but the emp_base_table can be changed by the owning schema tkyte anytime. I mean the owner will always have all privilages on its objects (tables, procedures etc) despite using FGAC.

3) I the section 'why use this feature' page 917 in the CD, your book states:
Supports Shared User Accounts
This is a corollary to the preceding section. FGAC does not mandate the use of a logon per user; it
simply facilitates it. Using a feature called an application context, as we will see below, we'll be able to
use FGAC in a 'single account' environment, as may arise in a connection pool with an application
server. Some connection pools mandate that you use a single database account to log in with. FGAC
works well with those environments as well.

My question is :
Does the above mean that there will be database schema tkyte, which will own all tables and to whose tables we'll be attcahing the security policy, and a seperate database schema schema_A (this is what I understand you meant by a 'single account' above) that will be used by application to connect to the database?

Procedure set_app_role accepts an input parameter p_username (lets suppose we don't want to specify a default value for this). The problem is that I don't want that a user connecting to database schema_B should also have to pass a username to the on-logon-trigger. I want to implement FGAC only for the database schemas tkyte and schema_A and Not for database schema_B!!

One solution to my above problem can be that the 'On logon' trigger should fire ONLY WHEN a user(or application) connects to database schema_A. How to achieve that?

Thanks.

Thanks for your reply to the 2nd point.

Can you please address the 3rd point.

Thanks.

Thanks for your very courteous reply !!!!

Ok Tom, I got it. Say I have schema owner_schema. It owns the trusted procedure set_app_role. It also owns tables with which I'll attach the security policy. I'll now create another schema say shared_schema. The application will connect to shared_schema. Shared_schema will have execute priv on the procedure owner_schema.set_app_role. The procedure will be invoked(i.e executed) by shared_schema only.

My only doubt remaining is:
Set_app_role accepts an input parameter say p_role (I use this parameter for setting the context). Set_app_role is executed by on-logon-trigger Also I don't want to specify a default value for p_role.

I want to implement FGAC for owner_schema ONLY, and not for the many other schemas in the same database. i.e. I don't want that a user connecting to schema_X should also have to pass in a value for p_role to the on-logon-trigger. This is because I'm not using the context or security policy for objects owned by schemas other then owner_schema. One solution can be that the 'On logon' trigger should fire ONLY WHEN a user(i.e application) connects to shared_schema. Is that possible please?

Tom

A couple of questions on the limit of 4000 chars in a predicate.

1. The function called by the VPD has now exceeded 4000 chars, so I attempted to return a CLOB instead of a VARCHAR2.

The code compiled but when it executed there seemed to be a bug where at the 4000 character mark the string seemed to duplicate a character that meant the where clause was not valid SQL. (The keyword AND was at the 4000 char mark and became ANN when executed!)

As this does not work can I assume that this is not a valid datatype to return?

Will it be supported in the future?

2. I then followed your advice and created multiple policies.

The question I have here is; can you determine in which order these policies are constructed?

e.g If I have policy 1 and Policy 2 can I get Policy 1 to ALWAYS append before Policy 2?

Thanks in advance.

Tom

Thanks for that, I forgot to mention we are on Release 9.2.0.7.0 (but will hopefully be moving to 10g Release 2 before my code needs to be implemented).

One more quick question.

If I set the long_predicate paramter to TRUE does that mean I need to change the function that the policy calls, i.e. it currently returns a VARCHAR2 (see below) should this now return a CLOB?

FUNCTION policy_function (object_schema IN VARCHAR2
,object_name VARCHAR2) RETURN VARCHAR2

Thanks again
Craig

I am working in 10g and I granted the exempt access policy to user X who is the schema owner used by our applications. All the data is returned through stored procedures that return ref cursors (these are not set to definer rights). When the exempt access policy is granted to user X the row level security fails to work when any user acceesses the stored procedures. Is this a bug, or just something that we all need to be aware of. I have sense removed this privilige from the user as it was only on a dev machine and in our real prod environment the schema owners have little or no priviliges.

--drop user proc_user cascade;
create user proc_user identified by qwer234qweqwewqe;
alter user proc_user default tablespace users;
alter user proc_user quota unlimited on users;

create table proc_user.example(text varchar2(4000));
insert into proc_user.example values('this should never be displayed when the policy is in place');
commit;

create or replace procedure proc_user.get_result(o_result out sys_refcursor)
as
begin
open o_result for
select *
from proc_user.example;
end;
/
create function proc_user.secure_example(obj_schema VARCHAR2, obj_name VARCHAR2)
return varchar2
is
begin
return '1=2';
end;
/
declare
result1 sys_refcursor;
result2 sys_refcursor;

example_row varchar2(4000);

begin
dbms_rls.add_policy(
object_schema => 'PROC_USER',
object_name => 'EXAMPLE',
policy_name => 'PROC_USER_POL1',
function_schema => 'PROC_USER',
policy_function => 'SECURE_EXAMPLE');

proc_user.get_result(result1);
fetch result1 into example_row;

dbms_output.put_line('The next line should be blank:');
dbms_output.put_line(example_row);

execute immediate 'grant exempt access policy to proc_user';

proc_user.get_result(result2);
fetch result2 into example_row;

dbms_output.put_line('Now that the policy owner has the exempt privilige I can bypass the rls policy through his procedure');
dbms_output.put_line(example_row);


end;

Hi,

Can i use a combination of FGAC and application contexts to dynamically and transparently select a view to query from?

There are 3 branches in my app and the rule is you can only see all the data in a branch if you are physically present in that branch(office). The other branches will return a mixture of real data and dummy data.

My current solution is to define 3 views - each view has all the data for one branch and dummy data for the other two.

A stored proc returns a cursor which selects from the appropriate view (the proc reads the app context for ip address of the user and selects from the appropriate view).

Is there a way to make the view selection dynamic and transparent like in FGAC?

Hi Tom,

Good!

I need to restrict to execute a particular statement by a user.

Scanerios is:
-------------

Suppose user A has CREATE TABLE privilege. So he can Create a table and can delete, truncate and drop a table.

right?

But i need to restrict that user from exection of delete, truncate and DROP statement executions.

How can i restrict this one?

I know there is no special privilege for this and know it is possible by inserting a record into a table or executing a procedure to restrict that.

But i do not know exactly that table or procedure.

Please help me for this issue.

I have a question regarding the exception from a function my VPD policy is based on.

It looks like the VPD can only output ORA-28112 and then the DBA needs to look at the udump trace file to get more details about the failing function.

Is there a way to output more descriptive messages to the user directly, when an error occurs?

Thanks.

Thanks Tom.

I actually need to let the user know about special conditions preventing their query to run, before they start calling a DBA. Is there a way to show the user a meaningfull exception other than ORA-28112?

Thanks.

Tom,

I am using POLICY_TYPE=STATIC in my DBMS_RLS.ADD_GROUPED_POLICY procedure call. This way I cache the predicate and the subsequent executions will not execute the policy function each time (unlike DYNAMIC policy type).
Well, now I changed my policy function and the changes aren't reflected in the new executions. Note that my predicate does not change.
Here are the details:

RLS_CONTEXT is set using a database logon trigger. USER attribute is set to SYS_CONTEXT('USERENV','SESSION_USER').

--- Policy package:
CREATE OR REPLACE PACKAGE SET_VPD_POLICY IS
FUNCTION SET_POLICY(SCHEMA_NAME IN VARCHAR2 DEFAULT NULL,
OBJECT_NAME IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY SET_VPD_POLICY IS
FUNCTION SET_POLICY(SCHEMA_NAME IN VARCHAR2 DEFAULT NULL,
OBJECT_NAME IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
VV_PREDICATE VARCHAR2(2000) := '1=2';
BEGIN
DBMS_LOCK.sleep(1);
RETURN 'OWNER = SYS_CONTEXT(''RLS_CONTEXT'',''USER'')';
exception
when others then
VV_PREDICATE := '1=2';
RETURN VV_PREDICATE;
END SET_POLICY;
END SET_VPD_POLICY;

I change the sleep interval from 1 to 3 and the cached predicate never executes my function again. (I check this by doing: set timing on in SQL*Plus). Not only that, but I drop and recreate the policy and it still never reruns the policy function. I would have expected it to run at least first time when it is newly created. Doesn't dropping/recreating policy invalidate the cached predicate?
The only time when it invalidated the predicate was when I bounced the database. :-)!!
Note that I am using 10.2.0.1 in a 2 node RAC environment. If STATIC policy is going to remain in the SGA forever then why would anyone want to use it? Because if I make some change then I would have to bounce the database?


Thanks!

Step 1: Create a context.
CREATE OR REPLACE CONTEXT RLS_CONTEXT USING VPD.SET_VPD_CONTEXT;

Step 2: Create the package for setting the attributes for the above context. Note the administrator user is going to be VPD.
CREATE OR REPLACE PACKAGE SET_VPD_CONTEXT IS
  PROCEDURE SET_CONTEXT;
END;

CREATE OR REPLACE PACKAGE BODY set_vpd_context IS
  PROCEDURE set_context IS
  begin
    dbms_session.set_context(namespace => 'RLS_CONTEXT',
                             attribute => 'USER',
                             value     => sys_context('USERENV', 'SESSION_USER'));
  END set_context;
END;

Step 3:Create a database logon trigger vpd_logon_trigger to set the proper context.
CREATE OR REPLACE TRIGGER vpd_logon_trigger
  after logon on database
begin
  set_vpd_context.set_context;
end;

Step 4: Set up a test table in VPD schema. I am going to have other users query this table T to test my security policy. They should only be able to query the objects owned by them.
CREATE TABLE T
AS
SELECT * FROM DBA_OBJECTS;

CREATE PUBLIC SYNONYM T FOR VPD.T;

GRANT SELECT ON VPD.T TO PUBLIC;

Step 5: Set VPD policy function.
CREATE OR REPLACE PACKAGE SET_VPD_POLICY IS
  FUNCTION SET_POLICY(SCHEMA_NAME IN VARCHAR2 DEFAULT NULL,
                      OBJECT_NAME IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY SET_VPD_POLICY IS
  FUNCTION SET_POLICY(SCHEMA_NAME IN VARCHAR2 DEFAULT NULL,
                      OBJECT_NAME IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
    VV_PREDICATE VARCHAR2(2000) := '1=2';
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN 'OWNER = SYS_CONTEXT(''RLS_CONTEXT'',''USER'')';
  exception
    when others then
      VV_PREDICATE := '1=2';
      RETURN VV_PREDICATE;
  END SET_POLICY;
END SET_VPD_POLICY;

Step 6: Create a policy group and add grouped policy, which is a static policy.
BEGIN
DBMS_RLS.CREATE_POLICY_GROUP ('VPD','T','VPD_POLICY_GROUP');
END;
BEGIN
DBMS_RLS.ADD_GROUPED_POLICY('VPD','T','VPD_POLICY_GROUP','VPD_ROW_POLICY','VPD',
'SET_VPD_POLICY.SET_POLICY','SELECT',policy_type => DBMS_RLS.STATIC);
END;

Step 7: Just for testing purposes I am going to disable the policy and query the table T just to show you
how many total rows exist in table T.
BEGIN
DBMS_RLS.ENABLE_GROUPED_POLICY('VPD','T','VPD_POLICY_GROUP','VPD_ROW_POLICY',FALSE);
END;

Step 8: Query the table T using userid APP
/usr/users/oracle:>sqlplus app/app

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 31 12:26:08 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SET TIMING ON
SQL> SET PAGES 35 LINES 100
SQL> SELECT * FROM SESSION_CONTEXT;

NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------
VALUE
----------------------------------------------------------------------------------------------------
RLS_CONTEXT                    USER
APP


Elapsed: 00:00:00.00
SQL> select count(*) from t;

  COUNT(*)
----------
     52980

Elapsed: 00:00:00.01

Step 9: Now I enable the policy.
BEGIN
DBMS_RLS.ENABLE_GROUPED_POLICY('VPD','T','VPD_POLICY_GROUP','VPD_ROW_POLICY',TRUE);
END;

Step 10: Query the table T.
/usr/users/oracle:>sqlplus app/app

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 31 12:27:43 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SET TIMING ON
SQL> SELECT * FROM SESSION_CONTEXT;

NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------
VALUE
--------------------------------------------------------------------------------
RLS_CONTEXT                    USER
APP


Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
       165

Elapsed: 00:00:02.04
SQL> /

  COUNT(*)
----------
       165

Elapsed: 00:00:00.01
SQL>exit

As you can see there are only 165 objects owned by the user APP out of total 52980 and it took 2 seconds
to run the query the first time. SLEEP(1) in the policy function kicked in. Correct? And then once the predicate is cached,
it took less than a second to come back.

Step 11: Now I change the policy function SET_VPD_POLICY.SET_POLICY. I change the DBMS_LOCK.SLEEP from 1 second to 3 seconds.
DBMS_LOCK.SLEEP(1) to DBMS_LOCK.SLEEP(3).

Step 12: I recompile SET_VPD_POLICY package spec and body both.

Step 13: I log back on as APP and query T.
/usr/users/oracle:>sqlplus app/app

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 31 12:34:30 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SET TIMING ON
SQL> SELECT *
  2  FROM SESSION_CONTEXT;

NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------
VALUE
--------------------------------------------------------------------------------
RLS_CONTEXT                    USER
APP


Elapsed: 00:00:00.01
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
       165

Elapsed: 00:00:00.02
SQL> /

  COUNT(*)
----------
       165

Elapsed: 00:00:00.01
SQL>

As you can see it did not execute the policy function again after I recompiled it. Because now it is showing me the query ran 
in less than a second.

Step 14: Now I drop the policy and delete policy group.
BEGIN
DBMS_RLS.DROP_GROUPED_POLICY('VPD','T','VPD_POLICY_GROUP','VPD_ROW_POLICY');
DBMS_RLS.DELETE_POLICY_GROUP ('VPD','T','VPD_POLICY_GROUP');
END;

Step 15: I recreate the policy group and add the policy.
BEGIN
DBMS_RLS.CREATE_POLICY_GROUP ('VPD','T','VPD_POLICY_GROUP');
DBMS_RLS.ADD_GROUPED_POLICY('VPD','T','VPD_POLICY_GROUP','VPD_ROW_POLICY','VPD',
'SET_VPD_POLICY.SET_POLICY','SELECT',policy_type => DBMS_RLS.STATIC);
END;

Step 16: I query the table T logging in as APP.
/usr/users/oracle:>sqlplus app/app

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 31 12:38:43 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> SET TIMING ON
SQL> SELECT * FROM SESSION_CONTEXT;

NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------
VALUE
--------------------------------------------------------------------------------
RLS_CONTEXT                    USER
APP


Elapsed: 00:00:00.00
SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
       165

Elapsed: 00:00:00.03
SQL>exit

Why is my policy function change not being taken into effect? Why is the elapsed time not showing as 6 seconds? SLEEP(1) showed 2 seconds, so SLEEP(3) should take 6 seconds. Right? If not 6 seconds, at least more than what it took when sleep(1) was in effect. Correct? Not only that, but I made a mistake in the return clause of the policy function on purpose as follows:

RETURN 'OWNER = %$**SYS_CONTEXT(''RLS_CONTEXT'',''USER'')';

Recompiled the policy package, dropped the policy group, recreated it and added the policy. Ran the SELECT COUNT(*) FROM T query. And I did not even get any error. I got the answer back as 165 rows in less than a second. Which shows to me that the policy function is not being executed if the policy_type is STATIC. And this behavior persists until I bounce the instance. I hope I have given enough information.

My question: The predicate is cached in SGA. Is there any way to invalidate the predicate without having to bounce the instance? Or maybe I don't understand what a STATIC policy_type means. I am using 10.2.0.1 on a 2 node RAC.

Help!

Thank you!
 

Hi Tom,

I am going to use FGAC to control access to the account table.

The restriction is based on branches you have access to. Each account belongs to a branch.

So the resulting predicate could be 'branch_id IN ('LON','MIA')' for example.

There are a total of 6 branches max.

I'm planning to load an application context with the branches the user has access to, then read them to construct the predicate.

Not sure the best way to do this from a performance perspective (using bind variables).

I could store a comma-delimited list of branches in the app context and concat it in. Or, i could have seperate name/value pairs in the context and read them individually (using NULL where they don't have access)....

Any advice?

This review, and all others, help me to understand VPD and how to export data from a EE database and import this DMP into a standard database, avoiding ORA-00439: feature not enabled: Fine-grained access control.
Thank you.

Hello!
I have the following problem:
1) I created security policies for SELECT operation on some views (the views are simple and updateable - "SELECT * FROM some_table")
2) Also I have some complex views over these policied views
3) While connecting with the owner of the views I have no problem to select either from the policied views or from the complex ones
4) I granted SELECT privileges on all views (both policied and complex ones) to another user
5) While connecting with the other user I have no problems to select from the policied views
6) But when I try select from one of the complex views I receive the following error:
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to Oracle

The user has SELECT privileges on the views. The views are working properly when executing with their owner. I have no problems with views that selects from non-policied views, only with those that selects from policied view.

I checked the log but it is not understandable for me:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
Process Id: 0x00000598 Thread Id : 0x00000154 Time : Fri Nov 17 17:29:18
Excp. Code: 0xc0000005 Excp. Type: ACCESS_VIO Flags: 0x00000000

------------------- Memory Map of process ----------------
Start Addr-End Addr Type Size ModuleName
0x00310000-0x0031ffff Image 0000065536 C:\ORACLE\ORA92\BIN\ORANMSP.DLL
0x00320000-0x00351fff Image 0000204800 C:\ORACLE\ORA92\BIN\ORAWWG9.DLL
0x00400000-0x02732fff Image 0036909056 C:\ORACLE\ORA92\BIN\ORACLE.EXE
0x02740000-0x029c9fff Image 0002662400 C:\ORACLE\ORA92\BIN\ORAGENERIC9.DLL
...

Where does the problem come from?

Actually I find out the source of the problem.
Here is the situation:
1) I created 2 views:
> CREATE OR REPLACE VIEW view1 AS SELECT * FROM Table1
OK.
> CREATE OR REPLACE VIEW view2 AS SELECT * FROM Table2
OK.
2) Then I set a policy function on these 2 views with the following predicate:
RETURN 'object_id IN (SElECT object_id FROM My_Objects)'
3) Then I granted privileges to some user:
> GRANT SELECT, REFERENCES ON view1 TO some_user
> GRANT SELECT, REFERENCES ON view2 TO some_user
4) Then I connected as some_user and:
> SELECT * FROM view1
OK. Returned 13560 rows.
> SELECT * FROM view2
OK. Returned 389 rows.
> SELECT * FROM view1 v1 INNER JOIN view2 v2 ON (v1.a = v2.a)
ORA-03113: End-of-file on communication channel
5) Even that:
> SELECT * FROM view1 v1 INNER JOIN non_fgac_table v2 ON (v1.a = v2.a)
ORA-03113: End-of-file on communication channel

So the problem is in the JOIN between the an FGAC view and other table/view. Does the user need some additional privileges?

Best regards, Beroetz

> SELECT * FROM view1 v1, view2 v2 WHERE v1.a = v2.a
OK. Returned 1110 rows.

Hi Tom,
I'm playing with VPD and views and I cannot solve following problem:

I have client table:

SQL> desc client

Name        Type         Nullable Default Comments 

----------- ------------ -------- ------- -------- 
ID          NUMBER(38)
FIRST_NAME  VARCHAR2(64)
LAST_NAME   VARCHAR2(64)
LOGIN       VARCHAR2(32)
PASSWORD    RAW(2000)    Y
SALT        RAW(2000)    Y
PHONE_NO    VARCHAR2(16) Y
ADDRESS     VARCHAR2(64)                           
CLIENT_TYPE VARCHAR2(16)                           
VALID       NUMBER(1)             1                

And I have 2 views on this table:

CREATE OR REPLACE VIEW V_ALL_CLIENT AS
SELECT id, first_name, last_name, login, phone_no, address, client_type, valid
FROM   client
WHERE  id = sys_context('bank_context', 'client_id')
WITH CHECK OPTION

grant select on v_all_client to client
grant select on v_all_client to teller
grant update(phone_no) on v_all_client to client
grant update(phone_no, address, valid) on v_all_client to teller


CREATE OR REPLACE VIEW V_TELLER_I_CLIENT AS
SELECT id, first_name, last_name, login, password, salt, phone_no, address, client_type
FROM   client
WHERE  client_type = decode(sys_context('bank_context', 'employee_type'),
                            'CTELLER',
                            'COMMON',
                            'BTELLER',
                            'B2B')
WITH CHECK OPTION

GRANT INSERT ON v_teller_i_client TO teller
grant select on seq_client_id to teller

So, client can see own data and update phone_no - client id is set automatically during client authentication

Teller has to set client_id to see client data, but he should not see password and salt..

Teller can insert new client with initial password and salt.

I want implement this logic using VPD, but the problem is:
How to hide password hash from teller and allow him insert initial password..
sec_relevant_cols does not solve this problem..
oracle does not support:

grant select(col1, col2,...,coln) on table to grantee

defined in SQL 2003 :(

Thank you.

Hi

I was wondering if in 10g we could set several values for a context attribute.

For example I have super users, power users and lower users. Super users can see data form all departments, power users 3 departements and lower users their own department. We would use EMP Table for example.

I have context set as follows:

dbms_session.set_context('department', 'deptno', l_deptno);

In one of your previous post you mentioned that namesppace, attriibite is a pair and nothing else. So does that mean I cannot have this for my super users?

dbms_session.set_context('department', 'deptno', 10);
dbms_session.set_context('department', 'deptno', 20);
dbms_session.set_context('department', 'deptno', 30);
dbms_session.set_context('department', 'deptno', 40);

Thanks

dbms_session.set_context('department', 'deptno001', 10);
dbms_session.set_context('department', 'deptno002', 20);
dbms_session.set_context('department', 'deptno003', 30);
dbms_session.set_context('department', 'deptno003', 40);

where in (select value from session_context where namespace = 'department' and attribute like 'deptno___' )

Hi

That is ok for super users but how about power users where he can only see three of four departments....?

Hi

Super users can see all departments, so 10, 20, 30 and 40.

Power users can only see three departments.

Using the approach

where in (select value from session_context where namespace = 'department' and attribute like
'deptno___' )

That would return all departments so it is fine for Super Uses since they can see all departments, but for power users who can only see 3 of 4 departments this doesnt work no?

ops$tkyte%ORA10GR2> create or replace context DEPARTMENT using my_proc
  2  /

Context created.

ops$tkyte%ORA10GR2> create or replace type my_table as table of number
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure my_proc( p_dept in my_table )
  2  as
  3  begin
  4          dbms_session.clear_all_context( 'DEPARTMENT' );
  5          for i in 1..p_dept.count
  6          loop
  7                  dbms_session.set_context( 'DEPARTMENT', 'DEPTNO'||to_char(i,'fm000'), p_dept(i) );
  8          end loop;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select value from session_context where namespace = 'DEPARTMENT' and attribute like 'DEPTNO___';

VALUE
--------
2
1

ops$tkyte%ORA10GR2> exec my_proc( my_table(1,2) );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select attribute, value from session_context where namespace = 'DEPARTMENT' and attribute like 'DEPTNO___';

ATTRIBUTE  VALUE
---------- --------
DEPTNO002  2
DEPTNO001  1

ops$tkyte%ORA10GR2> exec my_proc( my_table(10,20,30,40) );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select attribute, value from session_context where namespace = 'DEPARTMENT' and attribute like 'DEPTNO___';

ATTRIBUTE  VALUE
---------- --------
DEPTNO003  30
DEPTNO002  20
DEPTNO004  40
DEPTNO001  10

ops$tkyte%ORA10GR2> exec my_proc( my_table(1,2) );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select attribute, value from session_context where namespace = 'DEPARTMENT' and attribute like 'DEPTNO___';

ATTRIBUTE  VALUE
---------- --------
DEPTNO002  2
DEPTNO001  1

In which memory area does application context gets created. Its session specific so does it gets created in UGA.

My problem in related to Fine Grained Access Control / DBMS_RLS.
Let's start with a simple example and elaborate further.
Basic problem:

Let's say we have two tables:

create table PARENT( ID NUMBER not null, PARENT_NAME VARCHAR2(20));
alter table PARENT add constraint PARENT_PK primary key (ID);

create table CHILD( CHILD_ID NUMBER, PARENT_ID NUMBER not null, CHILD_NAME VARCHAR2(20));
alter table CHILD add constraint CHILD_PK primary key (CHILD_ID);
alter table CHILD add constraint CHILD_FK foreign key (PARENT_ID) references PARENT (ID);

I want to convince the database to rewrite a query that looks like this:
A)
SELECT P.PARENT_NAME, C.CHILD_NAME
FROM
CHILD C
INNER JOIN PARENT P
ON C.PARENT_ID = P.ID
WHERE
EXISTS( SELECT 1 FROM PARENT P2 WHERE P2.ID = C.PARENT_ID)

As CHILD will be joined to PARENT for my human eye and mind
it is obvious that the where clause is redundant,
and the query is equivalent to:
B)
SELECT P.PARENT_NAME, C.CHILD_NAME
FROM
CHILD C
INNER JOIN PARENT P
ON C.PARENT_ID = P.ID

Is there any way to convince the parser/optimizer to remove the redundant join?
Why do I have a query like A) and why I cannot rewrite it myself I will explain below.
We develop an application and we want to enforce security on the data at the row level.
We want to give a user the privilege to make reports on the invoices issued for a category of customers.
DBMS_RLS allows the database developer to attach dynamic where clauses to queries.
It does this by rewriting queries like:
"select blabla from employees"
to "select blabla from (select * from employees where <some expression that filters the data according to the user's policy>)"

The filters are attached on a table by table basis, so queries like:

SELECT dept.NAME, empl.NAME
FROM
DEPARTMENT dept
INNER JOIN employees empl

are rewritten to

SELECT dept.NAME, empl.NAME
FROM
(SELECT * FROM DEPARTMENT WHERE <FILTER_DEPT>) dept
INNER JOIN (SELECT * FROM employees WHERE <FILTER_EMP>) empl

So far so good, nothing looks bad, but the devil is in the detail
Back to our reporting problem.
Let's say we have a scenario simpler than life, and I associate the user directly to a customer category by adding a CUST_CAT_ID column to the APP_USER table.
I want restrict the user to see only the data associated to its category (category, customers, invoices, invoice item)
So when he writes "select NAME, ADDRESS from customer" the query is rewritten to
"select NAME, ADDRESS from (SELECT * FROM CUSTOMER WHERE (EXISTS SELECT 1 FROM category join app_user on ... where customer.CUST_CAT_ID = app_user.CUST_CAT_ID and app_user.login = sys_context( 'myctx', 'crtuser' )))"
where get_current_user is some framework function that gives me the current user.

A little complicated, but nothing too scary.


But when I write something like:

SELECT <relevant columns> FROM category cat JOIN customer cust JOIN invoice inv JOIN invoice_item item WHERE <some filters>

...(query totally legitimate for a sales report), this gets expanded to a scary query that looks like this:

SELECT <relevant columns>
FROM
(SELECT * FROM category WHERE (EXISTS SELECT 1 FROM app_user u WHERE u.CUST_CAT_ID = CAT_ID AND u.login = sys_context( 'myctx', 'crtuser' ))) cat
JOIN (SELECT * FROM customer WHERE(EXITS SELECT 1 FROM category JOIN app_user WHERE ...)) cust
JOIN (SELECT * FROM invoice WHERE(EXITS SELECT 1 FROM customer JOIN category JOIN app_user WHERE ...)) inv
JOIN (SELECT * FROM invoice_item WHERE(EXITS SELECT 1 FROM invoice JOIN customer JOIN category JOIN app_user WHERE ...)) item
WHERE
<some filters>

Oops! A query with 4 tables is expanded to a 14 tables query, when all I really need is:

SELECT <relevant columns>
FROM category cat JOIN customer cust JOIN invoice inv JOIN invoice_item item
WHERE <some filters> AND (EXISTS SELECT 1 FROM app_user u WHERE u.CUST_CAT_ID = CAT_ID AND u.login = sys_context( 'myctx', 'crtuser' ))

We have to enforce policies for every table, so the data will be filtered when a reporting tool queries even a single table.
But when multiple tables are joined, filters are redundant and lead to very complicate execution plans.

Let me tell you that we don't use here roles and privileges tables that we must use in a real life scenario.
In a real life scenario we will easily transform the original query in a 20-30 table join (grrrrrrr).
Well, I cannot change the way DBMS_RLS/FGAC works, and also if I choose to use views I cannot write a filtered view for every possible join that a user might create.

All I want is to find out if there is any way to instruct the parser/optimizer, using primary and foreign keys, optimizer parameters hints and other methods, to rewrite the query and eliminate redundant joins.

Of course I can create some materialized views or bitmap join indexes to help me in the process and speed up the query, but using this method in a database that has hundreds or thousands of tables can be a little problem in terms of management and performance.

Also the users have distinct privileges, so we cannot tag all the data with ease. Maintaining up to date tags when user priveleges change or data is added/updated can become a nightmare and performance problem.

------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    63 |     3
|   1 |  NESTED LOOPS                |           |     1 |    63 |     3
|   2 |   NESTED LOOPS SEMI          |           |     1 |    38 |     2
|   3 |    TABLE ACCESS FULL         | CHILD     |     1 |    25 |     2
|*  4 |    INDEX UNIQUE SCAN         | PARENT_PK |     1 |    13 |     0
|   5 |   TABLE ACCESS BY INDEX ROWID| PARENT    |     1 |    25 |     1
|*  6 |    INDEX UNIQUE SCAN         | PARENT_PK |     1 |       |     0
------------------------------------------------------------------------

------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    50 |     3
|   1 |  NESTED LOOPS                |           |       |       |
|   2 |   NESTED LOOPS               |           |     1 |    50 |     3
|   3 |    TABLE ACCESS FULL         | CHILD     |     1 |    25 |     2
|*  4 |    INDEX UNIQUE SCAN         | PARENT_PK |     1 |       |     0
|   5 |   TABLE ACCESS BY INDEX ROWID| PARENT    |     1 |    25 |     1
------------------------------------------------------------------------

-- Here is a small example to better illustrate my problem

DROP TABLE APP_USER;
DROP TABLE INVOICE;
DROP TABLE CUSTOMER;
DROP TABLE CUST_CATEG;

CREATE TABLE CUST_CATEG(CAT_ID NUMBER NOT NULL, CAT_NAME VARCHAR2(100));
ALTER TABLE CUST_CATEG ADD CONSTRAINT CUST_CATEG_PK PRIMARY KEY (CAT_ID);

CREATE TABLE CUSTOMER(CUST_ID NUMBER NOT NULL, CUST_CAT_ID NUMBER NOT NULL, CUST_NAME VARCHAR2(100));
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUST_ID);
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_FK FOREIGN KEY(CUST_CAT_ID) REFERENCES CUST_CATEG(CAT_ID);

CREATE TABLE INVOICE(INV_ID NUMBER NOT NULL, INV_DATE DATE NOT NULL, INV_CUST_ID NUMBER NOT NULL, TOTAL_AMOUNT NUMBER);
ALTER TABLE INVOICE ADD CONSTRAINT INVOICE_PK PRIMARY KEY(INV_ID);
ALTER TABLE INVOICE ADD CONSTRAINT INVOICE_FK FOREIGN KEY(INV_CUST_ID) REFERENCES CUSTOMER(CUST_ID);

CREATE TABLE APP_USER(USER_ID NUMBER NOT NULL, LOGIN VARCHAR2(100) NOT NULL, USER_CAT_ID NUMBER NOT NULL);
ALTER TABLE APP_USER ADD CONSTRAINT APP_USER_PK PRIMARY KEY(USER_ID);
ALTER TABLE APP_USER ADD CONSTRAINT APP_USER_FK FOREIGN KEY(USER_CAT_ID) REFERENCES CUST_CATEG(CAT_ID);

--I will skip the policy functions here. All you need to know is that the policy functions return the following filters:
--
-- for APP_USER: UPPER(LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN')
-- for CUST_CATEG: EXISTS(SELECT 1 FROM APP_USER u WHERE u.USER_CAT_ID = CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
-- for CUSTOMER: EXISTS(SELECT 1 FROM APP_USER u WHERE u.USER_CAT_ID = CUST_CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
-- for INVOICE: EXISTS(SELECT 1 FROM CUSTOMER c JOIN APP_USER u ON C.CUST_CAT_ID = u.USER_CAT_ID WHERE c.CUST_ID = INV_CUST_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
--
-- this means:
-- for APP_USER : I can see only myself
-- for CUST_CATEG: I can see only my associated category
-- for CUSTOMER: I can see only the customers that are in my associated category
-- for INVOICE: I can see only the invoices for customers that are in my associated category


-- Now a simple query like this:

SELECT cat.CAT_NAME, SUM(inv.TOTAL_AMOUNT) TOTAL_SALES
FROM
CUST_CATEG cat
INNER JOIN CUSTOMER cust
ON cust.CUST_CAT_ID = cat.CAT_ID
INNER JOIN INVOICE inv
ON inv.INV_CUST_ID = cust.CUST_ID
GROUP BY
cat.CAT_NAME;

-- Will be rewrited to someting like this (you may see that at least the filter for invoice is redundant):

SELECT cat.CAT_NAME, SUM(inv.TOTAL_AMOUNT) TOTAL_SALES
FROM
( SELECT * FROM CUST_CATEG
WHERE EXISTS(SELECT 1 FROM APP_USER u WHERE u.USER_CAT_ID = CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
) cat
INNER JOIN
( SELECT * FROM CUSTOMER
WHERE EXISTS(SELECT 1 FROM APP_USER u WHERE u.USER_CAT_ID = CUST_CAT_ID AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
)cust
ON cust.CUST_CAT_ID = cat.CAT_ID
INNER JOIN
( SELECT * FROM INVOICE
WHERE EXISTS(
SELECT 1
FROM
CUSTOMER c
JOIN APP_USER u
ON C.CUST_CAT_ID = u.USER_CAT_ID
WHERE
c.CUST_ID = INV_CUST_ID
AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN'))
)inv
ON inv.INV_CUST_ID = cust.CUST_ID
GROUP BY
cat.CAT_NAME;

-- I really need to enforce these filters since the user may issue queries like:
SELECT * FROM INVOICE
-- which really needs to be rewrited to
SELECT * FROM (
SELECT * FROM INVOICE
WHERE
EXISTS(
SELECT 1
FROM
CUSTOMER c
JOIN APP_USER u
ON C.CUST_CAT_ID = u.USER_CAT_ID
WHERE
c.CUST_ID = INV_CUST_ID
AND UPPER(u.LOGIN) = SYS_CONTEXT('MY_APP_CONTEXT','CRT_LOGIN')
)
)

Hi Tom :), thank you a lot for taking your time to answer our questions and being so patient.
Unfortunatelly I still have problems.

I downloaded 11g and installed on my machine (DELL desktop, Windows, 2GB RAM, 200GB HDD)

I created a test schema and created the tables using the scripts in the previous post.
Then added data with the following scripts:

INSERT INTO CUST_CATEG(CAT_ID, CAT_NAME)
SELECT ROWNUM, 'CAT' || ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11;

INSERT INTO APP_USER(USER_ID, LOGIN, USER_CAT_ID)
SELECT ROWNUM, 'U' || ROWNUM, ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11;

INSERT INTO CUSTOMER(CUST_ID,CUST_CAT_ID,CUST_NAME)
SELECT cat.CAT_ID*100+n.NR, cat.CAT_ID, 'CUSTOMER '||(cat.CAT_ID*100+n.NR)
FROM CUST_CATEG cat, (SELECT ROWNUM NR FROM ALL_OBJECTS WHERE ROWNUM < 100) n;

INSERT INTO INVOICE(INV_ID,INV_DATE,INV_CUST_ID,TOTAL_AMOUNT)
SELECT c.CUST_ID*100+n.NR, SYSDATE - dbms_random.VALUE * 100, c.CUST_ID, dbms_random.VALUE*100
FROM CUSTOMER c, (SELECT ROWNUM NR FROM ALL_OBJECTS WHERE ROWNUM < 100) n;

COMMIT;


Then analyzed the schema using:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME => USER, ESTIMATE_PERCENT => 20,
METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
CASCADE => TRUE
);
END;
/

Now a simple query like:

SELECT
C.CUST_NAME, inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON c.CUST_ID = inv.INV_CUST_ID
WHERE
EXISTS (SELECT 1 FROM customer c2 WHERE c2.CUST_ID = inv.INV_CUST_ID)

is correctly rewrited, but something a little different like

SELECT /*+ CURSOR_SHARING_EXACT */
inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON c.CUST_ID = inv.INV_CUST_ID
JOIN customer c2 ON c2.CUST_ID = inv.INV_CUST_ID ---- totally redundant
WHERE
c.CUST_ID = 626
AND c2.CUST_ID = 626 ---- totally redundant

doesn't work, let alone a more complex query like

SELECT /*+ CURSOR_SHARING_EXACT */
inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON inv.inv_cust_id = c.cust_id
JOIN cust_categ cat ON cat.CAT_ID = c.CUST_CAT_ID
JOIN customer c2 ON c2.CUST_ID = inv.INV_CUST_ID ---- totally redundant
JOIN cust_categ cat2 ON cat2.CAT_ID = c2.CUST_CAT_ID ---- totally redundant
WHERE
cat.CAT_ID = 10
AND cat2.CAT_ID = 10 ---- totally redundant

Even if "AND cat2.CAT_ID = 10" is removed there is still redundancy in the execution plan.

I used CURSOR_SHARING_EXACT because I thought that literals may be replaced by binds and this will change the meaning and remove redundancy.

Am I missing something obvious?

Sorry Tom, probably you missed "I created a test schema and created the tables using the scripts in the previous post."

Here are all the scripts again, so you don't have to search in the previous posts.
Create user:

CREATE USER DRAGOS_TEST
DEFAULT TABLESPACE DRAGOS_TBL
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
GRANT CONNECT TO DRAGOS_TEST;
GRANT RESOURCE TO DRAGOS_TEST;
GRANT QUERY REWRITE TO DRAGOS_TEST;
GRANT UNLIMITED TABLESPACE TO DRAGOS_TEST;

Create tables:

CREATE TABLE CUST_CATEG(CAT_ID NUMBER NOT NULL, CAT_NAME VARCHAR2(100));
ALTER TABLE CUST_CATEG ADD CONSTRAINT CUST_CATEG_PK PRIMARY KEY (CAT_ID);

CREATE TABLE CUSTOMER(CUST_ID NUMBER NOT NULL, CUST_CAT_ID NUMBER NOT NULL, CUST_NAME
VARCHAR2(100));
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUST_ID);
ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_FK FOREIGN KEY(CUST_CAT_ID) REFERENCES
CUST_CATEG(CAT_ID);

CREATE TABLE INVOICE(INV_ID NUMBER NOT NULL, INV_DATE DATE NOT NULL, INV_CUST_ID NUMBER NOT NULL,
TOTAL_AMOUNT NUMBER);
ALTER TABLE INVOICE ADD CONSTRAINT INVOICE_PK PRIMARY KEY(INV_ID);
ALTER TABLE INVOICE ADD CONSTRAINT INVOICE_FK FOREIGN KEY(INV_CUST_ID) REFERENCES
CUSTOMER(CUST_ID);

CREATE TABLE APP_USER(USER_ID NUMBER NOT NULL, LOGIN VARCHAR2(100) NOT NULL, USER_CAT_ID NUMBER NOT NULL);
ALTER TABLE APP_USER ADD CONSTRAINT APP_USER_PK PRIMARY KEY(USER_ID);
ALTER TABLE APP_USER ADD CONSTRAINT APP_USER_FK FOREIGN KEY(USER_CAT_ID) REFERENCES
CUST_CATEG(CAT_ID);

Add data:

INSERT INTO CUST_CATEG(CAT_ID, CAT_NAME)
SELECT ROWNUM, 'CAT' || ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11;

INSERT INTO APP_USER(USER_ID, LOGIN, USER_CAT_ID)
SELECT ROWNUM, 'U' || ROWNUM, ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11;

INSERT INTO CUSTOMER(CUST_ID,CUST_CAT_ID,CUST_NAME)
SELECT cat.CAT_ID*100+n.NR, cat.CAT_ID, 'CUSTOMER '||(cat.CAT_ID*100+n.NR)
FROM CUST_CATEG cat, (SELECT ROWNUM NR FROM ALL_OBJECTS WHERE ROWNUM < 100) n;

INSERT INTO INVOICE(INV_ID,INV_DATE,INV_CUST_ID,TOTAL_AMOUNT)
SELECT c.CUST_ID*100+n.NR, SYSDATE - dbms_random.VALUE * 100, c.CUST_ID, dbms_random.VALUE*100
FROM CUSTOMER c, (SELECT ROWNUM NR FROM ALL_OBJECTS WHERE ROWNUM < 100) n;

COMMIT;

Then analyzed the schema using:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME => USER, ESTIMATE_PERCENT => 20,
METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
CASCADE => TRUE
);
END;
/

Now a simple query like:

SELECT
C.CUST_NAME, inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON c.CUST_ID = inv.INV_CUST_ID
WHERE
EXISTS (SELECT 1 FROM customer c2 WHERE c2.CUST_ID = inv.INV_CUST_ID)

is correctly rewrited, but something a little different like

SELECT /*+ CURSOR_SHARING_EXACT */
inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON c.CUST_ID = inv.INV_CUST_ID
JOIN customer c2 ON c2.CUST_ID = inv.INV_CUST_ID ---- totally redundant
WHERE
c.CUST_ID = 626
AND c2.CUST_ID = 626 ---- totally redundant

doesn't work (redundancy is not eliminated from the plan), let alone a more complex query like

SELECT /*+ CURSOR_SHARING_EXACT */
inv.INV_ID, inv.INV_DATE, inv.total_amount
FROM
invoice inv
JOIN customer c ON inv.inv_cust_id = c.cust_id
JOIN cust_categ cat ON cat.CAT_ID = c.CUST_CAT_ID
JOIN customer c2 ON c2.CUST_ID = inv.INV_CUST_ID ---- totally redundant
JOIN cust_categ cat2 ON cat2.CAT_ID = c2.CUST_CAT_ID ---- totally redundant
WHERE
cat.CAT_ID = 10
AND cat2.CAT_ID = 10 ---- totally redundant

Even if "AND cat2.CAT_ID = 10" is removed there is still redundancy in the execution plan.

I used CURSOR_SHARING_EXACT because I thought that literals may be replaced by binds and this will
change the meaning and remove redundancy.

Am I missing something obvious?

ops$tkyte%ORA11GR1> SELECT /*+ CURSOR_SHARING_EXACT */
  2    inv.INV_ID, inv.INV_DATE, inv.total_amount
  3  FROM
  4    invoice inv
  5    JOIN customer c ON c.CUST_ID = inv.INV_CUST_ID
  6    JOIN customer c2 ON c2.CUST_ID = inv.INV_CUST_ID       ---- totally redundant
  7  WHERE
  8    inv.inv_cust_id = 626
  9  --  c.CUST_ID = 626
 10   -- AND c2.CUST_ID = 626
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 742750370

------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    48 |     2   (0)| 00:00
|*  1 |  TABLE ACCESS FULL| INVOICE |     1 |    48 |     2   (0)| 00:00
------------------------------------------------------------------------

Hello Tom, this thread is awesome and I keep coming back to read all new comments since 2004!

We use the VPD feature extensively, and as you guessed, we are now developing a web application on top of the PL/SQL business logic used by our "old" client-server app.

As it is usual for web applications, we have a list (say 100 rows) on a page, and 3 buttons on the left of each row: edit, show and delete.

Since we used VPD, on the web app we just need to SELECT * FROM TABLE and the app gets only the rows the current user is allowed to see. Great! No duplication of logic and all works as it worked on the "old" app.

But how can we use the VPD logic to decide if we should show the edit or delete buttons? The user may be allowed to see the current row, but there might be some rows he is not allowed to edit. All this logic is already coded in the VPD functions, is there some straight forward way to get this info from oracle?

A virtual column like "can_update" true/false would be great, but there is nothing like that in the docs.

What would you suggest?

rajesh@10GR2> drop table emp purge;

Table dropped.

Elapsed: 00:00:00.54
rajesh@10GR2> create table emp as select * from scott.emp;

Table created.

Elapsed: 00:00:00.29
rajesh@10GR2>
rajesh@10GR2> create or replace function f (p_schema in varchar2,p_object in varchar2)
  2  return varchar2
  3  as
  4  begin
  5     dbms_output.put_line ( ' p_schema = '||p_schema);
  6     dbms_output.put_line ( ' p_object = '||p_object);
  7     if (user ='SCOTT') then
  8       return '';
  9     else
 10             return '1=0';
 11     end if;
 12  end;
 13  /

Function created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_rls.add_policy(object_schema=>user,
  3     object_name =>'EMP',
  4     policy_name =>'EMP_SELECT_PLCY',
  5     function_schema => USER,
  6     policy_function => 'F',
  7     statement_types => 'SELECT,INSERT,UPDATE,DELETE',
  8     update_check => TRUE,
  9     enable => TRUE);
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from emp;

no rows selected

 p_schema = RAJESH
 p_object = EMP
 p_schema = RAJESH
 p_object = EMP

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2>

Tom:

1) while parsing the query internally it will be re-written using SELECT * FROM ( SELECT * FROM T ) WHERE <predicate returned by policy function>. since the query will be parsed only once before its execution.So, Why the function named 'F' is called twice while executing the query SELECT * FROM EMP ?

This table may help clarify when the policy functions are executed:

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/vpd.htm#CIHICBIE

The default policy_type is currently DYNAMIC, which states:

Policy function re-executes every time a policy-protected database object is accessed.

rajesh@10GR2> create or replace procedure prc_test_ctx(p_username in varchar2 default
  2                     sys_context('userenv','session_user') )
  3  as
  4     l_ctx varchar2(20) default 'hr_app_ctx';
  5     l_empno emp.empno%type;
  6     l_count number;
  7  begin
  8     dbms_session.set_context(l_ctx,'username',p_username);
  9
 10     begin
 11             select empno
 12             into l_empno
 13             from emp
 14             where ename = p_username;
 15
 16             dbms_session.set_context(l_ctx,'empno',l_empno);
 17
 18             exception
 19                     when no_data_found then
 20                             null;
 21     end;
 22
 23     select count(*)
 24     into l_count
 25     from hr_reps
 26     where username = p_username;
 27
 28     if l_count <> 0 then
 29             dbms_session.set_context(l_ctx,'ROLE','HR_REPS');
 30     else
 31             select count(*)
 32             into l_count
 33             from emp
 34             where mgr = p_username;
 35
 36             if (l_count > 0) then
 37                     dbms_session.set_context(l_ctx,'ROLE','MGR');
 38             else
 39                     dbms_session.set_context(l_ctx,'ROLE','EMPLOYEE');
 40             end if;
 41     end if;
 42  end;
 43  /

Procedure created.

Elapsed: 00:00:00.10
rajesh@10GR2> create or replace context hr_app_ctx using prc_test_ctx;

Context created.

Elapsed: 00:00:00.04
rajesh@10GR2> create or replace trigger emp_logon_trig
  2  after logon on database
  3  begin
  4     prc_test_ctx;
  5  end;
  6  /

Trigger created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> discon
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rajesh@10GR2> connect rajesh@ORA10GR2
Enter password:
Connected.
rajesh@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- --------------------
HR_APP_CTX           USERNAME             RAJESH

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> exec prc_test_ctx(null);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- --------------------
HR_APP_CTX           USERNAME
HR_APP_CTX           ROLE                 EMPLOYEE

Elapsed: 00:00:00.03
rajesh@10GR2> select * from hr_reps;

USERNAME                           DEPTNO
------------------------------ ----------
KING                                   10
BLAKE                                  20

Elapsed: 00:00:00.01

Tom:

You see this, the results of session_context is not set correctly using logon Triggers but when executed manually the Procedure prc_test_ctx Application contexts are set properly. please help me what i am doing wrong here?

 43  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA11GR2> show errors
Errors for PROCEDURE PRC_TEST_CTX:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/12     PL/SQL: Item ignored
5/12     PLS-00201: identifier 'EMP.EMPNO' must be declared
11/12    PL/SQL: SQL Statement ignored
13/17    PL/SQL: ORA-00942: table or view does not exist
16/12    PL/SQL: Statement ignored
16/51    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

23/4     PL/SQL: SQL Statement ignored
25/9     PL/SQL: ORA-00942: table or view does not exist
31/12    PL/SQL: SQL Statement ignored
33/17    PL/SQL: ORA-00942: table or view does not exist

Sorry to bother you Tom, The problem is with my code piece fixed it and its working fine now.

rajesh@10GR2> create or replace procedure prc_test_ctx(p_username in varchar2 default
  2                     sys_context('userenv','session_user') )
  3  as
  4     l_ctx varchar2(20) default 'hr_app_ctx';
  5     l_empno emp_base_table.empno%type;
  6     l_count number;
  7  begin
  8     dbms_session.set_context(l_ctx,'username',p_username);
  9
 10     begin
 11             select empno
 12             into l_empno
 13             from emp_base_table
 14             where ename = p_username;
 15
 16             dbms_session.set_context(l_ctx,'empno',l_empno);
 17
 18             exception
 19                     when no_data_found then
 20                             null;
 21     end;
 22
 23     select count(*)
 24     into l_count
 25     from hr_reps
 26     where username = p_username;
 27
 28     if l_count <> 0 then
 29             dbms_session.set_context(l_ctx,'ROLE','HR_REPS');
 30     else
 31             select count(*)
 32             into l_count
 33             from emp_base_table
 34             where mgr = l_empno;
 35
 36             if (l_count > 0) then
 37                     dbms_session.set_context(l_ctx,'ROLE','MGR');
 38             else
 39                     dbms_session.set_context(l_ctx,'ROLE','EMPLOYEE');
 40             end if;
 41     end if;
 42  end;
 43  /

Procedure created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> create or replace context hr_app_ctx using prc_test_ctx;

Context created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> create or replace trigger emp_logon_trig
  2  after logon on database
  3  begin
  4     prc_test_ctx;
  5  end;
  6  /

Trigger created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> discon
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rajesh@10GR2> connect rajesh/****@ORA10GR2
Connected.
rajesh@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- --------------------
HR_APP_CTX           USERNAME             RAJESH
HR_APP_CTX           ROLE                 EMPLOYEE

Elapsed: 00:00:00.01
rajesh@10GR2>

 10     begin
 11             select empno
 12             into l_empno
 13             from emp_base_table
 14             where ename = p_username;
 15
 16             dbms_session.set_context(l_ctx,'empno',l_empno);
 17
 18             exception
 19                     when no_data_found then
 20                             null;
 21     end;

 23     select count(*)
 24     into l_count
 25     from hr_reps
 26     where username = p_username;
 27
 28     if l_count <> 0 then
 29             dbms_session.set_context(l_ctx,'ROLE','HR_REPS');
 30     else

begin
    select * into l_rec from hr_reps where username = p_username;
    dbms_session.set_context( l_ctx, 'role', 'hr_reps' );
exception
    when no_data_found
          begin
            select * into l_rec2 from emp_base_table where mgr = l_empno;
            dbms_session.set_context( l_ctx, 'role', 'mgr' );
          exception
              when no_data_found then
                   dbms_session.set_context( l_ctx, 'role', 'employee' );
          end;



exception
   WHEN OTHERS THEN /* Yes, I coded that */
         dbms_session.set_context( l_ctx, 'empno', null );
         dbms_session.set_context( l_ctx, 'role', null );
         RAISE;

Tom:

The problem is at line no 34.

34 where mgr = p_username;

Where I compared Number column with Varchar2 variable, sorry that was my mistake. Really sorry.

but it doesn't cover "it is executed more than once", the crux of the question. The policy function is executed during parse, during execute and sometimes during the fetch phase as well. For a *single* statement.

By default, yes. But by altering the policy_type, you can have it execute just once. Consider:

create or replace function f (p_schema in varchar2,p_object in varchar2)
return varchar2
as
begin
 dbms_output.put_line ( ' p_schema = '||p_schema);
 dbms_output.put_line ( ' p_object = '||p_object);
 if (user ='SCOTT') then
  return '';
 else
  return '1=0';
 end if;
end;
/


Function created.


begin
 dbms_rls.add_policy(object_schema=>user,
 object_name =>'EMP',
 policy_name =>'EMP_SELECT_PLCY',
 function_schema => USER,
 policy_function => 'F',
 statement_types => 'SELECT,INSERT,UPDATE,DELETE',
 update_check => TRUE,
 enable => TRUE,
 POLICY_TYPE => dbms_rls.STATIC); -- <<=== ADDED
end;
/

PL/SQL procedure successfully completed.



FOO@dg1> select * from emp;

no rows selected

p_schema = FOO
p_object = EMP
FOO@dg1> select * from emp;

no rows selected

FOO@dg1> select * from emp;

no rows selected

FOO@dg1> select * from emp;

no rows selected

FOO@dg1>

It is just executed once in this case, is it not?

rajesh@10GR2>  begin
  2     dbms_rls.add_policy( object_schema=>user,
  3             object_name=>'EMP',
  4             policy_name=>'EMP_CHK_PLCY',
  5             function_schema=>USER,
  6             policy_function=>'fnc_select',
  7             statement_types=>'SELECT');
  8   end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
rajesh@10GR2>  create or replace function fnc_select(p_schema in varchar2,p_object in varchar2)
  2   return varchar2
  3   as
  4   begin
  5     if sys_context('hr_app_ctx','ROLE') = 'EMPLOYEE' then
  6             return ' empno =  sys_context(''hr_app_ctx'',''empno'') ';
  7     elsif sys_context('hr_app_ctx','ROLE') = 'MGR' then
  8             return ' empno in ( select empno from emp start with empno =  sys_context(''hr_app_ctx'',''empno'') connect by prior mgr = empno ';
  9     end if;
 10   end;
 11  /

Function created.

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- --------------------
HR_APP_CTX           USERNAME             RAJESH
HR_APP_CTX           ROLE                 EMPLOYEE

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> select * from emp;

no rows selected

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> connect scott/tiger@ORA10GR2
Connected.
scott@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- ------
HR_APP_CTX           USERNAME             SCOTT
HR_APP_CTX           ROLE                 MGR
HR_APP_CTX           EMPNO                7788

scott@10GR2> select * from rajesh.emp;
select * from rajesh.emp
                     *
ERROR at line 1:
ORA-28113: policy predicate has error


Elapsed: 00:00:00.28

scott@10GR2> select rtrim(c.value,'/') || '/' || d.instance_name ||
  2  '_ora_' || ltrim(to_char(a.spid)) || '.trc'
  3  from v$process a, v$session b, v$parameter c, v$instance d
  4  where a.addr = b.paddr
  5  and b.audsid = sys_context( 'userenv', 'sessionid')
  6  and c.name = 'user_dump_dest';

RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'
-----------------------------------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP/10GR2_ora_708.trc

Elapsed: 00:00:00.09

Windows thread id: 708, image: ORACLE.EXE (SHAD)


*** ACTION NAME:() 2010-11-25 13:33:18.929
*** MODULE NAME:(SQL*Plus) 2010-11-25 13:33:18.929
*** SERVICE NAME:(SYS$USERS) 2010-11-25 13:33:18.929
*** SESSION ID:(146.2816) 2010-11-25 13:33:18.929
*** 2010-11-25 13:33:18.929
-------------------------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : RAJESH.EMP
Policy name    : EMP_CHK_PLCY
Policy function: RAJESH.FNC_SELECT
RLS predicate  :
 empno in ( select empno from emp start with empno =  sys_context('hr_app_ctx','empno') connect by prior mgr = empno )
ORA-28108: circular security policies detected

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
scott@10GR2>
scott@10GR2> SELECT *
  2  FROM rajesh.emp_base_table
  3  WHERE empno IN
  4    (SELECT empno
  5    FROM emp
  6      START WITH empno     = 7788
  7      CONNECT BY prior mgr = empno
  8    )
  9  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

Elapsed: 00:00:00.01
scott@10GR2>

Tom:

rajesh.emp is a view created on base table rajesh.emp_base_table, when executing the query outside the policy returns 3 rows but when executing from policy function gives ORA-28108: circular security policies detected. please help me to understand and resolve this.

rajesh@10GR2> create table emp_base_table as select * from scott.emp;

Table created.

Elapsed: 00:00:00.23
rajesh@10GR2> create table dept as select * from scott.dept;

Table created.

Elapsed: 00:00:00.06
rajesh@10GR2> create or replace view emp as select * from emp_base_table;

View created.

Elapsed: 00:00:00.06
rajesh@10GR2> create table hr_reps(ename varchar2(20),deptno number);

Table created.

Elapsed: 00:00:00.03
rajesh@10GR2> insert into hr_reps values ('BLAKE',30);

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> insert into hr_reps values ('CLARK',10);

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> create or replace procedure set_app_role(p_username in varchar2 default sys_context('userenv','session_user'))
  2  as
  3     l_ctx varchar2(20) default 'hr_app_ctx';
  4     l_empno number;
  5     l_count number;
  6  begin
  7     dbms_session.set_context(l_ctx,'ENAME',p_username);
  8
  9     select count(*)
 10     into l_count
 11     from emp
 12     where ename = p_username;
 13
 14     if l_count > 0 then
 15             select empno
 16             into l_empno
 17             from emp
 18             where ename = p_username;
 19             dbms_session.set_context(l_ctx,'EMPNO',l_empno);
 20     end if;
 21
 22     select count(*)
 23     into l_count
 24     from hr_reps
 25     where ename = p_username;
 26
 27     if l_count > 0 then
 28             dbms_session.set_context(l_ctx,'ROLE','HR');
 29     else
 30             select count(*)
 31             into l_count
 32             from emp
 33             where mgr = l_empno;
 34
 35                     if l_count >  0 then
 36                             dbms_session.set_context(l_ctx,'ROLE','MGR');
 37                     else
 38                             dbms_session.set_context(l_ctx,'ROLE','EMP');
 39                     end if;
 40     end if;
 41  end;
 42  /

Procedure created.

Elapsed: 00:00:00.09

rajesh@10GR2> create or replace context hr_app_ctx using set_app_role;

Context created.

Elapsed: 00:00:00.06
rajesh@10GR2> exec set_app_role('BLAKE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> create or replace package hr_app_pkg as
  2     function select_prd(p_schema in varchar2,p_object in varchar2) return varchar2;
  3     function update_prd(p_schema in varchar2,p_object in varchar2) return varchar2;
  4     --function insert_prd(p_schema in varchar2,p_object in varchar2) return varchar2;
  5     --function delete_prd(p_schema in varchar2,p_object in varchar2) return varchar2;
  6  end;
  7  /

Package created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> create or replace package body hr_app_pkg as
  2             function select_prd(p_schema in varchar2,p_object in varchar2) return varchar2
  3             as
  4                     l_ctx varchar2(20) default 'hr_app_ctx';
  5                     l_value varchar2(4000);
  6             begin
  7                     if sys_context(l_ctx,'ROLE') = 'EMP' then
  8                             l_value := ' ename = sys_context(''hr_app_ctx'',''ENAME'') ' ;
  9                     elsif sys_context(l_ctx,'ROLE') = 'MGR' then
 10                             l_value := ' empno in ( select empno from emp_base_table start with mgr = sys_context(''hr_app_ctx'',''emp
r mgr = empno ) ';
 11                     elsif sys_context(l_ctx,'ROLE') = 'HR'  then
 12                             l_value := ' deptno in (select deptno from hr_reps where ename = sys_context(''hr_app_ctx'',''ENAME'') ) '
 13                     end if;
 14
 15                     return l_value;
 16             end;
 17
 18             function update_prd(p_schema in varchar2,p_object in varchar2) return varchar2
 19             as
 20                     l_ctx varchar2(20) default 'HR_APP_CTX';
 21                     l_value varchar2(4000);
 22             begin
 23                     l_value := sys_context(l_ctx,'ROLE');
 24                     dbms_output.put_line ('Policy Role = '|| nvl(l_value,'****') );
 25                     if sys_context(l_ctx,'ROLE') = 'EMP' then
 26                             l_value := ' 1 = 0' ;
 27                     elsif sys_context(l_ctx,'ROLE') = 'MGR' then
 28                             l_value := ' mgr = sys_context(''HR_APP_CTX'',''empno'') ';
 29                     elsif sys_context(l_ctx,'ROLE') = 'HR'  then
 30                             l_value := ' deptno in (select deptno from hr_reps where ename = sys_context(''HR_APP_CTX'',''ENAME'') ) '
 31                     end if;
 32             end;
 33  end;
 34  /

Package body created.

Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2> begin
  2  dbms_rls.add_policy(object_schema=>user,
  3                                     object_name=>'EMP',
  4                                     policy_name=>'SELECT_PLCY',
  5                                     function_schema=>user,
  6                                     policy_function=>'hr_app_pkg.select_prd',
  7                                     statement_types=>'SELECT');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2> begin
  2  dbms_rls.add_policy(object_schema=>user,
  3                                     object_name=>'EMP',
  4                                     policy_name=>'UPDATE_PLCY',
  5                                     function_schema=>user,
  6                                     policy_function=>'hr_app_pkg.update_prd',
  7                                     statement_types=>'UPDATE',
  8                                     update_check =>true
  9                                     );
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> select * from session_context;

NAMESPACE            ATTRIBUTE            VALUE
-------------------- -------------------- --------------------
HR_APP_CTX           ENAME                BLAKE
HR_APP_CTX           ROLE                 HR
HR_APP_CTX           EMPNO                7698

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

6 rows selected.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> update emp set sal = 999;
Policy Role = HR
update emp set sal = 999
       *
ERROR at line 1:
ORA-28112: failed to execute policy function


Elapsed: 00:00:00.18
rajesh@10GR2>
rajesh@10GR2> select rtrim(c.value,'/') || '/' || d.instance_name ||
  2  '_ora_' || ltrim(to_char(a.spid)) || '.trc'
  3  from v$process a, v$session b, v$parameter c, v$instance d
  4  where a.addr = b.paddr
  5  and b.audsid = sys_context( 'userenv', 'sessionid')
  6  and c.name = 'user_dump_dest';

RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'
-----------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.1.0\ADMIN\10GR2\UDUMP/iradsdb_ora_444.trc

Policy function execution error:
Logon user     : RAJESH
Table/View     : RAJESH.EMP
Policy name    : UPDATE_PLCY
Policy function: RAJESH.HR_APP_PKG.UPDATE_PRD
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "RAJESH.HR_APP_PKG", line 32
ORA-06512: at line 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11

Tom:

Here is the complete set of code. You see here the ROLE is set as HR while invoking the function (see below )hr_app_pkg.update_prd. I don't know why its not matching with elsif sys_context(l_ctx,'ROLE') = 'HR' section in function.

rajesh@10GR2> update emp set sal = 999;
Policy Role = HR
update emp set sal = 999
*
ERROR at line 1:
ORA-28112: failed to execute policy function

 18             function update_prd(p_schema in varchar2,p_object in varchar2) 
return varchar2
 19             as
 20                     l_ctx varchar2(20) default 'HR_APP_CTX';
 21                     l_value varchar2(4000);
 22             begin
 23                     l_value := sys_context(l_ctx,'ROLE');
 24                     dbms_output.put_line ('Policy Role = '|| 
nvl(l_value,'****') );
 25                     if sys_context(l_ctx,'ROLE') = 'EMP' then
 26                             l_value := ' 1 = 0' ;
 27                     elsif sys_context(l_ctx,'ROLE') = 'MGR' then
 28                             l_value := ' mgr = 
sys_context(''HR_APP_CTX'',''empno'') ';
 29                     elsif sys_context(l_ctx,'ROLE') = 'HR'  then
 30                             l_value := ' deptno in (select deptno from 
hr_reps where ename = sys_context(''HR_APP_CTX'',''ENAME'') ) '
 31                     end if;
 32             end;

Thanks a Ton Tom !

Tom,
How do I identify if a user or role has row level security?

Hi Tom,

I am confused by DBMS_RLS and DBMS_FGA. They are all about policy. So what's their main difference.

You mentioned FGAC does not support column level security.

But we can use DBMS_RLS to add policy to do column level masking, which is called VPD.

So your mentioned 'FGAC' is using DBMS_FGA rather than DBMS_RLS?

Please kindly elaborate more on them.