Home » RDBMS Server » Security » Row level security
Row level security [message #627003] Thu, 06 November 2014 08:05 Go to next message
Femke
Messages: 6
Registered: November 2014
Junior Member
Hello,
At our company we would like to implement security at rowlevel and not only the abillity to view but also allow updates, deletes (for specific roles) based on the record data.
I did read some documentation on VPD and OLS, if I understand correctly, both can only hide the data and not restrict updates Sad , or did I miss something Smile ?
I would like to know what would be a good solution, can I use oracle functionalities or do I have to build it myself (fi: triggers).
Thanks!!
Re: Row level security [message #627007 is a reply to message #627003] Thu, 06 November 2014 08:23 Go to previous messageGo to next message
John Watson
Messages: 7937
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You mis-read the docs. You can apply a VPD policy to all statements, or (if you want to make it really confusing) you can have different policies for SELECT, INSERT, UPDATE, and DELETE. Look at the STATEMENT_TYPE argument of DBMS_RLS.ADD_POLICY.
Re: Row level security [message #627009 is a reply to message #627007] Thu, 06 November 2014 08:26 Go to previous messageGo to next message
Femke
Messages: 6
Registered: November 2014
Junior Member
Great!!!
Thanks for the fast reply!
Re: Row level security [message #627015 is a reply to message #627003] Thu, 06 November 2014 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Depending on the actual requirements, you can most likely do what you want with views.

Re: Row level security [message #627018 is a reply to message #627015] Thu, 06 November 2014 09:33 Go to previous messageGo to next message
Femke
Messages: 6
Registered: November 2014
Junior Member
Hi Michel, thanks for the reply.
Actually, we would like to leave our developed forms and .net applications as much intact as possible but block updates of some records for some tables by some users depending on very diverse reasons and probably in the future, maybe also block viewing some records for some users.
Re: Row level security [message #627019 is a reply to message #627018] Thu, 06 November 2014 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the condition is simple so you can do it with views.
If not then go to VDP but, first, be sure you can't do it with views (or maybe triggers as you mentioned).
My opinion is the to follow:
1/ views
2/ if not possible, triggers
3/ if not possible, VPD

Till now, the only times I see VPD has to be used is when you can't modify the application owner and user schemas and so can't create views or triggers.

Re: Row level security [message #627020 is a reply to message #627019] Thu, 06 November 2014 09:53 Go to previous messageGo to next message
Femke
Messages: 6
Registered: November 2014
Junior Member
Hi Michel,
I don't see how a view will help when 2 users look at the same UI, one is allowed to update, the other is not, can you explain?
Triggers would work indeed but before going there, we would like to see if there is a more generic solution (the main db has > 800 tbls., +/-650 oracle forms a number of .net applications, people connecting with odbc, ms access, ....).
I detect an aversion towards VPD, is it not an obvious choice?
Thanks!
Re: Row level security [message #627021 is a reply to message #627020] Thu, 06 November 2014 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

VPD requires knowledge on how SGA and your application (SQL and PL/SQL code) work. It is a more powerful but complex and less stable option, this is why you should use it only when the other simpler method can't achieve what you want.
Having many tables is not a valid answer, you develop once, you use many and performances issue with VPD are hard to handle.

Re: Row level security [message #627074 is a reply to message #627021] Fri, 07 November 2014 02:34 Go to previous messageGo to next message
Femke
Messages: 6
Registered: November 2014
Junior Member
Hi Michel,
You are right, many tables is not an excuse, just that modifying all triggers.. + I thought with VPD, maybe we could for future developments, disable the UI for non-updateable rows by knowing beforehand if the row is updatable.
Just another question, with VPD, can I block an update by querying another table? Or do I need to base the restriction on the record itself?
Thanks!
Re: Row level security [message #627081 is a reply to message #627074] Fri, 07 November 2014 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Just another question, with VPD, can I block an update by querying another table?


Yes, you can do anything you can write in PL/SQL but more it is complex more you will fool the SQL optimizer and you will increase the hard parse ratio, so take care of the performances.

Re: Row level security [message #627082 is a reply to message #627074] Fri, 07 November 2014 03:05 Go to previous messageGo to next message
John Watson
Messages: 7937
Registered: January 2010
Location: Global Village
Senior Member
As a general principle, I agree with Michel: avoid if possible. VPD can be a mission to set up, and tuning can be complicated. Furthermore, it needs Enterprise Edition licences.
But being a declarative technique, it is far superior technology to any of the suggested alternative programmatic techniques. You, the DBA, define the policy and it is applied everywhere. No programmer input.

Your VPD function can do pretty much anything, as long as it conforms to the specification and does not query the table on which the policy is defined.
Re: Row level security [message #627090 is a reply to message #627082] Fri, 07 November 2014 03:15 Go to previous message
Femke
Messages: 6
Registered: November 2014
Junior Member
Michel, John,
Thanks for the helpfull input, we're going to think about it.
Previous Topic: Password complexity
Next Topic: How To monitor triggers behavior of schemas
Goto Forum:
  


Current Time: Mon Jun 24 15:09:54 CDT 2019