Compartir a través de


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:

  1. Configure el entorno.
  2. Cree un certificado.
  3. Cree y firme un procedimiento almacenado mediante el certificado.
  4. Cree una cuenta de certificado mediante el certificado.
  5. Conceda al certificado derechos de base de datos de cuentas.
  6. Muestre el contexto de acceso.
  7. 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

Ayuda e información

Obtener ayuda sobre SQL Server 2005