Database users

Until version 11.70.xC2, Informix^® database users must be created at the operating system level and must 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.

Oracle users can be authenticated in different manner: as database users, as operating system users or by delegating authentication to another service, like Kerberos or LDAP.

Oracle users must be created in the database with a CREATE USER command, to create a user authenticated by the database server:

CREATE USER username IDENTIFIED BY password

Oracle users can also be created with the "IDENTIFIED EXTERNALLY" clause:

CREATE USER username IDENTIFIED EXTERNALLY

In this case, ORACLE trusts the operating system to authenticate the user. See the Oracle documentation for OS user authentication configuration, especially the OS_AUTHENT_PREFIX (empty string) and REMOTE_OS_AUTHENT (true) server parameters. Note also that the Oracle user name needs to be specified in uppercase in the CREATE USER instruction, and gets an additional prefix, according to the operating system (domain name on Windows platforms)

In ORACLE, is it also possible to define users that are defined in a central LDAP directory, with the "IDENTIFIED GLOBALLY" clause:

CREATE USER username IDENTIFIED GLOBALLY AS 'distinguished_name'

Global users are registered and managed by an external LDAP service, and are identified by the distinguished name (DN).

Solution

Based on the application logic, you must create one or several ORACLE users. Use RDBMS or external authentication according to your needs. If you want to keep the same Informix OS users, you must configure Oracle for OS authentication, and create users with the IDENTIFIED EXTERNALLY option. Consider however to use real RDBMS users instead, and ask for login/password when connecting a program to Oracle.

To connect to an Oracle server from a program, use the CONNECT TO instruction. When the USER/USING clause is not specified, external authentication takes place. You can check if external or rdbms authentication takes place with the FGLSQLDEBUG output (check the line containing "Credential flag").

Tester with Oracle 11.2 on a Linux system (the Linux user login name is "sf" in lowercase):

$ sqlplys / as sysdba

SQL> show parameter os_authent_prefix;
NAME                TYPE            VALUE
----------------------------------------------
os_authent_prefix   string

SQL> show parameter remote_os_authent;
NAME                TYPE            VALUE
----------------------------------------------
remote_os_authent   boolean         TRUE

SQL> create user "SF" identified externally; 
User created.

SQL> grant connect, resource to "SF";
Grant succeeded.

To connect to Oracle as an external user declared with IDENTIFIED EXTERNALLY (authenticated by the operating system), do not specify any login/password. For example, omit the USER/USING clause in the CONNECT TO instruction:

CONNECT TO "orc1fox+driver='dbmora'"

If no db login is specified, the Oracle driver will open a database session with the OCI_CRED_EXT credentials.

An Oracle connection can also be established as SYSDBA or SYSOPER users. This is possible by specifying the following strings after the user name in the USER clause of the CONNECT TO instruction:

Table 1. Oracle connection as SYSDBA or SYSOPER
String passed to USER clause after user name	Effect as Oracle connection
`/SYSDBA`	Connection will be established as SYSDBA user.
`/SYSOPER`	Connection will be established as SYSOPER user.

Specify the user login before the /SYSDBA or /SYSOPER strings:

CONNECT TO "orc1fox+driver='dbmora'"
    USER "orauser/SYSDBA" USING "fourjs"