Firmar procedimientos almacenados con certificados en SQL Server
Firmar los procedimientos almacenados usando un certificado es muy útil si se desea asignar permisos para la ejecución del procedimiento almacenado sin conceder explícitamente esos derechos al usuario usando Grant (Ver Permisos con Grant).
Uso de Execute As
El uso de Execute As permite la ejecución de un procedimiento almacenado sin necesidad de haber iniciado la sesión con el usuario que tiene permisos para ejecutar el procedimiento, los certificados de SQL server permiten realizar un seguimiento para buscar al autor de la ejecución original del procedimiento almacenado.
El uso de los procedimientos almacenados firmados permite un alto nivel de auditoría, especialmente durante las operaciones de seguridad o de lenguaje de definición de datos (DDL) que son las instrucciones Create, Alter o Drop.
Niveles de permisos de los certificados
Permisos a nivel de servidor
Si se crea un certificado en la base de datos «master» se permiten permisos de nivel de servidor.
Permisos a nivel de Base de datos de usuario
Si se crea un certificado en la base de datos de usuario se asignan los permisos a nivel de la base de datos.
Pasos para firmar un procedimiento almacenado
Los pasos para firmar los procedimientos almacenados para conseguir mejor nivel de auditoría y seguridad son los que se describen a continuación:
- Crear un usuario en base a un login para asignar los permisos.
- Crear un certificado de SQL Server.
- Crear un procedimiento almacenado y firmarlo con el certificado creado.
- Asignar los permisos para ejecutar el SP
Ejercicio
Usando la base de datos Northwind, crear un SP que listará los productos y firmarlo con un certificado.
1. Crear login y usuario (Ver Logins, Ver Usuarios)
use master
go
Create login TrainerSQL with password = ‘123’
go
use Northwind
go
Create user TrainerUserConLogin
from login TrainerSQL
go
2. Crear el certificado
set dateformat dmy
go
Create certificate TrainerCertificado
encryption by password = ‘TSQLCertificadoSP’
with subject = ‘Certificado para prueba de cifrado SP’,
Expiry_date = ’15/05/2025′
go
Para listar los certificados
select * from sys.certificates
go
3. Crear el procedimiento y firmarlo con el certificado
Create procedure spProductosListado
As
Select P.ProductID As ‘Código’,
P.ProductName As ‘Descripción’,
P.UnitPrice As ‘Precio’
from Products As P
— Ver el usuario que lo ejecuta
— No es parte del SP
select principal_id As ‘Código Usuario’,
name As ‘Nombre’
from sys.user_token
go
4. Para asignar el certificado al SP se necesita obviamente
el nombre del certificado y su password
Add signature to spProductosListado
by certificate TrainerCertificado
with password = ‘TSQLCertificadoSP’
go
5. Crear el usuario a partir del certificado
Create user TrainerUserConCertificado
from certificate TrainerCertificado
go
Existen dos usuarios, TrainerUserConLogin que no ha sido creado en base al certificado y TrainerUserConCertificado que ha sido creado en base al certificado. Si todo funciona correctamente TrainerUserConLogin no podrá ejecutar el procedimiento almacenado y TrainerUserConCertificado si.
Para ver los Logins que inician con el nombre Trainer
Select * from sys.database_principals where name like ‘Trainer%’
go
Asignar el permiso al usuario TrainerUserConLogin para ejecutar el procedimiento almacenado (Ver Permisos con Grant)
Grant Execute
on object::spProductosListado
to TrainerUserConLogin
go
Asignar el permiso al usuario TrainerUserConCertificado para ejecutar el procedimiento almacenado
Grant Execute
on object::spProductosListado
to TrainerUserConCertificado
go
Ejecutar el procedimiento como el usuario TrainerUserConCertificado, se sugiere en este paso conectarse nuevamente a SQL Server con el usuario para realizar la prueba. Otra forma es cambiar el entorno de ejecución usando Execute As.
execute as login = ‘TrainerSQL’
go
Ahora ejecutar el procedimiento almacenado
Execute spProductosListado
go
Para restablecer el entorno de ejecución
Revert