Granting a Default Role

The DBA or the owner of the database (by default, user informix) can define a default role for one or more users or for PUBLIC with the GRANT DEFAULT ROLE statement. A default role is activated when the user connects to the database. The SET ROLE statement is not required to activate a default role.

Default roles are useful if users access databases through client applications that cannot modify access privileges nor set roles.

A default role can specify a set of access privileges to all the users who are assigned that role, as in the following example:

CREATE ROLE accounting
GRANT ALTER, INSERT, SELECT ON stock TO accounting
GRANT DEFAULT ROLE accounting TO mary, asok, vlad;

The last statement provides users mary, asok, and vlad with accounting as their default role. If any of these users connects to a database, that user activates whatever privileges the accounting role holds, in addition to any privileges that the user already possesses as an individual or as PUBLIC.

The role must already exist and the user must have the access privileges to set the role. If the role has not previously been granted to a user, it is granted as part of setting the default role.

If no default role is defined for a user nor for PUBLIC, then no role is set, and the existing privileges of the user are in effect.

The following example shows how the default role can be assigned to all users:

DATABASE hrdb;
CREATE ROLE emprole;
GRANT CONNECT TO PUBLIC;
GRANT SELECT ON emptab TO emprole;
GRANT emprole TO PUBLIC;
GRANT DEFAULT ROLE emprole TO PUBLIC;

Changing the default role for a user or for PUBLIC only affects new database connections. Existing connection continue to run under currently assigned roles. If one default role was granted to user, and another default role was granted to PUBLIC, the default role granted to user takes precedence at connection time.

A default role cannot be assigned to another role. Because roles are not defined across databases, the default role must be assigned for each database. No options besides the user-list are valid after the TO keyword in the GRANT DEFAULT ROLE statement. The database server issues an error if you attempt to include the AS grantor clause or the WITH GRANT OPTION clause.