Upgrade information for SQL data stores
Note
Applies to customers upgrading to the Identity Platform release 23.07 or later.
SecureAuth has improved the mobile service architecture in the Identity Platform release 21.04. The mobile service encompasses push tokens, TOTP tokens and TOTP seeds. To support this change, the mobile service references unique user values assigned to each user login. This change impacts SQL data stores. Before you can upgrade to Identity Platform release 23.07 or later, you must update the SQL data stores.
To summarize, you will need to add new columns and update the SQL stored procedures. This update is cumulative of previous updates to the SecureAuth schema and stored procedures (SP).
Intended audience
The intent of this document is for customers who have custom SQL databases. It does not apply to customers who use the unmodified SecureAuth SQL database (DB) schema and SPs. The SecureAuth global support team will apply updates to the standard SecureAuth DB using scripts built for this purpose.
This guide is for database administrators (DBAs) who want to make changes on their own.
For help with updating custom SQL databases, contact SecureAuth Support. We will engage the Project Management and Tailoring teams and work with you.
Sample SQL scripts
For customers who want to use SQL, but do not have an existing database, SecureAuth provides a "stock" schema and stored procedures. The sample SQL scripts are a reference to the SecureAuth development updates for use with the stock schema and stored procedures.
Download the sample scripts: ScriptUpgrade2307.zip
Caution
Do not run these scripts on a custom SQL database. Use these only as a reference.
Considerations
Before you can upgrade or use Identity Platform release 23.07 or later, you must update your custom SQL databases.
First, determine which data format your organization uses - standard or JSON.
Both scripts make the same functional changes, but impact different tables. Make sure to reference the correct script for your environment.
The standard script backs up the existing stored procedures, in case you need to revert. For example, you might have unknown customizations and the new stored procedures are not compatible with their non-standard system. It is up to you as a DBA to determine whether you need to do a backup.
Note
Before you implement the update, the recommendation is to take the SecureAuth IdP / Identity Platform systems out of production.
Estimated time to complete update: 1 hour
Script documentation
The script checks your custom SQL tables and stored procedures, and makes the following adjustments.
- UserTable
If it does not exist in the UserTable, the script adds the column UniqueUserId.
When the script successfully adds the column, it logs the event in Windows Event Viewer > Windows Logs > Application like this:
Event 17061, Error 60000 Severity 10 State 1| Column [UniqueUserId] was added |- UserTable
If it does not exist in the UserTable, the script adds the column CookieKeys.
When the script successfully adds the column, it logs the event in Windows Event Viewer > Windows Logs > Application like this:
Event 17061, Error 60000 Severity 10 State 1| Column [CookieKeys] was added |- sp_GetUserProfile
If sp_GetUserProfile exists, the script renames this to sp_GetUserProfile_backup_DATE or sp_GetUserProfile_backupJson_DATE (depending on script version).
The script creates a new stored procedure sp_GetUserProfile, which supports UniqueUserId.
When the script successfully renames and adds the stored procedures, it logs the event in Windows Event Viewer > Windows Logs > Application like this:
Event 17061, Error 60000 Severity 10 State 1|[sp_GetUserProfile] was renamed |or| [sp_GetUserProfileJson] was renamed |(depending on script version).If the stored procedure sp_GetUserProfile does not exist, the script creates sp_GetUserProfile, which supports UniqueUserId.
- sp_UpdateUserProfile
If sp_UpdateUserProfile exists, the script renames this to sp_UpdateUserProfile_backup_DATE or sp_UpdateUserProfile_backupJson_DATE (depending on script version).
The script creates a new stored procedure sp_UpdateUserProfile, which supports UniqueUserId.
When the script successfully renames and adds the stored procedures, it logs the event in Windows Event Viewer > Windows Logs > Application like this:
Event 17061, Error 60000 Severity 10 State 1|[sp_UpdateUserProfile] was renamed |or| [sp_UpdateUserProfileJson] was renamed |(depending on script version).If the stored procedure sp_UpdateUserProfile does not exist, the script creates sp_UpdateUserProfile, which supports UniqueUserId.
- sp_CreateUser
If sp_CreateUser exists, the script renames this to sp_CreateUser_backup_DATE or sp_CreateUser_backupJson_DATE (depending on script version).
The script creates a new stored procedure sp_CreateUser, which supports UniqueUserId.
When the script successfully renames and adds the stored procedures, it logs the event in Windows Event Viewer > Windows Logs > Application like this:
Event 17061, Error 60000 Severity 10 State 1|[sp_CreateUser] was renamed |or| [sp_CreateUserJson] was renamed |(depending on script version).If the stored procedure sp_CreateUser does not exist, the script creates sp_CreateUser, which supports UniqueUserId.
- db_serviceaccount
The script grants execute permissions to the db_serviceaccount for all new stored procedures.
This does not register an event log for this step.