· 9 years ago · Feb 01, 2017, 09:02 PM
1USE [SecureAuthProdDB]
2GO
3
4/****** Object: Table [dbo].[UserTable] Script Date: 2/1/2017 12:57:49 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 CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED
19(
20 [UserName] ASC
21)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
22) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
23
24GO
25
26
27USE [SecureAuthProdDB]
28GO
29
30/****** Object: Table [dbo].[UserPN] Script Date: 2/1/2017 12:57:45 PM ******/
31SET ANSI_NULLS ON
32GO
33
34SET QUOTED_IDENTIFIER ON
35GO
36
37CREATE TABLE [dbo].[UserPN](
38 [UserName] [varchar](60) NOT NULL,
39 [PNToken] [varbinary](max) NOT NULL
40) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
41
42GO
43
44ALTER TABLE [dbo].[UserPN] WITH CHECK ADD CONSTRAINT [FK_UserPN_UserTable] FOREIGN KEY([UserName])
45REFERENCES [dbo].[UserTable] ([UserName])
46GO
47
48ALTER TABLE [dbo].[UserPN] CHECK CONSTRAINT [FK_UserPN_UserTable]
49GO
50
51
52USE [SecureAuthProdDB]
53GO
54
55/****** Object: Table [dbo].[UserOT] Script Date: 2/1/2017 12:57:38 PM ******/
56SET ANSI_NULLS ON
57GO
58
59SET QUOTED_IDENTIFIER ON
60GO
61
62CREATE TABLE [dbo].[UserOT](
63 [UserName] [varchar](60) NOT NULL,
64 [OATHToken] [varbinary](max) NOT NULL
65) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
66
67GO
68
69ALTER TABLE [dbo].[UserOT] WITH CHECK ADD CONSTRAINT [FK_UserOT_UserTable] FOREIGN KEY([UserName])
70REFERENCES [dbo].[UserTable] ([UserName])
71GO
72
73ALTER TABLE [dbo].[UserOT] CHECK CONSTRAINT [FK_UserOT_UserTable]
74GO
75
76
77USE [SecureAuthProdDB]
78GO
79
80/****** Object: Table [dbo].[UserFP] Script Date: 2/1/2017 12:57:33 PM ******/
81SET ANSI_NULLS ON
82GO
83
84SET QUOTED_IDENTIFIER ON
85GO
86
87CREATE TABLE [dbo].[UserFP](
88 [UserName] [varchar](60) NOT NULL,
89 [DigitalFP] [varbinary](max) NOT NULL
90) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
91
92GO
93
94ALTER TABLE [dbo].[UserFP] WITH CHECK ADD CONSTRAINT [FK_UserFP_UserTable] FOREIGN KEY([UserName])
95REFERENCES [dbo].[UserTable] ([UserName])
96GO
97
98ALTER TABLE [dbo].[UserFP] CHECK CONSTRAINT [FK_UserFP_UserTable]
99GO
100
101
102USE [SecureAuthProdDB]
103GO
104
105/****** Object: Table [dbo].[UserAH] Script Date: 2/1/2017 12:57:28 PM ******/
106SET ANSI_NULLS ON
107GO
108
109SET QUOTED_IDENTIFIER ON
110GO
111
112CREATE TABLE [dbo].[UserAH](
113 [UserName] [varchar](60) NOT NULL,
114 [AccessHistory] [varbinary](max) NOT NULL
115) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
116
117GO
118
119ALTER TABLE [dbo].[UserAH] WITH CHECK ADD CONSTRAINT [FK_UserAH_UserTable] FOREIGN KEY([UserName])
120REFERENCES [dbo].[UserTable] ([UserName])
121GO
122
123ALTER TABLE [dbo].[UserAH] CHECK CONSTRAINT [FK_UserAH_UserTable]
124GO
125
126
127USE [SecureAuthProdDB]
128GO
129
130/****** Object: UserDefinedTableType [dbo].[ObjectTable] Script Date: 2/1/2017 12:59:33 PM ******/
131CREATE TYPE [dbo].[ObjectTable] AS TABLE(
132 [ObjectValue] [varbinary](max) NULL
133)
134GO
135
136
137USE [SecureAuthProdDB]
138GO
139
140/****** Object: StoredProcedure [dbo].[sp_GetUser] Script Date: 2/1/2017 12:59:45 PM ******/
141SET ANSI_NULLS ON
142GO
143
144SET QUOTED_IDENTIFIER ON
145GO
146
147
148CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
149AS
150BEGIN
151 DECLARE @GroupList VARCHAR(1000) = '', @PwdLastSet DATETIME = '1/1/1900', @AccountStatus VARCHAR(60) = '';
152
153 IF NOT EXISTS(SELECT UserName FROM UserTable WHERE UserName = @UserName)
154 BEGIN
155 INSERT INTO UserTable (UserName)
156 VALUES (@UserName)
157 END
158
159 SELECT [UserName]
160 ,@GroupList As GroupList
161 ,@PwdLastSet As PwdLastSet
162 ,@AccountStatus As AccountStatus
163 FROM UserTable
164 WHERE UserName = @UserName;
165END
166
167GO
168
169
170USE [SecureAuthProdDB]
171GO
172
173/****** Object: StoredProcedure [dbo].[sp_GetUserProfile] Script Date: 2/1/2017 1:00:21 PM ******/
174SET ANSI_NULLS ON
175GO
176
177SET QUOTED_IDENTIFIER ON
178GO
179
180
181
182CREATE PROCEDURE [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
183AS
184BEGIN
185DECLARE @AuxID1 varchar(max), @AuxID2 varchar(max);
186
187IF NOT EXISTS(SELECT UserName FROM UserTable WHERE UserName = @UserName)
188 BEGIN
189 INSERT INTO UserTable (UserName)
190 VALUES (@UserName)
191 END
192
193 SELECT UserName
194 ,FirstName = ''
195 ,LastName = ''
196 ,Phone1 = ''
197 ,Phone2 = ''
198 ,Phone3 = ''
199 ,Phone4 = ''
200 ,Email1 = ''
201 ,Email2 = ''
202 ,Email3 = ''
203 ,Email4 = ''
204 ,IsNull(NumberProfile, '') AS AuxID1
205 ,IsNull(Throttle, '') AS AuxID2
206 ,IsNull(AuxID3, '') AuxID3
207 ,IsNull(AuxID4, '') AuxID4
208 ,IsNull(AuxID5, '') AuxID5
209 ,AuxID6 = ''
210 ,AuxID7 = ''
211 ,AuxID8 = ''
212 ,AuxID9 = ''
213 ,AuxID10 = ''
214 ,pinHash = ''
215 ,Questions = ''
216 ,Answers = ''
217 ,ChallengeQuestion = ''
218 ,ChallengeAnswer = ''
219 ,CertResetDate = '1/1/1900'
220 ,CertCount = 0
221 ,CertSerialNumber = ''
222 ,MobileResetDate = '1/1/1900'
223 ,MobileCount = 0
224 ,ExtSyncPwdDate = '1/1/1900'
225 ,HardwareToken = ''
226 ,iOSDevices = ''
227 ,OATHSeed = ''
228 ,OneTimeOATHList = ''
229 ,GroupList = ''
230 FROM UserTable
231 WHERE UserName = @UserName
232
233 SELECT DigitalFP
234 FROM UserFP
235 WHERE UserName = @UserName
236
237 SELECT PNToken
238 FROM UserPN
239 WHERE UserName = @UserName
240
241 SELECT AccessHistory
242 FROM UserAH
243 WHERE UserName = @UserName
244
245 SELECT OATHToken
246 FROM UserOT
247 WHERE UserName = @UserName
248END
249
250
251GO
252
253
254USE [SecureAuthProdDB]
255GO
256
257/****** Object: StoredProcedure [dbo].[sp_UpdateUserProfile] Script Date: 2/1/2017 1:00:30 PM ******/
258SET ANSI_NULLS ON
259GO
260
261SET QUOTED_IDENTIFIER ON
262GO
263
264
265
266CREATE PROCEDURE [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
267 ,@FirstName VARCHAR(50) = ''
268 ,@LastName VARCHAR(50) = ''
269 ,@Phone1 VARCHAR(60) = ''
270 ,@Phone2 VARCHAR(40) = ''
271 ,@Phone3 VARCHAR(40) = ''
272 ,@Phone4 VARCHAR(40) = ''
273 ,@Email1 VARCHAR(60) = ''
274 ,@Email2 VARCHAR(60) = ''
275 ,@Email3 VARCHAR(60) = ''
276 ,@Email4 VARCHAR(60) = ''
277 ,@AuxID1 VARCHAR(MAX)
278 ,@AuxID2 VARCHAR(MAX)
279 ,@AuxID3 VARCHAR(MAX)
280 ,@AuxID4 VARCHAR(MAX)
281 ,@AuxID5 VARCHAR(MAX)
282 ,@AuxID6 VARCHAR(512) = ''
283 ,@AuxID7 VARCHAR(512) = ''
284 ,@AuxID8 VARCHAR(512) = ''
285 ,@AuxID9 VARCHAR(512) = ''
286 ,@AuxID10 VARCHAR(512) = ''
287 ,@pinHash VARCHAR(120) = ''
288 ,@Questions VARCHAR(1000) = ''
289 ,@Answers VARCHAR(1000) = ''
290 ,@ChallengeQuestion VARCHAR(1000) = ''
291 ,@ChallengeAnswer VARCHAR(1000) = ''
292 ,@CertResetDate DATETIME ='190011'
293 ,@CertSerialNumber VARCHAR(1000) = ''
294 ,@CertCount INT = 0
295 ,@MobileResetDate DATETIME ='1/1/1900'
296 ,@MobileCount INT = 0
297 ,@ExtSyncPwdDate DATETIME ='1/1/1900'
298 ,@HardwareToken VARCHAR(1000) = ''
299 ,@iOSDevices VARCHAR(1000) = ''
300 ,@OATHSeed VARCHAR(MAX) = ''
301 ,@OneTimeOATHList VARCHAR(1000) = ''
302 ,@DigitalFP dbo.ObjectTable READONLY
303 ,@PNToken dbo.ObjectTable READONLY
304 ,@AccessHistory dbo.ObjectTable READONLY
305 ,@OATHToken dbo.ObjectTable READONLY
306AS
307IF NOT EXISTS (
308 SELECT UserName
309 FROM UserTable
310 WHERE UserName = @UserName
311 )
312BEGIN
313 INSERT INTO UserTable (UserName)
314 VALUES (@UserName)
315END
316
317BEGIN TRY
318 BEGIN TRANSACTION
319
320 UPDATE UserTable
321 SET NumberProfile = @AuxID1
322 ,Throttle = @AuxID2
323 ,AuxID3 = @AuxID3
324 ,AuxID4 = @AuxID4
325 ,AuxID5 = @AuxID5
326 WHERE UserName = @UserName
327
328 DELETE
329 FROM UserFP
330 WHERE UserName = @UserName
331
332 INSERT INTO UserFP
333 SELECT @UserName
334 ,ObjectValue
335 FROM @DigitalFP
336
337 DELETE
338 FROM UserAH
339 WHERE UserName = @UserName
340
341 INSERT INTO UserAH
342 SELECT @UserName
343 ,ObjectValue
344 FROM @AccessHistory
345
346 DELETE
347 FROM UserPN
348 WHERE UserName = @UserName
349
350 INSERT INTO UserPN
351 SELECT @UserName
352 ,ObjectValue
353 FROM @PNToken
354
355 DELETE
356 FROM UserOT
357 WHERE UserName = @UserName
358
359 INSERT INTO UserOT
360 SELECT @UserName
361 ,ObjectValue
362 FROM @OATHToken
363
364 COMMIT
365END TRY
366
367BEGIN CATCH
368 IF @@TRANCOUNT > 0
369 ROLLBACK
370END CATCH
371
372
373GO