Airlock IAM database with fixed-size character columns

The default Airlock IAM database schema uses the VARCHAR type for its string columns. If there are database schemas with a fixed-length string type (CHAR) on an Oracle database, Airlock IAM will not allow users to log-in.

Symptoms

Users can suddenly not log in anymore and the log messages indicate that the user was not found, although a user entry with the given username exists.

Reality check

The following conditions must be met:

  • Airlock IAM uses an Oracle Database for managing its users
  • The username column of either the admin or the user table is of type CHAR (assuming the users are stored in the medusa_user and the admins in the medusa_admin table): 
 
Example

SELECT data_type FROM all_tab_columns

  WHERE lower(table_name) IN ('medusa_admin','medusa_user')

  AND lower(column_name) = 'username'

Solutions

  • Convert username column to VARCHAR (create a backup beforehand!)
  •  
    Example

    ALTER TABLE medusa_admin MODIFY ( username VARCHAR(100) );

  • or use the “User Name Resolve Query” in the “Database User Persisters”. There the following query for users ensure that the username is padded with whitespaces to its fixed length.
  •  
    Example

    SELECT username FROM medusa_user WHERE RTRIM(username) = ?

  • The next query ensures the same for admins:

  •  
    Example

    SELECT username FROM medusa_admin WHERE RTRIM(username) = ?