Concerns: Vision Database
Edition: Futurama Website
Module: Futurama Accounts
User: System administrator
- installation Futurama Vision Database
- data is imported in Futurama Vision and further processed into your application
- in the Futurama Vision Management Site the account process is configured
Description of the structure of the account table in the Futurama Vision Database where the information regarding the different accounts is stored.
After importing data into Futurama Vision, and configuring the account process, new accounts can be created. This can be done using the Futurama Vision Management Site. However it can be the situation that you already have accounts in another system and want to migrate these accounts to Futurama Vision. To do this a better understanding of the technical structure of the account tables in the Futurama Vision Database is required. The current account data have to be inserted into the account table. When migrating it is for example possible to maintain the password your users have in the old system.
In the Futurama Vision Database the account information is stored in the table Vision.Account_Persons. At the moment you do a synchronization in order to create accounts (see the paragraph 'Create accounts') the table Vision.Account_Persons will be filled automatically with the new created accounts. This table also can be filled with a custom made script in order to migrate the current accounts. To see the structure of this table follow the next steps:
Open in SQL Server the Vision Database
Open the folder Tables and do a rightclick on the table 'Vision.Account_Persons'
Choose Script table as/INSERT to/ New Query Editor Window
Result is an INSERT script displaying all the table fields and the format of these fields
This UPDATE script can be used as basis to create script to migrate your current accounts into this table. For example to first make a (temporary) table in SQL Server with your current account information. After that you can make script to retreive information from this temporary table and insert this information in the Vision.Account_Persons table. See the next paragraph for a description of the columns in the Vision.Account_Persons table.
Below a description of all the different column in the table Vision.Account_Persons.
The unique identifier to identify a person. The combination of Login and PasswordHash is used to log in into your application. The password will be saved hashed (see below).
The hashed password of the account. See below the column 'HashingTypeCode' for the hashing method that is used to hash the password of the account.
The e-mailaddress of the account.
In the Futurama Vision Management Site (see paragraph 'Configure account process') the security questions can be defined. When creating an account a member has to select one of these secret questions. The question choses in saved in this column.
The answer to the security question mentioned above.
The code used to activate (changes to) an account. The person that wants to create or change his account will get an e-mail with an activation link. Part of this link is the activation code. This code will be compared with the activation code in the 'ActivationCode' column.
When a person changes his password, the hash of the new password is saved in the PasswordNew column. At the moment that the person has activated his account by clicking the activation link in his e-mail and by answering the security question the value in the PasswordNew column is copied to the PasswordHash column.
It is possible to automatically block the account of a person after a number of wrong attemps to log in. See 'Configure account process' for more information. In the BlockCount column the number of wrong attempts is saved.
The last try of a person to log in.
The last successful log in of a person.
The PersonID is the identifier which is used to connect the information in the Vision.Account_Persons table with information in other Vision tables. Prerequisites before inserting information in the Vision.Account_Persons table is to import your data in Futurama Vision, and process these data in your application. After these actions in the Futurama Vision Database persons are created. These persons do have an ID to identify them. This PersonID has to be inserted in the PersonID column. The value of the PersonID of your Login can be found in the table vision.Persons. Use the query below to find the PersonID for the Login in this Vision.Account_Persons table:
select * from vision.Persons
where identifier ='Login'
For Login the Login of the column Login must be used. The value of the ProductGroupID can be found in the ProductGroups tab in the Futurama Vision Management Site.
In this field the Account status is saved. An account can have different statuses. Depending on which status an account has, a person can directly log in, or has to confirm an account change, or is not allowed to log in. The description of the different statuses can be seen in the table vision.Account_STATUS. If for example the value of Account_STATUS_ID is 2, the account of the person is Activated.
Indicator which hashing type is used. The hashing type to be used can be configured in the Futurama Vision Management Site (see paragraph 'Configure account process'). HashingTypeCode=0 means HashingType MD5, HashingTypeCode=1 means HashingType SHA1, HashingTypeCode=2 means HashingType SHA1WithSalt and HashingTypeCode=3 means HashingType SHA256WithSalt. It is possible that in a conversion the original HashingType for example is SHA1, and that one wants to have HashingType SHA1WithSalt when using Futurama. This is possible. For each account the HashingType has to be set at the value of the original account (in this example 1). At the moment a person logs in, the hashing is converted to SHA1WithSalt. So for each account another HshingType can be appliacble in the Vision database.
If the HashingTypeCode is 2 or 3 (see above), a hashing salt is applicable. This hashing salt is saved in the HashingSalt column.
If you have used another authentication system to secure the access to your application and want to convert the accounts in this system to Futurama, the table Vision.Account_Persons has to be filled. In the paragraph above all the columns of Vision.Account_Persons are described. It is not necessary to fill all the columns with data. Mentioned below the columns that at least have to be filled in:
- PasswordHash: make sure the PasswordHash is in line with the value of the column HashingTypeCode
- Email: required if you set Account_STATUS_ID to 2 (see below). Otherwise optional.
- HashingTypeCode: make sure the PasswordHash is based on the HashingTypeCode you choose
- HashingSalt: if HashingTypeCode is 2 or 3
If you want your members to log in with the old password, and want to force them to first create an account within Futurama (with e-mail address, security question, answer) then set the value of Account_STATUS_ID to 0. If you want your members to log in with the old password and don't want to force them to fill in this information set the value of Account_STATUS_ID to 2. In this situation make sure you also have converted the e-mail adresses into Vision.Account_Persons.
If you have any questions about this subject or if you want to provide us feedback please send us an e-mail.