Tutorial: Firmar procedimientos almacenados con un certificado
Se aplica a:
SQL ServerAzure SQL DatabaseAzure 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
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente las Cuestiones de GitHub como mecanismo de retroalimentación para el contenido y lo sustituiremos por un nuevo sistema de retroalimentación. Para más información, consulta: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de