Τι είναι το Always Encrypted και πως εφαρμόζεται για την προστασία ευαίσθητων δεδομένων

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς συλλέγουμε to actual execution plan από τα queries με χρήση Extended Event και πως διαβάζουμε τα δεδομένα του - 2 Δεκέμβριος 2024
• Πώς βρίσκουμε τι δικαιώματα έχει ένας χρήστης σε βάση δεδομένων της Oracle - 1 Νοέμβριος 2024
• Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024

Στην εποχή του GDPR ας μιλήσουμε για άλλο ένα χαρακτηριστικό του SQL Server για την προστασία των ευαίσθητων δεδομένων. Δεν είναι άλλο από το Always Encrypted!

Τι είναι το Always Encrypted

Ως σκοπό έχει τα δεδομένα να είναι πάντα encrypted από την εφαρμογή από και προς την βάση δεδομένων. Περιέχει διαχωρισμό στα δικαιώματα μεταξύ  σε ποιόν ανήκουν τα δεδομένα και ποιος μπορεί να τα δει.

Πάμε να δούμε πως δουλεύει

Μπορούμε να ενεργοποιήσουμε το Always Encrypted  για συγκεκριμένα πεδία που περιέχουν  ευαίσθητα δεδομένα. Κάνει χρήση δύο κλειδιών του  column encryption key  που κρυπτογραφεί το πεδίο και του column master  key που κρυπτογραφεί τα υπόλοιπα κλειδιά.

Το encryption χωρίζεται σε δύο κατηγορίες

Deterministic: είναι η δημιουργία κρυπτογραφημένης τιμής που παραμένει η ίδια για την κάθε ίδια τιμή στο πεδίο. Π.χ. σε encryption της χώρας, κάθε πελάτης που έχει την τιμή Ελλάδα θα έχει την ίδια  ακριβώς κρυπτογραφημένη τιμή σε όλες τις εγγραφές. Αυτή η λειτουργία  δίνει τη δυνατότητα να μπορεί να χρησιμοποιηθεί το πεδίο αυτό για indexing, joins και pivoting,αλλά σαν μειονέκτημα δίνει τη δυνατότητα να μπορεί  κάποιος  να μαντέψει  την τιμή. Π.χ. από την στιγμή  που  θα  ξέρω ότι  ένας πελάτης είναι  στη χώρα  Ελλάδα  και  ξέρω τη κωδικοποιημένη τιμή έτσι ξέρω και ποιοι άλλοι  πελάτες  είναι  από Ελλάδα.

Randomized: είναι η δημιουργία κρυπτογραφημένης τιμής που κάθε φορά θα  είναι διαφορετική ακόμα και  για  την ίδια τιμή στο πεδίο.  Π.χ.  σε encryption της  χώρας κάθε  πελάτης  που  έχει την τιμή Ελλάδα θα έχει διαφορετική  κρυπτογραφημένη τιμή. Αυτή η  λειτουργία οδηγεί σε μεγαλύτερη ασφάλεια αλλά ως μειονέκτημα δεν δίνει την δυνατότητα να μπορεί κάποιος  να χρησιμοποιήσει  το  πεδίο  για  indexing, join και  groups.

Το παράδειγμα

Για αρχή φτιάχνουμε ένα  column encryption key και ένα master encryption key.

Αυτό μπορεί να γίνει με Τ-SQL και με το γραφικό περιβάλλον του SSMS.

Με χρήση T-SQL

USE [bi_test]
GO

CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
       KEY_PATH = N’CurrentUser/my/44866B5D9463FDAB3BBF961C2BBE78E01CCD5C35′
)
GO

CREATE COLUMN ENCRYPTION KEY [CEK_Auto1]
WITH VALUES
(
       COLUMN_MASTER_KEY = [CMK_Auto1],
       ALGORITHM = 'RSA_OAEP',
       ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003400340038003600360062003500640039003400360033006600640061006200330062006200660039003600310063003200620062006500370038006500300031006300630064003500630033003500555BAFF9D24FD8C7D15AD1E8850AA95921ABFD8213E80ED87D630EB3AD01AE60E758689FDC8F18ABCBC50AF48F927FBB22CBED7CFF871FE0579AB9B8F8A128AB5129AF5CA693C05AECE025EF02A344D36C4BD50AA665433D1F9C97B8FA605A79F4A54A0065A8252D78619A6D467547358033F31078326A75F8626FC7306611673B043A6E4D3AA99547E9B2A06967ABC488656229C19045A8145445A84338DDA807B40444E41093AA6BB0B5D29B16E9787EAD652042FFD73B264483CEE592B370233C51523BC843434D6201C5F3D51C5BE50B9EE03AE9A2C888C79FC4C5A09143625F55DD641359A14C21DD627FAEB13DD59AD019E8E92BE7638A171ADF8341326DC17EA3416F642FBCE8C2D34C54E5ED47F2E3D077C6D57907ADC54ED150193D7E6E605617A5D7EC53B7B21C8BBFBDF256CA7F16A41DB28821432454EA4EA976C07342060C2EFC07354BE3D7601A6E4591C0EEAAC563348444DA15CEEABB6AAF40E81C7303F66DA734630AEEAE0C9CFF02FB5EFA03985ABB21CA7AE52CE504802D17B2409226B18C8F3E9D6582ADADD512D285C533C58905B2097FD8BAEA143BCB159A03CE483040C19444ECD9E7D9C3C58897D98461988CA296A29F8C452CC110D65536CA36079C74AED3D7647A223F249BBA395E586E1A5DA4C31F1AD682C635ADEDB906B767780DBFF3598DAF20940E7C50101D2EE3FB1C3D91E4B6D89859
)
GO

Απαιτείται να καθαρίσουμε την cache:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Με SSMS

Από τη βάση – security – Always Encrypted Keys

Ενεργοποιούμε το  secure enclave για μεγαλύτερη ασφάλεια:

SELECT [name], [value], [value_in_use] FROM sys.configurations
WHERE [name] ='column encryption enclave type';

EXEC sys.sp_configure 'column encryption enclave type', 1;
GO

RECONFIGURE;

Φτιάχνουμε ένα πίνακα με ένα deterministic και ένα randomized encrypted πεδίο:

Με T-SQL

CREATE TABLE [dbo].[customer](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [name] [nvarchar](50) NULL,
       [surname] [nvarchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
       [sex] [nvarchar](20) NULL,
       [afm] [nvarchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)

*Σε περίπτωση που ο  πίνακας  περιέχει ήδη εγγραφές, θα πρέπει με ένα SSIS πακέτο να εξάγουμε και να ξανά  εισάγουμε τις εγγραφές, αλλιώς με το wizard στο SSMS (δεξί κλικ στην  βάση encrypt columns)

Με SSMS

Δεξί κλικ στην  βάση encrypt columns, ανοίγει τον wizard με τον οποίο μπορούμε να αυτοματοποιήσουμε όλη την διαδικασία χωρίς τα βήματα με T-SQL:

Περνώντας δύο εγγραφές ίδιες από την εφαρμογή μου:

Παρατηρούμε ότι παρότι βάλαμε δύο φορές την ίδια τιμή, το Α.Φ.Μ είναι διαφορετικό λόγο randomized  ενώ το surname που είναι deterministic είναι το  ίδιο.

Για να δούμε από το SSMS τις  πραγματικές τιμές  πρέπει  πριν κάνουμε connect να  ενεργοποιήσουμε το enable always encrypt:

Τρέχουμε ξανά το query:

Πως κάνουμε insert σε encrypted  πίνακα

DECLARE @surname nvarchar(50) = 'Nikolaou';
DECLARE @afm nvarchar(20) = '141280950';

INSERT INTO customer values('Georgios',@surname,'Male',@afm)

select * from customer

Πως κάνουμε update σε encrypted  πίνακα

DECLARE @customer_S nvarchar(50) = 'Nikolaou';
DECLARE @customer_N nvarchar(50) = 'Papandreou';

UPDATE [dbo].customer SET surname=@customer_N
WHERE surname = @customer_S

select * from customer

Πως κάνουμε select σε encrypted πίνακα (δεν έχουμε την δυνατότητα για like)

DECLARE @customer_S nvarchar(50) = 'Matzouranis';

SELECT * FROM [dbo].customer
WHERE surname = @customer_S

Πως γίνεται  να  συνδέσουμε την βάση δεδομένων που χρησιμοποιεί always encrypt με εφαρμογή σε Python

Φορτώνουμε τις βιβλιοθήκες:

import pyodbc,time,easygui

Συνδεόμαστε με το odbc που έχουμε φτιάξει με την παράμετρο Column Encryption Setting=Enabled :

conn = pyodbc.connect("DSN=sql_encrypt;"
                      "UID=user;"
                      "PWD=password;"
                      "Column Encryption Setting=Enabled;"
                      )

Φτιάχνουμε με τη βιβλιοθήκη easygui μια φόρμα  με τα  πεδία:

msg  = 'Insert Value'
title = 'Insert Value'

fieldNames = ['name','surname','sex','afm']
fieldValues = []
fieldValues = easygui.multenterbox(msg,title,fieldNames)

name,surname,sex,afm=fieldValues

Ανοίγουμε cursor στο connection και βάζουμε ? στις παραμέτρους του insert:

cursor = conn.cursor()

statement="""INSERT INTO customer values(?,?,?,?)"""

Καλούμε όλες τις παραμέτρους ως dynamic query:

cursor.execute(statement,name,surname,sex,afm)

conn.commit();

cursor.execute('select * from customer')

print(cursor.fetchall())

conn.commit();
cursor.close();
conn.close();

exit();

Κλείνοντας δεν θα πρέπει  να ξεχάσουμε ότι όταν δε θέλουμε να κάνουμε decrypt πεδία χρειαζόμαστε και πιστοποιητικά, το πως τα παίρνουμε backup υπάρχει ένας καλός οδηγός  εδώ.

Πηγές:

• Microsoft Always Encrypted