Πώς κάνουμε encrypt μια κολόνα ενός πίνακα με ευαίσθητα δεδομένα

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

Stratos Matzouranis

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

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

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

• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024

Σε αυτό το άρθρο θα περιγράψουμε πως γίνεται να εφαρμόσουμε encryption σε μία κολόνα πίνακα μέσω Column Level Encryption (CLE). Επίσης θα αναφέρουμε τι θα πρέπει να προσέξουμε για να έχουμε μία ομαλή μετάβαση.

Το CLE προτείνεται ως λύση όταν θέλουμε να αποκρύψουμε ευαίσθητα δεδομένα εντός της βάσης δεδομένων.

Τι διαφορά έχει με το Always Encrypted;

Το CLE προσφέρει κρυπτογράφηση μέσω πιστοποιητικού / κλειδιού η οποία όμως πραγματοποιείται μέσα στη βάση δεδομένων. Πράγμα που σημαίνει ότι ένας διαχειριστής που έχει δικαίωμα, μπορεί να δει τα κρυπτογραφημένα αυτά δεδομένα.

Αντιθέτως το Always Encrypted προσφέρει κρυπτογράφηση μέσα από την εφαρμογή, έτσι ώστε η database engine να μην γνωρίζει το κλειδί της κρυπτογράφησης.

Για τον λόγο αυτό προτείνεται από θέμα ασφαλείας η λύση του Always Encrypted.

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

Φτιάχνουμε τη βάση δεδομένων που θα χρησιμοποιήσουμε.

USE master;
GO
 
CREATE DATABASE EmpData2;
GO

Κλείνουμε όποιο άλλο κλειδί μπορεί να υπάρχει ανοιχτό.

USE EmpData2;
GO
CLOSE ALL SYMMETRIC KEYS;

Φτιάχνουμε έναν πίνακα που θα χρησιμοποιήσουμε για το παράδειγμα.

create table EmpData2..gdpr(
id int identity(1,1) primary key,
onoma varchar(50),
tilefono varchar(50)
)

Καλούμε ένα βρόγχο ώστε να γεμίσουμε τον πίνακα δεδομένα.

DECLARE @i as int;
set @i = 0;
while @i<10
begin
    insert into EmpData2..gdpr (onoma,tilefono)
    values('Stratos','2109090'+cast(@i as varchar(50)))
    set @i=@i+1
end

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

select * from EmpData2..gdpr

Για αρχή θα πρέπει να φτιάξουμε ένα master key ορίζοντας κωδικό. Έπειτα θα πρέπει να το κάνουμε open.

CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Pass!'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pass!'
go

Αν κάναμε σωστά τη διαδικασία θα πρέπει να μας εμφανιστεί με το select αυτό.

SELECT * FROM [sys].[openkeys]

Θα πρέπει να πάρουμε backup ότι κλειδιά ή πιστοποιητικά φτιάχνουμε γιατί αν τα χάσουμε δεν υπάρχει γυρισμός…

BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key' ENCRYPTION BY PASSWORD = 'Pass!';  
BACKUP MASTER KEY  TO FILE = 'C:\DbMasterKey.key' ENCRYPTION BY PASSWORD = 'Pass!'

Φτιάχνουμε και το πιστοποιητικό. Το οποίο θα πρέπει να μας εμφανιστεί και στο select.

CREATE CERTIFICATE gdpr_cert
    WITH SUBJECT = 'Gdpr cert'
GO
select * from sys.certificates

Κάνουμε και αυτό backup.

BACKUP CERTIFICATE gdpr_cert TO FILE = 'C:\backup\gdpr_cert.cer' WITH PRIVATE KEY ( FILE = 'c:\cert_key' ,  ENCRYPTION BY PASSWORD = 'Pass2!' );  

Φτιάχνουμε ένα κλειδί που είναι κωδικοποιημένο με το πιστοποιητικό.

CREATE SYMMETRIC KEY cle_key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE gdpr_cert;
GO

Ελέγχουμε ότι εμφανίζεται και αυτό στο select.

SELECT * FROM [sys].[symmetric_keys]
GO

Κάνουμε open το κλειδί κάνοντας το decrypt με το πιστοποιητικό.

OPEN SYMMETRIC KEY cle_key
DECRYPTION BY CERTIFICATE gdpr_cert
GO
SELECT * FROM [sys].[openkeys]
GO

Θα προσθέσουμε τώρα μια varbinary κολόνα στον πίνακα που θέλουμε να κρυπτογραφήσουμε.

ALTER TABLE empdata2..GDPR ADD
	encrypt_tilefono varbinary(200) NULL
GO
ALTER TABLE empdata2..GDPR SET (LOCK_ESCALATION = TABLE)
GO

Με update θα γεμίσουμε τη κολόνα με τα κρυπτογραφημένα δεδομένα από τη κολόνα του τηλεφώνου.

UPDATE empdata2..GDPR
SET encrypt_tilefono = ENCRYPTBYKEY(KEY_GUID('cle_key'), tilefono)
GO

Με ένα select στον πίνακα θα δούμε κάτι τέτοιο.

select *  from empdata2..GDPR

Αν βεβαιωθούμε ότι είμαστε ΟΚ μπορούμε να σβήσουμε τη μη κρυπτογραφημένη κολόνα.

Καλό θα ήτανε να έχουμε πάρει backup τη βάση δεδομένων πριν σβήσουμε κάτι.

alter table empdata2..GDPR
drop column  tilefono
GO

Για να μπορεί ένας απλός χρήστης που δεν είναι διαχειριστής να δει τα δεδομένα αυτά θα πρέπει να δώσουμε δικαίωμα στο πιστοποιητικό και στο κλειδί.

GRANT CONTROL ON CERTIFICATE::[gdpr_cert] TO [xristi];
GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY::[cle_key] TO [xristi];
GO

Για να δούμε τα δεδομένα χωρίς κρυπτογράφηση.

SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono]))
 FROM empdata2..GDPR
GO

Για να προσθέσουμε μια καινούργια εγγραφή κρυπτογραφημένη.

insert into empdata2..GDPR (onoma,encrypt_tilefono)
values('Stratos', ENCRYPTBYKEY(KEY_GUID('cle_key'), '21090999'))
GO

SELECT onoma,CONVERT([varchar](max), DECRYPTBYKEY([encrypt_tilefono]))
 FROM empdata2..GDPR
GO

Πηγές:

• Encrypt a Column with Cell-Level-Encryption