Passive STS database changes

[Scott Golightly <sc...@hotmail.com>]

I wanted to provide a little more concrete example of what I have been talking about with the database changes needed for the passive STS. I have a prototype that is using a new database that I have named StonehengePasssiveSts. In the StonehengePassiveSts database I have created one table and populated it with some sample data.

 

Here is the create table statement from SQL Server Management Studio.

CREATE TABLE [dbo].[Users](
 [UserId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [UserName] [nvarchar](50) NOT NULL,
 [EncryptedPassword] [varbinary] (500) NOT NULL,
 [PasswordSalt] [varbinary] (50) NOT NULL,
 [UserEmail] [nvarchar](250) NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [Address1] [nvarchar](250) NOT NULL,
 [Address2] [nvarchar](250) NULL,
 [City] [nvarchar](50) NOT NULL,
 [StateProvince] [nvarchar](50) NOT NULL,
 [PostalCode] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_UserId]  DEFAULT (newid()) FOR [UserId]
GO


Here are some statements that I used to populate values for the first 3 users (uid:0, uid1, and uid2). (Note at this point the encrypted password and salt values are just place holders and will be replaced by real encrypted and salted versions of the xxx password later).

INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail, 
 FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User0', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary (50), '<encryptedsalt>'), 'user0@stonehenge.apache.org',
 'Sample', 'User0', '111 First Street', 'Redmond', 'WA', '98033')
GO

INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail, 
 FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User1', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary (50), '<encryptedsalt>'), 'user1@stonehenge.apache.org',
 'Sample', 'User1', '111 First Street', 'Redmond', 'WA', '98033')
GO

INSERT INTO dbo.Users(UserName, EncryptedPassword, PasswordSalt, UserEmail, 
 FirstName, LastName, Address1, City, StateProvince, PostalCode)
VALUES('User2', convert(varbinary (500), '<encryptedvalue>'), convert(varbinary (50), '<encryptedsalt>'), 'user2@stonehenge.apache.org',
 'Sample', 'User2', '111 First Street', 'Redmond', 'WA', '98033')
GO


In the existing StockTraderDB database I added a new table that I named AccountStsUser. It has just an account ID and STS user ID. Here is the corresponding data.

AccountID StsUserId
1 9DF8E4CA-BB9F-46BB-8ACD-4D6F6872B37C
5 70F44ACF-E4D4-4153-B978-4740D0B1067F
11 8B32E49F-B52B-4E39-89A7-254F628ED170

 

I hope this makes our previous discussions on the STS and mapping STS users to an account ID (called profileID in the cookie) a little clearer.

 

With this table structure all STS implementations could use the same GUID to identify the user at the STS or each STS could come up with its own unique GUID and place that value in the StsUserId table. As long as there were no GUID colissions with different AccountID values then the code will work without problem.

 

Please let me know if you have any comments or concerns about these database changes.

 

Scott Golightly