Quick Search:
 The Oracle ROLE Keyword      [Return To Index] Jump to:  

Term: ROLE

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 page.

Example Syntax:

CREATE ROLE <role_name>
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.

Example Usage

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.


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 Links:

Related Code Snippets:
  • DBA Roles - Returns a list of grantees and granted roles.
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org