Administrar seguridad en SQL Server mediante sintaxis

Regresar al índice

Hola!
En esta oportunidad vamos a crear usuarios, roles y otorgar permisos mediante Transact SQL. Es momento de poner manos a la obra. Aprendamos!

Para crear un usuario, debemos realizar los siguientes pasos:

-  Crear el usuario asignándole una cuenta de inicio de sesión.
-  Asignar el usuario a una base de datos y darle permiso de acceso a ella
- Asociar el usuario a un rol la cuál contiene unos determinados permisos para acceder a la base de datos.

• Cuentas de inicio de sesión

Cuando un usuario se conecta a un servidor, lo hace a través de una cuenta facilitada por el administrador de la base de datos. Esta cuenta tiene una serie de permisos que el administrador da, estos permisos pueden ser de restricción a determinadas tablas, o por ejemplo que un usuario pueda solo insertar, o seleccionar en determinadas tablas.

Existen dos formas de entrar a la base de datos, bien mediante el usuario de windows o mediante el usuario de la base de datos.

Mediante el usuario de windows, facilitamos al equipo desde el que nos conectamos, los recursos del servidor de sql server, pudiendo incluso bloquear el equipo desde el que se conecta el usuario.

• Acceso a una base de datos

Para que un usuario tenga acceso a una base de datos, después de crearle una cuenta de inicio de sesión, y asociársela a una base de datos, hemos de darle permisos para que pueda acceder a esa base de datos. Posteriormente, mediante roles le indicaremos que es lo que puede hacer en esa base de datos.

Puede tener permisos para una base de datos o para todo el servidor.

• Roles

Agrupamos usuarios y sobre estos usuarios daremos permisos a ese grupo de usuarios.

Los roles, se usan para establecer los permisos que un grupo de usuario tendrá sobre una determinada base de datos.

Una vez creado el usuario, y asignada la base de datos, podemos asociar este usuario al rol que queramos para controlar los permisos que tiene sobre la base de datos asignada.

Hay una serie de roles y usuarios predefinidos en la base de datos.

• Crear un usuario

Para crear un usuario y poder usarlo, hemos de establecer todos los pasos descritos en los puntos anteriores.

Estos pasos, los realizaremos todos en el analizador de consultas:

1. Creamos el inicio de sesión del usuario.

SP_ADDLOGGIN

Crea una cuenta de inicio para un usuario y lo asigna a una base de datos.

Sintaxis:

SP_ADDLOGIN ‘Usuario’, ‘Contraseña’, ‘Base de datos’

EXEC SP_ADDLOGIN 'Pepe', 'Pepe','Hospital_BD'

Creado nuevo inicio de sesión.

SP_DROPLOGIN

Borra una cuenta de inicio de un usuario, siempre y cuando el usuario no esté conectado y no tenga permisos sobre ninguna base de datos (si los tiene hemos de revocarlos antes de borrar la cuenta de inicio y el usuario)

Sintaxis:

SP_DROPLOGIN 'Usuario'

EXEC SP_DROPLOGIN 'Pepe'

Inicio de sesión quitado.

Nota: Si el usuario tiene permisos asignados sobre una base de datos, debemos quitar primero esos permisos antes de borrarlo, ya que sino dará error al intentarlo.

2. Asignamos permisos al usuario sobre la base de datos que queramos. Para ello usaremos el procedimiento almacenado de sistema SP_GRANTDBACCESS.

SP_GRANTDBACCESS.

Sintaxis:

SP_GRANTDBACCESS ‘Usuario’

SP_GRANTDBACCESS 'Pepe'

Concedido a la base de datos acceso a 'Pepe'.

Si queremos revocar estos permisos al usuario, usaremos el procedimiento almacenado de sistema SP_REVOKEDBACCESS.

SP_REVOKEDBACCESS

Sintaxis:

SP_REVOKEDBACCESS ‘Usuario’

Es decir en este caso sería:

EXEC SP_REVOKEDBACCESS 'Pepe'

El usuario se ha quitado de la base de datos actual.

4. Probamos el usuario creado entrando en el analizador de consulta con su login y password

Una vez en el analizador de consultas, solo tendremos acceso a la base de datos asociada al usuario y las bases de datos de ejemplo.

5. Asociar el usuario al rol que queramos.

Crear roles (grupos de usuarios) y establecer sus permisos.

Para ello crearemos el rol mediante el procedimiento almacenado de sistema SP_ADDROLE

EXEC SP_ADDROLE 'Becarios'

Agregado el nuevo rol.

Una vez realizado esto, vemos que en el apartado “Roles” de la base de datos, está el rol creado, si hacemos doble click sobre ella, vemos que no tiene usuarios asignados:

Para establecer los permisos del rol que hemos creado, usaremos el comando GRANT. 

Sintaxis:

GRANT Permisos

ON Tabla / Objeto

To Rol / Usuarios

Es decir en este caso sería:

GRANT INSERT, UPDATE, DELETE
ON Emp
To Becarios

También podemos establecer permisos directamente sobre los usuarios, poniendo el nombre de estos en lugar del Rol.

GRANT INSERT, UPDATE, DELETE
ON Emp
To Pepe, Pepa

Para denegar permisos sería mediante el comando DENY.

Sintaxis:

DENY Permisos

On Tabla

To Rol

DENY SELECT
ON Emp
TO Becarios

Para revocar permisos ya asignados mediante GRANT, usaremos el comando REVOKE 

REVOKE Permisos

On Tabla

To Rol

REVOKE UPDATE
On Emp
to Becarios

• Asociar usuarios a un Rol.

Para asociar un usuario a una función usaremos el procedimiento almacenado de sistema SP_ADDROLEMEBER. 

Sintaxis:

SP_ADDROLEMEMBER ‘Rol’, ‘Usuario’

Es decir, en este caso sería:

EXEC SP_ADDROLEMEMBER 'Becarios', 'Pepe'

'Pepe' agregado al Rol'Becarios'.

Una vez realizados estos pasos, si entramos en el analizador de consultas con el usuario Pepe, si intentamos realizar una consulta no permitida, nos advertirá mediante un mensaje que no podemos realizar la consulta:

Select * from emp

Servidor: mensaje 229, nivel 14, estado 5, línea 1

Permiso SELECT denegado para el objeto 'EMP', base de datos 'Hospital_BD', propietario 'dbo'.

Para eliminar un usuario de un Rol, usaremos el procedimiento almacenado de sistema SP_DROPROLEMEMBER. 

Sintaxis:

SP_DROPROLEMEMBER ‘Rol’, ‘Usuario’

Es decir en este caso sería:

EXEC SP_DROPROLEMEMBER 'Becarios', 'Pepe'

'Pepe' quitado del Rol 'Becarios'.

Una vez tengamos la función sin usuarios asignados, podremos borrarla, para borrarla usaremos el procedimiento almacenado de sistema SP_DROPROLE. 

Sintaxis:

SP_DROPROLE ‘Rol’

En este caso sería:

EXEC SP_DROPROLE 'Becarios'

Rol quitado.

SP_ADDSRVROLEMEMBER : Añade a un rol de sistema el usuario que queramos.

Sintaxis: 

SP_ADDSRVROLEMEMBER 'Usuario','Rol'

Damos permiso al usuario para modificar y crear bases de datos.

EXEC SP_ADDSRVROLEMEMBER 'Pepe','DbCreator'

SP_DROPSRVROLEMEMBER : Quita a un usuario del rol del sistema que seleccionemos.

Sintaxis: 

SP_ADDSRVROLEMEMBER 'Usuario','Rol'

Quitamos del Rol al usuario ‘Pepe’

EXEC SP_ADDSRVROLEMEMBER 'Pepe','DbCreator'

'PEPE' quitado del Rol 'DBCREATOR'.

Otro método que tenemos para poder ofrecer permisos es con la opción With Grant Option.

Con esta opción después de la sentencia de conceder derechos, permito al usuario al que estoy concediendo permisos pueda conceder permisos a su vez sobre los privilegios que se le han otorgado.

Sintaxis:

Grant Select, Insert, Update, Delete

On Tabla / Vista

To Usuario / Funcion

With Grant Option

Ejemplo:

Un administrador concede permisos a Pepe para poder hacer selecciones sobre la tabla Emp.

Usuario -> Administrador

Grant Select
On Emp
To Pepe
With Grant Option

Este usuario podrá a su vez conceder permisos a otro usuario sobre sus privilegios, es decir, sobre la tabla emp y solamente con Select.

Usuario -> Pepe

Grant Select
On Emp
To Luisa

La cadena termina aquí, ya que Pepe no ha concedido permisos a Luisa para que pueda conceder permisos a su vez.

Si el administrador revocase los permisos Select a Pepe, estos permisos se revocan a su vez sobre el usuario Luisa, ya que no tiene permisos de nadie más para ver la Tabla.

Otra opción es que el usuario Pepe conceda permisos a Luisa pero solamente sobre unos determinados campos.

Grant Select (Emp_no, Apellido, Fecha_alt, Dept_no)
On Emp
To Luisa

Esto se puede utilizar también pero sería más conveniente utilizar una Vista.

DESCARGAR PUBLICACIÓN

Espero haber ayudado en algo. Hasta la próxima oportunidad!

Regresar al índice

Twittear