Muchas veces nos encontramos con la necesidad de obtener los permisos que tiene un determinado Role de base de datos de SQL Server para conocer el alcance en cuanto a privilegios que posee una cierta Aplicación que haga uso de ese Rol.
Las tablas del SQL que contienen la información necesaria para extraer los permisos y los datos relacionados son: (para SQL Server 2008 o superior, revisar si para SQL Server 2005 o anterior)
database_permissions
database_principals
sysobjects
El procedimiento almacenado (store procedure) que permite listar todos los roles de una determinada tabla es:
sp_helprole
Con lo cual luego de haber obtenido el id del role que deseas filtrar y conociendo todas las tablas que tienen los datos de permisos construimos la siguiente consulta de ejemplo:
En la cual deberás reemplazar master por el nombre de la base de datos correspondiente.
El principal_id 16386 que está en la condición corresponde a ‘db_securityadmin’ (rol predeterminado de SQL)
El nombre del permiso en este caso es ‘DELETE’ pero puede intercambiarse o sumarse con ‘INSERT’, ‘DELETE’, ‘SELECT’, ‘UPDATE’, ‘CONNECT’, etc..
Va en texto para copy-paste-ar =;-)
USE master
GO
SELECT
ob.name,pe.permission_name, pe.state, pr.name
FROM
sys.database_permissions pe
join sys.database_principals pr on pe.grantee_principal_id = pr.principal_id
join sys.sysobjects ob on ob.id=pe.major_id
WHERE
pe.grantee_principal_id = 16386 — ID del Role de Base de datos (usar sp: sp_helprole)
and permission_name = ‘DELETE’ — permission: ‘INSERT’,’DELETE’,’SELECT’,’UPDATE’
ORDER BY
ob.name
Esta consulta normalmente puede ser utilizada antes dos eventos uno fortuito y otro no tanto, el primero ante un security analysis – assessment – hardening de una base de datos.
Y la otra no tan contenta es ante un Incidente de seguridad donde el equipo de gestión de incidentes solicitan un relevamiento y revisión de los permisos otorgados y/o intrínsecos de una aplicación.
Esta consulta puede ser tenido como base para ser moldeada según vuestra necesidad, agregando o quitando condiciones, mostrando más datos de las tablas de permisos y/o cambiando el orden.
Espero contribuya a la comunidad de ‘aseguradores’ de bases de datos…!