- SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE)
- SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted
- SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM)
- General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases
- SQL Server - Avoiding Queries on Certain Columns with Column Level Security (CLS)
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. Unlike the 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE dbo.Funcionario ( Codigo INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Nome VARCHAR(50) NOT NULL, Cargo VARCHAR(50) NOT NULL, Salario NUMERIC(18, 2) NOT NULL ) INSERT INTO dbo.Funcionario ( Nome, Cargo, Salario ) VALUES ( 'João', 'DBA JR', 7259.87 ), ( 'José', 'DBA PL', 11022.91 ), ( 'Matheus', 'DBA SR', 18751.22 ) |
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:
|
1 2 3 4 5 6 |
GRANT SELECT ON dbo.Funcionario TO [usrChato] -- Ou se você for um DBA que não tá nem aí com a segurança mesmo ALTER ROLE [db_datareader] ADD MEMBER [usrChato] GRANT SELECT TO [usrChato] GRANT SELECT ON SCHEMA::dbo TO [usrChato] |
When trying to query the table, we were able to look at all rows and columns:
Using view to constrain columns
Click to view contentThat 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE VIEW dbo.vwFuncionario_Sem_Salario AS SELECT Codigo, Nome, Cargo FROM dbo.Funcionario GO GRANT SELECT ON dbo.vwFuncionario_Sem_Salario TO [usrChato] GO |
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 contentThe 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, First Name e Cargo on the table Official:
|
1 2 |
GRANT SELECT ON dbo.Funcionario(Codigo, Nome, Cargo) TO [usrChato] GO |
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:
|
1 2 3 4 5 6 7 8 |
EXEC AS USER = 'usrChato' GO SELECT Codigo, Nome, Cargo, Salario FROM dbo.Funcionario GO REVERT GO |
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:
|
1 2 3 4 5 6 7 8 |
EXEC AS USER = 'usrChato' GO SELECT * FROM dbo.Funcionario GO REVERT GO |
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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE VIEW dbo.vwFuncionario_Completo AS SELECT Codigo, Nome, Cargo, Salario FROM dbo.Funcionario GO GRANT SELECT ON dbo.vwFuncionario_Completo TO [usrChato] GO |
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):
|
1 |
DENY SELECT ON dbo.vwFuncionario_Completo(Salario) TO [usrChato] -- poderia ser public aqui, caso queira que ninguém acesse |
The SELECT permission was denied on the column 'Salary' of the object 'vwFuncionario_Completo', 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
- UPDATED
- REFERENCES
How to identify column-level permissions
Click to view contentOpen the database where your table is, expand the folder "Security" and then "Users", right-click and click the option "Properties":
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 through the list until you find the Select permission. When you click on that item, you will see that the “Columns Permissions” button is now enabled.
To view the columns, click on the “Effective” tab
And if you want to see more details about column permissions, click on the “Columns Permissions” button
Another way to identify column-level permissions is by querying the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT A.state_desc, A.[permission_name], B.[name] AS username, E.[name] AS [schema], C.[name] AS [object], D.[name] AS [column_name] FROM sys.database_permissions A JOIN sys.database_principals B ON A.grantee_principal_id = B.principal_id JOIN sys.objects C ON A.major_id = C.[object_id] JOIN sys.columns D ON C.[object_id] = D.[object_id] AND A.minor_id = D.column_id JOIN sys.schemas E ON C.[schema_id] = E.[schema_id] WHERE A.class_desc = 'OBJECT_OR_COLUMN' AND A.[permission_name] = 'SELECT' AND A.minor_id > 0 |
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.
That's it folks!
I hope you enjoyed this tip and see you next time!
