SQL Server - Evitando consultas em determinadas colunas com o Column Level Security (CLS)

Post Views 1,046 views

Neste artigo

Esse post é a parte 5 de 5 da série Data Protection

SQL Server 2008 – Como criptografar seus dados utilizando Transparent Data Encryption (TDE)
SQL Server 2016 – Como criptografar seus dados utilizando Always Encrypted
SQL Server 2016 – Mascaramento de dados com o Dynamic Data Masking (DDM)
Lei Geral de Proteção de Dados Pessoais (LGPDP ou LGPD) aplicada a bancos de dados SQL Server
SQL Server – Evitando consultas em determinadas colunas com o Column Level Security (CLS)

Reading time 6 minutes

Olá pessoas!
Nesse artigo eu gostaria de demonstrar um recurso bem antigo (disponível desde o SQL Server 2005 – ou antes) e que pouca gente utiliza ou conhece que existe, que é o Column Level Security (CLS) ou Segurança a nível de coluna. Diferente do Row Level Security, onde o usuário visualiza todas as colunas mas apenas algumas linhas, no CLS a restrição é feita em determinadas colunas em que o usuário terá ou não acesso para visualizar.

Esse recurso também está disponível no Azure SQL Datawarehouse, mas, até o momento em que estou escrevendo esse artigo, não está disponível para o Azure SQL Database.

Um exemplo clássico para demonstrar o uso desse recurso é uma tabela de Funcionarios, em uma base de RH, por exemplo:

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 )

CREATE TABLE dbo.Funcionario (

Codigo INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

Name VARCHAR(50) NOT NULL,

Cargo VARCHAR(50) NOT NULL,

Salario NUMERIC(18, 2) NOT NULL

)

INSERT INTO dbo.Funcionario

(

Name,

Cargo,

Salario

)

VALUES

( 'João', 'DBA JR', 7259.87 ),

( 'José', 'DBA PL', 11022.91 ),

( 'Matheus', 'DBA SR', 18751.22 )

Nesse cenário, um DBA mais despreocupado com a segurança do ambiente daria acesso de leitura (SELECT) nessa tabela para o usuário solicitante, e este conseguiria consultar todos os dados da tabela:

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]

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]

Ao tentar realizar a consulta na tabela, conseguimos olhar todas as linhas e colunas:

Gostou desse comando EXEC AS para fazer o IMPERSONATE do usuário? Saiba mais sobre ele no meu artigo SQL Server – Como utilizar o EXECUTE AS para executar comandos como outro usuário (Impersonate login e user).

Utilizando view para restringir colunas

Clique para visualizar o conteúdo

Para os DBA’s mais preocupados com segurança e privacidade dos dados, um recurso que eu costumo ver com frequência sendo utilizado é a utilização de views para limitar as colunas que determinados usuários terão acesso de visualizar.

Ou seja, caso eu queira que o nosso usrChato tenha acesso a todas as colunas da tabela Funcionario, menos a de Salario, eu posso criar uma view sem essa coluna e dar acesso de select na view para esse usuário:

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

CREATE VIEW dbo.vwFuncionario_Sem_Salario

SELECT

Codigo,

Name,

Cargo

FROM

dbo.Funcionario

GRANT SELECT ON dbo.vwFuncionario_Sem_Salario TO [usrChato]

E com isso, caso o usuário tente acessar diretamente a tabela de Funcionario, irá tomar uma mensagem de erro de falta de permissão:

Enquanto ele terá acesso na view, que possui todos os campos, menos o Salario:

E agora, sempre que eu tiver um usuário que precise visualizar todas as colunas dessa tabela, menos o salário, eu libero acesso de SELECT nessa view (ou gerencio o acesso através de roles).

Mas e quando eu tenho vários usuários, com diferentes necessidades de controle de quais colunas eles vão acessar? Vou criar um monte de views no meu ambiente para atender a cada necessidade ?

Utilizando o Column Level Security (CLS) para restringir colunas

Clique para visualizar o conteúdo

Disponível desde a versão 2005 do SQL Server (provavelmente até antes), existe um recurso chamado Column Level Security (CLS), também conhecido como Column Level Permission, que permite a definir em quais colunas determinada permissão será aplicada para o usuário.

A grande vantagem dessa abordagem, é que de forma alguma o usuário terá acesso à essa coluna, mesmo que sejam criadas views consultando a tabela em questão e ele tenha acesso nessa view (neste caso, teremos que utilizar o DENY). Além disso, você evita ficar criando vários objetos e views para atender a necessidades de segurança, exclusivamente.

Entretanto, uma grande desvantagem dessa abordagem, é que o usuário precisará conhecer muito bem a estrutura da tabela e as colunas específicas que ele precisará consultar, já que um SELECT * não vai funcionar mais.

No exemplo desse artigo, vou definir que o usrChato só tenha permissão de visualizar as colunas Codigo, Name e Cargo na tabela Funcionario:

GRANT SELECT ON dbo.Funcionario(Codigo, Nome, Cargo) TO [usrChato]
GO

1 2	GRANT SELECT ON dbo.Funcionario(Codigo, Name, Cargo) TO [usrChato] GO

E agora, vou conseguir consultar essas colunas com o usuário usrChato:

O mesmo já não é possível caso eu tente incluir a coluna Salario:

EXEC AS USER = 'usrChato'
GO

SELECT Codigo, Nome, Cargo, Salario FROM dbo.Funcionario
GO

REVERT
GO

EXEC AS USER = 'usrChato'

SELECT Codigo, Name, Cargo, Salario FROM dbo.Funcionario

REVERT

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column ‘Salario’ of the object ‘Funcionario’, database ‘master’, schema ‘dbo’.

Ou ao tentar realizar o famoso SELECT * FROM:

EXEC AS USER = 'usrChato'
GO

SELECT * FROM dbo.Funcionario
GO

REVERT
GO

EXEC AS USER = 'usrChato'

SELECT * FROM dbo.Funcionario

REVERT

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column ‘Salario’ of the object ‘Funcionario’, database ‘master’, schema ‘dbo’.

E se eu criar uma view que tenha essa coluna? Será que o usuário conseguirá visualizar os dados, mesmo que ele não tenha permissão na Tabela ?

CREATE VIEW dbo.vwFuncionario_Completo
AS
SELECT 
    Codigo,
    Nome,
    Cargo,
    Salario
FROM
    dbo.Funcionario
GO

GRANT SELECT ON dbo.vwFuncionario_Completo TO [usrChato]
GO

CREATE VIEW dbo.vwFuncionario_Completo

SELECT

Codigo,

Name,

Cargo,

Salario

FROM

dbo.Funcionario

GRANT SELECT ON dbo.vwFuncionario_Completo TO [usrChato]

E agora vou tentar acessar a view:

Ouch!! O usuário conseguiu visualizar o salário!! E agora?
Bom, para resolver isso precisamos aplicar um comando de DENY na coluna de salário NA VIEW (na tabela original não adianta, pois o usuário tem acesso full na view):

DENY SELECT ON dbo.vwFuncionario_Completo(Salario) TO [usrChato] -- poderia ser public aqui, caso queira que ninguém acesse

1	DENY SELECT ON dbo.vwFuncionario_Completo(Salario) TO [usrChato] -- poderia ser public aqui, caso queira que ninguém acesse

Msg 230, Level 14, State 1, Line 4
The SELECT permission was denied on the column ‘Salario’ of the object ‘vwFuncionario_Completo’, database ‘master’, schema ‘dbo’.

É importante observar que é possível utilizar o Column Level Security (CLS) (também conhecido como Column Level Permission) em conjunto com as seguintes permissões:

SELECT
UPDATE
REFERENCES

Esse recurso não é exclusividade apenas de tabelas. Ele é compatível com views também, e funciona da mesma forma.

Como identificar as permissões a nível de coluna

Clique para visualizar o conteúdo

Caso você opte por começar a utilizar esse recurso, é importante saber como identificar em quais colunas determinado usuário tem acesso. Uma das formas de visualizar isso é utilizando a interface do SSMS (SQL Server Management Studio).

Abre o database onde está sua tabela, expanda a pasta “Security” e depois “Users”, clique com o botão direito do mouse e clique na opção “Properties”:

Na janela que abriu, clique na opção “Securables”, selecione a tabela que deseja visualizar, clique na aba “Explicit” para visualizar as permissões e role a lista até encontrar a permissão Select. Ao clicar sobre esse item, verá que o botão “Columns Permissions” agora está habilitado.

Para visualizar as colunas, clique na aba “Effective”

E caso queira ver mais detalhes sobre as permissões das colunas, clique no botão “Columns Permissions”

Uma outra forma de identificar as permissões a nível de coluna é através de query no banco:

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

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:

Controle por views ou permissão

Aqui chegamos a um ponto de vista muito pessoal.. Eu, particularmente, prefiro gerenciar as permissões através de views, pois a gestão é mais simples do que ter uma tabela ou view onde cada usuário acessa de um jeito. Como já havia comentado nesse artigo, utilizando a permissão a nível de coluna, evitamos a criação de vários objetos no banco apenas para fins de segurança e privacidade dos dados, mas ao mesmo tempo, o usuário precisará conhecer muito bem a estrutura da tabela e as colunas específicas que ele precisará consultar, já que um SELECT * não vai funcionar mais.

Eu acredito que as 2 soluções atendem muito bem ao que se refere restringir o acesso de usuários não autorizados à colunas sensíveis, cabe à equipe de banco escolher qual método ela vai se habituar melhor a trabalhar.

Caso você esteja utilizando a versão 2016 ou superior, pode também utilizar o recurso de Dynamic Data Masking para mascarar a saída de colunas que tenham dados sensíveis.

And that's it, folks!
I hope you enjoyed this tip and until next time!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Evitando consultas em determinadas colunas com o Column Level Security (CLS)

Utilizando view para restringir colunas

Utilizando o Column Level Security (CLS) para restringir colunas

Como identificar as permissões a nível de coluna

Controle por views ou permissão

You may also like...

Deixe uma respostaCancelar resposta

Utilizando view para restringir colunas

Utilizando o Column Level Security (CLS) para restringir colunas

Como identificar as permissões a nível de coluna

Controle por views ou permissão

You may also like...

Habilitando OLE Automation via T-SQL no SQL Server

SQL Server and Azure SQL: How to Delete or Update Data in Large Tables

SQL Server – Como renomear o servidor / instância no banco de dados quando o nome da máquina é alterado no SO

Deixe uma respostaCancelar resposta