About Roles
A role defines a set of privileges on database objects. DBAs can define different roles for different job functions and responsibilities, then assign those roles to different users. Typically, users are members of more than one role. Roles can also be assigned to other roles.
Assigning a default role to a user gives that user access to all the objects for which the role has access privileges. A default role that has been granted other roles provides a user to which it is assigned additional access to all the objects on which the nested roles have privileges. The privileges of a role granted to another role are inherited by every user member of the grantee role.
Right-click a role in the Object List Viewer and click Modify Privileges to grant or revoke role privileges for that role on database objects selected in the form.
Right-click one or more roles in the Object List Viewer and click Modify System Privileges to grant or revoke system-level privileges for these Roles.
About Role Types
Role Type | Description |
---|---|
Internal | Database-managed roles; useful as roles that only apply to a single database platform. |
External | Directory-managed; provide capability to centralize the management of enterprise-wide roles in a directory. |
Required Privileges
You must have the CREATE ROLE privilege to create a role. This applies to both standard database roles and external roles. When you create a role, you automatically receive the creator privileges. This gives you the DROP ROLE privilege and the WITH ADMIN OPTION privilege, but does not give you the privilege to assign a default role to a user. A newly created role has no associated privileges until the privileges are granted.
Task | Required Privilege |
---|---|
To modify a user's default role | CREATE USER or DROP USER |
To grant a role to a user or other role | WITH ADMIN OPTION privilege on the role |
About Dropping a Default Role
Dropping a role removes all members from the role. If you drop a default role, the system no longer uses the role by default to check privileges when the user logs on, and does not return an error or warning. The result of dropping a role does not cascade to users logged on to the database.
For More Information
For more information on roles, see SQL Data Definition Language - Syntax and Examples and Database Administration.