O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required