(en)It’s time for another post on security matters. And through a forum-thread on data-driven security by the means of views using the IS_MEMBER(), USER_NAME(), SUSER_SNAME() – functions, I came up with the idea of giving a short example how such constructs can easily be circumvented and the protected/hidden data become disclosed, when not being secured by further means. So let’s look at an example.

In the following we will see a quite common scenario of how Row-Level Security (and also Cell-Level Security) can be implemented.

The architecture is quite simple: A table is holding rows of data, some of which are supposed to be readable by a certain group of people, and other rows by other people – in each case exclusively. In order to achieve this, a view is created. This view naturally must have the same owner, so the principal can be granted permissions on nothing but the view and get to the data by means of the ownership-chain. Within the view there is a Where-clause which contains a filter on a certain attribute in the table, by which the user of the current session is detected and returned solely the data which matches his role-membership.

Of course there are also more complex designs with intermediate tables and multi-role-memberships/permissions, but it all comes down sharing the same vulnerability which I am about to demonstrate.

So in an innocent world, before the fall of mankind, this would be sufficient. (After logging in as “Andreas”, who is member of the RoleAlpha database-role) our queries would look like this and only return the rows which “belong” to RoleAlpha:

The well-educated reader may remember this kind of attack from a different area as well: SQL Injection. It’s a form of the old fried “error based attack” or “error-disclosure”, which can also be used for badly written web-applications. I have also shown that amongst others in 2013 at several conferences (series of sessions). The context is a little bit different, but the idea is the same.

(en)Alright, this is going to be the by far most active year in terms of speaking at international conferences: After 6 conferences last year, including SQL Rally Nordic, which I really liked a lot, I had to decide between SQL Rally Nordic again or SQL Rally Amsterdam or even both. I decided for SQL Rally Amsterdam, because it is new, and because I promised the Dutch Chapter leader, to hand in a session. So this year no SQL Rally Nordic.

- Having spoken at already 7 conference this year, including 1.5 days of PreCon (www.andreas-wolter.com/sql-conferences/sql-conferences-2013.htm ) + 3 more coming up (PASS Summit Charlotte USA, TechNet Berlin Germany, PASS Camp Darmstadt Germany), 11 conferences in 2013 really is a lot. Also considering once in a while my customers are actually happy if I have time for them :-).