go backarticles

Articles of SQLschool.gr Team

Dynamic Data Masking

Kyriakos Petsalaris

Overview

Οι περισσότεροι απο εμάς έχουμε δεδομένα τόσο στο Azure όσο και σε on-premise περιβάλλοντα που θέλουμε να διασφαλίσουμε τόσο για λόγους security όσο και για λόγους Regulation.(GDPR).

To DDM ( Dynamic Data Masking) είναι ένα Feature που το γνωρίσαμε πρώτη φορά με την έλευση του SQL Server 2016 (130).

Το DDM περιορίζει την έκθεση προσωπικών δεδομένων σε μή-εξουσιοδότημένους χρήστες με την μέθοδο του masking.Με αυτόν τον τρόπο μπορούμε να διασφαλισουμε τα δεδομένα μας μέσα απο τις εφαρμογές μας απλοποιώντας τον σχεδιασμό του κώδικα μας όταν και όπου αυτό απαιτείται ανάλογα με τις αναγκες της εφαρμογής μας.Η μέθοδος αυτή δεν αλλάζει τα δεδομένα σε μία database γιατί το Data Masking γίνεται on the fly.

Permissions

Για να εφαρμοσουμε το Dynamic Data Masking θα πρέπει το Login να έχει το CREATE TABLE και το ALTER permission στο schema της βάσης.Για να αντικαταστήσουμε ,διαγράψουμε προσθέσουμε το masking σε μία κολόνα θα πρεπει να έχουμε το ALTER ANY MASK και το ALTER permission σε αυτήν.

Για να διαβάσουμε τα δεδομένα μιας masked κολόνας αρκεί ο Database user να έχει το Select Permission αυτής. Το CONTROL Permission μας δίνει την δυνατότητα να κάνουμε MASK τα δεδομένα μιας κολόνας με το ALTER ANY MASK και UNMASK αν θέλουμε να διαβάσουμε τα masked περιεχόμενα αυτής.

Ένας κανόνας απόκρυψης (masking rule) μπορεί να οριστεί σε μια στήλη σε έναν πίνακα, προκειμένου να συγκαλυφθούν τα δεδομένα σε αυτήν τη στήλη. Διατίθενται τέσσερις τύποι data masking.

Default

Πλήρης masking της στήλης .Για numeric data types γινεται χρήση μηδενικής τιμής (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).

Για date και time data types γίνεται η χρήση της τιμής 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).

Για string data types, χρησιμοποιείται το XXXX ή λιγότερα Xs ανάλογα με το μέγεθος του πεδίου (char, nchar, varchar, nvarchar, text, ntext).

Για binary data types χρησιμοποιήστε ένα byte της τιμής ASCI με τιμή 0.

Email

Αυτή η μέθοδος masking εκθέτει το πρώτο γράμμα μιας διεύθυνσης email και το σταθερό πρόθεμα πχ ".gr", με τη μορφή διεύθυνσης email πχ cXXX@XXXX.gr.

CustomString

Μέθοδος που εκθέτει τα πρώτα και τελευταία γράμματα και προσθέτει μια προσαρμοσμένη συμβολοσειρά padding στo μέσο της πληροφορίας.

Random

Είναι ή λειτουργία τυχαίας κάλυψης για χρήση σε οποιοδήποτε numeric data type που εφαρμόζει τυχαίο masking σε ένα εύρος τιμών μιας στήλης.

Samples

Ας προχωρήσουμε στα ενδότερα με μερικά παραδείγματα εφαρμογής του DDM.

Στο step-1 και σε μία βάση επιλογής μας δημιουργούμε εναν πίνακα με κάποιες κολόνες και τα σχετικά Data Type τους.

Στην συνέχεια εισάγουμε μερικές εγγραφές στον πίνακα SensitiveData όπως φαίνεται στο step-2

Στο step-3 κάνουμε ενα Select να δούμε (Image-1) τα δεδομένα του πινακά που μόλις δημιουργήσαμε.

Στο step-4 κανουμε χρήση του default function με την ALTER TABLE στην κολόνα CreditRating.

Αν εκτελέσουμε ένα Select στον πίνακα SensitiveData θα δούμε ότι το result set δεν επιστρέφει τα masked data του column CreditRating και ο λόγος είναι ότι έχω κάνει login σαν sysadmin (SA) στο instance.

Επομένως δημιουργώ εναν non-privileged user με όνομα NPuser στο Step-5 και του δίνω δικαιώματα GRANT SELECT στον πίνακα SensitiveData.

Αν εκτελέσουμε το Select με τον χρήστη NPuser (Impersonate) θα δούμε τα mask δεδομένα της στήλης CreditRating.(Step-6)

Στο step-7 κάνουμε mask την κολόνα SSNR με την partial (Custom String) function.

Στο step-8 μπορείτε να δείτε επίσης την κολόνα creditcard να γίνεται masking με την partial functrion.

Στο step-9 γίνεται χρήση της function Email την κολόνα Email και στο step-10 εφαρμόζουμε την Function ‘random’ που σημαίνει ότι ή στήλη CreditRating θα πάρει τυχαίες τιμές απο το 1 εώς το 9 βάση της εντολης WITH (FUNCTION='random(1,9)') που εκτελέσαμε στο παράδειγμα.

Αν εκτελέσουμε πάλι το Select με τον Impersonate user(Step-11) το result set θα μας επιστρέφει (Image-2) τις κολόνες με τα δεδομένα να είναι masked.

Τέλος αν κάνουμε Select στο Context της βάσης μας το sys.masked_columns view θα μας επιστρέψει τους πίνακες και τις κολόνες που έχει γίνει χρήση με τις Fuction που έχουν χρησιμοποιηθεί.

Limitations

To Dynamic Data Masking δεν μπορεί να βρεί χρήση σε περιπτώσεις που έχουμε Filestream, Always Encrypted,σε dependencies columns πχ κάνουμε drop τον index – χρήση masking – recreate index και σε column-set ή σε sparse column που ανήκει σε ένα άλλο Column Set.

SQL Script

-- Step-1
   CREATE TABLE SensitiveData
   (
         Firstname varchar(20),
         LastName varchar(25),
         CreditRating int,
         SSNR varchar(12),
         CreditCard varchar(20),
         Email varchar(30),
    )
 
-- Step-2
   INSERT SensitiveData
   VALUES
          ('Kyriakos','Petsalaris',715,'656-52-6777','2345-4444-7788-8814','kyriakostest@gmail.com'),
          ('Anestis','Mavrid',615,'677-45-6999','1538-2233-75557789','anestis_test@gmail.com'),
          ('Argiris','Kandilor',715,'699-33-8877','3489-7865-1234-4444','argiris_test@gmail.com')

-- Step-3
   select * from SensitiveData

Image 1
image

SQL Script

-- Step-4
 
   ALTER TABLE SensitiveData
   ALTER COLUMN CreditRating ADD MASKED
   WITH (FUNCTION='default()')
   Select * from SensitiveData
 
-- Step-5
   CREATE USER NPUser WITHOUT LOGIN
   GRANT SELECT ON SensitiveData to NPUser;
 
 
-- Step-6
   EXECUTE AS USER='NPUser'
   SELECT * FROM SensitiveData;
   REVERT;
 
-- Step-7
   ALTER TABLE SensitiveData
   ALTER COLUMN SSNR ADD MASKED
   WITH (FUNCTION='partial(0,"XXX-XX-",4)')

-- Step-8
   ALTER TABLE SensitiveData
   ALTER COLUMN Creditcard ADD MASKED
   WITH (FUNCTION='partial(0,"CCCC-CCCC-CCCC-C",4)')
 
-- Step-9
   ALTER TABLE SensitiveData
   ALTER COLUMN Email ADD MASKED
   WITH (FUNCTION='email()')

   EXECUTE AS USER='NPUser'
   SELECT * FROM SensitiveData;
   REVERT;
             
-- Step-10
   ALTER TABLE SensitiveData
   ALTER COLUMN CreditRating ADD MASKED
   WITH (FUNCTION='random(1,9)')  

-- Step-11
   EXECUTE AS USER='NPUser'
   SELECT * FROM SensitiveData;
   REVERT;
             

Image 2
image


Image 3
image

Το Dynamic Data masking (DDM) είναι μια αναδυόμενη τεχνολογία που στοχεύει στην κάλυψη δεδομένων σε πραγματικό χρόνο (on the fly) των δεδομένων παραγωγής και αλλάζει τη ροή δεδομένων έτσι ώστε ο αιτών(Non-Privileged User) να μην έχει πρόσβαση στα ευαίσθητα δεδομένα, ενώ δεν πραγματοποιούνται φυσικές αλλαγές στα πραγματικά δεδομένα μιας παραγωγικής βάσης.

Είναι μια δυνατότητα που μπορεί επίσης αυτός που θα την χρησιμοποιήσει να είναι κανονιστικα συμμορφωμένος σύμφωνα με το GDPR το οποίο έχει εγκριθεί απο την ΕΕ.


Kyriakos Petsalaris

Kyriakos Petsalaris

Kyriakos is a Multifaceted Information Technology professional with a background in Information Technology Management. An experienced database and systems administrator, who also establishes clear objectives for team members and enforces deadlines to keep projects on track. Well versed in personnel training and supporting servers,Azure based Systems and Virtual Enviroments. He can do a bit of everything, but he has a passion for SQL Server and loves resolving complex live site incidents, as well as engineering continuous improvements to avoid repeated incidents. He is also skilled with writing, performance-tuning and debugging T-SQL, and developing automated and scalable maintenance/monitoring procedures to ensure optimal uptime across multiple systems. He has 20+ years experience as a Systems Engineer with specific expertise in SQL Server (from SQL 2005 to 2019). He works with both on-premise and Azure-based, customer-facing services including several Microsoft’s Products and have experience collaborating remotely with global teams in Bahrein,South Africa, Egypt and England.

  


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.