gimli.bioss.sari.ac.uk/gimli.bioss.sari.ac.uk.zip/gimli.bioss.sari.ac.uk/pub/misc/McAfee8.7.Zip/ePOPolicyMigration.exe/CABFILE/1/VNA7.1ToVSE-S1.0_Policy_Upgrade_Script_For_ePO3.6.sql

Text File | 2008-06-26 | 15KB | 363 lines

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradeVNA71ToVSES10]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradeVNA71ToVSES10]
GO

CREATE PROCEDURE dbo.VSE_UpgradeVNA71ToVSES10
(
    @PolicySettingsID INT,
    @szPlatform NVARCHAR(50)
)
AS
BEGIN
    DECLARE @SectionName NVARCHAR(100)
    DECLARE @Value NVARCHAR(1024)

    SELECT @SectionName = 'NetAppGeneral'

    IF EXISTS(SELECT *
          FROM EPOPolicySettingValues
          WHERE EPOPolicySettingValues.PolicySettingsID = @PolicySettingsID
          AND SectionName = @SectionName)
    BEGIN
        EXEC EPOPolicy_GetPolicySettingValue @PolicySettingsID, @SectionName, 'szFiler_0', @Value OUTPUT
        IF @Value = '<Adding filers here enforces this filer list on the scanner-servers.>'
        BEGIN
            EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bOverwriteLocalFilerList', '0'
        END
        ELSE
        BEGIN
            EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bOverwriteLocalFilerList', '1'
        END

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bUseGroupedFilerAccount', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szFilerUsername', ''
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szFilerPassword', ''
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szFilerDomainName', ''

        SELECT @SectionName = 'NetAppDetection'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'PlugInEnabled', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'LocalExtensionMode', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szIncludeExts', ''
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szProgExts', ''


        SELECT @SectionName = 'NetAppExclusions'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bAppendExclusions', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwExclusionCount', '0'

        SELECT @SectionName = 'NetAppAdvanced'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwMacroHeuristicsLevel', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwProgramHeuristicsLevel', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'ScanArchives', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'ScanMime', '0'

        SELECT @SectionName = 'NetAppPerformance'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwMaxScanTime', '20'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwScanThreadCount', '8'

        SELECT @SectionName = 'NetAppAction'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'uAction', '5'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'uSecAction', '4'

        SELECT @SectionName = 'NetAppSpyware'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'ApplyNVP', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'uAction_Program', '5'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'uSecAction_Program', '4'

        SELECT @SectionName = 'NetAppReporting'

        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bLimitSize', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bLogScanEncryptFail', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bLogSettings', '0'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bLogSummary', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'bLogToFile', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'dwMaxLogSizeMB', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'LogFileFormat', '1'
        EXEC EPOPolicy_SetPolicySettingValue 0, @PolicySettingsID, @SectionName, 'szLogFileName', '%DEFLOGDIR%\OnAccessScanLog.txt'
    END
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradeEnforce]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradeEnforce]
GO

CREATE PROCEDURE dbo.VSE_UpgradeEnforce
(
    @PolicySettingsID INT,
    @PolicySettingsTypeID NVARCHAR(256),
    @NewFeatureTextID NVARCHAR(50)
)
AS
BEGIN
    DECLARE @PolicyObjectID INT
    SELECT @PolicyObjectID = EPOPolicyObjectToSettings.PolicyObjectID
    FROM EPOPolicyObjectToSettings
    WHERE EPOPolicyObjectToSettings.PolicySettingsID = @PolicySettingsID

    DECLARE @TypeTextID NVARCHAR(256)
    SELECT @TypeTextID = EPOPolicyTypes.TypeTextID
    FROM EPOPolicyTypes
    WHERE EPOPolicyTypes.TypeID = @PolicySettingsTypeID

    DECLARE @NewTypeID INT
    SELECT @NewTypeID = EPOPolicyTypes.TypeID
    FROM EPOPolicyTypes
    WHERE EPOPolicyTypes.TypeTextID = @TypeTextID
    AND EPOPolicyTypes.FeatureTextID = @NewFeatureTextID

    DECLARE @PolicyObjectName NVARCHAR(256)
    SELECT @PolicyObjectName = EPOPolicyObjects.Name
    FROM EPOPolicyObjects
    WHERE EPOPolicyObjects.PolicyObjectID = @PolicyObjectID

    DECLARE @NewPolicyObjectID INT
    SELECT @NewPolicyObjectID = EPOPolicyObjects.PolicyObjectID
    FROM EPOPolicyObjects
    WHERE EPOPolicyObjects.TypeID = @NewTypeID
    AND EPOPolicyObjects.Name = @PolicyObjectName

    DECLARE @SlotID INT
    SELECT @SlotID = 0
    SELECT @SlotID = EPOPolicyAssignment.SlotID
    FROM EPOPolicyTypes, EPOPolicyObjects, EPOPolicyAssignment
    WHERE EPOPolicyTypes.TypeID = @NewTypeID
    AND EPOPolicyObjects.TypeID = EPOPolicyTypes.TypeID
    AND EPOPolicyAssignment.PolicyObjectID = EPOPolicyObjects.PolicyObjectID

    IF (@SlotID > 0)
    BEGIN
        DECLARE @AssignmentCursor CURSOR;
        SET @AssignmentCursor = CURSOR FORWARD_ONLY READ_ONLY FOR
            SELECT EPOPolicyAssignment.NodeID, EPOPolicyAssignment.NodeType, EPOPolicyAssignment.ForceInheritance, EPOPolicyAssignment.Hidden, EPOPolicyAssignment.TheTimestamp, EPOPolicyAssignment.TheHiddenTimestamp
            FROM EPOPolicyAssignment
            WHERE EPOPolicyAssignment.PolicyObjectID = @PolicyObjectID;

        OPEN @AssignmentCursor;

        DECLARE @NodeID INT
        DECLARE @NodeType INT
        DECLARE @ForceInheritance INT
        DECLARE @Hidden INT
        DECLARE @TheTimestamp INT
        DECLARE @TheHiddenTimestamp INT
        DECLARE @NewAssignmentID INT

        FETCH NEXT FROM @AssignmentCursor INTO @NodeID, @NodeType, @ForceInheritance, @Hidden, @TheTimestamp, @TheHiddenTimestamp;
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE EPOPolicy_AssignPolicyToNode 0, @NewPolicyObjectID, @NodeID, @NodeType, @SlotID, @ForceInheritance, @Hidden, @NewAssignmentID

            FETCH NEXT FROM @AssignmentCursor INTO @NodeID, @NodeType, @ForceInheritance, @Hidden, @TheTimestamp, @TheHiddenTimestamp;
        END
    END

    CLOSE @AssignmentCursor;
    DEALLOCATE @AssignmentCursor;
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradePolicy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradePolicy]
GO

CREATE PROCEDURE dbo.VSE_UpgradePolicy
(
    @PolicySettingsID INT,
    @PolicySettingsName NVARCHAR(256),
    @PolicySettingsTypeID NVARCHAR(256),
    @ProductCode NVARCHAR(50),
    @NewFeatureTextID NVARCHAR(50)
)
AS
BEGIN

    DECLARE @TypeTextID NVARCHAR(256)
    SELECT @TypeTextID = EPOPolicyTypes.TypeTextID
    FROM EPOPolicyTypes
    WHERE EPOPolicyTypes.TypeID = @PolicySettingsTypeID;

    IF @TypeTextID = 'VSC700_NetApp_Policies'
    BEGIN
        IF NOT EXISTS (SELECT EPOPolicyObjects.Name
                   FROM EPOPolicyObjects, EPOPolicyTypes
                   WHERE EPOPolicyObjects.Name = @PolicySettingsName
                   AND EPOPolicyObjects.TypeID = EPOPolicyTypes.TypeID
                   AND EPOPolicyTypes.FeatureTextID = @NewFeatureTextID
                AND EPOPolicyTypes.TypeTextID = 'VSES1000_Netapp_Policies')
        BEGIN
            DECLARE @PolicyObjectID INT
            SELECT @PolicyObjectID = EPOPolicyObjectToSettings.PolicyObjectID
            FROM EPOPolicyObjectToSettings
            WHERE EPOPolicyObjectToSettings.PolicySettingsID = @PolicySettingsID

            DECLARE @UserID INT
            SELECT @UserID = 0
            SELECT @UserID = UserID
            FROM EPOPolicyObjectUserRoles
            WHERE EPOPolicyObjectUserRoles.PolicyObjectID = @PolicyObjectID
            AND EPOPolicyObjectUserRoles.ACFlags = 1

            DECLARE @NewTypeID INT
            SELECT @NewTypeID = 0
            SELECT @NewTypeID = EPOPolicyTypes.TypeID
            FROM EPOPolicyTypes
            WHERE EPOPolicyTypes.TypeTextID = 'VSES1000_Netapp_Policies'
            AND EPOPolicyTypes.FeatureTextID = @NewFeatureTextID
            IF (@NewTypeID > 0)
            BEGIN
                DECLARE @NewPolicyObjectID INT
                DECLARE @NewPolicySettingsName NVARCHAR(256)
                SELECT @NewPolicySettingsName = @PolicySettingsName + '(vse-s1.0)'
                EXECUTE EPOPolicy_CopyPolicyObject 0, @PolicyObjectID, @NewPolicySettingsName, @UserID, 1, @NewPolicyObjectID OUTPUT

                UPDATE EPOPolicyObjects
                SET TypeID = @NewTypeID
                WHERE PolicyObjectID = @NewPolicyObjectID

                UPDATE EPOPolicyObjects
                SET Name = @PolicySettingsName
                WHERE PolicyObjectID = @NewPolicyObjectID

                DECLARE @NewPolicySettingsID INT
                SELECT @NewPolicySettingsID = EPOPolicyObjectToSettings.PolicySettingsID
                FROM EPOPolicyObjectToSettings
                WHERE EPOPolicyObjectToSettings.PolicyObjectID = @NewPolicyObjectID

                UPDATE EPOPolicySettings
                SET TypeID = @NewTypeID
                WHERE PolicySettingsID = @NewPolicySettingsID

                IF @ProductCode = 'VSNETAPP7100'
                BEGIN
                    EXECUTE VSE_UpgradeVNA71ToVSES10 @NewPolicySettingsID, ''
                END

                DECLARE @SlotID INT
                SELECT @SlotID = 0
                SELECT @SlotID = EPOPolicyAssignment.SlotID
                FROM EPOPolicyTypes, EPOPolicyObjects, EPOPolicyAssignment
                WHERE EPOPolicyTypes.TypeID = @NewTypeID
                AND EPOPolicyObjects.TypeID = EPOPolicyTypes.TypeID
                AND EPOPolicyAssignment.PolicyObjectID = EPOPolicyObjects.PolicyObjectID

                IF (@SlotID > 0)
                BEGIN
                    DECLARE @AssignmentCursor CURSOR;
                    SET @AssignmentCursor = CURSOR FORWARD_ONLY READ_ONLY FOR
                        SELECT EPOPolicyAssignment.NodeID, EPOPolicyAssignment.NodeType, EPOPolicyAssignment.ForceInheritance, EPOPolicyAssignment.Hidden, EPOPolicyAssignment.TheTimestamp, EPOPolicyAssignment.TheHiddenTimestamp
                        FROM EPOPolicyAssignment
                        WHERE EPOPolicyAssignment.PolicyObjectID = @PolicyObjectID;

                    OPEN @AssignmentCursor;

                    DECLARE @NodeID INT
                    DECLARE @NodeType INT
                    DECLARE @ForceInheritance INT
                    DECLARE @Hidden INT
                    DECLARE @TheTimestamp INT
                    DECLARE @TheHiddenTimestamp INT
                    DECLARE @NewAssignmentID INT
                    FETCH NEXT FROM @AssignmentCursor INTO @NodeID, @NodeType, @ForceInheritance, @Hidden, @TheTimestamp, @TheHiddenTimestamp;
                    WHILE (@@FETCH_STATUS = 0)
                    BEGIN
                        EXECUTE EPOPolicy_AssignPolicyToNode 0, @NewPolicyObjectID, @NodeID, @NodeType, @SlotID, @ForceInheritance, @Hidden, @NewAssignmentID

                        FETCH NEXT FROM @AssignmentCursor INTO @NodeID, @NodeType, @ForceInheritance, @Hidden, @TheTimestamp, @TheHiddenTimestamp;
                    END

                    CLOSE @AssignmentCursor;
                    DEALLOCATE @AssignmentCursor;
                END
            END
        END
    END
END
GO

IF EXISTS (SELECT ePOVersion FROM ServerInfo WHERE ePOVersion > '3.5.0')
BEGIN
    DECLARE @ProductCode NVARCHAR(30)

    IF EXISTS (SELECT * FROM EPOPolicyTypes
        WHERE FeatureTextID = 'VSNETAPP7100')
    BEGIN
        SELECT @ProductCode = 'VSNETAPP7100'
    END

    IF @ProductCode <> ''
    BEGIN
        DECLARE @PolicyCursor CURSOR;
        SET @PolicyCursor = CURSOR FORWARD_ONLY READ_ONLY FOR
            SELECT EPOPolicySettings.PolicySettingsID, EPOPolicyObjects.Name, EPOPolicySettings.TypeID
            FROM EPOPolicyProductToTypes, EPOPolicySettings, EPOPolicyObjects, EPOPolicyObjectToSettings
            WHERE EPOPolicyProductToTypes.TypeID = EPOPolicySettings.TypeID
            AND EPOPolicyProductToTypes.TypeID = EPOPolicyObjects.TypeID
            AND EPOPolicyObjectToSettings.PolicySettingsID =  EPOPolicySettings.PolicySettingsID
            AND EPOPolicyObjectToSettings.PolicyObjectID = EPOPolicyObjects.PolicyObjectID
            AND EPOPolicyProductToTypes.ProductCode = @ProductCode
            AND EPOPolicyProductToTypes.EditURL != 'dummy.htm'
            AND EPOPolicyObjects.Name != 'McAfee Default';

        OPEN @PolicyCursor;

        DECLARE @PolicySettingsID INT
        DECLARE @PolicySettingsName NVARCHAR(256)
        DECLARE @PolicySettingsTypeID NVARCHAR(256)

        FETCH NEXT FROM @PolicyCursor INTO @PolicySettingsID, @PolicySettingsName, @PolicySettingsTypeID;
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE VSE_UpgradePolicy @PolicySettingsID, @PolicySettingsName, @PolicySettingsTypeID, @ProductCode, 'VSESTOMD1000'

            FETCH NEXT FROM @PolicyCursor INTO @PolicySettingsID, @PolicySettingsName, @PolicySettingsTypeID;
        END

        CLOSE @PolicyCursor;
        DEALLOCATE @PolicyCursor;


        DECLARE @EnforceCursor CURSOR;
        SET @EnforceCursor = CURSOR FORWARD_ONLY READ_ONLY FOR
            SELECT EPOPolicySettings.PolicySettingsID, EPOPolicySettings.TypeID
            FROM EPOPolicyProductToTypes, EPOPolicySettings, EPOPolicyObjects, EPOPolicyObjectToSettings
            WHERE EPOPolicyProductToTypes.TypeID = EPOPolicySettings.TypeID
            AND EPOPolicyProductToTypes.TypeID = EPOPolicyObjects.TypeID
            AND EPOPolicyObjectToSettings.PolicySettingsID =  EPOPolicySettings.PolicySettingsID
            AND EPOPolicyObjectToSettings.PolicyObjectID = EPOPolicyObjects.PolicyObjectID
            AND EPOPolicyProductToTypes.ProductCode = @ProductCode
            AND EPOPolicyProductToTypes.EditURL = 'dummy.htm'
            AND EPOPolicyObjects.Name != 'McAfee Default';

        OPEN @EnforceCursor;

        FETCH NEXT FROM @EnforceCursor INTO @PolicySettingsID, @PolicySettingsTypeID;
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE VSE_UpgradeEnforce @PolicySettingsID, @PolicySettingsTypeID, 'VSESTOMD1000'

            FETCH NEXT FROM @EnforceCursor INTO @PolicySettingsID, @PolicySettingsTypeID;
        END

        CLOSE @EnforceCursor;
        DEALLOCATE @EnforceCursor;
    END
END

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradePolicy]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradePolicy]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradeEnforce]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradeEnforce]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[VSE_UpgradeVNA71ToVSES10]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[VSE_UpgradeVNA71ToVSES10]
GO