row level security. How do I do it?

From: <mdlcpgs_at_lhn.gns.cri.nz.nospam>
Date: 1998/03/12
Message-ID: <6e7dg1$qi4$1@wnnews1.netlink.net.nz>#1/1

I have table where I want to limit access for certain users to only a subset of rows in the table. Ie there is as column STATUS in the table and I want a user_guest to only see rows where the value in Status = 1

Now I can see several ways. One would be to partition the table into status =1 table and other status table. Underprivileged users wouldnt get select rights on privileged table. I could then use a UNION view to show the contents of both tables to privileged users. Disadvantage is pain in moving records between tables when status changes. (among other things).

An approach that appeals more is a view like CREATE VIEW crippled as SELECT * FROM myTABLE where status = 1;

The problem is, how do I let them open this view without also giving them select rights to the underlying table?

Anyone know the answer to this or another way to do it?

---

Phil Scadden, Institute of Geological and Nuclear Sciences

Work: PO Box 30368, Lower Hutt		  Home: 55 Buick St, Petone, Lower Hutt
			        New Zealand
ph +64 (04) 569-9059, fax (04) 569 5016	        ph (04) 568-7190, 

Received on Thu Mar 12 1998 - 00:00:00 CST