Roles en SQL SERVER

Buenas tardes queridos lectores, ya poco a poco va pasando la cuarentena y empiezan a abrir todo, el día de hoy, les traigo el post de los ROLES en SQL SERVER, una manera más, fácil, práctica y correcta de administrar los permisos de las bases de Datos. Pero como siempre, pasemos a la práctica que es lo que nos gusta y como aprendemos

Los roles sirven para administrar con facilidad los permisos de bases de datos.

Existen dos tipos de roles: los roles de usuario y los roles de servidor, en los roles de servidor, no entraremos en detalles, basta con mencionar que dentro de los roles de servidor, existe un rol llamado sysadmin, que nos proporcionará el control total del servidor para un login especifico, es decir seremos un super usuario (sa). Los roles de base de datos, que son los creados por el usuario también se dividen en dos:

• Roles fijos de base de datos
• Roles de base de Datos definidos por el usuario

Nos enfocaremos en los segundos, aunque explicaremos un poco los primeros.

Los roles fijos de base de datos se definen en el nivel de base de datos y existen en cada una de ellas. Los miembros de los roles de base de datos db_owner pueden administrar la pertenencia a roles fijos de base de datos, es decir, quien tenga el rol db_owner en la base de datos, será dueño y señor de la base de datos, no del server como en el caso de sysadmin También hay algunos roles de base de datos con fines especiales en la base de datos msdb.

A continuación se mostrará una tabla con los permisos fijos de base de datos, es decir, los roles que tienen todas las bases de datos.

Nombre del rol fijo de base de datos	Descripción
db_owner	Los miembros del rol fijo de base de datos db_owner pueden realizar todas las actividades de configuración y mantenimiento en la base de datos y también pueden quitar la base de datos en SQL Server. (En SQL Database y SQL Data Warehouse, algunas actividades de mantenimiento requieren permisos a nivel de servidor y los roles db_ownersno las pueden realizar).
db_securityadmin	Los miembros del rol fijo de base de datos db_securityadmin pueden modificar la pertenencia a roles únicamente para roles personalizados y administrar permisos. Los miembros de este rol pueden elevar potencialmente sus privilegios y se deben supervisar sus acciones.
db_accessadmin	Los miembros del rol fijo de base de datos db_accessadmin pueden agregar o quitar el acceso a la base de datos para inicios de sesión de Windows, grupos de Windows e inicios de sesión de SQL Server .
db_backupoperator	Los miembros del rol fijo de base de datos db_backupoperator pueden crear copias de seguridad de la base de datos.
db_ddladmin	Los miembros del rol fijo de base de datos db_ddladmin pueden ejecutar cualquier comando del lenguaje de definición de datos (DDL) en una base de datos.
db_datawriter	Los miembros del rol fijo de base de datos db_datawriter pueden agregar, eliminar o cambiar datos en todas las tablas de usuario.
db_datareader	Los miembros del rol fijo de base de datos db_datareader pueden leer todos los datos de todas las tablas de usuario.
db_denydatawriter	Los miembros del rol fijo de base de datos db_denydatawriter no pueden agregar, modificar ni eliminar datos de tablas de usuario de una base de datos.
db_denydatareader	Los miembros del rol fijo de base de datos db_denydatareader no pueden leer datos de las tablas de usuario dentro de una base de datos.

Ahora procederemos a crear roles a nivel de base de Datos definidos por el usuario, y para realizar esto disponemos de dos métodos, el método grafico y el método script.

Método gráfico

Para crear roles de bases de datos definidos por el usuario, en el método gráfico, es decir, desde el Management Studio realizaremos los siguientes pasos:

• Posicionarnos en la base de Datos a la cual le agregaremos los roles de base de datos definidos por el usuario y expandirla
• Expandir la carpeta Seguridad
• Expandir la carpeta Roles
• Posicionarnos sobre la opción Roles de base de datos y dar clic con el botón derecho del mouse y seleccionar la opción Nuevo rol de base de datos

Crear rol de base de datos

• En el campo Nombre del rol colocaremos el nombre que le asignaremos al rol
• En el campo Propietario, colocaremos que usuario será dueño del rol, en caso de no ponerle nada, el dueño será el super usuario
• En el campo, esquemas propietarios, asignaremos los esquemas que será dueños de ese rol
• En la lista Miembros del rol, agregaremos a los usuarios que serán dueños de ese rol mediante el botón ADD

Roles en SQL SERVER

• En la sección Elementos protegibles, asignaremos todos los objetos a los cuales les daremos permisos mediante el botón Buscar, podremos buscar un objeto en especifico o por tipo, o por esquema, en este caso pondremos una única tabla
• En la lista inferior, nos aparecerán los permisos y las opciones para conceder o denegar, en este ejemplo voy a seleccionar el Select en la columna Denegar, esto quiere decir que todos los miembros de este rol, no podrán ver esta tabla, y haremos la prueba con mas de un usuario.

• Dar clic en el botón Aceptar, y ahora realizaremos la prueba

Si recordamos, el usuario sql_test, pertenece al rol, por lo cual, no debería de tener acceso a seleccionar la tabla Tabla1, del esquema Ejemplos, vamos a comprobar que eso sea cierto

Si ejecutamos con el usuario sql_test el siguiente comando, veremos un error como este:

use PRUEBAS

go

select * from ejemplos.tabla1

Msg 229, Level 14, State 5, Line 4

The SELECT permission was denied on the object 'Tabla1', database 'PRUEBAS', schema 'EJEMPLOS'.

Como vemos, con ese usuario, al estar dentro del rol, no podremos realizar un select a esa tabla, sin embargo con otro usuario si lo podremos realizar, ahora vamos a borrar el rol, para realizar lo mismo, pero ahora de manera de script.

Para borrar el rol, realizaremos los siguientes pasos:

• Posicionarnos en la base de Datos a la cual le agregaremos los roles de base de datos definidos por el usuario y expandirla
• Expandir la carpeta Seguridad
• Expandir la carpeta Roles
• Expandir la carpeta Roles de base de datos y seleccionar el nombre del rol a eliminar, dar clic con el botón derecho del mouse y seleccionar la opción Eliminar

Método script

Para crear roles de bases de datos definidos por el usuario, en el método script realizaremos lo siguiente:

use PRUEBAS

go

create role [Rol - Ejemplo]

Mediante esta sentencia creamos el rol de base de Datos definido por el usuario de nombre Rol - Ejemplo, si queremos hacer propietario del rol a un esquema en particular ejecutaríamos una sentencia como esta:

use PRUEBAS

go

ALTER AUTHORIZATION ON SCHEMA::[EJEMPLOS] TO [Rol - Ejemplo]

Aunque para el ejemplo no lo usaremos, ahora vamos a agregar al rol, al usuario sql_test

USE [PRUEBAS]

GO

ALTER ROLE [Rol - Ejemplo] ADD MEMBER [sql_test]

GO

Como vemos, mediante esta sentencia, agregamos al rol, el usuario sql_test, pero aun falta lo más importante, denegar el permiso de select en la tabla Ejemplos.Tabla1

USE [PRUEBAS]

GO

deny select on object::ejemplos.tabla1 to [Rol - Ejemplo]

go

En esa sentencia estamos denegando al objeto ejemplos.tabla1 el permiso de select, en el Rol Rol - Ejemplo, ahora podemos volver a hacer la prueba para comprobar que, en efecto, no se puede realizar el select, el uso de esto es que pueden tener muchos usuarios, con un mismo rol, en post pasado habíamos visto como darle permisos a un usuario en particular, pero eso seria repetir los permisos con cada usuario, de esta manera, al general un rol, bastara con agregar al rol, los usuarios.

Hasta aquí el post queridos lectores, espero que haya sido de su agrado y utilidad, si tienen alguna duda, pregunten sin pena, espero compartan, comenten y si pueden dar clic a algún anuncio me ayudaría bastante.

Saludos