Creating and Controlling Roles

You can use SQL commands to create and control access to roles to provide security for your database tables.

By creating a role and then controlling who has access to it, you can ensure that only certain users have access to particular database privileges.

Roles are created and used with the SQL CREATE, GRANT, and SET commands:

• To create a role, you use the CREATE command. You can create roles with or without passwords.

• To grant access to roles, you use the GRANT command. In this way, you can control who has access to the privileges associated with the role.

• To access roles, you use the SET ROLE command. If you created the role with a password, the user must know the password in order to access the role.

For more information about roles, see your Oracle9i SQL Reference, your Oracle9i Administrator's Guide, and your Oracle9i Concepts manual.

Disabling SET ROLE

From SQL*Plus, users can submit any SQL command. In certain situations, this can cause security problems. Unless you take proper precautions, a user could use SET ROLE to access privileges obtained via an application role. With these privileges, they might issue SQL statements from SQL*Plus that could wrongly change database tables.

To prevent application users from accessing application roles in SQL*Plus, you can use the PUP table to disable the SET ROLE command. You also need to disable the BEGIN and SQL*Plus EXECUTE commands to prevent application users setting application roles through a PL/SQL block. This allows a SQL*Plus user only those privileges associated with the roles enabled when they started SQL*Plus. For more information about the creation and usage of user roles, see your Oracle9i SQL Reference and Oracle9i Administrator's Guide.

Disabling User Roles

To disable a role for a given user, insert a row in the PUP table containing the user's username in the Userid column, "ROLES" in the Attribute column, and the role name in the Char_Value column.

Note: When you enter "PUBLIC" or "%" for the Userid column, you disable the role for all users. You should only use "%" or "PUBLIC" for roles which are granted to "PUBLIC". If you try to disable a role that has not been granted to a user, none of the roles for that user are disabled.

The Scope, Numeric_Value, and Date_Value columns should contain NULL. For example:

PRODUCT    USERID  ATTRIBUTE  SCOPE   NUMERIC      CHAR       DATE
                                      VALUE        VALUE      VALUE
-------    ------  ---------  -----   --------     ------     -----
SQL*Plus   HR      ROLES                           ROLE1
SQL*Plus   PUBLIC  ROLES                           ROLE2

During login, these table rows are translated into the command

SET ROLE ALL EXCEPT ROLE1, ROLE2

To ensure that the user does not use the SET ROLE command to change their roles after login, you can disable the SET ROLE command. See "Disabling SET ROLE" earlier in this appendix.

To re-enable roles, delete the row containing the restriction.