Oracle SQL: the Essential Reference

CREATE/ALTER/DROP ROLE

CREATE ROLE rolename [NOT IDENTIFIED | IDENTIFIED BY password | IDENTIFIED EXTERNALLY]

Creates a role, which is a set of privileges that can be granted to users.

ALTER ROLE rolename 
  [NOT IDENTIFIED | IDENTIFIED BY password | IDENTIFIED EXTERNALLY]

Changes the authorization level required to enable a role.

DROP ROLE rolename 

Removes a role from the database.

Keywords

rolename

Name of the role to be created.

NOT IDENTIFIED

Specifies that a user who was granted the role does not need to be verified when enabling it.

IDENTIFIED BY

Specifies that the password must be provided when enabling the role.

IDENTIFIED EXTERNALLY

Specifies that the operating system verifies the user enabling the role.

When you create a role, you are automatically granted that role WITH ADMIN OPTION, which allows you to grant or revoke the role or modify it using the ALTER ROLE command.

Examples

Create a role called manager and assign the password ‘dilbert’ to it:

CREATE ROLE manager IDENTIFIED BY dilbert;

Change the existing MANAGER role to use operating system authentication:

ALTER ROLE manager IDENTIFIED EXTERNALLY;

Remove the manager role from the database:

DROP ROLE manager;