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?

Improve this question
2
  • 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, 2012 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, 2012 at 4:10

2 Answers 2

Reset to default
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.

Improve this answer
5
  • 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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 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://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2016

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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