bitwarden-server/util/Migrator/DbScripts/2024-11-25_00_AddUseRiskInsightsToOrganization.sql

    /* Introduce new column 'UseRiskInsights' not nullable with default of 0  */
    ALTER TABLE [dbo].[Organization] ADD [UseRiskInsights] bit NOT NULL CONSTRAINT [DF_Organization_UseRiskInsights] default (0)
    GO

    /* Add UseRiskInsights as a column to Organization_create */
    CREATE OR ALTER PROCEDURE [dbo].[Organization_Create]
        @Id UNIQUEIDENTIFIER OUTPUT,
        @Identifier NVARCHAR(50),
        @Name NVARCHAR(50),
        @BusinessName NVARCHAR(50),
        @BusinessAddress1 NVARCHAR(50),
        @BusinessAddress2 NVARCHAR(50),
        @BusinessAddress3 NVARCHAR(50),
        @BusinessCountry VARCHAR(2),
        @BusinessTaxNumber NVARCHAR(30),
        @BillingEmail NVARCHAR(256),
        @Plan NVARCHAR(50),
        @PlanType TINYINT,
        @Seats INT,
        @MaxCollections SMALLINT,
        @UsePolicies BIT,
        @UseSso BIT,
        @UseGroups BIT,
        @UseDirectory BIT,
        @UseEvents BIT,
        @UseTotp BIT,
        @Use2fa BIT,
        @UseApi BIT,
        @UseResetPassword BIT,
        @SelfHost BIT,
        @UsersGetPremium BIT,
        @Storage BIGINT,
        @MaxStorageGb SMALLINT,
        @Gateway TINYINT,
        @GatewayCustomerId VARCHAR(50),
        @GatewaySubscriptionId VARCHAR(50),
        @ReferenceData VARCHAR(MAX),
        @Enabled BIT,
        @LicenseKey VARCHAR(100),
        @PublicKey VARCHAR(MAX),
        @PrivateKey VARCHAR(MAX),
        @TwoFactorProviders NVARCHAR(MAX),
        @ExpirationDate DATETIME2(7),
        @CreationDate DATETIME2(7),
        @RevisionDate DATETIME2(7),
        @OwnersNotifiedOfAutoscaling DATETIME2(7),
        @MaxAutoscaleSeats INT,
        @UseKeyConnector BIT = 0,
        @UseScim BIT = 0,
        @UseCustomPermissions BIT = 0,
        @UseSecretsManager BIT = 0,
        @Status TINYINT = 0,
        @UsePasswordManager BIT = 1,
        @SmSeats INT = null,
        @SmServiceAccounts INT = null,
        @MaxAutoscaleSmSeats INT= null,
        @MaxAutoscaleSmServiceAccounts INT = null,
        @SecretsManagerBeta BIT = 0,
        @LimitCollectionCreationDeletion BIT = NULL, -- Deprecated https://bitwarden.atlassian.net/browse/PM-10863
        @LimitCollectionCreation BIT = NULL,
        @LimitCollectionDeletion BIT = NULL,
        @AllowAdminAccessToAllCollectionItems BIT = 0,
        @UseRiskInsights BIT = 0
    AS
    BEGIN
        SET NOCOUNT ON

        SET @LimitCollectionCreation = COALESCE(@LimitCollectionCreation, @LimitCollectionCreationDeletion, 0);
        SET @LimitCollectionDeletion = COALESCE(@LimitCollectionDeletion, @LimitCollectionCreationDeletion, 0);

        INSERT INTO [dbo].[Organization]
        (
            [Id],
            [Identifier],
            [Name],
            [BusinessName],
            [BusinessAddress1],
            [BusinessAddress2],
            [BusinessAddress3],
            [BusinessCountry],
            [BusinessTaxNumber],
            [BillingEmail],
            [Plan],
            [PlanType],
            [Seats],
            [MaxCollections],
            [UsePolicies],
            [UseSso],
            [UseGroups],
            [UseDirectory],
            [UseEvents],
            [UseTotp],
            [Use2fa],
            [UseApi],
            [UseResetPassword],
            [SelfHost],
            [UsersGetPremium],
            [Storage],
            [MaxStorageGb],
            [Gateway],
            [GatewayCustomerId],
            [GatewaySubscriptionId],
            [ReferenceData],
            [Enabled],
            [LicenseKey],
            [PublicKey],
            [PrivateKey],
            [TwoFactorProviders],
            [ExpirationDate],
            [CreationDate],
            [RevisionDate],
            [OwnersNotifiedOfAutoscaling],
            [MaxAutoscaleSeats],
            [UseKeyConnector],
            [UseScim],
            [UseCustomPermissions],
            [UseSecretsManager],
            [Status],
            [UsePasswordManager],
            [SmSeats],
            [SmServiceAccounts],
            [MaxAutoscaleSmSeats],
            [MaxAutoscaleSmServiceAccounts],
            [SecretsManagerBeta],
            [LimitCollectionCreationDeletion], -- Deprecated https://bitwarden.atlassian.net/browse/PM-10863
            [LimitCollectionCreation],
            [LimitCollectionDeletion],
            [AllowAdminAccessToAllCollectionItems],
            [UseRiskInsights]
        )
        VALUES
        (
            @Id,
            @Identifier,
            @Name,
            @BusinessName,
            @BusinessAddress1,
            @BusinessAddress2,
            @BusinessAddress3,
            @BusinessCountry,
            @BusinessTaxNumber,
            @BillingEmail,
            @Plan,
            @PlanType,
            @Seats,
            @MaxCollections,
            @UsePolicies,
            @UseSso,
            @UseGroups,
            @UseDirectory,
            @UseEvents,
            @UseTotp,
            @Use2fa,
            @UseApi,
            @UseResetPassword,
            @SelfHost,
            @UsersGetPremium,
            @Storage,
            @MaxStorageGb,
            @Gateway,
            @GatewayCustomerId,
            @GatewaySubscriptionId,
            @ReferenceData,
            @Enabled,
            @LicenseKey,
            @PublicKey,
            @PrivateKey,
            @TwoFactorProviders,
            @ExpirationDate,
            @CreationDate,
            @RevisionDate,
            @OwnersNotifiedOfAutoscaling,
            @MaxAutoscaleSeats,
            @UseKeyConnector,
            @UseScim,
            @UseCustomPermissions,
            @UseSecretsManager,
            @Status,
            @UsePasswordManager,
            @SmSeats,
            @SmServiceAccounts,
            @MaxAutoscaleSmSeats,
            @MaxAutoscaleSmServiceAccounts,
            @SecretsManagerBeta,
            COALESCE(@LimitCollectionCreation, @LimitCollectionDeletion, 0), -- Deprecated https://bitwarden.atlassian.net/browse/PM-10863)
            @LimitCollectionCreation,
            @LimitCollectionDeletion,
            @AllowAdminAccessToAllCollectionItems,
            @UseRiskInsights
        )
    END
    GO

    /* Add UseRiskInsights to Organization_readAbilities */
    CREATE OR ALTER PROCEDURE [dbo].[Organization_ReadAbilities]
    AS
    BEGIN
        SET NOCOUNT ON

        SELECT
            [Id],
            [UseEvents],
            [Use2fa],
            CASE
            WHEN [Use2fa] = 1 AND [TwoFactorProviders] IS NOT NULL AND [TwoFactorProviders] != '{}' THEN
                1
            ELSE
                0
            END AS [Using2fa],
            [UsersGetPremium],
            [UseCustomPermissions],
            [UseSso],
            [UseKeyConnector],
            [UseScim],
            [UseResetPassword],
            [UsePolicies],
            [Enabled],
            [LimitCollectionCreationDeletion], -- Deprecated https://bitwarden.atlassian.net/browse/PM-10863
            [LimitCollectionCreation],
            [LimitCollectionDeletion],
            [AllowAdminAccessToAllCollectionItems],
            [UseRiskInsights]
        FROM
            [dbo].[Organization]
    END
    GO

    /* Add UseRiskInsights to Organization_Update */
    CREATE OR ALTER PROCEDURE [dbo].[Organization_Update]
        @Id UNIQUEIDENTIFIER,
        @Identifier NVARCHAR(50),
        @Name NVARCHAR(50),
        @BusinessName NVARCHAR(50),
        @BusinessAddress1 NVARCHAR(50),
        @BusinessAddress2 NVARCHAR(50),
        @BusinessAddress3 NVARCHAR(50),
        @BusinessCountry VARCHAR(2),
        @BusinessTaxNumber NVARCHAR(30),
        @BillingEmail NVARCHAR(256),
        @Plan NVARCHAR(50),
        @PlanType TINYINT,
        @Seats INT,
        @MaxCollections SMALLINT,
        @UsePolicies BIT,
        @UseSso BIT,
        @UseGroups BIT,
        @UseDirectory BIT,
        @UseEvents BIT,
        @UseTotp BIT,
        @Use2fa BIT,
        @UseApi BIT,
        @UseResetPassword BIT,
        @SelfHost BIT,
        @UsersGetPremium BIT,
        @Storage BIGINT,
        @MaxStorageGb SMALLINT,
        @Gateway TINYINT,
        @GatewayCustomerId VARCHAR(50),
        @GatewaySubscriptionId VARCHAR(50),
        @ReferenceData VARCHAR(MAX),
        @Enabled BIT,
        @LicenseKey VARCHAR(100),
        @PublicKey VARCHAR(MAX),
        @PrivateKey VARCHAR(MAX),
        @TwoFactorProviders NVARCHAR(MAX),
        @ExpirationDate DATETIME2(7),
        @CreationDate DATETIME2(7),
        @RevisionDate DATETIME2(7),
        @OwnersNotifiedOfAutoscaling DATETIME2(7),
        @MaxAutoscaleSeats INT,
        @UseKeyConnector BIT = 0,
        @UseScim BIT = 0,
        @UseCustomPermissions BIT = 0,
        @UseSecretsManager BIT = 0,
        @Status TINYINT = 0,
        @UsePasswordManager BIT = 1,
        @SmSeats INT = null,
        @SmServiceAccounts INT = null,
        @MaxAutoscaleSmSeats INT = null,
        @MaxAutoscaleSmServiceAccounts INT = null,
        @SecretsManagerBeta BIT = 0,
        @LimitCollectionCreationDeletion BIT = null, -- Deprecated https://bitwarden.atlassian.net/browse/PM-10863
        @LimitCollectionCreation BIT = null,
        @LimitCollectionDeletion BIT = null,
        @AllowAdminAccessToAllCollectionItems BIT = 0,
        @UseRiskInsights BIT = 0
    AS
    BEGIN
        SET NOCOUNT ON

        SET @LimitCollectionCreation = COALESCE(@LimitCollectionCreation, @LimitCollectionCreationDeletion, 0);
        SET @LimitCollectionDeletion = COALESCE(@LimitCollectionDeletion, @LimitCollectionCreationDeletion, 0);

        UPDATE
            [dbo].[Organization]
        SET
            [Identifier] = @Identifier,
            [Name] = @Name,
            [BusinessName] = @BusinessName,
            [BusinessAddress1] = @BusinessAddress1,
            [BusinessAddress2] = @BusinessAddress2,
            [BusinessAddress3] = @BusinessAddress3,
            [BusinessCountry] = @BusinessCountry,
            [BusinessTaxNumber] = @BusinessTaxNumber,
            [BillingEmail] = @BillingEmail,
            [Plan] = @Plan,
            [PlanType] = @PlanType,
            [Seats] = @Seats,
            [MaxCollections] = @MaxCollections,
            [UsePolicies] = @UsePolicies,
            [UseSso] = @UseSso,
            [UseGroups] = @UseGroups,
            [UseDirectory] = @UseDirectory,
            [UseEvents] = @UseEvents,
            [UseTotp] = @UseTotp,
            [Use2fa] = @Use2fa,
            [UseApi] = @UseApi,
            [UseResetPassword] = @UseResetPassword,
            [SelfHost] = @SelfHost,
            [UsersGetPremium] = @UsersGetPremium,
            [Storage] = @Storage,
            [MaxStorageGb] = @MaxStorageGb,
            [Gateway] = @Gateway,
            [GatewayCustomerId] = @GatewayCustomerId,
            [GatewaySubscriptionId] = @GatewaySubscriptionId,
            [ReferenceData] = @ReferenceData,
            [Enabled] = @Enabled,
            [LicenseKey] = @LicenseKey,
            [PublicKey] = @PublicKey,
            [PrivateKey] = @PrivateKey,
            [TwoFactorProviders] = @TwoFactorProviders,
            [ExpirationDate] = @ExpirationDate,
            [CreationDate] = @CreationDate,
            [RevisionDate] = @RevisionDate,
            [OwnersNotifiedOfAutoscaling] = @OwnersNotifiedOfAutoscaling,
            [MaxAutoscaleSeats] = @MaxAutoscaleSeats,
            [UseKeyConnector] = @UseKeyConnector,
            [UseScim] = @UseScim,
            [UseCustomPermissions] = @UseCustomPermissions,
            [UseSecretsManager] = @UseSecretsManager,
            [Status] = @Status,
            [UsePasswordManager] = @UsePasswordManager,
            [SmSeats] = @SmSeats,
            [SmServiceAccounts] = @SmServiceAccounts,
            [MaxAutoscaleSmSeats] = @MaxAutoscaleSmSeats,
            [MaxAutoscaleSmServiceAccounts] = @MaxAutoscaleSmServiceAccounts,
            [SecretsManagerBeta] = @SecretsManagerBeta,
            [LimitCollectionCreationDeletion] = COALESCE(@LimitCollectionCreation, @LimitCollectionDeletion, 0),
            [LimitCollectionCreation] = @LimitCollectionCreation,
            [LimitCollectionDeletion] = @LimitCollectionDeletion,
            [AllowAdminAccessToAllCollectionItems] = @AllowAdminAccessToAllCollectionItems,
            [UseRiskInsights] = @UseRiskInsights
        WHERE
            [Id] = @Id
    END
    GO