System Administration Certification exam 919 for Informix 11.70 prep, Part 7



Content series:

This content is part # of # in the series: System Administration Certification exam 919 for Informix 11.70 prep, Part 7

Stay tuned for additional content in this series.

This content is part of the series:System Administration Certification exam 919 for Informix 11.70 prep, Part 7

Stay tuned for additional content in this series.

Before you start

About this series

Thinking about seeking certification on System Administration for IBM Informix version 11.70 (Exam 919)? If so, you've landed in the right spot to get started. This series of IBM Informix certification preparation tutorials covers all the topics you'll need to understand before you read that first exam question. Even if you're not seeking certification right away, this set of tutorials is a great place to start learning what's new in IBM Informix 11.70.

About this tutorial

In this tutorial, you will learn how to keep your data secure by preventing unauthorized viewing and altering of data or other database objects. The material provided here primarily covers the objectives in Section 8 of the exam, entitled Security. Topics covered in this tutorial include:

  • Connection security
  • Network encryption
  • Auditing
  • Discretionary access control


After completing this tutorial, you should be able to:

  • Know the concepts and usages of authentication, authorization, and encryption
  • Allow database access to non-OS users
  • Use onaudit to set up and configure security auditing on your database server
  • Take advantage of trusted and trusted-context connections to enhance security
  • Use the privileges of a role to secure your data
  • Set up role separation to enforce access control on database server users


Informix 11.70 installation is covered in Part 1 of this tutorial series. If you haven't already done so, consider downloading and installing a copy of IBM Informix 11.70. An Informix server can help you understand many of the concepts that are tested on the System Administration for IBM Informix V11.70 Certification exam.

System requirements

You do not need a copy of IBM Informix to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of Informix Innovator-C Edition (see Related topics) to work along with this tutorial.

Understanding connection security

Database connection security protects a database server from unauthorized access by using authentication and authorization processes. As a database administrator (DBA), you can configure the Informix server authentication mechanisms to meet varying needs, such as different security methods required for local and remote connections, database access by users without operating system accounts on the server's host computer, and non-root installation. You can also authorize authenticated users to perform certain database actions or access certain database objects based on their needs. When configuring Informix authentication, a DBA should consider how user names and passwords are validated:

  • Should the user authenticate on the server machine?
  • Do any non-OS users need to connect to Informix databases?
  • What are the current security policies enforced on the system?
  • What are the authentication methods available?

In Informix 11.70, you can configure authentication in different ways:

  • Using OS for verification that the user is legitimate. This mechanism requires that a user has a valid OS user account on the Informix host computer and is granted connection privileges by the database system administrator (DBSA).
  • Using a self-defined authentication mechanism supported by Informix server, including pluggable authentication modules (PAM) on UNIX or Linux and lightweight directory access protocol (LDAP) authentication on Windows.
  • Using mapped users. This mechanism is new in Informix 11.70 to maintain security while reducing the dependency on system administrator and root-level privileges.
  • Using single sign-on (SSO) to bypass the requirement to provide user name and password after a user logs in to the client computer's operating system with Kerberos.

You can establish trusted connections through trusted-context database objects to increase system performance and security within a three-tier application model. The concept of a trusted connection through a trusted-context object is different than trusted systems by configuring hosts.equiv or .rhosts file on UNIX or Linux.

Understanding the pluggable authentication module

Pluggable authentication module (PAM) enables system administrators to implement different authentication mechanisms for different applications based on their business needs. Make sure the operating system of the Informix server supports PAM.

The PAM module determines whether a user can authenticate by providing a password, responding correctly to a challenge, or a combination of both. The modes supported by Informix 11.70 are password mode and challenge-response mode. In password mode, the user password is sufficient to satisfy the authentication. In the challenge-response mode, a server raises a challenge, and the client sends a response. A client gets access to the database only if the response is as expected. When the Informix server is configured to use PAM module for authentication, the default authentication will be ignored.

To configure an Informix server to use PAM, the system administrator must know:

  • The name of the PAM module that is typically located under the /usr/lib/security directory and its parameters are listed in /etc/pam.conf. In Linux, the /etc/pam.conf file can be replaced with a directory called /etc/pam.d, where there is a file for each PAM service. If /etc/pam.d exists, Linux will ignore /etc/pam.conf.
  • Whether the PAM module will raise a challenge in addition to accepting a simple username and password combination.

You must modify the server entry in the sqlhosts file on the database server to enable PAM authentication. If the application and server are on separate computers or in separate locations on a single machine, the sqlhosts file will need to be modified for both the client and server.

Table 1 lists the settings for PAM services in sqlhosts.

Table 1. Settings for PAM services
pam_servThe name of the PAM service that the database server is using PAM services typically are located in the /usr/lib/security directory, and parameters are listed in the /etc/pam.conf file. In Linux, the /etc/pam.conf file can be replaced with a directory called /etc/pam.d, where there is a file for each PAM service. If /etc/pam.d exists, Linux ignores /etc/pam.conf.
pamauthThe method of authentication that the PAM service uses. With this authentication mode, an application must be designed to respond to the challenge prompt correctly before connecting to the database server.pamauth=password uses the connection request password for authentication. pamauth=challenge authentication requires a correct user reply to a question or prompt.

You need to set the s option to 4 for enabling PAM services.

Listing 1 shows an sqlhosts entry that enables PAM authentication with challenge.

Listing 1. Enabling PAM authentication with challenge
#Authentication mode: challenge 
ifxserver2 oltlitcp servermc portnum2 s=4, pam_serv=(pam_pass), pamauth=(challenge)

Listing 2 shows another sqlhosts entry that enables PAM authentication that needs only a password.

Listing 2. Enabling PAM authentication with only a password challenge
#Authentication mode: password
ifxserver2 oltlitcp servermc portnum2 s=4, pam_serv=(pam_pass), pamauth=(password)

When the operating system or third-party PAM modules (shared libraries) are loaded into the informix user thread, the stack size required for the PAM modules is unpredictable. You can customize the stack size for PAM modules by setting the onconfig parameter PAM_STACKSIZE to a larger value, as shown in Listing 3.

Listing 3. Setting PAM_STACKSIZE in the onconfig file
PAM_STACKSIZE 64 # Stack size needed for the PAM modules (kilobytes)

On UNIX, the default value of PAM_STACKSIZE is 32 KB. On Linux, the default value is 128 KB plus the value of the STACKSIZE configuration parameter.

Not all IBM Informix products and tools support PAM. You can find more information about compatibility issues with authentication modules in the Informix Information Center (see Related topics).

Understanding LDAP authentication on Windows

Similar to PAM used on UNIX and Linux, LDAP can be set up and configured on Windows for different authentication mechanisms. You need to create your own LDAP authentication module for your specific LDAP authentication.

The authentication module is a DLL that usually is located in the %INFORMIXDIR%\dbssodir\lib\security directory. The parameters of the module are listed in the %INFORMIXDIR%\dbssodir\pam.conf file. The source code for a fully functional LDAP authentication module and samples of the required configuration files are included in the %INFORMIXDIR%\demo\authentication directory.

Not all IBM Informix products and tools support PAM. You can find more information about compatibility issues with authentication modules in the Informix Information Center (see Related topics).

To make an LDAP authentication module work with Informix server, you should make sure you have an LDAP server and an LDAP client-side system available. You can use IBM Directory Server or openLDAP as the LDAP client-side system.

You can use the template of the PAM configuration file to configure your LDAP module by copying the template file pam.conf_tmpl to %INFORMIXDIR%\dbssodir\etc, renaming it to pam.conf, and customizing it.

To configure a server to use an LDAP authentication support module, you must edit the sqlhosts file with the name of the LDAP module and the authentication mode. Listing 4 shows an Informix server configured to use PAM challenge authentication.

Listing 4. Using PAM challenge authentication
ifxserver1 onsoctcp servermc portnum1 s=4,pam_serv=(pam_chal),pamauth=(challenge)

Listing 5 shows an Informix server configured to use PAM password authentication.

Listing 5. Using PAM password authentication
ifxserver2 onsoctcp servermc portnum2 s=4,pam_serv=(pam_pass),pamauth=(password)

You can find more information about how to install and customize the LDAP authentication module in the Informix Information Center.

Mapping users (UNIX or Linux)

Beginning in Informix version 11.70, Informix can allow externally authenticated users to access a database server by mapping them to the appropriate user and group privileges, regardless of whether these users have local operating system accounts on the IBM Informix host computer. This feature is only available for UNIX or Linux systems. External users that are authenticated with SSO or PAM can be mapped to either of the following:

  • A UID and GID pair defined in the database server but not established as an OS account on the server host computer
  • An existing OS user account on the database server host computer

Users who obtain database access using user mapping are referred to as mapped users. Only a DBSA can create a mapped user.

When the DBSA grants database server access to externally authenticated users, the permissions that are mapped to these users are referred to as surrogate user properties. Surrogate user properties include one or more of the following:

  • User ID
  • Group ID
  • OS user name
  • Group name
  • Home directory

This mapped user functionality can help DBSAs and system administrators who do not know in advance all legitimate users who will need access to the database server.

The DBSA maps a user to surrogate user properties by running the GRANT ACCESS TO command in SQL.

Allowing connections to the database server without corresponding OS user accounts changes the default Informix configuration. The USERMAPPING configuration parameter must be set for mapped users to access the database successfully. Access is set to OFF when you create a new Informix instance or when you complete an upgrade.

Removing the dependency on a local host OS account for database server access reduces administrative work. With mapped users, the DBSA is not required to coordinate with the OS administrator to ensure that every user who should have Informix access also has an OS account. However, in many environments other considerations might warrant that Informix access still requires the presence of a user identity on the OS level of the host computer.

Before you start to create a mapped user, you must verify the following:

  • You have DBSA privileges to complete this task.
  • The users that you want to map to surrogate user properties for Informix access can externally authenticate with single sign-on (SSO) or a pluggable authentication module (PAM).
  • Informix server instance is configured to accept authentication through PAM or SSO.

Complete the following steps to create a mapped user.

  1. Set the USERMAPPING parameter of the onconfig file to one of two values:
    • BASIC: no Informix administrative privileges, regardless of which UID or GID the user is mapped to.
    • ADMIN: possible to have Informix administrative privileges. No administrative privileges are given to any users until you run the AUTHORIZATION clause of the GRANT ACCESS TO statement. Typically, if you set this parameter to ADMIN, there are only a few individual mapped users to whom you plan to grant administrative privileges.
  2. Specify surrogate user properties with the GRANT ACCESS TO statement. The statement maps externally authenticated users to the properties that enable Informix access. If you want to grant administrative privileges to a mapped user, you must include the AUTHORIZATION keyword with the value that designates the role that you want to grant the user. After you run the GRANT ACCESS TO statement, new rows are added to the user mapping tables in the SYSUSER database.

Note: Mapped users can access Informix with the surrogate user properties only if they authenticate with SSO or PAM.

Using single sign-on authentication

Single sign-on (SSO) is an authentication mechanism that bypasses the requirement to provide user name and password after a user logs in to the client computer's operating system. With SSO, authentication for the DBMS and other SSO-enabled services happens when a user first logs into the client computer (or domain, in the case of Windows). The Kerberos implementation validates the user credentials. Kerberos authentication generates a system of secret keys that store login credentials. When a user action tries to access an Informix database, an exchange of ticket-granting tickets (TKTs) allows database access without a login prompt.

SSO authentication uses both of the following open computing standards:

Generic Security Services Application Programming Interface (GSSAPI)
An API defined by Internet Engineering Task Force (IETF) standard RFC 2743 for client-server authentication
Kerberos security protocol
RFC 1510 that defines a typical key exchange mechanism. Applications can use the Kerberos service to authenticate their users and exchange cryptographic keys containing credentials.

Generic Security Services communications support module (GSSCSM) does not function with the simple password and encryption modules (SPWDCSM and ENCCSM). SSO implemented with GSSCSM supports PAM and LDAP, but it does not support mutual authentication.

For single sign-on, the user login process and authentication must employ a Kerberos 5 network infrastructure, including a dedicated Key Distribution Center computer.

Following is the high-level procedure for deploying Kerberos SSO for Informix:

  1. Configure the computers on the network to function with the Kerberos 5 authentication protocol. This involves setup of a secured computer to host the Key Distribution Center (KDC). It is possible that your network has already been set up with a Kerberos mechanism.
  2. Create client user principals and the Informix service principal in the KDC (see Related topics).
  3. Configure the sqlhosts information and GSSCSM on the computer hosting the database server.
  4. Configure the Informix service principal key and ensure it is on the computer hosting the database server.
  5. Configure a database client program that functions with GSSCSM (see Related topics).

To configure an Informix instance for SSO, you need to complete the following tasks in the Informix Information Center:

  1. Set SQLHOSTS information for SSO
  2. Set up the concsm.cfg file for SSO
  3. Ensure keytab file has the required key (UNIX and Linux)
  4. Verify Informix uses Kerberos authentication for SSO

Understanding trusted context

In a three-tier application model, all database users must log in to the middle tier with their user IDs and passwords. The middle tier will then connect to the database server with a different user ID and password. Using a trusted-context database object, you can establish trusted connections from your application to your database to achieve the following:

  • Increase system security
  • Increase overall system performance
  • Reduce maintenance overhead
  • Control user privileges
  • Preserve auditing capability of user access

A trusted connection through a trusted context object also allows an application developer to maintain his or her user ID to access a database or share a single database connection with other users.

Only a user ID that has been granted the DBSECADM role can create, alter, rename, and drop trusted context objects.

When creating a trusted context object, consider the following:

  • Which role and privileges should be assigned for users of a trusted-context object
  • Which client locations are trustable
  • Which use is required to authenticate

A context object can obtain any of the following:

  • Attributes or locations for defining a trusted connection
  • Authentication requirements for trusted-connection users
  • Roles for defining the access privileges of trusted-connection users

If a trusted-connection request matches all of a trusted-context object's attributes, the system grants a trusted connection. If a connection request contains an attribute that doesn't match the trusted-context object, the system rejects the request.

Two steps are required to establish a trusted connection through a trusted-context database object:

  1. Create a trusted context database object with the CREATE TRUSTED CONTEXT statement. You must have the database security administrator (DBSECADM) role to run this statement. Make sure that the following are true:
    • Each ATTRIBUTES, DEFAULT ROLE, ENABLE, and WITH USE clause is specified no more than once.
    • Each attribute name and corresponding value is unique.

    The example in Listing 6 creates a trusted-context object called appserver such that the current user john on a trusted connection based on appserver can be switched to two different users: joe and bob. When the current user john is switched to user joe, no authentication is required, but authentication is required when switched to user bob.

    Listing 6. Establishing a trusted connection through a trusted context database object
        USER john
            bob WITH AUTHENTICATION;
  2. Create a trusted connection in your database application. To create trusted connections, you must use an application that connects to the Informix server using TCP/IP. Local communication protocols are not supported. Make sure that the following are true:
    • The trusted-context object is enabled.
    • Your user ID has CONNECT privileges to the database.
    • Your user ID matches the primary user ID in the trusted-context object.
    • Your connection request is coming from a trusted location that is defined in the trusted-context object.

    The following APIs can be used to request trusted connections:

    • IBM Informix ESQL/C
    • IBM Informix JDBC Driver
    • IBM Informix ODBC Driver
    • IBM Data Server Driver for JDBC and SQLJ
    • IBM Data Server Provider for .NET

    The example in Listing 7 creates a trusted connection in ESQL/C by adding the TRUSTED keyword within the existing CONNECT statement.

    Listing 7. Adding the TRUSTED keyword
    EXEC SQL CONNECT TO 'database_name' TRUSTED;

Specific rules apply to switching users on a trusted connection.

Using network encryption

Encryption is the process of transforming data into an unintelligible form to prevent the unauthorized use of the data. Network encryption refers to data encryption in transit between client and server, and between server and another server.

Unencrypted data is called plain text because it is in a commonly understandable form. Encrypted data is called cipher text because it is in a secret and non-understandable form. Unencrypted plain text can be converted to encrypted cipher text using an encryption-decryption algorithm or cipher. Ciphers can be categorized based on the type of the encryption key used in the cryptographic algorithm:

Private key cryptography
One key is used for both encryption and decryption. Also known as symmetric cryptography.
Public key cryptography
A key used for encryption is different than the key used for decryption. Also known as asymmetric cryptography.

Informix supports only symmetric cryptography for network data encryption.

You can configure Informix server with one of the following two network encryption options:

  • Integrated communication support modules (CSMs), which is a complete data encryption with a standard cryptography library.
  • Secure Sockets Layer (SSL), which is a communication protocol that uses encryption for data communication through a reliable, end-to-end, secure connection.

SSL is a more widely used alternative to the IBM Informix CSMs. You can use SSL for encrypted communication with both DRDA and SQLI clients, but you can use the CSMs only for connections with SQLI clients.

You can also configure the CSMs with SSL connections. However, there will be no extra benefit from the redundant CSMs encryption functionality that involves additional configuring effort.

You can configure pluggable authentication module (PAM) and the generic security services communications support module (GSSCSM), which uses the Kerberos 5 security protocol for single sign-on (SSO) with SSL connections.

Understanding CSM encryption

The communication support modules (CSMs) encryption option in Informix provides complete data encryption with a standard cryptography library and many configurable options. A message authentication code (MAC) is transmitted as part of the encrypted data transmission to ensure data integrity. You can use the CSMs to encrypt data transmissions, including distributed queries, over the network.

CSMs have the following restrictions:

  • An encryption CSM cannot be used with a simple password CSM simultaneously.
  • You cannot use any CSM over a multiplexed connection.
  • Enterprise replication and high-availability clusters cannot use a connection configured with a CSM.
  • Encrypted connections and unencrypted connections cannot be combined on the same port.

Enabling data encryption with CSMs for Informix server requires the following high-level steps:

  1. Locate the concsm.cfg file to insert entries for describing the CSMs used for data encryption. The oncsm.cfg file is in $INFORMIXDIR/ect by default, but you can put it anywhere else on the host and set the INFORMIXCONCSMCFG environment variable to the full path name of the new location.

    Create an oncsm.cfg file if it does not exist. You can use the examples in $INFORMIXDIR/ect/concsm.example.

  2. Add an entry to the concsm.cfg file for the CSM you are going to use. The entry specifies encryption libraries and encryption options to be used. You must specify which ciphers and mode to use in the encryption options. Each entry must conform to the following restrictions:
    • The following characters are not allowed to be part of the library path names:
      • = (equal sign)
      • " (double quotation mark)
      • , (comma)
    • White spaces cannot be used unless the white spaces are part of a path name.
    • The length is limited to 1024 bytes.

    You can specify the following types of encryption options:

    • DES and AES ciphers to use during encryption
    • Modes to use during encryption
    • Message authentication code (MAC) key files
    • MAC levels
    • Switch frequency for ciphers and keys

    You can use either one of the following methods to specify encryption options.

    Invoke an encryption parameters file in concsm.cfg
    This is the simpler method. Listing 8 shows an encryption parameter file that states to use all available ciphers for this connection and also to switch the cipher being used every 120 minutes and renegotiate the secret key every 60 minutes.
    Listing 8. Invoking the encryption parameters file
    ENCCSM_SWITCH 120,60 
    ENCCSM_MAC medium 
    ENCCSM_MACFILES /usr/informix/etc/MacKey.dat

    Listing 9 illustrates a line in the concsm.cfg file to specify encryption with a parameter file named encrypt.txt:

    Listing 9. Specifying encryption with the encrypt.txt file.
    Use encryption tags in concsm.cfg
    Listing 10 shows an entry using encryption tags in oncsm.cfg file that uses all available ciphers except for any of the Blowfish ciphers does not use any cipher in ECB mode.
    Listing 10. Using encryption tags in concsm.cfg
  3. Generate a MAC key file that contains encryption keys to be used to encrypt messages. The same MAC key file is required to enable data encryption between Informix client and server. The default MAC key file is the built-in file provided by IBM Informix. This file provides limited message verification (some validation of the received message and confirmation that it has come from an IBM Informix client or server). A site-generated MAC key file performs the strongest verification. You can generate key files with the GenMacKey utility. Listing 11 generates a MAC key file named IfxMacKey.dat.

    Listing 11. Creating a MAC key file
    cd $INFORMIXDIR/etc 
    GenMacKey -o IfxMacKey.dat

    If you don't specify the key file name, GenMacKey will use the default name MacKey.dat. You need to transfer the MAC key file generated to the remote client machine using secure copy or secure FTP. The GenMacKey utility prioritizes each of the MAC key files based on its creation time. The built-in key file has the lowest priority. If you don't have MAC key files present, the built-in MAC key is used by default. However, by using a MAC key file, the default built-in MAC key is disabled.

  4. Enable the CSM in the options column of the sqlhosts file or registry. The format of the CSM option in the sqlhosts file is csm=(name,options). The value of the name must match a name entry in the concsm.cfg file. CSM options defined in the sqlhosts file override options specified in the concsm.cfg file. CSM encryption options cannot be specified in the sqlhosts file. If you do not specify the csm option, the database server uses the default authentication policy for that database server. Table 2 shows the available CSM options.

    Note: The s=7 option is deprecated and is not required for the single sign-on (SSO) CSM.

Table 2. CSM options for the sqlhosts file
pPasswordThis option is available as part of the simple password CSM, which provides password encryption
  • p=0 password is not required (default)
  • p=1 password is required
cConfidentiality serviceData transmitted to and from the SSO-authenticated user is encrypted and can be viewed only by the user logged in with the authorized credentials. This option is available as part of the generic security services CSM, which supports single sign-on (SSO).
  • c=1 enables the service (default)
  • c=0 disables the service
iIntegrity serviceEnsures that data sent between user and the DBMS is not altered during transmission. This option is available as part of the generic security services CSM, which supports single sign-on (SSO).
  • i=1 enables the service (default)
  • i=0 disables the service

Listing 12 shows that the Informix server ids1170fc4_encrypt is configured to use CSM ENCCSM, as specified in the oncsm.cfg file.

Listing 12. Using the ENCCSM file
Ids1170fc4_encrypt onsoctcp 9889 csm=(ENCCSM)

Where ENCCSM is the name specified for the CSM in the oncsm.cfg file.

Understanding SSL encryption

The secure sockets layer (SSL) protocol is a communication protocol that uses encryption to provide privacy and integrity for data communication through a reliable, end-to-end, secure connection between two points over a network.

The SSL protocol provides the following advantages over the Informix communication support modules (CSMs):

  • SSL is a more widely used alternative to the IBM Informix CSMs.
  • You can use SSL for encrypted communication with both DRDA and SQLI clients.

You can use the SSL protocol for all types of connections, including client-to-server connections and server-to-server connections.

SSL uses digital certificates to exchange keys for encryption and server authentication. The trusted entity that issues a digital certificate is known as a certificate authority (CA). The CA issues a digital certificate for only a limited time. When the expiration date passes, you must acquire another digital certificate.

SSL uses a symmetric key (secret or private key) algorithm for data encryption, but it uses an asymmetric key (public key) algorithm for the exchange of the secret keys in the symmetric algorithm.

Typically, establishing a new SSL connection involves the following two steps:

  1. Hand shake to establish a secure connection by the client's validating the digital certificate received from the server
  2. Cipher exchange: the client generates a random symmetric key and sends it to the server

Once those two steps are complete successfully, the client and server encrypt data for the duration of the session.

Informix uses a keystore, which is a protected database, to store SSL keys and digital certificates. Both the client and server must have the keystore. The server keystore stores its digital certificate and the root CA certificate of all other servers to which Informix is connecting. The server keystore must be located in the INFORMIXDIR/ssl directory and must be named server_name.kdb, where server_name is the value specified in the DBSERVERNAME configuration parameter. The keystore on an Informix client stores the root CA certificates of all servers to which the client is connecting. A password for the keystore is optional on the client.

Each Informix instance must have its own keystore, and the keystore is protected by a password that Informix must know so that it can retrieve the digital certificate for SSL communications.

You can use the IBM Global Security Kit (GSKit) to generate keys required to run SSL. The GSKit is installed together with both Informix server and client (CSDK) to provide libraries and utilities for SSL communication. The GSKit includes the GSKCapiCmd command-line interface for managing keys, certificates, and certificate requests.

Complete the following steps to configure IDS to use SSL.

  1. Set up Informix ONCONFIG.
    1. Configure the server name and server aliases for SSL connections.
    2. Configure encrypt VPs using the VPCLASS onconfig parameter. For example, enter VPCLASS encrypt, num=3. If VPCLASS is not configured, IDS will start one encrypt VP by default.
    3. Configure poll threads for SSL connection using the NETTYPE onconfig parameter. If poll threads are not configured, IDS will start one poll thread. For example, enter NETTYPE socssl,3,50,NET.
    4. Configure the SSL_KEYSTORE_LABEL for the server's digital certificate in keystore. For example, enter SSL_KEYSTORE_LABEL myssllabel. If not configured, the server will use the default label in keystore for SSL communication.
  2. Configure or create an $INFORMIXDIR/etc/conssl.cfg file. This file is needed by only SQLI clients, including dbaccess, dbimport, and esql applications. The file contains the fully qualified filename of the client keystore and the fully qualified filename of the client stash file.

    Following are the client configuration parameters that are in the conssl.cfg file and their descriptions.

    This is the fully qualified file name of the keystore that stores the root CA certificates of all of the servers to which the client connects.
    This is the fully qualified file name of the stash file containing the encrypted keystore password.

    Listing 13 shows examples.

    Listing 13. Entering client configuration parameters
    SSL_KEYSTORE_FILE /u/keystores/clikeydb.kdb 
    SSL_KEYSTORE_STH /u/keystores/clikeydb.sth

    If conssl.cfg does not exist, the client keystore and stash file will default to $INFORMIXDIR/etc/client.kdb and $INFORMIXDIR/etc/client.sth.

  3. Set up sqlhosts to configure a server name to use the onsocssl SSL protocol. You need to update connection information in the sqlhosts file (UNIX) or the SQLHOSTS registry (Windows) to include information about SSL connections. Use onsocssl protocol for Informix SQLI connections and drsocssl protocol for DRDA connections.

    Table 3 shows an example of an sqlhosts file configured for both SSL and non-SSL connections.

Table 3. Example of sqlhosts file configured for SSL connections
Server nameProtocolHost nameServer name
  1. Create a server keystore. Set up a keystore and its password stash file and digital certificate by using the iKeyman utility, GSKCmd command-line interface, or GSKCapiCmd command-line interface. The iKeyman utility and GSKCmd tool require JRE 1.6 or later. The GSKCapiCmd tool is a part of the GSKit (see Related topics) and does not require Java.

    Create the keystore and its stash file in the INFORMIXDIR/ssl directory using the code in Listing 14.

    Listing 14. Creating the keystore and stash file

    Where the servername is the value of the DBSERVERNAME onconfig parameter.

    It is recommended that you change the permissions on the keystore and stash file to 664/informix:informix, as shown in Listing 15.

    Listing 15. Changing permissions on keystore and stash file
    gsk8capicmd -keydb -create -db oak_on.kdb -pw mypasswd -type cms -stash
    gsk8capicmd -cert -create -db oak_on.kdb -pw mypasswd -label myssllabel 
                -size 1024 -default_cert yes

    Important: If the DBSA configures the database server to use a different version of GSKit, the version-specific gsk8capicmd command must be replaced with command from the different GSKit version, such as gsk7capicmd.

    Export the certificate to an ASCII file (to be imported to client keystore), as shown in Listing 16.

    Listing 16. Export the certificate
    gsk8capicmd -cert -extract -db oak_on.kdb -format ascii -label myssllabel 
                -pw mypasswd -target myssllabel.cert
  2. Create a client keystore by importing the server certificate using GSKit, as shown in Listing 17.
    Listing 17. Importing the server certificate
    gsk8capicmd -keydb -create -db clikeydb.kdb -pw mypasswd -type cms -stash
    gsk8capicmd -cert -add -db clikeydb.kdb -pw mypasswd -label myssllabel 
                -file myssllabel.cert -format ascii

    Change the permissions on the keystore and stash file to 664/informix:informix. In an INFORMIXDIR that contains CSDK or I-Connect, you should have public read access. If the INFORMIXDIR contains only IDS, you might use just 600 or 640 permissions.

  3. Initialize the server by completing the following steps:
    1. Move the server and client keystores to the specified location (as described in earlier steps).
    2. Initialize the server. All the communication between client and server on the SSL configured port will be encrypted using SSL protocol.

Using auditing

Auditing creates a record of selected activities that users perform. Auditing is based on the notion of audit events and audit masks. Auditing needs to be configured and then set up on the Informix server. It can also be turned off when not needed.

Using audit events

Any database server activity that can potentially alter or reveal data or the auditing configuration is considered an event. You can use the database server secure-auditing facility to audit and keep a record of events either when they succeed or fail, or when the activity is attempted. Each audit event is identified by a four-letter event code. There are over 150 events that can be audited. Following are some common event codes:

  • ACTB is access table.
  • UPRW is update current row.
  • STEX is set explain.
  • RDRW is read row.
  • DLRW is delete row.

Using audit masks

Audit masks specify those events that the database server must audit. You can include any event in a mask. The masks are associated with user IDs so that specified actions that a user ID takes are recorded. Global masks _default, _require, and _exclude are specified for all users in the system. Table 4 lists four types of audit masks.

Table 4. Types of audit masks
Mask typeMask name
Individual user masksUsername
Default mask_default
Global masks_require and _exclude
Template masks_maskname

User masks

The global masks are always applied to user actions that are performed during a session in which auditing is turned on. Audit masks are applied in the following order:

  1. An individual user mask or if none, the _default mask
  2. The _require mask
  3. The _exclude mask

When a user initiates access to a database, the database server checks whether an individual user mask exists with the same user name as the account that the user uses. If an individual user mask exists, the database server reads the audit instructions in it first and ignores the _default mask. If no individual user mask exists, the database server reads and applies the audit instructions in the _default mask to that user.

Template masks

You can create template audit masks to help set up auditing for situations that recur or for various types of users. Template mask names begin with an underscore (_).

Using audit instructions

Masks and their events are called auditing instructions. You can select anything from minimal audit instructions, in which no events are audited, to maximum audit instructions, in which all security-relevant database server events are audited for all users. The onaudit utility is used for all audit-mask creation and maintenance.

Selective row-level auditing

With Informix 11.70, auditing can be configured so that row-level events of only selected tables are recorded in the audit trail. In previous releases, auditing was an all-or-nothing type of activity. Selective row-level auditing can compact audit records so that they are more manageable, which can potentially improve database server performance.

Informix 11.70 adds the new table level property AUDIT, which can be specified with the CREATE TABLE or ALTER TABLE commands, as shown in Listing 18. The AUDIT property controls the auditing on the specific table.

Listing 18. Examples of the CREATE and ALTER TABLE commands
CREATE TABLE {existing syntax} | with AUDIT;
ALTER TABLE {existing syntax} | add AUDIT;
ALTER TABLE {existing syntax} | drop AUDIT;

The onaudit utility supports an option (the -R flag) that can be run to enable selective row-level auditing. You can start selective row-level auditing either when you initially start auditing your databases or when the auditing utility is already running.

Understanding the audit process

When you turn on auditing, the database server generates audit records for every event that the auditing instructions specify. The database server stores the audit records in a file called an audit file. (The audit trail might consist of more than one audit file.)

Audit administrators must specify and maintain the audit configuration, which includes the following information:

  • The audit mode
  • How the database server behaves if it encounters an error when writing audit records to the audit trail
  • For UNIX, the directory in which the audit trail is located
  • For UNIX, the maximum size of an audit file before the database server automatically starts another audit file

Audit configuration

Complete the following steps to set the audit configuration.

  1. Turn auditing on or off. Auditing is turned on or off via the onaudit utility.
  2. Specify audit modes. ADTMODE in the ADTCFG file sets the type and level of auditing.
  3. Use the ADTCFG file. Configuration parameters in the ADTCFG file specify the properties of the audit configuration. These configuration parameters are ADTERR, ADTMODE, ADTPATH, and ADTSIZE. The path name for the default ADTCFG file is $INFORMIXDIR/aaodir/adtcfg for UNIX and %INFORMIXDIR%\aaodir\adtcfg for Windows.
  4. On UNIX, determine the properties of the audit files. Audit files are located in a directory you specify via the ADTPATH onconfiguration parameter. The naming convention used for audit files is dbservername.integer, where dbservername is the database server name as defined in your onconfig file, and integer is the next available integer after the number defined in the ADTLOG file.

Setting up auditing in an Informix server

Setting up auditing in an Informix server requires the following high-level steps:

  1. Use the onaudit utility to add audit events to audit masks. The command in Listing 19 shows how the Update Audit Mask and Delete Audit Mask audit events are added to the _default mask by their four-letter event codes.
    Listing 19. Adding events
     $ onaudit -m -u _default -e +UPAM,DRAM
  2. Confirm your audit mask settings in Listing 20.
    Listing 20. Confirm audit mask settings
    $ onaudit -o -u _default 
    _default - UPAM,DRAM
  3. Specify a directory for the audit trail in UNIX. The database server stores audit files in a file system directory. You can specify the directory with the onaudit utility, as shown in Listing 21.
    Listing 21. Using the onaudit utility
    $ onaudit -p /work/audit
  4. Check the audit configuration using the $ onaudit -c command. The configuration results are shown in Listing 22.
    Listing 22. Checking the audit configuration
    Onaudit -- Audit Subsystem Configuration Utility 
    Current audit system configuration: 
            ADTMODE = 0
            ADTERR  = 0 
            ADTPATH = /usr/informix/aaodir 
            ADTSIZE = 50000 
            Audit file = 0
            ADTROWS = 0
  5. Set the audit level and turn on auditing. The AAO or DBSSO configures the level of auditing in the system. To start auditing all sessions, enter $ onaudit -l 1. Following are additional auditing-level options:
  • 0 = auditing disabled
  • 1 = auditing on, starts auditing for all sessions
  • 3 = auditing on, audits DBSSO actions
  • 5 = auditing on, audits database server administrator actions
  • 7 = auditing on, audits DBSSO and database server administrator actions

Once auditing is started, the audit level is logged in the online.log, and the adt VP is started, as shown in Listing 23.

Listing 23. Logged audit level
14:43:19 Dynamically added 1 adt VP
14:43:19 Audit Mode changed to 1 

54 4da833a8 0 1 IO Idle 9adt* adt vp 0

Configuring additional options with auditing

You can configure additional options with auditing.

Error mode

The database server can perform either of two functions if an error occurs when writing to the audit trail:

  • Continue-error mode
  • Two levels of severity of halt-error mode

Setting up selective row-level auditing

The command in Listing 24 continues auditing all tables that have the AUDIT flag and stops auditing all other tables.

Listing 24. Command for selective row-level auditing
$ onaudit -R 1 

0 = Auditing row-level events on all tables (0 is the default value) 
1 = Selective row-level auditing is enabled for tables set with the AUDIT flag. 
2 = Selective row-level auditing is enabled for tables set with the AUDIT flag. 
    The primary key, if an integer data type, is included in the audit records.

Turning off auditing

Auditing is stopped by setting the audit level to 0, as in $ onaudit -l 0.

Using the onshowaudit utility

The audit trail (or audit files) is accessed using the onshowaudit utility. You can use the $ onshowaudit command to extract information for a particular user, database server, or both, making it possible to isolate a particular subset of data from a potentially large audit trail. The database server does not audit the onshowaudit utility's execution. Listing 25 shows the results of the onshowaudit command.

Listing 25. Command for onshowaudit utility
ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC4
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix
Program Over.

The -f path option specifies the directory and file name of the audit files, as shown in Listing 26.

If -f is omitted from the command, onshowaudit searches for audit files in the ADTPATH directory specified in the default ADTCFG file. The audit directory and file name must conform to minimum security levels. The directory must be owned by user informix, belong to the AAO group, and must not allow public access (0770 permission). The files must have comparable permissions (0660 permission). The files must not be symbolic links to other locations. The directory can be a symbolic link. If the audit directory and files are not secure, the onshowaudit utility returns an error message and does not display the audit results.

Listing 26. Command showing the audit log file /work/aaodir/ol_lx_rama.7 and results
$ onshowaudit -I -f /work/aaodir/ol_lx_rama.7

ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC4
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix
Program Over.

The command in Listing 27 shows only the records that pertain to usr1 in the audit log file /work/aaodir/ol_lx_rama.7.

Listing 27. Showing only usr1 records
onshowaudit -I -f /work/aaodir/ol_lx_rama.7 -u usr1

The onshowaudit command can also prepare the extracted data with delimiters so it can be later loaded into a database, as shown in Listing 28.

Listing 28. Command showing onshowaudit with delimiters and results
$ onshowaudit -I -l -f /work/aaodir/ol_lx_rama.7 

ONSHOWAUDIT Secure Audit Utility 
INFORMIX-SQL Version 11.70.FC4 
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix|0|RDRW|stores_demo
Program Over.

Using discretionary access control

Discretionary access control verifies a user's privileges to perform any particular operation.

Managing privileges

You can use following steps to define and grant privileges for a default role:

  1. Select an existing role in the current database to use as a default role, or create the role that you want to use as a default role. Use the CREATE ROLE rolename statement to create a new role in the current database.
  2. Use the GRANT statement to grant privileges to the role.
  3. Grant the role to a user, and set the role as the default user or PUBLIC role by using the syntax GRANT DEFAULT ROLE rolename TO username or GRANT DEFAULT ROLE rolename TO PUBLIC.

Use the REVOKE DEFAULT ROLE statement to disassociate a default role from a user.

A user must use the SET ROLE DEFAULT statement to change any other current role to the default role.

Defining default roles

As an administrator, you can define a default role to assign to individual users or to the PUBLIC group for a particular database. The default role is automatically applied when a user establishes a connection with the database. Each user has whatever privileges you grant to the user individually and the privileges of the default role. A user can switch from the current individual role to the default role by using the SET ROLE DEFAULT statement. If different default roles are assigned to a user and to PUBLIC, the default role of the user takes precedence. If a default role is not assigned to a user, the user only has individually granted and public privileges.

Separating roles

Role separation enforces separating administrative tasks by people who run and audit the database server. If INF_ROLE_SEP is not set, then user informix can perform all administrative tasks. Set the INF_ROLE_SEP environment variable to a non-zero integer value to implement role separation.

Setting permission to create databases

Use the DBCREATE_PERMISSION configuration parameter to give specified users permission to create databases and thus prevent other users from creating databases. If you do not set the DBCREATE_PERMISSION configuration parameter, any user can create a database. The user informix always has permission to create databases. Complete the following steps to set permission to create databases.

  • Add DBCREATE_PERMISSION informix to the onconfig file to restrict the ability to create databases to the informix user.
  • Optionally include multiple instances of DBCREATE_PERMISSION in the onconfig file to give additional users permission to create databases. For example, to grant permission to users named watson and jay, add DBCREATE_PERMISSION watson, jay to the onconfig file:


This tutorial focuses on IBM Informix database auditing concepts. It tells you what you need to know about auditing before taking the Informix system administration certification exam. You should now have a better understanding of Informix auditing operations, and you should be able to do the following:

  • Know the concepts and usages of authentication, authorization, and encryption
  • Allow database access to non-OS users
  • Use onaudit to set up and configure security auditing on your database server
  • Take advantage of trusted and trusted-context connections to enhance security
  • Use the privileges of a role to secure your data
  • Set up role separation to enforce access control on database server users

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=System Administration Certification exam 919 for Informix 11.70 prep, Part 7: Security