SOLIDQ SUMMIT MADRID 2017

#SQSummit17

Jose Antonio Pineda Montes

Data Platform Arquitect

JPINEDA@SOLIDQ.COM

SOLIDQ SUMMIT MADRID 2017

SOLIDQ SUMMIT MADRID 2017

Kerberos

NTLM (Windows NT Lan Manager)

Cuentas de Windows

Cuentas especificas de SQL Server

Autentificación

Buena Practica: Usar siempre Autentificación Windows a menos que la aplicación sea de terceros y te

obligue a utilizar una cuenta de seguridad de SQL.

Buena Practica: cambiar el nombre de la cuenta después de la

instalación SSMS>Object Explorer>Logins>Rename (right click) / T-SQL

Best Practice: Asegurar que password complejas son utilizadas para SA y otros SQL logins.

Intenta siempre utilizar las opciones ENFORCE EXPIRATION & MUST_CHANGE para SQL login

Buena práctica:

Usar cuentas especificas para cada servicio especifico de SQL

Component Windows Server 2008 Windows 7 and Windows Server 2008 R2 and higher

Recommended accounts

Database Engine NETWORK SERVICE Virtual Account * SQL_Engine

SQL Server Agent NETWORK SERVICE Virtual Account * SQL_Agent

SSAS NETWORK SERVICE Virtual Account * SQL_srvAS

SSIS NETWORK SERVICE Virtual Account * SQL_srvIS

SSRS NETWORK SERVICE Virtual Account * SQL_srvRS

SQL Server Distributed Replay Controller NETWORK SERVICE Virtual Account * SQL_DRContro

SQL Server Distributed Replay Client NETWORK SERVICE Virtual Account * SQL_DRReplay

FD Launcher (Full-text Search) LOCAL SERVICE Virtual Account

SQL Server Browser LOCAL SERVICE LOCAL SERVICE

SQL Server VSS Writer LOCAL SYSTEM LOCAL SYSTEM

Buena Practica:

Escoger cuidadosamente los miembros del role sysadmin

Buena práctica: Usar los roles de servidor y base de datos que vienen por defecto o crear roles

personalizados, y entonces aplicárselos a los logins de SQL.

Buena Practica: Deshabilitar la cuenta invitado de todas las bbdds de

usuario y sistemas ( menos en el msdb)

Buena Practica:

Cambiar el puerto por defecto siempre que sea posible

https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server

Buena Practica:

Actualizar a TSL 1.2

Seguridad a nivel de fila

Se previene a usuarios que

vean datos sin autorización

Configuración con lógica en

BBDD y esquemas

Administrado con SSMS

15 © 2016 SolidQ

Seguridad a nivel de fila

RLS (Row Level Security) restringe qué usuario puede ver qué fila

16 © 2016 SolidQ

Seguridad a nivel de fila

• Definida por Usuario

• Inline Table Valued Function

• Puede ser complicada conteniendo joins a otras tablas

Funcion de predicado

• Funcion vinculada a una tabla

• Dos tipos: de filtro o bloqueantes

Predicado de seguridad

• Colección de predicados de seguridad para administrar la seguridad sobre multiples tablas

Política de seguridad

17 © 2016 SolidQ

Seguridad a nivel de fila

18

Uno

Administrador de la seguridad crea un filtro de predicado y una política de seguridad vinculada a

la tabla

Dos

Usuario de aplicación hace una select

Tres

La política de seguridad de forma transparente añade los filtros pertinentes

Database

CREATE FUNCTION dbo.fn_securitypredicate(@wing int)

RETURNS TABLE WITH SCHEMABINDING AS

return SELECT 1 as [fn_securitypredicate_result] FROM

StaffDuties d INNER JOIN Employees e

ON (d.EmpId = e.EmpId)

WHERE e.UserSID = SUSER_SID() AND @wing = d.Wing;

CREATE SECURITY POLICY dbo.SecPol

ADD FILTER PREDICATE dbo.fn_securitypredicate(Wing) ON

Patients

WITH (STATE = ON)

Filter

predicate:

INNER

JOIN…

Security

policy

Application Patients

SELECT * FROM Patients

SELECT * FROM Patients

SEMIJOIN APPLY dbo.fn_securitypredicate(patients.Wing);

SELECT Patients.* FROM Patients,

StaffDuties d INNER JOIN Employees e ON (d.EmpId =

e.EmpId)

WHERE e.UserSID = SUSER_SID() AND Patients.wing = d.Wing;

Nurse Policy manager

Seguridad a nivel de fila con contexto de usuario

19

----Se crea una función predicado que utliza la variable CONTEXT_INFO de la

aplcición para filtrar que registros puede ver.

CREATE FUNCTION rls.fn_securitypredicate (@AppUserId int)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN (

SELECT 1 AS fn_securitypredicate_result

WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo')-- application context

AND CONTEXT_INFO() = CONVERT(VARBINARY(128), @AppUserId);

GO

-- Con el siguiente código creamos la politica de seguridad con filtro de

predicado para la table customers, y lo dejamos deshabilitado

CREATE SECURITY POLICY [FederatedSecurityPolicy]

ADD FILTER PREDICATE [rls].[fn_securitypredicate]([CustomerId])

ON [dbo].[Customer];

Seguridad a nivel de fila: casos de uso

Aplicaciones tradicionales

Finanzas, Seguros, sanidad, gobiernos

BBDD multitenant

Cada inquilino solo accede a sus datos

Reporting, analytics,

DW

Diferentes usuarios ven datos según su nivel de acceso (PowerBI ya da soporte nativo)

20

SOLIDQ SUMMIT MADRID 2017 21

Funcionalidad de RLS

• Centralizo la seguridad en la BBDD, solo tengo que tocar en un sitio.

• Puedo bloquear o filtrar las operaciones de modificación de datos.

• Si creo el predicado con la opción schemabinding, evito que alguien pueda modificar el esquema de las tablas con las que trabaja el predicado y se comprueban los permisos sobre esas tablas para que funcione.

• Puedo generar un role tipo “Gestor de Politicas” con los permisos necesarios para generar predicados de seguridad ALTER ANY SECURITY POLICY y un esquema especifico para generar estas, de tal manera que separo funcionalidades. Pudiendo auditar los cambios que se realicen.

Mejores Practicas con RSL

• Rendimiento dependerá de la complejidad de los joins en el predicado.

• Es recomendable que las columnas que utiliza el predicado para filtrar tengan un índice.

• Crear un esquema diferente para centralizar en él, todas las políticas de seguridad.

• Utilizar CONTEXT_INFO para aplicaciones de capa intermedia y cuando trabajemos en SQL AZURE siempre inicializar esta variable después de conectarnos porque por defecto la inicializa.

Always Encrypted

Prevención de revelación de datos

• Encriptación a nivel cliente de datos sensibles

• No se puede descrifrar desde servidor

Consultas sobre datos cifrados

• Soporte para operadores “=“, “join”, “Group by” y “distinct”

Transparente para aplicaciones

• Cambios mínimos a nivel de aplicación

• Usar la libreria correcta

25

Permite a las aplicaciones almacenar de forma segura datos, incluso lejos del alcance de DBA sysadmin

Always Encrypted

26

Always Encrypted

28

Always Encrypted: Limitaciones No soportado con estos tipos

de datos

•Xml

•Rowversion

• Image/Text/ntext

•Sql_variant

•Hiearchyid

•Geography/geometry

•Alias

•UDT´s

Claúsulas que no funcionan

•FOR XML

•FOR JSON PATH

Características incompatibles

•Transactional replication

•Merge replication

•Linked server y consultas distribuidas

29 © 2016 SolidQ

Cuidado con la corrupción de datos

• Usando ALLOW_ENCRYPTED_VALUE_MODIFICATIONS puedes corromper tus propios datos

Herramientas soportadas parcialmente (por ahora)

• SSDT (por ahora)

• Solo se permite crear DACPAC desde SSMS

SOLIDQ SUMMIT MADRID 2017 30

Comparativa entre AE y TDE

Always Encrypted TDE

Nivel de columna Nivel de BBDD

Encriptación en cliente Encriptación en servidor

El servidor no conoce en ningún momento las claves para encriptar

El servidor conoce las claves para encriptar

EL dato viaje por la red encriptado EL dato tal cual viaja por la red.

Tempdb hereda la encriptación- incluso después de deshabilitar TDE

EL dato puede ser protegido de administradores de sistema y bbdd

El dato es accesible para administradores de sistema y bbdd

Comportamiento consultas con Always Encripted

Características de la

consulta

Always Encrypted esta

habilitado y la

aplicación puede

acceder a las claves y

al metadata de las

claves.

Always Encrypted esta

habilitado y la

aplicación no puede

accede a las claves o a

los metadatos de las

claves

Always Encrypted esta

deshabilitado

Consultas con parametros que hacen referencia a columnas encriptadas.

Los valores de los parametros se encriptan transparentemente.

Error Error

Consultas que recuperan datos de columnas encriptadas, sin parametros que referencien columnas encriptadas.

EL resultado de las columnas encriptadas se desencripta transparentamente. La aplicacion recive los valores en texto plano de los tipos de datos .NET correspondientes a los tipos de SQL configurados para las columnas encriptadas.

Error EL resultado de las columnas encriptadas no se desencripta. La aplicación recive los valores encriptados como arrays de byte (byte[]).

Controlando el impacto de AE en el rendimiento • Debido a que Always Encrypted es una tecnología de cifrado en el

cliente, la mayoría de los overheads de rendimiento se observan en el lado del cliente, no en la base de datos. Aparte del costo de las operaciones de cifrado y descifrado, hay otros factores que afectan:

Viajes de ida y vuelta adicionales a la base de datos para recuperar

metadatos para los parámetros de la consulta.

Llamadas al almacén de claves maestras de columna para acceder a una clave maestra de columna.

Optimizaciones en .NET Framework Provider para SQL Server para AE • Query metadata caching

Puedes configurar Always Encrypted a nivel de consulta y de no de toda la sesión(esto ya no tiene sentido con .Net 4.6.2 debido al cache de parametros).

• Column encryption key caching En .NET Framework 4.6 y 4.6.1, las entradas de clave de cifrado de columna en el

caché nunca se desalojan. Esto significa que para una clave cifrada de cifrado de columna dada, el controlador contacta con el almacén de claves sólo una vez durante el tiempo de vida de la aplicación.

En .NET Framework 4.6.2 y posteriores, las entradas de la memoria caché son desalojadas después de un intervalo configurable de tiempo de vida por razones de seguridad. El valor por defecto del tiempo de vida es de 2 horas.

Dynamic Data Masking

Enmascaramiento

• Datos sensibles enmascarados a nivel Usuario

Basado en políticas

• A nivel de tabla o columna para grupos de usuarios

Tiempo real

• Se aplica sobre las filas a devolver

36

Dynamic data masking

37 © 2016 SolidQ

3 ) DDM ofusca los datos sensibles durante la devolución de datos 2 ) El Usuario lanza una select 1 ) El administrador de seguridad define la mascara en T-SQL sobre datos sensibles

SELECT [Name],

[SocialSecurityNumber],

[Email],

[Salary]

FROM [Employee]

admin1 login other login

BUSINESS APP

BUSINESS APP

ALTER TABLE [Employee] ALTER COLUMN [SocialSecurityNumber] ADD MASKED WITH (FUNCTION = ‘SSN()’) ALTER TABLE [Employee] ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION = ‘EMAIL()’) ALTER TABLE [Employee] ALTER COLUMN [Salary] ADD MASKED WITH (FUNCTION = ‘RANDOM(1,20000)’) GRANT UNMASK to admin1

Security

Officer

Dynamic data masking

• No puede usarse sobre los siguientes tipos de datos • Always Encrypted columns

• FILESTREAM

• COLUMN_SET

• Text y ntext si soportados • Pero no funcionarán READTEXT, UPDATETEXT ni WRITETEXT

38

SOLIDQ SUMMIT MADRID 2017 39

SOLIDQ SUMMIT MADRID 2017

• +500h de trabajo

• Clases 100% ONLINE en directo

• Proyecto fin de máster real para tu empresa

• Tutorías bajo demanda

• Laboratorios virtuales guiados paso a paso

• Invitación 1 día al SolidQ Summit Madrid

• Te ayudamos en tus proyectos de BI & Analytics

CONTENIDOS

• BI & Analytics Overview

• Data Warehousing y Modelo Dimensional

• Obtención de datos. ETL e Integración

• Soluciones Analíticas

• Análisis y Visualización de la información

• Proyecto Fin de Máster

“Con la evolución de los tiempos, es imprescindible estudiar todo lo que tiene que ver con real-time, analytics y el mundo de los datos. Por lo que, consideré que tener formación en BI me podría ser muy útil y me venía muy bien personalmente, porque nuestro sistema trata con muchísimas transacciones y muchísima carga y ‘el relacional’ ya se nos estaba quedando pequeño. El máster nos dio una visión bastante general de cómo implementar un proyecto y gestionarlo tú mismo y cómo ver las soluciones de distintas maneras. Aunque tenía mis dudas de hacer una formación online, la valoración es bastante positiva, las clases son muy interactivas y al final salió todo muy bien.”

Adolfo Gabriel VP Software

Development, Payvision Máster en BI & Analytics

Alumni

¡Plazas Limitadas! Más información:

http://www.solidq.com/es/masterbi

CONTENIDOS

• BI & Analytics Overview

• Data Warehousing y Modelo Dimensional

• Obtención de datos. ETL e Integración

• Soluciones Analíticas

• Análisis y Visualización de la información

• Proyecto Fin de Máster

SOLIDQ SUMMIT MADRID 2017