Defining database users

Prior to version 11.70.xC2, Informix^® database users must be created at the operating system level and be members of the 'informix' group. Starting with 11.70.xC2, Informix supports database-only users with the CREATE USER instruction, as in most other db servers. Any database user must have sufficient privileges to connect and use resources of the database; user rights are defined with the GRANT command.

Genero db users must be registered in the database. They are created by the database administrator (SYSTEM) with the following command:

CREATE USER username IDENTIFIED BY password

or for Operating System authentication:

CREATE USER username IDENTIFIED EXTERNALLY

Note: Additional configuration is required for Operating System authentication, see database server documentation for more details.

Solution

For migration and testing purposes only, you can specify the user name and password in the FGLPROFILE.

For a live system, it is recommended that you use the CONNECT TO statement and supply the user name and password, or create database users IDENTIFIED EXTERNALLY.

Prior to Genero db 3.81, it was necessary to use quotes around user names:

CREATE USER "harry" IDENTIFIED EXTERNALLY [DEFAULT SCHEMA stores];

The quotes were necessary to force lower case, as most Linux/UNIX user names are by convention in lower case. Since the release of Genero db 3.81, when COMPATIBILTY_MODE = Informix is set in the configuration file, this is no longer needed, and should NOT be used. Instead, create the user name WITHOUT quotes:

CREATE USER harry IDENTIFIED EXTERNALLY [DEFAULT SCHEMA stores];

In this case, Genero db would internally map the lower case name to the OS user, and would use OS authentication when connecting to the database.

A few additional changes are required to make OS authentication work:

Change the ants.rhosts file's permission to 600. By default, this file has greater permission for "groups" and "other", and Genero db would refuse to load the file if the permission is something like 644. It needs to be 0 for "group" and 0 for "other". This file resides in $ANTSHOME/Server
Edit the file ants.rhosts and comment out the "+". This would allow permissions for all user names created as OS users.
Restart Genero db or execute the following SQL statement: ALTER SYSTEM REFRESHTRUST;
Remove or comment out the "user" and "password" entries in odbc.ini. The default values for these parameters are "SYSTEM" and "SYSTEM" respectively.