Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

Encriptación de datos en SQL Server – Parte 2

with 5 comments

Hola, continuando con esta serie de post relacionados con la encriptación de datos dentro de la base de datos SQL Server y utilizando las mismas herramientas que el motor de base de datos, en este articulo cubriremos la encriptacion en base a un certificado. Si desea se puede visitar el anterior post haciendo clic aqui.

Certificate: SQL Server permite la encriptación de la información a través de certificados digitales los cuales pueden ser adquiridos en alguna de las entidades que los expiden. Adicionalmente SQL Server tambien permite la creación de certificados “self-signed” los cuales permiten al usuario crear un certificado propio con una sentencia simple de T-SQL. Los certificados en SQL Server pueden estar encriptados por un password, o por la Database Master Key, la cual es la primera llave que se debe crear en la base de datos para que a partir de ésta se encripten los demás objetos. A través de los certificados se puede tener un poco mas de seguridad ya que para poder usar un certificado es necesario tener permiso al mismo para poder utilizarlo. A continuación hare un ejemplo completo de la creación de un certificado “self-signed” encriptado a partir de la Database Master Key además se mostrara la encriptación y desencriptación de la información con el mismo. Además se mostrara como es que se debe dar permisos a un usuario para que este pueda ver la información encriptada por el certificado.

-- limpiamos el ambiente
IF (DATABASEPROPERTY('SecureDB','version') > 0)
BEGIN
USE MASTER
ALTER DATABASE
SecureDB SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
SecureDB
END

— Creamos la base de datos de prueba
USE MASTER
GO
CREATE DATABASE SecureDB
GO

—  Usamos nuestra base de datos de demo
USE SecureDB
GO

— Creamos una tabla cualquiera con una columna DocNum
— de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE dbo.CertificateEncription
(Nombres VARCHAR(100)
,
DocNum VARBINARY(128))
GO

— Creamos una Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MiClaveSegura’
GO

— Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT=‘DBAMemories Certificate’,
EXPIRY_DATE = ’12/31/2012′
GO

— Revisamos los certificados que tengo creados en la db
SELECT name, pvt_key_encryption_type_desc, subject, expiry_date
FROM sys.certificates
/*
name                pvt_key_encryption_type_desc subject                 expiry_date
——————- —————————- ———————– ———————–
MiPrimerCertificado ENCRYPTED_BY_MASTER_KEY      DBAMemories Certificate 2012-12-31 00:00:00.000
*/

— insertamos un valor
INSERT INTO dbo.CertificateEncription (Nombres, DocNum)
VALUES (‘Juan Perez’, ENCRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),‘12345678’))
GO

— Intentamos hacer un select convencional
SELECT Nombres, DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 0x9E363FDB9…
*/

— Ahora seleccionamos los datos pero antes los desencriptamos con el certificado
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Creamos un usuario de prueba
CREATE LOGIN [Usuario1] WITH PASSWORD = N’123′
GO
CREATE USER [Usuario1] FOR LOGIN [Usuario1]
GO
GRANT SELECT ON dbo.CertificateEncription TO Usuario1
GO

— Probamos seleccionar la informacion con el certificado
EXECUTE AS USER = ‘Usuario1’
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription;
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez NULL
*/

— Como se puede ver la informacion sale como NULL debido a que no tenemos
— permiso para usuar el certificado como el Usuario1

— Para poder hacer uso del certificado debemos tener el permiso
— de control sobre el certificado. Ahora asignaremos ese permiso al Usuario1
GRANT CONTROL ON CERTIFICATE::[MiPrimerCertificado] TO [Usuario1]
GO

— Probamos nuevamente seleccionar la informacion con el certificado
EXECUTE AS USER = ‘Usuario1’
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID(‘MiPrimerCertificado’),DocNum)) AS DocNum
FROM dbo.CertificateEncription;
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Como vemos ahora la informacion si aparece.

Es muy importante mencionar que los certificados deben tener una copia de seguridad como la base de datos misma debido a que en el caso de que perdamos el certificado solo esa copia de seguridad nos podrá asegurar la recuperación de toda la data encriptada con ese certificado, caso contrario, la información permanecerá perdida debido a que ya no se tiene el mismo certificado que la encriptó. Ahora veremos un ejemplo de cómo sacarle una copia de seguridad al certificado y simularemos la pérdida del mismo y los distintos escenarios para la recuperación de la información.

USE SecureDB
GO

-- Sacaremos una copia de seguridad del certificado
BACKUP CERTIFICATE MiPrimerCertificado TO FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY ( FILE = 'D:\DBAMemories\MiPrimerCertificado.key' ,
ENCRYPTION BY PASSWORD = 'pass' );
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE MiPrimerCertificado
GO

-- Podemos confirmar que nuestro certificado no esta y no podemos desencriptar nuestra informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez NULL
*/

-- Ahora que se puede hacer, bueno se puede pensar que
-- si se crea nuevamente el certificado, todo funcionara nuevamente. Veamos
-- Creamos el certificado
CREATE CERTIFICATE MiPrimerCertificado WITH SUBJECT='DBAMemories Certificate',
EXPIRY_DATE = '12/31/2012'
GO

-- Probamos desencriptar la informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez NULL
*/

-- Que paso? Por que no funciono?
-- Debido a que el certificado que se uso no es el mismo
-- que encripto la información, a pesar de que se creo
-- el certificado con la misma sentencia

-- Volvemos a eliminar el certificado
DROP CERTIFICATE MiPrimerCertificado
GO

-- Ahora restauramos el certificado desde nuestra copia de seguridad
CREATE CERTIFICATE MiPrimerCertificado
FROM FILE = 'D:\DBAMemories\MiPrimerCertificado.cert'
WITH PRIVATE KEY (FILE = 'D:\DBAMemories\MiPrimerCertificado.key',
DECRYPTION BY PASSWORD = 'pass');
GO

-- Finalmente volvemos a consultar la informacion
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYCERT(CERT_ID('MiPrimerCertificado'),DocNum)) AS DocNum
FROM dbo.CertificateEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez 12345678
*/

-- Y como podemos observar nuestra informacion volvio a estar disponible.

Written by dbamemories

octubre 4, 2011 a 9:28 pm

5 comentarios

Subscribe to comments with RSS.

  1. […] sobre la encriptación de los datos de una manera mas informada. Si gustan pueden visitar la Parte 2 or la Parte 1 de los post relacionados con este tema. Ahora procederemos a explicar la […]

  2. me ayudo mucho para mi tarea

    drake jon vides vidu

    junio 29, 2012 at 12:45 pm

  3. Muy buen tutorial, muy útil, muy práctico y muy claro, muchas gracias.

    Enrique Luna

    octubre 4, 2012 at 11:04 am


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: