Re: Row Level Security.

Steve,

I've implemented Row Level Security (FGAC) in 8.1.6.

The only way you can enforce RLS by User is to somehow (this may be a significant challenge) capture the User's login or some other ID before the app switches her to the schema owner. Then, after the User has been switched to the schema owner (which, I assume, starts a new database session), set the appropriate Application Context variables based on the "real" User's login or ID. Thus, even as the schema owner, the User-specific Application Context variables will be used to modify the predicates of SQL statements hitting the RLS-protected tables. Of course the predicate-generating functions that enforce your RLS Policies must drive off of the Application Context variables.

It works very well if the security policies by which Users are related to specific rows in specific tables are defined in a set of security policy tables. That is, a set of tables that define the relationships between specific Users and identifiers (Types, Classifications, etc.) of the rows in the actual data tables to which they have access.

The only challenge is if this is a Web app, the Application Context variables must be properly set for every access to the database. We used a Cookie to track a unique session ID for each Web User and reset Application Context variables appropriately every time the User hit the database.

Regards,
Jack

Steve Barlow wrote:

> Hi,
>
> Has anyone out there implemented Row Level Security on an ERP like
> application like PeopleSoft? This is not PeopleSoft application but like PS
> it authenticates users and then performs all actions as the scehma owner
> (SYSADM). How can I go about controlling access to rows in the database by
> user if the schema owner takes control after the user logs in?
>
> Anyone have any thoughts on this?
>
> Thanks,
>
> Steve

--
Jack C. Applewhite
Senior Consultant, OCP Oracle8 DBA
Stonebridge Technologies, Inc.
...The Fast Track to e-Business.
  (visit us at www.sbti.com)