The idea for this post comes from a recent discussion on c.d.i and also from checking the URL referrers that bring people here.
Most of this blog visits comes from Google or other search engines. And apparently there are a lot of questions about how Informix authenticates and establishes user connections.
I will try go give a generic overview on these subjects. I'll point out a few less known aspects of Informix authentication.
This article in no way tries to substitute the official documentation, but I hope this can be used as a concentrated information resource about the subject.
Authentication versus privilegesFirst, we have to create a distinction between two steps involved in a connection establishment.
As we all know, IDS and other databases have several privileges required for doing any action in the database.
At the first level we have Database level privileges. These are CONNECT, RESOURCE and DBA. Then we have object level privileges. For tables we have INSERT, DELETE, ALTER, REFERENCE and for column level we have SELECT and UPDATE
The list above is not exhaustive. The privileges relevant for this article are the database level ones. In particular the CONNECT privilege. Without it we will not be able to connect.
Privileges can be given using the SQL stament GRANT and removed with the REVOKE. Object level privileges can be GRANT'ed to individual users or to ROLEs. You can imagine ROLEs as groups and you can GRANT a role to individual users.
But let's get back on track. The purpose here is not to explain the privilege infra-structure, but to explain how do we connect to the database. I just explained the privileges because we need to go through two steps for establishing a connection:
- First Informix has to make sure we are who we pretend to be. This is called the authentication phase
- Second, Informix will check if the user we are defining in the connection has the required privileges to establish the connection.
It cannot do the second step without first completing the authentication.
To be able to connect we need at least CONNECT privilege on the database level. This privilege can be granted specifically to the user or to the special role PUBLIC which means "anyone"
User repositoriesOne very important aspect of Informix is that it doesn't use the concept of database only users. Any user in an Informix database must be
recognized by the underlying OS (up to the current version at the time of writing which is IDS 11.50.xC3).
It's essential to know this in order to understand how it works. For example, if you want to create a user for Informix Dynamic Server usage, you have to create it in the OS or other repository, but you
must make the OS aware of that user. And for users created in the OS, if you need to change their password you must do it using the OS tools.
Some people may consider this a big limitation, but the subject is a bit more complex than what it may look at first.
There are reasons for this requirement:
- In an Informix stored procedure, you can call an OS command or script. This is done using the SYSTEM() SPL statement. This command will be run with the user identity that was used to establish the database connection.
Other databases execute these commands as a specifically configured user or as the user running the database software. The way Informix works can give you a lot of flexibility, but the price for that is the need to have the user recognized at the OS level - When we ask the database server to print out the explain of a query using the SET EXPLAIN SQL instruction, Informix will create a file on the database server. The information is written by the database instance processes, so it has to be written on the server running the database.
This files are written with the user ID used for the connection. The file is written on the current client directory for connections local to the database server and on the users $HOME for remote connections. Again, this requires a user created in the OS - The SET DEBUG FILE/TRACE ON SPL instructions have the same behavior and requirements
The above doesn't necessarily means we need to have the users in /etc/passwd (Unix/Linux). It means that getpwnam() must be able to retrieve the user info. This will happen transparently if you configure your system to use LDAP or NIS authentication.
So you can have a central user management infra-structure like LDAP, MS Active Directory or NIS. Informix also doesn't require the user to be able to establish a session (ssh or telnet for example) on the database server at the OS level. You can for example set the users Shell to /bin/false or use other means to prevent the users to connect to the OS.
To complicate things a little bit more, we should not confuse the need to have the user id recognized in the OS with the authentication mechanism. Since 9.40 Informix can use PAM for authentication. This gives you complete freedom to implement complex and sophisticated authentication methods. You can use whatever PAM modules you desire to implement the authentication, which means your Informix authentication doesn't have to be the same as your OS authentication.
But the user has to be known by the OS due to the reasons presented before. As a quick example, you can create your users with random passwords in the OS (not known to anyone) and configure IDS to authenticate your users based on files, or any remote process using PAM.
The fact that the user has to be known in the OS is generally accepted as an inconvenience, and as such I believe it's safe to assume that in a future version Informix will relax these requirements. It could probably accept connections from users not recognized by the OS (either authenticated internally or with PAM or both) and have some parameter to define a default user for the situations where it needs a user id.
Types of connectionsInformix implements the following types of connections:
- Trusted connections
These connections are based on trusted relations between hosts. Basically the database server host is configured to trust the user if he connects from a specific host or list of hosts.
As such, this connections can be established without providing a password or any other authentication mechanism. The way it works is exactly the same as the "r" services (rshell, rcmd, rlogin).
The configuration is done using the network connectivity files, /etc/hosts.equiv and ~/.rhosts
- Non trusted connections
These connections are the standard type of connections. Typically we provide a user and a password that is used to check the identity. The password is checked against the user's stored password
- Challenge/response based authentication using PAM modules
Connections made to PAM configured ports (DBSERVERALIAS). PAM stands for Plugin Authentication Modules and can be used in any Unix/Linux system (AIX, HP-UX, Linux and Solaris)
- Distributed query connections
These connections can be considered trusted connections. But they're established implicitly when a client connected to instance "A" sends a query referencing instance "B".
In these situation the IDS server "A" will make an implicit connection on behalf of the user, to IDS server "B". The connection authentication will follow the rules for implicit connections, or will use a different authentication mechanism if server "B" is setup with PAM authentication (more on this later)
Non trusted connectionsWe use non-trusted connections when we give a user id and a token (typically a password) that guarantees our identity (only the user should know it's personal authentication secret or password) . Meaning we have not only the user id, but also it's secret key.
These connections are used mostly in applications that use JDBC, ODBC, .NET, PHP (PDO), Perl (DBI) etc. These APIs require a connection string or URL, or use specific API connection functions. So we define the password in the string, or we provide is as an argument for the API connection function.
Here's an example of a JDBC URL to connect to an Informix instance:
jdbc:informix-sqli://ids_server_machine:9088/my_database;user=username;password=secret
In ESQL/C (embedded SQL/C - which is basically C language with SQL commands) or Informix 4GL we can use the CONNECT instruction:
CONNECT TO database@ids_instance USER <"username"|variable_user> USING <variable_password>
One important note, that allows me to show the two steps in a connection establishment: authentication and database open:
Typically in 4GL we use a "database test_auth" instruction. This, by default, makes the two steps by trying to make a trusted connection using the owner of the process.
But let's create a simple 4GL program to show the difference between authentication and the database opening phase. Here's the code (don't worry if you don't know 4GL, because these lines are self explanatory):
DEFINE username,password char(20)
DEFINE c CHAR
MAIN
PROMPT "Insert your username: " FOR username
PROMPT "Insert your password: " FOR password ATTRIBUTE ( INVISIBLE )
CONNECT TO "@cheetah2" USER username USING password
PROMPT "We are authenticated, but not yet connected to a database..." FOR CHAR c
DATABASE test_auth
PROMPT "Now we have an opened database!" FOR CHAR c
END MAIN
So, we're asking the user's name and password. Than we're CONNECTing to the server. Note that I didn't use a database name. This is a supported syntax, although not very frequently used.
After the connect, we then issue the usual "DATABASE" instruction. Let's see what happens when we run the program above. First the status of the IDS instance showing the existing sessions:
cheetah2@PacMan.onlinedomus.net:informix-> onstat -u
IBM Informix Dynamic Server Version 11.50.UC2 -- On-Line -- Up 09:52:57 -- 88064 Kbytes
Userthreads
address flags sessid user tty wait tout locks nreads nwrites
4780c018 ---P--D 1 informix - 0 0 0 69 559
4780c5f0 ---P--F 0 informix - 0 0 0 0 1032
4780cbc8 ---P--F 0 informix - 0 0 0 0 596
4780d1a0 ---P--- 5 informix - 0 0 0 0 0
4780d778 ---P--B 6 informix - 0 0 0 48 0
4780e328 ---P--- 17 informix - 0 0 1 315 224
4780e900 ---P--D 9 informix - 0 0 0 3 0
4780eed8 ---P--- 16 informix - 0 0 1 1286 1093
4780f4b0 ---P--- 15 informix - 0 0 1 189 3
47810060 Y--P--D 21 informix - 440cfe28 0 0 0 0
10 active, 128 total, 18 maximum concurrent
So... Only informix user system sessions. Now let's run the program (I'll do it as root):
cheetah2@pacman.onlinedomus.net:root-> ./test.4ge
Insert your username: fnunes
Insert your password: [......][ENTER]
We are authenticated, but not yet connected to a database...
The program is now holding at the first "PROMPT" instruction after the connect. Let's see the list of sessions:
cheetah2@PacMan.onlinedomus.net:informix-> onstat -u
IBM Informix Dynamic Server Version 11.50.UC2 -- On-Line -- Up 09:57:30 -- 88064 Kbytes
Userthreads
address flags sessid user tty wait tout locks nreads nwrites
4780c018 ---P--D 1 informix - 0 0 0 69 559
4780c5f0 ---P--F 0 informix - 0 0 0 0 1032
4780cbc8 ---P--F 0 informix - 0 0 0 0 596
4780d1a0 ---P--- 5 informix - 0 0 0 0 0
4780d778 ---P--B 6 informix - 0 0 0 48 0
4780e328 ---P--- 17 informix - 0 0 1 315 224
4780e900 ---P--D 9 informix - 0 0 0 3 0
4780eed8 ---P--- 16 informix - 0 0 1 1286 1093
4780f4b0 ---P--- 15 informix - 0 0 1 189 3
4780fa88 Y--P--- 61 fnunes 4 485bff38 0 0 0 0
47810060 Y--P--D 21 informix - 440cfe28 0 0 0 0
11 active, 128 total, 18 maximum concurrent
cheetah2@PacMan.onlinedomus.net:informix-> onstat -g ses 61
IBM Informix Dynamic Server Version 11.50.UC2 -- On-Line -- Up 09:57:39 -- 88064 Kbytes
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
61 fnunes - 4 15267 pacman.o 1 40960 37280 off
tid name rstcb flags curstk status
85 sqlexec 4780fa88 Y--P--- 5728 cond wait netnorm -
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
61 V 483c3028 40960 3680 68 7
name free used name free used
overhead 0 1672 scb 0 96
opentable 0 784 filetable 0 192
misc 0 64 log 0 16512
temprec 0 1608 gentcb 0 1232
ostcb 0 2632 sqscb 0 8192
sql 0 40 hashfiletab 0 280
osenv 0 1720 sqtcb 0 2208
fragman 0 48
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
488a18a0 48558018 0 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
61 - - - Not Wait 0 0 9.29 Off
So what do we see? We have a session, on behalf of user "fnunes" but the "Current Database" is not defined. So we've just gone through the authentication process with success.
Now, if I press ENTER on the program I'll force the program to execute the DATABASE statement and wait on the second PROMPT:
cheetah2@pacman.onlinedomus.net:root-> ./test.4ge
Insert your username: fnunes
Insert your password: [...] [ENTER]
We are authenticated, but not yet connected to a database... [ENTER]
Now we have an opened database!
ok... now let's look at the database session again:
IBM Informix Dynamic Server Version 11.50.UC2 -- On-Line -- Up 10:09:32 -- 88064 Kbytes
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
61 fnunes - 4 15267 pacman.o 1 49152 45544 off
tid name rstcb flags curstk status
85 sqlexec 4780fa88 Y--P--- 5728 cond wait netnorm -
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
61 V 483c3028 49152 3608 83 6
name free used name free used
overhead 0 1672 scb 0 96
opentable 0 1792 filetable 0 352
misc 0 64 log 0 16512
temprec 0 1608 gentcb 0 1232
ostcb 0 2632 sqscb 0 13712
sql 0 40 rdahead 0 832
hashfiletab 0 280 osenv 0 1720
sqtcb 0 2792 fragman 0 208
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
488a18a0 48558018 0 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
61 - test_auth CR Not Wait 0 0 9.29 Off
So, now we have a current database, in the same session.
This ends the description of non trusted connections using username and passwords. Pretty simple, just like in any other database server.
Challenge/response connections using PAMAnother kind of non trusted connections are connections established through PAM configured DBSERVERALIAS (specific ports where the instance will listen for connections).
I will not go into details about this, because I already wrote an article about it. In
http://vietnamtech.blogspot.com/2007/11/informix-user-authentication-pam-for.html I explain how to setup and give examples of challenge/response scenarios.
In summary, a non-trusted connection in Informix is similar to connections in other RDBMS. You supply a username and a token that certifies you're entitled to connect as that user.
Typically this token is a password. But with PAM this can be a much more complex item.
In IDS 11.50 we can also use single sign on with kerberos. Due to the complexity of this scenario I've left it out in this article.
Non trusted connections are used mostly in more recent applications, like Web based applications, Windows client applications, or script languages (PHP, Perl, Ruby etc.) languages.
Trusted connectionsThese connections are made without any token to confirm the user identity. It's assumed that the user is already identified on the client system and we configure the database server to trust that identification. Trusted connections are mostly used in applications created using Informix 4GL and Informix ESQL/C.
The definition of the trusts are done by configuration of the so called network security files. These files are /etc/hosts.equiv and the ~/.rhosts (.rhosts created in each users's home dir).
Before we dig into these files content, and what it means I have to make a parenthesis to deal with something I'd almost call a myth surrounding Informix trusted connections. If you look around on the Internet you'll easy find a lot of information about how insecure is to use these files. In particular, you may found explicit instructions to not use the ~/.rhosts file.
The main reasons for these are two facts:
- These files are not specific for Informix, and in fact they were created to configure a group of services, sometimes called the "r" services. These services are rexec (allows remote execution of a command) on TCP port 512, rlogin (allows remote login) on TCP port 513 and rcmd (all remote shell execution) on TCP port 514.
- Many of these services are considered insecure and have a long history of security flaws. The most famous worm also took advantage of some of these security flaws, and also from very relaxed security setup on many of the networks connected to the Internet at the time.
Why did I referrer before the "myth"? Well, many customers have the idea that since IDS uses the same files for trusted connections authentication that you need to be running the above services for it to work. This is completely false. I cannot stress this enough. Even in the official documentation, the administrator guide, is written:
To determine whether a client is trusted, execute the following statement on the client computer: rlogin hostname If you log in successfully without receiving a password prompt, the client is a trusted computer.
Although this is not wrong, it misses the point, and gives the idea that the rlogin service should be running. The above sentence, saying that if rlogin works the user is trusted, is true. But you can create a trust for an Informix connection without having the service(s) running. In this case you'll get a "can't connect error" instead of the password prompt when you run rlogin. And it
doesn't mean it won't work with Informix.
I strongly believe Informix should not use these same files for defining it's trust relationships. But the concern that this usage raises is not really an issue. If you're concerned about security, you probably will not use the "r" services. Most of the sites which take security seriously have replaced them with ssh or something similar. So the truth is that these files should be useless in a modern system. And in this scenario, the fact that they're used by Informix should not be considered too critical, since nothing else should depend on them.
I hope this contributes to the disappearance of the "myth". Now let's see how we have to change the network security files in order to create the trusts.
The contents of the files should be similar to how you would configure "r" services trusts. Let's see the two files, because there some slight but very important differences.
- /etc/hosts.equiv
Each line in this file defines a trust relation. You can specify a host only, or a host and a user. This file is a system file. No user other than system administrator should have permission to change it.
If you specify only the host name, you're saying that any user from the remote host is trusted to connect as the same user in the local host. If you specify a "+" (plus sign) this means "any host" and you should never do that...
There is a significant difference between how Informix and "r" services interpret the file if you specify a hostname followed by a user name. For "r" services, this means that you trust the remote user on the remote system to connect as ANY user in the local system (except root). This is obviously a very serious issue... And by the way, "+ +" means any user from anywhere can login locally as any user! (don't try this even at home :) )
Informix on the other hand interprets this as a way to specify user by user and not all the users. The remote user cannot connect as another local user without providing a username and password. - ~/.rhosts
This file also includes a trust relation in each non comment line. This file, being in the user home directory, means that you allow the user on the local host to define who he trusts. Many systems verify that the file has no write permissions to the "world". In this case it ignores the settings.
A line with a host and a username means that the specified username on the specified remote host can connect as the local user owning the .rhosts file. So, for "r" services, if you specify a host and user in /etc/hosts.equiv it means that user can connect as any local user. If you do the same in ~/.rhosts you're reducing the scope of local users that the remote user can authenticate as, to only the file owner
For Informix, the interpretation is the same: The remote user can authenticate on the local machine, but without changing it's identity. It means that for example, if the local user is luser and it's ~/.rhosts contains "remotehost ruser", then this entry is useless because Informix doesn't have a way to specify the identity change in a connection attempt.
Some additional notes on these files:
- You can specify negative entries. For example you could use the following in /etc/hosts.equiv:
pacman -informix
pacman
This would allow all users from host "pacman" to connect except informix - An entry with a host name means "trust all users except root". A trust relation for root user has to be defined in it's .rhosts file.
- The order of the entries is relevant. If we exchange the order of the entries in the example above, even informix will be allowed to connect. That's because a positive entry will be found first
Let's see some examples. I have two hosts (pacman and ids1150srvr). I'm running IDS on the ids1150srvr host. If I want to allow the user informix to connect to this instance from the pacman host I can do it in the following ways (these file entries are on the ids1150srvr host):
- /etc/hosts.equiv:
pacman
pacman informix
The first entry allows all users (except root) to connect from pacman. The second will allow only informix. But be aware that if you're running "r" services you're saying that informix on pacman can connect as any user (again, except root) on ids1150srvr.
- ~informix/.rhosts
pacman
Distributed query connectionsWe've seen a client/server trusted connection. But we have another situation where we must establish trusts between systems. That's the case when you need to join data from two Informix instances. The SQL syntax to do this will look like this:
SELECT
local_table.column, remote_table.column
FROM
table1 local_table, remote_database@remote_ids_instance:table2 remote_table
WHERE
local_table.join_column = remote_table.join_column;
Assuming we are connected to a "local_ids_instance" on ServerA, and that "remote_ids_instance" is running on ServerB, this query will establish a connection on "remote_ids_instance" from the "local_ids_instance". Informix will establish this connection on behalf of our user on "local_ids_instance". As such, our user must be trusted on ServerB when connecting from ServerA. Note that the trust is not done between our client and the ServerB. It's between ServerA and ServerB. If the "remote_ids_instance" is accessed through a normal Informix port, the authentication mechanism used is the same as for trusted connections.
The situation will be different if the "remote_ids_instance" port is configured with PAM. In this situation the trust configuration is done entirely through SQL instructions. IDS versions that support PAM also have a new system database. It's called sysuser. In it we have a table called sysauth with the following schema:
{ TABLE "informix".sysauth row size = 322 number of columns = 4 index size = 295 }
create table "informix".sysauth
(
username char(32) not null ,
groupname char(32),
servers varchar(128) not null ,
hosts varchar(128) not null ,
check (groupname IS NULL )
);
revoke all on "informix".sysauth from "public" as "informix";
create unique index "informix".sysauth_idx on "informix".sysauth (username,servers,hosts) using btree ;
This table is used to define trusts for distributed queries when the destination Informix instance is configured for PAM authentication.
We must specify the username, the hostname and the IDS server from where the connection is made. Note that this not only avoids the usage of the network security files but also provides more control. With the network security files we cannot specify the Informix instance originating the connection.
So, picking up the query above, and assuming the username is "fnunes" we would need the following configuration on ServerB for it to work:
- Using /etc/hosts.equiv one of the following lines:
ServerA
# this would create a trust for all users from ServerA
ServerA fnunes
# This would restrict the trust in Informix for user fnunes.
# But for "r" services it would allow fnunes to login as any user except root on ServerB
- Using ~fnunes/.rhosts
ServerA
- Using the sysuser:sysauth table (if the port was configured for PAM):
INSERT INTO sysauth ( username, groupname, servers, hosts) VALUES ('fnunes', NULL, 'local_ids_instance', 'ServerA');
Additional notes about sysauth:
- Although the field names "servers" and "hosts" suggest you could use a list of servers or hosts the documentation says we should create one entry per user/server/host
- The field "groupname" is not used currently as expected from the check constraint
Additional notesThere are a few less none configuration details about connections in Informix. Let's check them.
- There are some options that we can specify in the sqlhosts file that control the way IDS deals with trusted connection attempts (for the specific ALIAS of the sqlhosts line).The options I'm referring to are "s=X". "s" stands for security on the server side. The following values are supported:
- s=0
Disables both hosts.equiv and rhosts lookup from the database server side (only incoming connections with passwords are accepted). - s=1
Enables only the hosts.equiv lookup from the database server side. - s=2
Enables only the rhosts lookup from the database server side. - s=3
Enables both hosts.equiv and rhosts lookup on the database server side (default setting for the database server side). - s = 6
Marks the port/ALIAS as a replication only (HDR or ER) port
If you use the option s=6 on sqlhosts options field, than you can create a file called hosts.equiv in $INFORMIXDIR/etc, owned by informix:informix and only modifiable by user informix. This makes the informix user independent of the system administrator for configuring the trusts, and additionally doesn't interfere with "r" services if they're in use
- There is another security option that defines if the client uses or not the .netrc file. I haven't yet mentioned the .netrc file... You can use this file so specify a user and password, per host that you want to connect. If you setup the file and try to make a trusted connection you'll instead make a non-trusted connection. An example of this file content would be:
ids1150srvr login fnunes password mysecret
This would connect you as user "fnunes" using password "mysecret" if you attempt to make a trusted connection to an Informix instance running on host "ids1150srvr". You can have several lines, one for each host you connect to.
There are obvious security issues derived from having a password in a file...
The option that specifies if the client libraries will look and use this file is the "r=X" settings on the sqlhosts options (client side): - r=0 it ignores the .netrc file
- r=1 it looks at the .netrc file when a client program tries to make a connection without using username and password
Windows specific informationIn MS Windows, the network security files are used in a similar way, but they're in different locations. /etc/hosts.equiv becomes %SYSTEMROOT%\system32\drivers\etc\hosts.equiv.
The .rhosts and .netrc should exist in the users HOME
Common error messages
The following is not an exhaustive list of connection errors, but a small list of the more common ones with a very brief comment. For more information about each one consult the documentation or use "finderr errorcode":
- -908 Attempt to connect to database server (servername) failed.
Usually means some problem on the TCP level. Either your server is not running or you have your client SQLHOSTS points to the wrong server or port
- -930 Cannot connect to database server servername.
Typically the client cannot obtain the server IP address. Check your DNS configuration.
- -951 Incorrect password or user user@host is not known on the database
You can get this error on the server side (online.log) and on the client side.
On the server side it means the user does not exist.
On the client side you'll see these error in three situations.. The same error is sent to the client side on different situations in case someone is trying to guess a user or password. The database system administrator can see the real problem. The situations where the error is sent to the client are: - The user does not exists on the database server
- The user's password cannot be validated, although the user exists
- The user exists, but you're trying to make a trusted connection and the trust is not setup correctly
- -952 User ()'s password is not correct for the database server.
This is the error given on the server side when the user password did not validate successfully
- -956 Client client-name or user is not trusted by the database server.
Server side error when a trusted connection fails because it's not properly setup
- -1809 Server rejected the connection.
This is the only error you'll see when a connection to a PAM configured server happens. In order to understand what went wrong you have to check the PAM stack modules messages
- -25596 The INFORMIXSERVER value is not listed in the sqlhosts file or the Reg
The INFORMIXSERVER you're trying to connect is not properly defined in you configured SQLHOSTS
SummaryI really hope this article covers most practical aspects of connection establishment in Informix. There are several aspects I left out for simplicity. These include:
- Single sign-on (introduced in IDS 11.50)
- Password encryption
- SSL connections
These topics may be covered in future articles. You can find more information about Informix connection setup in the Administrator Guide and the Security Guide.
