PlotDirector/PlotLine/Sql/041_Phase4F_SecurityHardening.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF COL_LENGTH(N'dbo.AppUser', N'SecurityStamp') IS NULL
BEGIN
    ALTER TABLE dbo.AppUser ADD SecurityStamp uniqueidentifier NOT NULL CONSTRAINT DF_AppUser_SecurityStamp DEFAULT NEWID();
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE object_id = OBJECT_ID(N'dbo.UserLoginAudit')
        AND name = N'Reason'
        AND max_length < 1000
)
BEGIN
    ALTER TABLE dbo.UserLoginAudit ALTER COLUMN Reason nvarchar(500) NULL;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_GetByEmail
    @Email nvarchar(256)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserID, Email, DisplayName, PasswordHash, EmailConfirmed, IsLocked, FailedLoginAttempts,
        LockoutEndUtc, TwoFactorEnabled, CreatedUtc, UpdatedUtc, LastLoginUtc, SecurityStamp
    FROM dbo.AppUser
    WHERE Email = @Email;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_GetById
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserID, Email, DisplayName, PasswordHash, EmailConfirmed, IsLocked, FailedLoginAttempts,
        LockoutEndUtc, TwoFactorEnabled, CreatedUtc, UpdatedUtc, LastLoginUtc, SecurityStamp
    FROM dbo.AppUser
    WHERE UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_UpdateLoginSuccess
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.AppUser
    SET FailedLoginAttempts = 0,
        IsLocked = 0,
        LockoutEndUtc = NULL,
        LastLoginUtc = SYSUTCDATETIME(),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.User_GetById @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_UpdateFailedLogin
    @UserID int,
    @LockoutEndUtc datetime2 = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.AppUser
    SET FailedLoginAttempts = FailedLoginAttempts + 1,
        IsLocked = CASE WHEN @LockoutEndUtc IS NULL THEN IsLocked ELSE 1 END,
        LockoutEndUtc = COALESCE(@LockoutEndUtc, LockoutEndUtc),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.User_GetById @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_ChangePassword
    @UserID int,
    @PasswordHash nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.AppUser
    SET PasswordHash = @PasswordHash,
        SecurityStamp = NEWID(),
        FailedLoginAttempts = 0,
        IsLocked = 0,
        LockoutEndUtc = NULL,
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.User_GetById @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_ResetPassword
    @UserID int,
    @Token uniqueidentifier,
    @PasswordHash nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserPasswordResetToken
    SET UsedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID
        AND Token = @Token
        AND UsedUtc IS NULL
        AND ExpiryUtc >= SYSUTCDATETIME();

    IF @@ROWCOUNT = 1
    BEGIN
        UPDATE dbo.AppUser
        SET PasswordHash = @PasswordHash,
            SecurityStamp = NEWID(),
            FailedLoginAttempts = 0,
            IsLocked = 0,
            LockoutEndUtc = NULL,
            UpdatedUtc = SYSUTCDATETIME()
        WHERE UserID = @UserID;

        SELECT CAST(1 AS bit);
        RETURN;
    END;

    SELECT CAST(0 AS bit);
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_Enable
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserTwoFactor
    SET ConfirmedUtc = COALESCE(ConfirmedUtc, SYSUTCDATETIME())
    WHERE UserID = @UserID;

    UPDATE dbo.AppUser
    SET TwoFactorEnabled = 1,
        SecurityStamp = NEWID(),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.User_GetById @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_Disable
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.AppUser
    SET TwoFactorEnabled = 0,
        SecurityStamp = NEWID(),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    DELETE FROM dbo.UserTwoFactor
    WHERE UserID = @UserID;

    EXEC dbo.User_GetById @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_SaveRecoveryCodes
    @UserID int,
    @RecoveryCodes nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.UserTwoFactor
    SET RecoveryCodes = @RecoveryCodes
    WHERE UserID = @UserID;

    UPDATE dbo.AppUser
    SET SecurityStamp = NEWID(),
        UpdatedUtc = SYSUTCDATETIME()
    WHERE UserID = @UserID;

    EXEC dbo.UserTwoFactor_Get @UserID = @UserID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.User_InsertLoginAudit
    @UserID int = NULL,
    @EmailAttempted nvarchar(256) = NULL,
    @IpAddress nvarchar(100) = NULL,
    @UserAgent nvarchar(500) = NULL,
    @WasSuccessful bit,
    @Reason nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.UserLoginAudit (UserID, EmailAttempted, IpAddress, UserAgent, WasSuccessful, Reason)
    VALUES (@UserID, @EmailAttempted, @IpAddress, @UserAgent, @WasSuccessful, @Reason);

    SELECT CAST(SCOPE_IDENTITY() AS bigint);
END;
GO