SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)

Views: 593
Reading Time: 6 minutes

Hello people!
In this article I would like to demonstrate a very old feature (available since SQL Server 2005 - or earlier) that few people use or know exists, which is Column Level Security (CLS) or Column Level Security. Different from Row level security, where the user views all columns but only a few rows, in CLS the restriction is made on certain columns to which the user will or will not have access to view.

This feature is also available in Azure SQL Datawarehouse, but as of this writing, not is available for Azure SQL Database.

A classic example for demonstrating the use of this feature is an Employee table, on an HR basis, for example:

In this scenario, a DBA more concerned with environment security would give read access (SELECT) on this table to the requesting user, and the requesting user would be able to query all data in the table:

When trying to query the table, we were able to look at all rows and columns:

Did you like this EXEC AS command to IMPERSONATE the user? Learn more about him in my article SQL Server - How to use EXECUTE AS to execute commands as another user (Impersonate login and user).

Using view to constrain columns

Click to view content
For DBAs more concerned with data security and privacy, one feature I often see being used is the use of views to limit the columns that certain users will have access to view.

That is, if I want our usrChato to have access to all columns in the Employee table, except for Salario, I can create a view without this column and give select view access to this user:

And with that, if the user tries to access the Employee table directly, they will get an error message of lack of permission:

While he will have access in the view, which has all fields except the Salary:

And now, whenever I have a user who needs to view all columns in this table except the salary, I release SELECT access to that view (or manage access through roles).

But what about when I have multiple users with different needs to control which columns they will access? Will I create a lot of views in my environment to meet every need?

Using Column Level Security (CLS) to constrain columns

Click to view content
Available since the 2005 version of SQL Server (probably earlier), there is a feature called Column Level Security (CLS), also known as Column Level Permission, which allows you to define which columns a particular permission will be applied to.

The great advantage of this approach is that by no means will the user have access to this column, even if views are created by querying the table in question and he has access to that view (in this case, we will have to use DENY). In addition, you avoid creating multiple objects and views solely for security purposes.

A major disadvantage of this approach, however, is that the user will need to know the table structure and the specific columns he will need to know very well, as a SELECT * will no longer work.

In the example of this article, I will define that the usrChato only allowed to view columns Code, Name e Position on the table Official:

And now, I will be able to query these columns with the user usrChato:

The same is no longer possible if I try to include the Salary column:

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column 'Salary' of the object 'Employee', database 'master', schema 'dbo'.

Or when trying to make the famous SELECT * FROM:

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column 'Salary' of the object 'Employee', database 'master', schema 'dbo'.

What if I create a view that has this column? Will the user be able to view the data even if they do not have permission on the Table?

And now I will try to access the view:

Ouch !! The user was able to view the salary !! And now?
Well, to solve this we need to apply a DENY command to the NA VIEW salary column (the original table is no use, because the user has full access in the view):

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column 'Salary' of the object 'vwFunctional_full', database 'master', schema 'dbo'.

It is important to note that you can use Column Level Security (CLS) (also known as Column Level Permission) in conjunction with the following permissions:

  • SELECT
  • UPDATE
  • REFERENCES
This feature is not unique to tables only. It supports views too, and works the same way.

How to identify column-level permissions

Click to view content
If you choose to start using this feature, it is important to know how to identify which columns a particular user has access to. One way to view this is by using the SQL Server Management Studio (SSMS) interface.

Open the database where your table is, expand the folder "Security" and then "Users", right-click and click the "Properties" option:

In the window that opened, click on the "Securables" option, select the table you want to view, click on the "Explicit" tab to view the permissions and scroll the list until you find the Select permission. By clicking on this item you will see that the “Columns Permissions” button is now enabled.

To view the columns, click on the “Effective” tab.

And if you would like to see more details about column permissions, click the “Columns Permissions” button.

Another way to identify column-level permissions is by querying the database:

Result:

Control by views or permission

Here we come to a very personal point of view. I particularly prefer to manage permissions through views, because management is simpler than having a table or view where each user accesses in one way. As we have already commented in this article, using column-level permission, we avoided creating multiple objects in the database for data security and privacy purposes only, but at the same time, the user will need to know the table structure and the data very well. specific columns that it will need to query, as a SELECT * will no longer work.

I believe that 2 solutions are very good at restricting unauthorized users from accessing sensitive columns, it is up to the bank team to choose which method they are going to get used to.

If you are using version 2016 or higher, you can also use the Dynamic Data Masking to mask the output of columns that have sensitive data.

That's it folks!
I hope you enjoyed this tip and see you next time!