Applies to Identity Platform 23.07 or later
Use this guide as a reference to create SQL tables and stored procedures that are needed for SecureAuth® Identity Platform to utilize the SQL database for membership and/or profile information.
Important: Before you upgrade to the Identity Platform release 23.07 or later, you must update your SQL data store
To learn about this important update, see Upgrade information for SQL data stores.
SecureAuth Identity Platform release 23.07 or later
SQL user data store
SQL user data store added in the Identity Platform
Ensure ports are open to enable connection to the SQL user data store
Basic SQL knowledge to deploy the scripts below
Note
If the JSON Data Format is selected for Device Profiles, Push Notification Tokens, HOTP Token, or Access Histories Profile Fields, then the JSON script must be executed for Create Object Table Type, Get User Profile Stored Procedure, Update User Profile Stored Procedure, and Create Database operations.
See a screenshot of Profile Fields with JSON Data Format selected...
![]() |
To delete an end user from the SQL server data store, use the Delete user API endpoint.
Single table contains the User ID, Password, and Profile Information.
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL,
[CookieKeys] [varchar](1000) NULL,
[UniqueUserId] [uniqueidentifier] NULL,
[MultiFactorThrottle] [varchar](512) NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[CookieKeys] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL,
[UniqueUserId] [uniqueidentifier] NULL,
[MultiFactorThrottle] [varchar](512) NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO
Script to create the Object Table Type used for Fingerprint and Push Notification token information.
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
Use this script to create the Object Table Type when Fingerprint and Push Notification token information is configured in JSON.
USE [UserStore]
GO
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
CREATE TYPE [dbo].[JsonTable] AS TABLE( [ObjectValue] [varchar](max) NULL)
GO
Table contains Browser / Device Fingerprinting information for each user.
CREATE TABLE [dbo].[UserFP] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO
CREATE TABLE [dbo].[UserFPJson] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFPJson (UserName)
GO
Table contains Push Notification token information for each user.
CREATE TABLE [dbo].[UserPN] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO
CREATE TABLE [dbo].[UserPNJson] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPNJson (UserName)
GO
Table contains Access History information for each user
CREATE TABLE [dbo].[UserAccessHistory] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO
CREATE TABLE [dbo].[UserAccessHistoryJson] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
GO
CREATE TABLE [dbo].[UserOT] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO
CREATE TABLE [dbo].[UserOTJson] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOTJson (UserName)
GO
Improve response times from SQL by having the tables indexed.
create nonclustered index UserIndex ON UserStore.dbo.UserTable (UserName)
create nonclustered index FPIndex ON UserStore.dbo.UserFP (UserName)
create nonclustered index OTIndex ON UserStore.dbo.UserOT (UserName)
create nonclustered index AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
create nonclustered index PNIndex ON UserStore.dbo.UserPN (UserName)
create nonclustered index UserIndex ON UserStore.dbo.UserTable (UserName)
create nonclustered index FPIndex ON UserStore.dbo.UserFPJson (UserName)
create nonclustered index OTIndex ON UserStore.dbo.UserOTJson (UserName)
create nonclustered index AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
create nonclustered index PNIndex ON UserStore.dbo.UserPNJson (UserName)
The following are sample stored procedures for use with the Identity Platform SQL Membership providers.
Checks for existing username and returns the same username when it exists.
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
SELECT [UserName]
,ISNULL([GroupList], '')
,ISNULL([PwdLastSet],'1/1/1900')
,ISNULL([AccountStatus], '')
FROM UserTable
WHERE UserName = @UserName
END
Resets the password for the given user.
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
IF @@ROWCOUNT > 0
BEGIN
SELECT 1
END
END
GO
Gets the password of the user for validation.
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
@UserName varchar(60)
AS
BEGIN
SELECT [Password], PasswordFormat, PasswordSalt
FROM dbo.UserTable
WHERE @UserName = UserName
END
GO
Inserts the username and password into the user table, and returns a MembershipCreateStatus enumeration, with zero (0) signaling success.
For more information about adding new enumeration values to the Create User stored procedure, see MembershipCreateStatus Enum.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat,NEWID())
IF @@ROWCOUNT > 0
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
Used on the Create User page to update the GroupList column for a user.
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET GroupList = @GroupList
WHERE UserName = @UserName
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
The stored procedure examples include all profile information. You must modify your stored procedures to match the profile information mapped to the SQL profile provider in the profile properties.
For example, to only use SQL to store and retrieve Fingerprint information, then send only the Fingerprint parameter to the Identity Platform.
Retrieves the profile of the given username.
CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,IsNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFP
WHERE UserName = @UserName
SELECT PNToken
FROM UserPN
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistory
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOT
WHERE UserName = @UserName
END
GOUSE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,isNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFP
WHERE UserName = @UserName
SELECT PNToken
FROM UserPN
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistory
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOT
WHERE UserName = @UserName
END
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,isNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFPJson
WHERE UserName = @UserName
SELECT PNToken
FROM UserPNJson
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistoryJson
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOTJson
WHERE UserName = @UserName
END
GO
Updates the user profile with the given profile information.
CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@MultiFactorThrottle VARCHAR(512) = NULL
,@DigitalFP dbo.ObjectTable READONLY
,@PNToken dbo.ObjectTable READONLY
,@AccessHistory dbo.ObjectTable READONLY
,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFP
WHERE UserName = @UserName
INSERT INTO UserFP
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPN
WHERE UserName = @UserName
INSERT INTO UserPN
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistory
WHERE UserName = @UserName
INSERT INTO UserAccessHistory
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOT
WHERE UserName = @UserName
INSERT INTO UserOT
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@MultiFactorThrottle VARCHAR(512) = NULL
,@DigitalFP dbo.JsonTable READONLY
,@PNToken dbo.JsonTable READONLY
,@AccessHistory dbo.JsonTable READONLY
,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFPJson
WHERE UserName = @UserName
INSERT INTO UserFPJson
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPNJson
WHERE UserName = @UserName
INSERT INTO UserPNJson
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistoryJson
WHERE UserName = @UserName
INSERT INTO UserAccessHistoryJson
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOTJson
WHERE UserName = @UserName
INSERT INTO UserOTJson
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
--------------------------- COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
Locks an end user account.
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
Unlocks an end user account.
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',LOCKED','')
SET @status = REPLACE(@status, 'LOCKED,','')
SET @status = REPLACE(@status, 'LOCKED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
Disables an end user account.
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
Enables an end user account.
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',DISABLED','')
SET @status = REPLACE(@status, 'DISABLED,','')
SET @status = REPLACE(@status, 'DISABLED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
Creates a service account user, service account role, and adds the user to the role.
Note
Change the username SecureAuthSQLUser
and Password
to the actual values.
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
The CreateDatabase2307.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database.
USE [master]
GO
CREATE DATABASE [UserStore] ON PRIMARY (
NAME = N'UserStore'
,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore.mdf'
,SIZE = 4096 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
) LOG ON (
NAME = N'UserStore_log'
,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore_log.ldf'
,SIZE = 1024 KB
,MAXSIZE = 2048 GB
,FILEGROWTH = 10 %
)
GO
ALTER DATABASE [UserStore]
SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [UserStore]
SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_NULLS OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_PADDING OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UserStore]
SET ARITHABORT OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UserStore]
SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [UserStore]
SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UserStore]
SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UserStore]
SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UserStore]
SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UserStore]
SET DISABLE_BROKER
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UserStore]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UserStore]
SET TRUSTWORTHY OFF
GO
ALTER DATABASE [UserStore]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UserStore]
SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UserStore]
SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UserStore]
SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [UserStore]
SET READ_WRITE
GO
ALTER DATABASE [UserStore]
SET RECOVERY SIMPLE
GO
ALTER DATABASE [UserStore]
SET MULTI_USER
GO
ALTER DATABASE [UserStore]
SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [UserStore]
SET DB_CHAINING OFF
GO
USE [UserStore]
GO
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL,
[CookieKeys] [varchar](1000) NULL,
[UniqueUserId] [uniqueidentifier] NULL,
[MultiFactorThrottle] [varchar](512) NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO
CREATE TABLE [dbo].[UserFP] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO
CREATE TABLE [dbo].[UserPN] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO
CREATE TABLE [dbo].[UserAccessHistory] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO
CREATE TABLE [dbo].[UserOT] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@MultiFactorThrottle VARCHAR(512) = NULL
,@DigitalFP dbo.ObjectTable READONLY
,@PNToken dbo.ObjectTable READONLY
,@AccessHistory dbo.ObjectTable READONLY
,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFP
WHERE UserName = @UserName
INSERT INTO UserFP
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPN
WHERE UserName = @UserName
INSERT INTO UserPN
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistory
WHERE UserName = @UserName
INSERT INTO UserAccessHistory
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOT
WHERE UserName = @UserName
INSERT INTO UserOT
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET GroupList = @GroupList
WHERE UserName = @UserName
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,IsNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFP
WHERE UserName = @UserName
SELECT PNToken
FROM UserPN
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistory
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOT
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
SELECT [UserName]
,ISNULL([GroupList], '')
,ISNULL([PwdLastSet],'1/1/1900')
,ISNULL([AccountStatus], '')
FROM UserTable
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID())
IF @@ROWCOUNT > 0
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
IF @@ROWCOUNT > 0
BEGIN
SELECT 1
END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
@UserName varchar(60)
AS
BEGIN
SELECT [Password], PasswordFormat, PasswordSalt
FROM dbo.UserTable
WHERE @UserName = UserName
END
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',LOCKED','')
SET @status = REPLACE(@status, 'LOCKED,','')
SET @status = REPLACE(@status, 'LOCKED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',DISABLED','')
SET @status = REPLACE(@status, 'DISABLED,','')
SET @status = REPLACE(@status, 'DISABLED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount
The CreateDatabaseJson2307.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database in JSON format.
USE [master]
GO
CREATE DATABASE [UserStore] ON PRIMARY (
NAME = N'UserStore'
,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore.mdf'
,SIZE = 4096 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
) LOG ON (
NAME = N'UserStore_log'
,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore_log.ldf'
,SIZE = 1024 KB
,MAXSIZE = 2048 GB
,FILEGROWTH = 10 %
)
GO
ALTER DATABASE [UserStore]
SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [UserStore]
SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_NULLS OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_PADDING OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UserStore]
SET ARITHABORT OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UserStore]
SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [UserStore]
SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UserStore]
SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UserStore]
SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UserStore]
SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UserStore]
SET DISABLE_BROKER
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UserStore]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UserStore]
SET TRUSTWORTHY OFF
GO
ALTER DATABASE [UserStore]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UserStore]
SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UserStore]
SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UserStore]
SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [UserStore]
SET READ_WRITE
GO
ALTER DATABASE [UserStore]
SET RECOVERY SIMPLE
GO
ALTER DATABASE [UserStore]
SET MULTI_USER
GO
ALTER DATABASE [UserStore]
SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [UserStore]
SET DB_CHAINING OFF
GO
USE [UserStore]
GO
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
CREATE TYPE [dbo].[JsonTable] AS TABLE([ObjectValue] [varchar](max) NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[CookieKeys] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL,
[UniqueUserId] [uniqueidentifier] NULL,
[MultiFactorThrottle] [varchar](512) NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO
CREATE TABLE [dbo].[UserFPJson] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFPJson (UserName)
GO
CREATE TABLE [dbo].[UserPNJson] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPNJson (UserName)
GO
CREATE TABLE [dbo].[UserAccessHistoryJson] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
GO
CREATE TABLE [dbo].[UserOTJson] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOTJson (UserName)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@MultiFactorThrottle VARCHAR(512) = NULL
,@DigitalFP dbo.JsonTable READONLY
,@PNToken dbo.JsonTable READONLY
,@AccessHistory dbo.JsonTable READONLY
,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFPJson
WHERE UserName = @UserName
INSERT INTO UserFPJson
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPNJson
WHERE UserName = @UserName
INSERT INTO UserPNJson
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistoryJson
WHERE UserName = @UserName
INSERT INTO UserAccessHistoryJson
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOTJson
WHERE UserName = @UserName
INSERT INTO UserOTJson
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
--------------------------- COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET GroupList = @GroupList
WHERE UserName = @UserName
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,isNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFPJson
WHERE UserName = @UserName
SELECT PNToken
FROM UserPNJson
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistoryJson
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOTJson
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
SELECT [UserName]
,ISNULL([GroupList], '')
,ISNULL([PwdLastSet],'1/1/1900')
,ISNULL([AccountStatus], '')
FROM UserTable
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID())
IF @@ROWCOUNT > 0
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
IF @@ROWCOUNT > 0
BEGIN
SELECT 1
END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
@UserName varchar(60)
AS
BEGIN
SELECT [Password], PasswordFormat, PasswordSalt
FROM dbo.UserTable
WHERE @UserName = UserName
END
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',LOCKED','')
SET @status = REPLACE(@status, 'LOCKED,','')
SET @status = REPLACE(@status, 'LOCKED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',DISABLED','')
SET @status = REPLACE(@status, 'DISABLED,','')
SET @status = REPLACE(@status, 'DISABLED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.JsonTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount
There are some slight modifications to the Create Database scripts to support Amazon RDS for SQL Server in AWS. See the following database scripts with comments specifying the location of where the database must be created.
The CreateDatabaseRDSAWS.sql script creates a "UserStore" database and also creates all the above tables, objects, and stored procedures within that database. See the comments in the script specifying the location of where to create the database.
USE [master]
GO
CREATE DATABASE [UserStore] ON PRIMARY (
NAME = N'UserStore'
,FILENAME = N'D:\rdsdbdata\DATA\UserStore.mdf' --please change it for the correct path
,SIZE = 4096 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
) LOG ON (
NAME = N'UserStore_log'
,FILENAME = N'D:\rdsdbdata\DATA\UserStore_log.ldf' --please change it for the correct path
,SIZE = 1024 KB
,MAXSIZE = 2048 GB
,FILEGROWTH = 10 %
)
GO
ALTER DATABASE [UserStore]
SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [UserStore]
SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_NULLS OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_PADDING OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UserStore]
SET ARITHABORT OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UserStore]
SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [UserStore]
SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UserStore]
SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UserStore]
SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UserStore]
SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UserStore]
SET DISABLE_BROKER
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UserStore]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UserStore]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UserStore]
SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UserStore]
SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UserStore]
SET READ_WRITE
GO
ALTER DATABASE [UserStore]
SET RECOVERY SIMPLE
GO
ALTER DATABASE [UserStore]
SET MULTI_USER
GO
ALTER DATABASE [UserStore]
SET PAGE_VERIFY CHECKSUM
GO
USE [UserStore]
GO
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL,
[CookieKeys] [varchar](1000) NULL,
[UniqueUserId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO
CREATE TABLE [dbo].[UserFP] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO
CREATE TABLE [dbo].[UserPN] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO
CREATE TABLE [dbo].[UserAccessHistory] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO
CREATE TABLE [dbo].[UserOT] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varbinary](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@DigitalFP dbo.ObjectTable READONLY
,@PNToken dbo.ObjectTable READONLY
,@AccessHistory dbo.ObjectTable READONLY
,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFP
WHERE UserName = @UserName
INSERT INTO UserFP
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPN
WHERE UserName = @UserName
INSERT INTO UserPN
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistory
WHERE UserName = @UserName
INSERT INTO UserAccessHistory
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOT
WHERE UserName = @UserName
INSERT INTO UserOT
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET GroupList = @GroupList
WHERE UserName = @UserName
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,IsNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFP
WHERE UserName = @UserName
SELECT PNToken
FROM UserPN
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistory
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOT
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
SELECT [UserName]
,ISNULL([GroupList], '')
,ISNULL([PwdLastSet],'1/1/1900')
,ISNULL([AccountStatus], '')
FROM UserTable
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID())
IF @@ROWCOUNT > 0
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
IF @@ROWCOUNT > 0
BEGIN
SELECT 1
END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
@UserName varchar(60)
AS
BEGIN
SELECT [Password], PasswordFormat, PasswordSalt
FROM dbo.UserTable
WHERE @UserName = UserName
END
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',LOCKED','')
SET @status = REPLACE(@status, 'LOCKED,','')
SET @status = REPLACE(@status, 'LOCKED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',DISABLED','')
SET @status = REPLACE(@status, 'DISABLED,','')
SET @status = REPLACE(@status, 'DISABLED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount
The CreateDatabaseRDSAWSJson.sql script creates a "UserStore" database and also creates all the above tables, objects, and stored procedures within that database in JSON format. See the comments in the script specifying the location of where to create the database.
USE [master]
GO
CREATE DATABASE [UserStore] ON PRIMARY (
NAME = N'UserStore'
,FILENAME = N'D:\rdsdbdata\DATA\UserStore.mdf' --please change it for the correct path
,SIZE = 4096 KB
,MAXSIZE = UNLIMITED
,FILEGROWTH = 1024 KB
) LOG ON (
NAME = N'UserStore_log'
,FILENAME = N'D:\rdsdbdata\DATA\UserStore_log.ldf' --please change it for the correct path
,SIZE = 1024 KB
,MAXSIZE = 2048 GB
,FILEGROWTH = 10 %
)
GO
ALTER DATABASE [UserStore]
SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [UserStore]
SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_NULLS OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_PADDING OFF
GO
ALTER DATABASE [UserStore]
SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UserStore]
SET ARITHABORT OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UserStore]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UserStore]
SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [UserStore]
SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UserStore]
SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UserStore]
SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UserStore]
SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UserStore]
SET DISABLE_BROKER
GO
ALTER DATABASE [UserStore]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UserStore]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UserStore]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UserStore]
SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UserStore]
SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UserStore]
SET READ_WRITE
GO
ALTER DATABASE [UserStore]
SET RECOVERY SIMPLE
GO
ALTER DATABASE [UserStore]
SET MULTI_USER
GO
ALTER DATABASE [UserStore]
SET PAGE_VERIFY CHECKSUM
GO
USE [UserStore]
GO
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
CREATE TYPE [dbo].[JsonTable] AS TABLE([ObjectValue] [varchar](max) NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserTable](
[UserName] [varchar](60) NOT NULL,
[Password] [varchar](128) NULL,
[PasswordSalt] [varchar](128) NULL,
[PasswordFormat] [int] NULL,
[PwdLastSet] [datetime] NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Phone1] [varchar](40) NULL,
[Phone2] [varchar](40) NULL,
[Phone3] [varchar](40) NULL,
[Phone4] [varchar](40) NULL,
[Email1] [varchar](60) NULL,
[Email2] [varchar](60) NULL,
[Email3] [varchar](60) NULL,
[Email4] [varchar](60) NULL,
[AuxID1] [varchar](512) NULL,
[AuxID2] [varchar](512) NULL,
[AuxID3] [varchar](512) NULL,
[AuxID4] [varchar](512) NULL,
[AuxID5] [varchar](512) NULL,
[AuxID6] [varchar](512) NULL,
[AuxID7] [varchar](512) NULL,
[AuxID8] [varchar](512) NULL,
[AuxID9] [varchar](512) NULL,
[AuxID10] [varchar](512) NULL,
[pinHash] [varchar](120) NULL,
[Questions] [varchar](1000) NULL,
[Answers] [varchar](1000) NULL,
[ChallengeQuestion] [varchar](1000) NULL,
[ChallengeAnswer] [varchar](1000) NULL,
[CertResetDate] [varchar](50) NULL,
[CertCount] [varchar](50) NULL,
[MobileResetDate] [varchar](50) NULL,
[MobileCount] [varchar](50) NULL,
[ExtSyncPwdDate] [varchar](50) NULL,
[OATHSeed] [varchar](MAX) NULL,
[OneTimeOATHList] [varchar](1000) NULL,
[CookieKeys] [varchar](1000) NULL,
[iOSDevices] [varchar](50) NULL,
[HardwareToken] [varchar](50) NULL,
[CertSerialNumber] [varchar](1000) NULL,
[GroupList] [varchar](1000) NULL,
[AccountStatus] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserFPJson] (
[UserName] [varchar](60) NOT NULL
,[DigitalFP] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserPNJson] (
[UserName] [varchar](60) NOT NULL
,[PNToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserAccessHistoryJson] (
[UserName] [varchar](60) NOT NULL
,[AccessHistory] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserOTJson] (
[UserName] [varchar](60) NOT NULL
,[OATHToken] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
,@FirstName VARCHAR(50) = NULL
,@LastName VARCHAR(50) = NULL
,@Phone1 VARCHAR(60) = NULL
,@Phone2 VARCHAR(40) = NULL
,@Phone3 VARCHAR(40) = NULL
,@Phone4 VARCHAR(40) = NULL
,@Email1 VARCHAR(60) = NULL
,@Email2 VARCHAR(60) = NULL
,@Email3 VARCHAR(60) = NULL
,@Email4 VARCHAR(60) = NULL
,@AuxID1 VARCHAR(512) = NULL
,@AuxID2 VARCHAR(512) = NULL
,@AuxID3 VARCHAR(512) = NULL
,@AuxID4 VARCHAR(512) = NULL
,@AuxID5 VARCHAR(512) = NULL
,@AuxID6 VARCHAR(512) = NULL
,@AuxID7 VARCHAR(512) = NULL
,@AuxID8 VARCHAR(512) = NULL
,@AuxID9 VARCHAR(512) = NULL
,@AuxID10 VARCHAR(512) = NULL
,@pinHash VARCHAR(120) = NULL
,@Questions VARCHAR(1000) = NULL
,@Answers VARCHAR(1000) = NULL
,@ChallengeQuestion VARCHAR(1000) = NULL
,@ChallengeAnswer VARCHAR(1000) = NULL
,@CertResetDate VARCHAR(50) = NULL
,@CertSerialNumber VARCHAR(1000) = NULL
,@CertCount VARCHAR(50) = NULL
,@MobileResetDate VARCHAR(50) = NULL
,@MobileCount VARCHAR(50) = NULL
,@ExtSyncPwdDate VARCHAR(50) = NULL
,@HardwareToken VARCHAR(1000) = NULL
,@iOSDevices VARCHAR(1000) = NULL
,@OATHSeed VARCHAR(MAX) = NULL
,@OneTimeOATHList VARCHAR(1000) = NULL
,@CookieKeys VARCHAR(1000) = NULL
,@DigitalFP dbo.JsonTable READONLY
,@PNToken dbo.JsonTable READONLY
,@AccessHistory dbo.JsonTable READONLY
,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET FirstName = IsNull(@FirstName, FirstName)
,LastName = IsNull(@LastName, LastName)
,Phone1 = IsNull(@Phone1, Phone1)
,Phone2 = IsNull(@Phone2, Phone2)
,Phone3 = IsNull(@Phone3, Phone3)
,Phone4 = IsNull(@Phone4, Phone4)
,Email1 = IsNull(@Email1, Email1)
,Email2 = IsNull(@Email2, Email2)
,Email3 = IsNull(@Email3, Email3)
,Email4 = IsNull(@Email4, Email4)
,AuxID1 = IsNull(@AuxID1, AuxID1)
,AuxID2 = IsNull(@AuxID2, AuxID2)
,AuxID3 = IsNull(@AuxID3, AuxID3)
,AuxID4 = IsNull(@AuxID4, AuxID4)
,AuxID5 = IsNull(@AuxID5, AuxID5)
,AuxID6 = IsNull(@AuxID6, AuxID6)
,AuxID7 = IsNull(@AuxID7, AuxID7)
,AuxID8 = IsNull(@AuxID8, AuxID8)
,AuxID9 = IsNull(@AuxID9, AuxID9)
,AuxID10 = IsNull(@AuxID10, AuxID10)
,pinHash = IsNull(@pinHash, pinHash)
,Questions = IsNull(@Questions, Questions)
,Answers = IsNull(@Answers, Answers)
,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
,CertResetDate = IsNull(@CertResetDate, CertResetDate)
,CertCount = IsNull(@CertCount, CertCount)
,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
,MobileCount = IsNull(@MobileCount, MobileCount)
,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
,HardwareToken = IsNull(@HardwareToken, HardwareToken)
,iOSDevices = IsNull(@iOSDevices, iOSDevices)
,OATHSeed = IsNull(@OATHSeed, OATHSeed)
,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
,CookieKeys = IsNull(@CookieKeys, CookieKeys)
WHERE UserName = @UserName
--- Update Fingerprints ---
IF EXISTS (SELECT
ObjectValue
FROM @DigitalFP)
BEGIN
DELETE
FROM UserFPJson
WHERE UserName = @UserName
INSERT INTO UserFPJson
SELECT @UserName
,ObjectValue
FROM @DigitalFP
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Push Notification ---
IF EXISTS (SELECT
ObjectValue
FROM @PNToken)
BEGIN
DELETE
FROM UserPNJson
WHERE UserName = @UserName
INSERT INTO UserPNJson
SELECT @UserName
,ObjectValue
FROM @PNToken
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update Access History ---
IF EXISTS (SELECT
ObjectValue
FROM @AccessHistory)
BEGIN
DELETE
FROM UserAccessHistoryJson
WHERE UserName = @UserName
INSERT INTO UserAccessHistoryJson
SELECT @UserName
,ObjectValue
FROM @AccessHistory
WHERE ObjectValue <> 'ERASE'
END
---------------------------
--- Update OATH Token ---
IF EXISTS (SELECT
ObjectValue
FROM @OATHToken)
BEGIN
DELETE
FROM UserOTJson
WHERE UserName = @UserName
INSERT INTO UserOTJson
SELECT @UserName
,ObjectValue
FROM @OATHToken
WHERE ObjectValue <> 'ERASE'
END
--------------------------- COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
SELECT UserName
FROM UserTable
WHERE UserName = @UserName
)
BEGIN
INSERT INTO UserTable (UserName) VALUES (@UserName)
END
BEGIN TRY
BEGIN TRANSACTION
UPDATE UserTable
SET GroupList = @GroupList
WHERE UserName = @UserName
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
SELECT UserName
,IsNull(FirstName, '') FirstName
,IsNull(LastName, '') LastName
,IsNull(Phone1, '') Phone1
,IsNull(Phone2, '') Phone2
,IsNull(Phone3, '') Phone3
,IsNull(Phone4, '') Phone4
,IsNull(Email1, '') Email1
,IsNull(Email2, '') Email2
,IsNull(Email3, '') Email3
,IsNull(Email4, '') Email4
,IsNull(AuxID1, '') AuxID1
,IsNull(AuxID2, '') AuxID2
,IsNull(AuxID3, '') AuxID3
,IsNull(AuxID4, '') AuxID4
,IsNull(AuxID5, '') AuxID5
,IsNull(AuxID6, '') AuxID6
,IsNull(AuxID7, '') AuxID7
,IsNull(AuxID8, '') AuxID8
,IsNull(AuxID9, '') AuxID9
,IsNull(AuxID10, '') AuxID10
,IsNull(pinHash, '') pinHash
,IsNull(Questions, '') Questions
,IsNull(Answers, '') Answers
,IsNull(ChallengeQuestion, '') ChallengeQuestion
,IsNull(ChallengeAnswer, '') ChallengeAnswer
,IsNull(CertResetDate, '1/1/1900') CertResetDate
,IsNull(CertCount, '0') CertCount
,IsNull(CertSerialNumber, '') CertSerialNumber
,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
,IsNull(MobileCount, '0') MobileCount
,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
,IsNull(HardwareToken, '') HardwareToken
,IsNull(iOSDevices, '') iOSDevices
,IsNull(OATHSeed, '') OATHSeed
,IsNull(OneTimeOATHList, '') OneTimeOATHList
,isNull(CookieKeys, '') CookieKeys
,IsNull(GroupList, '') GroupList
FROM UserTable
WHERE UserName = @UserName
SELECT DigitalFP
FROM UserFPJson
WHERE UserName = @UserName
SELECT PNToken
FROM UserPNJson
WHERE UserName = @UserName
SELECT AccessHistory
FROM UserAccessHistoryJson
WHERE UserName = @UserName
SELECT OATHToken
FROM UserOTJson
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
SELECT [UserName]
,ISNULL([GroupList], '')
,ISNULL([PwdLastSet],'1/1/1900')
,ISNULL([AccountStatus], '')
FROM UserTable
WHERE UserName = @UserName
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [PwdLastSet]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, GetDate())
IF @@ROWCOUNT > 0
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
IF @@ROWCOUNT > 0
BEGIN
SELECT 1
END
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
@UserName varchar(60)
AS
BEGIN
SELECT [Password], PasswordFormat, PasswordSalt
FROM dbo.UserTable
WHERE @UserName = UserName
END
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',LOCKED','')
SET @status = REPLACE(@status, 'LOCKED,','')
SET @status = REPLACE(@status, 'LOCKED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('LOCKED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) = 0
BEGIN
UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
END
END
ELSE
BEGIN
UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
@UserName varchar(60)
AS
BEGIN
DECLARE @status varchar (200)
SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
IF (LEN(@status) > 0)
BEGIN
IF charindex('DISABLED', @status) > 0
BEGIN
SET @status = REPLACE(@status, ',DISABLED','')
SET @status = REPLACE(@status, 'DISABLED,','')
SET @status = REPLACE(@status, 'DISABLED','')
UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
END
END
SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.JsonTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount