GRANT

This is probably the most used SQL statement for creating users and granting them privileges in MySQL. While is often used for creating users, it's main function is to set a user's privileges for a database system, specific databases, or specific tables.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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] ...]

Explanation

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 limited by number of connections or 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 a procedure. To limit the privileges to a function, use the FUNCTION keyword; 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 quotes. You may also use the asterisk wildcard (*) to specify all databases or all tables, functions, or procedures offered by the database.

In the TO clause, give the user name (in quotes) and the IP address or host (also in quotes) for which the user account privileges are permitted, separated by an at-sign (@). To provide the password for the user account, add the IDENTIFIED BY clause, followed by the user's password in plain text and enclosed in quotes. To provide the password in encrypted hash form, add the keyword PASSWORD just before the password given. You can use the WITH clause to grant the GRANT OPTION privilege to a user so that that user may execute this statement. A GRANT statement with an IDENTIFIED BY clause can be used to change a password for an existing user.

For an explanation of how to restrict user accounts based on types of connections, see the Connection Restrictions Types documentation page related to this statement. For information on how to restrict user accounts based on the amount of activity for a period of time or the number of connections permitted, see the Time and Number of Connection Limits page related to this statement. To see the privileges for a given user, use the SHOW GRANTS statement.

Examples

A large variety of privileges may be granted to a user, so a common set of privileges has been combined in the ALL keyword. Here is an example:

GRANT ALL PRIVILEGES ON *.*
TO 'evagelia'@'localhost'
IDENTIFIED BY 'papadimitrou1234'
WITH GRANT OPTION;

In this example, the user evagelia is created and granted all basic privileges because of the ALL keyword. This does not include the GRANT privilege, the ability to use the GRANT statement. To do that, the WITH GRANT OPTION clause is given, as shown here, to explicitly give that privilege to the user. It's not a good idea to give users this privilege unless they are MySQL server administrators. GRANT and REVOKE Privileges page lists and describes each privilege.

As mentioned before, a user's privileges can be refined to specific SQL statements and specific databases. A GRANT statement can also restrict a user only to certain tables and columns. Here is an example that leaves the user fairly limited:

GRANT SELECT ON workrequests.*
TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

GRANT SELECT,INSERT,UPDATE ON workrequests.workreq
TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

Assuming the user jerry does not already exist, the first statement here creates the user and gives him SELECT privileges only for the workrequests database for all of its tables. This will allow him to read from the various tables but not edit the data. The second SQL statement grants jerry the right to add and to change data in the workreq table of the workrequests database. This will allow him to enter work requests and to make changes to them. The first statement causes an entry to be made to the db table in the mysql database. The second one affects the tables_priv table. An entry is also made to the user table showing the user jerry, but he has no global privileges. This is the equivalent of granting just the USAGE privilege.