O'Reilly logo

SQL in a Nutshell, 3rd 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

GRANT Statement

The GRANT statement assigns privileges to users and roles, allowing them to access and use database objects. 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. In other words, on most platforms it can be used to assign both object and usage privileges.

Platform

Command

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL Server

Supported, with variations

SQL2003 Syntax

The SQL2003 syntax for GRANT allows for 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

GRANT object privilege[, . . . ]

Grants privileges to issue one or more SQL statements. Multiple privileges may be granted at one time, separated by commas. You should not combine ALL PRIVILEGES with other privileges, but the remainder may be combined in any order. Valid privileges are:

ALL PRIVILEGES

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

EXECUTE

Grants the privilege to execute any ANSI SQL routine. An ...

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