jump to navigation

Row Level Security 3 – In Pictures! November 22, 2012

Posted by mwidlake in database design, security.
Tags: , ,
trackback

<..Part one intro and examples
<….Part two Permissions

I’ve noticed that there has not been a lot of traffic on this series on Row Level Security (data masking) so far – maybe due to how I am presenting the material? So here is a summary to date in picture/diagram format:

This is the flow of logic as to whether Row Level Security is applied to the SQL statement. Usually this is a query and usually it is on a table but the policy can be applied to other DML and also on views and synonyms:

These are the steps you need to add RLS to a table:

Below is a summary of how oracle fires and uses RLS functions and the predicates they provide:

An finally, a summary of what happens if an RLS function is invalid:

If you have not looked at the previous posts on this topic, more details are in there.

Enjoy!

About these ads

Comments»

1. Dom Brooks - November 23, 2012

> there has not been a lot of traffic on this series
It’s surprisingly niche functionality, I think.

2. Jeff Kemp - March 11, 2013

Thanks for this series on RLS.

Just something I noticed when working with RLS:

I had a table which modelled the organisation hierarchy (division, directorates and branches); the users wanted to be able to restrict access to data based on this hierarchy – so, for example, a division head could see all data for his division (including child directorates and branches), but a branch manager could only see his branch.

I added an RLS policy to all the tables which would query the hierarchy table to see if the user is allowed to see the row – effectively running a hierarchical query on the table until it found a level to which the user had Read access.

That worked fine; until the users complained that queries *on the hierarchy table itself* were still showing all the different divisions, directorates and branches. The directors didn’t want to see all the branches from the other directorates (the selection lists were “too long”).

At first I tried implementing an RLS policy on the hierarchy table itself – but this doesn’t work, because the RLS policy for that table was trying to query the table itself, which is not allowed.

In the end we decided that queries on the hierarchy table were not a security issue (just a user convenience issue), and we implemented the filtering on that table with a view instead.

mwidlake - March 11, 2013

Thank you Jeff, both for the positive feedback and also the extra information. I think the decision on whether to use VPD or views is a non-trivial one, but can be forced by some of the limitations on VPD. In my current role I keep wondering if VPD was the correct choice – but then, I can’t see how I would have come to a better solution with views (in my particular user-case).

I can foresee issues with VPD on hierarchies and thus connect-by queries – but I want to try a few things before I blog on it. I have one last post on VPD to finish and I hope to do so soon, but my current role is coming to an end and so I am up to my eyes trying to finish things off for the client. You might have made this two!

Once again, thanks for the extra information.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 165 other followers

%d bloggers like this: