SQL in a Nutshell

Name

CREATE ROLE

Synopsis

CREATE ROLE allows the creation of a named set of privileges that may be assigned to users of a database. When a user is granted a role, that user also gets all the privileges and permissions of that role.

Vendor	Command
SQL Server	Not supported
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

Microsoft SQL Server does not support the CREATE ROLE command, but has the equivalent capability via the system stored procedure sp_add_role .

SQL99 Syntax and Description

CREATE ROLE role_name [WITH ADMIN {CURRENT_USER | CURRENT_ROLE}]

This statement creates a new role, and differentiates that role from a host-DBMS user. The WITH ADMIN clause allows assigns a role immediately to the currently active user or currently active role. By default, the statement defaults to WITH ADMIN CURRENT_USER.

Oracle Syntax and Variations

CREATE ROLE role_name [NOT IDENTIFIED | IDENTIFIED
   {BY password | EXTERNALLY | GLOBALLY}]

In Oracle, the role is created first, then granted privileges and permissions as if it is a user via the GRANT command. When users want to get access to the permissions of a role protected by a password, they use the SET ROLE command. If a password is placed on the role, any user wishing to access it must provide the password with the SET ROLE command.

Oracle ships with several preconfigured roles. CONNECT, DBA, and RESOURCE are available in all versions of Oracle. EXP_FULL_DATABASE and IMP_FULL_DATABASE are newer roles used ...