Funciones de base de datos

 

Funciones de Base de datos (Roles de Base de Datos)

Permiten administrar los permisos sobre los objetos de la base de datos
La administración de los permisos sobre los asegurables a nivel de base de datos es mas sencilla usando funciones de base de datos.

Existen dos tipos de funciones de base de datos

1. Funciones fijas de base de datos, las que se encuentran definidas  en todas las bases de datos.
2. Funciones de base de datos, llamadas también funciones flexibles que son los tipos de funciones de base de datos que se pueden crear.

Funciones fijas de base de datos.

Función 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.
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_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.
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_securityadmin Los miembros del rol fijo de base de datos db_securityadmin pueden modificar la pertenencia a roles y administrar permisos. Si se agregan entidades de seguridad a este rol, podría habilitarse un aumento de privilegios no deseado.
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.

 

Crear funciones de base de datos

Instrucción: Create Role
Permite crear un rol de base de datos.

Sintaxis:

Crear: Create role NombreRol [Authorization Usuario]

Donde:
NombreRol: es el nombre del rol de base de datos a crear.
Authorization Usuario: Usuario de base de datos o rol que es el dueño del rol creado.  Si no se especifica el dueño es el usuario que lo crea.

Modificar las funciones de base de datos

Instrucción: Alter Role
Permite modificar un rol de base de datos.

Sintaxis:

Alter role NombreRol
[ ADD MEMBER Principal ]
| [ DROP MEMBER Principal ]
| [ WITH NAME = NuevoNombre ]

Donde:
NombreRol: es el nombre del rol a modificar.
ADD MEMBER Usuario: especifica el usuario o rol creado por el usuario que se incluye como miembro.
DROP MEMBER Usuario: especifica el usuario o rol creado por el usuario que se elimina del rol.
WITH NAME = NuevoNombre: especifica el nuevo nombre del rol de base de datos.



Procedimientos almacenados sp_addrolemember y sp_droprolemember

Permiten agregar o eliminar los miembros de una función de base de datos.

Agregar un usuario a un Rol o función de base de datos.
sp_addrolemember Usuario, RolBaseDatos

Quitar un usuario de un Rol o función de base de datos.
sp_droprolemember Usuario, RolBaseDatos

Procedimiento almacenado sp_helprolemember

Permite visualizar los miembros de un rol de servidor
Sintaxis
sp_helprolemember RolDeBaseDatos

Eliminar un rol de base de datos

Instrucción: Drop role NombreRol
Permite eliminar el rol de base de datos creado por el usuario.
Sintaxis:

Drop role NombreRol

Nota: El rol debe ser un rol flexible y no debe tener miembros

Para listar los roles de servidor:
select * from sys.database_principals where type = ‘R’
go

Los roles fijos se pueden listad con el procedimiento almacenado sp_helprole
sp_helprole
go

Ejercicios

Usando la base de datos Northwind
use northwind
go

Ejercicio 1
Crear un rol de base de datos llamado Reportes

Create role Reportes
go

Ejercicio 2
Crear un rol de base de datos llamado Gestion autorizando a Reportes como su propietario

Create role Gestion authorization Reportes
go

Ejercicio 3
Listar los roles de base de datos

select name, type, type_desc, is_fixed_role  from sys.database_principals where type = ‘R’
go

Ejercicio 4
Listar los roles fijos de base de datos

select * from sys.database_principals where type = ‘R’ and is_fixed_role = 1
go

Ejercicio 5
Ver los miembros del rol db_Owner

sp_helprolemember db_Owner
go

Ejercicio 6
Crear un inicio de sesión llamado Jefe, crear un usuario en Northwind llamado TrainerSQL (Ver Usuarios de Base de datos)  en base al inicio de sesión Jefe y hacerlo miembro de db_securityadmin

use master
go
Create login Jefe with password = ‘sinpass’
go
use Northwind
go
create user TrainerSQL from login Jefe
go
Alter role db_securityadmin add member TrainerSQL
go

Ejercicio 7
Ver los miembros del rol de base de datos db_securityadmin

sp_helprolemember db_securityadmin
go

Ejercicio 8
Cambiar de nombre el rol de base de datos llamado Gestion por Administracion

Alter role Gestion with name = Administracion
go

Ejercicio 9
Crear el rol de base de datos llamado ServicioVenta, considere si el rol existe.

if not exists (select * from sys.database_principals where type = ‘R’ and name = ‘ServiciosVenta’)
Begin
Create role ServiciosVenta
End
go
Nota: el código para crear el rol de base de datos llamado ServicioVenta no reporta error
si el rol existe, lo que puede ocurrir con las instruccciones anteriores si el rol a crear existe.

Ejercicio 10
Eliminar el rol de base de datos llamado Administracion

Drop role Administracion
go

Ejercicio 11
Quitar el usuario TrainerSQL del rol de base de datos db_securityadmin

Alter role db_securityadmin drop member TrainerSQL
go