What to do with existing user tables in existing applications after introducing IdentityServer4

Question

We have several existing applications, each having its own database (and its own users table, since different application have different customers). Each application does its own login/change password/reset password/forget password etc. Now we want to build a central authentication/authorization server with IdentityServer4 where existing applications and future new applications can all use and no longer need to do functions lie login/change password/reset password/forget password for each application again and again.

So my question is what shall we do with those existing user tables? I can see two options:

1. Keep existing user tables When a customer tries to login to an application, he will be redirected to the identity server and it will look up that existing user table. When a different customer of a different applications comes, it will look up a different existing user table.

2. Merge all those existing user tables to a single user table in the new database in identity server, and delete all those existing user tables. But in those applications, after user logged in, program might still need to access those user tables. How can we solve this problem if we merge to the new table in identity server? We cannot directly access that user table in identity server, right?

I feel option 2 is the right way to go, but don't know exactly why and how. Can anyone explain to me? Thanks.

Answer 1

Here is my recommendation:

1. Create New User Table
2. Create New User to Old App Users Table
3. Populate the new user table

Create New User Table

Create a new user table with all of the common fields from your individual application user tables. Something like this example.

CREATE TABLE [USERS](
    [ID]  [bigint] IDENTITY(1,1)  NOT NULL
    [FIRST_NAME] [char](15) NOT NULL,
    [LAST_NAME] [char](30) NOT NULL,    
    [PASSWORD] [char](15) NOT NULL,
    [PHONE] [char](12) NOT NULL,
    [PHONE_EXT] [char](4) NOT NULL,
    [EMAIL] [char](50) NOT NULL,
    [STATUS] [char](1) NOT NULL,    
    [UPD_TSTAMP] [datetime] NOT NULL,
    [UPD_USER] [char](8) NOT NULL
)

If your applications have other fields that aren't common across all applications you can write code to look up those details in the corresponding application. You would not want to include that in the common table.

Create New User to Old App Users Table

This table will allow you to continue to use any special logic in your applications that could be tied to the old user ids.

The second thing this will allow you to do is to track the mapping of new user Id's to the old user ids during the migration process.

WARNING - You should make sure your existing user ids are of the same type. This example will assume that they are all bigint types.

The UserID is a Foreign Key to the Users.ID column from the first table.

CREATE TABLE [USER_APPS](
    UserID [bigint] NOT NULL,
    AppName  [char](30) NOT NULL,
    AppUserID [bigint] NOT NULL
)

Populate New User Table

When a user attempts to log in search the new table first. If there is no record then search through each application's user table to see if you find a match. If there is no match prompt the user to follow your registration process. If you find a match create a new record in the USERS and USER_APPS tables. Show the user a secondary screen and ask the user if they use one of your other applications with a different user id and password combination. If they do have another account, attempt to authenticate them. If they succeed enter a corresponding record to the USER_APPS table. You would continue this flow until the user has no more applications to log into. Once they are done ask them to update their password. As part of the update password logic, you may want to update all of the applications passwords that you have mapped in the USER_APPS table.

Follow my instructions on this question as to how you can have IdentityServer4 use an existing database to authenticate users.