Compliance: Role Separation and Audit (part I)

Depending on your geography and your business, you are probably at least well aware of the current government, legal and privacy regulations.
After some well noticed economical frauds, and also due to the constant fight between security and privacy, many companies had to increase their internal regulations and security measures, and they had to begin to comply with several national and international regulations. If you're in the USA, names like Sarbanes-Oxley Act of 2002 (SOX) and Health Insurance Portability and Accountability Act (HIPAA) have surely become part of your daily jargon. Other countries have similar national laws or must follow similar directives (EU countries). From a brief search Asian countries like Japan also have similar regulations. So, although not equal in every country, most of us have met the same or similar requirements to comply with security, accountability and privacy regulations.
The situations will vary but in general this means companies have to define, ensure and control who does what to their data. This directly implies that all IT departments must make sure they can answer their auditors (either internal or external) about the controls they implemented in their systems.

I will, in this and another article, try to explain some of the security, auditing and other control features IBM Informix Dynamic Server has to offer. In this first part I will show you how Informix can separate the various roles that are needed to install, manage and audit an Informix database instance. The second article will focus on the audit facility. I will leave out of these articles the features that allow you to control the user's access to data. This will be the privileges (database and table level) and the Label Based Access Control (LBAC). The reason for this is that both of these features are reasonable well known and also because when LBAC was introduced in Cheetah (IDS 11.10) it received a lot of coverage in several places (like IDS Experts Blog and Guy Bowerman's Blog).

The role separation and audit functionalities were traditionally covered in the Trusted Facility Manual (V7, V9, V10) , but since IDS 11.10 they were put together with LBAC and some other functionalities like encryption into a new manual, the Security Guide Manual.

If you start looking at the manuals and look for auditing facilities you'll first find references to "role separation". You may also notice that the IDS installer will ask you if you want to enable role separation. Please, don't mix up this "role" with the well known database roles that you can use to manage database and table level privileges. These "roles" in the audit facility context are different. IDS documentation identifies five roles regarding to database instance installation, administration, audit control and audit administration:

1. Operating System Administrator(s) (OSA)
   These are responsible for the Operating System administration and they are needed for the IBM Informix Dynamic Server product installation
   
2. DataBase System Administrators (DBSA)
   These are the database system administrators. They can do all maintenance tasks on the database instance (not the databases within the instance). These include starting, stopping, monitoring, executing backups etc. They cannot access the data inside the databases and they cannot control any of the auditing tasks of the instance. This means they cannot control what is audited, neither who is audited and they cannot access the audit logs. They can of course be audited!
   
3. DataBase System Security Officers (DBSSO)
   These define what is audited. As you will see in the second article, they will define the audit masks. But they won't be able to manipulate the auditing facility (start, stop, configure...) or access/manipulate the audit logs
   
4. Audit Analysis Officers (AAO)
   These will manage (start, stop, configure...) the auditing facility and will also be able to access the audit logs. They will not be able to define what is audited
   
5. DataBase Administrators (DBA)
   These are the users with "GRANT DBA" on one or more databases inside an instance. Remember that the owner of the database is inherently a DBA of its database.

It also talks about another group or role, the instance users, which you can define in the $INFORMIXDIR/dbssodir/seccfg file. In this file you can define a group, and then only the members of that OS group will be able to connect to the instance. Unfortunately you can only define one group, and this file is not configurable per instance (as $ONCONFIG for example). So, if you have more than one instance in your server you'll need more than one $INFORMIXDIR.

Hopefully I will be able to explain the tasks of each of these roles, and it will be clear that by providing this separation ability IDS offers you tools to implement compliance that you'd have to buy in other databases. The decision to implement all these roles and to really keep them separate (by keeping each user in only one role) will depend on your organization objectives (and also the capabilities in terms of organization and staff).

I'd like to make one thing very clear: Although ideally these roles should be completely separated, organizations should be aware that they must team together. DBSSO and AAO can do certain tasks that will impact the database performance and availability. They should take this into consideration. Also, some tasks, usually attributed to DBSA can be impossible to achieve without giving them the DBA role also... So, you should really define your objectives, priorities and staff functions. Sometimes there will be a price to pay for some functionalities and this may need management approval. The good news is that everything I'll talk about here is possible to configure on the fly meaning you can dynamically change everything... from role separation to audit configuration and management. So, if as I mentioned earlier, your different departments work as a team you benefit from this ability to reconfigure without stop, if and when the need arises.

I'll focus on Unix/Linux, mainly because that's where I'm most experienced, but everything I'll talk about is possible to do on Windows. It may have to be done in a slightly different way. Please consult the manuals if I'm not specific about it here. There is also an article about it on Guy Bowerman's blog.

So, let's start by the beginning... Ideally it all starts at installation time. The installation itself needs to be done by an OSA role element. Typically this will be root on Linux/Unix and a member of Administrators group in Windows. The installation will ask you if you want to create role separation. If you answer "yes", it will ask you for OS group that will represent the DBSSO and the AAO roles. It assumes DBSA will be group informix (Linux/Unix) and Informix-Admin on Windows. I don't like to assume that DBSA will map to group informix. I'll explain it later...
So, the mapping between instance administrative roles and users will be implemented by OS groups. To be more specific, there are three directories inside your $INFORMIXDIR that will define the roles. The group owners of those directories will be the OS groups representing the roles. Specifically the mappings are:

• DBSA
  Group owning $INFORMIXDIR/etc
• DBSSO
  Group owning $INFORMIXDIR/dbssodir
• AAO
  Group owning $INFORMIXDIR/aaodir

The OSA and DBA roles don't map like this. The OSA is defined by OS configuration, and DBAs are defined in each database by GRANTing privileges to specific users.

So, if you installed the product and you did not choose to create role separation, you can do it later. Let's assume we will have OS groups called ixdbsa, ixdbsso and ixaao that respectively map to DBSA, DBSSO and AAO roles. To implement role separation on an existing instance you should issue the following commands:

chgrp ixdbsa $INFORMIXDIR/etc
chgrp ixaao $INFORMIXDIR/aaodir
chgrp ixdbsso $INFORMIXDIR/ixdbsso

The directory permissions should also be changed:

chmod 770 $INFORMIXDIR/aaodir
chmod 770 $INFORMIXDIR/dbssodir
chmod g+w $INFORMIXDIR/etc

Note: Some documents may show 775 for aaodir and dbssodir, but I don't see the need for permissions in these directories outside the group they belong to.

And also you should give permissions for your DBSAs to alter $ONCONFIG file:

chgrp ixdbsa $INFORMIXDIR/etc/$ONCONFIG
chmod g+w $INFORMIXDIR/etc/$ONCONFIG

Let me make a few considerations before proceeding: As I wrote above, I personally don't like to use group informix for the DBSA role. Why? Well, you are probably aware that the IDS chunks must be owned by user informix and group informix and must have permissions 660. Now, if you use group informix as your DBSA group, then you have to include your instance administrators in this group. The problem with this is that then, theoretically, they will have read/write ability on the chunks. They could, using just OS commands, read data pages, overwrite data pages, eliminate chunks etc. Of course they would need deep technical knowledge about a chunk structure and IDS datatypes to be able to read any useful information, but it would be technically possible. The downside of having a different group for DBSA is that you must use the informix account (or an OSA) to add space to your instance. The upside would be that your team of DBSAs could do everything needed to manage the instance, but could not ever access your data. It could of course drop a chunk, but this would have to be done using IDS commands which can be (and should be) easily audited.

By now, some of you are probably thinking I'm being too though or too strict... I mean... we are DBSAs/DBAs and we are supposed to be trustworthy individuals... Yes, this is true. And yes, after the end of these two articles you'll be asking "and what about root?... He can still do rm -rf /*, can't he?"... Yes, we are trustworthy individuals, and yes, root is a problem, but when you are confronted with an auditor, you MUST prove that you can't do any harm without leaving a trace, and YOU don't have to take care of the root problem... The idea here is to show how the product can be configured to assure maximum security and accountability... then there are the implications, and organizational decisions...

So, first decision, use informix group for DBSA? Your choice... Or your organization's choice. Anyway, after putting your staff that will administer the IDS instance in the "DBSA" OS group, you should consider their access to the informix account. After doing the right configuration you can (and should) impose serious restrictions on the use of the informix account. The fact is, in general, access to it will not be needed. If you choose not to use group informix then it will be necessary for adding space to the instance, but that's the only thing I remember... everything else can be done using your individual accounts as long they belong to the DBSA group.
Some very important notes:

1. If you choose to use a different DBSA group (not informix) your OSA must change permissions on the oninit executable file;
   
   chmod o+x $INFORMIXDIR/bin/oninit
   
   Before you shout "What?! Isn't that the most insecure thing to do?", I should say that oninit will check the identity of the invoking user, and will not run if he doesn't belong to the DBSA defined group
2. Many customers usually have several instances in the same box, sharing the same product installation directory. IDS is prepared to do this, and most of its configuration and operation files ($ONCONFIG, MSGPATH etc.) are separated by instance. But if you're using role separation, this would mean you'd need to have the same groups/roles for all the instances. This may not be convenient. I suggest that you install your product just once, but then create separated $INFORMIXDIRs for your instances. The steps to achieve this are:
1. Create one $INFORMIXDIR for each of your instances
2. Inside each one, create symbolic links to the equivalent directory on the product installation directory except for $INFORMIXDIR/etc, $INFORMIXDIR/aaodir and $INFORMIXDIR/dbssodir
3. For this three directories, copy their content from the product installation directory into each one of yours $INFORMIXDIR
   
   This process is well described in Jonathan Leffler's "Is your DBA paranoid enough" presentation... You can Google it...
   At the bottom of this article you can also find a link to a script called ixvirtdir that will help you in the process of creating these directories for each instance.
   
3. If you're running versions 9+ and you use a specific group for AAO you may hit one problem: In these versions the majority of the oninit processes will not run as user root, but will use user informix. When you do certain audit commands the engine will try to create a file with the running user and then will try to change it's ownership (like chown...). This will fail in many, if not most platforms. There is an undocumented variable that you can use (NONROOT_OFF=1) in the server environment that will change the default behavior, and will force oninit to run as root. This has disadvantages (that's why it was changed), but may be the only way to create certain files with the desired user
   

By now you should have all the info needed for setting up role separation in your IBM Dynamic Server instances. This is the basic step to allow for a proper audit environment.
This will be the subject of the second article, which should be available soon.

You still have two problems to address: root and informix. These are privileged users, and you should restrict access to them. root is always a problem, because it really can do whatever he wants... and many times it's nearly impossible to completely audit its actions, because usually root will be able to circumvent all the restrictions you may implement. informix will be much easier, and you should avoid at all cost the use of this user, once you create and defined the DBSA group and you have your instances configured. There will be a few actions that you must do as informix but you should take measures like:

• Avoid direct login as informix
• Make it impossible to have more then one people at the same time using this login
• Audit the actions of user informix using the audit facility
• Audit everyone that accesses the informix account (logging su and/or sudo commands)
• Eventually use the OS to log informix keystrokes

I recommend that you test all these steps and all the actions in test environments. A mistake or bad configuration options can really cause harm to your databases. Please note that many times functionality comes with a price... Please consider it before taking decisions.

To end this article, a few references:

• Security and Compliance Solutions for IBM Informix Dynamic Server (Redbook)
• A recent webcast from Jonathan Leffler about database security and compliance
• IDS 11.10 security manual at Infocenter
• Script to help you create a "virtual" INFORMIXDIR for each instance, based on a common product installation
• The second part of this article:
  http://informix-technology.blogspot.com/2008/07/compliance-role-separation-and-audit.html
  

Fix Central: A central place to get your Informix fixes

In this article I'll cover one of the sites that IBM uses to improve it's interaction with customers. As many Informix customers know, traditionally we would only have access to the normal releases.
Theese would be like IDS 10.00.FC8, or CSDK 3.00.TC1 etc.
I won't go into details explaining the meaning of all the release code fields, but you probably know that any two letters and one numeric digit following major and minor versions (FC8 and TC1 in the examples) mean that this is a standard, generally available release level (internally called Interim Release). It includes mainly code fixes, but can also include some new features and they are cumulative (FC8 should include all the corrections and features that went into FC7).

But, if you ever hit a bug and contacted support, then you probably ended up with a patch level like 10.00.FC7X5 or 11.10.FC2W1. These have special meanings... The "X" letter, followed by a numeric digit means this is a special build, generated from a customer request, and it includes one or more bug fixes. It is not cumulative, meaning X2 may have a completely disjunct set of fixes from X1 and also X1 on one platform may have nothing in common with X1 on another platform.
"W" means it's a Post Interim Drop (PID). These were introduced a few years ago, and they are build for the major platforms. These release levels include a set of fixes (no new features) and are made from time to time. They are cumulative, meaning all W1 fixes will be included in W2 (unless the fix itself has problems and has to be removed).
The idea behind all this is that the customer should not have to wait for the next commercial interim release to get a product fix, if he hits a bug.

Until June 2007, you would have to open a support case before you could have access to one of this special builds. Then your support engineer would give you specific instructions on how to download the fix from a temporary location (or you could receive the fix by mail if you prefer).
Since that date, IBM made available many of these fixes in a new system or web site, called the Fix Central.
You will need a valid ibm.com account, associated to a valid Passport Advantage customer account. Then, assuming your PA account entitles you to use the specific version you will have access to some of these fix releases.
This makes the interaction between customers and IBM technical support easier, and it also gives the ability for a customer to access and test the most recent releases by himself. If you fill you're hitting a bug, and after searching the support site you find an APAR (Authorized Program Analysis Report) that matches it, you may search for a fix that solves it in the Fix Central site.
The searches can be made by product family, APAR, release level and platform. So this means you have a lot of flexibility and ease of use.

I'll end this post with some references where you can get more details about all these:

• Fix Central website:
  http://www.ibm.com/support/fixcentral
  
• Fix Central website announcement:
  http://www-1.ibm.com/support/docview.wss?uid=swg21260952
• Explanation about IBM Informix non commercial fix packs:
  http://www-1.ibm.com/support/docview.wss?uid=swg27009801
• Passport Advantage website:
  http://ibm.com/software/passportadvantage
  
• A glossary of IBM support jargon (things like APAR, PMR, EOS...)
  http://techsupport.services.ibm.com/guides/acronyms.html

Cheetah 2: The cat is out for it's second open beta...

As the title suggests, IBM has just made available the IBM Informix Dynamic Server (IDS) 11.50 (codenamed Cheetah 2) for anyone wanting to test it. It's the second open beta of an IDS version (the first happened last year with 11.10 - Cheetah).

The process is a little bit more complex than the previous beta. This time you have to send and email requesting access. The system will provide you with an access code and you'll receive a copy of Carlton Doe's book on IDS 11.

Again IBM proves it's commitment to Informix technology and to it's customers, by providing a new release available in about 1 year.

This beta program includes a non-disclosure agreement and we cannot talk about it... But I'm sure as soon as the new features are announced we'll see a lot of articles about them, and I plan to cover some of them here in detail. For now, please check the release notes :)

So, in order to get hold of your copy, just go to http://www.ibm.com/informix/new and click on IDS 11.50 beta. As I mentioned above, the process will take longer than last time, but you'll receive your copy of Carlton's book.

Once again R&D has made a wonderful job in providing excellent new features. I'm curious to know for how long the competition will play the 'FUD' song to customers...

Informix user authentication: PAM for the rescue (part 2)

I hope you had the chance to read and eventually follow part one of these series of articles related to IDS authentication using PAM. If you did, you already know the basics of PAM and how to setup IDS to authenticate against an LDAP or Active Directory server.
But there are still several topics we need to cover to fully understand IDS PAM authentication:

• Challenge/response and implicit (no password) connections
  
• How to setup distributed query environments using PAM
• Can we develop customized PAM modules?
• What are the current IDS PAM limitations?

Challenge/response and implicit connections:

Most of us, people working with Informix, know that besides the usual user/password connection we can establish what we usually call implicit connections. These connections are based on trust relations between the client and the server or between a specific user on the client and a user (the same or other) in the server.

These trusts are configured exactly as the trust relations for the "r" commands (rsh, rcp, rexec etc.).Specifically you must use the /etc/hosts.equiv or the ~user/.rhosts files. Just as a side note, I've found many customers that assume they need these services in order to Informix trusted relations work. This is not correct. You can work with the files even if the services (rshd, rexecd etc.) are down. And it's probably much better to leave the services down since they raise a lot of security issues.
These connections are normally used in environments using 4GL and ESQL/C applications. More modern applications (Java, J2EE, .NET, PHP, Ruby, Python, Perl etc.) use the more usual user/password mechanism. 4GL and ESQL/C can also use user/password, but typically they use the implicit connections for simplicity and because they normally run in more controlled environments.
dbaccess, the ASCII tool that every Informix DBA uses can use both types of connections. If you specify the database in non interactive mode it will try implicit connection, but using the menus or specific instructions will allow you to make user/password connections. So, how does all this relate to IDS PAM authentication? Read on...

PAM is based on a challenge/response mechanism. The modules will throw challenges to applications, and these should respond with the correct answers to these challenges. A correct answer will fulfill the modules requirements and a wrong answer will cause module failure.

So in a sense, you can consider the user/password has a challenge/response case. If the module you're using requires a password and you use a user/password connection everything will be handled transparently for you, assuming the module has been configured in password mode (pamauth=(password) in $INFORMIXSQLHOSTS)

But we can configure the modules in another mode (pamauth=(challenge)). This has to be done if we want to use implicit connections with PAM.
In these cases you have to prepare your application to answer the challenges issued by modules. This is done only in recent versions of Client Software Development Kit (CSDK) and JDBC driver, and the way it works is by allowing you (the developer) to code a callback function to answer challenges. Whenever the authentication modules raise a challenge, the CSDK or JDBC interface code will forward this challenge to your function. Obviously you need to register it as a callback function for PAM authentication. In ESQL/C the API function to do this is called ifx_pam_callback(). You should declare your callback function like this:

int callback(char *challenge, char *response, int msg_style);

and then register it:

ifx_pam_callback(callback);

The callback function must be declared with three arguments:

1. char *challenge
   The challenge issued by the authentication module
2. char *response
   The answer provided by the callback function
3. int msg_style
   The kind of message the module sent

Depending on the module you're using you may choose to give automatic responses or else forward the challenge to the user and allow him to introduce the answer. The msg_style parameter can be used for this, and is the module choice.
If you choose to ask the user, the same callback function may work with very different modules, as long as the user understands the challenges and can provide a correct answer. This approach implies that you'll have to bother the user, which is a different behavior from the traditional implicit mechanism of authentication.
On the other hand you may choose to code the answers in the callback function, or allow it to find the correct answer automatically. This approach forces you to adapt your callback function if you choose to change the authentication modules. As an example you could arrange the callback functions and a PAM module to authenticate through key exchange, or through a set of questions and answers that only an algorithm could understand. Connections would be allowed only to clients using the correct callback function.

In JDBC the callback mechanism is provided by the IfxPAM() method in IfmxPAM interface.
If you have CSDK installed, you can find an example of a callback function being use in an application. Check $INFORMIXDIR/demo/esqlc/pamdemo.ec

But the real beauty comes when you also see the other side. That side is the PAM module itself.
To show you this I will borrow an example published in DeveloperWorks, more exactly here.

The authors are three IBMers who have created an example of a PAM module that I will reproduce here with very small changes:

/*
example from http://www.ibm.com/developerworks/db2/library/techarticle/dm-0704anbalagan/
with slight changes and a few comments
*/


#include <link.h>
#include <string.h>
#include <security/pam_appl.h>
#include <security/pam_modules.h>

#ifndef PAM_EXTERN
#define PAM_EXTERN
#endif


/*
This implements the main PAM API function. This will be called when we create a
PAM service like:

auth pam_demo_mod required


The function signature is defined in the PAM include modules
*/

PAM_EXTERN int
pam_sm_authenticate(pam_handle_t *pamh, int flags, int argc, const char *argv[])
{
   struct pam_conv *conv;
   struct pam_message msg[3], *msgp;
   struct pam_response *resp;
   const char *user;
   char *answer, *prompt[2], *vanswer[2];
   int pam_err, retry;
   void *handle = NULL;

/*
Define three prompts. We will raise three challenges. Two questions and an info string.
The answers are shown in parenthesis.
The user will see the questions and the answers. Only for demonstration purposes of course!
*/

   prompt[0] = (char *) strdup("Your school name (MIT):");
   prompt[1] = (char *) strdup("Your maiden name (SUZE):");

   vanswer[0] = (char *) strdup("MIT");
   vanswer[1] = (char *) strdup("SUZE");

   pam_err = pam_get_item(pamh, PAM_CONV, (void **)&conv);
   if (pam_err != PAM_SUCCESS)
       return (PAM_SYSTEM_ERR);

   msg[0].msg_style = PAM_PROMPT_ECHO_OFF;
   msg[0].msg = prompt[0];
   msg[1].msg_style = PAM_PROMPT_ECHO_ON;
   msg[1].msg = prompt[1];


/*

Send the two challenges to the client

*/

   for (retry=0;retry<2;retry++) msgp =" &msg[retry];" resp =" NULL;" pam_err =" (*conv-">conv)(1, &msgp, &resp, conv->appdata_ptr);
       if (pam_err == PAM_SUCCESS){

/* No response needed for text info and error msg */
           if ((msg[retry].msg_style == PAM_TEXT_INFO) ||
           (msg[retry].msg_style == PAM_ERROR_MSG))
           {
               continue;
           }
           answer = resp->resp;
           if (!answer){
               pam_err = PAM_AUTH_ERR;
               break;
           }
           if (strcmp(answer, vanswer[retry])){
               pam_err = PAM_AUTH_ERR;
               break;
           }
       }
   }
   if (resp){
       if (resp->resp){
           free(resp->resp);
       }
       free (resp);
   }
   return (pam_err);
}

/*
The functions below are required, but not used in this module
They are called for account/session etc.
So, we'll just return success
*/

PAM_EXTERN int
pam_sm_setcred(pam_handle_t *pamh, int flags,
int argc, const char *argv[])
{
   return (PAM_SUCCESS);
}

PAM_EXTERN int
pam_sm_acct_mgmt(pam_handle_t *pamh, int flags,
int argc, const char *argv[])
{
   return (PAM_SUCCESS);
}

PAM_EXTERN int
pam_sm_open_session(pam_handle_t *pamh, int flags,
int argc, const char *argv[])
{
   return (PAM_SUCCESS);
}
PAM_EXTERN int
pam_sm_close_session(pam_handle_t *pamh, int flags,
int argc, const char *argv[])
{
   return (PAM_SUCCESS);
}

PAM_EXTERN int
pam_sm_chauthtok(pam_handle_t *pamh, int flags,
int argc, const char *argv[])
{
   return (PAM_SERVICE_ERR);
}

Create a file called pam_demo_mod.c with the above code and then compile/link it with:

cc -g -c pam_demo_mod.c
ld -G  -o pam_demo_mod.so pam_demo_mod.o -lpam

If it works, you should have a pam_demo_mod.so module in your current directory. This is your newly created module. As I mentioned in part 1 of this series, it can be really simple to implement a PAM module.

Now, to show you how this works, we'll have to create another PAM service in our IDS instance.
This is similar to what we've done for LDAP authentication. Follow this steps:

1. Edit $INFORMIXDIR/etc/$ONCONFIG and add another DBSERVERALIAS (let's call it cheetah_chg):
   
   

   DBSERVERALIASES cheetah_pam,cheetah_chg,cheetah_drda                # List of alternate dbservernames
   

   
   
2. Edit your $INFORMIXSQLHOSTS and configure this new service:
   
   

   
   cheetah_chg   onsoctcp      pacman.onlinedomus.net     1532 s=4,pam_serv=(ids_pam_challenge),pamauth=(challenge)
   

   
   Please note:
1. We must choose a different PAM service name (ids_pam_challenge)
2. The pamauth option is set to "challenge"
3. Create a file called /etc/pam.d/ids_pam_challenge with the following line:
   auth required pam_demo_mod.so
4. Copy your newly created pam_demo_mod.so to /lib/security (or any other place where your distribution keeps the PAM modules)
5. Restart your IDS instance

After configuring the server side, it's time to show you the client side. You must use a second machine for this. Follow the following steps:

1. Make sure you have a stores demo called stores_demo
2. cd to $INFORMIXDIR/demo/esqlc (in your CSDK installation)
3. Edit the file called pamdemo.ec: Check the SELECT statement in that file and change it's WHERE clause to "customer_num=104"
   
4. compile pamdemo.ec:
   
   

   
   esql -o pamdemo.exe pamdemo.ec

   
   
5. Setup the $INFORMIXSQLHOST file to have the new port we just created in the other host
   
6. set the correct INFORMIXSERVER:
   

   INFORMIXSERVER=cheetah_chg;export INFORMIXSERVER

   
   
7. Run pamdemo.exe:
   

   ./pam_demo.exe

Answer what you see in parenthesis. You will see something like this:


cheetah_chg@manicminer:fnunes-> ./pamdemo.exe
Starting PAM demo
Callback function registered.
Your school name (MIT):: 1:MIT
Your maiden name (SUZE):: 2:SUZE
SQLCODE ON CONNECT = 0
John Doe

PAM DEMO run completed successfully


You can follow the code, but in brief what happened was that the PAM module we used raised two challenges which were handled and forward to the user by the call back function.
The first challenge expects an answer "MIT" an the second expects "MIT". If any of those are wrong the authentication will fail. The message showed to the user is defined by the callback function, but it can use parameters passed by the module. As I wrote above, this can have the user intervention or not. It's up to the programmer to decide and to implement it in the callback function.
This ends the topic of challenge response and implicit connections using PAM.


How to setup distributed query environments using PAM:

One of the greatest advantage of using PAM comes in fact from a limitation... When you are connected to instance "A" and want to access a database/table in instance "B" you do a distributed query. Something like:

SELECT field1, field2
FROM remote_db@remote_instance:remote_table
WHERE ...

In a typical Informix installation, this would require a trusted relation between the host running the instance to which the application is connected and the host running the remote instance.
Once you setup this trust it would be active for any instance... so you would have to be very careful with the database privileges.
Since PAM works by raising challenges between host and client, and because a distributed query can happen at any time, it would be impossible to establish connection between instances/clients when a distributed query was sent. The solution to this problem introduced a great functionality (IMHO it should have been like this since ever)... You may have noticed that since 9.4+ you have another system database called sysuser.
In this database you'll see a table called sysauth with the following structure:

username char(32)
groupname char(32)
servers char(128)
hosts char(128)

This is the place where you define the "trust" relations between your instance and remote hosts and Informix servers.

I will extend my example above... As you may have noticed I have a host called "Pacman" running an IDS 11.10 instance with the names cheetah (standard port), cheetah_pam (pam in password mode), cheetah_chg (PAM in challenge mode) and cheetah_drda (the new protocol DRDA). The other host I used to run the pamdemo.exe example is called "ManicMiner". In this host I have created another instance called cheetah2. I then start dbaccess on manicminer against instance cheetah2 by running:

cheetah2@manicminer:informix-> dbaccess stores_demo -

Database selected.

> select * from stores_demo@cheetah:customer;

956: Client host or user informix@manicminer is not trusted by the server.

No such file or directory
Error in line 1
Near character position 42
>

The error is expected. I tried to connect to the remote instance normal authentication port and have no trust between Pacman and ManicMiner. Let's see what happens with the PAM enabled port:

cheetah2@manicminer:informix-> dbaccess stores_demo -

Database selected.

> select * from stores_demo@cheetah_pam:customer;

950: User informix@manicminer is not known on the database server.
Error in line 1
Near character position 46

Interesting... I can't access it, as expected, but we have a different error (-950, instead of the usual -956). Let's check it with finderr:

User username is not known on the database server.

This statement refers to a database on another computer system, but the other database server does not accept your account name. You must have a valid trusted login identity on any database server you access remotely. See your database administrator about putting your login ID in 'sysuser:sysauth' on the remote server. This message appears on PAM enabled servers.

Well, it explicitly tells us to use the sysauth table of the sysuser database of the remote instance... So let's get back to the Pacman server and run:

cheetah:PacMan.onlinedomus.net:informix-> dbaccess sysuser -

Database selected.

> insert into sysauth (username, servers, hosts ) values ( 'informix', 'cheetah2', 'manicminer' );

1 row(s) inserted.

>

And now, again on ManicMiner, the same query:

cheetah2@manicminer:informix-> dbaccess stores_demo -

Database selected.

> select * from stores_demo@cheetah_pam:customer;



customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2
city          Sunnyvale
state         CA
zipcode       94086
phone         408-789-8075

...

28 row(s) retrieved.

So, in order to authorize distributed queries in PAM enabled engines/ports, you have to setup the authorizations in the sysauth table of the sysuser database.
You need to specify the username, the instance of origin (servers column) and the hosts originating the query (hosts column). Note the following:

• Although it's possible to specify more than one server or host in the respective columns (separated by spaces and/or commas), you should always indicate only one server/host per row. This will be easier to maintain (if you need to remove for example) using scripting and it's possible this may change, and in the future it may only support one value
• The host field specifies the machine where the query comes from. This will be the remote instance host and not the client application host. The existence of both servers and hosts columns allows you to have two instances with the same name in your organization and distinguish between them. I wouldn't suggest having two instances with the same name, but you may want to have it. One for production and the other for quality for example.
• The groupname apparently is not used at this time. Personally I would love to see this field be used for specifying the default role for the user... But this is just my wish...
  

Can we develop customized PAM modules?


Well, the quick answer is yes. Above is an example of a dummy PAM module. As you can see it's not very complex. You just have to send challenges and receive responses. You may even just check certain properties setup by the PAM framework and decide if they verify certain criteria (things like time of day, number of sessions for that user, machine load etc.). I would say that with PAM, sky is the limit... But as we'll see further ahead there are still some limitations.

One thing that you must have in mind, in case you think about writing your own modules is security. Be aware, that you'll be defining your database authentication mechanism. You have to be careful! Your code cannot compromise authentication security nor database stability and performance. But don't let this scare you. There are plenty of PAM modules using open source licensing schemes... So, there's a good chance that whatever you need was already developed by someone, and if not, you can get examples and PAM modules skeletons to start with.


What are the current IDS PAM limitations?


Unfortunately, by the time this article is being written, there are quite a few limitations. I will try to enumerate them, and at the same time give you some insight on the status of these limitations. Some of them are perfectly known to the R&D teams, and they'll solve it as soon as possible. But given the current release schedule and the list of features they want to put in IDS it's not easy to start working on some of this issues. Let's see the list. Bear in mind this is my personal view. Nothing here represents IBM positions on any of these topics, and this does not represent any assumption on how or when these issues may be addressed:

1. PAM is not yet supported in .NET clients
   This is a known issue.
2. PAM authentication still requires that the OS knows about the user
   The reasons for this odd behavior relate to some features that depend on the existence on an OS identity: SET EXPLAIN to file and SYSTEM command in the stored procedures are two examples of this
   R&D is aware of this problem and accordingly to some talks they already have ideas for solving this
3. PAM layer does not setup all the PAM framework variables. The PAM API defines these internal variables:
• PAM_SERVICE
  The service name
• PAM_USER
  The user name
• PAM_AUTHTOK
  The user authentication token
• PAM_OLDAUTHTOK
  The old user authentication token
• PAM_TTY
  The tty name
• PAM_RHOST
  The remote host name
• PAM_RUSER
  The remote user name
• PAM_CONV
  The pam_conv structure



At this time, IDS does not establish PAM_RHOST and PAM_RUSER. PAM_RHOST would be good to have... PAM_RUSER is also not set but I have doubts it could be securely establish by IDS. The client libraries can send it, but to assume this is secure would be a mistake since the connection packets can, theoretically be handcrafted.
I personally believe adding PAM_RHOST would be an easy fix, but I don't know the code...

4. The groupname field of the sysuser:sysauth table is apparently not used. Accordingly to same sources this would be the OS group of the user. I really don't see great advantage in this. What I would love to see is a way to specify the default role for a remote user (eventually different from his default role, even if this is defined). This would make sense, because we would be able to specify the user default role depending on the connection origin for distributed queries
5. Documentation!
   PAM is an area where the documentation doesn't live up to its standards (remember that Informix manuals received industry recognition...). All the features should be clearly documented... In fact, this was the main reason to publish these two articles

I think I covered most of the aspects of PAM authentication in IBM Informix Dynamic Server.
With this and the examples on this and the previous article I think anyone should have enough information to test it.

I'd like to end with some references to other material that may be useful, and relates to this:

• Documentation on PAM from the creator (SUN):
  http://www.sun.com/software/solaris/pam/pam.external.pdf
  
• Using PAM in JDBC (includes an example of a PAM module) from DeveloperWorks
  http://www.ibm.com/developerworks/db2/library/techarticle/dm-0704anbalagan/
  
• Using PAM in ESQL/C from DeveloperWorks
  http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/0306mathur/0306mathur.html
  
• Technical note on PAM usage in 4GL applications
  http://www-1.ibm.com/support/docview.wss?uid=swg21243682
  
• The Linux PAM application developer's Guide
  http://www.kernel.org/pub/linux/libs/pam/Linux-PAM-html/Linux-PAM_ADG.html

As always, if you have any comment or doubt don't hesitate in leaving a comment or contact me.
Hope this helps to clarify PAM authentication in IDS. Hopefully more customer feedback and requirements will give R&D more reasons to solve the issues and to improve this functionality.