Implementing Virtual Private Databases

(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