Sacar permisos de Base de Datos SQL scripts
Cuando trabajamos con bases de datos SQL, es crucial gestionar adecuadamente los permisos para asegurar que solo los usuarios autorizados puedan acceder y modificar la información. Un aspecto vital de la administración de bases de datos es la capacidad de extraer y revisar los permisos asignados a diferentes roles y usuarios. En este blog, exploraremos cómo lograr esto de manera eficiente usando scripts SQL.
Importancia de la Gestión de Permisos en Bases de Datos SQL
La gestión de permisos es esencial por varias razones:
- Seguridad: Restringir el acceso a información sensible protege contra accesos no autorizados y posibles brechas de seguridad.
- Control: Permite definir quién puede ver y modificar datos específicos, ayudando a mantener la integridad de la información.
- Auditoría: Facilita el seguimiento de actividades y cambios realizados por diferentes usuarios, crucial para auditorías y cumplimiento normativo.
Conceptos Clave: Roles y Permisos en SQL
Antes de profundizar en cómo extraer permisos, es importante entender algunos conceptos básicos:
- Roles: Son grupos de permisos que se pueden asignar a usuarios o a otros roles. SQL Server tiene roles predefinidos como
db_owner
ydb_datareader
, y permite la creación de roles personalizados. - Permisos: Son privilegios que determinan las acciones que los usuarios pueden realizar en la base de datos, como
SELECT
,INSERT
,UPDATE
, yDELETE
.
Script para Extraer Roles de la Base de Datos SQL
A continuación, presento un script que te permitirá extraer los roles definidos en tu base de datos. Este script selecciona los roles no predefinidos y genera las declaraciones SQL necesarias para recrearlos.
SELECT
'CREATE ROLE [' + dbU.[name] + '];'
FROM
sys.database_principals AS dbU
WHERE TYPE = 'R' AND is_fixed_role = 0
GO
Explicación del Script
- sys.database_principals: Esta vista del sistema contiene información sobre los principales (usuarios y roles) en la base de datos.
- WHERE TYPE = ‘R’ AND is_fixed_role = 0: Filtra los resultados para incluir solo los roles personalizados, excluyendo los roles predefinidos.
Ejemplo de Resultado
Supongamos que tienes los siguientes roles personalizados en tu base de datos: analyst
y developer
. El script generará el siguiente resultado:
CREATE ROLE [analyst];
CREATE ROLE [developer];
Ampliando el Script para Incluir Permisos de los Roles
Para hacer el script más completo, puedes ampliarlo para extraer no solo la creación de roles sino también sus permisos asociados. Aquí te dejo un ejemplo de cómo podrías hacerlo:
SELECT
'CREATE ROLE [' + dbR.[name] + '];' + CHAR(13) +
STUFF((
SELECT CHAR(13) + 'GRANT ' + dp.permission_name + ' ON ' +
dp.class_desc + '::[' + OBJECT_NAME(dp.major_id) + '] TO [' + dbR.[name] + '];'
FROM sys.database_permissions dp
WHERE dp.grantee_principal_id = dbR.principal_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
FROM
sys.database_principals AS dbR
WHERE
TYPE = 'R' AND is_fixed_role = 0
GO
Explicación del Script Ampliado
- sys.database_permissions: Esta vista contiene información sobre los permisos a nivel de base de datos.
- GRANT … TO: Genera las declaraciones
GRANT
necesarias para cada permiso asignado a los roles personalizados.
Ejemplo de Resultado
Si el rol analyst
tiene permisos SELECT
en la tabla Customers
, y el rol developer
tiene permisos INSERT
en la tabla Orders
, el resultado sería:
CREATE ROLE [analyst];
GRANT SELECT ON OBJECT::[Customers] TO [analyst];
CREATE ROLE [developer];
GRANT INSERT ON OBJECT::[Orders] TO [developer];
Ampliando el Script para Incluir Permisos de los Usuarios SQL
Además de los roles, también es importante extraer los permisos asignados directamente a los usuarios. Aquí tienes un script que extrae los usuarios de la base de datos y sus permisos:
SELECT
'CREATE USER [' + dp.name + '] FOR LOGIN [' + dp.name + '];' + CHAR(13) +
STUFF((
SELECT CHAR(13) + 'GRANT ' + p.permission_name + ' ON ' +
p.class_desc + '::[' + OBJECT_NAME(p.major_id) + '] TO [' + dp.name + '];'
FROM sys.database_permissions p
WHERE p.grantee_principal_id = dp.principal_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
FROM
sys.database_principals dp
WHERE
dp.type_desc = 'SQL_USER'
GO
Explicación del Script de Usuarios
- sys.database_principals: Se usa nuevamente para obtener los usuarios de la base de datos.
- CREATE USER … FOR LOGIN: Crea las declaraciones para recrear los usuarios en la base de datos.
- GRANT … TO: Añade las declaraciones
GRANT
para cada permiso asignado a los usuarios.
Ejemplo de Resultado
Si el usuario john
tiene permisos SELECT
en la tabla Customers
, y el usuario jane
tiene permisos INSERT
en la tabla Orders
, el resultado sería:
CREATE USER [john] FOR LOGIN [john];
GRANT SELECT ON OBJECT::[Customers] TO [john];
CREATE USER [jane] FOR LOGIN [jane];
GRANT INSERT ON OBJECT::[Orders] TO [jane];
Combinando Todo en un Solo Script
Para mayor eficiencia, puedes combinar los scripts de roles y usuarios en un solo script que extraiga y recree tanto roles como usuarios y sus permisos. Aquí te dejo el script combinado:
-- Extraer Roles
SELECT
'CREATE ROLE [' + dbR.[name] + '];' + CHAR(13) +
STUFF((
SELECT CHAR(13) + 'GRANT ' + dp.permission_name + ' ON ' +
dp.class_desc + '::[' + OBJECT_NAME(dp.major_id) + '] TO [' + dbR.[name] + '];'
FROM sys.database_permissions dp
WHERE dp.grantee_principal_id = dbR.principal_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
FROM
sys.database_principals AS dbR
WHERE
TYPE = 'R' AND is_fixed_role = 0
GO
-- Extraer Usuarios
SELECT
'CREATE USER [' + dp.name + '] FOR LOGIN [' + dp.name + '];' + CHAR(13) +
STUFF((
SELECT CHAR(13) + 'GRANT ' + p.permission_name + ' ON ' +
p.class_desc + '::[' + OBJECT_NAME(p.major_id) + '] TO [' + dp.name + '];'
FROM sys.database_permissions p
WHERE p.grantee_principal_id = dp.principal_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
, 1, 1, '')
FROM
sys.database_principals dp
WHERE
dp.type_desc = 'SQL_USER'
GO
Explicación del Script Combinado
Este script ejecuta tanto la extracción de roles como de usuarios en una sola consulta, generando todas las declaraciones necesarias para recrear la estructura de permisos de tu base de datos.
Conclusión
La correcta gestión de permisos es vital para la seguridad y el control en una base de datos SQL. Con los scripts proporcionados, puedes extraer y revisar los roles y permisos de tu base de datos de manera eficiente. Esto no solo facilita la administración, sino que también te permite mantener un control más riguroso sobre quién tiene acceso a qué información.
Implementar y utilizar estos scripts te ayudará a fortalecer la seguridad de tu base de datos y garantizar que solo los usuarios autorizados tengan los permisos adecuados. No dudes en personalizar estos scripts según las necesidades específicas de tu entorno.
SQL: ¿Configurar los Archivos LDF y MDF en Unidades Distintas es Importante?
dm_exec_procedure_stats en SQL Server
Monitoreo y Mantenimiento SQL: Mantén Tu Base de Datos Saludable