OraFAQ Forum: Security » Row level security

Home » RDBMS Server » Security » Row level security

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row level security [message #627003]

Thu, 06 November 2014 08:05

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

Report message to a moderator

Re: Row level security [message #627007 is a reply to message #627003]

Thu, 06 November 2014 08:23

John Watson
Messages: 8968
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.

Report message to a moderator

Re: Row level security [message #627009 is a reply to message #627007]

Thu, 06 November 2014 08:26

Femke
Messages: 6
Registered: November 2014

Junior Member

Great!!!
Thanks for the fast reply!

Report message to a moderator

Re: Row level security [message #627015 is a reply to message #627003]

Thu, 06 November 2014 09:24

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Row level security [message #627018 is a reply to message #627015]

Thu, 06 November 2014 09:33

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.

Report message to a moderator

Re: Row level security [message #627019 is a reply to message #627018]

Thu, 06 November 2014 09:40

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Row level security [message #627020 is a reply to message #627019]

Thu, 06 November 2014 09:53

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!

Report message to a moderator

Re: Row level security [message #627021 is a reply to message #627020]

Thu, 06 November 2014 10:07

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Row level security [message #627074 is a reply to message #627021]

Fri, 07 November 2014 02:34

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!

Report message to a moderator

Re: Row level security [message #627081 is a reply to message #627074]

Fri, 07 November 2014 03:03

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Row level security [message #627082 is a reply to message #627074]

Fri, 07 November 2014 03:05

John Watson
Messages: 8968
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.

Report message to a moderator

Re: Row level security [message #627090 is a reply to message #627082]

Fri, 07 November 2014 03:15

Femke
Messages: 6
Registered: November 2014

Junior Member

Michel, John,
Thanks for the helpfull input, we're going to think about it.

Report message to a moderator

Previous Topic:	Password complexity
Next Topic:	How To monitor triggers behavior of schemas

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 21:25:05 CDT 2025