Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 2: Security

Get an introduction to the concepts of authentication, authorization, and privileges as they relate to IBM® Informix® Dynamic Server 11.50 (IDS). This tutorial is the second in a series of nine tutorials designed to help you prepare for the IDS Fundamentals Certification Exam (555).

Manoj Mohan (, Advisory Software Engineer, IBM

Manoj MohanManoj Mohan is a software engineer and has worked on IBM Informix Dynamic Server for the past 10 years in the networking and security area of IDS. His experiences include designing and implementing PAM, IPV6, SSO, and LBAC support in IDS.

20 August 2009

Also available in Russian

Before you start

About this series

Thinking about seeking certification on Informix Dynamic Server (IDS) fundamentals (Exam 555)? If so, you've landed in the right spot. This complimentary series of nine IDS certification preparation tutorials covers all the basics -- the topics you'll need to understand before you read the first exam question. Even if you're not planning to seek certification right away, this set of tutorials is a great place to start learning what's new in IDS 11.50.

This certification exam will test your knowledge of entry level administration of IDS 11.50, including basic SQL (Structured Query Language), how IDS 11.50 is installed, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes.

These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

About this tutorial

In this tutorial, learn about IDS 11.50 security features, including 11.50 authentication, authorization, and privileges.

This is the second in a series of nine tutorials you can use to help prepare for the IDS 11.50 Fundamentals exam 555. The material in this tutorial primarily covers the objectives in Section 2 of the test, which is entitled "Security".


To understand the concepts described in this tutorial, you should already have a basic knowledge of database concepts and an understanding of operating system security features.

System requirements

The examples in this tutorial are specific to IDS 11.50 running on a UNIX® operating system (with native security features). However, the concepts and information provided are relevant to IDS running on any distributed platform.

Database security

Database security is of utmost importance today. Your database might allow customers to purchase products over the Internet, or it could contain historical data used to predict business trends; either way, your company needs a sound database security plan. A database security plan should define:

  • Who is allowed access to the database server instance and the database
  • Where and how user passwords are verified
  • Authority levels granted to users
  • Commands that users are allowed to run
  • Data that users are allowed to read or alter
  • Database objects that users are allowed to create, alter, or drop

IDS Security mechanisms

IDS provides three main mechanisms to implement a database security plan: authentication, authorization, and privileges.

  • Authentication is the process by which IDS verifies that someone is who they claim they are. IDS authentication works closely with the security features of the underlying operating system to verify user IDs and passwords. IDS can also work with security protocols like PAM and Kerberos to authenticate users.
  • Authorization involves determining the operations that users or groups can perform, and the data objects that they can access. A user's ability to perform high-level database and instance management operations is determined by the authorities that they have been assigned.
  • Privileges are more granular than authorities and can be assigned to users or groups. Privileges help define the objects that a user can create or drop. They also define the commands that a user can use to access objects like tables, views, indexes, and packages. IDS also supports label-based access control (LBAC) that allows even more granular control of who can access individual rows and columns.

IDS also provides encryption for the next level of security.

Authentication mechanisms in IDS

Authentication is the mechanism of verifying the identity of a user or an application. The database server allows only those users who confirm their identities to access data, as shown in Figure 1. IDS conforms to the client-server architecture, where a client can be a remote or a local user.

Figure 1. Authentication flow
Authentication flow

A local connection is a connection between a client and a server residing in the same machine. A remote connection is a connection between a client and server residing in different machines. IDS supports a traditional authentication mechanism under which a user has to provide an ID and password to connect to a database. IDS also supports additional authentication mechanisms such as pluggable authentication modules (PAM).

Traditional authentication

IDS servers follow UNIX security requirements for making connections. In traditional authentication, IDS validates the users using their UNIX or Windows® login ID and passwords with the operating system (OS) APIs.

Pluggable authentication modules

A pluggable authentication module (PAM) is a framework that enables the user to develop and implement a customized user authentication mechanism without making any changes to the application. The modes supported by IDS are password mode and challenge-response mode. In password mode, the user password is sufficient to satisfy the authentication. In the challenge response mode, the server raises a challenge and the client sends a response. A client gets access to the database only if the response is as expected.

Configuring IDS to use PAM

Here are the basic steps to configure IDS to use a PAM module (some of the details are platform specific, however the concept is generic):

  1. Define the PAM module: Identify or create the PAM module that you would like to use. For example, you can use pam_unix/pam_aix/pam_unix_auth, which does the traditional network-file based authentication. Typically, this shared object is located in a platform-dependent path (like /usr/lib/security/ on Solaris). If you create a module yourself, you need to copy it to the correct location.
  2. Configure the PAM module: Add the option field in the $INFORMIXDIR/etc/sqlhosts file to indicate that PAM authentication will be used. Listing 1 shows how to configure a password-type module:
    Listing 1. sqlhosts entry for PAM
    #Server-name service  machinename portno Options
    demo_on      ontlitcp demohost    1111   s=4,pam_serv=(login),pamauth=(password)
    Where login is name of the PAM module set the PAM configuration file
    sqlhosts entry for a challenge-type module
    #Server-name service  machinename portno Options
    demo_on      ontlitcp demohost    1111   s=4,pam_serv=(xxx),pamauth=(challenge)
    Note: Here xxx is a challenge-oriented module which will have the required entry
          in the PAM configuration file.

Single sign-on (SSO)

Single sign-on is an authentication feature that bypasses the need to provide user name and password after a user logs in to the client computer's operating system. IDS delivers support for single sign-on (SSO) using Kerberos 5 security protocol.

With SSO, authentication for the DBMS and other SSO-enabled services happens when a user first logs in to 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 a Dynamic Server database, an exchange of ticket-granting tickets (TKTs) allows database access without a login prompt.

SSO also includes support for confidentiality and integrity services, so an SSO environment does not need to have other Dynamic Server CSMs. With confidentiality enabled, the data transmitted to and from the SSO-authenticated user is encrypted and can be viewed only by the user logged in with the authorized credentials. Integrity service ensures that data sent between user and the DBMS is not altered during transmission.

Configuring IDS to use SSO requires a lot of platform-specific setup. However, for the fundamentals exam, you just need to understand how to set up the sqlhosts file and the concsm.cfg file to use SSO. This is explained in the Generic Security Services Communications Support Module (GSSCSM) topic under "Encryption Support in IDS."

Access control in IDS

There are various ways of securing access to data using SQL statements. Initially, this tutorial describes various access control mechanisms available in the database server to control access to the SQL objects. This is followed by a description of the column-level encryption feature, used to encrypt data in specified columns of a table.

The access control mechanisms available in IDS are broadly classified into discretionary access control (DAC) and label-based access control (LBAC). DAC is enforced in the database server using privileges and roles. LBAC enforcement is done by using security labels.

The database administrator (DBA) plays a critical role in ensuring that sensitive data is protected from unauthorized users. SQL provides the means in achieving data protection at different levels of granularity. Note that a DBA is different from a database system administrator (DBSA). The role of a DBSA is to perform server maintenance, whereas the role of a DBA is to create and maintain databases.

Discretionary access control

Discretionary access control (DAC) is the primary access control mechanism that enables access to SQL objects using privileges and roles. The objects in the database server that are protected using DAC are databases, tables, columns, views, table, types, routines, and languages. DAC cannot be used for row-level protection; for that, LBAC needs to be used. By protecting these objects and by granting privileges to access these objects to authorized users, effective access control is achieved. Users who do not have the necessary privileges will not be able to execute the queries that access these objects.

In a typical IDS installation, there is a large number of users accessing the system, executing queries, and modifying data. Granting necessary privileges to all these users can make the administrator role quite tedious. Furthermore, company policies might stipulate that users assume a different set of privileges depending on the work that they perform at any moment. This makes the administrator responsibility more onerous because privileges have to be constantly granted and revoked from users based on the least privilege principle. The roles capability in the server alleviates the extra effort of the administrator. Role-based access control (RBAC) extends privilege-based access control by grouping a set of privileges into roles and assigning these roles to users.

Protected SQL objects

The following objects in the DBMS require users to have specific authorization or privileges in order to access them:

  • Databases
  • Tables
  • Columns in tables
  • Fragments
  • Views
  • User-defined types (UDT)
  • Routines
  • Languages

Most of the SQL queries submitted by the user interact with one or more of these objects. Privileges granted to the user on these objects dictate whether the user can perform the desired operation. Figure 2 shows the privilege checking done in the course of an SQL query execution path. First, database privileges are checked. This is followed by checking privileges on SQL objects associated with the user query. User privileges are based on privileges granted to the user explicitly, privileges derived from user's current role, and privileges that belong to PUBLIC.

Figure 2. Privilege checking in an SQL query
Diagram illustrating privilege checking in an SQL query

Table 1 provides a summary of privileges available for a particular SQL object:

Table 1. SQL objects and their privileges
SQL objectPrivileges
DatabaseConnect, resource, DBA
TableSelect, update, insert, delete, index, alter, references
ColumnSelect, update, references
FragmentInsert, update, delete
ViewSelect, insert, delete, update
SequenceSelect, alter
UDTUsage, under

Each SQL object is associated with certain privileges, and each privilege has an associated set of capabilities. When a user is granted a privilege, the user has permissions to exercise that capability on the SQL object. IDS provides GRANT and REVOKE statements to grant or remove privileges.

GRANT and REVOKE statements

SQL provides GRANT and REVOKE statements to grant and revoke privileges to users, respectively. Listing 2 shows the syntax of the GRANT and REVOKE statements on database objects. In the example, privilege represents one of CONNECT, RESOURCE, or DBA privileges. The "user-list" represents a list of users, including PUBLIC.

Listing 2. Syntax: GRANT/REVOKE statement
GRANT privilege TO user-list
REVOKE privilege FROM user-list

A privilege is granted to a user or a list of users. PUBLIC is a keyword used to represent all the users in the database system. When a privilege is granted to PUBLIC, it means that all the users in the system acquire that privilege. Similarly, when a privilege is revoked from PUBLIC, all users in the system are stripped of the privileges granted to PUBLIC. However, they still retain the privileges granted to them individually, or granted through a role. The privileges that you grant remain in effect until you revoke the privilege. Only the granter of a privilege or DBA can revoke it. Table 2 lists the table privileges you can grant to users:

Table 2. Database privileges
Database privilegeDescription
DBACreator/owner of the database. Ability to grant privileges on other objects in the database.
ResourceCan connect to the database and create other objects.
ConnectCan connect to database and execute queries.

The DBA privilege is granted to the user who creates the database, but can also be granted to other users later. The DBA privilege is the highest of the three database-level privileges. (Hereafter, this tutorial refers to a DBA privileged user as a DBA.) A DBA can perform all operations in the database. The DBA can also grant DBA, RESOURCE, and CONNECT privileges to other users of the database. Thus, more than one DBA can exist for a database. Typically, the creator of an object in a database is the owner of an object. A DBA can also create objects in the database to be owned by others. The tasks a DBA can perform include:

  • Grant and revoke database-level privileges to other users
  • Grant and revoke privileges on other objects in the database such as tables, views, sequences, UDT, routines, languages, and so on
  • Create tables, views, and indexes in the database to be owned by other users
  • Drop or alter any object in the database regardless of who owns it
  • Run the DROP DATABASE statement
  • Perform all operations that a RESOURCE privileged user can perform

The RESOURCE privilege has the second highest ranking in a database next to DBA. The DBA grants RESOURCE privilege to users to create other SQL objects within the database. The tasks a RESOURCE privileged user can perform include:

  • Create permanent tables, indexes, and SPL routines
  • Tasks that require allocation of disk space
  • Perform all operations that a CONNECT privileged user can perform

The DBA grants the CONNECT privilege to users to connect to the database and execute queries. The CONNECT privilege has the least ranking because users are not able to create other objects within the database with this privilege. The following are the tasks that a CONNECT privileged user can perform:

  • Connect to the database
  • Run queries and DML statements on database tables on which they have the necessary table-level privileges
  • Execute an SPL routine if they have the necessary table-level privileges
  • Create views if they are permitted to query on tables on which the view is based
  • Create temporary tables and create indexes on temporary tables
Table 3. Table privileges
Table privilegeDescription
SelectAllows users to read data from the table. They can perform SELECT queries or create views on table.
UpdateAllows users to update data in the table.
InsertAllows users to insert rows in the table.
DeleteAllows users to delete rows from the table.
IndexAllows users to create indexes on tables.
AlterAllows users to alter the table.
ReferencesAllows users to create referential constraints on the table.
UnderAllows users to create sub-tables under this table.

Column privileges

When a table-level privilege is granted, it is implicitly granted on all columns of the table. The column-level privilege helps you to further restrict the scope of user activity to specific columns. The privileges that are applicable at the column level are SELECT, UPDATE, and REFERENCES only.

Fragment privileges

You can use the GRANT FRAGMENT statement to grant INSERT, UPDATE, and DELETE privileges on individual fragments of a table. The fragment privileges are only applicable to fragments created by expression-based fragmentation. Fragment-level privileges that are granted to fragments of a table cease to exist under one or more of the following conditions:

  • If the fragmentation strategy of the table is changed from expression-based to another strategy, such as round-robin
  • If a fragment is dropped from the table, which results in the privileges on the fragment being dropped
  • If the expression on which fragmentation is based is changed, and the fragment privileges are therefore also dropped and the user assumes default table privileges

Table 4 lists the type privileges you can grant to users:

Table 4. Type privileges
Type privilegeDescription
UsageAuthorization to use the named data type.
UnderAuthorization to use the named data type as a super-type in the type hierarchy.

View privileges

When a VIEW is created, IDS checks the privileges on the underlying tables before granting privileges to the creator or owner of the VIEW. If the user has the SELECT privilege on the underlying table, then the VIEW is created and the SELECT privilege is granted on the VIEW to the user. If the user does not have the SELECT privilege on the underlying table, then the VIEW is not created. If the user also has INSERT, UPDATE, and DELETE privileges on the underlying table, then the same privileges are granted on the VIEW to the user. If you are using a VIEW, your privileges for the VIEW, not the underlying tables, apply. The creator of the VIEW typically grants privileges on the VIEW to other users.

Sequence privileges

SEQUENCE privileges are similar to table-level privileges. The difference is that only SELECT and ALTER privileges are granted on sequences. A user with the SELECT privilege can use the sequence in queries. A user with the ALTER privilege can alter the sequence using the ALTER SEQUENCE or RENAME SEQUENCE statements. Sequences are created only by DBA-privileged users. Both ALTER and SELECT privileges on a sequence can be granted to a user or a role.

Routine privileges

The privileges needed to create a routine are different from privileges needed to use the routine. The following privileges are needed to create a routine or procedure:

  • DBA privilege or RESOURCE privilege on the database
  • Language-level privilege on the language in which the routine is written
  • EXTEND role privilege if the IFX_EXTEND_ROLE configuration parameter is set to 1 and the routine is a Java™ technology or C UDR

By default, a routine creator has the privilege to execute the routine. If another user needs to execute the routine, then the creator of the routine must grant the EXECUTE privilege to that user.

Language privileges

In IDS, user-defined routines (UDRs) are written in SPL or in any of the external languages, such as C and Java. To create a routine in a particular language, the user needs the USAGE privilege for that language. By default, language usage privileges on SPL are available to the user informix and the user holding the DBA privilege to that database. However, only the user informix can grant the language usage privilege to others. The user with the DBA privilege cannot grant this privilege to others. The USAGE privilege to create SPL routines is granted to PUBLIC by default.


Role-based access control (RBAC) extends the privilege-based access control by grouping a set of privileges into roles and assigning these roles to users. A role can be defined as a classification of a work task. For example, you can define the manager role in a company. The manager role comes with a set of responsibilities, such as hiring employees, doing performance reviews, negotiating salaries, approving bonuses, and so on. These are common responsibilities that a typical manager performs across all departments. In order to fulfill these responsibilities, managers need certain privileges, such as access to employee records and other data privy to managers only. When an employee becomes a manager or when a new manager is hired into the company, the employee assumes the manager role and gets along with it the privileges associated with that role. IDS provides the same concept in the DBMS in the form of roles.

Predefined roles

From a security perspective, it makes sense to split the administration duties to different employees in a company to minimize the risks of misusing the database content. To support this duty separation, IDS has the following predefined roles:

  • Database Server Administrator (DBSA)
    • User account responsible for configuring, tuning, and maintaining the server instances based on the local IDS database server installation
    • Duties include the startup and shutdown of the database server, disk space management, backup and restore, performance tuning, and troubleshooting
  • Database System Security Officer (DBSSO)
    • Defines the audit masks for particular users working on the local database server instance
  • Audit Analysis Officer (AAO)
    • Responsible for audit configuration and audit trail analysis
  • Database Administrator (DBA)
    • Maintains a specific database in the local database server
    • Permission was either automatically given to the database creator or granted by the database creator
    • Does not necessarily include database server privileges
  • Operating System Administrator (OSA)
    • Defines and maintains the local user accounts including the AAO, DBSA, and DBSSO groups
    • Installs the IDS product; responsible for changing required group permissions for role separation
    • Maintains the kernel parameter settings and resource limits, such as files and memory
  • Users
    • Local or remote user accounts that are used to run database-based applications
  • Privileged Users
    • IDS defines the informix and the root users as privileged users

You can create custom roles in the database to suit your needs. A role can only be created by a DBA privileged user. Privileges are granted to roles by the DBA, and roles are assigned to users. A user can assume a role depending on the task being performed and relinquish the role when the task is completed. A user can be granted more than one role, but the user can assume only one role at any given time. Thus, depending on the operations to be performed, the user assumes a role and acquires the privileges that come with it.

The following sections describe the syntax and semantics of role creation, granting and setting of roles, default roles, revoking roles from users, revoking privileges from roles, and dropping roles.

Creating roles

A role is applicable only within the database in which it is created. Listing 3 provides the syntax of the CREATE ROLE statement, where role_name is the name of the role:

Listing 3. Syntax: CREATE ROLE
CREATE ROLE role_name;

Granting privileges to roles

The syntax for granting privileges to roles is equivalent to the syntax of granting privileges to users. The role name has to be specified in place of the user name. Only a DBA can grant a privilege to a role.

Listing 4. Syntax: Granting privileges to roles
GRANT priv_list ON table To role_list;

Granting/revoking roles to/from users

Granting a role to a user is similar to granting a privilege to a user. The main difference is that in the case of granting a privilege to the user, it is granted on an SQL object, such as a table, column, view, routine, language, or fragment. While granting a role, there is no SQL object associated with the grant. A role's existence is within a database, and the user assumes a role for the set of privileges that come along with it. These privileges can be on any of the SQL objects, as discussed in the previous section. Similar is the case with revoke role statement.

Listing 5. Syntax: Granting/revoking roles to users
GRANT role_name TO user_list;
REVOKE role_name FROM user_list;

A user with the DBA privilege grants roles to users. The CREATE ROLE and GRANT role statements do not activate the role. A role is activated by the SET ROLE statement.

Default role

An administrator can define a default role to be assigned to individual users or PUBLIC for a particular database. The default role is automatically applied when the user establishes the connection with the database. Listing 6 provides the syntax of the GRANT DEFAULT ROLE statement. A default role can be granted to a set of users.

Listing 6. Syntax: Grant default role
GRANT DEFAULT ROLE role_name TO user_list;

Assuming a role

After a non-default role is granted to the user, the user must use the SET ROLE statement to enable the role. The user assumes the default role as soon as a connection to the database is established. If no default role is granted to the user, then the user does not have any current role in the database when the connection is established. The user can change the current role or switch to a new role using the SET ROLE statement. Listing 7 provides the syntax of the SET ROLE statement. The role can be set to role_name. The role can also be set to a NULL value or NONE, in which case the current role is disabled. When the role is set to DEFAULT, the default role is enabled.

Listing 7. Syntax: Setting a role
SET ROLE role_name;

When the user assumes a new role, the user acquires all the privileges of the role in addition to the privileges that the user already holds as an individual or as PUBLIC. If a role is granted to another role that has been assigned to the user, then the user acquires all the privileges of both roles, in addition to privileges the user already holds as an individual or as PUBLIC. A user can be granted several roles, but can assume no more than one non-default role, as specified by the SET ROLE statement. If the SET ROLE statement is repeated, then the new role replaces the old one as the current role.

Dropping a role

Only a DBA or the user to whom the role is granted with the WITH GRANT OPTION can drop a role. After you drop a role, no user can grant or enable the dropped role. Also, users who are currently assigned the dropped role lose their privileges that came with the role, unless the same privileges were granted to PUBLIC or to the user individually. If the dropped role is a default role, then the default role for the user becomes NULL. Listing 8 provides the syntax of the DROP ROLE statement. The name of the role is indicated by role_name.

Listing 8. Syntax: Drop role
DROP ROLE role_name;

Label-based access control (LBAC)

While discretionary access control works at the level of database objects such as databases, tables, columns, views, and so on, label-based access control works at the level of data rows and columns. Tables are protected by security policies, and individual rows and columns are protected by security labels. The database security administrator (DBSECADM) creates security policies and labels, and grants labels to individual users. When individual users with security labels write data rows to a table protected by an equivalent security policy, data labels are generated in individual rows of the table. The cardinal principle by which LBAC works is that users whose security labels dominate the data labels protecting the rows are able to read data. How is the dominance calculated? Any label is composed of individual components and elements within each component. A user label dominates a row label if individual components in the user label dominate individual components of a row label for a given security policy. These concepts are best described with sample scenarios.

Sample LBAC scenarios

Consider a company LBL Corp., which has a traditional organizational structure. Figure 3 shows the partial organizational structure of the company. This figure shows all departments of the company headed by the respective VPs:

  • Marketing (VP)
  • Sales (VP)
    • West-region (Director)
    • East-region (Director)
    • South-region (Director)
    • North-region (Director)
  • Engineering (VP)
  • Finance (VP)
  • HR (VP)
Figure 3. LBL Corp. - Departments
LBL Corp. - Departments

The following scenarios describe three business requirements for LBL Corp. and then describe how to use LBAC to fulfill these business requirements.

Scenario 1: Department hierarchy

The sales department is further divided into regions headed by the respective regional directors. The hierarchy extends further into sub-regions and finally into individual sales-persons. Figure 4 shows an expanded view of the sales organization:

  • Sales (VP
    • West-region (Director)
    • East-region (Director)
      • Georgia (Manager)
        • Atlanta (Sales rep 1; Sales rep 2)
      • Florida (Manager)
    • South-region (Director)
      • Texas
        • Dallas (Sales rep 1; Sales rep 2)
        • Houston (Sales rep 1; Sales rep 2)
    • North-region (Director)
Figure 4. LBL Corp. - Sales department
LBL Corp. - Sales department

The prime requirement of storing sales data is that employees are able to see the data belonging to them and their subordinates while not seeing data that belongs to their peers. For instance, the sales vice president should be able to see sales data belonging to all the regions, whereas each regional director only sees data belonging to their region. Using a similar approach down the hierarchy, an individual sales person can only see his own data.

This can be accomplished by granting labels to users in the organization such that labels granted to people higher up in the hierarchy dominate labels granted to people who report to them. Thus, the label granted to the sales VP dominates the labels granted to any of the regional directors, the labels granted to regional directors dominate the labels granted to any of the sub-region managers that report to them, and the labels that are granted to individual sales people are dominated by labels granted to their managers. This is best accomplished by defining a TREE component for the sales organization. This tutorial further discusses this scenario as an example while defining the security policies and labels for the sales data table.

Scenario 2: Document classification

Another scenario in which LBAC is useful is where the documents are classified based on their level of sensitivity and a user with their sensitivity level can read documents belonging to lower sensitivity levels. Once again, consider Figure 3, where marketing documents can be classified into sensitivity levels, such as Top Secret, Secret, Confidential, and Unclassified. The CEO of the company could be granted a label that has Top Secret as its component element. A technical architect could be given a higher sensitivity level so that competitive information is accessible. This is best accomplished by defining the ARRAY component of sensitivity levels. This tutorial discusses this scenario further while describing examples of creating security components.

Scenario 3: Special users

Now, let's look at a third scenario that describes the usage of the SET component type. Consider the HR department in Figure 3. The VPs of individual departments can see the employee records belonging to their own departments. They should not be able to see records of employees belonging to other departments. But the human resources VP should be able to see all employee records of the company. Thus, the HR VP has a label that includes all departments. Other VPs have labels that have only their departments as part of the SET component. This tutorial demonstrates this with examples in subsequent sections.

The discussion of the sample scenarios demonstrates that LBAC provides the framework to create security policies in a variety of ways and to protect data with these policies. The chief characteristic of an LBAC-protected table is that security and privacy is achieved by making data access more granular. The SQL objects that enable LBAC enforcement are security label components, security policies, and security labels. These objects are used in protecting individual rows and columns in tables. LBAC enforcement is done at a lower level than DAC enforcement. After the privileges are checked at the database level and table level, LBAC enforcement is done at row and column levels to fetch individual rows. Only a DBSECADM can create LBAC SQL objects or apply them to tables or grant them to users. The DBSECADM is a built-in role provided by IDS, and this role is granted to individual users by the database system administrator. All LBAC SQL objects exist in the context of a database. There are three important steps in creating an LBAC framework:

  • Defining the security policy
  • Creating the security labels
  • Assigning the security labels to the users

Encryption support in IDS

Encryption technology is integrated with IDS as a pluggable communication supports module (CSM). Table 5 provides a list of CSMs that IDS provides:

Table 5. CSMs in IDS
CSM nameOSLibrary location and nameDescription
ENCCSMUNIX$INFORMIXDIR/lib/csm/iencs11a.soThis module is used to encrypt all the data that is transferred between client and server
SPWDCSMUNIX$INFORMIXDIR/lib/csm/ispws11a.soThis module is used to encrypt ONLY passwords sent from clients to the server
GSSCSMUNIX$INFORMIXDIR/lib/csm/igsss11a.soThis module is used to support single sign-on authentication in IDS

Configuring IDS to use encryption

This is a two step process:

Step 1: Configure the CSM in the CSM configuration file (concsm.cfg)

This file is located at $INFORMIXDIR/etc or you can use the INFORMIXCONCSMCFG environment variable.

  • Define an encryption CSM. Listing 9 shows the syntax for an ENCCSM entry in concsm.cfg\:
Listing 9. ENCCSM syntax
ENCCSM ("path","cipher[options],mac[ options], switch[ options]", "")
Where the options are:
		Path   - The absolute path of the CSM shared library
		Cipher - Specifies the names of the ciphers that you want to use 
			   for encryption
		Mac    - Specifies the absolute path of the MAC key file
		Switch - Specifies the time interval for switching between ciphers if 
			 multiple ciphers are defined

For example,
  • Define a Simple Password CSM. Listing 10 shows the syntax for a SPWDCSM entry in concsm.cfg:
Listing 10. SPWDCSM syntax
SPWDCSM("path","global option","conn_options")
Where the options are:
	Path          - Absolute path of the CSM shared library
	Global option - Not supported currently, must be null
	conn_options  - p=0 password not mandatory
			    p=1 password mandatory for authentication

The following example shows how the entry for SPWDCSM in the concsm.cfg file should
look. Here is a CSM library and p=1 mandates that the password verification
is needed.

  • Defining a GSSCSM. Listing 11 shows the syntax for a GSSCSM entry in concsm.cfg:
Listing 11. GSSCSM syntax
GSSCSM("path", "global options", "conn_options")
Where the options are:
	Path          - Absolute path of the CSM shared library
	Global option - Not supported currently, must be null
	Conn_options  - You can configure Kerberos-defined confidentiality and 
                      integrity services leave it blank to accept the defaults. 
			    You can enter any values, you can do this for 
                      one service or for both services. The settings must be 
                      entered as comma-separated values. The conn_options in 
                      the concsm.cfg file are as follows: 
			    Settings Result
			    c=0      Confidentiality service of the Generic Security 
                               Services (GSS)API is disabled. 
			    c=1      Confidentiality service is enabled. This is the 
                               default setting. 
			    i=0      Integrity service of the GSS-API is disabled. 
			    i=1      Integrity service is enabled. This is the default 

The following example shows how the entry for SPWDCSM in the concsm.cfg file should
look.  Here is a CSM library and p=1 mandating the password verification
is needed.


Step 2: Associate CSM to IDS instance

After setting the concsm.cfg, you can associate a CSM to IDS by updating the sqlhosts file.

Listing 12. sqlhosts entry for ENCCSM/SPWDCSM/GSSCSM
demo_on1 onsoctcp demohost 1111 csm=(ENCCSM)
demo_on2 onsoctcp demohost 1112 csm=(SPWDCSM)
demo_on3 onsoctcp demohost 1113 s=7,csm=(GSSCSM)

Apart from encryption over the wire, IDS also supports column level encryption (CLE), which is an SQL-based implementation.

Secure-Socket-Layer (SSL) support in IDS

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.

Configure an IDS instance to use SSL

Configure an instance of IDS for Secure Sockets Layer (SSL) connections by adding connection information to the sqlhosts file, setting SSL configuration parameters, and configuring the keystore and the digital certificates it stores. The concepts of keystore and digital certificates are beyond the scope of this exam; you just need to know how to set up the sqlhosts file to use SSL.

  • Update connection information in the sqlhosts file (UNIX) or the SQLHOSTS registry (Windows) to include information on SSL connections. Use the appropriate protocol:
    • onsocssl protocol for all the Informix clients, such as ESQL/C, ODBC, DB-Access, dbexport utility, dbimport utility, dbschema utility, or dbload utility connections
    • drsocssl protocol for DRDA connections

Listing 13 shows an example of an sqlhosts file configured for both SSL and non-SSL connections:

Listing 13. sqlhosts entry for SSL
demo_on1 onsoctcp demohost 1111 
demo_on2 onsocssl demohost 1112 
demo_on3 drsocssl demohost 1113


Now that you've completed this tutorial, you should have a fundamental understanding of the following topics:

  • Security mechanisms: You should understand authentication, authorization, and privileges.
  • Authentication mechanisms in IDS: You should know what the traditional, PAM, and SSO authentications are in IDS and how to set up IDS to use them.
  • Access control in IDS: You should understand what DAC and LBAC are, what the protected SQL objects are in IDS, and the SQL statements you use to grant and revoke access and privileges.
  • Roles and LBAC: You should understand various concepts and SQL statements associated with roles. You should also have a basic understanding of implementing label-based access control.



Get products and technologies



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

Zone=Information Management
ArticleTitle=Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 2: Security