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”):
CREATE USER joe WITH PASSWORD "6Wro&XnW";
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:
CREATE DEFAULT USER WITH PROPERTIES USER "guest";
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:
$ onmode -wf USERMAPPING=BASIC
Value for USERMAPPING (BASIC) was saved in config file.
Value of USERMAPPING has been changed to BASIC.
You must add the surrogate user and its group to this file:
$ cat /etc/informix/allowed.surrogates
users:guest
groups:guest
Changes to the above file can be made effective without restarting Informix with:
$ onmode -cache surrogates
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:
GRANT privilege TO recipient;
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:
CREATE ROLE "updater";
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”:
# Generate Informix SQL statements for default read-only database access
# Doug Lawry, Oninit Consulting, January 2021
#
# Example usage:
# IFX-read-only.sh database | dbaccess -e database 2>&1 | tee IFX-read-only.log
#
# To allow updates:
# GRANT DEFAULT ROLE 'updater' TO 'whoever';
#
# Views are excluded as they do not have default public access.
# Note that DBA users have unconditional access regardless.
# See also "export NODEFDAC yes".
if [ $# != 1 ]
then
echo "Usage: $0 database" 1>&2
exit 1
fi
cd /tmp
trap '
rm -f $$.*
trap 0 2
exit
' 0 2
dbaccess $1 >$$.log 2>&1 <<!
UNLOAD TO '$$.unl' DELIMITER ' '
SELECT tabname, owner FROM systables
WHERE tabid > 99 AND tabtype = 'T'
ORDER BY 1;
!
if [ $? != 0 ]
then
egrep '^ *[-0-9]*: ' $$.log 1>&2
exit 2
fi
awk '
BEGIN {
role = "updater"
printf("CREATE ROLE \"%s\";\n", role)
}
{
printf("\n")
printf("GRANT SELECT, INSERT, UPDATE, DELETE ON %s TO \"%s\" AS \"%s\";\n", $1, role, $2)
printf("REVOKE ALL ON %s FROM PUBLIC AS \"%s\";\n", $1, $2)
printf("GRANT SELECT ON %s TO PUBLIC AS \"%s\";\n", $1, $2)
}
' $$.unl
Example output follows:
$ IFX-read-only.sh stores_demo
CREATE ROLE "updater";
GRANT SELECT, INSERT, UPDATE, DELETE ON call_type TO "updater" AS "informix";
REVOKE ALL ON call_type FROM PUBLIC AS "informix";
GRANT SELECT ON call_type TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON catalog TO "updater" AS "informix";
REVOKE ALL ON catalog FROM PUBLIC AS "informix";
GRANT SELECT ON catalog TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON classes TO "updater" AS "informix";
REVOKE ALL ON classes FROM PUBLIC AS "informix";
GRANT SELECT ON classes TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON cust_calls TO "updater" AS "informix";
REVOKE ALL ON cust_calls FROM PUBLIC AS "informix";
GRANT SELECT ON cust_calls TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO "updater" AS "informix";
REVOKE ALL ON customer FROM PUBLIC AS "informix";
GRANT SELECT ON customer TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO "updater" AS "informix";
REVOKE ALL ON employee FROM PUBLIC AS "informix";
GRANT SELECT ON employee TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON items TO "updater" AS "informix";
REVOKE ALL ON items FROM PUBLIC AS "informix";
GRANT SELECT ON items TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON manufact TO "updater" AS "informix";
REVOKE ALL ON manufact FROM PUBLIC AS "informix";
GRANT SELECT ON manufact TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO "updater" AS "informix";
REVOKE ALL ON orders FROM PUBLIC AS "informix";
GRANT SELECT ON orders TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON state TO "updater" AS "informix";
REVOKE ALL ON state FROM PUBLIC AS "informix";
GRANT SELECT ON state TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON stock TO "updater" AS "informix";
REVOKE ALL ON stock FROM PUBLIC AS "informix";
GRANT SELECT ON stock TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON tab TO "updater" AS "informix";
REVOKE ALL ON tab FROM PUBLIC AS "informix";
GRANT SELECT ON tab TO PUBLIC AS "informix";
GRANT SELECT, INSERT, UPDATE, DELETE ON warehouses TO "updater" AS "informix";
REVOKE ALL ON warehouses FROM PUBLIC AS "informix";
GRANT SELECT ON warehouses TO PUBLIC AS "informix";
After running the above, users by default would have only read access. The following would then give “joe” write access:
GRANT DEFAULT ROLE 'updater' TO joe;
The best tool to view privileges or make adjustments is InformixHQ, as shown in these screenshots:
![InformixHQ – Read-Only Access 1](https://www.oninitgroup.com/wp-content/uploads/2021/02/image1-1.png)
![InformixHQ – Read-Only Access 2](https://www.oninitgroup.com/wp-content/uploads/2021/02/image2-1.png)
![InformixHQ – Read-Only Access 3](https://www.oninitgroup.com/wp-content/uploads/2021/02/image3-1.png)
![InformixHQ – Read-Only Access 4](https://www.oninitgroup.com/wp-content/uploads/2021/02/image4.png)
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.