Funciones de Servidor

SEGURIDAD en SQL Server

Roles de Servidor – Funciones fijas de servidor

La administración de la seguridad con los elementos de una base de datos es una tarea que necesita de mucho cuidado, es necesario que los asegurables presentes en la base de datos tengan los permisos requeridos y asignados adecuadamente a las entidades de seguridad.

Niveles de Seguridad en SQL Server

La seguridad se puede administrar para el servidor y también para una base de datos específica. Cada nivel tiene sus elementos asegurables y sus entidades de seguridad.

Los niveles de seguridad son:

1. A nivel de Servidor

En el nivel de servidor podemos administrar las siguientes entidades de seguridad

* Funciones de servidor
* Inicios de sesión

2. A nivel de Base de datos

En el nivel de base de datos podemos trabajar con las siguientes entidades de seguridad

* Funciones de base de datos
* Usuarios de base de datos
* Funciones de aplicación

Se recomienda la revisión permanente de los cambios en los permisos.

Seguridad a nivel de Servidor

Funciones Fijas de servidor (Roles de Servidor)

Permiten administrar los permisos sobre los objetos del servidor
Desde SQL Server 2012 se pueden crear nuevas funciones de servidor y asignar permisos en los asegurables a nivel de servidor.

Las funciones fijas de servidor o roles de servidor son:

Rol fijo de servidor Descripción
bulkadmin Los miembros del rol fijo de servidor bulkadmin pueden ejecutar las opciones de copia masiva de datos usando la instrucción BULK INSERT.
diskadmin El rol fijo de servidor diskadmin se usa para administrar archivos de disco.
dbcreator Los miembros del rol fijo de servidor dbcreator pueden crear, modificar, quitar y restaurar cualquier base de datos.
public Cada inicio de sesión de SQL Server pertenece al rol de servidor public.
sysadmin Los miembros del rol fijo de servidor sysadmin son los administradores del servidor y pueden realizar cualquier actividad sin restricciones.
processadmin Los miembros del rol fijo de servidor processadmin pueden finalizar los procesos que se ejecuten en una instancia de SQL Server.
serveradmin Los miembros del rol fijo de servidor serveradmin pueden cambiar las opciones de configuración del servidor y apagarlo.
securityadmin Los miembros del rol fijo de servidor securityadmin administran los inicios de sesión y sus propiedades.

Pueden administrar los permisos de los asegurables a nivel de servidor usando GRANT, DENY y REVOKE.

Pueden administrar los permisos de los asegurables a nivel de base de datos usando GRANT, DENY y REVOKE siempre que tengan acceso a una base de datos.

A nivel de servidor pueden  restablecer contraseñas para  los inicios de sesión de SQL Server.

setupadmin Los miembros del rol fijo de servidor setupadmin pueden agregar y quitar servidores vinculados mediante instrucciones Transact-SQL.

Cada función fija de servidor tiene una relación de permisos asignados para cada inicio de sesión que pertenezca a estos roles.




La imagen muestra las diferentes funciones fijas de servidor y los permisos. Son 9 funciones fijas.

 

Crear Roles o funciones fijas de servidor

Desde SQL Server 2012 es posible crear nuevos roles de servidor.

Instrucción Create server role

Permite crear un rol de servidor.

Sintaxis

Create server role NombreRol [Authorization InicioSesion]

Ejemplos

Ejercicio 1
Crear un rol de servidor llamado Administradores autorizado a SecurityAdmin
use master
Create server role Administradores authorization SecurityAdmin
go

Ejercicio 2
Crear un rol de servidor llamado Vendedores autorizado a GerenteVentas
use master
Create server role Vendedores authorization GerenteVentas
go

Ejercicio 3
Listar los roles de servidor
select name, type_desc from sys.server_principals where type = ‘R’
go

Para listar los roles fijos, no incluye los creados por el usuario
sp_helpsrvrole
go

Ejercicio 4
Crear un rol de servidor llamado Logistica autorizado a ProcessAdmin
El Script debe comprobar que el rol no existe.

use master
if not exists (select name, type_desc from sys.server_principals where type = ‘R’ and name =’Logistica’)
Begin
Create server role Logistica authorization ProcessAdmin
End
go

Modificar roles de servidor

Instrucción: Alter server role
Permite agregar o quitar miembros al rol y cambiarle de nombre.

Sintaxis

Alter server role NombreRol
ADD MEMBER InicioSesion |
DROP MEMBER InicioSesion |
WITH NAME = NuevoNombre

Para agregar o quitar inicios de sesión a un rol se pueden usar los siguientes procedimientos almacenados del sistema.
Para agregar
sp_addsrvrolemember  InicioSesión, Rol
Para quitar
sp_dropsrvrolemember  InicioSesión, Rol

Ejemplos

Ejercicio 1
Crear un inicio de sesión llamado EmpresaDBA y hacerlo miembro de Sysadmin

use master
Create login EmpresaDBA with password = ‘Unodostres’
go
Alter server role sysadmin add member EmpresaDBA
go

Para visualizar los miembros del rol Sysadmin
sp_helpsrvrolemember Sysadmin
go

Ejercicio 2
Cambiar el nombre del rol Vendedores por FuerzaVentas

use master
Alter server role Vendedores with name= FuerzaVentas
go

Ejercicio 3
Quitar el inicio de sesión EmpresaDBA de sysadmin y hacerlo miembro de SecurityAdmin

use master
Alter server role sysadmin drop member EmpresaDBA
Alter server role SecurityAdmin add member EmpresaDBA
go

Eliminación de un rol de servidor

Instrucción: Drop server role
Permite eliminar un rol creado

Sintaxis:

Drop server role NombreRol

Nota: El rol debe estar vacío, es decir, no debe tener miembros (Logins).

El listado de los roles de servidor y los inicios de sesión que son miembros de cada uno de ellos.

select
P.name As ‘Rol de Servidor’,
R.member_principal_id As ‘Código Inicio de Sesión’,
L.name As ‘Nombre Inicio de Sesión’
from sys.server_principals As P
join sys.server_role_members As R on P.principal_id = R.role_principal_id
join sys.sql_logins As L on R.member_principal_id = L.principal_id
where P.type = ‘R’

go

Ejemplos

Ejercicio 1
Eliminar el rol FuerzaVentas
use master
Drop server role FuerzaVentas
go

Ejercicio 2
Eliminar el rol Gerentes
use master
Drop server role Gerentes
go

Note que aparece el mensaje: El rol tiene miembros. Debe estar vacío antes de quitarlo.




Para ver los miembros se utiliza la siguiente instrucción:

select
P.name As ‘Rol de Servidor’,
R.member_principal_id As ‘Código Inicio de Sesión’,
L.name As ‘Nombre Inicio de Sesión’
from sys.server_principals As P
join sys.server_role_members As R on P.principal_id = R.role_principal_id
join sys.sql_logins As L on R.member_principal_id = L.principal_id
where P.type = ‘R’

El miembro que debemos eliminar es: GerenteVentas
Alter server role Gerentes drop member GerenteVentas
go
Drop server role Gerentes
go