Firmar procedimientos almacenados con un certificado
Nuevo: 12 de diciembre de 2006
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, la seguridad de modo mixto debe estar configurada y la base de datos AdventureWorks debe estar instalada. Para obtener más información acerca de la seguridad de modo mixto, vea Modo de autenticación. Para obtener más información acerca de la instalación de la base de datos AdventureWorks, vea Instalar ejemplos y bases de datos de ejemplo AdventureWorks.
Escenario
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 una traza para buscar al autor de la llamada original del procedimiento almacenado. De este modo, se consigue un de auditoría alto, especialmente durante las operaciones de seguridad o de lenguaje de definición de datos (DDL).
Puede crear un certificado en la base de datos master 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 AdventureWorks 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:
- Configure el entorno.
- Cree un certificado.
- Cree y firme un procedimiento almacenado mediante el certificado.
- Cree una cuenta de certificado mediante el certificado.
- Conceda al certificado derechos de base de datos de cuentas.
- Muestre el contexto de acceso.
- Restablezca el entorno.
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.
1. Configurar el entorno
Para establecer el contexto inicial del ejemplo, en SQL Server Management Studio abra una consulta nueva y ejecute el código siguiente para abrir la base de datos AdventureWorks. Este código cambia el contexto de la base de datos a AdventureWorks 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 AdventureWorks;
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 acerca de la instrucción CREATE USER , vea CREATE USER (Transact-SQL). Para obtener más información acerca de la instrucción CREATE LOGIN, vea CREATE LOGIN (Transact-SQL).
2. Crear un certificado
Puede crear certificados en los servidores mediante la base de datos master 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 acerca de los certificados, vea 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/05/2010';
GO
3. Crear y firmar un procedimiento almacenado mediante el certificado
Use el código siguiente para crear un procedimiento almacenado que seleccione los datos de la tabla Vendor en el esquema de base de datos Purchasing y restrinja el acceso a tan sólo las compañías con una solvencia de categoría 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, el cual sólo es válido 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 acerca de los procedimientos almacenados, vea Procedimientos almacenados (motor de base de datos).
Para obtener más información acerca de los esquemas de base de datos, vea Esquemas.
Para obtener más información acerca de cómo firmar los procedimientos almacenados, vea 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 AdventureWorks;
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 acerca de cómo conceder permisos a los objetos, vea 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.
EXECUTE AS LOGIN = 'TestCreditRatingUser';
GO
EXECUTE TestCreditRatingSP;
GO
Se muestra la auditoría disponible porque ha firmado el procedimiento almacenado.
[!NOTA] Existen dos modos de permitir a un usuario que cambie los contextos en una base de datos: mediante SETUSER o EXECUTE AS. Para obtener más información acerca del cambio de contexto, vea EXECUTE AS frente a SETUSER.
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 acerca de la instrucción REVERT, vea REVERT (Transact-SQL).
Ejemplo completo
En esta sección se muestra el código de ejemplo completo.
/* Step 1 - Open the AdventureWorks database */
USE AdventureWorks;
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 AdventureWorks database */
CREATE CERTIFICATE TestCreditRatingCer
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Credit Rating Records Access',
EXPIRY_DATE = '12/05/2010';
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 AdventureWorks;
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
Vea también
Otros recursos
Consideraciones de seguridad para SQL Server
Consideraciones de seguridad para bases de datos y aplicaciones de bases de datos