Gå direkte til indhold
Kund hos itm8
Kund hos itm8

Dynamisk datamaskering

lästid I minuter: 6

FXA 2018-feb-22 16:52:00

En ny säkerhetsfunktion har från och med SQL Server 2016 och Azure SQL Database nu implementerats, nämligen möjligheten att maskera data (Dynamic Data Masking).

 

Varför skulle vi vilja maskera data? Varför inte bara kryptera den? Till skillnad från kryptering, kan man byta ut en del av en kolumns innehåll med maskering. Vid kryptering kan man inte välja att kryptera en del av en sträng, utan man slår på eller av krypteringen. Det kan vara så att man i en helpdesk inte ska kunna se all känslig data, men en del av den. De kanske identifierar en kund med hjälp av de siffror i ett personnummer som inte är de 4 sista. Det kan också vara så att kreditkortsuppgifter identifierar en kund med de sista 4 siffrorna i kreditkortsnumret, eller att en databas återställs för utvecklare/testare varje dag för att man behöver live data. I sådana här situationer kan det vara på sin plats att maskera en del av informationen för den som hanterar informationen, men inte allt.

Förnamn Efternamn Kortnummer
Olle Svensson xxxx-xxxx-xxxx-1234
Gunilla Jansson xxxx-xxxx-xxxx-4321

 

Även om man inte kan se all data i klartext, kan man fortfarande, om man har rätt behörigheter, köra UPDATE och DELETE på rader som har dynamisk maskering påslagen. Man kan också lägga till rader, som då blir maskerade vid en SELECT.

Behörigheter

För att skapa en tabell med en eller flera kolumner med dynamiskt maskerad data kräver bara CREATE TABLE och ALTER på schema-behörighet.

För att lägga till, ändra eller ta bort en dynamisk maskering kräver ALTER TABLE och ALTER ANY MASK.

Om man ingår i rollen SA eller i rollen db_owner ser man all data, även den som är maskerad. Om man inte ingår i dessa roller kan man bara se omaskerad data om man inte explicit har fått behörigheten UNMASK genom GRANT UNMASK TO NewUser;

Export/Import

Om man gör en export/import av data som ingår i dynamiskt maskerade kolumner och man inte har UNMASK behörigheter när exporten görs, kommer de kolumner som är maskerade att fortsätta vara maskerade, till och med statiskt maskerade. Detta innebär att även om du har behörighet att se datan i maskerade kolumner som importerats, så är dessa kolumner fortfarande maskerade.

Om man inte har behörighet att se maskerad data och fyller en temporär tabell med den maskerade datan, fylls den temporära tabellen med statiskt maskerad data. Det betyder att om du sedan tittar på datan i denna temporära tabell, så är den maskerade datan maskerad även för dig som behörig och detta går inte att ändra på.

Att tänka på är att om man har behörighet att se maskerad data och skapar en temporär tabell och fylla den med data genom att köra:

SELECT CreditcardNumber INTO #TempTable FROM MaskedTable

kommer datan inte att vara maskerad för den person som egentligen inte har behörighet. Man måste direkt efter att tabellen skapats köra kommandot för att maskera data, eller först skapa en temporär tabell med:

CREATE TABLE #TempTable
och då lägga på masken direkt innan man fyller tabellen med data.

Begränsningar

Det finns så klart begränsningar. Dynamisk maskering fungerar inte på kolumner med beroenden, inte på Filestream och inte på Sparce columns. Inte heller computed columns eller nyckelkolumner för Fulltext index.

Olika typer av maskeringar

Det finns 4 olika fördefinierade maskeringsfunktioner att använda:

Default
Maskerar allt som finns i kolumnen. Olika datatyper maskeras på olika sätt.

ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')

Email
Visar bara 1:a tecknet i emailadressen och ett statiskt suffix i formen ”.com”

Exempel: axxxxx@xxxx.com

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

Random
En slumpmässig funktion som kan användas på vilken numerisk typ som helst där man anger övre- och nedre gränser:

ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')

Partial
En ren strängutbytarfunktion där man anger hur många tecken som ska visas i början och slutet och vad man ska visa istället mellan dessa.

Exempel personnummer: 19771223-xxxx

ALTER COLUMN IdNumber ADD MASKED WITH (FUNCTION = 'partial(0,"xxxxxxxx-",4)')

Exempel kreditkort: 1238-xxxx-xx54-8523

ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION = 'partial(5,"xxxx-xx",7)')

Exemplen ovan är alla med ALTER på tabell, men man kan så klart ange den dynamiska masken redan när man skapar tabellen med följande syntax:

CREATE TABLE...PhoneNumber varchar(12) MASKED WITH (FUNCTION = 'default()') NOT NULL&gt; 1</pre>

Alla typer av maskeringar går att implementera med TSQL, både via queryfönster i Management Studio och queryfönster i din Azure Portal, men i Azure har du även möjlighet att göra det via ditt gränssnitt. Gör följande:

  • Gå in i din Azure-portal
  • Välj den databas som du vill lägga en maskering på
  • I den vänstra kolumnen under Settings, välj Dynamic Data Masking
  • Välj den kolumn som du vill skapa en maskering till genom att klicka på den blå knappen Add Mask
  • Längst upp i bild lades det nu till en maskering på den kolumn du valt. Det är Default-maskering som skapas med automatik. Vill du ha en annan maskering klickar du på den rad som skapades. Du får då fram en rad med fasta fält och en dropdownmeny där du kan välja annan maskering
  • När du valt din nya maskering klickar du på Update och sedan klickar du på krysset för att stänga dialogen
  • Du kommer nu tillbaka till ursprungsfönstret där du kan fortsätta välja maskeringar. Om du är nöjd klickar du bara på Save längst upp till vänster i fönstret

Slutligen

Tänk på att detta inte ersätter andra säkerhetsaspekter utan är bara ytterligare ett lager man kan tillgå för att snabbt och enkelt kunna skala bort data som inte ska synas.

Exempelkod att köra

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Skapa en tom databas med namnet DDM_Test

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

CREATE DATABASE [DDM_Test]

GO

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Skapa ett login

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

CREATE LOGIN [DDM_Login] WITH PASSWORD=N'BananKula', DEFAULT_DATABASE=[DDM_Test]

GO

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Skapa Usern i databasen och sätt läsbehörighet på denne

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

USE [DDM_Test]

GO

CREATE USER [DDM_User] FOR LOGIN [DDM_Login] WITH DEFAULT_SCHEMA=[dbo]

GO

ALTER ROLE db_datareader ADD MEMBER [DDM_User]

GO

 

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Alternativ 1: Skapa tabellen inklusive maskerignarna

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

CREATE TABLE [dbo].[DDM_Table_Include](

[Id] [int] IDENTITY(1,1) NOT NULL,

[StringValue_Default] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NOT NULL,

[DateTimeValue_Default] [datetime] MASKED WITH (FUNCTION = 'default()') NOT NULL,

[NumericValue_Default] [bigint] MASKED WITH (FUNCTION = 'default()') NOT NULL,

[EmailValue_Email] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NOT NULL,

[NumericValue_Random] [bigint] MASKED WITH (FUNCTION = 'random(7,8)') NOT NULL,

[CreditCard_1_Custom] [nvarchar](19) MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)') NOT NULL,

[CreditCard_2_Custom] [nvarchar](19) MASKED WITH (FUNCTION = 'partial(5,"xxxx-xx",7)') NOT NULL

) ON [PRIMARY]

GO

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Altgernativ 2: Skapa tabellen och lägg på maskerignarna i

--         efterhand

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

CREATE TABLE [dbo].[DDM_Table_Exclude](

[Id] [int] IDENTITY(1,1) NOT NULL,

[StringValue_Default] [nvarchar](50) NOT NULL,

[DateTimeValue_Default] [datetime] NOT NULL,

[NumericValue_Default] [bigint] NOT NULL,

[EmailValue_Email] [nvarchar](50) NOT NULL,

[NumericValue_Random] [bigint] NOT NULL,

[CreditCard_1_Custom] [nvarchar](19) NOT NULL,

[CreditCard_2_Custom] [nvarchar](19) NOT NULL

) ON [PRIMARY]

GO

 

-- Lägg på maskeringarna

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN StringValue_Default ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN DateTimeValue_Default ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN NumericValue_Default ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN EmailValue_Email ADD MASKED WITH (FUNCTION = 'email()')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN NumericValue_Random ADD MASKED WITH (FUNCTION = 'random(5,6)')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN CreditCard_1_Custom ADD MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)')

ALTER TABLE [dbo].[DDM_Table_Exclude] ALTER COLUMN CreditCard_2_Custom ADD MASKED WITH (FUNCTION = 'partial(5,"xxxx-xx",7)')

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Fyll på med lite data

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

INSERT INTO [dbo].[DDM_Table_Include] VALUES

(N'Bananfluga', '2018-01-15 10:25:19.123', 123456789, N'bananfluga@hotmail.se', 987654321, N'1234-4567-7896-8523', N'1234-4567-7896-8523'),

(N'Kodänga', '2018-02-22 13:14:15.123', 123456789, N'kodanga@gmail.se', 987654321, N'9874-8523-9854-7896', N'9874-8523-9854-7896'),

(N'Griskulting', '2017-12-22 09:55:33.123', 123456789, N'griskulting@spotify.net', 987654321, N'9654-8569-8741-8547', N'9654-8569-8741-8547')

 

INSERT INTO [dbo].[DDM_Table_Exclude] VALUES

(N'Bananfluga', '2018-01-15 10:25:19.123', 123456789, N'bananfluga@hotmail.se', 987654321, N'1234-4567-7896-8523', N'1234-4567-7896-8523'),

(N'Kodänga', '2018-02-22 13:14:15.123', 123456789, N'kodanga@gmail.se', 987654321, N'9874-8523-9854-7896', N'9874-8523-9854-7896'),

(N'Griskulting', '2017-12-22 09:55:33.123', 123456789, N'griskulting@spotify.net', 987654321, N'9654-8569-8741-8547', N'9654-8569-8741-8547')

 

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Nu tittar vi på det som lagts in.

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- Man ser all data

SELECT * FROM [dbo].[DDM_Table_Include]

 

-- Byt användare till den som bara ha läsbehörighet

EXECUTE AS USER = 'DDM_User';

-- Man får fram data som är maskerad

SELECT * FROM [dbo].[DDM_Table_Include]

 

-- Gå tillbaka till sa/db_owner

REVERT;

-- Man ser nu all data igen

SELECT * FROM [dbo].[DDM_Table_Include]

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Här ser vi att om vi skapar en temporär tabell samtidigt som

--   vi fyller den och man har behörigheten att se all data,

--         kommer maskeringen inte att skapas/läggas på

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

IF OBJECT_ID('tempdb..#DDM') IS NOT NULL

DROP TABLE #DDM

 

SELECT * INTO #DDM FROM [dbo].[DDM_Table_Include]

SELECT * FROM #DDM

 

EXECUTE AS USER = 'DDM_User';

SELECT * FROM #DDM

 

REVERT;

SELECT * FROM #DDM

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Här ser vi att om vi skapar en temporär tabell samtidigt som

--   vi fyller den och man INTE har behörigheten att se all data,

--         kommer maskeringen att skapas/läggas på statiskt, dvs inte

--         heller den som har behörighet att se data ser något.

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

IF OBJECT_ID('tempdb..#DDM') IS NOT NULL

DROP TABLE #DDM

 

EXECUTE AS USER = 'DDM_User';

SELECT * INTO #DDM FROM [dbo].[DDM_Table_Include]

SELECT * FROM #DDM

 

REVERT;

SELECT * FROM #DDM

 

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

-- * Här ser vi att om vi först skapar en temporär tabell och

--         lägger på maskeringen, så är datan maskerad för de som bara

--         har SELECT

--¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤

IF OBJECT_ID('tempdb..#DDM') IS NOT NULL

DROP TABLE #DDM

 

CREATE TABLE #DDM(

[Id] [int] NOT NULL,

[StringValue_Default] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NOT NULL,

[DateTimeValue_Default] [datetime] MASKED WITH (FUNCTION = 'default()') NOT NULL,

[NumericValue_Default] [bigint] MASKED WITH (FUNCTION = 'default()') NOT NULL,

[EmailValue_Email] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NOT NULL,

[NumericValue_Random] [bigint] MASKED WITH (FUNCTION = 'random(7,8)') NOT NULL,

[CreditCard_1_Custom] [nvarchar](19) MASKED WITH (FUNCTION = 'partial(3,"x-xxxx-xxxx-xx",2)') NOT NULL,

[CreditCard_2_Custom] [nvarchar](19) MASKED WITH (FUNCTION = 'partial(0,"xxxx-xxxx-xxxx-",4)') NOT NULL

)

 

INSERT INTO #DDM

SELECT * FROM [dbo].[DDM_Table_Include]

 

SELECT * FROM #DDM

 

EXECUTE AS USER = 'DDM_User';

SELECT * FROM #DDM

 

REVERT;

SELECT * FROM #DDM

 

​

Ska vi träffas och prata om vad vi konkret kan göra för dig?