[Transact-SQL] Como obtener los permisos de un Role de Base de datos

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:

SELECT PERMISSIONS

 

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…!

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s