156 lines
3.5 KiB
Transact-SQL
156 lines
3.5 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.UserTwoFactor', N'ConfirmedUtc') IS NULL
|
|
ALTER TABLE dbo.UserTwoFactor ADD ConfirmedUtc datetime2 NULL;
|
|
GO
|
|
|
|
IF COL_LENGTH(N'dbo.UserTwoFactor', N'LastUsedTimeStep') IS NULL
|
|
ALTER TABLE dbo.UserTwoFactor ADD LastUsedTimeStep bigint NULL;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_Get
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT UserID, SecretKey, RecoveryCodes, CreatedUtc, ConfirmedUtc, LastUsedTimeStep
|
|
FROM dbo.UserTwoFactor
|
|
WHERE UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_Save
|
|
@UserID int,
|
|
@SecretKey nvarchar(500),
|
|
@RecoveryCodes nvarchar(max) = NULL,
|
|
@ConfirmedUtc datetime2 = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
MERGE dbo.UserTwoFactor AS target
|
|
USING (SELECT @UserID AS UserID, @SecretKey AS SecretKey, @RecoveryCodes AS RecoveryCodes, @ConfirmedUtc AS ConfirmedUtc) AS source
|
|
ON target.UserID = source.UserID
|
|
WHEN MATCHED THEN
|
|
UPDATE SET SecretKey = source.SecretKey,
|
|
RecoveryCodes = source.RecoveryCodes,
|
|
ConfirmedUtc = source.ConfirmedUtc,
|
|
LastUsedTimeStep = NULL
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (UserID, SecretKey, RecoveryCodes, ConfirmedUtc)
|
|
VALUES (source.UserID, source.SecretKey, source.RecoveryCodes, source.ConfirmedUtc);
|
|
|
|
EXEC dbo.UserTwoFactor_Get @UserID = @UserID;
|
|
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,
|
|
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,
|
|
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;
|
|
|
|
EXEC dbo.UserTwoFactor_Get @UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_GetRecoveryCodes
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT RecoveryCodes
|
|
FROM dbo.UserTwoFactor
|
|
WHERE UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserTwoFactor_UpdateLastUsedTimeStep
|
|
@UserID int,
|
|
@LastUsedTimeStep bigint
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.UserTwoFactor
|
|
SET LastUsedTimeStep = @LastUsedTimeStep
|
|
WHERE UserID = @UserID
|
|
AND (LastUsedTimeStep IS NULL OR LastUsedTimeStep < @LastUsedTimeStep);
|
|
|
|
SELECT CAST(CASE WHEN @@ROWCOUNT = 1 THEN 1 ELSE 0 END AS bit);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_GetTwoFactor
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
EXEC dbo.UserTwoFactor_Get @UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_SaveTwoFactor
|
|
@UserID int,
|
|
@SecretKey nvarchar(500),
|
|
@RecoveryCodes nvarchar(max) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
EXEC dbo.UserTwoFactor_Save
|
|
@UserID = @UserID,
|
|
@SecretKey = @SecretKey,
|
|
@RecoveryCodes = @RecoveryCodes,
|
|
@ConfirmedUtc = NULL;
|
|
END;
|
|
GO
|