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.
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 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
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).
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):
- 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/pam_unix.so on Solaris). If you create a module yourself, you need to copy it to the correct location.
- 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:
- Columns in tables
- User-defined types (UDT)
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
Table 1 provides a summary of privileges available for a particular SQL object:
Table 1. SQL objects and their privileges
|Database||Connect, resource, DBA|
|Table||Select, update, insert, delete, index, alter, references|
|Column||Select, update, references|
|Fragment||Insert, update, delete|
|View||Select, insert, delete, update|
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
to grant or remove privileges.
REVOKE statements to grant and revoke
privileges to users, respectively. Listing 2 shows the syntax of the
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 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
PUBLIC, all users in the system are stripped of the privileges
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
|DBA||Creator/owner of the database. Ability to grant privileges on other objects in the database.|
|Resource||Can connect to the database and create other objects.|
|Connect||Can 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
- 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
|Select||Allows users to read data from the table. They can perform SELECT queries or create views on table.|
|Update||Allows users to update data in the table.|
|Insert||Allows users to insert rows in the table.|
|Delete||Allows users to delete rows from the table.|
|Index||Allows users to create indexes on tables.|
|Alter||Allows users to alter the table.|
|References||Allows users to create referential constraints on the table.|
|Under||Allows users to create sub-tables under this table.|
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.
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
|Usage||Authorization to use the named data type.|
|Under||Authorization to use the named data type as a super-type in the type hierarchy.|
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 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
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.
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_ROLEconfiguration 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.
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.
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
- 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.
A role is applicable only within the database in which it is created.
Listing 3 provides the syntax of the
ROLE statement, where role_name is the
name of the role:
Listing 3. Syntax:
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
GRANT role statements do not activate the role. A role is
activated by the
SET ROLE statement.
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
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; SET ROLE NULL; SET ROLE NONE; SET ROLE DEFAULT;
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
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
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
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)
- Georgia (Manager)
- South-region (Director)
- Dallas (Sales rep 1; Sales rep 2)
- Houston (Sales rep 1; Sales rep 2)
- North-region (Director)
Figure 4. 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 name||OS||Library location and name||Description|
|ENCCSM||UNIX||$INFORMIXDIR/lib/csm/iencs11a.so||This module is used to encrypt all the data that is transferred between client and server|
|SPWDCSM||UNIX||$INFORMIXDIR/lib/csm/ispws11a.so||This module is used to encrypt ONLY passwords sent from clients to the server|
|GSSCSM||UNIX||$INFORMIXDIR/lib/csm/igsss11a.so||This 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
- 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, ENCCSM("/usr/informix/lib/csm/iencs11a.so","cipher[des:ecb,ede:ecb], mac[levels:<medium>,files:</usr/informix/etc/mackey.dat>], switch[cipher:2,key:1]","")
- 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 ispws11a.so is a CSM library and p=1 mandates that the password verification is needed. SPWDCSM("/usr/informix/lib/csm/ispws11a.so","","p=1")
- 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 setting. The following example shows how the entry for SPWDCSM in the concsm.cfg file should look. Here ispws11a.so is a CSM library and p=1 mandating the password verification is needed. GSSCSM("/usr/informix/lib/csm/igsss11a.so","","")
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,
- drsocssl protocol for DRDA connections
- onsocssl protocol for all the Informix clients, such as ESQL/C, ODBC, DB-Access,
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.
- "IBM Informix Guide to SQL: Tutorial" (IBM, 2007): Get an introduction to the Informix implementation of SQL, including SQL statements, data types, and system catalog tables that provide information about database structures.
- developerWorks Informix zone: Get the resources you need to advance your skills in the Informix arena.
- "The IDS Detective Game" (developerWorks, April 2008): Learn or teach the basics of Informix Dynamic Server (IDS) and relational databases with an interactive game called "The IDS Detective Game".
- IBM Information Management certification page: Find more resources for IDS certification
- IDS roadmap for administrators, developers, and end users: Find resources for all aspects of IDS—planning, installing, configuring, administering, tuning, monitoring, and more.
- Informix Education Training Path: See the courses you need to take to achieve particular skills or certification.
- Informix library: Learn more details about IDS from the online manuals or the IDS Information Center.
- IBM Informix Dynamic Server
11.50 Information Center:
Find information that you need to use the IDS family of products and
- Security in IBM Informix Dynamic Server: Learn more about the methods for keeping your data secure.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Informix Dynamic Server Express Edition: Download a trial version of Informix Dynamic Server Express Edition to get started with IDS.
- Informix Dynamic Server Enterprise and Developer Edition: Download a free trial version of Informix Dynamic Server Enterprise or Developer Edition.
- Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox, and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
- Participate in the discussion forum.
- IDS Experts Blog: Read the technical notes on Informix Dynamic Server from a worldwide team of Development and Technical Support engineers.
- Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.