SQL Server Row- and Cell-Level Security – Disclosure vulnerability
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.
First of all, here’s a diagram of the high-level architecture:
So let’s see it in action. The Setup of the Table and the View including 2 sample data rows: 
The column “Role” is used by the view to return the respective row by using the IS_MEMBER()-function only to members of the respectively stored database-role.
User(s), Roles and Permissions:
Now, remember what our table contains:
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:
– Of course the function User_Name() is only used for demo-purposes.
Result:
Attack
But, Andreas does not play nice. He is curious about what else might be in the table. So he crafts a query like this:
And the result is:
Not exactly “pretty”, but we got what we want: the “protected” data.
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.
To some, this picture may already look familiar 🙂
Make sure it’s not your front-yard!
Speaking of which:
Security-measures
How to prevent such forms of attack? Essentially there are 3 well-known methods at hand:
1) The use of stored procedures which catch all errors, or, if one really wants to use views for some reason, using of a multi-statement table valued function which will be put between.
2) Data encryption (Not TDE!)
3) Similar to 1, implementation of a mid-tier in the application which prohibits such actions.
Finally one should also think about an Auditing solution for critical data.
The technique of Row-Level Disclosure shown above isn’t really new, but frequently forgotten about. One can read about this, for example, in this (old but still applicable) whitepaper:
Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005
Happy securing,
Andreas
If you now feel encouraged to really dive into the subject of “Security with SQL Server”, I do have 3 first-class Trainings on offer:
For Starters, who will get a good overview and acquire essential knowledge of the basics:
(SES) SQL Server Security Essentials for Developers & Administrators (1 day) 3. April 2014 in Düsseldorf
For Administrators who have to implement advanced security concepts:
(SIA) Securityworkshop for SQL Server Administrators (advanced) (1 day) 4. April 2014 in Düsseldorf
For Developers who have to implement advanced security concepts:
(SID) Securitysworkshop for SQL Server Developers (advanced) (1 day) 24. April 2014 in Düsseldorf
Brilliant one