SQL in a Nutshell, 2nd Edition

Name

CREATE ROLE Statement

Synopsis

CREATE ROLE allows the creation of a named set of privileges that may be assigned to users of a database. When a role is granted to a user, that user gets all the privileges and permissions of that role. Roles are generally accepted as one of the best means for maintaining security and controlling privileges within a database.

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

SQL2003 Syntax

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

Keywords

CREATE ROLE role_name

Creates a new role and differentiates that role from a host DBMS user and other roles. A role can be assigned any permission that a user can be assigned. The important difference is that a role can then be assigned to one or more users, thus giving them all the permissions of that role.

WITH ADMIN {CURRENT_USER | CURRENT_ROLE}

Assigns a role immediately to the currently active user or currently active role along with the privilege to pass the use of the role on to other users. By default, the statement defaults to WITH ADMIN CURRENT_USER.

Rules at a Glance

Using roles for database security can greatly ease administration and user maintenance. The general steps for using roles in database security are:

1. Assess the needs for roles and pick the role names (e.g., administrator, manager, data_entry, report_writer, etc.).

2. Assign permissions to each role as if it were a standard database ...