O'Reilly logo

SQL in a Nutshell, 2nd Edition 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 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 ...

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