· 9 years ago · Feb 01, 2017, 10:06 PM
1USE [master]
2GO
3
4CREATE DATABASE [UserStore] ON PRIMARY (
5 NAME = N'UserStore'
6 ,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore.mdf'
7 ,SIZE = 2048 KB
8 ,MAXSIZE = UNLIMITED
9 ,FILEGROWTH = 1024 KB
10 ) LOG ON (
11 NAME = N'UserStore_log'
12 ,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore_log.ldf'
13 ,SIZE = 1024 KB
14 ,MAXSIZE = 2048 GB
15 ,FILEGROWTH = 10 %
16 )
17GO
18
19ALTER DATABASE [UserStore]
20
21SET COMPATIBILITY_LEVEL = 100
22GO
23
24IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
25BEGIN
26 EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
27END
28GO
29
30ALTER DATABASE [UserStore]
31
32SET ANSI_NULL_DEFAULT OFF
33GO
34
35ALTER DATABASE [UserStore]
36
37SET ANSI_NULLS OFF
38GO
39
40ALTER DATABASE [UserStore]
41
42SET ANSI_PADDING OFF
43GO
44
45ALTER DATABASE [UserStore]
46
47SET ANSI_WARNINGS OFF
48GO
49
50ALTER DATABASE [UserStore]
51
52SET ARITHABORT OFF
53GO
54
55ALTER DATABASE [UserStore]
56
57SET AUTO_CLOSE OFF
58GO
59
60ALTER DATABASE [UserStore]
61
62SET AUTO_CREATE_STATISTICS ON
63GO
64
65ALTER DATABASE [UserStore]
66
67SET AUTO_SHRINK OFF
68GO
69
70ALTER DATABASE [UserStore]
71
72SET AUTO_UPDATE_STATISTICS ON
73GO
74
75ALTER DATABASE [UserStore]
76
77SET CURSOR_CLOSE_ON_COMMIT OFF
78GO
79
80ALTER DATABASE [UserStore]
81
82SET CURSOR_DEFAULT GLOBAL
83GO
84
85ALTER DATABASE [UserStore]
86
87SET CONCAT_NULL_YIELDS_NULL OFF
88GO
89
90ALTER DATABASE [UserStore]
91
92SET NUMERIC_ROUNDABORT OFF
93GO
94
95ALTER DATABASE [UserStore]
96
97SET QUOTED_IDENTIFIER OFF
98GO
99
100ALTER DATABASE [UserStore]
101
102SET RECURSIVE_TRIGGERS OFF
103GO
104
105ALTER DATABASE [UserStore]
106
107SET DISABLE_BROKER
108GO
109
110ALTER DATABASE [UserStore]
111
112SET AUTO_UPDATE_STATISTICS_ASYNC OFF
113GO
114
115ALTER DATABASE [UserStore]
116
117SET DATE_CORRELATION_OPTIMIZATION OFF
118GO
119
120ALTER DATABASE [UserStore]
121
122SET TRUSTWORTHY OFF
123GO
124
125ALTER DATABASE [UserStore]
126
127SET ALLOW_SNAPSHOT_ISOLATION OFF
128GO
129
130ALTER DATABASE [UserStore]
131
132SET PARAMETERIZATION SIMPLE
133GO
134
135ALTER DATABASE [UserStore]
136
137SET READ_COMMITTED_SNAPSHOT OFF
138GO
139
140ALTER DATABASE [UserStore]
141
142SET HONOR_BROKER_PRIORITY OFF
143GO
144
145ALTER DATABASE [UserStore]
146
147SET READ_WRITE
148GO
149
150ALTER DATABASE [UserStore]
151
152SET RECOVERY SIMPLE
153GO
154
155ALTER DATABASE [UserStore]
156
157SET MULTI_USER
158GO
159
160ALTER DATABASE [UserStore]
161
162SET PAGE_VERIFY CHECKSUM
163GO
164
165ALTER DATABASE [UserStore]
166
167SET DB_CHAINING OFF
168GO
169
170USE [UserStore]
171GO
172
173CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
174GO
175
176SET ANSI_NULLS ON
177GO
178
179SET QUOTED_IDENTIFIER ON
180GO
181
182SET ANSI_PADDING ON
183GO
184
185CREATE TABLE [dbo].[UserTable](
186 [UserName] [varchar](60) NOT NULL,
187 [Password] [varchar](60) NULL,
188 [PasswordSalt] [varchar](128) NULL,
189 [PasswordFormat] [int] NULL,
190 [PwdLastSet] [datetime] NULL,
191 [FirstName] [varchar](50) NULL,
192 [LastName] [varchar](50) NULL,
193 [Phone1] [varchar](40) NULL,
194 [Phone2] [varchar](40) NULL,
195 [Phone3] [varchar](40) NULL,
196 [Phone4] [varchar](40) NULL,
197 [Email1] [varchar](60) NULL,
198 [Email2] [varchar](60) NULL,
199 [Email3] [varchar](60) NULL,
200 [Email4] [varchar](60) NULL,
201 [AuxID1] [varchar](512) NULL,
202 [AuxID2] [varchar](512) NULL,
203 [AuxID3] [varchar](512) NULL,
204 [AuxID4] [varchar](512) NULL,
205 [AuxID5] [varchar](512) NULL,
206 [AuxID6] [varchar](512) NULL,
207 [AuxID7] [varchar](512) NULL,
208 [AuxID8] [varchar](512) NULL,
209 [AuxID9] [varchar](512) NULL,
210 [AuxID10] [varchar](512) NULL,
211 [pinHash] [varchar](120) NULL,
212 [Questions] [varchar](1000) NULL,
213 [Answers] [varchar](1000) NULL,
214 [ChallengeQuestion] [varchar](1000) NULL,
215 [ChallengeAnswer] [varchar](1000) NULL,
216 [CertResetDate] [datetime] NULL,
217 [CertCount] [int] NULL,
218 [MobileResetDate] [datetime] NULL,
219 [MobileCount] [int] NULL,
220 [ExtSyncPwdDate] [datetime] NULL,
221 [OATHSeed] [varchar](1000) NULL,
222 [OneTimeOATHList] [varchar](1000) NULL,
223 [iOSDevices] [varchar](50) NULL,
224 [HardwareToken] [varchar](50) NULL,
225 [CertSerialNumber] [varchar](1000) NULL,
226 [GroupList] [varchar](1000) NULL,
227 [AccountStatus] [varchar](50) NULL
228) ON [PRIMARY]
229
230GO
231
232CREATE TABLE [dbo].[UserFP] (
233 [UserName] [varchar](60) NOT NULL
234 ,[DigitalFP] [varbinary](max) NOT NULL
235 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
236GO
237
238CREATE TABLE [dbo].[UserPN] (
239 [UserName] [varchar](60) NOT NULL
240 ,[PNToken] [varbinary](max) NOT NULL
241 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
242GO
243
244CREATE TABLE [dbo].[UserAccessHistory] (
245 [UserName] [varchar](60) NOT NULL
246 ,[AccessHistory] [varbinary](max) NOT NULL
247 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
248GO
249
250CREATE TABLE [dbo].[UserOT] (
251 [UserName] [varchar](60) NOT NULL
252 ,[OATHToken] [varbinary](max) NOT NULL
253 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
254GO
255
256SET ANSI_NULLS ON
257GO
258
259SET QUOTED_IDENTIFIER ON
260GO
261
262CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
263 ,@FirstName VARCHAR(50)
264 ,@LastName VARCHAR(50)
265 ,@Phone1 VARCHAR(60)
266 ,@Phone2 VARCHAR(40)
267 ,@Phone3 VARCHAR(40)
268 ,@Phone4 VARCHAR(40)
269 ,@Email1 VARCHAR(60)
270 ,@Email2 VARCHAR(60)
271 ,@Email3 VARCHAR(60)
272 ,@Email4 VARCHAR(60)
273 ,@AuxID1 VARCHAR(512)
274 ,@AuxID2 VARCHAR(512)
275 ,@AuxID3 VARCHAR(512)
276 ,@AuxID4 VARCHAR(512)
277 ,@AuxID5 VARCHAR(512)
278 ,@AuxID6 VARCHAR(512)
279 ,@AuxID7 VARCHAR(512)
280 ,@AuxID8 VARCHAR(512)
281 ,@AuxID9 VARCHAR(512)
282 ,@AuxID10 VARCHAR(512)
283 ,@pinHash VARCHAR(120)
284 ,@Questions VARCHAR(1000)
285 ,@Answers VARCHAR(1000) = ''
286 ,@ChallengeQuestion VARCHAR(1000) = ''
287 ,@ChallengeAnswer VARCHAR(1000) = ''
288 ,@CertResetDate DATETIME
289 ,@CertSerialNumber VARCHAR(1000)
290 ,@CertCount INTEGER
291 ,@MobileResetDate DATETIME
292 ,@MobileCount INTEGER
293 ,@ExtSyncPwdDate DATETIME
294 ,@HardwareToken VARCHAR(1000)
295 ,@iOSDevices VARCHAR(1000)
296 ,@OATHSeed VARCHAR(MAX)
297 ,@OneTimeOATHList VARCHAR(1000)
298 ,@DigitalFP dbo.ObjectTable READONLY
299 ,@PNToken dbo.ObjectTable READONLY
300 ,@AccessHistory dbo.ObjectTable READONLY
301 ,@OATHToken dbo.ObjectTable READONLY
302AS
303IF NOT EXISTS (
304 SELECT UserName
305 FROM UserTable
306 WHERE UserName = @UserName
307 )
308BEGIN
309 INSERT INTO UserTable (UserName) VALUES (@UserName)
310END
311 BEGIN TRY
312 BEGIN TRANSACTION
313
314 UPDATE UserTable
315 SET FirstName = @FirstName
316 ,LastName = @LastName
317 ,Phone1 = @Phone1
318 ,Phone2 = @Phone2
319 ,Phone3 = @Phone3
320 ,Phone4 = @Phone4
321 ,Email1 = @Email1
322 ,Email2 = @Email2
323 ,Email3 = @Email3
324 ,Email4 = @Email4
325 ,AuxID1 = @AuxID1
326 ,AuxID2 = @AuxID2
327 ,AuxID3 = @AuxID3
328 ,AuxID4 = @AuxID4
329 ,AuxID5 = @AuxID5
330 ,AuxID6 = @AuxID6
331 ,AuxID7 = @AuxID7
332 ,AuxID8 = @AuxID8
333 ,AuxID9 = @AuxID9
334 ,AuxID10 = @AuxID10
335 ,pinHash = @pinHash
336 ,Questions = @Questions
337 ,Answers = @Answers
338 ,ChallengeQuestion = @ChallengeQuestion
339 ,ChallengeAnswer = @ChallengeAnswer
340 ,CertResetDate = @CertResetDate
341 ,CertCount = @CertCount
342 ,CertSerialNumber = @CertSerialNumber
343 ,MobileResetDate = @MobileResetDate
344 ,MobileCount = @MobileCount
345 ,ExtSyncPwdDate = @ExtSyncPwdDate
346 ,HardwareToken = @HardwareToken
347 ,iOSDevices = @iOSDevices
348 ,OATHSeed = @OATHSeed
349 ,OneTimeOATHList = @OneTimeOATHList
350 WHERE UserName = @UserName
351
352 --- Update Fingerprints ---
353 DELETE
354 FROM UserFP
355 WHERE UserName = @UserName
356
357 INSERT INTO UserFP
358 SELECT @UserName
359 ,ObjectValue
360 FROM @DigitalFP
361 ---------------------------
362
363 --- Update Push Notification ---
364 DELETE
365 FROM UserPN
366 WHERE UserName = @UserName
367
368 INSERT INTO UserPN
369 SELECT @UserName
370 ,ObjectValue
371 FROM @PNToken
372 ---------------------------
373
374 --- Update Access History ---
375 DELETE
376 FROM UserAccessHistory
377 WHERE UserName = @UserName
378
379 INSERT INTO UserAccessHistory
380 SELECT @UserName
381 ,ObjectValue
382 FROM @AccessHistory
383 ---------------------------
384
385 --- Update OATH Token ---
386 DELETE
387 FROM UserOT
388 WHERE UserName = @UserName
389
390 INSERT INTO UserOT
391 SELECT @UserName
392 ,ObjectValue
393 FROM @OATHToken
394 ---------------------------
395 COMMIT
396 END TRY
397
398 BEGIN CATCH
399 IF @@TRANCOUNT > 0
400 ROLLBACK
401 END CATCH
402GO
403
404SET ANSI_NULLS ON
405GO
406
407SET QUOTED_IDENTIFIER ON
408GO
409
410CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
411AS
412BEGIN
413 SELECT UserName
414 ,IsNull(FirstName, '') FirstName
415 ,IsNull(LastName, '') LastName
416 ,IsNull(Phone1, '') Phone1
417 ,IsNull(Phone2, '') Phone2
418 ,IsNull(Phone3, '') Phone3
419 ,IsNull(Phone4, '') Phone4
420 ,IsNull(Email1, '') Email1
421 ,IsNull(Email2, '') Email2
422 ,IsNull(Email3, '') Email3
423 ,IsNull(Email4, '') Email4
424 ,IsNull(AuxID1, '') AuxID1
425 ,IsNull(AuxID2, '') AuxID2
426 ,IsNull(AuxID3, '') AuxID3
427 ,IsNull(AuxID4, '') AuxID4
428 ,IsNull(AuxID5, '') AuxID5
429 ,IsNull(AuxID6, '') AuxID6
430 ,IsNull(AuxID7, '') AuxID7
431 ,IsNull(AuxID8, '') AuxID8
432 ,IsNull(AuxID9, '') AuxID9
433 ,IsNull(AuxID10, '') AuxID10
434 ,IsNull(pinHash, '') pinHash
435 ,IsNull(Questions, '') Questions
436 ,IsNull(Answers, '') Answers
437 ,IsNull(ChallengeQuestion, '') ChallengeQuestion
438 ,IsNull(ChallengeAnswer, '') ChallengeAnswer
439 ,IsNull(CertResetDate, '1/1/1900') CertResetDate
440 ,IsNull(CertCount, 0) CertCount
441 ,IsNull(CertSerialNumber, '') CertSerialNumber
442 ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
443 ,IsNull(MobileCount, 0) MobileCount
444 ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
445 ,IsNull(HardwareToken, '') HardwareToken
446 ,IsNull(iOSDevices, '') iOSDevices
447 ,IsNull(OATHSeed, '') OATHSeed
448 ,IsNull(OneTimeOATHList, '') OneTimeOATHList
449 ,IsNull(GroupList, '') GroupList
450 FROM UserTable
451 WHERE UserName = @UserName
452
453 SELECT DigitalFP
454 FROM UserFP
455 WHERE UserName = @UserName
456
457 SELECT PNToken
458 FROM UserPN
459 WHERE UserName = @UserName
460
461 SELECT AccessHistory
462 FROM UserAccessHistory
463 WHERE UserName = @UserName
464
465 SELECT OATHToken
466 FROM UserOT
467 WHERE UserName = @UserName
468END
469GO
470
471SET ANSI_NULLS ON
472GO
473
474SET QUOTED_IDENTIFIER ON
475GO
476
477CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
478AS
479BEGIN
480 SELECT [UserName]
481 ,ISNULL([GroupList], '')
482 ,ISNULL([PwdLastSet],'1/1/1900')
483 ,ISNULL([AccountStatus], '')
484 FROM UserTable
485 WHERE UserName = @UserName
486END
487GO
488
489SET ANSI_NULLS ON
490GO
491
492SET QUOTED_IDENTIFIER ON
493GO
494
495
496CREATE PROC [dbo].[sp_CreateUser]
497@UserName VARCHAR(60),
498@Password VARCHAR(60),
499@PasswordSalt VARCHAR(128),
500@PasswordFormat int,
501@Status int OUTPUT
502AS
503BEGIN
504INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [PwdLastSet]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, GetDate())
505
506IF @@ROWCOUNT > 0
507SELECT @Status = 0
508ELSE
509SELECT @Status = 1
510END
511GO
512
513SET ANSI_NULLS ON
514GO
515
516SET QUOTED_IDENTIFIER ON
517GO
518
519CREATE PROC [dbo].[sp_ResetPassword]
520@UserName VARCHAR(60),
521@Password VARCHAR(60),
522@PasswordSalt VARCHAR(128),
523@PasswordFormat int
524AS
525BEGIN
526 UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
527 IF @@ROWCOUNT > 0
528 BEGIN
529 SELECT 1
530 END
531END
532
533GO
534SET ANSI_NULLS ON
535GO
536
537SET QUOTED_IDENTIFIER OFF
538GO
539
540CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
541 @UserName varchar(60)
542AS
543BEGIN
544 SELECT [Password], PasswordFormat, PasswordSalt
545 FROM dbo.UserTable
546 WHERE @UserName = UserName
547END
548GO
549
550CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
551CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
552CREATE ROLE db_serviceaccount
553EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
554GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
555GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
556GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
557GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
558GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
559GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
560GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount