When you connect to an Informix database, the user name specified is authenticated in the host operating system (OS) by default (whether locally or via PAM, LDAP, etc.). From IDS 11.70, users can alternatively be defined inside the instance (which then takes precedence) as described in full here. The simplest example of a SQL statement to create such an internal user follows (all commands as user “informix”):
One advantage is that this will be replicated by HDR/RSS/SDS, so you do not need to add the user on other hosts in a cluster.
For that simplest form of SQL to work, you must already have specified a default surrogate OS user name, for example:
You can be connected to any database when the above statements are executed: users apply to the whole instance, and are stored in “sysusers:sysintauthusers”.
The relevant database engine feature must be enabled:
You must add the surrogate user and its group to this file:
Changes to the above file can be made effective without restarting Informix with:
Once the user name has been checked, Informix must then verify that access is allowed to the database. Database-level privileges are described here and are declared with:
where “privilege” is one of CONNECT, RESOURCE or DBA, and “recipient” is a user name, role (see below) or PUBLIC for everyone.
When tables are created, they have full public read/write access by default unless the NODEFDAC environment variable is set to “yes”. To create a read-only user, we must therefore first ensure that users by default do not have write access to any table. We can then grant fuller privileges just to users than really need write access, though it is far better to use a ROLE for this purpose, for example:
This is a pre-defined profile to which table privileges can be assigned using GRANT, and then the whole set applied to any number of users without having to repeat the details. Subsequent corrections only need applying once to the role. Table-level privileges are described here.
The following shell script “IFX-read-only.sh” generates SQL to revoke PUBLIC privileges except SELECT from all tables in the specified database, and allowing write access to users in role “updater”:
After running the above, users by default would have only read access. The following would then give “joe” write access:
The best tool to view privileges or make adjustments is InformixHQ, as shown in these screenshots: