Controlling Database Use

Informix database software provides the means to control database use.When you design a database, you can perform any of the following functions:

Access-Management Strategies

A database administrator (DBA) can set roles to standardize and change the privileges of many users by treating them as members of a class. When the DBA assigns privileges to that role, every user of that role has those privileges. In order to enable these roles, a user must issue a SET ROLE statement. The SQL statements used for defining and manipulating roles include: CREATE ROLE, DROP ROLE, GRANT, REVOKE, and SET ROLE.

To create and grant a role

Use the CREATE ROLE statement to create a new role in the current database.
Use the GRANT statement to grant privileges to that role
Use the GRANT ROLE statement to grant the role to a user or to PUBLIC (all users).
The user must issue the SET ROLE statement to enable that role.

For more information on the SQL syntax statements for defining and manipulating roles, see the IBM Informix: Guide to SQL Syntax.

The DBA can define a default role to assign a role to individual users or to the PUBLIC group for a particular database. The role is automatically activated when the user establishes a connection with the database, without the user needing to issue a SET ROLE statement. Each user has whatever privileges are granted to the user individually, as well as the privileges of the default role.

To define and grant privileges for a default role

Use the CREATE ROLE statement to create a new role in the current database.
Use the GRANT statement to grant privileges to the role.
Grant the role to a user and set the role as the default user or PUBLIC role using the following syntax:
```
GRANT DEFAULT ROLE rolename TO username 
```
or
```
GRANT DEFAULT ROLE rolename TO PUBLIC
```
Use the REVOKE DEFAULT ROLE statement to disassociate a default role from a user.
Only the DBA or the database owner can remove the default role.
Use the SET ROLE DEFAULT statement to reset the current role back to the default role.

For security reasons, the DBA can create roles that have limited access. For instance, only the Database System Administrator (DBSA) or users to whom the DBSA has granted the built-in EXTEND role can create or drop UDRs that are defined with the EXTERNAL keyword. For more information on the External Routine Reference segment or SQL statements for defining and manipulating roles, see the IBM Informix: Guide to SQL Syntax.

For more information on default roles, see the IBM Informix: Administrator's Guide.

For more information about how to grant and limit access to your database, see the IBM Informix: Database Design and Implementation Guide.