Re: Can you trigger an automatic WHERE-clause on any statement on a table?

Charlton,
We kept a user access table where we kept usernames. The function used in the policy was able to get the user from the Oracle function USER, and then cross referenced the user by an access level. The access level table had a region, installation, or organization to associate with the user. Some users were restricted by installation, regions, or organizations. The returned where clause fragment from the policy function restricted the records the user could see. For an admin user, you can return null which gives everything. Returning something like 1=2 returns nothing.

It seemed to me that Oracle performed very well against the policies. We of course indexed these fields appropriately, usually with bitmap indexes for the low cardinality fields.
I hope this helps.
Dave

Charlton Purvis wrote:

> Right, Dave, that's the route I've been testing lately. I found
> http://govt.oracle.com/~tkyte/article2/index.html a good reference.
>
> Have you been happy w/ your implementation? Without asking you to go
> into too much detail, did you organize your security by adding a
> standard set of columns to each table?
>
> Back to the UNIX-like filesystem structure: Do you have something
> equivalent to user/group/other sets of columns added to each table to
> contain something synonymous to rwx permissions, i.e. select, udpate,
> delete, etc., and then use policies to modify the WHERE clauses
> accordingly? Any secrets about caching that you'd like to share?
>
> Thanks,
>
> Charlton
>
> In article <3A00BF72.FEB800B4_at_erols.com>,
> luhbey_at_erols.com wrote:
> > Yes, you can. Use Oracle policies. I don't have my code here at home,
 but you
> > can email me at mailto:godbeyd_at_saic.com for examples.
> > > > Bottom line . . . Is it unpracitcal to associate file system-like
> > > > permissions on every row of every table to limit a user's scope
 and
> > > > permissions?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 03 2000 - 19:59:39 CST