Re: Fine Grained Access Control

Ovind,

You seem to have got the answer on

a very small hint. I didn't want to

answer in detail in case I confused the

issue by answering the wrong question.

The table the predicate attaches to has

to be referenced by its full name, because

Oracle (almost) turns the table into an inline

view so your query:

select

......

from

table_001 parent,

table_002 child

where

......

effectively turns into:

select

.....

from

( select * from table_001

where {your predicate}

) parent,

table_002 child

where

.....

So the alias you give to the table

is not known at the moment Oracle

applies the predicate.

Bear in mind, though, that when you

call the predicate function, one of

the input parameters to the function

is the object_name, so you can use that

as you build the predicate string to

make the function table-generic.

Jonathan Lewis

Ce mel a été envoyé avec Meloo http://www.meloo.com

"ovind osengen" <oeo_at_rocketmail.com> wrote in message news:8t148h$4et$1_at_oslo-nntp.eunet.no...

> Thank you for your idea!
> Initially I would like to have one function for several tables, but the
> drawback with table specific functions is not critical. This solves the
> problem easiliy, i.e. I change T to the table name.
>
> Ovind
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:972072400.5954.0.nnrp-02.9e984b29_at_news.demon.co.uk...
> >
> > Can you clarify this.
> > Are you trying to create a function which generates
> > the string:
> >
> > T.ID IN
> > ( SELECT P.ID
> > FROM PRIVILEGIES P
> > WHERE P.READ_OWN = 'Y' )
> > AND
> > CODE IN
> > ( SELECT M.CODE
> > FROM MAPPING M,
> > PRIVILEGIES P
> > WHERE M.CODE = P.CODE
> > AND T.ID = P.ID );
> >
> > so that you can attach it to just about
> > any table, or to just one specific table ?
> >
> >
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > ovind osengen wrote in message <8snaor$dmc$1_at_oslo-nntp.eunet.no>...
> > >I am designing a security mechanism with the fine grained access
 control.
> > >The filter should basically look like:
> > >
> > >SELECT T.ID, T.CODE, T.NAME
> > >FROM TABLE T
> > >WHERE T.ID IN
> > > ( SELECT P.ID
> > > FROM PRIVILEGIES P
> > > WHERE P.READ_OWN = 'Y' )
> > >AND
> > > CODE IN
> > > ( SELECT M.CODE
> > > FROM MAPPING M,
> > > PRIVILEGIES P
> > > WHERE M.CODE = P.CODE
> > > AND T.ID = P.ID );
> > >
> > >However the security policy function only returns the predicate, but
 this
> > >cause T.ID in the 3rd select statement to be undefined. How can this be
 done
> > >better?
> > >
> > >
> > >Best Regards
> > >Ovind
> > >
> > >oeo_at_rocketmail.com
> > >
> > >
> > >
> >
> >
>
>
Received on Tue Oct 24 2000 - 03:53:13 CDT