Tutorial: Firmar procedimientos almacenados con un certificado

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este tutorial se describe cómo se firman los procedimientos almacenados con un certificado generado por SQL Server.

Nota:

Para ejecutar el código de este tutorial, debe estar configurada la seguridad de modo mixto y la base de datos AdventureWorks2022 debe estar instalada.

La firma de procedimientos almacenados mediante un certificado es útil si desea exigir permisos en el procedimiento almacenado sin conceder explícitamente esos derechos al usuario. Aunque esto se puede conseguir de distintas maneras, por ejemplo, mediante la instrucción EXECUTE AS, los certificados le permiten usar un seguimiento para buscar al autor de la llamada original del procedimiento almacenado. De este modo, se consigue un alto nivel de auditoría, especialmente durante las operaciones de seguridad o de lenguaje de definición de datos (DDL).

Puede crear un certificado en la base de datos maestra para permitir permisos de nivel de servidor o puede crear un certificado en cualquier base de datos de usuario para permitir permisos de nivel de base de datos. En este escenario, un usuario sin derechos en las tablas base deberá obtener acceso a un procedimiento almacenado de la base de datos AdventureWorks2022 y deberá realizar una auditoría del acceso a los objetos. En lugar de usar los métodos de cadenas de propiedad, deberá crear una cuenta de servidor y de usuario de base de datos sin derechos en los objetos base, y una cuenta de usuario de base de datos con derechos en una tabla y en un procedimiento almacenado. Tanto el procedimiento almacenado como la segunda cuenta de usuario de base de datos estarán protegidos con un certificado. La segunda cuenta de la base de datos tendrá acceso a todos los objetos y podrá conceder acceso al procedimiento almacenado en la primera cuenta de usuario de la base de datos.

En este escenario, en primer lugar creará un certificado de base de datos, un procedimiento almacenado y un usuario y, a continuación, comprobará el proceso mediante los pasos siguientes:

Cada bloque de código incluido en este ejemplo se describe en línea. Para copiar el ejemplo completo, vea Ejemplo completo al final de este tutorial.

Requisitos previos

Para llevar a cabo este tutorial necesita tener SQL Server Management Studio, acceso a un servidor que ejecute SQL Server y una base de datos de AdventureWorks.

• Instale SQL Server Management Studio.
• Instale SQL Server 2017 Developer Edition.
• Descargue bases de datos de ejemplo AdventureWorks.

Para obtener instrucciones sobre cómo restaurar una base de datos en SQL Server Management Studio, vea Restauración de una base de datos.

1. Configurar el entorno

Para establecer el contexto inicial del ejemplo, en SQL Server Management Studio, abra una nueva consulta y ejecute el código siguiente para abrir la base de datos. AdventureWorks2022. Este código cambia el contexto de la base de datos a AdventureWorks2022 y crea un inicio de sesión de servidor y una cuenta de usuario de base de datos nuevos (TestCreditRatingUser) mediante una contraseña.

USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  

Para obtener más información sobre la instrucción CREATE USER, consulte CREATE USER (Transact-SQL). Para obtener más información sobre la instrucción CREATE LOGIN, consulte CREATE LOGIN (Transact-SQL).

2. Crear un certificado

Puede crear certificados en los servidores mediante la base de datos maestra como contexto y mediante una base de datos de usuario, o ambas. Existen varias opciones para proteger el certificado. Para obtener más información sobre los certificados, consulte CREATE CERTIFICATE (Transact-SQL).

Ejecute este código para crear un certificado de base de datos y protéjalo mediante una contraseña.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is not in the future
GO  

3. Crear y firmar un procedimiento almacenado mediante el certificado

Utilice el siguiente código para crear un procedimiento almacenado que seleccione los datos de la tabla Vendor en el esquema de la base de datos Purchasing y restrinja el acceso únicamente a las compañías con una solvencia de 1. Tenga en cuenta que la primera sección del procedimiento almacenado muestra el contexto de la cuenta de usuario que ejecuta el procedimiento almacenado, que solo se usa para demostrar los conceptos. No es necesario cumplir los requisitos.

CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Show who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1  
END  
GO  

Ejecute este código para firmar el procedimiento almacenado con el certificado de base de datos mediante una contraseña.

ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  

Para obtener más información sobre los procedimientos almacenados, consulte Procedimientos almacenados (motor de base de datos).

Para obtener más información sobre la firma de procedimientos almacenados, consulte ADD SIGNATURE (Transact-SQL).

4. Crear una cuenta de certificado mediante el certificado

Ejecute este código para crear un usuario de base de datos (TestCreditRatingcertificateAccount) a partir del certificado. Esta cuenta no tiene inicio de sesión en el servidor y en última instancia controlará el acceso a las tablas subyacentes.

USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  

5. Conceder al certificado derechos de base de datos de cuentas

Ejecute este código para conceder derechos TestCreditRatingcertificateAccount a la tabla base y al procedimiento almacenado.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  

Para obtener más información sobre cómo conceder permisos a objetos, consulte GRANT (Transact-SQL).

6. Mostrar el contexto de acceso

Para mostrar los derechos asociados al acceso al procedimiento almacenado, ejecute el siguiente código para conceder al usuario de TestCreditRatingUser los derechos para ejecutar el procedimiento almacenado.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  

A continuación, ejecute el siguiente código para ejecutar el procedimiento almacenado como el inicio de sesión dbo que ha usado en el servidor. Compruebe la salida de la información de contexto del usuario. Mostrará la cuenta dbo como el contexto con sus derechos propios y no a través de una pertenencia a un grupo.

EXECUTE TestCreditRatingSP;  
GO  

Ejecute el siguiente código para que la instrucción EXECUTE AS se convierta en la cuenta TestCreditRatingUser y ejecute el procedimiento almacenado. En esta ocasión, el contexto de usuario se establece en el contexto USER MAPPED TO CERTIFICATE. Tenga en cuenta que esta opción no se admite en una base de datos independiente, en Azure SQL Database o en Azure Synapse Analytics.

EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXECUTE TestCreditRatingSP;  
GO  

Se muestra la auditoría disponible porque ha firmado el procedimiento almacenado.

Nota:

Use EXECUTE AS para cambiar contextos dentro de una base de datos.

7. Restablecer el entorno

El código siguiente usa la instrucción REVERT para devolver el contexto de la cuenta actual a dbo y, a continuación, restablece el entorno.

REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO  

Para obtener más información sobre la instrucción REVERT, consulte REVERT (Transact-SQL).

Ejemplo completo

En esta sección se muestra el código de ejemplo completo.

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  
  
/* Step 2 - Create a certificate in the AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
GO  
  
/* Step 3 - Create a stored procedure and  
sign it using the certificate */  
CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Shows who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token;     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1;  
END  
GO  
  
ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
  
/* Step 4 - Create a database user for the certificate.   
This user has the ownership chain associated with it. */  
USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  
  
/* Step 5 - Grant the user database rights */  
GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE  
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  
  
/* Step 6 - Test, using the EXECUTE AS statement */  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  
  
-- Run the procedure as the dbo user, notice the output for the type  
EXEC TestCreditRatingSP;  
GO  
  
EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXEC TestCreditRatingSP;  
GO  
  
/* Step 7 - Clean up the example */  
REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO  

Consulte también

Centro de seguridad para el Motor de base de datos de SQL Server y Azure SQL Database