Technological recipes that I've held to prepare some solutions DBA environment. (by Felipe Donoso Bastias)

29 Oct 2010

Respaldar o visualizar privilegios sobre objetos en SQL Server

¿Se han preguntado como en SQL Server yo puedo averiguar que privilegios existen en mi base de datos y que para que usuarios están siendo estos otorgados? Bueno, existe un método para averiguar esto, que también a su vez puede servir como método de Backup & Recovery. Lo he encontrado desde el el sitio web http://www.mssqltips.com.

Que un DBA posea a su alcance información de esta naturaleza puede ser de gran utilidad frente a determinadas circunstancias. En Oracle sabemos que existen vistas que entregan esta información como DBA_PROFILES, DBA_ROLES,etc. ¿Pero que hay para SQL Server? La Receta mágica es:


El procedimiento almacenado: sp_helprotect.

Este procedimiento almacenado entrega información de privilegios asignados sobre objetos de base de datos. He aquí un ejemplo de la información que entrega:

(Naturalmente ocupamos el usuario SA)

EXEC master.dbo.sp_helprotect;



Aún así para obtener un script más automatizado que nos entregue un procedimiento con todas las sentencias grant de nuestra base de datos, podemos utilizar las siguientes funciones para obtener un listado de comandos listos para usar:

-- Temporary table to hold results from sp_helprotect

drop table #TempPerms

go

CREATE TABLE #TempPerms (

[Owner] sysname NULL,

[Object] sysname NULL,

[Grantee] sysname NULL,

[Grantor] sysname NULL,

[ProtectType] NVARCHAR(9) NULL,

[Action] NVARCHAR(100) NULL,

[Column] NVARCHAR(300) NULL,

[WithGrant] VARCHAR(18) NULL DEFAULT ''

);

-- Let sp_helprotect do the work for us

INSERT INTO #TempPerms

([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])

EXEC master.dbo.sp_helprotect;


-- actualizamos por si acaso la columna ProtectType

-- en el caso de existieran valores 'Grant_WGO'

-- lo que quiere decir que se otorgaron privilegios

-- con permisos de otorgamiento hacia otros usuarios

UPDATE #TempPerms

SET [ProtectType] = 'Grant ', [WithGrant] = ' WITH GRANT OPTION'

WHERE [ProtectType] = 'Grant_WGO';


-- Build and output the permissions

SELECT

CASE [Owner]

WHEN '.' THEN ProtectType + ' ' + [Action] + ' TO [' + [Grantee] + ']' + [WithGrant] + ';'

ELSE CASE [Column]

WHEN '(All+New)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

WHEN '(All)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

WHEN '.' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'

ELSE ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] ([' + [Column] + ']) TO [' + Grantee + ']' + [WithGrant] + ';'

END

END AS 'Permissions'

FROM #TempPerms;

Al ejecutar la consulta verán que el resultado será el siguiente. (les sugiero presionar [CTRL+T] para obtener los resultados en modo texto más rápido.

Grant Execute ON [dbo].[fn_MSgensqescstr] TO [public];

Grant Execute ON [dbo].[fn_MSsharedversion] TO [public];

Grant Execute ON [dbo].[fn_sqlvarbasetostr] TO [public];

Grant Execute ON [dbo].[fn_varbintohexstr] TO [public];

Grant Execute ON [dbo].[fn_varbintohexsubstring] TO [public];

Grant Execute ON [dbo].[MS_sqlctrs_users] TO [public];

Esto funciona a la perfección en SQLServer2000 hacia atrás. Ahora para SQL Server 2005 y superior se tiene que realizar una consulta sobre las tablas sys.database_permissions, sys.database_principals y sys.objects. La query quedaría mas o menos así:

SELECT CASE dperms.state_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'

ELSE state_desc

END

+ ' ' + permission_name + ' ON ' +

CASE dperms.class

WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'

WHEN 1 THEN

CASE dperms.minor_id

WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'

ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'

END

WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'

WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'

WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'

WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'

WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'

END

+ ' TO [' + dprins.[name] + ']' +

CASE dperms.state_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'

ELSE ';'

END COLLATE database_default AS 'Permissions'

FROM sys.database_permissions dperms

INNER JOIN sys.database_principals dprins

ON dperms.grantee_principal_id = dprins.principal_id

LEFT JOIN sys.columns col

ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id

LEFT JOIN sys.objects obj

ON dperms.major_id = obj.object_id

LEFT JOIN sys.schemas sch

ON obj.schema_id = sch.schema_id

LEFT JOIN sys.asymmetric_keys asymm

ON dperms.major_id = asymm.asymmetric_key_id

LEFT JOIN sys.symmetric_keys symm

ON dperms.major_id = symm.symmetric_key_id

LEFT JOIN sys.certificates certs

ON dperms.major_id = certs.certificate_id

WHERE dperms.type <> 'CO'

AND dperms.major_id > 0;


Eso es todo.

Aquí la fuente origina la Fuente Original:

http://www.mssqltips.com/tip.asp?tip=2142

Share:

Copyright © DBA TIPS | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com | Free Blogger Templates