Roles de aplicación en SQL Server

Roles de Aplicación

  • Una función de aplicación es un objeto de base de datos que permite que una aplicación ejecute con sus propios permisos similares a los de un usuario.
  • Puede utilizar funciones de aplicación para permitir el acceso a datos específicos sólo a aquellos usuarios que se conectan a través de una aplicación concreta. A diferencia de las funciones de la base de datos, las  funciones de la aplicación no contienen miembros y están inactivas de forma predeterminada.
  • Los roles de aplicación funcionan con ambos modos de autenticación.
  • Las funciones de aplicación se habilitan mediante el procedimiento almacenado sp_setapprole, que requiere
    una contraseña.
  • Dado que los roles de aplicación son un principal de nivel de base de datos, pueden acceder a otras bases de datos sólo a través de permisos concedidos en esas bases de datos al invitado. Por lo tanto, cualquier base de datos en la que el invitado se haya inhabilitado será inaccesible a las funciones de la aplicación en otras bases de datos.

Conectarse con un rol de aplicación

Para hacer efectivos los permisos de un rol de aplicación se siguen los siguientes pasos:

1. Un usuario ejecuta una aplicación cliente.
2. La aplicación cliente se conecta a una instancia de SQL Server como el usuario.
3. Desde la aplicación se ejecuta el procedimiento almacenado sp_setapprole guardado con una contraseña conocida únicamente por la aplicación.
4. Si el nombre y la contraseña de la función de aplicación son válidos, se habilita la función de aplicación.
5. En este punto, la conexión pierde los permisos del usuario y asume los permisos de la función de la aplicación.
Los permisos se pierden al desconectarse de la aplicación.

Crear Roles de Aplicación

Instrucción: Create Application Role
Permite crear un rol de aplicación
Sintaxis:

CREATE APPLICATION ROLE NombreRolAplicacion
WITH PASSWORD = ‘password’ [, DEFAULT_SCHEMA = NombreEsquema ]

Donde:
NombreRolAplicacion es el nombre del rol de aplicación a crear.
DEFAULT_SCHEMA = NombreEsquema especifica el esquema por defecto para el rol de aplicación.

Modificar Roles de Aplicación

Instrucción: Alter Application Role
Permite modificar un rol de aplicación
Sintaxis:

ALTER APPLICATION ROLE NombreRolAplicacion
WITH NAME = NuevoNombre
| PASSWORD = ‘password’
| DEFAULT_SCHEMA = NombreEsquema

Donde:
NombreRolAplicacion es el nombre del rol de aplicación a modificar.
With name = NuevoNombre especifica el nuevo nombre para el rol de aplicación.
Password = ‘password’ permite especificar el nuevo password del rol de aplicación.
DEFAULT_SCHEMA = NombreEsquema especifica el esquema por defecto para el rol de aplicación.

Eliminar Roles de Aplicación

Instrucción: Drop Application Role
Permite eliminar un rol de aplicación
Sintaxis:

Drop APPLICATION ROLE NombreRolAplicacion

Donde:
NombreRolAplicacion es el nombre del rol de aplicación a eliminar.

Ejercicios:

Usando AdventureWorks
use AdventureWorks
go

Ejercicio 1
Crear un rol de aplicación llamado Ventas y asignarle el esquema Sales

Create application role Ventas with password = ‘5286’, default_schema = Sales
go

Ejercicio 2
Crear un rol de aplicación llamado Administrador

Create application role Administrador with password = ‘delunoalocho’
go

Ejercicio 3
Asignar el permiso de lectura al rol Administrador sobre el esquema Production

Grant select on schema::Production to Administrador
go

Ejercicio 4
Cambiar el password del rol de aplicación Administrador a ‘SinClave’

Alter application role Administrador with password = ‘SinClave’
go

Ejercicio 5
Cambiar el esquema del rol de aplicación Ventas a Purchasing

Alter application role Ventas with DEFAULT_SCHEMA = Purchasing
go

Ejercicio 6
Ver los roles de aplicación

select * from sys.database_principals where type = ‘A’
go

Activar un rol de aplicación

Procedimiento sp_setapprole
Activa los permisos asociados con el rol de aplicación en la base de datos.
Sintaxis:

sp_setapprole [ @rolename = ] ‘NombreRol’,
[ @password = ] { encrypt N’password’ }
[ @fCreateCookie = ] true | false ]

Ejemplos

Ejercicio 7
Activar el rol de aplicación Ventas

Exec sp_setapprole Ventas,5286
go

Ejercicio 8
Crear un rol de aplicación llamado Contador, asignar el esquema Person y luego activar el rol de aplicación y crear una Cookie.

Create application role Contador with password = ‘Cero333’
go
DECLARE @cookie varbinary(8000)
Exec sp_setapprole Contador, Cero333 ,
@fCreateCookie = true, @cookie = @cookie OUTPUT
go

Procedimiento almacenado sp_unsetapprole

Desactiva un rol de aplicación y regresa al contexto de seguridad previo.
Sintaxis:

sp_unsetapprole @cookie

La @Cookie es creada por sp_setapprole

Ejercicio 9
Desactivar el rol de aplicación Contador

sp_unsetapprole @cookie
go
Nota: Esta instrucción no se puede probar desde SQL Server, salvo que
ejecute en el mismo bloque la activación y desactivación del rol de aplicación.
Ejemplo

Ejercicio 10
Crear un rol de aplicación Almacen luego activarlo y desactivarlo mostrando el usuario del contexto.

Create application role Almacen with password = ‘TrainSQL’
go
DECLARE @cookie varbinary(8000)
Exec sp_setapprole Almacen, TrainSQL ,
@fCreateCookie = true, @cookie = @cookie OUTPUT
Select USER_NAME() As ‘Contexto Aplicación’
Exec sp_unsetapprole @cookie
select USER_NAME() As ‘Contexto por defecto’
go

Ejercicio 11
Eliminar el rol de aplicación Almacen

Drop application role Almacen
go