Forzar la eliminación de objetos en una Replicación

Cuando una replicación deja de funcionar apropiadamente y reiniciarla no basta, a veces es necesario borrarla y volverla a crear, pero el mismo daño que tiene no deja borrarla con las herramientas gráficas, por lo que tenemos que usar script con el parámetro, @ignore_distributor=1. Aquí un ejemplo de los procedimientos a ejecutar:

Forzar la eliminación del suscriptor.

--Seleccione la base de datos con la publicación
USE Northwind

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

--Nombre de la publicación

SET @publication = N'NorthwindPub';

--Nombre de la suscripcion

SET @subscriber = N'NorthiwndSub';

USE Northwind

EXEC sp_dropsubscription

@publication = @publication,

@article = N'all',

@subscriber = @subscriber,

@ignore_distributor=1;

GO

Forzar la eliminación de la publicación.

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

--Nombre de la base de datos con la publicación

SET @publicationDB = N'Northwind';

--Nombre de la publicación

SET @publication = N'NorthwindPub';

-- Remover la publicación

USE Northwind

EXEC sp_droppublication

@publication = @publication,

@ignore_distributor=1;

-- Remover objetos de la publicación

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N'publish',

@value = N'false';

GO

Eliminar el distribuidor del servidor:

USE master

GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

Enmascaramiento de datos dinámicos (Dynamic Data Masking)

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;

Row Level Security

En SQL Server 2016 la seguridad a nivel de fila (Row Level Security) es una característica que permite un control más preciso al acceso a las filas de una tabla. Row Level Security le permite controlar fácilmente qué usuarios pueden acceder a los datos con total transparencia a la aplicación, restringiendo los datos en función de la identidad del usuario o contexto de seguridad.

En nuestro ejemplo contamos con la tabla Ordenes, de la cual necesitamos limitar el acceso a las filas y que cada usuario solo pueda ver las Ordenes que ingreso y no las de otros usuarios.

Create database TestRowLevel

go

Use TestRowLevel

go

--Creacion de tabla

Create table dbo.Ordenes

(
Codigo_Cliente int,
Nombre_Producto varchar(100),
Fecha datetime,
Cantidad int,
ProcesadoPor Varchar(10)
)          
go

 -- ingreso de datos de ejemplo

Insert into dbo.Ordenes

values(101,'Monitores','2016-08-11 00:34:51:090',100,'SOFIA')
Insert into dbo.Ordenes
values(102,'Teclados CORP','2016-01-08 19:44:51:090',700,'SOFIA')
Insert into dbo.Ordenes
values(103,'Memoria RAM','2015-08-19 19:44:51:090',1500,'SOFIA')
Insert into dbo.Ordenes
values(102,'Disco Duro','2014-08-19 19:44:51:090',1099,'CLAUDIA')
Insert into dbo.Ordenes
values(101,'Web Cam','2014-08-04 19:44:51:090',5600,'CLAUDIA')
Insert into dbo.Ordenes
values(103,'Ratones','2015-08-10 19:44:51:090',498,'HUGO')
Insert into dbo.Ordenes
values(102,'Cable HDMI','2015-04-17 19:44:51:090',999,'HUGO')
Insert into dbo.Ordenes
values(101,'Cable VGA','2015-08-21 19:44:51:090',543,'VICTOR')
Insert into dbo.Ordenes
values(103,'Conectores RJ45','2015-08-06 19:44:51:090',876,'VICTOR')
Insert into dbo.Ordenes
values(102,'Memory Stick','2015-08-26 19:44:51:090',665,'VICTOR')
go

--Creación de la función de predicado que filtra que Ordenes fueron realizadas por cada usuario, el Filtro será aplicada durante la ejecución de la consulta

Create Function dbo.fn_SeguridadOrdenes (@ProcesadoPor sysname)

returns table with Schemabinding
as
return select 1 as [fn_SeguridadOrdenes_result]
from
dbo.Ordenes
where @ProcesadoPor = user_name() 
go

--Creacion de la politica de seguridad al crearla aplicará la limitación de acceso a los usuarios

Create security Policy fn_seguridad

add Filter Predicate
dbo.fn_SeguridadOrdenes(ProcesadoPor)
on dbo.Ordenes
go

--creación de usuarios para prueba

Create user VICTOR without login;

Create user HUGO without login;
Create user CLAUDIA without login;
Create user SOFIA without login;

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

GRANT SELECT ON dbo.Ordenes to CLAUDIA;

GRANT SELECT ON dbo.Ordenes to SOFIA;
GRANT SELECT ON dbo.Ordenes to HUGO;
GRANT SELECT ON dbo.Ordenes to VICTOR;

--Ejecución de una consulta en nombre de los usuarios para probar que cada usuario puede ver solo sus ordenes

Execute ('SELECT * FROM DBO.ORDENES') as user='VICTOR';

go

 

Execute ('SELECT * FROM DBO.ORDENES') as user='CLAUDIA';

go

 

Execute ('SELECT * FROM DBO.ORDENES') as user='SOFIA';

go