In Oracle PL/SQL, the term ROLE refers to a set of pre-defined system and object privileges. Roles are used to give sets of privileges to multiple users as a group, rather than by assigning the same privileges to multiple users over and over, one at a time. A role is basically just a default set of privileges that you define and then grant to users to save time and provide consistency.
You create a role with all of the common privileges that users need to do their work, then grant that role to users. All the privileges in the role will be enabled for users with that grant. You may add (or remove) privileges to the role later as required.
For more complete examples of granting and revoking roles, please view our Oracle PL/SQL ROLES
CREATE ROLE <role_name>
[ NOT IDENTIFIED | IDENTIFIED
BY <password> | USING [schema.] package | EXTERNALLY | GLOBALLY;
Note that if both the IDENTIFIED and NOT IDENTIFIED parameters are omitted, the role will be created as a NOT IDENTIFIED role. Creating a role this way is a possible security risk.
- The role_name parameter is the name of the new role that is being created. This parameter is mandatory (you cannot have an unnamed role).
- The NOT IDENTIFIED parameter specifies that the role is immediately enabled and that no password is required to enable the role. Note that this is a possible security risk.
- The IDENTIFIED parameter specifies that a user must be authorized by a specified method before the role is enabled.
- The BY password parameter specifies that a user must supply a password to enable the role (this is the common usage).
- The USING package parameter specifies that you are creating an application role. An application role is one that is enabled only by applications using an authorized package.
- The EXTERNALLY parameter specifies that a user must be authorized by an external service to enable the role. An external service can be an operating system login, a third-party service, or other authentication mechanism.
- The GLOBALLY parameter specifies that a user is required to be authorized by the enterprise directory service to enable the role.
|Privilege ||Privilege Description |
|SELECT ||Ability to query the table with a SELECT statement. |
|UPDATE ||Ability to update rows in the table with the UPDATE statement. |
|INSERT ||Ability to add new rows to the table with the INSERT statement. |
|DELETE ||Ability to delete rows from the table with the DELETE statement. |
|REFERENCES ||Ability to create one or more constraints that refer to the table using the CONSTRAINT statement. |
|ALTER ||Ability to change the table definition (structure) with the ALTER table statement. |
|INDEX ||Ability to create an index on the table using the create INDEX statement. |
This grants the user bob the master_admin role:
GRANT master_admin TO bob;
The example below sets the role named std_user as a default role for the user named bob.
ALTER USER bob
This example drops the role named std_user. All users who have this role assigned will no longer have it in effect.
DROP ROLE std_user;
Related Code Snippets:
- DBA Roles - Returns a list of grantees and granted roles.