SQL Data Discovery & Classification in SQL Server Management Studio (SSMS) 17.5 [Behind the Scenes]
In my previous post on the new Classify Data function in SQL Server Management Studio (SSMS) 17.5 - I focused on using it, but in this post I thought I'd peak behind the curtain to see what it is going on.
There are two steps, the Classifier function; how we automaticially classify the sensitiviy and information (type) for fields, the second step, how we save/store the classification.
Step 1: Classifer Function.
When you click on Classify Data, whats actually going on. Just open SQL Profiler or create an Extended Events trace and you will see the following SQL code. You can execute this on a machine that doesn't have SSMS 17.5.
[css collapse="true" language="sql" firstline="1" gutter="true" padlinenumbers="false" smarttabs="true" tabsize="4" toolbar="true" title="Click to open the classifier function."]
DECLARE @Dictionary TABLE
(
pattern NVARCHAR(128),
info_type NVARCHAR(128),
sensitivity_label NVARCHAR(128),
can_be_numeric BIT
)
INSERT INTO @Dictionary (pattern, info_type, sensitivity_label, can_be_numeric)
VALUES
('%username%' ,'Credentials' , 'Confidential' ,1),
('%pwd%' ,'Credentials' , 'Confidential' ,1),
('%password%' ,'Credentials' , 'Confidential' ,1),
('%email%' ,'Contact Info' , 'Confidential - GDPR' ,0),
('%e-mail%' ,'Contact Info' , 'Confidential - GDPR' ,0),
('%last%name%' ,'Name' , 'Confidential - GDPR' ,0),
('%first%name%' ,'Name' , 'Confidential - GDPR' ,0),
('%surname%' ,'Name' , 'Confidential - GDPR' ,0),
('%mainden%name%' ,'Name' , 'Confidential - GDPR' ,0),
('%addr%' ,'Contact Info' , 'Confidential - GDPR' ,0),
('%phone%' ,'Contact Info' , 'Confidential - GDPR' ,1),
('%mobile%' ,'Contact Info' , 'Confidential - GDPR' ,1),
('%area%code%' ,'Contact Info' , 'Confidential - GDPR' ,1),
('%reset%code%' ,'Credentials' , 'Confidential' ,1),
('%birthday%' ,'Date Of Birth' , 'Confidential - GDPR' ,1),
('%date%of%birth%' ,'Date Of Birth' , 'Confidential - GDPR' ,1),
('dob' ,'Date Of Birth' , 'Confidential - GDPR' ,1),
('ssn' ,'SSN' , 'Confidential - GDPR' ,1),
('%ss_num%' ,'SSN' , 'Confidential - GDPR' ,1),
('%ssnum%' ,'SSN' , 'Confidential - GDPR' ,1),
('sin' ,'SSN' , 'Confidential - GDPR' ,1),
('%employeessn%' ,'SSN' , 'Confidential - GDPR' ,1),
('%passport%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Pasaporte%' ,'National ID' , 'Confidential - GDPR' ,1),
('%social%security%' ,'SSN' , 'Confidential - GDPR' ,1),
('%soc%sec%' ,'SSN' , 'Confidential - GDPR' ,1),
('%security%' ,'Other' , 'Confidential' ,1),
('ssid' ,'SSN' , 'Confidential - GDPR' ,1),
('%tax%id%' ,'National ID' , 'Confidential - GDPR' ,1),
('itin' ,'National ID' , 'Confidential - GDPR' ,1),
('%driver%' ,'National ID' , 'Confidential - GDPR' ,1),
('%pass%' ,'Credentials' , 'Confidential' ,1),
('%personal%' ,'Other' , 'Confidential' ,1),
('%identification%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Identificación%Fiscal%' ,'National ID' , 'Confidential - GDPR' ,1),
('%street%' ,'Contact Info' , 'Confidential - GDPR' ,0),
('%city%' ,'Contact Info' , 'Confidential - GDPR' ,0),
('%postal%' ,'Contact Info' , 'Confidential - GDPR' ,1),
('%zip%' ,'Contact Info' , 'Confidential - GDPR' ,1),
('%identification%' ,'National ID' , 'Confidential - GDPR' ,1),
('%id%number%' ,'National ID' , 'Confidential - GDPR' ,1),
('%national%id%' ,'National ID' , 'Confidential - GDPR' ,1),
('%credit%' ,'Credit Card' , 'Confidential' ,1),
('%card%' ,'Credit Card' , 'Confidential' ,1),
('%account%' ,'Credentials' , 'Confidential' ,1),
('%tax%' ,'Financial' , 'Confidential' ,1),
('%paypal%' ,'Financial' , 'Confidential' ,1),
('%payment%' ,'Financial' , 'Confidential' ,1),
('%banking%' ,'Banking' , 'Confidential' ,1),
('%routing%no%' ,'Banking' , 'Confidential' ,1),
('%savings%acc%' ,'Banking' , 'Confidential' ,1),
('%debit%acc%' ,'Banking' , 'Confidential' ,1),
('%insurance%' ,'Financial' , 'Confidential' ,1),
('%ccn%' ,'Credit Card' , 'Confidential' ,1),
('%debit%' ,'Credit Card' , 'Confidential' ,1),
('%visa%' ,'Credit Card' , 'Confidential' ,1),
('%mastercard%' ,'Credit Card' , 'Confidential' ,1),
('%pmt%' ,'Financial' , 'Confidential' ,1),
('%cvv%' ,'Credit Card' , 'Confidential' ,1),
('%amount%' ,'Financial' , 'Confidential' ,1),
('%amt%' ,'Financial' , 'Confidential' ,1),
('%compensation%' ,'Financial' , 'Confidential' ,1),
('%currency%' ,'Financial' , 'Confidential' ,1),
('iban' ,'Banking' , 'Confidential' ,1),
('%iban%code%' ,'Banking' , 'Confidential' ,0),
('%iban%num%' ,'Banking' , 'Confidential' ,0),
('%routing%number%' ,'Banking' , 'Confidential' ,1),
('%patient%' ,'Health' , 'Confidential - GDPR' ,1),
('aba' ,'Banking' , 'Confidential' ,1),
('%aba%routing%' ,'Banking' , 'Confidential' ,1),
('%bank%routing%' ,'Banking' , 'Confidential' ,1),
('%swift%code%' ,'Banking' , 'Confidential' ,1),
('%swift%routing%' ,'Banking' , 'Confidential' ,1),
('%swift%num%' ,'Banking' , 'Confidential' ,1),
('%bic%code%' ,'Banking' , 'Confidential' ,1),
('%bic%num%' ,'Banking' , 'Confidential' ,1),
('%expy%' ,'Credit Card' , 'Confidential' ,1),
('%expm%' ,'Credit Card' , 'Confidential' ,1),
('%invoice%' ,'Financial' , 'Confidential' ,1),
('%clinic%' ,'Health' , 'Confidential - GDPR' ,1),
('%medical%' ,'Health' , 'Confidential - GDPR' ,1),
('%treatment%' ,'Health' , 'Confidential - GDPR' ,1),
('%healthcondition%' ,'Health' , 'Confidential - GDPR' ,1),
('%atmkaart%' ,'Credit Card' , 'Confidential' ,1),
('%medication%' ,'Health' , 'Confidential - GDPR' ,1),
('%health%' ,'Health' , 'Confidential - GDPR' ,1),
('%prescription%' ,'Health' , 'Confidential - GDPR' ,1),
('ip' ,'Networking' , 'Confidential' ,0),
('%[^h]ip%address%' ,'Networking' , 'Confidential' ,0),
('ip%address%' ,'Networking' , 'Confidential' ,0),
('%mac%address%' ,'Networking' , 'Confidential' ,0),
('%acct%nbr%' ,'Banking' , 'Confidential' ,1),
('%acct%num%' ,'Banking' , 'Confidential' ,1),
('%acct%no%' ,'Banking' , 'Confidential' ,1),
('%american%express%' ,'Credit Card' , 'Confidential' ,1),
('%americanexpress%' ,'Credit Card' , 'Confidential' ,1),
('%americano%espresso%' ,'Credit Card' , 'Confidential' ,1),
('%amex%' ,'Credit Card' , 'Confidential' ,1),
('%atm%card%' ,'Credit Card' , 'Confidential' ,1),
('%atm%cards%' ,'Credit Card' , 'Confidential' ,1),
('%atm%kaart%' ,'Credit Card' , 'Confidential' ,1),
('%atmcard%' ,'Credit Card' , 'Confidential' ,1),
('%atmcards%' ,'Credit Card' , 'Confidential' ,1),
('%carte%bancaire%' ,'Credit Card' , 'Confidential' ,1),
('%atmkaarten%' ,'Credit Card' , 'Confidential' ,1),
('%bancontact%' ,'Credit Card' , 'Confidential' ,1),
('%bank%card%' ,'Credit Card' , 'Confidential' ,1),
('%bankkaart%' ,'Credit Card' , 'Confidential' ,1),
('%card%holder%' ,'Credit Card' , 'Confidential' ,1),
('%card%num%' ,'Credit Card' , 'Confidential' ,1),
('%card%type%' ,'Credit Card' , 'Confidential' ,1),
('%cardano%numerico%' ,'Credit Card' , 'Confidential' ,1),
('%carta%bianca%' ,'Credit Card' , 'Confidential' ,1),
('%carta%credito%' ,'Credit Card' , 'Confidential' ,1),
('%carta%di%credito%' ,'Credit Card' , 'Confidential' ,1),
('%cartao%de%credito%' ,'Credit Card' , 'Confidential' ,1),
('%cartao%de%crédito%' ,'Credit Card' , 'Confidential' ,1),
('%cartao%de%debito%' ,'Credit Card' , 'Confidential' ,1),
('%cartao%de%débito%' ,'Credit Card' , 'Confidential' ,1),
('%cirrus%' ,'Credit Card' , 'Confidential' ,1),
('%carte%blanche%' ,'Credit Card' , 'Confidential' ,1),
('%carte%bleue%' ,'Credit Card' , 'Confidential' ,1),
('%carte%de%credit%' ,'Credit Card' , 'Confidential' ,1),
('%carte%de%crédit%' ,'Credit Card' , 'Confidential' ,1),
('%carte%di%credito%' ,'Credit Card' , 'Confidential' ,1),
('%carteblanche%' ,'Credit Card' , 'Confidential' ,1),
('%cartão%de%credito%' ,'Credit Card' , 'Confidential' ,1),
('%cartão%de%crédito%' ,'Credit Card' , 'Confidential' ,1),
('%cartão%de%debito%' ,'Credit Card' , 'Confidential' ,1),
('%cartão%de%débito%' ,'Credit Card' , 'Confidential' ,1),
('%check%card%' ,'Credit Card' , 'Confidential' ,1),
('%chequekaart%' ,'Credit Card' , 'Confidential' ,1),
('%hoofdkaart%' ,'Credit Card' , 'Confidential' ,1),
('%cirrus-edc-maestro%' ,'Credit Card' , 'Confidential' ,1),
('%controlekaart%' ,'Credit Card' , 'Confidential' ,1),
('%credit%card%' ,'Credit Card' , 'Confidential' ,1),
('%debet%kaart%' ,'Credit Card' , 'Confidential' ,1),
('%debit%card%' ,'Credit Card' , 'Confidential' ,1),
('%debito%automatico%' ,'Credit Card' , 'Confidential' ,1),
('%diners%club%' ,'Credit Card' , 'Confidential' ,1),
('%discover%' ,'Credit Card' , 'Confidential' ,1),
('%discover%card%' ,'Credit Card' , 'Confidential' ,1),
('%débito%automático%' ,'Credit Card' , 'Confidential' ,1),
('%eigentümername%' ,'Credit Card' , 'Confidential' ,1),
('%european%debit%card%' ,'Credit Card' , 'Confidential' ,1),
('%master%card%' ,'Credit Card' , 'Confidential' ,1),
('%hoofdkaarten%' ,'Credit Card' , 'Confidential' ,1),
('%in%viaggio%' ,'Credit Card' , 'Confidential' ,1),
('%japanese%card%bureau%' ,'Credit Card' , 'Confidential' ,1),
('%japanse%kaartdienst%' ,'Credit Card' , 'Confidential' ,1),
('%jcb%' ,'Credit Card' , 'Confidential' ,1),
('%kaart%' ,'Credit Card' , 'Confidential' ,1),
('%kaart%num%' ,'Credit Card' , 'Confidential' ,1),
('%kaartaantal%' ,'Credit Card' , 'Confidential' ,1),
('%kaarthouder%' ,'Credit Card' , 'Confidential' ,1),
('%karte%' ,'Credit Card' , 'Confidential' ,1),
('%karteninhaber%' ,'Credit Card' , 'Confidential' ,1),
('%kartennr%' ,'Credit Card' , 'Confidential' ,1),
('%kartennummer%' ,'Credit Card' , 'Confidential' ,1),
('%kreditkarte%' ,'Credit Card' , 'Confidential' ,1),
('%maestro%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%carte%' ,'Credit Card' , 'Confidential' ,1),
('mc' ,'Credit Card' , 'Confidential' ,1),
('%mister%cash%' ,'Credit Card' , 'Confidential' ,1),
('%n%carta%' ,'Credit Card' , 'Confidential' ,1),
('%n.%carta%' ,'Credit Card' , 'Confidential' ,1),
('%no%de%tarjeta%' ,'Credit Card' , 'Confidential' ,1),
('%no%do%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%no%do%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%no.%de%tarjeta%' ,'Credit Card' , 'Confidential' ,1),
('%no.%do%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%no.%do%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%nr%carta%' ,'Credit Card' , 'Confidential' ,1),
('%nr.%carta%' ,'Credit Card' , 'Confidential' ,1),
('%numeri%di%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%numero%carta%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%número%de%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%tarjeta%' ,'Credit Card' , 'Confidential' ,1),
('%numero%della%carta%' ,'Credit Card' , 'Confidential' ,1),
('%numero%di%carta%' ,'Credit Card' , 'Confidential' ,1),
('%numero%di%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%numero%do%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%numero%do%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%numéro%de%carte%' ,'Credit Card' , 'Confidential' ,1),
('%nº%carta%' ,'Credit Card' , 'Confidential' ,1),
('%nº%de%carte%' ,'Credit Card' , 'Confidential' ,1),
('%nº%de%la%carte%' ,'Credit Card' , 'Confidential' ,1),
('%nº%de%tarjeta%' ,'Credit Card' , 'Confidential' ,1),
('%nº%do%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%nº%do%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%nº.%do%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%scoprono%le%schede%' ,'Credit Card' , 'Confidential' ,1),
('%número%de%cartão%' ,'Credit Card' , 'Confidential' ,1),
('%número%de%tarjeta%' ,'Credit Card' , 'Confidential' ,1),
('%número%do%cartao%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%dell''assegno%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%dell''atmosfera%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%dell''atmosfera%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%della%banca%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%di%controllo%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%di%debito%' ,'Credit Card' , 'Confidential' ,1),
('%scheda%matrice%' ,'Credit Card' , 'Confidential' ,1),
('%schede%dell''atmosfera%' ,'Credit Card' , 'Confidential' ,1),
('%schede%di%controllo%' ,'Credit Card' , 'Confidential' ,1),
('%schede%di%debito%' ,'Credit Card' , 'Confidential' ,1),
('%schede%matrici%' ,'Credit Card' , 'Confidential' ,1),
('%scoprono%la%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%visa%plus%' ,'Credit Card' , 'Confidential' ,1),
('%solo%' ,'Credit Card' , 'Confidential' ,1),
('%supporti%di%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%supporto%di%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%switch%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%atm%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%credito%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%de%atm%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%de%credito%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%de%debito%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%debito%' ,'Credit Card' , 'Confidential' ,1),
('%tarjeta%no%' ,'Credit Card' , 'Confidential' ,1),
('%tarjetahabiente%' ,'Credit Card' , 'Confidential' ,1),
('%tipo%della%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%ufficio%giapponese%della%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%v%pay%' ,'Credit Card' , 'Confidential' ,1),
('%codice%di%verifica%' ,'Credit Card' , 'Confidential' ,1),
('%visa%electron%' ,'Credit Card' , 'Confidential' ,1),
('%visto%' ,'Credit Card' , 'Confidential' ,1),
('%card%identification%number%' ,'Credit Card' , 'Confidential' ,1),
('%card%verification%' ,'Credit Card' , 'Confidential' ,1),
('%cardi%la%verifica%' ,'Credit Card' , 'Confidential' ,1),
('cid' ,'Credit Card' , 'Confidential' ,1),
('%cod%seg%' ,'Credit Card' , 'Confidential' ,1),
('%cod%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%cod%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%cod%sicurezza%' ,'Credit Card' , 'Confidential' ,1),
('%cod.%seg%' ,'Credit Card' , 'Confidential' ,1),
('%cod.%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%cod.%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%cod.%sicurezza%' ,'Credit Card' , 'Confidential' ,1),
('%codice%di%sicurezza%' ,'Credit Card' , 'Confidential' ,1),
('%código%de%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%codigo%' ,'Credit Card' , 'Confidential' ,1),
('%codigo%de%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%codigo%de%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%crittogramma%' ,'Credit Card' , 'Confidential' ,1),
('%cryptogram%' ,'Credit Card' , 'Confidential' ,1),
('%cryptogramme%' ,'Credit Card' , 'Confidential' ,1),
('%cv2%' ,'Credit Card' , 'Confidential' ,1),
('%cvc%' ,'Credit Card' , 'Confidential' ,1),
('%cvc2%' ,'Credit Card' , 'Confidential' ,1),
('%cvn%' ,'Credit Card' , 'Confidential' ,1),
('%cód%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%cód%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%cód.%seguranca%' ,'Credit Card' , 'Confidential' ,1),
('%cód.%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%código%' ,'Credit Card' , 'Confidential' ,1),
('%numero%di%sicurezza%' ,'Credit Card' , 'Confidential' ,1),
('%código%de%segurança%' ,'Credit Card' , 'Confidential' ,1),
('%de%kaart%controle%' ,'Credit Card' , 'Confidential' ,1),
('%geeft%nr%uit%' ,'Credit Card' , 'Confidential' ,1),
('%issue%no%' ,'Credit Card' , 'Confidential' ,1),
('%issue%number%' ,'Credit Card' , 'Confidential' ,1),
('%kaartidentificatienummer%' ,'Credit Card' , 'Confidential' ,1),
('%kreditkartenprufnummer%' ,'Credit Card' , 'Confidential' ,1),
('%kreditkartenprüfnummer%' ,'Credit Card' , 'Confidential' ,1),
('%kwestieaantal%' ,'Credit Card' , 'Confidential' ,1),
('%no.%dell''edizione%' ,'Credit Card' , 'Confidential' ,1),
('%no.%di%sicurezza%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%securite%' ,'Credit Card' , 'Confidential' ,1),
('%numero%de%verificacao%' ,'Credit Card' , 'Confidential' ,1),
('%numero%dell''edizione%' ,'Credit Card' , 'Confidential' ,1),
('%numero%di%identificazione%della%scheda%' ,'Credit Card' , 'Confidential' ,1),
('%veiligheid%nr%' ,'Credit Card' , 'Confidential' ,1),
('%numero%van%veiligheid%' ,'Credit Card' , 'Confidential' ,1),
('%numéro%de%sécurité%' ,'Credit Card' , 'Confidential' ,1),
('%nº%autorizzazione%' ,'Credit Card' , 'Confidential' ,1),
('%número%de%verificação%' ,'Credit Card' , 'Confidential' ,1),
('%perno%il%blocco%' ,'Credit Card' , 'Confidential' ,1),
('%pin%block%' ,'Credit Card' , 'Confidential' ,1),
('%prufziffer%' ,'Credit Card' , 'Confidential' ,1),
('%prüfziffer%' ,'Credit Card' , 'Confidential' ,1),
('%security%code%' ,'Credit Card' , 'Confidential' ,1),
('%security%no%' ,'Credit Card' , 'Confidential' ,1),
('%security%number%' ,'Credit Card' , 'Confidential' ,1),
('%sicherheits%kode%' ,'Credit Card' , 'Confidential' ,1),
('%sicherheitscode%' ,'Credit Card' , 'Confidential' ,1),
('%sicherheitsnummer%' ,'Credit Card' , 'Confidential' ,1),
('%speldblok%' ,'Credit Card' , 'Confidential' ,1),
('%datum%van%exp%' ,'Credit Card' , 'Confidential' ,1),
('%veiligheidsaantal%' ,'Credit Card' , 'Confidential' ,1),
('%veiligheidscode%' ,'Credit Card' , 'Confidential' ,1),
('%veiligheidsnummer%' ,'Credit Card' , 'Confidential' ,1),
('%verfalldatum%' ,'Credit Card' , 'Confidential' ,1),
('%ablauf%' ,'Credit Card' , 'Confidential' ,1),
('%data%de%expiracao%' ,'Credit Card' , 'Confidential' ,1),
('%data%de%expiração%' ,'Credit Card' , 'Confidential' ,1),
('%data%del%exp%' ,'Credit Card' , 'Confidential' ,1),
('%data%di%exp%' ,'Credit Card' , 'Confidential' ,1),
('%data%di%scadenza%' ,'Credit Card' , 'Confidential' ,1),
('%data%em%que%expira%' ,'Credit Card' , 'Confidential' ,1),
('%data%scad%' ,'Credit Card' , 'Confidential' ,1),
('%data%scadenza%' ,'Credit Card' , 'Confidential' ,1),
('%date%de%validité%' ,'Credit Card' , 'Confidential' ,1),
('%datum%afloop%' ,'Credit Card' , 'Confidential' ,1),
('%de%afloop%' ,'Credit Card' , 'Confidential' ,1),
('%datum%van%exp%' ,'Credit Card' , 'Confidential' ,1),
('%espira%' ,'Credit Card' , 'Confidential' ,1),
('%espira%' ,'Credit Card' , 'Confidential' ,1),
('%exp%date%' ,'Credit Card' , 'Confidential' ,1),
('%exp%datum%' ,'Credit Card' , 'Confidential' ,1),
('%expiration%' ,'Credit Card' , 'Confidential' ,1),
('%expire%' ,'Credit Card' , 'Confidential' ,1),
('%expires%' ,'Credit Card' , 'Confidential' ,1),
('%expiry%' ,'Credit Card' , 'Confidential' ,1),
('%fecha%de%expiracion%' ,'Credit Card' , 'Confidential' ,1),
('%fecha%de%venc%' ,'Credit Card' , 'Confidential' ,1),
('%gultig%bis%' ,'Credit Card' , 'Confidential' ,1),
('%gultigkeitsdatum%' ,'Credit Card' , 'Confidential' ,1),
('%gültig%bis%' ,'Credit Card' , 'Confidential' ,1),
('%gültigkeitsdatum%' ,'Credit Card' , 'Confidential' ,1),
('%Fuehrerschein%' ,'National ID' , 'Confidential - GDPR' ,1),
('%scadenza%' ,'Credit Card' , 'Confidential' ,1),
('%valable%' ,'Credit Card' , 'Confidential' ,1),
('%validade%' ,'Credit Card' , 'Confidential' ,1),
('%valido%hasta%' ,'Credit Card' , 'Confidential' ,1),
('%valor%' ,'Credit Card' , 'Confidential' ,1),
('%venc%' ,'Credit Card' , 'Confidential' ,1),
('%vencimento%' ,'Credit Card' , 'Confidential' ,1),
('%vencimiento%' ,'Credit Card' , 'Confidential' ,1),
('%verloopt%' ,'Credit Card' , 'Confidential' ,1),
('%vervaldag%' ,'Credit Card' , 'Confidential' ,1),
('%vervaldatum%' ,'Credit Card' , 'Confidential' ,1),
('%vto%' ,'Credit Card' , 'Confidential' ,1),
('%válido%hasta%' ,'Credit Card' , 'Confidential' ,1),
('%Führerschein%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Fuhrerschein%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Fuehrerschein%' ,'National ID' , 'Confidential - GDPR' ,1),
('%insee%' ,'SSN' , 'Confidential - GDPR' ,1),
('%securité%sociale%' ,'SSN' , 'Confidential - GDPR' ,1),
('%securite%sociale%' ,'SSN' , 'Confidential - GDPR' ,1),
('%numéro%identité%' ,'National ID' , 'Confidential - GDPR' ,1),
('%no%identité%' ,'National ID' , 'Confidential - GDPR' ,1),
('%no.%identité%' ,'National ID' , 'Confidential - GDPR' ,1),
('%numero%identite%' ,'National ID' , 'Confidential - GDPR' ,1),
('%no%identite%' ,'National ID' , 'Confidential - GDPR' ,1),
('%no.%identite%' ,'National ID' , 'Confidential - GDPR' ,1),
('%le%numéro%d''identification%nationale%' ,'National ID' , 'Confidential - GDPR' ,1),
('%identité%nationale%' ,'National ID' , 'Confidential - GDPR' ,1),
('%numéro%de%sécurité%sociale%' ,'SSN' , 'Confidential - GDPR' ,1),
('%le%code%de%la%sécurité%sociale%' ,'SSN' , 'Confidential - GDPR' ,1),
('%numéro%d''assurance%sociale%' ,'SSN' , 'Confidential - GDPR' ,1),
('%numéro%de%sécu%' ,'SSN' , 'Confidential - GDPR' ,1),
('%code%sécu%' ,'SSN' , 'Confidential - GDPR' ,1),
('%reisepass%' ,'National ID' , 'Confidential - GDPR' ,1),
('%passeport%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Personalausweis%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Identifizierungsnummer%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Ausweis%' ,'National ID' , 'Confidential - GDPR' ,1),
('%Identifikation%' ,'National ID' , 'Confidential - GDPR' ,1),
('%patente%di%guida%' ,'National ID' , 'Confidential - GDPR' ,1)
DECLARE @InfoTypeRanking TABLE
(
info_type NVARCHAR(128),
ranking INT
)
INSERT INTO @InfoTypeRanking (info_type, ranking)
VALUES
('Banking', 800),
('Contact Info', 200),
('Credentials', 300),
('Credit Card', 700),
('Date Of Birth', 1100),
('Financial', 900),
('Health', 1000),
('Name', 400),
('National ID', 500),
('Networking', 100),
('SSN', 600),
('Other', 1200)
DECLARE @ClassifcationResults TABLE
(
schema_name NVARCHAR(128),
table_name NVARCHAR(128),
column_name NVARCHAR(128),
info_type NVARCHAR(128),
sensitivity_label NVARCHAR(128),
ranking INT,
can_be_numeric BIT
)
INSERT INTO @ClassifcationResults
SELECT DISTINCT S.NAME AS schema_name,
T.NAME AS table_name,
C.NAME AS column_name,
D.info_type,
D.sensitivity_label,
R.ranking,
D.can_be_numeric
FROM sys.schemas S
INNER JOIN sys.tables T
ON S.schema_id = T.schema_id
INNER JOIN sys.columns C
ON T.object_id = C.object_id
INNER JOIN sys.types TP
ON C.system_type_id = TP.system_type_id
LEFT OUTER JOIN @Dictionary D
ON (D.pattern NOT LIKE '%[%]%' AND LOWER(C.name) = LOWER(D.pattern) COLLATE DATABASE_DEFAULT) OR
(D.pattern LIKE '%[%]%' AND LOWER(C.name) LIKE LOWER(D.pattern) COLLATE DATABASE_DEFAULT)
LEFT OUTER JOIN @infoTypeRanking R
ON (R.info_type = D.info_type)
WHERE (D.info_type IS NOT NULL ) AND
NOT (D.can_be_numeric = 0 AND TP.name IN ('bigint','bit','decimal','float','int','money','numeric','smallint','smallmoney','tinyint'))
SELECT DISTINCT
CR.schema_name AS schema_name,
CR.table_name AS table_name,
CR.column_name AS column_name,
CR.info_type AS information_type_name,
CR.sensitivity_label AS sensitivity_label_name
FROM @ClassifcationResults CR
INNER JOIN
(
SELECT
schema_name,
table_name,
column_name,
MIN(ranking) AS min_ranking
FROM
@ClassifcationResults
GROUP BY
schema_name,
table_name,
column_name
) MR
ON CR.schema_name = MR.schema_name
AND CR.table_name = MR.table_name
AND CR.column_name = MR.column_name
AND CR.Ranking = MR.min_ranking
ORDER BY schema_name, table_name, column_name
--select distinct sensitivity_label from @Dictionary;
--select distinct info_type from @Dictionary;
[/css]
This is a link to the source on github.
Step 2: Store Classications.
The results of the classification are saved to extended properites of the the column.
[caption id="attachment_5325" align="alignnone" width="1280"]
GDPR extended properties[/caption]
The actions above create a script like this:
Although right now, we can not customise the drop downs at the moment for Information Type and Sensitivty labels, but we can tweek them ( but this is very very unsupported!)
The script below, assigns a new Sensitivity Label, called 'Custom Confidential'.
Below is the new updated Sensitivity Label in the tool.
[caption id="attachment_5335" align="alignnone" width="798"]
GDPR custom values[/caption]
This is the first release of the SQL Data Discovery & Classification and I'm looking forward to updates.