Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: row level security. Why functions are good
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+--------------------------------------------------------------------------
--Received on Sat Mar 14 1998 - 00:00:00 CST
+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-