Cómo asignar los permisos correctos en nuestra base de datos a posteriori

No es extraño encontrarnos con el escenario de tener una base de datos en la que los usuarios de la misma tienen todos los permisos posibles sobre todos los objetos. El cómo se ha llegado a esta situación puede deberse a diferentes causas:

• La base de datos era pequeña al principio y era correcto que el usuario pudiera acceder a todo, pero ahora esto ya no es así
• Se dejó a la aplicación la responsabilidad de asegurar el acceso, pero luego aparecieron más aplicaciones que acceden a la misma base de datos con el mismo usuario
• Desconocimiento/dejadez para asignar los permisos correctos a nivel de base de datos
• Etc.

Sea cual sea la causa inicial, el caso es que nos han pedido que asignemos los permisos justos (ni más, ni menos) que tienen que tener los usuarios de la base de datos para que no haya problemas de seguridad (al menos, no debido a esto)

Afortunadamente, SQL Server dispone de la característica SQL Server Audit desde la versión 2008, basada en Extended Events, la cual (como su nombre indica) permite auditar diferentes eventos que se producen en el motor de base de datos. Y, por supuesto, esto incluye el acceso a los objetos por parte de los usuarios y/o roles que a nosotros nos interese analizar. Por ejemplo, si quisiéramos conocer quién y cómo se accede a una determinada tabla de nuestra base de datos, los pasos a seguir serían 

1. Creamos la auditoría a nivel de servidor por medio de la instrucción CREATE SERVER AUDIT, donde especificamos dónde queremos almacenar los datos y sus propiedades relacionadas
2. Una vez disponible este objeto, creamos la especificación de la auditoría a nivel de base de datos con la instrucción CREATE DATABASE AUDIT SPECIFICATION. Como lo que queremos es saber quién accede a qué, usaremos el evento SCHEMA_OBJECT_ACCESS_GROUP

Una vez habilitada la auditoría, podemos acceder a los datos que registra la misma mediante el uso de la función sys.fn_get_audit_file (si es que especificamos que se almacenara en un fichero, lo cual será lo más normal) Esta función nos dirá quién (columna database_principal_name), cuándo (event_time) y cómo (action_id) se accedió a cada uno de los objetos (object_name) que componen nuestra base de datos de estudio, así que si dejamos la auditoría ejecutándose durante un tiempo prudencial, pasado ese tiempo podremos saber cuáles son los permisos justos que necesita cada usuario.

Así que si usamos diferentes usuarios en nuestras aplicaciones cliente, el problema tiene fácil solución usando SQL Audit. Ahora bien, puede ocurrir que o bien no tengamos la edición Enterprise (en SQL 2008 y 2008R2 esta característica sólo está disponible en las ediciones premium,  mientras que en SQL 2012 está habilitada en todas las ediciones) o que usemos el mismo usuario de conexión para todas las aplicaciones. En estos casos podemos echar mano de nuestro amigo SQL Server Profiler, el cual tiene un grupo de eventos relacionados con la seguridad y, dentro de éstos, uno que a nosotros nos va a servir para solucionar nuestro problema: Audit Schema Object Access

Una vez creada la traza (exportando su definición y ejecutándola mediante los procedimientos almacenados correspondientes), el único cambio que tendremos que hacer en nuestras aplicaciones es especificar en la cadena de conexión la propiedad ApplicationName (práctica recomendada en general)

Y al igual que hacíamos con SQL Audit, dejaremos la traza activa durante un tiempo suficiente como para capturar todos los diferentes accesos que se producen. Una vez hecho esto, sólo nos queda consultar esta información. Y en este punto podemos mostrar los datos en un formato que nos permita un fácil estudio de los mismos, pivotando los datos sobre los diferentes nombres de aplicación que hayamos establecido. Veamos más en detalle este proceso.

Lo primero es almacenar los datos en una tabla temporal para un manejo más rápido:

IF OBJECT_ID('tempdb..#auditTrace') IS NOT NULL
DROP TABLE #auditTrace SELECT t.DatabaseName,
t.ApplicationName,
t.LoginName,
t.ObjectName,
t.[permissions]
INTO #auditTrace
FROM sys.fn_trace_gettable('<ubicación de nuestro archivo de traza>', default) t
WHERE t.TextData IS NOT NULL
AND t.DatabaseName = '<nombre de nuestra base de datos>'

A continuación creamos un índice para agilizar las transformaciones que vamos a realizar:

CREATE INDEX nci_AnOn ON #auditTrace(ApplicationName, ObjectName) INCLUDE ([Permissions])

Y por último generamos dinámicamente los valores sobre los que vamos a pivotar los datos. Esta parte no sería necesaria si conociéramos previamente los diferentes nombres que se han establecido en ApplicationName, pero al hacerlo dinámico nos sirve para cualquier escenario:


DECLARE @cols AS NVARCHAR(MAX), @colsIsNull NVARCHAR(MAX)
SET @cols = STUFF
(
    (
     SELECT DISTINCT ',' + QUOTENAME(RTRIM((t.ApplicationName)))
FROM #auditTrace t
     FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1,1,''
)

SET @colsIsNull = STUFF
(
    (
     SELECT DISTINCT ',ISNULL(' + QUOTENAME(RTRIM((t.ApplicationName))) + ', '''') AS ' + QUOTENAME(RTRIM((t.ApplicationName)))
     FROM #auditTrace t
     FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1,1,''
)


Y por último, la instrucción en sí:

DECLARE @sSql  AS NVARCHAR(MAX)
SET @sSql = '
USE <nombre de nuestra base de datos>

EXEC( @sSql)

 Lo que obtenemos es un conjunto de resultados compuesto por el tipo (procedimiento almacenado, función, vista o tabla) y nombre de objeto y tantas columnas como aplicaciones diferentes tengamos, bajo las cuales encontraremos separados por comas los permisos reales (INSERT, SELECT, UPDATE, EXECUTE) que han necesitado para ejecutarse sin errores. 

Con esta información en la mano, identificar los diferentes roles que son necesarios en nuestra base de datos es sencillo, con lo que nuestro objetivo estaría logrado.

Espero os haya servido de ayuda.