SQL Server: Roles definidos por el usuario

  • Artículo

La llegada de SQL Server 2012” trae la posibilidad de crear roles de servidor definidos por el usuario; un avance enorme en la administración de SQL.

Denny Cherry

A pesar de que hemos podido crear roles de base de datos definidos por el usuario durante décadas para facilitar el proceso de otorgar permisos en el nivel de base de datos, en el nivel de instancia siempre ha habido solo nueve roles de servidor fijos (ocho, si usa versiones anteriores a SQL Server 2000; el rol bulkadmin se introdujo con SQL Server 2005). Ahora, con SQL Server 2012, finalmente podremos crear roles de servidor definidos por el usuario.

Durante años, la imposibilidad de crear estos roles se ha traducido en dolores de cabeza para la administración de SQL. ¿Qué hace cuando necesita otorgar derechos a varios usuarios o grupos en el nivel de instancia y desea mantener esos derechos sincronizados? Digamos que deseaba otorgar el derecho Ver estado del sistema a una gran cantidad de usuarios para que pudieran ver la información de bloqueo dentro de lo servidores de desarrollo. Habría tenido que otorgar este derecho uno a uno a cada grupo de dominio o usuario que lo necesitara.

Si todos los inicios de sesión se basaran en inicios de sesión de dominio, casi la única solución alternativa sería crear un grupo de dominio para todos los usuarios que necesiten este derecho (posiblemente necesite un grupo de dominio para cada servidor si los usuarios no obtienen el derecho en todos los servidores). Podría poner los usuarios en este grupo, crear un inicio de sesión asignado al grupo de dominio y luego otorgar a ese grupo el derecho Ver estado del servidor en el servidor. Sin embargo, tendría que hacer esto con mucho cuidado. De lo contrario, podría otorgar derechos de inicio de sesión a usuarios que no los tenían antes. Incluso es posible que le otorgue derechos a usuarios que no desea que los tenga.

Rol con T/SQL

Puede crear un rol de servidor definido por el usuario varias veces, incluido T/SQL, la interfaz de usuario de SQL Server Management Studio y Windows PowerShell. Si fuese a crear un rol de servidor definido por el usuario con T/SQL, usaría tres comandos diferentes. Primero, la instrucción Create Server Role crearía el rol de servidor definido por el usuario; segundo, Alter Server Rol agregaría un usuario al rol de servidor; finalmente, la instrucción Grant otorgaría los derechos necesarios al rol.

Estas tres instrucciones se muestran en el siguiente código, el que describe la creación de un rol de servidor definido por el usuario llamado "ViewServerState". Esto especifica que se agregó un usuario al rol "SomeFakeLogin" y que se otorgo al rol el derecho Ver estado del servidor. Para otorgar este derecho a usuarios adicionales, simplemente debe agregar esos usuarios al rol de servidor fijo a través de la línea Alter Server Role:

USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO

Podría usar Alter Server Role para quitar un usuario de un rol de servidor definido por el usuario. En lugar de usar la sintaxis Add Member, usaría Drop Member:

ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO

Cuando es momento de implementar uno o más roles definidos por el usuario contra diversas instancias de SQL Server, tendrá algunas opciones diferentes. Probablemente no quiere conectar con cada servidor uno a la vez para crear los roles de servidor definidos por el usuario. Una opción sería usar la característica SQL Server Management Studio. Esto le permite ejecutar scripts de T/SQL contra varias instancias.

También puede usar los componentes Windows PowerShell de SQL Server para implementar nuevos roles de servidor definidos por el usuario contra todas las instancias de SQL dentro de su organización. (Debido a las múltiples maneras de usar Windows PowerShell para implementar estos scripts, este proceso se encuentra fuera del alcance de este artículo).

SQL Server Management Studio

Puede realizar fácilmente la misma acción con la interfaz del usuario de SQL Server Management Studio. Para crear un rol de servidor definido por el usuario, conéctese a la instancia dentro del Explorador de objetos. Navegue a InstanceName | Seguridad | Roles de servidor dentro del explorador de objetos. Haga clic con el botón secundario en Roles de servidor y seleccione Nuevo rol de servidor en el menú contexto. Cuando se abra la ventana Nuevo rol de servidor, especifique el nombre del rol de servidor y el propietario, seleccione los elementos que pueden protegerse y luego los permisos que desea que tengan los miembros del rol (consulte la figura 1).

Figura 1 Especifique los derechos que desea que tenga un usuario en especial.

Una vez que complete los campos en la página General, seleccione la página Miembros (consulte la figura 2) y especifique los inicios de sesión de SQL Server que serán miembros de este rol de servidor definido por el usuario.

Figura 2 Seleccionar los miembros especificará los usuarios que tienen acceso a este rol de servidor.

Después de seleccionar los miembros del rol, seleccione la página Pertenencias. Con esto se especificarán los roles de servidor de los que este rol de servidor definido por el usuario será miembro. Si especifica un rol de servidor en esta pantalla, los usuarios dentro del rol de servidor definidos por el usuario también tendrán los derechos otorgados por ese rol de servidor.

Si fuese a crear un rol definido por el usuario y hacerlo miembro del rol de servidor fijo serveradmin (consulte la figura 3), no todos los miembros del rol de servidor definido por el usuario serían eficazmente miembros del rol de servidor fijo serveradmin. Tal como ocurre con los roles de base de datos o grupos de dominio anidados, debe tener mucho cuidado en el momento de anidar roles, para no otorgar a los usuarios derechos que no debieran tener de grupos de mayor valor.

Figura 3 Puede aplicar el rol definido por el usuario a otros roles de servidor.

Para anidar roles de servidor con T/SQL, seguirá teniendo que usar la instrucción Alter Server Role con la sintaxis Add Member. Por ejemplo, para hacer que el rol de servidor del usuario final ViewServerState sea miembro del rol de servidor fijo setupadmin, alteraría el rol de servidor fijo setupadmin. Luego agregaría el rol de servidor definido por el usuario ViewServerState como miembro:

ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO

Los roles de servidor definidos por el usuario tienen muchos usos. Hay docenas de derechos en el nivel de instancia que puede otorgar a un rol de servidor definido por el usuario para simplificar la administración de estos derechos. También podría crear un rol de administrador de base de datos secundario con algunos derechos, pero no todos los derechos de un administrador de sistemas. Podría crear un grupo AlwaysOnAdmin, lo que permitiría que los derechos realicen conmutación por error en una base de datos AlwaysOn (que se debe realizar desde SQL Server) sin contar con todos los derechos de un administrador de sistemas.

Existe diversos casos de uso para roles de servidor definidos por el usuario. Estos facilitarán considerablemente las cargas de trabajo de administración. También ayudarán a aumentar la seguridad de instancias de SQL Server, así como también estos nuevos roles de servidor reducirán las instancias de otorgar por accidente derechos inapropiados.

DennyCherry

Denny Cherry* tiene más de una década de experiencia en la administración de SQL Server. Sus áreas de experiencia incluyen arquitectura de sistemas, ajuste de rendimiento, replicación y solución de problemas. Posee varias certificaciones relacionadas con SQL Server, incluida Microsoft Certified Master para SQL Server 2008. Ha sido MVP de Microsoft SQL Server durante varios años y ha escrito numerosos libros y artículos técnicos sobre la administración de SQL Server y sobre la manera en que SQL Server se integra con diversas otras tecnologías.*

Contenido relacionado