1179 lines
41 KiB
PL/PgSQL
1179 lines
41 KiB
PL/PgSQL
/*=============================================================================
|
|
|
|
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
|
|
|
|
|