Hiding internal user ID from JWT token

Question

There is a REST API Service under development, that provides different search methods over an internal SQL database for user, that makes a request. The database contains several tables, and each of them has a user_id column, which is a reference key to the id column in the User table (unique integer number, primary key). This is important.

When the User logins from the client app, he obtains the JWT token, which contains the "sub" section with the user ID value. Then when the User calls one of the API methods, he just provides the JWT token, so API gets the User ID from "sub" and makes a search request to the appropriate table. Cause each table has a user_id column, there is no needs to make a join on the User table in SQL queries (if we do not need user information in the response).

The problem is that exposing the internal User ID is a bad practice, that can cause security/business issues. So I am looking into hiding it. Right now I see the following options:

• change the User ID into a string column (using sequential GUID), but this is a huge breaking change for our system

• extant User table with additional ext_user_id column (GUID), and use this value in JWT token. The disadvantage is after that each SQL query will have "join" to the User table to obtain the user id value.

• leave the database as is, but use the JWE token instead of JWT. The disadvantage is that each API request will do token decryption, which may affect performance.

Maybe there is another option? Or does one of these approaches have more advantage, over others (actually except first one)?

Answer 1

Basically I think those are the choices you have.

You are focused on performance. With solution 2 and 3 you have to compare the overhead of a SQL query/join to get the user ID with the decryption of the JWE token.

JWE decryption time will always be basically the same, with a symmetric key probably a few milliseconds of processor time. However, a database querye requires disk access and depend on other factors: number of records, number of joins, disk access time, etc. It is not possible to give you an absolute measure without knowing the problem in detail. I suggest you to empirically measure the overhead of each case and assess whether it is a problem for you

In addition to performance I suggest valuing collateral problems by hiding the ID:

Will you need that the client knows the ID in your REST API?

For example if you have a resource that uses the ID like this   

/user/{userId}/getSomeData

JWE will not be useful, because you need to provide an identifier in any way

Do you need that a client decodes JWT?

If you use JWE, the client can not decode because it does not have the decryption key

How many places in your server you need to convert the GUID to ID?

You will need a query for each one, or a general conversion query in a filter at entry point