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.

 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;

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.

GDPR extended properties

GDPR extended properties

 

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.

GDPR custom values

GDPR custom values

 

This is the first release of the SQL Data Discovery & Classification and I'm looking forward to updates.

 

 

Comments (0)

Skip to main content