The USER constant

Both Informix^® and ORACLE provide the USER constant, which identifies the current user connected to the database server.

Informix:

SELECT USER FROM systables WHERE tabid=1

Oracle:

SELECT USER FROM DUAL

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).
ORACLE returns the user identifier which is stored in the database. By default ORACLE 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 ORACLE 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 creator column. The second statement will not find the row.

Solution

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

CREATE USER "username" IDENTIFIED BY pswd

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

SELECT * FROM ALL_USERS;

USERNAME     USER_ID      CREATED
------------------------------------------------------------
SYS                0      02-OCT-98
SYSTEM             5      02-OCT-98
DBSNMP            17      02-OCT-98
FBDL              20      03-OCT-98
Paul              21      03-OCT-98