/*============================================================================= Propose data classification to all columns in database This script is to help classify data in the database. The logic is copied from the Sql Server Management Studio logic and extended. This script is compatible with the SSMS gui and reports. !!! A couple variables are present around line 230. They cannot be put higher in the script, please review them before launching the script. !!! The logic is: * We collect a list of pattern to match against fields in the database. Those patterns can add or retract matches. * If the flag @searchInFields is true, a check against the content of every fields containing text is also run Only fields with a max lenght > 90 characters are checked. * Only tables are checked, views are ignored * Tables with no rows are ignored and removed from the matches * every fields with a name finishing with "id" are removed from the results * every primary key and foreign keys column are excluded from the results * only columns with alpha numeric or numeric content are included * computed columns are skipped * a list of unused tables in the arizona database have been identified in the US OCTP-724 Those tables are excluded from the results as well. It will set classification as: category: "Galenica Used Personal Informations" with the sensitivity: "Confidential" on every matched rows category: "Other" with sensitivity: "General" on every other columns Creation : 29.03.2023 / TSC Modifications: =============================================================================*/ SET XACT_ABORT ON; SET NOCOUNT ON; USE [ActivePos_read] GO IF OBJECT_ID('dbo.setSensitivity') IS NOT NULL BEGIN DROP PROCEDURE dbo.setSensitivity; END GO CREATE PROCEDURE dbo.setSensitivity @schemaName VARCHAR(100) ,@tableName VARCHAR(100) ,@columnName VARCHAR(100) ,@category VARCHAR(100) ,@sensitivity VARCHAR(100) ,@rank VARCHAR(10) = 'LOW' ,@removeClassification BIT = 0 AS BEGIN DECLARE @useSql2019Syntax BIT = 0; DECLARE @command NVARCHAR(MAX)=''; SELECT @useSql2019Syntax = CASE WHEN CAST(SERVERPROPERTY('productversion') AS VARCHAR(2)) >= 15 THEN 1 ELSE 0 END SET @rank = UPPER(@rank); --PRINT ' --@schemaName : '+ISNULL(CONVERT(VARCHAR(200),@schemaName),'NULL')+' --@tableName : '+ISNULL(CONVERT(VARCHAR(200),@tableName),'NULL')+' --@columnName : '+ISNULL(CONVERT(VARCHAR(200),@columnName),'NULL')+' --@category : '+ISNULL(CONVERT(VARCHAR(200),@category),'NULL')+' --@sensitivity : '+ISNULL(CONVERT(VARCHAR(200),@sensitivity),'NULL')+' --@rank : '+ISNULL(CONVERT(VARCHAR(200),@rank),'NULL')+' --@removeClassification : '+ISNULL(CONVERT(VARCHAR(200),@removeClassification),'NULL')+' --' IF @useSql2019Syntax = 0 BEGIN --PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - using pre-sql 2019 syntax'; IF EXISTS ( SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(@schemaName+'.'+@tableName) AND [name] = N'sys_information_type_name' AND [minor_id] = ( SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@schemaName+'.'+@tableName) ) ) EXECUTE [sys].[sp_dropextendedproperty] @name = N'sys_information_type_name', @level0type = N'SCHEMA', @level0name = @schemaName, @level1type = N'TABLE', @level1name = @tableName, @level2type = N'COLUMN', @level2name = @columnName; ; IF EXISTS ( SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID(@schemaName+'.'+@tableName) AND [name] = N'sys_sensitivity_label_name' AND [minor_id] = ( SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = @columnName AND [object_id] = OBJECT_ID(@schemaName+'.'+@tableName) ) ) EXECUTE [sys].[sp_dropextendedproperty] @name = N'sys_sensitivity_label_name', @level0type = N'SCHEMA', @level0name = @schemaName, @level1type = N'TABLE', @level1name = @tableName, @level2type = N'COLUMN', @level2name = @columnName; ; IF @removeClassification = 0 BEGIN EXEC [sys].[sp_addextendedproperty] @name = N'sys_information_type_name', @value = @category, @level0type = N'Schema', @level0name = @schemaName, @level1type = N'Table', @level1name = @tableName, @level2type = N'Column',@level2name = @columnName; EXEC [sys].[sp_addextendedproperty] @name = N'sys_sensitivity_label_name', @value = @sensitivity, @level0type = N'Schema', @level0name = @schemaName, @level1type = N'Table', @level1name = @tableName, @level2type = N'Column',@level2name = @columnName; END END ELSE BEGIN PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - using sql 2019 syntax'; IF @removeClassification = 0 BEGIN SET @command ='ADD SENSITIVITY CLASSIFICATION TO ['+@schemaName+'].['+@tableName+'].['+@columnName+'] WITH ( LABEL='''+@sensitivity+''', INFORMATION_TYPE='''+@category+''', RANK='+@rank+' );' PRINT @command; EXEC(@command); END ELSE BEGIN SET @command = 'DROP SENSITIVITY CLASSIFICATION FROM ['+@schemaName+'].['+@tableName+'].['+@columnName+'];' PRINT @command; EXEC(@command); END END END GO --#region vars DECLARE @Tblpattern TABLE( patternId INT IDENTITY NOT NULL ,pattern VARCHAR(100) NOT NULL ,toExclude BIT NOT NULL DEFAULT 0 ); IF OBJECT_ID('tempdb..#classification')IS NOT NULL BEGIN; DROP TABLE #classification; END; CREATE TABLE #classification ( [table_schema] sysname NOT NULL ,[table_name] sysname NOT NULL ,[column_name] sysname NOT NULL ,[data_type] sysname NOT NULL ,[matchedByPattern] VARCHAR(100) NOT NULL ,[matchedInsideField] BIT NOT NULL DEFAULT 0 ); IF OBJECT_ID('tempdb..#all_text_fields')IS NOT NULL BEGIN; DROP TABLE #all_text_fields; END; CREATE TABLE #all_text_fields ( [table_schema] VARCHAR(255) NOT NULL ,[table_name] VARCHAR(255) NOT NULL ,[column_name] VARCHAR(255) NOT NULL ,[data_type] VARCHAR(255) NOT NULL ,[matchedByPattern] VARCHAR(100) NULL ); IF OBJECT_ID('tempdb..#existing_classification')IS NOT NULL BEGIN; DROP TABLE #existing_classification; END; CREATE TABLE #existing_classification( [table_schema] VARCHAR(255) NOT NULL ,[table_name] VARCHAR(255) NOT NULL ,[column_name] VARCHAR(255) NOT NULL ,[category] VARCHAR(255) ,[sensibility] VARCHAR(100) ,[rank] VARCHAR(20) ); DECLARE @pattern VARCHAR(100); DECLARE @toExclude BIT = 0; DECLARE @cmd VARCHAR(MAX)=''; DECLARE @schema_name VARCHAR(100); DECLARE @table_name VARCHAR(100); DECLARE @column_name VARCHAR(100); DECLARE @data_type VARCHAR(100); DECLARE @searchInFields BIT = 1; DECLARE @assignUnmatchedColumns BIT = 1; DECLARE @matchedType VARCHAR(255); DECLARE @unmatchedType VARCHAR(255); DECLARE @useSql2019Syntax BIT = CASE WHEN CAST(SERVERPROPERTY('productversion') AS VARCHAR(2)) >= 15 THEN 1 ELSE 0 END ; DECLARE @tplCheckInField VARCHAR(MAX)=' INSERT INTO #classification( [table_schema] ,[table_name] ,[column_name] ,[matchedByPattern] ,[data_type] ,[matchedInsideField] ) SELECT DISTINCT ''@schema@'' ,''@tbl@'' ,''@field@'' ,''@pattern@'' ,''@data_type@'' ,1 FROM [@schema@].[@tbl@] x WHERE x.[@field@] like ''@pattern@'' AND NOT EXISTS( SELECT 1 FROM #classification c WHERE c.[table_schema] = ''@schema@'' AND c.[table_name] = ''@tbl@'' AND c.[column_name] = ''@field@'' ); '; --#endregion /* If true, search inside text fields longer than 90 characters for occurence of the defined patterns Only non computed columns of tables are checked (views are ignored, computed columns are ignored) If false, the search inside the fields is skipped. */ SET @searchInFields = 01; /* if true, every columns that is not matched by a pattern will be marked as category: Other, sensitivity: General if false, no classification will be set on the column. Existing classification will be removed. */ SET @assignUnmatchedColumns = 01; /* The type applied to recognized fields */ SET @matchedType = 'Galenica Used Personal Informations'; SET @unmatchedType = 'Other'; --#region populate patterns INSERT INTO @Tblpattern ([pattern]) VALUES('%last%name%') ,('%first%name%') ,('%surname%') ,('%maiden%name%') ,('%full%name%') ,('%family%name%') ,('%birthday%') ,('%date%of%birth%') ,('%birth%date%') ,('dob') ,('%email%') ,('%e-mail%') ,('%addr%') ,('%street%') ,('%city%') ,('%phone%') ,('%mobile%') ,('%natel%') ,('%area%code%') ,('%postal%') ,('%zip%') ,('%npa%') ,('%AVS%') ,('%AHV%') ,('%social_security_number%') ,('%fidelity%') ,('%loyalty_card%') ,('%insurance_card%') ,('%vip_card%') ,('%starcard%') ,('%covercard%') INSERT INTO @Tblpattern ([pattern], [toExclude]) VALUES('%periodicity%', 1) --,('%text', 1) ,('%id', 1) --#endregion --#region find all fields that match the patterns DECLARE crsPatternInclude CURSOR FAST_FORWARD READ_ONLY FOR SELECT [pattern], [toExclude] FROM @Tblpattern c WHERE c.[toExclude] = 0 OPEN crsPatternInclude FETCH NEXT FROM crsPatternInclude INTO @pattern, @toExclude WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [#classification] ([table_schema], [table_name], [column_name], [matchedByPattern] ,[data_type]) SELECT [c].[TABLE_SCHEMA], [c].[TABLE_NAME], [c].[COLUMN_NAME], @pattern, c.[DATA_TYPE] FROM [INFORMATION_SCHEMA].[COLUMNS] c WHERE c.[COLUMN_NAME] LIKE @pattern AND EXISTS( /* is in a table and not a view */ SELECT 1 FROM sys.[tables] st WHERE SCHEMA_NAME(st.[schema_id]) = c.[TABLE_SCHEMA] AND st.[name] = c.[TABLE_NAME] AND st.[type_desc] = 'USER_TABLE' ) AND NOT EXISTS ( /* column is not already in the list */ SELECT 1 FROM [#classification] t WHERE t.[table_schema] = c.[TABLE_SCHEMA] AND t.[table_name] = c.[TABLE_NAME] AND t.[column_name] = c.[COLUMN_NAME] ) AND NOT EXISTS ( /* not a PK */ SELECT 1 FROM sys.tables tab LEFT OUTER JOIN sys.indexes pk ON tab.object_id = pk.object_id AND [pk].[is_primary_key] = 1 OUTER APPLY ( SELECT [col].[name] FROM sys.index_columns ic INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE ic.object_id = tab.object_id AND [ic].[index_id] = pk.index_id ) D(column_names) WHERE [D].[column_names] IS NOT NULL AND d.[column_names] = c.[COLUMN_NAME] AND tab.[name] = c.[TABLE_NAME] AND SCHEMA_NAME(tab.[schema_id]) = c.[TABLE_SCHEMA] ) AND NOT EXISTS( /* not a FK */ SELECT 1 FROM sys.foreign_key_columns AS fkc INNER JOIN sys.foreign_keys AS fk ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.tables AS t ON fkc.parent_object_id = t.object_id INNER JOIN sys.tables AS rt ON fkc.referenced_object_id = rt.object_id INNER JOIN sys.columns AS pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id INNER JOIN sys.columns AS c1 ON fkc.referenced_object_id = c1.object_id AND fkc.referenced_column_id = c1.column_id WHERE SCHEMA_NAME(t.[schema_id]) = c.[TABLE_SCHEMA] AND t.[name] = c.[TABLE_NAME] AND pc.[name] = c.[COLUMN_NAME] ) /* only consider columns holding number, text and xml. */ AND c.[DATA_TYPE] IN ( 'text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar', 'sysname', 'Account_numbers', 'Alpha_table_key', 'Large_name_field', 'Medium_remarks', 'Normal_name_field', 'Small_alphabetical_field', 'Small_remarks', 'Userid', 'Week', 'VGUID_identifier', 'Large_text', 'Large_remarks', 'tinyint', 'smallint', 'int', 'real', 'money', 'float', 'decimal', 'numeric', 'bigint', 'xml', 'Small_integer' ) AND NOT EXISTS( /* ignore computed columns */ SELECT 1 FROM sys.[tables] t JOIN sys.columns c1 ON c1.[object_id] = t.[object_id] WHERE c1.[is_computed] = 1 AND SCHEMA_NAME(t.[schema_id]) = c.[TABLE_SCHEMA] AND t.[name] = c.[TABLE_NAME] AND c1.[name] = c.[COLUMN_NAME] ) /* do not process known tables not used anymore in the arizona database. see OCTP-724 */ AND ( DB_NAME()='arizona' AND c.[TABLE_SCHEMA]+'.'+c.[TABLE_NAME] NOT IN( 'dbo.TT_File_Transfer', 'dbo.pfs_gdi$', 'dbo.BOM_revision', 'dbo.fixed_price_Pmed_bkp', 'dbo.Project_weighting', 'dbo.TT_Visilab_AD_AMR_regular_import', 'dbo.ATS_time_period_balance', 'dbo.EMCH_payroll_period_link', 'dbo.TT_LD_EMC_Payroll_Var', 'dbo.TT_Visilab_address_data_regular_import', 'dbo.WKPA_capacity', 'dbo.Clocking_summary', 'dbo.APS_version_hist_detail', 'dbo.TT_Synchro_Account_List', 'dbo.EAS_APS_operation_link', 'dbo.EMC_absence', 'dbo.TT_Payroll_Stat', 'dbo.Manual_clocking', 'dbo.TT_Visilab_IT_AMR_regular_import', 'dbo.Activity_supplement', 'dbo.ZDBA_localisation', 'dbo.Clocking', 'dbo.TT_Visilab_item_data_regular_import', 'dbo.TT_Galenicare_Bonart', 'dbo.Process_revision', 'dbo.PCA_EMC_absence', 'dbo.TT_Search_Item', 'dbo.TT_Pre_Calc', 'wkl.ArizonaToWKLDataTransformationLog', 'dbo.Sheet1$', 'dbo.EMC_termination_type', 'dbo.Family_allowance', 'dbo.TT_EPP_Withholding_Tax', 'dbo.EMC_termination_reason', 'dbo.TT_GetNext_ID', 'dbo.TT_LORE_item_facing', 'dbo.Interco_synchronization', 'dbo.TT_Tax_List_Recap', 'dbo.Quantity_formula_detail', 'dbo.TT_VMI', 'dbo.Payroll_batch_bank', 'dbo.TT_LD_generic_item', 'dbo.Occupation', 'dbo.Pro_svc_received_answer', 'dbo.TT_DL_planning', 'dbo.Asset_insurance', 'dbo.TT_Visilab_ADG_AMR_regular_import', 'dbo.Payroll_batch_detail', 'dbo.TT_PH_RG_Label_Print', 'dbo.Asset_leasing', 'dbo.TT_Visilab_data_regular_import', 'dbo.EAS_structure_operation', 'dbo.Time_table_schema_history', 'dbo.TT_PH_purchase_order_error', 'dbo.PDX_mapping_wholesaler', 'dbo.Event_premise', 'dbo.TT_DH_Manual_Stock_Allocation', 'dbo.TT_Clocking_Key_Status', 'dbo.Asset_maintenance', 'dbo.TT_STM', 'dbo.Symptom', 'dbo.External_entry', 'dbo.PRH_list_column_link', 'dbo.TTSNH_authorized_status', 'dbo.TT_User_Badge_Identifier', 'dbo.ATT_time_period_balance', 'dbo.EAS_structure_trigger', 'dbo.TT_SN_DH_WKO', 'dbo.TT_GAL_Price_Factor', 'dbo.Contract_daily_time', 'dbo.TT_LD_Doc_Line', 'dbo.TT_DL_Part_List_Exchange', 'dbo.HR_certificate', 'dbo.TT_Synchro', 'dbo.TT_WKOP', 'dbo.TT_BRE_Plan_Fab_Index', 'dbo.TT_IMP_bom_item', 'dbo.Time_table_type_history', 'dbo.Contract_time_adjustment', 'dbo.TT_LD_EMC_periodicity', 'dbo.TT_IMP_item', 'dbo.Time_table_type_detail', 'dbo.Facility_technical_criteria', 'dbo.TT_LD_PR_Employee_Profile', 'dbo.TT_IMP_item_2', 'dbo.TT_IMP_item_stock', 'dbo.PR_certificate_header', 'dbo.TT_PRCH', 'dbo.TT_IMP_item_cover_date', 'dbo.TT_LD_IT_Category_management', 'dbo.TT_IMP_item_conso', 'dbo.PRH_activity_type_link', 'dbo.Payroll_scale_date', 'dbo.TT_OU_Price_Code_Priority', 'dbo.TT_IMP_item_PL', 'dbo.OFFCLD_reject', 'dbo.TT_LD_DH_FINA', 'dbo.TT_IMP_item_PL_Tot', 'upd.Change_tracking_monitor', 'dbo.TT_PH_APOS_item_status', 'dbo.Payroll_calc_detail', 'dbo.TT_DH_auto_addr', 'dbo.TT_IMP_wko', 'dbo.FA_RAFAM_type', 'dbo.Alloy_element_quantity', 'dbo.Arrondis$', 'dbo.Alloy_element', 'dbo.TT_IMP_DL', 'dbo.Prescription_task_link', 'dbo.WKOP_planning_header', 'dbo.TT_DH_auto_document_line', 'dbo.Alloy_detail', 'dbo.TT_IMP_DL_1', 'dbo.TT_PHGD_Fixed_Price', 'dbo.TT_IMP_Matr', 'dbo.PRH_layout_link', 'dbo.TT_PHGD_Exp_AC', 'dbo.PerformancePoint', 'dbo.Offline_clocking', 'dbo.PerformancePointAggregation', 'dbo.TT_PHGD_Exp_ACBARCODE', 'dbo.ATT_cust_list_column', 'dbo.HR_training_field', 'dbo.TT_BCR_WKO', 'dbo.Weekly_schema_detail', 'dbo.PRH_period_type', 'dbo.sysdiagrams', 'dbo.TT_PHGD_Exp_ACNAM', 'dbo.TT_Auto_Data_Acquisition', 'dbo.PRH_totalizator_link', 'dbo.RAFAM_action_monitor', 'dbo.Knowledge_category', 'dbo.ATT_list_column_link', 'dbo.Knowledge_base_category', 'dbo.TT_BCR_RECAP_T2', 'wkl.ServiceBrokerError', 'dbo.TT_PHGD_Exp_NETART_ACTION', 'dbo.HR_training_type', 'dbo.TT_LD_budget', 'dbo.TT_Export_WHSPRICE', 'dbo.PJS_totalizator_link', 'dbo.Knowledge_base_key', 'dbo.PR_cust_list_column', 'dbo.TT_PHGD_Exp_NETART_GREEN', 'dbo.Bmc_license', 'del.PH_qualification', 'dbo.TT_PHGD_Exp_WHSPRICE', 'dbo.BMH_rule_type_link', 'dbo.TT_Ctrl_Item_PH', 'dbo.TT_LD_Fixed_Price_Factor', 'dbo.PCA_EMC_loan', 'dbo.TT_Export_NETART_GREEN', 'dbo.Process_document', 'dbo.EFT_OW_rule', 'dam.MonitorTableHash', 'dbo.BOM_rule', 'dbo.TT_Export_NETART_ACTION', 'dbo.ACS_totalizator_link', 'dbo.BML_rule_link', 'dbo.TT_Export_ACNAM', 'dbo.PR_global_value_link', 'dam.HashValueUniqueIndexInt', 'dbo.CCI_address_key', 'dbo.Contract_time_table_header', 'dbo.TT_DWH_sales_extraction', 'dbo.Custom_export_licence', 'dbo.BMOH_selected_rule', 'dbo.dtproperties', 'dbo.TT_SUN_Web_Item', 'dam.HashValueUniqueIndexGuid', 'dbo.TT_LD_Item_Visilab', 'dbo.TT_DWH_price_modifier_extraction', 'dbo.Payroll_period_type', 'dbo.TT_AC_ACPE_lock', 'dbo.Bmc_system', 'dbo.Process_document_link', 'dbo.TT_ITQ_Export', 'dbo.TT_Export_AC', 'dbo.Surplus_line', 'dbo.Process_line_antecedence', 'dbo.Project_resource', 'dbo.TT_PH_ITQ_Import', 'dbo.PRCH_EPP_link', 'dbo.TT_INB_generation', 'dbo.systranschemas', 'dbo.Process_line_option', 'dbo.PJS_layout_detail', 'dbo.TT_OU_advertising_process', 'dbo.TT_LD_CCI_ATA_online', 'dbo.Process_overlap_line', 'dbo.Clocking_message', 'dbo.TT_DH_transitory', 'dbo.GaldatItemsWithStatus', 'dbo.CLK_user_interface', 'dbo.BMOH_formula_variable', 'dbo.PJS_task_selection', 'dbo.TT_Web_Item', 'dbo.TT_Ctrl_Item', 'dbo.Item_alloy_quantity', 'dbo.CLT_authorized_CLKG', 'dbo.Machine_downtime', 'dbo.Element_transaction', 'dbo.Task_resource', 'dbo.EMC_certificate_layout', 'dbo.AUTO_to_round$', 'dbo.TT_CICR_LD_PCEM', 'dbo.BOM_location', 'dbo.Payroll_period', 'dbo.Clocking_terminal_profile', 'dbo.TT_ITSH_generation', 'dbo.TT_PHFCM_extraction', 'dbo.PRH_PRHC_link', 'dbo.Offline_clocking_detail', 'dbo.CLTP_schedule', 'dbo.TT_IT', 'dbo.HR_skill_level', 'dbo.AX_item_mappings_rules', 'dbo.CLTP_user_interface', 'dbo.PHGD_MCMTXT_TEMP', 'dbo.TT_ITCAT', 'dbo.TT_FUN_OUSH_link', 'dbo.Task_budget_detail', 'dbo.Asset_investment_history', 'dbo.HR_skill_type', 'dbo.TT_OU_store_history', 'dbo.Salary_grade_funct_link', 'dbo.WKOP_planning', 'dbo.Process_overlap_header', 'dbo.Event_sector', 'dbo.zz_tr_01', 'dbo.TT_address_exp', 'dbo.document_line_link_pda', 'dbo.TT_address_exp_ESC', 'dbo.TT_address_double', 'dbo.TT_PMP', 'dbo.Wprice_item', 'dbo.TT_item_PV', 'dbo.TriaFact_AutoCorrect', 'dbo.TT_fobl_lines', 'dbo.PHGD_FCMTXT_lpe', 'dbo.PHGD_MCMTXT_lpe', 'dbo.TT_item_availability_check_mouchard', 'dbo.patient_card_fid$', 'dbo.temp_mouchard_stm', 'dbo.T_STM_Create', 'dbo.TT_Change_tracking_history', 'dbo.TT_change_tracking_triggers', 'del.Address_merge_prevalidation', 'del.PDX_mapping_wholesaler', 'dbo.Bmc_application_default_backup_vesta', 'dbo.ITCAT_IT_link_09_09_2015', 'dbo.Config_Sync_V', 'dbo.TT_PPUB_itk_src', 'dbo.preissenkung', 'dbo.tt_compteur', 'dbo.DBA_Label_Monitoring', 'dbo.DBA_Servers_Site', 'dbo.LORE_supplying_procedure_savGBA', 'dbo.brand$', 'dbo.brand_line$', 'dbo.TT_PPUB_itk', 'dbo.brl_phcode$', 'dbo.TT_xPUB_itk', 'dbo.tt_GBA_list', 'dbo.TT_Criteria', 'dbo.DBA_Job_Monitoring', 'dbo.TT_item_II', 'dbo.DBA_Job_List', 'dbo.TT_item_type_2', 'dbo.TT_item_type_3', 'dbo.TT_item_type_5', 'dbo.TT_item_type_6', 'dbo.Item_LORE_min_max_614', 'dbo.Bichsel', 'dbo.TT_Item_20171005', 'dbo.xpub_to_upload', 'dbo.TT_OU_price_code_priority_20171026', 'dbo.RAFAM_notification_error', 'dbo.aps_monitor_table_SUN_SPE', 'dbo.TT_GBA_Supplier', 'dbo.aps_monitor_table_SUN_SPE2', 'dbo.temp_PHGD_ACCHAIN', 'dbo.TT_address', 'dbo.S13X_NUS', 'dbo.TT_itsh', 'dbo.sales_tax_code_sav', 'dbo.tt_err_sync_dve', 'dbo.sales_tax_rate_sav', 'dbo.SAV_bmc_user_role', 'dbo.TT_maj_default_supplier', 'dbo.TT_item', 'dbo.TT_Fusion_Item_relation_info', 'dbo.SUN_DBA_LOGS', 'dbo.LD_external_item', 'dbo.item_link_del', 'dbo.KuKo038', 'dbo.LORE_item_facing_20160302', 'dbo.tt_table', 'dbo.MinMax_Facings_Output$', 'dbo.vip_card_20040831', 'dbo.TT_records', 'dbo.Account_link_secu_doublon', 'dbo.Account_chart_secu_doublon', 'dbo.Account_secu_doublon', 'dbo.Account_key_secu_doublon', 'dbo.Account_text_secu_doublon', 'dbo.Account_chart_secu_doublons_SPE', 'dbo.TT_Upd_Account', 'dbo.item_quantity_sav_20170405', 'dbo.Account_chart_secu_doublons', 'dbo.date_peremption$', 'dbo.Address_group_text_Merge', 'dbo.Bmc_user_role_2016_04_04', 'dbo.TT_fusion_Item_status_history_II', 'dbo.TT_Sync_Autoprod_AZ', 'dam.ChecksumUniqueIndex_Work_type_text', 'dbo.LD_item_FP', 'dbo.Entry_Debug', 'dbo.TT_excepted_field', 'dbo.LORE_item_Facing20160308', 'dbo.tt_ld_entry_dpa', 'dbo.t_adk', 'dbo.FUN_OUSH_link_patrice', 'dbo.tt_ld_entry_dpa_2', 'dbo.Entry_Sans_DH', 'dbo.TT_PHGD_AC_Ins_Code', 'dbo.TT_Rayons_textes', 'dbo.TT_OU_advertising_process_dca', 'dbo.OU_store_type_2016_05_12', 'dbo.OU_store_type_text_2016_05_12', 'dbo.ACS_totalizator_link_dve', 'dbo.TT_XPUB_itk', 'dbo.tt_gal_price_factor', 'dbo.pda_CRM_reprocessing', 'dbo.TT_Address', 'dbo.Item_LORE_min_max_complet', 'dbo.TT_Fusion_Item', 'dbo.ZZ_DVE_SYNC', 'dbo.Item_catalog_text_2016_11_09', 'dbo.TT_fusion_Address', 'dbo.ITCAT_IT_link_2016_11_09', 'dbo.TT_Fusion_Item_text', 'dbo.TT_Fusion_Item_sale', 'dbo.TT_Fusion_PH_Item', 'dbo.TT_fusion_telecom', 'dbo.TT_ITSH', 'dbo.TT_Fusion_Item_Status_History', 'dbo.TT_LORE_item_facing_20150609', 'dbo.TT_SUN_sp_exported', 'dbo.LORE_service_level_rate_2016_08_19', 'dbo.z_dve_pharmacode', 'dbo.OU_store_history_2016_08_22', 'dbo.DPA_tax_list', 'dbo.DPA_dh_tax', 'dbo.Item_statistics_yearly_OLD', 'dbo.TT_fusion_item_relation_info', 'dbo.APS_monitor_row_OLD', 'dbo.APS_monitor_row_OLD2', 'dbo.Item_statistics_monthly_new', 'dbo.Item_statistics_yearly_new', 'dbo.ph_item_20040325', 'dbo.t_ol', 'dbo.t_ol2', 'dbo.zz_dve_duree', 'dbo.MSmerge_tombstone', 'dbo.MSmerge_contents', 'dbo.MSmerge_genhistory', 'dbo.MSmerge_settingshistory', 'dbo.sysmergeschemachange', 'dbo.sysmergesubsetfilters', 'dbo.MSdynamicsnapshotjobs', 'dbo.MSmerge_altsyncpartners', 'dbo.MSmerge_partition_groups', 'dbo.MSmerge_generation_partition_mappings', 'dbo.MSmerge_current_partition_mappings', 'dbo.MSmerge_past_partition_mappings', 'dbo.MSmerge_dynamic_snapshots', 'dbo.MSmerge_supportability_settings', 'dbo.MSmerge_sessions', 'dbo.MSmerge_log_files', 'dbo.MSmerge_articlehistory', 'dbo.MSrepl_errors', 'dbo.MSmerge_history', 'dbo.MSmerge_agent_parameters', 'dbo.MSmerge_conflicts_info', 'dbo.MSmerge_metadataaction_request', 'dbo.MSmerge_errorlineage', 'dbo.tmpOrdonnance', 'dbo.MSmerge_identity_range', 'dbo.sysmergepartitioninfo', 'vesta.VestaVersion', 'del.PHGD_WHS', 'del.PHGD_WHSPRICE', 'del.PHGD_AC', 'del.PHGD_ACACT', 'del.PHGD_ACBARCODE', 'del.PHGD_ACCHAIN', 'del.PHGD_ACCOMP', 'del.PHGD_ACLIM', 'del.PHGD_ACNAM', 'del.PHGD_ACPRICEALG', 'del.PHGD_ACMED', 'del.Numerary_value', 'del.PHGD_ACWHS', 'del.Numerary_value_text', 'del.PHGD_COMP', 'dbo.ZZ_DVE_Item_calc_besoin', 'dbo.TT_MESA_narcotic_export', 'dbo.Item_LORE_min_max_test', 'dbo.pda_duration_1500_3000', 'dbo.ZZPDA_Rebuild_DLL', 'dbo.tt_auto_data_acquisition_dpa', 'dbo.Applications', 'dbo.Settings', 'dbo.Bmc_desktop_property_save', 'dbo.ZZ_DVE_Item_calc_besoin_2', 'dbo.ZZ_DVE_ITSSI', 'dbo.BMC_trace', 'dbo.BMC_Trace_dpa_item', 'dbo.BMC_Trace_item_2', 'dbo.TRACE_20120307_LPO', 'dbo.ZZ_APS_monitor_row_OLD_SPE', 'dbo.addres_link_201800807', 'dbo.ph_prescriber_sav_20191203', 'dbo.ph_physician_sav_20191203', 'dbo.bkp_Item_inventory_20191210_AMA', 'dbo.Bmc_application_default_backup_vesta_migration_lot3', 'dbo.TT_Bmc_application_default_backup_vesta_migration_lot3' ) ) ; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Added columns matched to list ('+@pattern+'). '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.'; FETCH NEXT FROM crsPatternInclude INTO @pattern, @toExclude END CLOSE crsPatternInclude DEALLOCATE crsPatternInclude --#endregion --#region Look in text fields for match on the pattern IF @searchInFields = 1 BEGIN PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - populate from field content'; INSERT INTO [#all_text_fields] ([table_schema], [table_name], [column_name], [data_type]) SELECT SCHEMA_NAME(t.[schema_id]), t.[name], c.[name], st.[name] FROM sys.[tables] t JOIN sys.columns c ON c.[object_id] = t.[object_id] JOIN sys.[types] st ON st.[system_type_id] = c.[system_type_id] AND st.[user_type_id] = c.[user_type_id] WHERE t.[type_desc] = 'USER_TABLE' AND c.[is_computed] = 0 AND st.[collation_name] IS NOT NULL AND c.[max_length] > 90 ORDER BY SCHEMA_NAME(t.[schema_id]), t.[name], c.[name] ; DECLARE crsr_pattern_in_field CURSOR FAST_FORWARD READ_ONLY FOR select [p].[pattern] FROM @Tblpattern p WHERE p.[toExclude] = 0 OPEN crsr_pattern_in_field FETCH NEXT FROM crsr_pattern_in_field INTO @pattern WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Looking for pattern '+@pattern; DECLARE crsr_in_field CURSOR FAST_FORWARD READ_ONLY FOR SELECT [table_schema], [table_name], [column_name], [data_type] FROM [#all_text_fields] OPEN crsr_in_field FETCH NEXT FROM crsr_in_field INTO @schema_name, @table_name, @column_name, @data_type WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Looking in '+@schema_name+'.'+@table_name+'.'+@column_name; SELECT @cmd = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@tplCheckInField,'@schema@', @schema_name), '@tbl@', @table_name), '@field@', @column_name), '@pattern@', @pattern), '@data_type@', @data_type); EXEC(@cmd) --PRINT @cmd FETCH NEXT FROM crsr_in_field INTO @schema_name, @table_name, @column_name, @data_type END CLOSE crsr_in_field DEALLOCATE crsr_in_field FETCH NEXT FROM crsr_pattern_in_field INTO @pattern END CLOSE crsr_pattern_in_field DEALLOCATE crsr_pattern_in_field END --#endregion --#region exclude results matched before DECLARE crsPatternExclude CURSOR FAST_FORWARD READ_ONLY FOR SELECT [pattern], [toExclude] FROM @Tblpattern c WHERE c.[toExclude] = 1 OPEN crsPatternExclude FETCH NEXT FROM crsPatternExclude INTO @pattern, @toExclude WHILE @@FETCH_STATUS = 0 BEGIN DELETE c FROM [#classification] c WHERE c.[COLUMN_NAME] LIKE @pattern AND @toExclude = 1 ; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Excluded columns from matched list ('+@pattern+'). '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.'; FETCH NEXT FROM crsPatternExclude INTO @pattern, @toExclude END CLOSE crsPatternExclude DEALLOCATE crsPatternExclude --#endregion --#region exlude from results tables with 0 rows DELETE [c] FROM [#classification] c JOIN ( SELECT SCHEMA_NAME(t.[schema_id]) AS [schema_name], t.name AS [table_name], SUM([ds].[row_count]) as [totalrows] FROM sys.dm_db_partition_stats ds JOIN sys.[tables] t ON ds.[object_id] = t.[object_id] GROUP BY t.[name], t.[schema_id] HAVING COUNT(1)=0 )cnt ON cnt.[schema_name] = c.[table_schema] AND cnt.[table_name] = c.[table_name] ; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Removed positive results from empty tables. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.'; --#endregion --#region Assign classification on matched columns PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Assign classification on matched columns'; DECLARE crsr_classify_active CURSOR FAST_FORWARD READ_ONLY FOR --SELECT 'ADD SENSITIVITY CLASSIFICATION TO ['+c.[table_schema]+'].['+c.[table_name]+'].['+c.[column_name]+'] WITH ( LABEL=''Confidential'', INFORMATION_TYPE=''Galenica Used Personal Informations'', RANK=High );' AS command SELECT 'EXEC dbo.setSensitivity @schemaName = '''+ c.[TABLE_SCHEMA] +''', @tableName = '''+[c].[TABLE_NAME]+''', @columnName = '''+[c].[COLUMN_NAME]+''', @category = '''+@matchedType+''', @sensitivity = ''Confidential'', @rank = ''High'', @removeClassification = 0 ' FROM [#classification] c; OPEN crsr_classify_active FETCH NEXT FROM crsr_classify_active INTO @cmd WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @cmd EXEC(@cmd); FETCH NEXT FROM crsr_classify_active INTO @cmd END CLOSE crsr_classify_active DEALLOCATE crsr_classify_active --#endregion --#region set classification "other - general" on every other columns PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Assign classification on not matched columns'; IF @useSql2019Syntax = 0 BEGIN SET @cmd=' INSERT INTO #existing_classification([table_schema], [table_name], [column_name], [category], [sensibility], [rank]) SELECT CAST(schema_name(O.schema_id) AS VARCHAR(255)) AS schema_name, CAST(O.NAME AS VARCHAR(255))AS table_name , CAST(C.NAME AS VARCHAR(255))AS column_name , CAST(information_type AS VARCHAR(255)) as information_type , CAST(sensitivity_label AS VARCHAR(255)) as sensitivity_label, NULL FROM ( SELECT IT.major_id, IT.minor_id, IT.information_type, L.sensitivity_label FROM ( SELECT major_id, minor_id, value AS information_type FROM sys.extended_properties WHERE NAME = ''sys_information_type_name'' ) IT FULL OUTER JOIN ( SELECT major_id, minor_id, value AS sensitivity_label FROM sys.extended_properties WHERE NAME = ''sys_sensitivity_label_name'' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id --WHERE CAST(information_type AS VARCHAR(255)) <> ''Galenica Used Personal Informations'' '; END ELSE BEGIN SET @cmd=' INSERT INTO #existing_classification([table_schema], [table_name], [column_name], [category], [sensibility], [rank]) SELECT SCHEMA_NAME([O].[schema_id]) AS schema_name ,CAST(O.name AS VARCHAR(255)) AS table_name ,CAST(C.name AS VARCHAR(255)) AS column_name ,CAST(sc.information_type AS VARCHAR(255)) as information_type ,CAST(sc.label AS VARCHAR(255)) as label ,CAST(sc.rank_desc AS VARCHAR(255)) as rank_desc FROM sys.sensitivity_classifications sc JOIN sys.objects O ON [sc].[major_id] = O.object_id JOIN sys.columns C ON [sc].[major_id] = C.object_id AND [sc].[minor_id] = [C].[column_id] --WHERE CAST(sc.information_type AS VARCHAR(255)) <> ''Galenica Used Personal Informations'' '; END EXEC(@cmd); DECLARE crsr_classify_other CURSOR FAST_FORWARD READ_ONLY FOR --SELECT 'ADD SENSITIVITY CLASSIFICATION TO ['+[ic].[TABLE_SCHEMA]+'].['+[ic].[TABLE_NAME]+'].['+[ic].[COLUMN_NAME]+'] WITH ( LABEL=''General'', INFORMATION_TYPE=''Other'', RANK=Low )' SELECT 'EXEC dbo.setSensitivity @schemaName = '''+ic.[TABLE_SCHEMA]+''', @tableName = '''+[ic].[TABLE_NAME]+''', @columnName = '''+[ic].[COLUMN_NAME]+''', @category = ''Other'', @sensitivity = '''+@unmatchedType+''', @rank = ''Low'', @removeClassification='+CAST(@assignUnmatchedColumns ^ 1 AS NVARCHAR(2))+' ' FROM [INFORMATION_SCHEMA].[COLUMNS] ic JOIN [INFORMATION_SCHEMA].[TABLES] it ON it.[TABLE_SCHEMA] = ic.[TABLE_SCHEMA] AND it.[TABLE_NAME] = ic.[TABLE_NAME] WHERE [it].[TABLE_TYPE] = 'BASE TABLE' AND NOT EXISTS( /* column is not classified */ SELECT [ec].[table_schema], [ec].[table_name], [ec].[column_name], [ec].[category], [ec].[sensibility], [ec].[rank] FROM #existing_classification ec WHERE ec.[table_schema] = ic.[TABLE_SCHEMA] AND ec.[table_name] = ic.[TABLE_NAME] AND ec.[column_name] = ic.[COLUMN_NAME] AND ec.[category] = @matchedType ) AND NOT EXISTS( /* ignore computed columns */ SELECT 1 FROM sys.[tables] t JOIN sys.columns c ON c.[object_id] = t.[object_id] WHERE c.[is_computed] = 1 AND SCHEMA_NAME(t.[schema_id]) = ic.[TABLE_SCHEMA] AND t.[name] = ic.[TABLE_NAME] AND c.[name] = ic.[COLUMN_NAME] ) OPEN crsr_classify_other FETCH NEXT FROM crsr_classify_other INTO @cmd WHILE @@FETCH_STATUS = 0 BEGIN --PRINT @cmd; EXEC(@cmd); FETCH NEXT FROM crsr_classify_other INTO @cmd END CLOSE crsr_classify_other DEALLOCATE crsr_classify_other --#endregion /* check classification recorded */ IF @useSql2019Syntax = 1 BEGIN EXEC(' SELECT SCHEMA_NAME([O].[schema_id]) AS schema_name ,CAST(O.name AS VARCHAR(255)) AS table_name ,CAST(C.name AS VARCHAR(255)) AS column_name ,CAST(sc.information_type AS VARCHAR(255)) as information_type ,CAST(sc.label AS VARCHAR(255)) as label ,CAST(sc.rank_desc AS VARCHAR(255)) as rank_desc FROM sys.sensitivity_classifications sc JOIN sys.objects O ON [sc].[major_id] = O.object_id JOIN sys.columns C ON [sc].[major_id] = C.object_id AND [sc].[minor_id] = [C].[column_id] '); END ELSE BEGIN SELECT schema_name(O.schema_id) AS schema_name, O.[name] AS table_name, C.[name] AS column_name, [EP].[information_type], [EP].[sensitivity_label] FROM ( SELECT IT.major_id, IT.minor_id, IT.information_type, L.sensitivity_label FROM ( SELECT major_id, minor_id, value AS information_type FROM sys.extended_properties WHERE NAME = 'sys_information_type_name' ) IT FULL OUTER JOIN ( SELECT major_id, minor_id, value AS sensitivity_label FROM sys.extended_properties WHERE NAME = 'sys_sensitivity_label_name' ) L ON IT.major_id = L.major_id AND IT.minor_id = L.minor_id ) EP JOIN sys.objects O ON EP.major_id = O.object_id JOIN sys.columns C ON EP.major_id = C.object_id AND EP.minor_id = C.column_id ; END /* see what has been recognized as sensitive */ SELECT * FROM [#classification] c ORDER BY [c].[table_schema], [c].[table_name], [c].[column_name] IF OBJECT_ID('dbo.setSensitivity') IS NOT NULL BEGIN DROP PROCEDURE dbo.setSensitivity; END