SQL Server - Evitando Consultas en Determinadas Columnas con Column-Level Security (CLS)

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 )

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]

Para los administradores de bases de datos más preocupados por la seguridad y privacidad de los datos, una característica que veo que se utiliza a menudo es el uso de vistas para limitar las columnas a las que ciertos usuarios tendrán acceso para ver.

En otras palabras, si quiero que nuestro usrChato tenga acceso a todas las columnas de la tabla Empleado, excepto Salario, puedo crear una vista sin esta columna y darle acceso selecto a la vista para este usuario:

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

Por lo tanto, si el usuario intenta acceder directamente a la tabla Empleado, recibirá un mensaje de error de falta de permiso:

Mientras que tendrá acceso a la vista, que tiene todos los campos, excepto Salario:

Y ahora, cada vez que tengo un usuario que necesita ver todas las columnas de esta tabla, excepto el salario, le otorgo acceso SELECT a esta vista (o administro el acceso a través de roles).

Pero ¿qué pasa cuando tengo varios usuarios, con diferentes necesidades para controlar a qué columnas accederán? ¿Crearé un montón de vistas en mi entorno para satisfacer cada necesidad?

Disponible desde la versión 2005 de SQL Server (probablemente incluso antes), existe una característica llamada Seguridad a nivel de columna (CLS), también conocida como Permiso a nivel de columna, que permite al usuario definir a qué columnas se aplicará un permiso determinado.

La gran ventaja de este enfoque es que de ninguna manera el usuario tendrá acceso a esta columna, incluso si crea vistas consultando la tabla en cuestión y tiene acceso a esta vista (en este caso tendremos que usar DENY). Además, evita crear múltiples objetos y vistas para satisfacer exclusivamente las necesidades de seguridad.

Sin embargo, una desventaja importante de este enfoque es que el usuario necesitará conocer muy bien la estructura de la tabla y las columnas específicas que necesitará consultar, ya que SELECT * ya no funcionará.

En el ejemplo de este artículo, definiré que el usrChato sólo tengo permiso para ver las columnas Código, Nombre y Posición en la mesa Empleado:

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

Y ahora podré consultar estas columnas con el usuario. usrChato:

Ya no es posible lo mismo si intento incluir la columna Salario:

EXEC AS USER = 'usrChato'
GO

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

REVERT
GO

O al intentar realizar el famoso SELECT * FROM:

EXEC AS USER = 'usrChato'
GO

SELECT * FROM dbo.Funcionario
GO

REVERT
GO

¿Qué pasa si creo una vista que tiene esta columna? ¿El usuario podrá ver los datos, incluso si no tiene permiso en la Tabla?

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

GRANT SELECT ON dbo.vwFuncionario_Completo TO [usrChato]
GO

Y ahora voy a intentar acceder a la vista:

¡¡Ay!! ¡¡El usuario pudo ver el salario!! ¿Y ahora?
Bueno, para resolver esto necesitamos aplicar un comando DENY a la columna de salario EN LA VISTA (en la tabla original no sirve, ya que el usuario tiene acceso completo a la vista):

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

Es importante tener en cuenta que puede utilizar la seguridad a nivel de columna (CLS) (también conocida como permiso a nivel de columna) junto con los siguientes permisos:

• SELECCIONAR
• ACTUALIZAR
• REFERENCIAS

Si decide comenzar a utilizar esta función, es importante saber cómo identificar a qué columnas tiene acceso un usuario en particular. Una de las formas de visualizar esto es utilizando la interfaz SSMS (SQL Server Management Studio).

Abre la base de datos donde está tu tabla, expande la carpeta “Seguridad” y luego “Usuarios”, haz clic derecho y haz clic en la opción “Propiedades”:

En la ventana que se abre, haga clic en la opción "Securables", seleccione la tabla que desea ver, haga clic en la pestaña "Explícito" para ver los permisos y desplácese hacia abajo en la lista hasta encontrar el permiso Seleccionar. Al hacer clic en este elemento, verá que el botón "Permisos de columnas" ahora está habilitado.

Para ver las columnas, haga clic en la pestaña "Efectivo"

Y si desea ver más detalles sobre los permisos de las columnas, haga clic en el botón "Permisos de columnas"

Otra forma de identificar permisos a nivel de columna es mediante una consulta a la base de datos:

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

Resultado: