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

On Wed, 11 Mar 1998 21:05:58 -0800, "Louis Cohen" <louiscoh_at_sprynet.com> wrote:

>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'

Why bother with the function?
I can't improve on the previous post.

>select access on a view does not give select access on the underlying
>table. f'rinstance, you can select from user_objects, but that doesn't give
>you permission to select from sys.obj$. this is one of the main reasons for
>using views.
 

>/
>
>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.
Views are also the same no matter how users connect. If the security rules change the view defintion can be changed without

affecting applications that use it.

What does the function add, it seems to complicate matters.

>
>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 Thu Mar 12 1998 - 00:00:00 CST