Abstract
Informix DBAs are often asked to provide login details for a user account that should only be allowed to view data or run reports. In other database products such as Microsoft SQL Server, a suitable database level privilege is available, but this is not so easy with Informix Dynamic Server (IDS). This article describes how to achieve this, including a script to perform the bulk of the work.
Content
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”:
Example output follows:
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:
Conclusion
This article describes the requirements for read-only access to Informix databases, and provides a script to make baseline setup easier.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
