Re: Bug/Feature of Fine-Grained Access Control ???

A copy of this was sent to michael_bialik_at_my-deja.com (if that email address didn't require changing) On Wed, 15 Dec 1999 20:43:51 GMT, you wrote:

>Hi.
>
> I'm trying to implement fine-grained access control for
> hierarchical table.
> I have to enable access for ONE branch only of that table ( code 30000
> at top level ).
> In order to do it I created 2 functions :
> 1. First function ( FUNCTION_A ) accepts subject code,
> makes a SELECT ... CONNECT BY PRIOR
> from SUBJECT_STUDY table and returns 1 if it is withit correct
> branch.
> 2. Second function implements security policy and returns string
> "function_a ( subject_code ) = 1".
> It is connected to the same SUBJECT_STUDY table.
> The problem is that during execution of
> "SELECT * FROM SUBJECT_STUDY" Oracle performs security policy function,
> calls FUNCTION_A that contains SELECT from the same table and starts
> recursive LOOP.
>
> I tried playing with INVOKER RIGHTS feature, but to no avail.
>
> Any ideas??
>
>
> TIA. Michael.
>
> For any really interested here are definitions.
> You need 2 users:
> 1 - execute all definitions ( I used OPS$ORACLE )
> 2 - Run SELECT * FROM SUBJECT_STUDY ( M2225283 in my example ).
>
> -- Create table of subjects for course
>--

[snip]

absolutely the defined, correct behaviour. Once a policy is in place -- it must be invoked for ALL queries on that table, regardless of the context in which that query is performed.

You might want to check out the site in my url. I have a paper there on fined grained access control i believe you would find of use. One of the quotes from it is:

<quote>
We use the EMP_BASE_TABLE and HR_REPs tables to make this determination. This leads us to the question "why do we have a table EMP_BASE_TABLE and a view EMP that is simply select * from emp_base_table?" Two reasons:

o We use the data in the employee table to enforce our security policy. o We read that table while attempting to set an application context.

In order to read the employee data we need the application context to be set – in order to set the application context we need to read the employee data. It’s a chicken and egg problem. Our solution is to create a view that all applications will use (the EMP view) and enforce our security on that view. The original EMP_BASE_TABLE will be used by our security policy to enforce the rules. From the EMP_BASE_TABLE we can discover who is a manager of a given department and who works for a given user. The application and end users will never use the EMP_BASE_TABLE – only our security policy will. The procedure to set our context then is:
...
</quote>

It is the exact situation you describe. I have other caveats spelled out in that paper as well....

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Dec 17 1999 - 07:09:56 CST