Non-root installation feature of Informix: Client perspective

Using non-root user installation with IBM Informix JDBC, ODBC, and ESQL/C drivers

Starting with IBM® Informix® 11.70xC2, Informix started supporting the non-root installation feature. This feature, sometimes called private installation, allows Informix to be installed by a user other than root and permits the software to run without user Informix or group Informix on the machine. Inside the server, Informix will be used as the owner of the system catalog, but neither user Informix nor group Informix will have any special privileges with respect to a private server. The CSDK and other installers, which are part of the Informix bundle, will also permit non-root users to install the software. This article discusses aspects of this feature in Informix and provides information about managing users using JDBC, ESQL/C, and ODBC drivers.

Kollol Kumar Misra (kolmisra@in.ibm.com), Senior Staff Software Engineer, IBM China

Photo of author Kollol MisraKollol Kumar Misra works at IBM India Lab, Bangalore. Kollol has more than seven years of IT experience. In his current role, Kollol is lead engineer for IBM Informix CSDK (Client Software Development Kit) at India Software Lab.



Mamta Sharma (mamta.sharma@in.ibm.com), Software Engineer, IBM China

Author photo of Mamta SharmaMamta Sharma works as a Software Engineer at IBM India Software Lab. She works actively toward maintaining and developing various features in Informix JDBC.



Tapan Kumar Ghosh (tapan.ghosh@in.ibm.com), Staff Software Engineer, IBM China

Photo of author Tapan GhoshTapan Kumar Ghosh works at IBM India Lab, Bangalore. Tapan has more than nine years of IT experience. In his current role, Tapan is lead engineer for IBM Informix CSDK (Client Software Development Kit) at India Software Lab.



11 July 2013 (First published 21 November 2012)

Introduction

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:

  1. Log in to the computer.
  2. To install Informix on Linux or UNIX run the media_location/ids_install file.
  3. The installation application runs in console mode by default, unless you specify GUI mode.
  4. Ensure that you select Custom setup as you follow the instructions in the installation application. Complete the installation and exit the installation application.
  5. After the installation is complete, configure the database server as you would do a conventional installation.
  6. 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.

CREATE USER

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.

  1. The USERMAPPING configuration parameter must be set in the $ONCONFIG file to enable support for mapped users.
  2. Using the DBACCESS tool connect to the sysmaster database and run the SQL statement CREATE DEFAULT USER WITH PROPERTIES USER dbsauser. This SQL statement creates a user, internally named PUBLIC, with the properties of the surrogate user dbsauser.
  3. 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 the 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

Use the 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

Use the 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

Use 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 RENAME USER SQL using JDBC driver
stmt.executeUpdate("RENAME USER 'myuser' TO 'mynewuser'");
Listing 12. Running the RENAME 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

Use the 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 driver
EXEC SQL SET USER PASSWORD OLD 'mypasswd' NEW 'mypd123' ;

For more information about SET USER PASSWORD, see SET USER PASSWORD SQL statement.


REMOTE_SERVER_CFG configuration parameter

Use the 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 about REMOTE_SERVER_CFG, see 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. The 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 situations:

  • 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

Use the 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 REMOTE_USERS_CFG configuration details.

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
Error CodeMeaning
-26700User (%s) was not found.
-26701User (%s) was not created because it already exists.
-26702User (%s) cannot connect to the database server because the user account is locked.
-26703User (%s) is not authorized to create, alter, drop, or rename users.
-26704User name (%s) exceeds the maximum length. Specify a user name not longer than 32 characters.
-26705The password specified for user (%s) is not valid. Specify a password that contains 6-32 characters.
-26706Cannot add a password to the user (%s) because a password already exists. Use the MODIFY option instead of ADD.
-26707User (%s) cannot be created because the user is not mapped to any properties.
-26708The existing password specified for user (%s) is not valid. You must specify the existing valid password before you can change it.
-26709The new password specified for user (%s) is not valid. Specify a password that contains 6-32 characters.
-26710PUBLIC is a reserved word. You cannot create, drop, alter, or rename a user with the name PUBLIC.
-26711The default user was not found.
-26712Default user was not created as it already exists.
-26713Do not specify a password while creating the default user.
-26714An internal error occurred while hashing the password. Record all circumstances prior to the error and contact IBM Software Support.
-26715Cannot alter the user (%s) because only one USER or UID property is allowed.
-26716Cannot 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 ALTER operation. Note all circumstances and contact IBM Software Support.
-26718Cannot alter the user (%s) to add a home directory because the property value already exists. Use MODIFY instead of ADD.
-26719The ALTER statement specified an incorrect authorization value (%s).
-26720Cannot change a property value more than once in the same ALTER statement.
-26721Cannot drop the password for the user (%s) because the password is specified. Do not include a value for the PASSWORD property with the DROP option.
-26722The surrogate user name (%s) exceeds the maximum length of 32 characters. Specify a user name that has 32 characters or fewer.
-26723The value of the surrogate property HOMEDIR exceeds the maximum length. Specify a value that is less that 255 bytes.
-26724Cannot drop the HOMEDIR property for the user (%s) because a value is specified. Do not include a value for the HOMEDIR property with the DROP option.
-26725Cannot drop the UID property for the user (%s) because a value is specified. Do not include a value for the UID property with the DROP option.
-26726Cannot drop the USER property for the user (%s) because a value is specified. Do not include a value for the USER property with the DROP option.
-26727The SQL statement cannot assign operating system properties to the user (%s).
-26732The USERMAPPING feature is disabled. The USERMAPPING configuration parameter must be set to BASIC or ADMIN.

Conclusion

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.


Downloads

DescriptionNameSize
Sample JDBC demo appJdbcDemo.zip2KB
Sample ODBC demo appOdbcDemo.zip1KB
Sample ESQL/C demo appEsqlcDemo.zip1KB

Resources

Learn

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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=846598
ArticleTitle=Non-root installation feature of Informix: Client perspective
publish-date=07112013