7

I think I need some pointers on security in SQL Server. I'm trying to restrict some of our end users from getting access to certain columns (i.e. SSN) on a table.

I thought I could just use column level security to restrict access to the columns. That successfully prevented users from accessing the table directly, but I was surprised that they could still get to those columns through a view that accessed that table.

I followed the tips here: http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/ Those were very helpful, but when I created a view at the end, the intern was able to access that column by default

I've read that views are the best way to accomplish this, but I really don't want to go through and change all of the views and the legacy front-end application. I would rather just restrict it once on the table and if a view tries to access that column it would just fail.

Is that possible or am I misunderstanding how security works in SQL Server?

  • 1
    Typical use case for a view - define a view on top of your table which contains just those columns you want your users to see and then give those users SELECT permission on the view, but deny them the permission on the underlying table – marc_s Sep 29 '12 at 19:45
  • I offer an alternative view: it may be possible to design a DDL trigger upon the table which propagates any grant/revoke to the views based on it. Those views would be found by inspecting the objects metadata. I'll elaborate and, hopefully, write a complete solution. – Marcus Vinicius Pompeu Oct 1 '12 at 4:10
4

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.

  • That's unfortunate. So is the same true at the table level? So that if I restrict access to the entire table, a view can still access it? If so it's almost better to just put those tables in another database, where I can restrict it to certain users – user46372 Sep 29 '12 at 16:27
  • @user46372 No you don't need to do that. Read this on Ownership Chains to understand what is happening. – Martin Smith Sep 29 '12 at 17:00
  • Martin, thanks for the link. So theoretically, if the tables I wanted to lock down were owned by a different user than all the rest of the objects / views, would that force SQL Server to recheck the permissions and prevent access? – user46372 Sep 29 '12 at 20:27
  • 3
    @user46372 - Yes. That should be correct. But FWIW I agree with marc_s's comnment on the question that it is easiest just to deny access on the table and manage this through creating a view with the desired subset of columns. – Martin Smith Sep 29 '12 at 21:33
  • Yes, I've heard the views option is the best, but unfortunately our legacy front-end program is an Access Data Project which exposes all of the views and tables (ick). Since we already have a bunch of views created I guess we would we have to go through each one and DENY access? And we would just have to remember to DENY access on every new view we create as well, right? That would be error-prone. – user46372 Sep 29 '12 at 23:14
0

Note that in SQL 2016+ there is a data masking feature, which can hide the full details of specific columns. This can prevent users from seeing the real values of certain columns unless they have specific "unmask" permissions.

Note that users may still indirectly find data via WHERE clauses -- for example, even if "Salary" were masked, you could still query users with Salary between X and Y.

For example:

ALTER COLUMN [Social Security Number] ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')

Documentation: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2016

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.