Re: Implementing Virtual Private Databases

Peter,

You would need to set the application context for the user(s) so that the predicate pkg/function could use that. Can't think of anything else to set that besides a logon trigger. If you're concern is about having to set that up for each user, you could also do that at the database level and the security gets managed using a security table in that case (every user would have to go thru that) -- its a small one-time hit at logon. I can also forward an example you like.

• Ravi Gaur

On 8/27/07, Schauss, Peter <peter.schauss_at_ngc.com> wrote:
>
> (Oracle 10.2.0.2.0)
>
> We are in the process of implementing COTS application in which a single
> database will be used by multiple customers. One of the requirements is
> to allow ad hoc reporting on a limited basis to a small number of users.
> Users from a given customer should only be allowed to see their own
> data. I have implemented a test case on a one table by doing the
> following:
>
> - Created a secure application context with a package and function to
> set the value of a context parameter which will distinguish between
> users.
>
> - Created a logon trigger to set the application context value for the
> application users.
>
> - Created a package/function to append a predicate to the where clause
> for queries to the table.
>
> - Applied the security policy to the table using dbms_rls.add_policy.
>
> Using this approach, it looks like I need to create an AFTER LOGON
> trigger for every user to whom we are granting ad hoc access. Is there
> a way to avoid this?
>
> Thanks,
> Peter Schauss
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l