The question is simple: is there any difference between the performance of the different service…
Pre-requisites
In the new SQL Server Management Studio version 17.5, released 15th Feb 2018
There is a new feature called Data Discovery and Classification feature described here:-
Research
So what can we glean from a quick Bingle?
Brent Ozar ran it on a case sensitive server and it fails with an invalid object name ‘information_schema.columns’ error, I’ve certainly seen (caused) this error and the underlying reason is that the correct case for INFORMATION_SCHEMA is uppercase.
https://www.brentozar.com/archive/2018/02/new-sql-server-management-studio-17-5-classified/
Thomas La Rock got further with his experiments and provides a nice summary of the main features of the product and explores what happens when you have column names in another language.
https://thomaslarock.com/2018/02/sql-data-discovery-and-classification/
Introduction
OK, let’s start by doing a very quick summary of the functionality and then dig a bit deeper to investigate how the proposed classifications are produced and the strengths and weaknesses.
So to bring up the suggested classifications for a database we simply right click on the database name and then pick Tasks>Classify Data…
We then get a suggestion of the columns that might contain sensitive data.
We can use the drop downs on each row to adjust the information types and sensitivity labels for each table and column in turn and fill in the check boxes in the left-hand margin and then when we are ready we can “Accept selected recommendations” and click the save button to persist the data into the database.
One potential gotcha here is if you have in memory tables you will get an error and it will look like the save has failed entirely but actually details are saved for all other tables and columns.
Limitations
Actually the adventureworks database is a good one for testing this feature – for example there is a table HumanResources.JobCandidate which contains an XML column called resume – the initial suggestions do not refer to this column at all but if you browse it in the grid view and then click the xml link you will quickly discover that no attempt has been made to parse the XML but that it is full to the brim with sensitive information, names, addresses, telephone number and former employments.
Manual Remediation
OK let’s try and manually remedy that.
First click the Add Classification button
Secondly when the dialog appears on the right-hand side (purple box added for highlighting purposes) choose the schema, table and column using the drop downs and specify the information type and sensitivity label.
Thirdly the save button is now enabled – so click here to persist your changes.
If we view the report – we can see that this entry is now added
As I mentioned earlier other people have also provided an overview of the functionality and reports, so I won’t elaborate further on the interface.
Exploring the Internals
Instead let’s take a look under the covers – I simply relied on the tried and tested investigative step of running a SQL Server trace (using the replay template) and we can quickly gain a LOT more information of how this feature is built.
The first thing that happens is that SQL runs a query to retrieve any existing data classification metadata.
SELECT s.name AS schema_name, t.name AS table_name, c.name AS column_name, EP1.value AS information_type_name, EP2.value AS information_type_id, EP3.value AS sensitivity_label_name, EP4.value AS sensitivity_label_id FROM sys.columns c LEFT JOIN sys.tables t ON t.object_id = c.object_id LEFT JOIN sys.schemas s ON s.schema_id = t.schema_id LEFT JOIN sys.extended_properties EP1 ON c.object_id = EP1.major_id and c.column_id = EP1.minor_id and EP1.name = 'sys_information_type_name' LEFT JOIN sys.extended_properties EP2 ON c.object_id = EP2.major_id and c.column_id = EP2.minor_id and EP2.name = 'sys_information_type_id' LEFT JOIN sys.extended_properties EP3 ON c.object_id = EP3.major_id and c.column_id = EP3.minor_id and EP3.name = 'sys_sensitivity_label_name' LEFT JOIN sys.extended_properties EP4 ON c.object_id = EP4.major_id and c.column_id = EP4.minor_id and EP4.name = 'sys_sensitivity_label_id' WHERE ( EP1.value IS NOT NULL OR EP2.value IS NOT NULL OR EP3.value IS NOT NULL OR EP4.value IS NOT NULL )
Extended Properties
Here we can quite clearly see that the metadata is stored using extended properties.
If we reverse engineer this information and we know the rudiments of extended properties we can quickly work out how to add our own entries programmatically via the back end, in this case for the column rate in HumanResources.EmployeePayHistory which we might choose to classify as Financial and Confidential. So using the script below and executing it
--'sys_information_type_name' --'sys_information_type_id' --'sys_sensitivity_label_name' --'sys_sensitivity_label_id' --Financial --C44193E1-0E58-4B2A-9001-F7D6E7BC1373 --Confidential --331F0B13-76B5-2F1B-A77B-DEF5A73C73C2 --HumanResources --EmployeePayHistory --Rate EXEC sp_addextendedproperty @name = N'sys_information_type_name', @value = 'Financial', @level0type = N'Schema', @level0name = 'HumanResources', @level1type = N'Table', @level1name = 'EmployeePayHistory', @level2type = N'Column', @level2name = 'Rate'; EXEC sp_addextendedproperty @name = N'sys_information_type_id', @value = 'C44193E1-0E58-4B2A-9001-F7D6E7BC1373', @level0type = N'Schema', @level0name = 'HumanResources', @level1type = N'Table', @level1name = 'EmployeePayHistory', @level2type = N'Column', @level2name = 'Rate'; EXEC sp_addextendedproperty @name = N'sys_sensitivity_label_name', @value = 'Confidential', @level0type = N'Schema', @level0name = 'HumanResources', @level1type = N'Table', @level1name = 'EmployeePayHistory', @level2type = N'Column', @level2name = 'Rate'; EXEC sp_addextendedproperty @name = N'sys_sensitivity_label_id', @value = '331F0B13-76B5-2F1B-A77B-DEF5A73C73C2', @level0type = N'Schema', @level0name = 'HumanResources', @level1type = N'Table', @level1name = 'EmployeePayHistory', @level2type = N'Column', @level2name = 'Rate';
When we run the front-end report it correctly picks up the information we just added via the calls to sp_addextendedproperty see the extra row in our report, highlighted below.
Once SQL has checked for metadata it goes about collecting the column name information and here it appears to use a loop over each table in turn and for each table uses sp_executesql to get back a list of column names.
exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA=@schema AND TABLE_NAME=@table)',N'@schema nvarchar(14),@table nvarchar(8)',@schema=N'HumanResources',@table=N'Employee'
The final step is to compare the column names with the predefined dictionary table using the SQL in appendix 1. Here we can see entries using wildcards (%) for several languages – English, German, Spanish, Portuguese, French. But of concern to me who lives and works in Stockholm no Swedish.
Summary
So we can see that this feature is very much an early beta and has several shortcomings
- Cannot work on case sensitive databases
- Cannot parse XML columns
- Cannot add extended properties to in memory tables (tries and fails)
- Works from a fixed and limited dictionary
- Cannot parse content to recognise telephone or credit card numbers for example
None the less with a bit of knowledge of the internals and some simple scripting we can quickly extend this functionality and it does provide a common standard way to tag databases for GDPR / data protection purposes and this is a good start.
For the next post we will explore using back-end scripts to parse Swedish language columns and extend support to new languages.
[/code]
Appendix 1
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'))
-- QUERY to apply the dictionary matches to the columns
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