Re: row level security. How do I do it?

Here is the best way to implement row-level security for any set of rules:

Create view my+AF8-tab+AF8-v as
select +ACo-
from my+AF8-tab
where my+AF8-tab+AF8-access(user, status) +AD0- 'Y' /

my+AF8-tab+AF8-access is a PL/SQL function (stored in the database of course) that takes the user name and any other relevant columns as parameters and returns Y if the user is allowed to see the record.

No user should have access to the underlying table+ADs- grant access only on the view.

This technique provides security no matter how the users connect to the database (e.g., ODBC, etc.) and if your rules change you just change the function without changing any application code.

Regards

Louis Cohen

mdlcpgs+AEA-lhn.gns.cri.nz.nospam wrote in message
+ADw-6e7dg1+ACQ-qi4+ACQ-1+AEA-wnnews1.netlink.net.nz+AD4-...
+AD4-I have table where I want to limit access for certain users to only a
+AD4-subset of rows in the table. Ie there is as column STATUS in the table
+AD4-and I want a user+AF8-guest to only see rows where the value in Status +AD0- 1
+AD4-
+AD4-Now I can see several ways. One would be to partition the table into status
+AD4APQ-1 table and other status table. Underprivileged users wouldnt get
+AD4-select rights on privileged table. I could then use
+AD4-a UNION view to show the contents of both tables to privileged users.
+AD4-Disadvantage is pain in moving records between tables when status changes.
+AD4-(among other things).
+AD4-
+AD4-An approach that appeals more is a view like
+AD4-CREATE VIEW crippled as SELECT +ACo- FROM myTABLE where status +AD0- 1+ADs-
+AD4-
+AD4-The problem is, how do I let them open this view without also giving them
+AD4-select rights to the underlying table?
+AD4-
+AD4-Anyone know the answer to this or another way to do it?
+AD4-
+AD4----------------------------------------------------------------------------
-
+AD4-Phil Scadden, Institute of Geological and Nuclear Sciences
+AD4-Work: PO Box 30368, Lower Hutt Home: 55 Buick St, Petone, Lower Hutt
+AD4- New Zealand
+AD4-ph (04) 569-9059, fax (04) 569 5016 ph (04) 568-7190,
Received on Wed Mar 11 1998 - 00:00:00 CST