Desarrollo de bases de datos con SQL Server | Discount Coupon for Udemy Course

Las Tablas versionadas o temporales se introdujeron en el estándar ANSI SQL 2011 y en SQL Server 2016 y es un tema a evaluar en la certificación de Microsoft para Consultas con SQL Server.

Una tabla versionada del sistema le permite consultar los datos actualizados y eliminados, mientras que una tabla normal sólo puede devolver los datos actuales. Por ejemplo, si se actualiza un valor de columna del 5 al 10, sólo se puede recuperar el valor 10 en una table normal. Una tabla versionada también le permite recuperar el valor anterior 5. Esto se logra manteniendo una tabla de historial. Esta tabla almacena los datos de la historia antigua, junto con un conjunto de datos de inicio y fin para indicar cuando el registro fue activo.

Estos son los casos de uso más comunes para las tablas temporales:

• Auditoría. Con tablas temporales puede averiguar qué valores una entidad específica ha tenido en toda su vida.

• Cambio de dimensiones lentas en un Data Warehouse. Una tabla versionada del sistema se comporta exactamente como una dimensión de tipo 2 cambiando comportamientos de sus propias tablas En este caso, la tabla de dimensión incluye los campos de fecha inicio de vigencia y fecha fin de vigencia. Estas fechas nos permiten determinar en qué estado estaba la dimensión en cualquier fecha del calendario.

• Reparación de corrupciones a nivel de registro. Que sería como una especie de mecanismo de copia de seguridad en una sola tabla.

• Eliminación accidental de un registro. Recuperar el archivo de la tabla historial e insertarla de nuevo en la tabla principal.

Creación de una tabla versionada del sistema

Cuando se desea crear una nueva tabla temporal, un par de pre–requisitos se deben cumplir:

• Se debe definir una clave principal

• Dos columnas deben ser definidos para registrar la fecha de inicio y final con un tipo de datos de datetime2. Si es necesario, estas columnas se pueden ocultar mediante el indicador oculto.
  Estas columnas se llaman las columnas de tiempo SYSTEM_TIME.

• Los triggers INSTEAD OF no están permitidos. Los triggers AFTER sólo están permitidos en la tabla actual.

• Dentro de la memoria OLTP no se puede utilizar

También hay algunas limitaciones:

• tabla temporal y la historia no puede ser FileTable

• La tabla de la historia no puede tener ninguna restricción

• INSERT y UPDATE no pueden hacer referencia a las columnas de época SYSTEM_TIME

• Los datos de la tabla de historia no pueden ser modificados

La siguiente secuencia de comandos crea una sencilla tabla versionada sistema:

CREATE TABLE dbo.TestTemporal
(ID int primary key, Multiplicando int, Multiplicador int
,Resultado AS A * B
,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);

Si no se especifica un nombre para la tabla de historial, SQL Server generará automáticamente una de la siguiente estructura: dbo.MSSQL_TemporalHistoryFor_<XXX>, donde <XXX> es el identificador de objeto de la tabla principal.

XML

XML es un lenguaje de marcado similar a HTML. is a markup language similar a HTML. Significa Extensible Markup Language (Lenguaje de Marcado Extensible) y es una especificación de W3C como lenguaje de marcado de propósito general. Esto significa que, a diferencia de otros lenguajes de marcado, XML no está predefinido, por lo que puedes y debes definir tus propias etiquetas, esto tiene una ventaja para las bases de datos ya que definir tus propias etiquetas puede adaptarse transportar datos.

SQL Server 2016 introduce una nueva característica de seguridad llamada Dynamic Data Masking (DDM) que no está destinada a ser una súper medida de seguridad infalible, como lo es el cifrado, pero se puede considerar como una ofuscación o bloqueo de información a los usuarios con menos privilegios, y puede ser fácil de aplicar con restricciones de enmascaramiento.

Para mostrar cómo funciona la característica, vamos a crear una base de datos con una tabla simple a la que insertaremos 10 filas.

CREATE database TestDataMasking
go

Use TestDataMasking
go

--Creacion de tabla
Create table dbo.Cliente
(Cliente_Codigo int,
Cliente_Nombre varchar(100),
Cliente_FechaInicio datetime,
Cliente_EMail varchar(100),
Cliente_TarjetaCredito Varchar(100)
)
go
-- ingreso de datos de ejemplo
Insert into dbo.Cliente values(101,‘Visoalgt’,‘2016-08-11 00:34:51:090’,‘YoshiTannamuri@visoalgt.com’,‘9135-5555-8798’)
Insert into dbo.Cliente values(102,‘Cardenas Corp’,‘2016-01-08 19:44:51:090’,‘DanielTonini@cardenas.com’,‘1555-9857-8709’)
Insert into dbo.Cliente values(103,‘Alfreds Futterkiste’,‘2015-08-19 19:44:51:090’,‘PhilipCramer@futterkiste.com’,‘7675-3425-3433’)
Insert into dbo.Cliente values(104,‘Antonio Moreno Taquería’,‘2014-08-19 19:44:51:090’,‘PatriciaMcKenna@tqueria.com’,‘5535-0297-6523’)
Insert into dbo.Cliente values(105,‘Around the Horn’,‘2014-08-04 19:44:51:090’,‘YoshiLatimer@araound.com’,‘1354-2534-4534’)
Insert into dbo.Cliente values(106,‘Berglunds snabbköp’,‘2015-08-10 19:44:51:090’,‘chbrancar@beglunds.com’,‘5203-4560-5455’)
Insert into dbo.Cliente values(107,‘Bs Beverages’,‘2015-04-17 19:44:51:090’,‘SimonCrowther@beverages’,‘555-9482-3587’)
Insert into dbo.Cliente values(108,‘Bólido Comidas preparadasCable VGA’,‘2015-08-21 19:44:51:090’,‘FelipeIzquierdo@bolido.com’,‘555-7555-4545’)
Insert into dbo.Cliente values(109,‘X enterprice’,‘2015-08-06 19:44:51:090’,‘CarlosGonzalez@xenterprice.com’,‘2833-2951-4544’)
Insert into dbo.Cliente values(110,‘Chop-suey Chinese’,‘2015-08-26 19:44:51:090’,‘JohnSteel@chop.com’,‘5535-1340-3453’)
go
--Consultar la tabla solo para revisión.
Select * from Cliente

Ahora, vamos a añadir algunas máscaras para algunas de estas columnas para proteger la privacidad de los datos de los usuarios normales. Hay cuatro tipos de máscaras soportados actualmente:

• Default()

  • para cadenas de texto, muestra x para cada carácter (hasta 4)

  • para los tipos numéricos, muestra 0

  • para las fechas, muestra el 1900-01-01

• email()

  • revela el primer carácter, a continuación, reemplaza el resto con XXX@XXXX.com

• partial()

  • se puede definir una cadena personalizada para representar la máscara, incluyendo
    cuántos caracteres iniciales y finales se van a mostrar de la cadena original.

• Random()

  • Una función de enmascaramiento aleatorio que se puede usar con cualquier tipo
    numérico a fin de enmascarar el valor original con uno aleatorio dentro de un intervalo especificado.

Ejemplo:

-- Se mostraran los dos primeros caracteres y el ultimo carácter del nombre

ALTER TABLE dbo.Cliente ALTER COLUMN Cliente_Nombre ADD MASKED WITH (FUNCTION = ‘partial(2,”XXXXXXXX”, 1)’);
-- Todas las direcciones de correo se mostraran nXXX@XXXX.com
ALTER TABLE dbo.Cliente ALTER COLUMN Cliente_Email ADD MASKED WITH (FUNCTION = ’email()’);
-- Se mostrará el dato de la siguiente manera nXXX-XXXX-XXXXn
ALTER TABLE dbo.Cliente ALTER COLUMN Cliente_TarjetaCredito ADD MASKED WITH (FUNCTION = ‘partial(1,”XXXX-XXXX-XXXXX”,1)’);
-- Todas las fechas las mostrara como 1900-01-01

ALTER TABLE dbo.Cliente ALTER COLUMN Cliente_FechaInicio ADD MASKED WITH (FUNCTION = ‘default()’);

Para un administrador de sistemas o db_owner, no hay enmascaramiento aplicado en absoluto, pero para un usuario con los privilegios más básicos al momento de efectuar la consulta a la tabla los datos se enmascararán, a menos que conceda el permiso de UNMASK al usuario.

--creación de usuarios para prueba

Create user VICTOR without login;

--asignación de permisos a los usuarios de prueba

GRANT SELECT ON dbo.Cliente to VICTOR;

--Ejecución de una consulta en nombre del usuario recién creado

Execute (‘SELECT * FROM DBO.Cliente’) as user=‘VICTOR’;
go

Limitaciones y restricciones

No se puede definir una regla de enmascaramiento para los siguientes tipos de columnas:

• Columnas cifradas (siempre cifradas)

• FILESTREAM

• COLUMN_SET o una columna dispersa que forme parte de un conjunto de columnas.

• No se puede configurar una máscara en una columna calculada, pero si esta última depende de otra con una máscara, entonces la
  columna calculada devolverá datos enmascarados.

• Una columna con enmascaramiento de datos no puede ser una clave para un índice FULLTEXT.

Modificar o eliminar un enmascaramiento.
--Modificar una mascara
ALTER TABLE dbo.Cliente
ALTER COLUMN Cliente_TarjetaCredito
ADD MASKED WITH (FUNCTION = ‘partial(0,”XXXX-XXXX-XXXXX”,4)’);
--Consultar
Select * from dbo.Cliente

--Eliminar una Mascara
ALTER TABLE dbo.Cliente ALTER COLUMN Cliente_TarjetaCredito DROP MASKED;