O'Reilly logo

MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer

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

GRANT privilege[,...] [(column[,...])][, ...] 
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[REQUIRE NONE |
[{SSL|X509}] [CIPHER 'cipher' [AND]] 
[ISSUER 'issue' [AND]] 
[SUBJECT 'subject']]

[WITH [GRANT OPTION | 
       MAX_QUERIES_PER_HOUR count |
       MAX_UPDATES_PER_HOUR count |
       MAX_CONNECTIONS_PER_HOUR count |
       MAX_USER_CONNECTIONS count] ...]

This statement may be used to create new MySQL users, but its primary use is for granting user privileges. Privileges can be global (apply to all databases on the server), database-specific, table-specific, or column-specific. Users can now also be limited by functions and procedures. Additionally, users can be limited by number of connections or by a maximum of resources per hour.

The privileges to grant to a user are listed immediately after the GRANT keyword in a comma-separated list. To restrict a user to specific columns in a table, list those columns in a comma-separated list within parentheses. This is then followed by the ON clause in which the privileges granted may be limited to a database, table, function, or procedure. To limit the privileges to a function, use the FUNCTION keyword; to limit them to a procedure, use the PROCEDURE keyword.

For tables, the keyword TABLE is optional and the default. You can then specify the database to which the privileges relate in quotes, followed by a period (.) and the name of the table, function, or procedure in ...

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