Setting privileges

Informix^® and ORACLE user privileges management are quite similar.

ORACLE provides roles to group privileges which then can be assigned to users. Starting with version 7.20, Informix provides roles also. But users must execute the SET ROLE statement in order to enable a role. ORACLE users do not have to explicitly set a role, they are assigned to a default privilege domain (set of roles). More than one role can be enabled at a time with ORACLE.

Informix users must have at least the CONNECT privilege to access the database:

GRANT CONNECT TO (PUBLIC|username)

ORACLE users must have at least the CREATE SESSION privilege to access the database. This privilege is part of the CONNECT role.

GRANT CONNECT TO (PUBLIC|username)

Informix database privileges do NOT correspond exactly to ORACLE CONNECT, RESOURCE and DBA roles. However, roles can be created with equivalent privileges.

Solution

Create a role which groups Informix CONNECT privileges, and assign this role to the application users:

CREATE ROLE ifx_connect IDENTIFIED BY oracle;
GRANT CREATE SESSION, ALTER SESSION, CREATE ANY VIEW, ... TO ifx_connect; 
GRANT ifx_connect TO user1;