Encriptación de datos en SQL Server – Parte 1

Muchas veces en los sistemas de información se maneja mucha data que es sensible y que las compañías quieren proteger para evitar que caiga en manos maliciosas. Ejemplos de esta información son los números de tarjetas de crédito, números de documentos de identidad, números de teléfono, etc.

La protección de esta información se puede programar en la aplicación, pero que es lo que sucedería si es que un empleado interno consulta la base de datos, obtiene esos datos y los vende o los entrega a la competencia; bueno pues nuestra seguridad estaría violada y la información que queríamos proteger se encontraría totalmente expuesta. Una solución alterna a esto sería tener la información encriptada dentro de la base de datos, de esta manera la información se encontraría protegida incluso si alguna persona dentro de la organización consulta la base de datos directamente.

Pero como podemos tener la información encriptada dentro de la base de datos; bueno podríamos crear una compleja función que encripte los datos con un complicado algoritmo. Esto no sería muy óptimo ya que el tiempo que pasaríamos creando esta función lo podríamos usar para hacer otras cosas, sin contar que nuestra función puede tener una serie de bugs. SQL Server, a partir de la versión 2005, nos ofrece una variedad de formas para encriptar la información en la base de datos, entre las cuales tenemos:

  • Passphrase
  • Certificate
  • Symmetric key
  • Asymmetric key

Passphrase: Esta es la forma más simple y básica de encriptación de datos que se puede tener en SQL Server. Bajo este método, lo único que se necesita para encriptar los datos es una frase o contraseña “segura”. Deben notar que el asegurar esta frase o contraseña deberá ser el trabajo de ustedes, SQL Server no validara que esta contraseña cumpla con las restricciones de seguridad que Windows establece para las contraseñas de los usuarios. Además si alguien llega a saber la contraseña toda nuestra información quedaría expuesta sin necesidad de tener permisos adicionales más que leer la información de la tabla. Otro posible problema que se puede llegar a tener con este método es que no todos los valores de una columna se encripten con la misma contraseña, en ese caso la información quedaría irrecuperable. Ahora voy a hacer un pequeño ejemplo para demostrar su uso:

-- 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 cliente con una columna TarjetaCredito
— de tipo varbinary para que contenga la informacion encriptada
CREATE TABLE dbo.Cliente
(CodigoCliente INT NOT NULL IDENTITY(1,1)
,
Nombres VARCHAR(100) NOT NULL
,
TarjetaCredito VARBINARY(128))
GO
— insertamos un valor
INSERT INTO dbo.Cliente (Nombres, TarjetaCredito)
VALUES (‘Frank Lampard’, ENCRYPTBYPASSPHRASE(‘EstaEsMiFraseSecreta’,‘1111-1111-1111-1111’))
GO
— Intentamos hacer un select convencional
SELECT CodigoCliente, Nombres, TarjetaCredito
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  0x01000000F2884AC9…
*/

— Intentamos hacer un select con una frase incorrecta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50),
DECRYPTBYPASSPHRASE(‘EstaNoEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  NULL
*/

— Ahora hacemos un select con la frase correcta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50), DECRYPTBYPASSPHRASE(‘EstaEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  1111-1111-1111-1111
*/

— Intentamos hacer un select con una frase incorrecta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50),
DECRYPTBYPASSPHRASE(‘EstaNoEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  NULL
*/

— Ahora hacemos un select con la frase correcta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50), DECRYPTBYPASSPHRASE(‘EstaEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  1111-1111-1111-1111
*/

— Intentamos hacer un select con una frase incorrecta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50),
DECRYPTBYPASSPHRASE(‘EstaNoEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  NULL
*/

— Ahora hacemos un select con la frase correcta
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50), DECRYPTBYPASSPHRASE(‘EstaEsMiFraseSecreta’,TarjetaCredito))
FROM dbo.Cliente
/*
CodigoCliente Nombres        TarjetaCredito
————- ————– ———————
1             Frank Lampard  1111-1111-1111-1111
*/

Este método adicionalmente tiene una variante para dar un poco mas de seguridad a los datos y es que las funciones ENCRYPTBYPASSPHRASE y DECRYPTBYPASSPHRASE aceptan un tercer parámetro que es el autenticador, el cual tiene que ser colocado para desencriptar la información si es que se encriptó con el autenticador. El autenticador podría ser el usuario que guardo la información, de esta manera cada usuario tendría encriptada su información y esta no sería visible a los demás usuarios. Veamos un ejemplo:

-- insertamos un valor
DECLARE @v_Usuario SYSNAME
SET
@v_Usuario = 'Usuario1'
INSERT INTO dbo.Cliente (Nombres, TarjetaCredito)
VALUES ('Fernando Torres', ENCRYPTBYPASSPHRASE('EstaEsMiFraseSecreta','2222-2222-2222-2222',1,@v_Usuario))
GO

— Intentamos hacer un select convencional
SELECT CodigoCliente, Nombres, TarjetaCredito
FROM dbo.Cliente
/*
CodigoCliente Nombres         TarjetaCredito
————- ————— ———————
1              Frank Lampard   0x01000000F2884AC9…
2              Fernando Torres 0x01000000D0E7D822…
*/

— Ahora hacemos un select con la frase correcta
— y con el autenticador correcto
DECLARE @v_Usuario SYSNAME
SET
@v_Usuario = ‘Usuario1’
SELECT CodigoCliente, Nombres, CONVERT(VARCHAR(50), DECRYPTBYPASSPHRASE(‘EstaEsMiFraseSecreta’,TarjetaCredito,1,@v_Usuario))
FROM dbo.Cliente
/*
CodigoCliente Nombres         TarjetaCredito
————- ————— ———————
1              Frank Lampard   NULL
2              Fernando Torres 2222-2222-2222-2222
*/

Proximamente publicara articulos referentes a los demas tipos de enciptacion de datos que nos da SQL Server.

Anuncios

Foreign Keys, Buenas o Malas?

Hola, ha pasado un buen tiempo desde mi último post, y la verdad es que he estado bastante atareado con el trabajo estas últimas semanas, pero ya volví, y decidí darle un poco de tiempo a este pequeño rincón que he creado para poder compartir el conocimiento en base de datos. Ahora quisiera hablar sobre algo que vi en estos últimos días y la verdad es algo polémico, porque la gente en la comunidad de SQL Server no está 100% de acuerdo con el tema, y a lo que me refiero es a las relaciones entre las tablas de las bases de datos.

Las relaciones entre las tablas de la base de datos son implementadas a través de constraints, los cuales aseguran la integridad de la información contenida en las tablas. Desde mi punto de vista los constraints de base de datos son el último cordón de seguridad para nuestra información. Un sistema puede tener muchísimas reglas de negocio y de integridad de los datos en el lado de la aplicación, y eso está bien, pero no hay que olvidar que las bases de datos siempre están expuestas a bugs dentro de la aplicación o ataques externos o internos dentro de la organización los cuales pueden introducir información maliciosa o incorrecta en las tablas sin necesidad de pasar por las capas aplicación, en este caso la base de datos queda completamente expuesta a inconsistencia de datos.

Ahora los detractores de las relaciones entre las tablas indican que al colocar este constraint en las tablas tiene un precio en la performance de las operaciones de insert, delete y update. Esto es cierto, pero si los constraints de Foreign Key han sido bien definidos, esto no tiene porque ser un problema debido a que una de las mejores prácticas al momento de crear un constraint de Foreign Key es Indexarlo. Esto no lo hace el motor de base de datos automáticamente, esto debe ser hecho por el arquitecto de base de datos o el administrador de base de datos. Si una relación esta indexada, se reduce bastante el impacto en las operaciones de insert, delete y update e incluso se logra reducir la posibilidad de obtener un deadlock en la base de datos; además de esto es muy beneficioso para las operaciones de lectura contra las tablas ya que si las relaciones están indexadas, el optimizador de consultas de la base de datos podrá obtener mejores planes de ejecución dando como resultado respuestas mas rápidas.

A continuación veremos un ejemplo simple que demostrara el efecto que puede tener el hecho de tener o no relaciones en las bases de datos. Para este ejemplo usaremos la base de datos AdventureWorksR2 la cual puede ser descargada desde aquí. Primero revisaremos el plan de ejecución que se genera con la siguiente consulta:

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Se puede observar que de las tres tablas que intervienen en la consulta solo dos aparecen en el plan de ejecución. Esto se debe a que sql server confia en la información que se encuentra en las tablas debido a los foreign keys que hay entre ellas. Para comprobarlo vamos a crear las mismas tres tablas pero sin relaciones para ver el comportamiento del motor de base de datos.

SELECT *
INTO dbo.ProductCategory
FROM Production.ProductCategory

SELECT *
INTO dbo.ProductSubcategory
FROM Production.ProductSubcategory

SELECT *
INTO dbo.Product
FROM Production.Product

Ahora ejecutaremos la misma consulta pero con las nuevas tablas sin relaciones.
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Ahora vemos que el plan de ejecución es diferente, esto se debe a que como no hay relaciones entre las tablas SQL debe acceder a todas las tablas para poder obtener la información requerida. Pero algunos dirán que esas tablas no tienen indices y que los índices ayudarían. Entonces crearemos los índices sugeridos.

ALTER TABLE dbo.ProductCategory
ADD CONSTRAINT PK_ProductCategory_tmp
PRIMARY KEY (ProductCategoryID)
GO

ALTER TABLE dbo.ProductSubcategory
ADD CONSTRAINT PK_ProductSubcategory_tmp
PRIMARY KEY (ProductSubcategoryID)
GO

ALTER TABLE dbo.Product
ADD CONSTRAINT PK_Product_tmp
PRIMARY KEY (ProductID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_ProductSubcategory_01
ON dbo.ProductCategory (ProductCategoryID)
GO

CREATE NONCLUSTERED INDEX IDX_tmp_Product_01
ON dbo.Product (ProductSubcategoryID)
GO

Ahora volvemos a ejecutar la misma consulta, pero ademas ahora mediremos las operaciones de IO.

SET STATISTICS IO ON
GO
SELECT pro.Name
FROM dbo.ProductCategory cat
INNER JOIN dbo.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN dbo.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID


Vemos que el plan de ejecución ha variado un poco. Ahora veamos las estadísticas de IO

Table 'Product'. Scan count 37, logical reads 664, physical reads 0
Table
'ProductCategory'. Scan count 0, logical reads 74, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads 0

Ahora mediremos las estadisticas de IO que resultan de la consulta a las tablas con relaciones.

SELECT pro.Name
FROM Production.ProductCategory cat
INNER JOIN Production.ProductSubcategory sub
ON sub.ProductCategoryID = cat.ProductCategoryID
INNER JOIN Production.Product pro
ON sub.ProductSubcategoryID = pro.ProductSubcategoryID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table
'Product'. Scan count 1, logical reads 15, physical reads 0
Table
'ProductSubcategory'. Scan count 1, logical reads 2, physical reads

Como se puede observar claramente la consulta con las tablas que tienen relaciones es mucho más eficiente que la consulta a las tablas sin relaciones, debido a que el optimizador de consultas de SQL Server tiene más recursos de los cuales valerse para poder obtener planes de ejecución mas óptimos. El numero de lecturas baja considerablemente casi un 98%. Entonces mi opinión personal las relaciones entre las tablas es bastante beneficioso para la base de datos tanto desde el punto de vista de seguridad como de performance.