Skip to content

Instantly share code, notes, and snippets.

@MarkPryceMaherMSFT

MarkPryceMaherMSFT/GDPR.sql

Created March 12, 2018 17:20
Show Gist options
  • Save MarkPryceMaherMSFT/a654ca3887866e3f9da3eb58dfdfc80e to your computer and use it in GitHub Desktop.
Save MarkPryceMaherMSFT/a654ca3887866e3f9da3eb58dfdfc80e to your computer and use it in GitHub Desktop.
Download ZIP
Code observerd from the Data Classify function in SSMS 17.5
Raw
GDPR.sql
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment