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

GRANT Statement

Synopsis

The GRANT statement assigns privileges to users and roles, which allows them to access and use database objects (that is, object privileges).

In addition, most database platforms use the GRANT statement to authorize users and roles to create database objects and execute stored procedures, functions, and so on (particularly usage privileges).

Platform

Command

DB2

Supported, with variations

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL Server

Supported, with variations

SQL2003 Syntax

The SQL2003 syntax for GRANT includes only the assignment of object privileges and roles to a specific user:

GRANT { {object privilege [,...] | role [,...]} }
[ON database_object_name]
[TO grantee [,...] ]
[WITH HIERARCHY OPTION] [WITH GRANT OPTION] [WITH ADMIN OPTION]
[FROM { CURRENT_USER | CURRENT_ROLE } ]

Keywords

object privilege

Grants one or more privileges for a SQL statement. Multiple privileges may be granted at one time, as long as commas separate them. Do not combine ALL PRIVILEGES with other privileges, but the remainder may be combined in any order. Valid privileges are:

ALL PRIVILEGES

Indicates a shorthand for every privilege the grantor has available to grant. Grants all privileges currently assigned to the grantor to the named users and/or for the specified database objects. This is generally not a recommended approach, since it can encourage sloppy permissioning.

EXECUTE

Grants the privileges ...

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