Non-root server installation of Informix refers to a private mode of installation that allows unprivileged users to install and run Informix without any root-level privileges associated with user Informix and group Informix. Completion of installation as a non-root user is performed by a database server administrator (DBSA). New internal user accounts for connecting to the server are created and controlled by the DBSA. A non-root installation copies or links files from your conventional installation of the database server to a directory of the user's choice. The installation script creates the necessary files and directory structure needed to run the database server in non-root database server mode. After the non-root installation is complete, critical resources and services such as the sysmaster database are owned by the user. Inside the server, the name Informix is used as the owner of the system catalog, as is the case with regular installation, but neither user Informix nor group Informix (if they exist) have any special privileges.
Advantages of non-root installation
This feature is helpful in scenarios where certain products installed with non-root privileges intend to use Informix for storage. It is more suited for running in embedded systems or in other environments where access to root privileges is limited and rigorously controlled. Finance and healthcare are some sectors where it is difficult to get access to root privileges due to tight control on the data. Non-root servers are easy to install, deploy, and maintain and work optimally for an environment that does not require high scalability. As opposed to standard installation, non-root installation eliminates the need for adding the user Informix and group Informix to the machine. A non-root installation also allows a programmer to create new user-defined functions, dynamically link them with the database server, execute them, and debug them without violating security.
Disadvantages and restrictions
A non-root installation does not require any system administration privileges or user and group Informix accounts. Although this offers ease of installation and deployment, there are features of a standard installation that will not work for the non-root server because they rely on root privileges, which are as follows:
- Replication features, such as enterprise replication (ER), High-Availability Clustering (MACH11), and High-Availability Data Replication (HDR)
- Role separation
- The OpenAdmin Tool (OAT) for Informix
- The ON-Bar utility for backing up and restoring data
- The onperf utility to monitor database server performance.
In addition, encrypted connections and column-level encryption (CLE) are supported if the required security-layer plug-in is separately installed on the host computer. You can use the IBM Global Security Kit (GSKit) installation media in $INFORMIXDIR to set up the security layer.
Also, there are some operating system features that a (root-privileged) regular server can use, whereas a non-root server without root assistance cannot use. For instance, a non-root instance cannot affinity VPs to a physical CPU; it cannot make shared memory resident (pinning); nor can it prevent process aging.
Creating a non-root installation
Follow these steps to create a non-root installation:
- Log in to the computer.
- To install Informix on Linux or UNIX run the media_location/ids_install file.
- The installation application runs in console mode by default, unless you specify GUI mode.
- Ensure that you select Custom setup as you follow the instructions in the installation application. Complete the installation and exit the installation application.
- After the installation is complete, configure the database server as you would do a conventional installation.
- While logged in as the non-root installation owner, connect to the server with DB-Access to create other database users with the CREATE USER statement.
Use of syntaxes
The DBSA of a non-root or regular installation needs new SQL statements to create, drop, and modify internally authenticated users. The following new statements will be provided to administer internally authenticated users.
After a non-root server installation, the user (i.e., dbsauser) who installed the server needs to perform the following steps to create the first internally authenticated users.
USERMAPPINGconfiguration parameter must be set in the $ONCONFIG file to enable support for mapped users.
- Using the DBACCESS tool connect to the sysmaster database and run the SQL
CREATE DEFAULT USER WITH PROPERTIES USER dbsauser. This SQL statement creates a user, internally named PUBLIC, with the properties of the surrogate user dbsauser.
- Execute the SQL statement to create the internally authenticated user
CREATE USER dbsauser WITH PASSWORD "dbsapwd";.
After the above steps, you can connect the dbsauser using the client drivers and create internally authenticated users who do not have accounts on the host system, or to map externally authenticated users to surrogate user properties required for access to Informix resources.
Listing 1. Running the create user SQL using ODBC driver
SQLExecDirect( hstmt, "create user 'myuser' with password 'mypasswd'", SQL_NTS );
Listing 2. Running the create user SQL using JDBC driver
stmt.executeUpdate( "CREATE USER 'myuser' WITH PASSWORD 'mypasswd' ");
Listing 3. Running the create user SQL using ESQL/C driver
EXEC SQL CREATE USER 'myuser' WITH PASSWORD 'mypasswd' ;
With a non-root installation, the user who installs the server is the equivalent of
the DBSA, unless the user delegates DBSA privileges to a different user. Only a DBSA
can run the
CREATE USER statement. The
USERMAPPING configuration parameter
must be set to a value that enables support for mapped users before users defined by
CREATE USER statement can connect to the database server. A DBSA can issue the
CREATE USER statement to map users to properties that correspond to the appropriate
level of authorization. For more information about the
USERMAPPING configuration parameter, see USERMAPPING configuration details. For more
information about the
CREATE USER SQL
statement, see Create user SQL statement.
ALTER USER statement to change one or more of the properties, including the
password, user ID, surrogate group, administrative authorization, and home
directory, and to enable or disable the account of an internally authenticated user,
or of the default internally authenticated user.
Listing 4. Running the
ALTER USER SQL using ODBC driver
SQLExecDirect( hstmt, "alter user 'myuser' modify password 'newpasswd' ", SQL_NTS );
Listing 5. Running the
ALTER USER SQL using JDBC driver
stmt.executeUpdate("ALTER USER 'myuser' MODIFY PASSWORD 'newpasswd'");
Listing 6. Running the
ALTER USER SQL using ESQL/C driver
EXEC SQL ALTER USER 'myuser' MODIFY PASSWORD 'newpasswd' ;
For more information about
ALTER USER, see
ALTER USER SQL statement.
DROP USER statement to remove an internal user. The user entry has to be
removed from the system catalog table sysintauthusers in the sysusers database. It
is not advisable to drop the user ID if it is still active on a connection,
but doing so will prevent any reconnection attempts by the user.
Listing 7. Running the
DROP USER SQL using ODBC driver
SQLExecDirect( hstmt, "drop user 'myuser' ", SQL_NTS );
Listing 8. Running the
DROP USER SQL using JDBC driver
stmt.executeUpdate("DROP USER 'mynewuser'");
Listing 9. Running the
DROP USER SQL using ESQL/C driver
EXEC SQL DROP USER 'myuser' ;
For more information about
DROP USER, see
DROP USER SQL statement.
RENAME USER to change the name of an internal user of a non-root
installation of the database server. The user entry has to be updated in the system
catalog table sysintauthusers in the sysusers database. It it not advisable to
rename the user ID if it is active on a connection. This operation does not
transfer any privileges granted to the old user name to the new user name. The new
name may not already exist.
Listing 10. Running the
RENAME USER SQL using ODBC driver
SQLExecDirect( hstmt, "rename user 'myuser' to 'mynewuser'", SQL_NTS );
Listing 11. Running the
USER SQL using JDBC driver
stmt.executeUpdate("RENAME USER 'myuser' TO 'mynewuser'");
Listing 12. Running the
USER SQL using ESQL/C driver
EXEC SQL RENAME USER 'myuser' TO 'mynewuser' ;
For more information about
RENAME USER, see
RENAME USER SQL statement.
SET USER PASSWORD
SET USER PASSWORD SQL statement to change your password for database server
access if you are an internally authenticated user.
Listing 13. Running the
SET USER PASSWORD password SQL using ODBC driver
SQLExecDirect( hstmt, " set user password old 'mypasswd' new 'mypd123'", SQL_NTS );
Listing 14. Running the
SET USER PASSWORD password SQL using JDBC driver
stmt.executeUpdate("SET USER PASSWORD OLD 'mypasswd' NEW 'mypd123'");
Listing 15. Running the
SET USER PASSWORD password SQL using ESQL/C
EXEC SQL SET USER PASSWORD OLD 'mypasswd' NEW 'mypd123' ;
For more information about
SET USER PASSWORD,
SET USER PASSWORD SQL
REMOTE_SERVER_CFG configuration parameter
REMOTE_SERVER_CFG parameter to specify the file that lists
trusted remote hosts on which the database server resides. The file specified by the
REMOTE_SERVER_CFG parameter must be located in $INFORMIXDIR/etc. If
the configuration parameter is set, the specified file is used instead of the
$INFORMIXDIR/etc/hosts.equiv file. If the specified file does not exist in
$INFORMIXDIR/etc, client host names cannot be read. For more information
REMOTE_SERVER_CFG configuration details.
Trusted host information
The database server can be configured to use the /etc/hosts.equiv or .rhosts files
for remote authentication. To use trusted-host information for
authentication, specify the s=1 or s=3 options in the sqlhosts file or
the Windows® SQLHOSTS registry key entry. If you do not specify
an s option, s=3 is the default. You
can configure the database server to use a different file for remote authentication.
REMOTE_SERVER_CFG configuration parameter can specify an alternative file to use
instead of the hosts.equiv file. This alternative file is necessary in the following
- You need different trusted hosts for the database server from those listed for the OS.
- The security policies at your installation do not allow the use of hosts.equiv.
- You are a user of a non-root server instance and need to control which hosts are trusted.
The trusted-hosts file refers to either the hosts.equiv file or the file specified by the
REMOTE_SERVER_CFG configuration parameter. For more
information, see Trusted host details.
REMOTE_USERS_CFG configuration parameter
REMOTE_USERS_CFG parameter to specify the file that lists the
names of trusted users that exist on remote hosts. The file specified by the
REMOTE_USERS_CFG configuration parameter must be located in $INFORMIXDIR/etc. If the
configuration parameter is set, the file specified is used instead of the
~/.rhosts file. If the specified file does not exist in $INFORMIXDIR/etc,
authentication will fail. For more information, see
Trusted user information
In the user's .rhosts file, the user can list hosts from which he can connect as a trusted user. The .rhosts file is in the user's home directory on the computer housing the database server. To enable the trusted user authentication, specify s=2 or s=3 in the options in the sqlhosts file or the Windows SQLHOSTS registry key entry. If you do not specify an s option, s=3 is the default. There may be reasons why a user's .rhosts file cannot be used. For example, a non-root installation might not have read access to a specific user's .rhosts file. You can specify an alternate filename by setting the REMOTE_USERS_CFG configuration parameter. If you set this parameter, the database server only has a single trusted-user file for all users. For more information, see Trusted user details.
Connections to a non-root installation
A non-root installation of a database server runs without user root or user Informix privileges. A DBSA who installs the database server without user root privileges performs a non-root installation. The DBSA creates and controls the other user accounts that can connect to the server. The non-root server cannot authenticate users based on system calls to the OS level. The DBSA of the installation must create internal users to grant database server access to other users. For more information, see non-root installation.
Shared memory and stream pipe connections to a non-root installation
You must include the cfd option in the sqlhosts file to use shared-memory and stream-pipe connections on servers that have a non-root installation of Informix. Use the cfd option in the sqlhosts file to set the directory where you want to store the communication files necessary for shared-memory and stream-pipe connections. Non-root installations of Informix now store communication files in the $INFORMIXDIR/etc directory by default. Clients connecting to the server now check the $INFORMIX/etc directory if communication files are not found in the /INFORMIXTMP directory. Non-root installations of the database server do not have permission to write to the /INFORMIXTMP directory, so shared-memory and stream-pipe connection communication files are not written to the $INFORMIXDIR/etc directory if no communication files directory is specified as an option in the sqlhosts file.
Error messages with respect to the SQL syntax
Following are the native error codes and their meanings returned in case there is any failure happens while executing the SQL syntax.
Table 1. Error codes and their meanings
|-26700||User (%s) was not found.|
|-26701||User (%s) was not created because it already exists.|
|-26702||User (%s) cannot connect to the database server because the user account is locked.|
|-26703||User (%s) is not authorized to create, alter, drop, or rename users.|
|-26704||User name (%s) exceeds the maximum length. Specify a user name not longer than 32 characters.|
|-26705||The password specified for user (%s) is not valid. Specify a password that contains 6-32 characters.|
|-26706||Cannot add a password to the user (%s) because a password already exists.
Use the |
|-26707||User (%s) cannot be created because the user is not mapped to any properties.|
|-26708||The existing password specified for user (%s) is not valid. You must specify the existing valid password before you can change it.|
|-26709||The new password specified for user (%s) is not valid. Specify a password that contains 6-32 characters.|
|-26710||PUBLIC is a reserved word. You cannot create, drop, alter, or rename a user with the name PUBLIC.|
|-26711||The default user was not found.|
|-26712||Default user was not created as it already exists.|
|-26713||Do not specify a password while creating the default user.|
|-26714||An internal error occurred while hashing the password. Record all circumstances prior to the error and contact IBM Software Support.|
|-26715||Cannot alter the user (%s) because only one USER or UID property is allowed.|
|-26716||Cannot alter the user to add groups because the number of groups would exceed the maximum limit (%s).|
|-26717|| An internal error occurred while performing an |
|-26718||Cannot alter the user (%s) to add a home directory because the property
value already exists. Use |
|-26720||Cannot change a property value more than once in the same |
|-26721||Cannot drop the password for the user (%s) because the password is
specified. Do not include a value for the PASSWORD property with
|-26722||The surrogate user name (%s) exceeds the maximum length of 32 characters. Specify a user name that has 32 characters or fewer.|
|-26723||The value of the surrogate property HOMEDIR exceeds the maximum length. Specify a value that is less that 255 bytes.|
|-26724||Cannot drop the HOMEDIR property for the user (%s) because a value is
specified. Do not include a value for the HOMEDIR property with
|-26725||Cannot drop the UID property for the user (%s) because a value is specified.
Do not include a value for the UID property with the |
|-26726||Cannot drop the USER property for the user (%s) because a value is
specified. Do not include a value for the USER property with the
|-26727||The SQL statement cannot assign operating system properties to the user (%s).|
|-26732||The USERMAPPING feature is disabled. The USERMAPPING configuration parameter must be set to BASIC or ADMIN.|
This article focuses on managing users from the application perspective. We have tried to demonstrate usage of the non-root server feature through simple JDBC, ODBC, and ESQL/C examples. There are sample applications available for download demonstrating the usage of users managing SQL queries in JDBC, ODBC, and ESQL/C.
|Sample JDBC demo app||JdbcDemo.zip||2KB|
|Sample ODBC demo app||OdbcDemo.zip||1KB|
|Sample ESQL/C demo app||EsqlcDemo.zip||1KB|
- Check out Informix SQL Statements to learn about the syntax and semantics of SQL statements that are recognized by Informix.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.