SQL adaptation guide For Oracle Database 9.2, 10.x, 11.x, 12x / Data manipulation |
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:
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.
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