The USER constant

Both Informix^® and Genero db provide the USER constant, which identifies the current user connected to the database server. However, there is a difference:

Informix returns the user identifier as defined in the operating system, where it can be case-sensitive (UNIX™) or not (NT).
Genero db returns the user identifier that is stored in the database. By default, Genero db converts the user name to uppercase letters if you do not put the user name in double quotes when creating it.

This is important if your application stores user names in database records (for example, to audit data modifications). You can, for example, connect to Genero db with the name 'scott', and perform the following SQL operations:

(1) INSERT INTO mytab ( creator, comment )
       VALUES ( USER, 'example' );
(2) SELECT * FROM mytab
       WHERE creator = 'scott';

The first command inserts 'SCOTT' (in uppercase letters) in the author column. The second statement will not find the row.

Solution

When creating a user in Genero db, you can put double quotes around the user name in order to force Genero db to store the given user identifier as is:

CREATE USER "scott" IDENTIFIED BY password

To verify the user names defined in Genero db, connect as SYSTEM and list the records of the ALL_USERS table as follows:

 CREATE USER john IDENTIFIED BY password
 SELECT user_name FROM table_of_users
 USER_NAME
 -----------
 SYSTEM
 JOHN
 Scott