396 lines
11 KiB
Transact-SQL
396 lines
11 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.AppUser', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.AppUser
|
|
(
|
|
UserID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AppUser PRIMARY KEY,
|
|
Email nvarchar(256) NOT NULL,
|
|
DisplayName nvarchar(200) NOT NULL,
|
|
PasswordHash nvarchar(max) NOT NULL,
|
|
EmailConfirmed bit NOT NULL CONSTRAINT DF_AppUser_EmailConfirmed DEFAULT 0,
|
|
IsLocked bit NOT NULL CONSTRAINT DF_AppUser_IsLocked DEFAULT 0,
|
|
FailedLoginAttempts int NOT NULL CONSTRAINT DF_AppUser_FailedLoginAttempts DEFAULT 0,
|
|
LockoutEndUtc datetime2 NULL,
|
|
TwoFactorEnabled bit NOT NULL CONSTRAINT DF_AppUser_TwoFactorEnabled DEFAULT 0,
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_AppUser_CreatedUtc DEFAULT SYSUTCDATETIME(),
|
|
UpdatedUtc datetime2 NOT NULL CONSTRAINT DF_AppUser_UpdatedUtc DEFAULT SYSUTCDATETIME(),
|
|
LastLoginUtc datetime2 NULL
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_AppUser_Email' AND object_id = OBJECT_ID(N'dbo.AppUser'))
|
|
CREATE UNIQUE INDEX UX_AppUser_Email ON dbo.AppUser(Email);
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.UserEmailVerificationToken', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.UserEmailVerificationToken
|
|
(
|
|
TokenID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserEmailVerificationToken PRIMARY KEY,
|
|
UserID int NOT NULL,
|
|
Token uniqueidentifier NOT NULL,
|
|
ExpiryUtc datetime2 NOT NULL,
|
|
UsedUtc datetime2 NULL,
|
|
CONSTRAINT FK_UserEmailVerificationToken_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_UserEmailVerificationToken_Token' AND object_id = OBJECT_ID(N'dbo.UserEmailVerificationToken'))
|
|
CREATE UNIQUE INDEX UX_UserEmailVerificationToken_Token ON dbo.UserEmailVerificationToken(Token);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserEmailVerificationToken_UserID' AND object_id = OBJECT_ID(N'dbo.UserEmailVerificationToken'))
|
|
CREATE INDEX IX_UserEmailVerificationToken_UserID ON dbo.UserEmailVerificationToken(UserID, ExpiryUtc DESC);
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.UserPasswordResetToken', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.UserPasswordResetToken
|
|
(
|
|
TokenID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserPasswordResetToken PRIMARY KEY,
|
|
UserID int NOT NULL,
|
|
Token uniqueidentifier NOT NULL,
|
|
ExpiryUtc datetime2 NOT NULL,
|
|
UsedUtc datetime2 NULL,
|
|
CONSTRAINT FK_UserPasswordResetToken_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_UserPasswordResetToken_Token' AND object_id = OBJECT_ID(N'dbo.UserPasswordResetToken'))
|
|
CREATE UNIQUE INDEX UX_UserPasswordResetToken_Token ON dbo.UserPasswordResetToken(Token);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserPasswordResetToken_UserID' AND object_id = OBJECT_ID(N'dbo.UserPasswordResetToken'))
|
|
CREATE INDEX IX_UserPasswordResetToken_UserID ON dbo.UserPasswordResetToken(UserID, ExpiryUtc DESC);
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.UserTwoFactor', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.UserTwoFactor
|
|
(
|
|
UserID int NOT NULL CONSTRAINT PK_UserTwoFactor PRIMARY KEY,
|
|
SecretKey nvarchar(500) NOT NULL,
|
|
RecoveryCodes nvarchar(max) NULL,
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_UserTwoFactor_CreatedUtc DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_UserTwoFactor_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.UserLoginAudit', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.UserLoginAudit
|
|
(
|
|
AuditID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserLoginAudit PRIMARY KEY,
|
|
UserID int NULL,
|
|
EmailAttempted nvarchar(256) NULL,
|
|
IpAddress nvarchar(100) NULL,
|
|
UserAgent nvarchar(500) NULL,
|
|
WasSuccessful bit NOT NULL,
|
|
Reason nvarchar(200) NULL,
|
|
CreatedUtc datetime2 NOT NULL CONSTRAINT DF_UserLoginAudit_CreatedUtc DEFAULT SYSUTCDATETIME(),
|
|
CONSTRAINT FK_UserLoginAudit_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserLoginAudit_UserCreated' AND object_id = OBJECT_ID(N'dbo.UserLoginAudit'))
|
|
CREATE INDEX IX_UserLoginAudit_UserCreated ON dbo.UserLoginAudit(UserID, CreatedUtc DESC);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserLoginAudit_EmailCreated' AND object_id = OBJECT_ID(N'dbo.UserLoginAudit'))
|
|
CREATE INDEX IX_UserLoginAudit_EmailCreated ON dbo.UserLoginAudit(EmailAttempted, CreatedUtc DESC);
|
|
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
|
|
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
|
|
FROM dbo.AppUser
|
|
WHERE UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_Create
|
|
@Email nvarchar(256),
|
|
@DisplayName nvarchar(200),
|
|
@PasswordHash nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT INTO dbo.AppUser (Email, DisplayName, PasswordHash)
|
|
VALUES (@Email, @DisplayName, @PasswordHash);
|
|
|
|
DECLARE @UserID int = SCOPE_IDENTITY();
|
|
|
|
EXEC dbo.User_GetById @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_CreateEmailVerificationToken
|
|
@UserID int,
|
|
@Token uniqueidentifier,
|
|
@ExpiryUtc datetime2
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT INTO dbo.UserEmailVerificationToken (UserID, Token, ExpiryUtc)
|
|
VALUES (@UserID, @Token, @ExpiryUtc);
|
|
|
|
DECLARE @TokenID int = SCOPE_IDENTITY();
|
|
|
|
SELECT TokenID, UserID, Token, ExpiryUtc, UsedUtc
|
|
FROM dbo.UserEmailVerificationToken
|
|
WHERE TokenID = @TokenID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_GetEmailVerificationToken
|
|
@Token uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TokenID, UserID, Token, ExpiryUtc, UsedUtc
|
|
FROM dbo.UserEmailVerificationToken
|
|
WHERE Token = @Token;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_ConfirmEmail
|
|
@UserID int,
|
|
@Token uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.UserEmailVerificationToken
|
|
SET UsedUtc = SYSUTCDATETIME()
|
|
WHERE UserID = @UserID
|
|
AND Token = @Token
|
|
AND UsedUtc IS NULL
|
|
AND ExpiryUtc >= SYSUTCDATETIME();
|
|
|
|
IF @@ROWCOUNT = 1
|
|
BEGIN
|
|
UPDATE dbo.AppUser
|
|
SET EmailConfirmed = 1,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE UserID = @UserID;
|
|
|
|
SELECT CAST(1 AS bit);
|
|
RETURN;
|
|
END;
|
|
|
|
SELECT CAST(0 AS bit);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_CreatePasswordResetToken
|
|
@UserID int,
|
|
@Token uniqueidentifier,
|
|
@ExpiryUtc datetime2
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT INTO dbo.UserPasswordResetToken (UserID, Token, ExpiryUtc)
|
|
VALUES (@UserID, @Token, @ExpiryUtc);
|
|
|
|
DECLARE @TokenID int = SCOPE_IDENTITY();
|
|
|
|
SELECT TokenID, UserID, Token, ExpiryUtc, UsedUtc
|
|
FROM dbo.UserPasswordResetToken
|
|
WHERE TokenID = @TokenID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_GetPasswordResetToken
|
|
@Token uniqueidentifier
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TokenID, UserID, Token, ExpiryUtc, UsedUtc
|
|
FROM dbo.UserPasswordResetToken
|
|
WHERE Token = @Token;
|
|
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,
|
|
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.User_EnableTwoFactor
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.AppUser
|
|
SET TwoFactorEnabled = 1,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE UserID = @UserID;
|
|
|
|
EXEC dbo.User_GetById @UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_DisableTwoFactor
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.AppUser
|
|
SET TwoFactorEnabled = 0,
|
|
UpdatedUtc = SYSUTCDATETIME()
|
|
WHERE UserID = @UserID;
|
|
|
|
EXEC dbo.User_GetById @UserID = @UserID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.User_GetTwoFactor
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT UserID, SecretKey, RecoveryCodes, CreatedUtc
|
|
FROM dbo.UserTwoFactor
|
|
WHERE 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;
|
|
|
|
MERGE dbo.UserTwoFactor AS target
|
|
USING (SELECT @UserID AS UserID, @SecretKey AS SecretKey, @RecoveryCodes AS RecoveryCodes) AS source
|
|
ON target.UserID = source.UserID
|
|
WHEN MATCHED THEN
|
|
UPDATE SET SecretKey = source.SecretKey,
|
|
RecoveryCodes = source.RecoveryCodes
|
|
WHEN NOT MATCHED THEN
|
|
INSERT (UserID, SecretKey, RecoveryCodes)
|
|
VALUES (source.UserID, source.SecretKey, source.RecoveryCodes);
|
|
|
|
EXEC dbo.User_GetTwoFactor @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(200) = 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
|