Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: row level security. Why functions are good

Re: row level security. Why functions are good

From: Louis Cohen <louiscoh_at_sprynet.com>
Date: 1998/03/14
Message-ID: <6efmun$8kh$1@lal.interserv.com>#1/1

In the original example, the security criterion was pretty simple and could easily be implemented in the view.

If the rules were more complicated, requiring procedural logic, you can use a function to keep the view nice and simple.

Regards

Louis Cohen

Ian Jones wrote in message +ADw-35077b92.753680+AEA-news.slip.net+AD4-...
+AD4-On Wed, 11 Mar 1998 21:05:58 -0800, +ACI-Louis Cohen+ACI-
+AD4APA-louiscoh+AEA-sprynet.com+AD4- wrote:
+AD4-
+AD4APg-Here is the best way to implement row-level security for any set of rules:
+AD4APg-
+AD4APg-Create view my+AF8-tab+AF8-v as
+AD4APg-select +ACo-
+AD4APg-from my+AF8-tab
+AD4APg-where my+AF8-tab+AF8-access(user, status) +AD0- 'Y'
+AD4-
+AD4-Why bother with the function?
+AD4-I can't improve on the previous post.
+AD4-
+AD4APg-select access on a view does not give select access on the underlying
+AD4APg-table. f'rinstance, you can select from user+AF8-objects, but that doesn't
give
+AD4APg-you permission to select from sys.obj+ACQ-. this is one of the main reasons
for
+AD4APg-using views.
+AD4-
+AD4APg-/
+AD4APg-
+AD4APg-my+AF8-tab+AF8-access is a PL/SQL function (stored in the database of course) that
+AD4APg-takes the user name and any other relevant columns as parameters and
returns
+AD4APg-Y if the user is allowed to see the record.
+AD4APg-
+AD4APg-No user should have access to the underlying table+ADs- grant access only on
the
+AD4APg-view.
+AD4APg-
+AD4APg-This technique provides security no matter how the users connect to the
+AD4APg-database (e.g., ODBC, etc.) and if your rules change you just change the
+AD4APg-function without changing any application code.
+AD4-Views are also the same no matter how users connect.
+AD4-If the security rules change the view defintion can be changed without
+AD4-
+AD4-affecting applications that use it.
+AD4-
+AD4-What does the function add, it seems to complicate matters.
+AD4-
+AD4APg-
+AD4APg-Regards
+AD4APg-
+AD4APg-Louis Cohen
+AD4APg-
+AD4APg-mdlcpgs+AEA-lhn.gns.cri.nz.nospam wrote in message
+AD4APgA8-6e7dg1+ACQ-qi4+ACQ-1+AEA-wnnews1.netlink.net.nz+AD4-...
+AD4APgA+-I have table where I want to limit access for certain users to only a
+AD4APgA+-subset of rows in the table. Ie there is as column STATUS in the table
+AD4APgA+-and I want a user+AF8-guest to only see rows where the value in Status +AD0- 1
+AD4APgA+-
+AD4APgA+-Now I can see several ways. One would be to partition the table into
status
+AD4APgA+AD0-1 table and other status table. Underprivileged users wouldnt get
+AD4APgA+-select rights on privileged table. I could then use
+AD4APgA+-a UNION view to show the contents of both tables to privileged users.
+AD4APgA+-Disadvantage is pain in moving records between tables when status
changes.
+AD4APgA+-(among other things).
+AD4APgA+-
+AD4APgA+-An approach that appeals more is a view like
+AD4APgA+-CREATE VIEW crippled as SELECT +ACo- FROM myTABLE where status +AD0- 1+ADs-
+AD4APgA+-
+AD4APgA+-The problem is, how do I let them open this view without also giving them
+AD4APgA+-select rights to the underlying table?
+AD4APgA+-
+AD4APgA+-Anyone know the answer to this or another way to do it?
+AD4APgA+-
+AD4APgA+--------------------------------------------------------------------------

--

+AD4APg--
+AD4APgA+-Phil Scadden, Institute of Geological and Nuclear Sciences
+AD4APgA+-Work: PO Box 30368, Lower Hutt Home: 55 Buick St, Petone, Lower Hutt
+AD4APgA+- New Zealand
+AD4APgA+-ph (04) 569-9059, fax (04) 569 5016 ph (04) 568-7190,
+AD4APg-
+AD4APg-
+AD4-
Received on Sat Mar 14 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US