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 3

with 9 comments

Hola, parece increible que ya estoy en la tercera parte de esta serie de post dedicados a la encriptación de datos en SQL Server, espero que este siendo de ayuda para poder tomar decisiones 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 encriptación basada en llaves simetricas.

Symmetric Key: La encriptación a partir de llaves simétricas tiene como principio que para  encriptar y desencriptar la información se necesita la misma llave. Es decir que si nuestra llave llega a ser pública y accesible para todos los usuarios, estos podrían ver la información sensible que tenemos encriptada en la base de datos. Pero no hay que alarmarse tanto, ya este tipo de encriptación es la más común, y es la que la mayoría de ejemplos en la red usan para explicar el tema de la encriptación de datos en SQL Server. Para crear una llave simétrica, esta debe ser encriptada a partir de un certificado, de una llave asimétrica o de otra llave simétrica, lo cual nos brinda mayor seguridad porque el usuario deberá pasar por encima de todos estos métodos de encriptación para poder acceder a la llave que le permitirá encriptar o desencriptar la información. A continuación veremos un ejemplo simple de encriptación de datos.

-- 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.SymetricKeyEncription
(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

— Creamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = ‘MyKeySource’,
IDENTITY_VALUE = ‘MyIdentityValue’,
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado;
GO

— Hacemos una consulta para visualizar las llaves simetricas de la base de datos
— en este caso podemos observar 2 las cuales son la DMK y la llave simetrica creada
— a partir del certificado.
SELECT name, algorithm_desc, create_date
FROM sys.symmetric_keys
/*
name                     algorithm_desc create_date
———————— ————– ———————–
##MS_DatabaseMasterKey## TRIPLE_DES     2011-10-12 21:34:50.147
MiLlaveSimetrica         AES_256        2011-10-12 21:34:57.500
*/

— Ahora procederemos a ingresar valores a nuestra tabla
— Para eso debemos abrir la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— insertamos un valor
INSERT INTO dbo.SymetricKeyEncription (Nombres, DocNum)
VALUES (‘Juan Perez’, ENCRYPTBYKEY(KEY_GUID(‘MiLlaveSimetrica’),‘12345678’))
GO

— Una vez que se termino de encriptar los datos, se cierra la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

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

— Ahora para poder ver los datos en claro, debemos desencriptarlos
— con la llave simetrica, y para hacerlo debemos primero abrirla
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

— Ahora seleccionamos los datos pero antes los desencriptamos con la llave simetrica
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

— Ahora para probar la seguridad de la llave simetrica
— Creamos un usuario de prueba
CREATE LOGIN [Usuario1] WITH PASSWORD = N’123′
GO
CREATE USER [Usuario1] FOR LOGIN [Usuario1]
GO
GRANT SELECT ON dbo.SymetricKeyEncription TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
REVERT

— Como se puede ver nos aparece un error porque el usuario no tiene permisos sobre la llave simetrica

— Para poder hacer uso de la llave simetrica debemos tener el permiso
— de control sobre el certificado que la encripta y sobre la misma llave simetrica.
— Ahora asignaremos ese permiso al Usuario1
GRANT CONTROL ON CERTIFICATE::[MiPrimerCertificado] TO [Usuario1]
GO
GRANT CONTROL ON SYMMETRIC KEY::MiLlaveSimetrica TO Usuario1
GO

— Probamos seleccionar la informacion con la llave simetrica
EXECUTE AS USER = ‘Usuario1’
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;
SELECT Nombress, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription;
CLOSE SYMMETRIC KEY MiLlaveSimetrica
REVERT
/*
Nombres    DocNum
———- ————–
Juan Perez 12345678
*/

— Como vemos ahora la informacion si aparece.

Al igual que los certificados, las llaves simetricas deberían tener una copia de seguridad, pero lamentablemente esto no es posible debido a que dentro de las sentencias T-SQL para manejar las llaves simetricas no hay ninguna que sirva para sacar copias de seguridad, entonces que podemos hacer para poder crear una llave simetrica que pueda recuperarse, bueno hay dos atributos especiales que fueron especificados en la creación de la llave simetrica en el ejemplo anterior, estos dos atributos son:

  • IDENTITY_VALUE: el cual SQL Server usa para generar in valor GUID para la llave.
  • KEY_SOURCE: el cual SQL Server usa como material para poder generar la llave en sí.

Si estos dos atributos fueron especificados en la creación de la llave simetrica, entonces esta podrá ser recreada siempre y cuando se tengan copias de seguridad de los objetos usados para generarla, en este caso del certificado usado. Si alguno de estos requisitos no es completado, entonces la información estará en peligro ya que si se llegan a perder alguno de los objetos de encriptación la información puede perderse para siempre. A continuación veremos un ejemplo de cómo sacar copia de seguridad a estos objetos y luego simularemos su perdida y su posterior recuperación.

USE SecureDB
GO

-- sacaremos una copia de seguridad de la DMK
BACKUP MASTER KEY TO FILE = 'D:\DBAMemories\DMK.dat'
ENCRYPTION BY PASSWORD = 'MiPasswordSeguro';
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 la llave simetrica
DROP SYMMETRIC KEY MiLlaveSimetrica
GO

-- Ahora perderemos el certificado.
DROP CERTIFICATE MiPrimerCertificado
GO

-- Ahora perderemos la DMK
DROP  MASTER KEY
GO

-- Listo ahora si tenemos un desastre completo
-- al no tener la llave simetrica la informacion es inaccesible
-- Ahora para recrear nuestro ambiente
-- Primero restauraremos la DMK
RESTORE MASTER KEY
FROM FILE =
'D:\DBAMemories\DMK.dat'
DECRYPTION BY PASSWORD = 'MiPasswordSeguro'
ENCRYPTION BY PASSWORD = 'MiClaveSegura'
GO

-- Ahora abrimos la DMK sino no podremos restaurar el certificado
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MiClaveSegura'
GO

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

-- Ahora recreamos la llave simetrica
CREATE SYMMETRIC KEY MiLlaveSimetrica
WITH
KEY_SOURCE = 'MyKeySource',
IDENTITY_VALUE = 'MyIdentityValue',
ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MiPrimerCertificado
GO

-- Ahora si, como tenemos todo restaurado abrimos la llave simetrica
OPEN SYMMETRIC KEY MiLlaveSimetrica DECRYPTION BY CERTIFICATE MiPrimerCertificado;

-- Consultamos la informacion encriptada
SELECT Nombres, CONVERT(VARCHAR(8),DECRYPTBYKEY(DocNum)) AS DocNum
FROM dbo.SymetricKeyEncription
/*
Nombres    DocNum
---------- --------------
Juan Perez 12345678
*/

-- Y ahora volvemos a cerrar la llave simetrica
CLOSE SYMMETRIC KEY MiLlaveSimetrica

-- Como se puede observar la informacion volvio a estar disponible

Written by dbamemories

octubre 14, 2011 a 9:33 pm

9 comentarios

Subscribe to comments with RSS.

  1. Que tal, me parece un muy buen articulo sobre el uso de encriptacion en sql server pero me ha quedado una duda.
    Suponiendo el caso en que estoy usando un certificado autegenerado por sql server y despues de un tiempo adquiero otro certificado de alguna entidad y quiero usar ese certificado para manipular mi llave simetrica ¿que tan complicado seria dejar de usar el certificado autogenerado y pasar a usar el nuevo certificado? suponiendo ademas que he creado mas de 100 procedimientos donde abro y cierro la llave simetrica.

    Se agradece tu aporte y quedo a la espera de tu respuesta.

    Saludos

    omar marcelo

    agosto 22, 2012 at 11:05 am

    • Hola,

      Gracias por el comentario. Bueno ahora respondiendo a tu pregunta, para poder cambiar de certificado en primer lugar debes volver a encriptar toda la informacion con el nuevo certificado. Ademas debes considerar realizar el cambio tambien en tus 100 procedimientos ya que necesitas usar el certificado con el cual estas encriptando tu información para poder verla.

      dbamemories

      agosto 24, 2012 at 4:58 pm

  2. Porque al abrir la llave simetrica no se hace visible mi campo encriptado para mi sesion, sin necesidad de utilizar una funcion para visualizarlo.
    Lo hacen para poner una capa mas de seguridad?

    Carlos

    julio 1, 2013 at 11:05 am

    • Hola Carlos,
      Yo pienso que es porque una misma llave puede usarse para encriptar otros campos dentro de la misma tabla, y para que tu puedas visualizar el campo necesitas usar la funcion. Como efecto adicional tenemos una capa mas de seguridad como bien mencionas.

      dbamemories

      julio 1, 2013 at 11:11 am

      • comparto, pero a la vez penso que en sistemas con mucho desarrollo encima hay que ponerle mucho mas aun para adecuarlo.
        Es como que los DBAS no le podemos dar un valor agregado a la base sin impactar fuertemente a desarrollo. O el sistema es muy chico o se inicia con este concepto de cero

        Carlos

        julio 1, 2013 at 11:24 am

      • En ORACLE, el concepto es el mismo que en SQL Server?

        Saludos

        Carlos

        julio 1, 2013 at 2:39 pm

      • No, en Oracle el concepto estoy casi seguro que es completamente distinto.

        dbamemories

        julio 1, 2013 at 4:12 pm

  3. Realmente este es un tema de SQL que me había costado mucho entender pero con tus post lo pude hacer, estoy muy agradecido de verdad y por favor no dejes de postear sobre este maravilloso motor de base de datos, saludos.

    Esteban Quijada Suazo

    marzo 19, 2014 at 7:13 pm


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: