Granting Roles

Assigning roles to a user uses the same syntax as the command that is used to grant a system privilege to the user, regardless of what privileges are in the role.

The following command would grant the developer role to Amandya WITH ADMIN OPTION:

 GRANT ROLE developer TO Amandya WITH ADMIN OPTION; 

The user who creates the role is implicitly assigned WITH ADMIN OPTION. Any user who has been granted a role but has not been granted it WITH ADMIN OPTION requires the GRANT ANY ROLE system privileges to grant or revoke the role to and from other users.

The maximum number of database roles that any user can enable is set by the MAX_ENABLED_ROLES initialization parameter. MAX_ENABLED_ROLES can take on a range of values from 1 to 148, and it defaults to 20. Actual roles and subroles for a user can be MAX_ENABLED_ROLES + 2 (Public and the user's own role). A change to this parameter requires a database restart.

Default Roles

Any given user can be assigned many roles. A default role is a distinct subset of those roles automatically enabled whenever that user logs in. By default, all of a user's assigned roles are enabled whenever that user logs in without the need of a password. You can limit the default roles for a user with the ALTER USER command.

The DEFAULT ROLE clause can only apply to roles that have already been granted directly to a user with the GRANT statement, not to embedded roles that have been granted to other roles. The DEFAULT ROLE clause cannot be used to enable the following:

• Roles that have not been granted to the user

• Roles that have been granted to the user through other roles to which they have been granted

The following is an example of the generic form of the DEFAULT ROLE clause:

 ALTER USER username DEFAULT ROLE {role [,role, role... |ALL [EXCEPT role [,role, role ... ] |NONE } 

ALL causes all the roles that have been granted to a user the default roles, except those listed in an EXCEPT clause.

EXCEPT indicates that the roles following the keyword should be excluded from being default roles.

NONE makes none of the granted roles the user default role.

If NONE has been used when setting the default role, the only privileges enabled when the user logs in are privileges that have been directly assigned to the user.

Because roles have to be granted before they can be default roles, you cannot use the DEFAULT ROLE with the CREATE USER command.