Для MS Sql Server
/*
** Project: BPMonline
** DBMS : MSSQL 2008
** Type : Stored Procedure
** Name : tsp_AnonymizeAllData
*/
IF NOT OBJECT_ID('[dbo].[tsp_GetAnonymizationIngoreColumns]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_GetAnonymizationIngoreColumns]
END
GO
CREATE PROCEDURE [dbo].[tsp_GetAnonymizationIngoreColumns]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @baseLookupSchemaUId uniqueidentifier = '11AB4BCB-9B23-4B6D-9C86-520FAE925D75'
DECLARE @baseCodeLookupSchemaUId uniqueidentifier = '2681062B-DF59-4E52-89ED-F9B7DC909AB2'
DECLARE @ignoreColumns table (tableName SYSNAME, columnName SYSNAME)
INSERT INTO @ignoreColumns VALUES(N'Activity', N'AllowedResult')
INSERT INTO @ignoreColumns VALUES(N'ActivityCategory', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ActivityParticipantRole', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ActivityPriority', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ActivityStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ActivityType', N'Code')
INSERT INTO @ignoreColumns VALUES(N'ActivityType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'AdditionalParam', N'ColumnCaption')
INSERT INTO @ignoreColumns VALUES(N'Amenity', N'Name')
INSERT INTO @ignoreColumns VALUES(N'AmenitySubtype', N'Name')
INSERT INTO @ignoreColumns VALUES(N'BulkEmailCategory', N'Name')
INSERT INTO @ignoreColumns VALUES(N'BulkEmailLaunchOption', N'Name')
INSERT INTO @ignoreColumns VALUES(N'CallDirection', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Campaign', N'SchemaData')
INSERT INTO @ignoreColumns VALUES(N'CampaignFilter', N'SearchData')
INSERT INTO @ignoreColumns VALUES(N'CampaignStepType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'CashflowType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'CashflowType', N'Description')
INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'ButtonCaption')
INSERT INTO @ignoreColumns VALUES(N'Category', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ChartAggregationType', N'AggregationTypeCode')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ChartEntityColumn')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ChartSeriesKind')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'Filter')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'GroupByField')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'GroupByTypeDateTimeId')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ModuleObjAccessField')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'OrderByChartPropertyColumn')
INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'OrderDirection')
INSERT INTO @ignoreColumns VALUES(N'Command', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Communication', N'Code')
INSERT INTO @ignoreColumns VALUES(N'Communication', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ConfigItemCategory', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ConfigItemStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ConfigItemType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ContentBlock', N'Config')
INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'ContextHelpId')
INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'LMSUrl')
INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'ProductEdition')
INSERT INTO @ignoreColumns VALUES(N'DataType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DependencyCategory', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DependencyType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DependencyType', N'ReverseTypeName')
INSERT INTO @ignoreColumns VALUES(N'Dimension', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Dimension', N'Path')
INSERT INTO @ignoreColumns VALUES(N'DisplayDateFormat', N'Format')
INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Default')
INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Handler')
INSERT INTO @ignoreColumns VALUES(N'DocumentState', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DocumentType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'EmailSendStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'ColumnPath')
INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'Position')
INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'Name')
INSERT INTO @ignoreColumns VALUES(N'EmailType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'EntitySchemaRecRightOperation', N'Description')
INSERT INTO @ignoreColumns VALUES(N'EntitySchemaRecRightOperation', N'Value')
INSERT INTO @ignoreColumns VALUES(N'ESNNotificationType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ESNNotificationType', N'Action')
INSERT INTO @ignoreColumns VALUES(N'ExternalSource', N'Name')
INSERT INTO @ignoreColumns VALUES(N'FolderType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Gender', N'Name')
INSERT INTO @ignoreColumns VALUES(N'GroupByTypeDateTime', N'Description')
INSERT INTO @ignoreColumns VALUES(N'GroupByTypeDateTime', N'Name')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'AmenityType')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterClass')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterDisplayName')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterName')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterProperty')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'PropertyPath')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'SelectedParameter')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'AmenityValue')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'ParameterDisplayValue')
INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'ParameterValue')
INSERT INTO @ignoreColumns VALUES(N'IntegrationSettings', N'Name')
INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'CodePage')
INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'VideoUrl')
INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'VideoCaption')
INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'AcademyUrl')
INSERT INTO @ignoreColumns VALUES(N'KnowledgeBaseType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LDAPAuthType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LDAPAuthType', N'Value')
INSERT INTO @ignoreColumns VALUES(N'LeadDisqualifyReason', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadMedium', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadRegisterMethod', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadType', N'Description')
INSERT INTO @ignoreColumns VALUES(N'LeadType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadStage', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadTypeStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LendingState', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ListingType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Listing', N'Latitude')
INSERT INTO @ignoreColumns VALUES(N'Listing', N'Longitude')
INSERT INTO @ignoreColumns VALUES(N'Lookup', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LookupFolder', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Macros', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Address')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Port')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'UseSSL')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'UseSSLforSending')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'SMTPServerAddress')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'SMTPPort')
INSERT INTO @ignoreColumns VALUES(N'MailServer', N'ExchangeEmailAddress')
INSERT INTO @ignoreColumns VALUES(N'MailServerType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MailSyncPeriod', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MailSyncPeriod', N'Code')
INSERT INTO @ignoreColumns VALUES(N'MainParam', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MessageListener', N'ClassName')
INSERT INTO @ignoreColumns VALUES(N'MessagePublisher', N'ClassName')
INSERT INTO @ignoreColumns VALUES(N'MessageNotifier', N'ClassName')
INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'AppClassName')
INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'ClientId')
INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'ClientClassName')
INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'SecretKey')
INSERT INTO @ignoreColumns VALUES(N'MLModel', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MLModel', N'MetaData')
INSERT INTO @ignoreColumns VALUES(N'MLModel', N'TrainingSetQuery')
INSERT INTO @ignoreColumns VALUES(N'MLModel', N'BatchPredictionQuery')
INSERT INTO @ignoreColumns VALUES(N'MLModelState', N'Code')
INSERT INTO @ignoreColumns VALUES(N'MLPrediction', N'Value')
INSERT INTO @ignoreColumns VALUES(N'MLPrediction', N'Significance')
INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'ServiceUrl')
INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'TrainingEndpoint')
INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'PredictionEndpoint')
INSERT INTO @ignoreColumns VALUES(N'MLTrainSession', N'Error')
INSERT INTO @ignoreColumns VALUES(N'NotificationProvider', N'ClassName')
INSERT INTO @ignoreColumns VALUES(N'NotificationProvider', N'Type')
INSERT INTO @ignoreColumns VALUES(N'ParamMatchingType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Period', N'DueDate')
INSERT INTO @ignoreColumns VALUES(N'Period', N'StartDate')
INSERT INTO @ignoreColumns VALUES(N'PeriodType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'PortalSchemaAccessList', N'AccessEntitySchemaName')
INSERT INTO @ignoreColumns VALUES(N'ProblemPriority', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ProblemStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ProblemType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ProcessSchemaType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ProjectEntryType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Property', N'Latitude')
INSERT INTO @ignoreColumns VALUES(N'Property', N'Longitude')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageNumber')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageOrder')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageInnerOrder')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'IsFinal')
INSERT INTO @ignoreColumns VALUES(N'QuickAddMenuItem', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ReleasePriority', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ReleaseStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ReleaseType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Description')
INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Name')
INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Value')
INSERT INTO @ignoreColumns VALUES(N'RequestType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'RoleInServiceTeam', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ServiceCategory', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ServiceObjectType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ServiceStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Status', N'Name')
INSERT INTO @ignoreColumns VALUES(N'TimeUnit', N'Name')
INSERT INTO @ignoreColumns VALUES(N'TimeZone', N'Offset')
INSERT INTO @ignoreColumns VALUES(N'TimeZone', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Name')
INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Code')
INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Number')
INSERT INTO @ignoreColumns VALUES(N'LMColumnTemplate', N'ColumnName')
INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'ConditionData')
INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'ChangedColumns')
INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'MessageTemplate')
INSERT INTO @ignoreColumns VALUES(N'ListenerByNotifier', N'NotifierConnectionColumn')
INSERT INTO @ignoreColumns VALUES(N'DisplayDateFormat', N'Format')
INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'Name')
INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInMinutes')
INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInHours')
INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInDays')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'Type')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'Caption')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'ErrorDescription')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInMinutes')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInHours')
INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInDays')
INSERT INTO @ignoreColumns VALUES(N'FileType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'FileType', N'Code')
INSERT INTO @ignoreColumns VALUES(N'BulkEmailStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'BulkEmailStatus', N'IsActive')
INSERT INTO @ignoreColumns VALUES(N'LeadSource', N'Name')
INSERT INTO @ignoreColumns VALUES(N'LeadSource', N'Description')
INSERT INTO @ignoreColumns VALUES(N'Country', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Country', N'Image')
INSERT INTO @ignoreColumns VALUES(N'Country', N'BilingInfo')
INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'Name')
INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'IsInitial')
INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'IsFinal')
INSERT INTO @ignoreColumns VALUES(N'City', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Region', N'Name')
INSERT INTO @ignoreColumns VALUES(N'QueuePriority', N'Name')
INSERT INTO @ignoreColumns VALUES(N'QueuePriority', N'Value')
INSERT INTO @ignoreColumns VALUES(N'Calendar', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ContractState', N'Name')
INSERT INTO @ignoreColumns VALUES(N'ContractState', N'Position')
INSERT INTO @ignoreColumns VALUES(N'Tax', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Tax', N'Percent')
INSERT INTO @ignoreColumns VALUES(N'AddressType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'AddressType', N'ForContact')
INSERT INTO @ignoreColumns VALUES(N'AddressType', N'ForAccount')
INSERT INTO @ignoreColumns VALUES(N'RelationType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForContactContact')
INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForAccountContact')
INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForContactAccount')
INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForAccountAccount')
INSERT INTO @ignoreColumns VALUES(N'SpecificationType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'SpecificationType', N'Description')
INSERT INTO @ignoreColumns VALUES(N'Unit', N'Name')
INSERT INTO @ignoreColumns VALUES(N'Unit', N'ShortName')
INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'Color')
INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'Color')
INSERT INTO @ignoreColumns VALUES(N'OpportunityStage', N'Color')
INSERT INTO @ignoreColumns VALUES(N'QueueObjectColumn', N'Name')
INSERT INTO @ignoreColumns VALUES(N'QueueObjectColumn', N'Caption')
INSERT INTO @ignoreColumns VALUES(N'BWSSalesPeriod', N'BWSYearMonthUnq')
INSERT INTO @ignoreColumns VALUES(N'DuplicatesHistory', N'SchemaTableName')
INSERT INTO @ignoreColumns VALUES(N'FieldForceActionType', N'Name')
INSERT INTO @ignoreColumns VALUES(N'FieldForceActionType', N'Code')
INSERT INTO @ignoreColumns VALUES(N'FieldForceActionsInRule', N'Priority')
INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'Name')
INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'StartDate')
INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'DueDate')
INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'VisitCount')
INSERT INTO @ignoreColumns
SELECT DISTINCT [Name], N'Name'
FROM SysSchema
WHERE [ParentId] IN (
SELECT [Id]
FROM SysSchema
WHERE [UId] IN (@baseLookupSchemaUId, @baseCodeLookupSchemaUId))
INSERT INTO @ignoreColumns
SELECT DISTINCT [Name], N'Code'
FROM SysSchema
WHERE [ParentId] IN (
SELECT [Id]
FROM SysSchema
WHERE [UId] = @baseCodeLookupSchemaUId)
SELECT DISTINCT tableName, columnName FROM @ignoreColumns
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_DoAnonymizationCheck]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_DoAnonymizationCheck]
END
GO
CREATE PROCEDURE [dbo].[tsp_DoAnonymizationCheck]
@DBName SYSNAME,
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableName SYSNAME
DECLARE @tables TABLE ([TABLE_NAME] SYSNAME)
DECLARE @columns TABLE (COLUMN_NAME SYSNAME)
INSERT INTO @tables EXEC sp_executesql @moduleTablesExpression
DECLARE @ignoreColumns TABLE (tableName SYSNAME, columnName SYSNAME)
INSERT INTO @ignoreColumns EXEC [dbo].[tsp_GetAnonymizationIngoreColumns]
DECLARE moduleTablesDB CURSOR LOCAL
FOR SELECT * FROM @tables
OPEN moduleTablesDB
WHILE 1 = 1
BEGIN
FETCH NEXT FROM moduleTablesDB INTO @tableName
IF @@FETCH_STATUS = -1 BREAK
IF @@FETCH_STATUS = -2 CONTINUE
IF @tableName = 'CaseStatus' CONTINUE
IF @tableName = 'SysProcessElementLog' CONTINUE
PRINT ('Checking ' + @tableName)
DECLARE @getUnananymazedDataCountExpression NVARCHAR(MAX)
DECLARE @countUnananymazedData INT = 0
DECLARE @displayColumnNameExpression NVARCHAR(MAX),
@defaultColumnNameExpression NVARCHAR(MAX),
@templateColumnName NVARCHAR(MAX),
@primaryColumnNameExpression NVARCHAR(MAX)
DECLARE @errorMessage NVARCHAR(MAX),
@moduleCaptionExpression NVARCHAR(MAX),
@moduleName NVARCHAR(MAX)
SET @primaryColumnNameExpression = 'SELECT kcu.[COLUMN_NAME]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [kcu].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE kcu.[TABLE_NAME] = ''' + @tableName +
''' AND kcu.[CONSTRAINT_NAME] LIKE ''PK%'''
INSERT INTO @columns EXEC sp_executesql @primaryColumnNameExpression
DECLARE @keyName NVARCHAR(MAX)
SET @keyName = (SELECT TOP 1 [COLUMN_NAME] FROM @columns)
DELETE FROM @columns
IF @keyName IS NULL
BEGIN
PRINT @tableName + N' пропускаем... KeyName = ' + ISNULL(@keyName, 'NULL')
CONTINUE
END
SET @displayColumnNameExpression = 'SELECT [ic].[COLUMN_NAME]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
WHERE [DATA_TYPE] IN (N''NVARCHAR'')
AND [ic].[TABLE_NAME] = ''' + @tableName + '''
AND [ic].[COLUMN_NAME] IN (''Name'', ''Title'', ''Number'', ''Type'')'
DELETE FROM @columns
INSERT INTO @columns EXEC sp_executesql @displayColumnNameExpression
IF EXISTS (SELECT * FROM @columns)
BEGIN
SELECT @templateColumnName = [COLUMN_NAME] FROM @columns
IF EXISTS(SELECT NULL FROM @ignoreColumns WHERE tableName = @tableName AND columnName = @templateColumnName)
CONTINUE
SET @getUnananymazedDataCountExpression = 'SELECT @countOUT = COUNT(*)
FROM [' + @DBName + '].[dbo].[' + @tableName + '] t1
WHERE NOT [' + @templateColumnName + '] LIKE ''' + @templateColumnName +' %''
AND [' + @templateColumnName + '] IS NOT NULL
AND [' + @templateColumnName + '] <> '''''
SELECT @getUnananymazedDataCountExpression = @getUnananymazedDataCountExpression +
CASE
WHEN @tableName='Contact' AND @templateColumnName='Name'
THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [t1].[Id])'
WHEN @tableName='Account' AND @templateColumnName='Name'
THEN ' AND (Name != ''Наша компания'' OR [t1].[Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')'
ELSE ''
END
EXEC sp_executesql @getUnananymazedDataCountExpression, N'@countOUT INT OUTPUT', @countOUT = @countUnananymazedData OUTPUT
IF @countUnananymazedData > 0
BEGIN
SET @moduleCaptionExpression = @errorModuleCaptionExpression + '''' + @tableName + ''''
EXEC sp_executesql @moduleCaptionExpression, N'@outModuleName NVARCHAR(MAX) OUTPUT', @outModuleName = @moduleName OUTPUT
SET @errorMessage = N'Данные раздела ' + @moduleName + ' не обезличенны!'
print @templateColumnName
RAISERROR (@errorMessage, 16, 1)
RETURN
END
DELETE FROM @columns
CONTINUE
END
SET @defaultColumnNameExpression = 'SELECT Top 1 [ic].[COLUMN_NAME]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
WHERE [DATA_TYPE] IN (N''NVARCHAR'')
AND [ic].[TABLE_NAME] = ''' + @tableName + ''''
DELETE FROM @columns
INSERT INTO @columns EXEC sp_executesql @defaultColumnNameExpression
IF EXISTS (SELECT * FROM @columns)
BEGIN
SELECT @templateColumnName = [COLUMN_NAME] FROM @columns
SET @getUnananymazedDataCountExpression = 'SELECT @countOUT = COUNT(*)
FROM [' + @DBName + '].[dbo].[' + @tableName + '] t1
WHERE NOT [' + @templateColumnName + '] LIKE ''' + @templateColumnName +' %''
AND [' + @templateColumnName + '] IS NOT NULL
AND [' + @templateColumnName + '] <> '''''
SELECT @getUnananymazedDataCountExpression = @getUnananymazedDataCountExpression +
CASE
WHEN @tableName='Contact' and @templateColumnName='Name'
THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [t1].[Id])'
WHEN @tableName='Account' and @templateColumnName='Name'
THEN ' AND (Name != N''Наша компания'' OR [t1].[Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')'
ELSE ''
END
EXEC sp_executesql @getUnananymazedDataCountExpression, N'@countOUT INT OUTPUT', @countOUT = @countUnananymazedData OUTPUT
IF @countUnananymazedData > 0
BEGIN
SET @moduleCaptionExpression = @errorModuleCaptionExpression + '''' + @tableName + ''''
EXEC sp_executesql @moduleCaptionExpression, N'@outModuleName NVARCHAR(MAX) OUTPUT', @outModuleName = @moduleName OUTPUT
SET @errorMessage = N'Данные раздела ' + @moduleName + ' не обезличенны!'
print @templateColumnName
RAISERROR (@errorMessage, 16, 1)
RETURN
END
END
DELETE FROM @columns
END
CLOSE moduleTablesDB
DEALLOCATE moduleTablesDB
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_DoDataAnonymization]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_DoDataAnonymization]
END
GO
CREATE PROCEDURE [dbo].[tsp_DoDataAnonymization]
@DBName SYSNAME,
@tablesExpression NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@tableName SYSNAME,
@columnName SYSNAME,
@keyName SYSNAME,
@expression NVARCHAR(MAX),
@type NVARCHAR(128)
DECLARE @tables TABLE ([TABLE_NAME] SYSNAME, [COLUMN_NAME] SYSNAME, [DATA_TYPE] SYSNAME)
DECLARE @columns TABLE (COLUMN_NAME SYSNAME)
DECLARE @updateChunkRowCount INT = 1000
PRINT ('Data Anonymization Tables Expression:')
PRINT (@tablesExpression)
INSERT INTO @tables EXEC sp_executesql @tablesExpression
DECLARE @ignoreColumns TABLE (tableName SYSNAME, columnName SYSNAME)
INSERT INTO @ignoreColumns EXEC [dbo].[tsp_GetAnonymizationIngoreColumns]
DECLARE @ignoreColumnsExpression NVARCHAR(MAX)
SET @ignoreColumnsExpression =
'SELECT [ic].TABLE_NAME, COLUMN_NAME
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [INFORMATION_SCHEMA].[TABLES] [it] ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE COLUMNPROPERTY(object_id([ic].TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
OR COLUMNPROPERTY(object_id([it].TABLE_NAME), COLUMN_NAME, ''IsComputed'') = 1'
INSERT INTO @ignoreColumns EXEC sp_executesql @ignoreColumnsExpression
DECLARE allDB CURSOR LOCAL
FOR SELECT * FROM @tables
OPEN allDB
WHILE 1 = 1
BEGIN
FETCH NEXT FROM allDB INTO @tableName, @columnName, @type
IF @@FETCH_STATUS = -1 BREAK
IF @@FETCH_STATUS = -2 CONTINUE
IF EXISTS(SELECT NULL FROM @ignoreColumns WHERE tableName = @tableName AND columnName = @columnName)
CONTINUE
DECLARE @columnNameExpression NVARCHAR(MAX)
SET @columnNameExpression = 'SELECT kcu.[COLUMN_NAME]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [kcu].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE kcu.[TABLE_NAME] = ''' + @tableName +
''' AND kcu.[CONSTRAINT_NAME] LIKE ''PK%'''
INSERT INTO @columns EXEC sp_executesql @columnNameExpression
SET @keyName = (SELECT TOP 1 [COLUMN_NAME] FROM @columns)
DELETE FROM @columns
IF @keyName IS NULL
BEGIN
PRINT @tableName + N' пропускаем... KeyName = ' + ISNULL(@keyName, 'NULL')
CONTINUE
END
SET @expression = N'ALTER TABLE [' + @DBName + N'].[dbo].[' + @tableName + N'] DISABLE TRIGGER ALL '
EXEC(@expression)
IF (@tableName = 'MailboxSynchronizationSettings' AND @columnName = 'UserPassword')
SET @expression = N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = ''p0mzIa0Nfdo='''
ELSE IF (@tableName = 'SysMsgUserSettings' AND @columnName = 'ConnectionParams')
SET @expression = N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] =
REPLACE([' + @columnName + N'], ''"disableCallCentre":false'', ''"disableCallCentre":true'')'
ELSE IF (@tableName = 'ActivityFile' AND @columnName = 'Data')
SET @expression =
N'WHILE (1 = 1)
BEGIN
UPDATE TOP (' + Convert(nvarchar(10), @updateChunkRowCount) + N') [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = 0x
WHERE [' + @columnName + N'] IS NOT NULL AND [' + @columnName + N'] <> 0x
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
END'
ELSE IF (@type = N'nvarchar')
SET @expression =
N'UPDATE [CurrentTable] SET [' + @columnName + N'] = ''' + @columnName + N''' + '' '' + [rownum]
FROM (SELECT CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT)) AS rownum, [' + @columnName + N']
FROM [' + @DBName + N'].[dbo].[' + @tableName + N']
WHERE [' + @columnName + N'] IS NOT NULL AND LEN([' + @columnName + N']) > 0 ' +
CASE
WHEN @tableName='Contact' and @columnName='Name'
THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [' + @tableName + N'].[Id])'
WHEN @tableName='Account' and @columnName='Name'
THEN ' AND (Name != N''Наша компания'' OR Name != N''Our company'' OR [Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')'
ELSE ''
END +
') AS [CurrentTable]'
ELSE IF (@type = 'int')
SET @expression = N'WITH TableWithRowNumber AS(
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as RowNumber,
[' + @columnName + N']
FROM [' + @DBName + N'].[dbo].[' + @tableName + N']
WHERE [' + @columnName + N'] IS NOT NULL
)
UPDATE TableWithRowNumber SET [' + @columnName + N'] = RowNumber'
ELSE
SET @expression =
N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = ' +
CASE
WHEN @type = N'varbinary'
THEN N'0x'
WHEN @type = N'binary'
THEN N'0x'
WHEN @type = N'datetime' OR @type = N'datetime2' OR @type = N'date'
THEN N'CAST(DATEADD(dd, -(SELECT CEILING(150*RAND())), GETDATE()) AS ' + @type + ')'
ELSE N'1'
END +
N' WHERE [' + @columnName + N'] IS NOT NULL'
PRINT ('Updating ' + @tableName + ' column ' + @columnName)
EXEC (@expression)
SET @expression = N'ALTER TABLE [' + @DBName + N'].[dbo].[' + @tableName + N'] ENABLE TRIGGER ALL '
EXEC(@expression)
END
CLOSE allDB
DEALLOCATE allDB
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData700]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData700]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData700]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @tablesExpression NVARCHAR(MAX)
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[SysLookup] [l]
JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
ON [l].[SysEntitySchemaUId] = [s].[UId]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
WHERE [m].[SysModuleEntityId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
)
AND [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT DISTINCT([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
DECLARE
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
SET @moduleTablesExpression = '
SELECT Distinct([it].[TABLE_NAME])
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
WHERE
[it].[TABLE_TYPE] = ''BASE TABLE''
AND
(
(
NOT [it].[TABLE_NAME] LIKE N''Sys%''
AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
AND [it].[TABLE_NAME] IN (
SELECT distinct([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [it].[TABLE_NAME] LIKE N''Sys%Log''
)
ORDER BY [it].[TABLE_NAME]'
SET @errorModuleCaptionExpression = '
SELECT Top 1 @outModuleName = [m].[Caption]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[Name] = '
EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData710]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData710]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData710]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @tablesExpression NVARCHAR(MAX)
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[SysLookup] [l]
JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
ON [l].[SysEntitySchemaUId] = [s].[UId]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
WHERE [m].[SysModuleEntityId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
)
AND [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT DISTINCT([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
DECLARE
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
SET @moduleTablesExpression = '
SELECT Distinct([it].[TABLE_NAME])
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
WHERE
[it].[TABLE_TYPE] = ''BASE TABLE''
AND
(
(
NOT [it].[TABLE_NAME] LIKE N''Sys%''
AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
AND [it].[TABLE_NAME] IN (
SELECT distinct([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [it].[TABLE_NAME] LIKE N''Sys%Log''
)
ORDER BY [it].[TABLE_NAME]'
SET @errorModuleCaptionExpression = '
SELECT Top 1 @outModuleName = [m].[Caption]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[Name] = '
EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData720]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData720]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData720]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @tablesExpression NVARCHAR(MAX)
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[SysLookup] [l]
JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
ON [l].[SysEntitySchemaUId] = [s].[UId]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
WHERE [m].[SysModuleEntityId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
)
AND [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT ('First Data Anonymization Started')
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT DISTINCT([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT ('Second Data Anonymization Started')
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
DECLARE
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
SET @moduleTablesExpression = '
SELECT Distinct([it].[TABLE_NAME])
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
WHERE
[it].[TABLE_TYPE] = ''BASE TABLE''
AND
(
(
NOT [it].[TABLE_NAME] LIKE N''Sys%''
AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
AND [it].[TABLE_NAME] IN (
SELECT distinct([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [it].[TABLE_NAME] LIKE N''Sys%Log''
)
ORDER BY [it].[TABLE_NAME]'
SET @errorModuleCaptionExpression = '
SELECT Top 1 @outModuleName = [m].[Caption]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[Name] = '
PRINT ('Anonymization Check Started')
EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData721]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData721]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData721]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @tablesExpression NVARCHAR(MAX)
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[SysLookup] [l]
JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
ON [l].[SysEntitySchemaUId] = [s].[UId]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
WHERE [m].[SysModuleEntityId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
)
AND [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT DISTINCT([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
)
)
OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
DECLARE
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
SET @moduleTablesExpression = '
SELECT Distinct([it].[TABLE_NAME])
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
WHERE
[it].[TABLE_TYPE] = ''BASE TABLE''
AND
(
(
NOT [it].[TABLE_NAME] LIKE N''Sys%''
AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
AND [it].[TABLE_NAME] IN (
SELECT distinct([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR [it].[TABLE_NAME] LIKE N''Sys%Log''
)
ORDER BY [it].[TABLE_NAME]'
SET @errorModuleCaptionExpression = '
SELECT Top 1 @outModuleName = [m].[Caption]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[Name] = '
EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData730]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData730]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData730]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @clearQrtzTriggerExpression NVARCHAR(MAX)
SET @clearQrtzTriggerExpression = 'DELETE FROM [' + @DBName + '].[dbo].[QRTZ_TRIGGERS]'
EXEC sp_executesql @clearQrtzTriggerExpression
DECLARE @sysAdminUnitExpression NVARCHAR(MAX)
DECLARE @supervisorId NVARCHAR(50)
SET @supervisorId = '7F3B869F-34F3-4F20-AB4D-7480A5FDF647'
SET @sysAdminUnitExpression = 'UPDATE [' + @DBName + '].[dbo].[SysAdminUnit] SET Name = ''Supervisor_Current'' WHERE Name = ''Supervisor'''
EXEC sp_executesql @sysAdminUnitExpression
SET @sysAdminUnitExpression = 'UPDATE [' + @DBName + '].[dbo].[SysAdminUnit] SET Name = ''Supervisor'', UserPassword = ''eF4f7sfUgBuNFei7bk+3djw/zXkaB1ODf3huR0RJDD4='', Active = 1 WHERE Id = ''' + @supervisorId + ''''
EXEC sp_executesql @sysAdminUnitExpression
DECLARE @sysSettingsValuesExpression NVARCHAR(MAX)
DECLARE @sysSettingsCodes NVARCHAR(MAX)
SET @sysSettingsCodes = '''GoogleTagManagerScript'',''UseGoogleTagManager'''
SET @sysSettingsValuesExpression = 'UPDATE [' + @DBName + '].[dbo].[SysSettingsValue] SET TextValue = '''', IntegerValue=0, FloatValue=0.00, BooleanValue=0, DateTimeValue=NULL, GuidValue=NULL, BinaryValue=NULL WHERE SysSettingsId IN (SELECT Id FROM [' + @DBName + '].[dbo].[SysSettings] WHERE Code IN('+ @sysSettingsCodes +'))'
EXEC sp_executesql @sysSettingsValuesExpression
DECLARE @tablesExpression NVARCHAR(MAX)
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND [ic].[COLUMN_NAME] NOT IN (''CreatedOn'',''ModifiedOn'')
AND NOT [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[SysLookup] [l]
JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
ON [l].[SysEntitySchemaUId] = [s].[UId]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
WHERE [m].[SysModuleEntityId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
)
AND [ic].[TABLE_NAME] IN (
SELECT [s].[Name]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
AND NOT EXISTS(
SELECT 1
FROM [' + @DBName + '].sys.computed_columns
WHERE object_id = OBJECT_ID([ic].[TABLE_NAME]) AND name = [ic].[COLUMN_NAME]
)
)
OR ([ic].[TABLE_NAME] LIKE N''Sys%Log'' AND [ic].[TABLE_NAME] NOT IN (''SysProcessLog'', ''SysProcessElementLog'', ''SysSqlLog''))
OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
)
AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'', ''ProcessListeners'')
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
AND
(
(
NOT [ic].[TABLE_NAME] LIKE N''Sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
AND NOT [ic].[TABLE_NAME] IN (
SELECT DISTINCT([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
)
)
)
ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
PRINT (@tablesExpression)
EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
DECLARE
@moduleTablesExpression NVARCHAR(MAX),
@errorModuleCaptionExpression NVARCHAR(MAX)
SET @moduleTablesExpression = '
SELECT Distinct([it].[TABLE_NAME])
FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
WHERE
[it].[TABLE_TYPE] = ''BASE TABLE''
AND
(
(
NOT [it].[TABLE_NAME] LIKE N''Sys%''
AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
AND [it].[TABLE_NAME] IN (
SELECT distinct([s].[Name])
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[ManagerName] = ''EntitySchemaManager''
)
)
OR ([it].[TABLE_NAME] LIKE N''Sys%Log'' AND [it].[TABLE_NAME] NOT IN (''SysProcessLog'', ''SysProcessElementLog'', ''SysSqlLog''))
)
ORDER BY [it].[TABLE_NAME]'
SET @errorModuleCaptionExpression = '
SELECT Top 1 @outModuleName = [m].[Caption]
FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
ON [sme].[SysEntitySchemaUId] = [s].UId
JOIN [' + @DBName + '].[dbo].[SysModule] [m]
ON [m].SysModuleEntityId = [sme].[Id]
WHERE [s].[Name] = '
EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData]') IS NULL
BEGIN
DROP PROCEDURE [dbo].[tsp_AnonymizeAllData]
END
GO
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData]
@DBName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
BEGIN
RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
RETURN
END
IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
BEGIN
RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
RETURN
END
DECLARE @configurationVersion NVARCHAR(MAX)
DECLARE @sqlSolution NVARCHAR(MAX)
SET @sqlSolution = N'SELECT TOP 1 @nameOUT = [TextValue]
FROM [' + @DBName + '].[dbo].[SysSettingsValue]
WHERE [SysSettingsId] IN (SELECT [Id]
FROM [' + @DBName + '].[dbo].[SysSettings]
WHERE [Code] LIKE ''ConfigurationVersion'')
ORDER BY [Position]'
EXEC sp_executesql @sqlSolution, N'@nameOUT NVARCHAR(10) OUTPUT', @nameOUT = @configurationVersion OUTPUT
IF (@configurationVersion = '0.0.0' OR @configurationVersion = '' OR @configurationVersion = NULL)
BEGIN
RAISERROR (N'Версия конфигурации невалидна!', 16, 1)
RETURN
END
ELSE
BEGIN
IF (@configurationVersion = '7.0.0')
BEGIN
EXEC tsp_AnonymizeAllData700 @DBName
RETURN
END
IF (@configurationVersion = '7.1.0')
BEGIN
EXEC tsp_AnonymizeAllData710 @DBName
RETURN
END
IF (@configurationVersion = '7.2.0')
BEGIN
EXEC tsp_AnonymizeAllData720 @DBName
RETURN
END
IF (@configurationVersion = '7.2.1')
BEGIN
EXEC tsp_AnonymizeAllData721 @DBName
RETURN
END
IF (@configurationVersion = '7.2.2')
BEGIN
EXEC tsp_AnonymizeAllData720 @DBName
RETURN
END
IF (@configurationVersion = '7.3.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.4.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.4.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.5.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.6.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.7.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.8.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.8.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.8.2')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.8.3')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.8.4')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.9.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.9.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.9.2')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.10.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.10.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.10.2')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.10.3')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.11.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.11.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.11.2')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.11.3')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.12.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.12.1')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.12.2')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.12.3')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.12.4')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
IF (@configurationVersion = '7.13.0')
BEGIN
EXEC tsp_AnonymizeAllData730 @DBName
RETURN
END
DECLARE @errorMessageUnknownConfigVersion NVARCHAR(MAX)
SET @errorMessageUnknownConfigVersion = N'Версия конфигурации ' + @configurationVersion + N' не поддерживается!'
RAISERROR (@errorMessageUnknownConfigVersion, 16, 1)
END
END
GO
Инструкция:
Для анонимизации базы, необходимо сделать копию базы, назвать ее с приставкой «anonymous», к примеру, mybse_anonymous, выполнить на ней скрипт.
После выполнения скрипта, в базе появится хранимая процедура «tsp_AnonymizeAllData», с единственным параметром «DBName». Ее необходимо выполнить, заполнив этот параметр, один в один с именем этой базы, с которой вы и запускаете хранимую процедуру (к примеру: mybse_anonymous).
После того как отработает хранимая процедура анонимизации базы, в базе (в нашем примере: mybse_anonymous) не останется важных данных.
Для Oracle
/*
** Project: BPMonline
** DBMS : Oracle
** Type : Stored Procedure
** Name : tsp_AnonymizeAllData
*/
DECLARE
existsObject number;
BEGIN
SELECT COUNT(1) INTO existsObject FROM user_objects WHERE object_name = 'TmpAnonimizationData' AND object_type = 'TABLE';
IF existsObject > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE "TmpAnonimizationData"';
END IF;
END;
/
CREATE GLOBAL TEMPORARY TABLE "TmpAnonimizationData" (
TABLE_NAME VARCHAR2(100),
COLUMN_NAME VARCHAR2(100),
DATA_TYPE VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;
/
CREATE OR REPLACE PROCEDURE tsp_DoDataAnonymization (schemaName IN varchar2) AS
BEGIN
DELETE FROM "TmpAnonimizationData" WHERE
(TABLE_NAME = 'Activity' AND COLUMN_NAME = 'AllowedResult') OR
(TABLE_NAME = 'ActivityCategory' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ActivityParticipantRole' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ActivityPriority' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ActivityStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ActivityType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'AdditionalParam' AND COLUMN_NAME = 'ColumnCaption') OR
(TABLE_NAME = 'Amenity' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'AmenitySubtype' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'BulkEmailCategory' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'BulkEmailLaunchOption' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'CallDirection' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Campaign' AND COLUMN_NAME = 'SchemaData') OR
(TABLE_NAME = 'CampaignFilter' AND COLUMN_NAME = 'SearchData') OR
(TABLE_NAME = 'CampaignStepType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'CashflowType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'CashflowType' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'Color') OR
(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'ButtonCaption') OR
(TABLE_NAME = 'Category' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ChartAggregationType' AND COLUMN_NAME = 'AggregationTypeCode') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ChartEntityColumn') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ChartSeriesKind') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'Filter') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'GroupByField') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'GroupByTypeDateTimeId') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ModuleObjAccessField') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'OrderByChartPropertyColumn') OR
(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'OrderDirection') OR
(TABLE_NAME = 'Command' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Communication' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ConfigItemCategory' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ConfigItemStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ConfigItemType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ContentBlock' AND COLUMN_NAME = 'Config') OR
(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'ContextHelpId') OR
(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'LMSUrl') OR
(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'ProductEdition') OR
(TABLE_NAME = 'DataType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Default') OR
(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Handler') OR
(TABLE_NAME = 'DependencyCategory' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DependencyType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DependencyType' AND COLUMN_NAME = 'ReverseTypeName') OR
(TABLE_NAME = 'Dimension' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Dimension' AND COLUMN_NAME = 'Path') OR
(TABLE_NAME = 'DisplayDateFormat' AND COLUMN_NAME = 'Format') OR
(TABLE_NAME = 'DocumentState' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DocumentType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'EmailSendStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'ColumnPath') OR
(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'Position') OR
(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'EmailType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'EntitySchemaRecRightOperation' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'EntitySchemaRecRightOperation' AND COLUMN_NAME = 'Value') OR
(TABLE_NAME = 'ESNNotificationType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ESNNotificationType' AND COLUMN_NAME = 'Action') OR
(TABLE_NAME = 'ExternalSource' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'FolderType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Gender' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'GroupByTypeDateTime' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'GroupByTypeDateTime' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'AmenityType') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterClass') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterDisplayName') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterName') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterProperty') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'PropertyPath') OR
(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'SelectedParameter') OR
(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'AmenityValue') OR
(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'ParameterDisplayValue') OR
(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'ParameterValue') OR
(TABLE_NAME = 'IntegrationSettings' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'CodePage') OR
(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'VideoUrl') OR
(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'VideoCaption') OR
(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'AcademyUrl') OR
(TABLE_NAME = 'KnowledgeBaseType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LDAPAuthType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LDAPAuthType' AND COLUMN_NAME = 'Value') OR
(TABLE_NAME = 'LeadDisqualifyReason' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadMedium' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadRegisterMethod' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadType' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'LeadType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadStage' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadTypeStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LendingState' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ListingType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Listing' AND COLUMN_NAME = 'Latitude') OR
(TABLE_NAME = 'Listing' AND COLUMN_NAME = 'Longitude') OR
(TABLE_NAME = 'Lookup' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LookupFolder' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Macros' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Address') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Port') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'UseSSL') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'UseSSLforSending') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'SMTPServerAddress') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'SMTPPort') OR
(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'ExchangeEmailAddress') OR
(TABLE_NAME = 'MailServerType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MailSyncPeriod' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MainParam' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MessageListener' AND COLUMN_NAME = 'ClassName') OR
(TABLE_NAME = 'MessagePublisher' AND COLUMN_NAME = 'ClassName') OR
(TABLE_NAME = 'MessageNotifier' AND COLUMN_NAME = 'ClassName') OR
(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'AppClassName') OR
(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'ClientId') OR
(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'ClientClassName') OR
(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'SecretKey') OR
(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'MetaData') OR
(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'TrainingSetQuery') OR
(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'BatchPredictionQuery') OR
(TABLE_NAME = 'MLModelState' AND COLUMN_NAME = 'Code') OR
(TABLE_NAME = 'MLPrediction' AND COLUMN_NAME = 'Value') OR
(TABLE_NAME = 'MLPrediction' AND COLUMN_NAME = 'Significance') OR
(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'ServiceUrl') OR
(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'TrainingEndpoint') OR
(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'PredictionEndpoint') OR
(TABLE_NAME = 'MLTrainSession' AND COLUMN_NAME = 'Error') OR
(TABLE_NAME = 'NotificationProvider' AND COLUMN_NAME = 'ClassName') OR
(TABLE_NAME = 'NotificationProvider' AND COLUMN_NAME = 'Type') OR
(TABLE_NAME = 'ParamMatchingType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Period' AND COLUMN_NAME = 'DueDate') OR
(TABLE_NAME = 'Period' AND COLUMN_NAME = 'StartDate') OR
(TABLE_NAME = 'PeriodType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ProblemPriority' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ProblemStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ProblemType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ProcessSchemaType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ProjectEntryType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Property' AND COLUMN_NAME = 'Latitude') OR
(TABLE_NAME = 'Property' AND COLUMN_NAME = 'Longitude') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageNumber') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageOrder') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageInnerOrder') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'IsFinal') OR
(TABLE_NAME = 'QuickAddMenuItem' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ReleasePriority' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ReleaseStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ReleaseType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Value') OR
(TABLE_NAME = 'RequestType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'RoleInServiceTeam' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ServiceCategory' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ServiceObjectType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ServiceStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Status' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'TimeUnit' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'TimeZone' AND COLUMN_NAME = 'Offset') OR
(TABLE_NAME = 'TimeZone' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DayOfWeek' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'DayOfWeek' AND COLUMN_NAME = 'Number') OR
(TABLE_NAME = 'LMColumnTemplate' AND COLUMN_NAME = 'ColumnName') OR
(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'ConditionData') OR
(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'ChangedColumns') OR
(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'MessageTemplate') OR
(TABLE_NAME = 'ListenerByNotifier' AND COLUMN_NAME = 'NotifierConnectionColumn') OR
(TABLE_NAME = 'DisplayDateFormat' AND COLUMN_NAME = 'Format') OR
(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInMinutes') OR
(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInHours') OR
(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInDays') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'Type') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'Caption') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'ErrorDescription') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInMinutes') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInHours') OR
(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInDays') OR
(TABLE_NAME = 'FileType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'BulkEmailStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'BulkEmailStatus' AND COLUMN_NAME = 'IsActive') OR
(TABLE_NAME = 'LeadSource' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'LeadSource' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'Country' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Country' AND COLUMN_NAME = 'Image') OR
(TABLE_NAME = 'Country' AND COLUMN_NAME = 'BilingInfo') OR
(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'IsInitial') OR
(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'IsFinal') OR
(TABLE_NAME = 'City' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Region' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'QueuePriority' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'QueuePriority' AND COLUMN_NAME = 'Value') OR
(TABLE_NAME = 'Calendar' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ContractState' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'ContractState' AND COLUMN_NAME = 'Position') OR
(TABLE_NAME = 'Tax' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Tax' AND COLUMN_NAME = 'Percent') OR
(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'ForContact') OR
(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'ForAccount') OR
(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForContactContact') OR
(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForAccountContact') OR
(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForContactAccount') OR
(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForAccountAccount') OR
(TABLE_NAME = 'SpecificationType' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'SpecificationType' AND COLUMN_NAME = 'Description') OR
(TABLE_NAME = 'Unit' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'Unit' AND COLUMN_NAME = 'ShortName') OR
(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'Color') OR
(TABLE_NAME = 'OpportunityStage' AND COLUMN_NAME = 'Color') OR
(TABLE_NAME = 'QueueObjectColumn' AND COLUMN_NAME = 'Name') OR
(TABLE_NAME = 'QueueObjectColumn' AND COLUMN_NAME = 'Caption') OR
(TABLE_NAME = 'DuplicatesHistory' AND COLUMN_NAME = 'SchemaTableName');
UPDATE "SysAdminUnit"
SET "Name" = 'Supervisor_Current'
WHERE "Name" = 'Supervisor';
UPDATE "SysAdminUnit"
SET "Name" = 'Supervisor',
"UserPassword" = 'jqtZ3uiTDazZFoZRF32m0OQpxrt4V45R7yYXgd1jl6xddsoOOIwja',
"Active" = '1'
WHERE "Id" = '{7F3B869F-34F3-4F20-AB4D-7480A5FDF647}';
UPDATE "SysAdminUnit"
SET "Name" = 'Name '|| Rownum
WHERE "SysAdminUnitTypeValue" < 4;
--DBMS_OUTPUT.ENABLE(200000);
DECLARE
CURSOR cur IS
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM "TmpAnonimizationData";
expression VARCHAR2(500);
BEGIN
FOR cur_rec in cur
LOOP
BEGIN
execute immediate 'ALTER TABLE "' || cur_rec.TABLE_NAME || '" DISABLE ALL TRIGGERS';
expression := 'UPDATE "' || cur_rec.TABLE_NAME || '" ';
expression := expression || ' SET "' || cur_rec.COLUMN_NAME || '" = ';
IF ((cur_rec.TABLE_NAME = 'MailboxSynchronizationSettings') AND (cur_rec.COLUMN_NAME = 'UserPassword')) THEN
expression := expression || '''p0mzIa0Nfdo='' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
ELSIF ((cur_rec.TABLE_NAME = 'SysMsgUserSettings') AND (cur_rec.COLUMN_NAME = 'ConnectionParams')) THEN
expression := expression || 'REPLACE("ConnectionParams", ''"disableCallCentre":false'', ''"disableCallCentre":true'')';
ELSIF ((cur_rec.DATA_TYPE = 'NVARCHAR2') OR (cur_rec.DATA_TYPE = 'VARCHAR2')) THEN
expression := expression || '''Name'' || SUBSTR("Id", 2, 8) WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
expression := expression || ' AND NOT (SUBSTR("' || cur_rec.COLUMN_NAME ||'",1,1) =''{'' ';
expression := expression || ' AND LENGTH("' || cur_rec.COLUMN_NAME ||'") =38 ) ';
ELSIF ( cur_rec.DATA_TYPE = 'NCLOB') THEN
expression := expression || '''DataName'' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
ELSIF ((cur_rec.DATA_TYPE = 'DATE') OR (cur_rec.DATA_TYPE = 'TIMESTAMP')) THEN
expression := expression || '(SELECT sysdate + (SELECT CEIL(150*dbms_random.value(1,10)) from dual) from dual)' ;
expression := expression || ' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
ELSIF (cur_rec.DATA_TYPE = 'NUMBER') THEN
expression := expression || '0 WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
ELSE
CONTINUE;
END IF;
--dbms_output.put_line(expression);
execute immediate(expression);
execute immediate 'ALTER TABLE "' || cur_rec.TABLE_NAME || '" ENABLE ALL TRIGGERS';
COMMIT;
END;
END LOOP;
END;
END;
/
CREATE OR REPLACE PROCEDURE tsp_AnonymizeAllData AS
schemaName VARCHAR(100);
ex_custom EXCEPTION;
baseLookupSchemaUId VARCHAR2(38) := '{11AB4BCB-9B23-4B6D-9C86-520FAE925D75}';
baseCodeLookupSchemaUId VARCHAR2(38) := '{2681062B-DF59-4E52-89ED-F9B7DC909AB2}';
BEGIN
SELECT SYS_CONTEXT('userenv', 'current_schema') INTO schemaName from DUAL WHERE ROWNUM=1;
BEGIN
IF regexp_instr(schemaName,'_ANONYMOUS') = 0 THEN
raise_application_error(-20001, 'Attempt to anonymize the data from the working database!!');
END IF;
EXCEPTION
WHEN ex_custom THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
DBMS_OUTPUT.PUT_LINE(schemaName);
END;
DELETE FROM "TmpAnonimizationData";
INSERT INTO "TmpAnonimizationData" (TABLE_NAME,COLUMN_NAME,DATA_TYPE)
SELECT TAB_COLS.TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM "ALL_TAB_COLS" TAB_COLS
INNER JOIN "ALL_TABLES" TABLES ON TAB_COLS.TABLE_NAME=TABLES.TABLE_NAME AND TABLES.OWNER = schemaName
INNER JOIN (SELECT distinct "Name"
FROM "SysSchema"
WHERE "ManagerName" = 'EntitySchemaManager') SCHEMA ON SCHEMA."Name" = TABLES.TABLE_NAME
WHERE TAB_COLS.OWNER = schemaName
AND TABLES.TABLE_NAME NOT LIKE 'Vw%'
AND TAB_COLS.DATA_TYPE IN ('NUMBER', 'NVARCHAR2', 'VARCHAR2', 'DATE', 'TIMESTAMP', 'RAW', 'NCLOB')
AND TAB_COLS.COLUMN_ID IS NOT NULL
AND TAB_COLS.DATA_LENGTH <> 38
AND TAB_COLS.VIRTUAL_COLUMN <> 'YES'
AND (TABLES.TABLE_NAME NOT LIKE 'Sys%'
OR TABLES."TABLE_NAME" = 'SysMsgUserSettings')
AND TABLES.TABLE_NAME NOT LIKE 'QRTZ%'
AND TABLES.TABLE_NAME NOT IN ('OPPEmailInitialStatus')
AND NOT TABLES.TABLE_NAME IN (
SELECT "SysSchema"."Name"
FROM "SysLookup"
JOIN "SysSchema" ON "SysLookup"."SysEntitySchemaUId" = "SysSchema"."UId"
WHERE "SysSchema"."ManagerName" = 'EntitySchemaManager'
AND NOT EXISTS (
SELECT 1
FROM "SysModule"
INNER JOIN "SysModuleEntity" ON "SysModuleEntity"."Id" = "SysModule"."SysModuleEntityId"
WHERE "SysModuleEntity"."SysEntitySchemaUId" = "SysLookup"."SysEntitySchemaUId"))
AND "COLUMN_NAME" NOT IN('CreatedOn', 'ModifiedOn', 'Code','SchemaToSearchName', 'ContainerId', 'ModuleName', 'ProcessListeners');
DELETE FROM "TmpAnonimizationData"
WHERE COLUMN_NAME = 'Name'
AND EXISTS (SELECT 1 FROM "SysSchema"
WHERE "SysSchema"."Name" = TABLE_NAME
AND "ParentId" IN (
SELECT "Id" FROM "SysSchema"
WHERE "UId" IN(baseLookupSchemaUId,baseCodeLookupSchemaUId)));
tsp_DoDataAnonymization(schemaName);
END;
/
Инструкция:
Для работы со скриптом необходимо:
- Выполнить бэкап БД
- Выполнить импорт в новую схему с именем “имя старой схемы_ANONYMOUS”
- Выполнить скрипт tsp_AnonymizeAllData.sql создания процедур анонимизации БД.
- Запустить на выполнение процедуру
EXEC tsp_AnonymizeAllData
