· 9 years ago · Feb 01, 2017, 11:26 PM
1USE [SecureAuthTestDB]
2GO
3
4/****** Object: Table [dbo].[UserTable] Script Date: 2/1/2017 3:20:50 PM ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE TABLE [dbo].[UserTable](
12 [UserName] [varchar](60) NOT NULL,
13 [NumberProfile] [varchar](max) NULL,
14 [Throttle] [varchar](max) NULL,
15 [AuxID3] [varchar](max) NULL,
16 [AuxID4] [varchar](max) NULL,
17 [AuxID5] [varchar](max) NULL
18) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
19
20GO
21
22CREATE TABLE [dbo].[UserPN](
23 [UserName] [varchar](60) NOT NULL,
24 [PNToken] [varbinary](max) NOT NULL
25) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
26
27GO
28
29CREATE TABLE [dbo].[UserOT](
30 [UserName] [varchar](60) NOT NULL,
31 [OATHToken] [varbinary](max) NOT NULL
32) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
33
34GO
35
36CREATE TABLE [dbo].[UserFP](
37 [UserName] [varchar](60) NOT NULL,
38 [DigitalFP] [varbinary](max) NOT NULL
39) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
40
41GO
42
43CREATE TABLE [dbo].[UserAH](
44 [UserName] [varchar](60) NOT NULL,
45 [AccessHistory] [varbinary](max) NOT NULL
46) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
47
48GO
49
50CREATE TYPE [dbo].[ObjectTable] AS TABLE(
51 [ObjectValue] [varbinary](max) NULL
52)
53GO
54
55CREATE PROCEDURE [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
56 ,@FirstName VARCHAR(50) = ''
57 ,@LastName VARCHAR(50) = ''
58 ,@Phone1 VARCHAR(60) = ''
59 ,@Phone2 VARCHAR(40) = ''
60 ,@Phone3 VARCHAR(40) = ''
61 ,@Phone4 VARCHAR(40) = ''
62 ,@Email1 VARCHAR(60) = ''
63 ,@Email2 VARCHAR(60) = ''
64 ,@Email3 VARCHAR(60) = ''
65 ,@Email4 VARCHAR(60) = ''
66 ,@AuxID1 VARCHAR(MAX)
67 ,@AuxID2 VARCHAR(MAX)
68 ,@AuxID3 VARCHAR(MAX)
69 ,@AuxID4 VARCHAR(MAX)
70 ,@AuxID5 VARCHAR(MAX)
71 ,@AuxID6 VARCHAR(512) = ''
72 ,@AuxID7 VARCHAR(512) = ''
73 ,@AuxID8 VARCHAR(512) = ''
74 ,@AuxID9 VARCHAR(512) = ''
75 ,@AuxID10 VARCHAR(512) = ''
76 ,@pinHash VARCHAR(120) = ''
77 ,@Questions VARCHAR(1000) = ''
78 ,@Answers VARCHAR(1000) = ''
79 ,@ChallengeQuestion VARCHAR(1000) = ''
80 ,@ChallengeAnswer VARCHAR(1000) = ''
81 ,@CertResetDate DATETIME ='1/1/1900'
82 ,@CertSerialNumber VARCHAR(1000) = ''
83 ,@CertCount INT = 0
84 ,@MobileResetDate DATETIME ='1/1/1900'
85 ,@MobileCount INT = 0
86 ,@ExtSyncPwdDate DATETIME ='1/1/1900'
87 ,@HardwareToken VARCHAR(1000) = ''
88 ,@iOSDevices VARCHAR(1000) = ''
89 ,@OATHSeed VARCHAR(MAX) = ''
90 ,@OneTimeOATHList VARCHAR(1000) = ''
91 ,@DigitalFP dbo.ObjectTable READONLY
92 ,@PNToken dbo.ObjectTable READONLY
93 ,@AccessHistory dbo.ObjectTable READONLY
94 ,@OATHToken dbo.ObjectTable READONLY
95AS
96IF NOT EXISTS (
97 SELECT UserName
98 FROM UserTable
99 WHERE UserName = @UserName
100 )
101BEGIN
102 INSERT INTO UserTable (UserName)
103 VALUES (@UserName)
104END
105
106BEGIN TRY
107 BEGIN TRANSACTION
108
109 UPDATE UserTable
110 SET NumberProfile = @AuxID1
111 ,Throttle = @AuxID2
112 ,AuxID3 = @AuxID3
113 ,AuxID4 = @AuxID4
114 ,AuxID5 = @AuxID5
115 WHERE UserName = @UserName
116
117 DELETE
118 FROM UserFP
119 WHERE UserName = @UserName
120
121 INSERT INTO UserFP
122 SELECT @UserName
123 ,ObjectValue
124 FROM @DigitalFP
125
126 DELETE
127 FROM UserAH
128 WHERE UserName = @UserName
129
130 INSERT INTO UserAH
131 SELECT @UserName
132 ,ObjectValue
133 FROM @AccessHistory
134
135 DELETE
136 FROM UserPN
137 WHERE UserName = @UserName
138
139 INSERT INTO UserPN
140 SELECT @UserName
141 ,ObjectValue
142 FROM @PNToken
143
144 DELETE
145 FROM UserOT
146 WHERE UserName = @UserName
147
148 INSERT INTO UserOT
149 SELECT @UserName
150 ,ObjectValue
151 FROM @OATHToken
152
153 COMMIT
154END TRY
155
156BEGIN CATCH
157 IF @@TRANCOUNT > 0
158 ROLLBACK
159END CATCH
160
161GO
162
163CREATE PROCEDURE [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
164AS
165BEGIN
166DECLARE @AuxID1 varchar(max), @AuxID2 varchar(max);
167
168IF NOT EXISTS(SELECT UserName FROM UserTable WHERE UserName = @UserName)
169 BEGIN
170 INSERT INTO UserTable (UserName)
171 VALUES (@UserName)
172 END
173
174 SELECT UserName
175 ,FirstName = ''
176 ,LastName = ''
177 ,Phone1 = ''
178 ,Phone2 = ''
179 ,Phone3 = ''
180 ,Phone4 = ''
181 ,Email1 = ''
182 ,Email2 = ''
183 ,Email3 = ''
184 ,Email4 = ''
185 ,IsNull(NumberProfile, '') AS AuxID1
186 ,IsNull(Throttle, '') AS AuxID2
187 ,IsNull(AuxID3, '') AuxID3
188 ,IsNull(AuxID4, '') AuxID4
189 ,IsNull(AuxID5, '') AuxID5
190 ,AuxID6 = ''
191 ,AuxID7 = ''
192 ,AuxID8 = ''
193 ,AuxID9 = ''
194 ,AuxID10 = ''
195 ,pinHash = ''
196 ,Questions = ''
197 ,Answers = ''
198 ,ChallengeQuestion = ''
199 ,ChallengeAnswer = ''
200 ,CertResetDate = '1/1/1900'
201 ,CertCount = 0
202 ,CertSerialNumber = ''
203 ,MobileResetDate = '1/1/1900'
204 ,MobileCount = 0
205 ,ExtSyncPwdDate = '1/1/1900'
206 ,HardwareToken = ''
207 ,iOSDevices = ''
208 ,OATHSeed = ''
209 ,OneTimeOATHList = ''
210 ,GroupList = ''
211 FROM UserTable
212 WHERE UserName = @UserName
213
214 SELECT DigitalFP
215 FROM UserFP
216 WHERE UserName = @UserName
217
218 SELECT PNToken
219 FROM UserPN
220 WHERE UserName = @UserName
221
222 SELECT AccessHistory
223 FROM UserAH
224 WHERE UserName = @UserName
225
226 SELECT OATHToken
227 FROM UserOT
228 WHERE UserName = @UserName
229END
230
231GO
232
233CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
234AS
235BEGIN
236 DECLARE @GroupList VARCHAR(1000) = '', @PwdLastSet DATETIME = '1/1/1900', @AccountStatus VARCHAR(60) = '';
237
238 IF NOT EXISTS(SELECT UserName FROM UserTable WHERE UserName = @UserName)
239 BEGIN
240 INSERT INTO UserTable (UserName)
241 VALUES (@UserName)
242 END
243
244 SELECT [UserName]
245 ,@GroupList As GroupList
246 ,@PwdLastSet As PwdLastSet
247 ,@AccountStatus As AccountStatus
248 FROM UserTable
249 WHERE UserName = @UserName;
250END
251
252
253GO