Log in to OpenAdmin Tool as your own user name
Manage access to OAT by managing permissions to the SQL Admin API commands for administering the Informix database server
With Informix version 12.10 and OpenAdmin Tool version 3.11, you can now manage SQL Admin API privileges for individual users. Individual users can be granted privileges to run some or all SQL Admin API commands. Privilege groups identify which SQL Admin API commands a user can run and thereby determine which features the user can access in OAT.
This functionality frees OAT from the requirement that all users must login as the user informix. Once a certain user has been granted access a set of SQL Admin API privileges, that user can log in to OAT using their own user name and monitor and administer the Informix database server as themselves. Security is enhanced when all users log in to OAT using their own user name, instead of everyone sharing the common informix account.
This feature involves two separate steps, which are described in detail in the following sections.
- Step 1: Grant SQL Admin API privileges
- Step 2: Log in to OAT as your own user name
Step 1: Grant SQL Admin API Privileges
To begin using this feature, you must first grant SQL Admin API privileges to individual users. By default, only the user informix can run SQL Admin API commands and only the user informix can grant and revoke SQL Admin API privileges to other users.
The Server Administration > User Privileges > SQL Admin API Privileges page in OAT allows you to manage SQL Admin API privileges on the database server. Before anyone can log in to OAT using their own user name, the user informix must first log in to OAT and grant SQL Admin API privileges to individual users.
The SQL Admin API commands are broken down into privilege groups which can be used to control which users can run which administration commands on the database server.
There are three global privilege groups:
- Operator which gives access to all SQL Admin API commands except the commands for granting and revoking access to the SQL Admin API
- Admin which gives access to all SQL Admin API commands including the commands for granting and revoking access to the SQL Admin API
- Monitor which gives access to all read-only SQL Admin API commands but excludes access from all commands that make changes to the database server
The remaining privilege groups break down the SQL Admin API commands by functional category. If you desire granular control of which types of commands a certain user has privileges to run, you can use these categories to, for example, grant access for a particular user to only the backup commands. A list of all SQL Admin API privilege groups can be found in Table 1.
Table 1: SQL Admin API Privilege Groups
|Operator||All the SQL Admin API commands except grant and revoke|
|Admin||All the SQL Admin API commands including grant and revoke|
|Monitor (Read-only)||All read-only SQL Admin API commands (i.e. only those that do not make changes on the database server).|
|Backup||Backup and restore commands|
|Files||General operating system file commands|
|High availability||High availability replication commands|
|Replication||cdr commands for Enterprise Replication|
|Storage||Storage and space commands|
|SQL Tracing||SQL tracing commands|
|Warehouse||Informix Warehouse Accelerator stored procedures|
|Miscellaneous||Miscellaneous commands for a variety of tasks|
|Grant||Grant and revoke privileges for the SQL Admin API commands|
For more information on SQL Admin API privilege groups or the commands contained in each group, see the "SQL administration API portal: Arguments by privilege groups" topic in the IBM Informix 12.10 Information Center.
Granting any SQL Admin API privileges automatically grants a user access to the sysadmin database. Users who are granted Monitor privileges will be given read-only access to the sysadmin database. Users who are granted access to any other privilege group will automatically be granted read/write access to the sysadmin database.
The following screenshot shows the OAT pop-up for granting SQL Admin API privileges to a user.
If you are not an OAT user, but would like to take advantage of the ability to grant other users access to the SQL Admin API, you can use the following new SQL Admin API commands.
To grant privileges
EXECUTE FUNCTION ADMIN('grant admin', <user>, <privilege group>);
To revoke a specific privilege
EXECUTE FUNCTION ADMIN(‘revoke admin', <user>, <privilege group>);
To revoke all privileges
EXECUTE FUNCTION ADMIN(‘revoke admin’, <user>);
EXECUTE FUNCTION ADMIN('grant admin', ‘john’, 'operator'); EXECUTE FUNCTION ADMIN('grant admin', 'jane', 'storage'); EXECUTE FUNCTION ADMIN('revoke admin', 'joe', ‘onstat');
Step 2: Log in to OAT as your own user name
Once you have been granted access to the SQL Admin API, you can log into OAT using your own user name and password. Security and access management to OAT and the Informix database server are enhanced when all users use their own user name and password rather than everyone sharing the common informix user.
The Informix database server enforces the permissions to the SQL Admin API commands, not OAT. This means that all OAT pages are visible to all users, but the ability to perform certain actions will be restricted by the database server based on the access level of the currently logged in user. If a user tries to run an action that they do not have the corresponding SQL Admin API privileges for, they will get an error message that they are ‘not authorized to run the command’ as show in the following screenshot.
SQL Admin API commands are logged in the sysdmin:command_history table and can be viewed on the Logs > Admin Command page in OAT. This page can be used to track which users have run which administration commands against the database server.