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

Synopsis

In SQL99, the GRANT statement authorizes users and roles to access and use database objects. Most database vendors also use the GRANT statement to authorize users and roles to create database objects and execute stored procedures, functions, and so on.

Vendor

Command

SQL Server

Supported, with variations

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported, with variations

SQL99 Syntax and Description

GRANT { ALL [PRIVILEGES] }
| SELECT
| INSERT [ (column_name [,...n]) ]
| DELETE
| UPDATE [ (column_name [,...n]) ]
| REFERENCES [ (column_name [,...n]) ] 
| USAGE }[,...n]
ON { [TABLE] table_name 
| DOMAIN domain_name
| COLLATION collation_name
| CHARACTER SET character_set_name
| TRANSLATION translation_name }
TO {grantee_name |  PUBLIC}
[WITH GRANT OPTION]

The GRANT statement allows users to be authorized for one or more access privileges — SELECT, INSERT, UPDATE, DELETE , REFERENCES, or USAGE — by an authority who can grant those privileges. Each privilege allows the user to execute the specified command, while REFERENCES and USAGE provide other privileges. Multiple access privileges are specified by placing a comma between each privilege, or access to all privileges is granted with ALL. The PRIVILEGES keyword is entirely optional.

The USAGE privilege applies to any database object besides a table, while the others apply only to tables. The USAGE privilege lets users create objects based upon the definition of another, ...

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