GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege levels:

For the GRANT and REVOKE statements, priv_type may be specified as any of the following:

USAGE can be used when you want to create a user that has no privileges.

The privileges CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER are new for in version 4.0.2. To use these new privileges after upgrading to 4.0.2, you have to run the mysql_fix_privilege_tables script. Section 2.5.6, “Upgrading the Grant Tables ”.

In older MySQL versions, the PROCESS privilege gives the same rights as the new SUPER privilege.

To revoke the GRANT privilege from a user, use a priv_type value of GRANT OPTION:

mysql REVOKE GRANT OPTION ON ... FROM ...;

The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER.

The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

MySQL allows you to create database level privileges even if the database doesn't exist, to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exist. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.

You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don't have a current database, you will affect the global privileges!)

Please note: the _ and % wildcards are allowed when specifying database names in GRANT commands. This means that if you wish to use for instance a _ character as part of a database name, you should specify it as \_ in the GRANT command, to prevent the user from being able to access additional databases matching the wildcard pattern, for example, GRANT ... ON `foo\_bar`.* TO ....

In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user_name value in the form user@host. If you want to specify a user string containing special characters (such as -), or a host string containing special characters or wildcard characters (such as %), you can quote the user or host name (for example, 'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@'%.loc.gov' applies to user for any host in the loc.gov domain, and user@'144.155.166.%' applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%".

MySQL doesn't support wildcards in user names. Anonymous users are defined by inserting entries with User=" into the mysql.user table or creating an user with an empty name with the GRANT command.

Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!

You can verify if this applies to you by executing this query:

mysql SELECT Host,User FROM mysql.user WHERE User='';

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A user name can be up to 16 characters.

The privileges for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, this can't be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in Section 4.2, “General Security Issues and the MySQL Access Privilege System ”.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.

In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

If you don't want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD() or the C API function make_scrambled_password(char *to, const char *password).

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.

Passwords can also be set with the SET PASSWORD command. Section 5.5.6, “SET Syntax ”.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). The same is true for SHOW DATABASES.

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT privilege, as two users with different privileges may be able to join privileges!

MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2. These options limit the number of queries/updates and logins the user can do during one hour. If # is 0 (default), then this means that there are no limitations for that user. Section 4.3.6, “Limiting user resources ”. Note: to specify any of these options for an existing user without adding other additional privileges, use GRANT USAGE ON *.* ... WITH MAX_....

You cannot grant another user a privilege you don't have yourself; the GRANT privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the GRANT privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, the user can give away not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can give away the INSERT, SELECT and UPDATE.

You should not grant ALTER privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!

Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL a bit.

When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. Section 4.3.3, “When Privilege Changes Take Effect ”.

The biggest differences between the SQL standard and MySQL versions of GRANT are:

For a description of using REQUIRE, see Section 4.3.9, “Using Secure Connections ”.

There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows:

MySQL users and their privileges are normally created with the GRANT command. Section 4.3.1, “GRANT and REVOKE Syntax ”.

When you login to a MySQL server with a command-line client you should specify the password with -password=your-password. Section 4.2.8, “Connecting to the MySQL Server ”.

mysql --user=monty --password=guess database_name

If you want the client to prompt for a password, you should use -password without any argument

mysql --user=monty --password database_name

or the short form:

mysql -u monty -p database_name

Note that in the last example the password is not 'database_name'.

If you want to use the -p option to supply a password you should do so like this:

mysql -u monty -pguess database_name

On some systems, the library call that MySQL uses to prompt for a password will automatically cut the password to 8 characters. Internally MySQL doesn't have any limit for the length of the password.

When mysqld starts, all grant table contents are read into memory and become effective at that point.

Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately.

If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise, your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference!

When the server notices that the grant tables have been changed, existing client connections are affected as follows:

After installing MySQL, you set up the initial access privileges by running scripts/mysql_install_db. Section 2.3.1, “Quick Installation Overview ”. The mysql_install_db script starts up the mysqld server, then initialises the grant tables to contain the following set of privileges:

Note: the default privileges are different for Windows. Section 2.6.1.3, “Running MySQL on Windows ”.

Because your installation is initially wide open, one of the first things you should do is specify a password for the MySQL root user. You can do this as follows (note that you specify the password using the PASSWORD() function):

shell mysql -u root mysql
mysql SET PASSWORD FOR root@localhost=PASSWORD('new_password');

Replace 'new_password' with the password that you want to use.

If you know what you are doing, you can also directly manipulate the privilege tables:

shell mysql -u root mysql
mysql UPDATE user SET Password=PASSWORD('new_password')
    -     WHERE user='root';
mysql FLUSH PRIVILEGES;

Another way to set the password is by using the mysqladmin command:

shell mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for other users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new_password').

Note that if you update the password in the user table directly using UPDATE, you must tell the server to re-read the grant tables (with FLUSH PRIVILEGES), because the change will go unnoticed otherwise.

Once the root password has been set, thereafter you must supply that password when you connect to the server as root.

You may wish to leave the root password blank so that you don't need to specify it while you perform additional setup or testing. However, be sure to set it before using your installation for any real production work.

See the scripts/mysql_install_db script to see how it sets up the default privileges. You can use this as a basis to see how to add other users.

If you want the initial privileges to be different from those just described above, you can modify mysql_install_db before you run it.

To re-create the grant tables completely, remove all the .frm, .MYI, and .MYD files in the directory containing the mysql database. (This is the directory named mysql under the database directory, which is listed when you run mysqld -help.) Then run the mysql_install_db script, possibly after editing it first to have the privileges you want.

Note: for MySQL versions older than Version 3.22.10, you should not delete the .frm files. If you accidentally do this, you should copy them back from your MySQL distribution before running mysql_install_db.

You can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. Section 4.3.1, “GRANT and REVOKE Syntax ”.

There are also several contributed programs (such as phpMyAdmin) that can be used to create and administrate users.

The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the INSERT privilege for the mysql database and the RELOAD administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands here.

First, use the mysql program to connect to the server as the MySQL root user:

shell mysql --user=root mysql

Then you can add new users by issuing GRANT statements:

mysql GRANT ALL PRIVILEGES ON *.* TO monty@localhost
    -     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT ALL PRIVILEGES ON *.* TO monty@'%'
    -     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql GRANT USAGE ON *.* TO dummy@localhost;

These GRANT statements set up three new users:

You can also add the same user access information directly by issuing INSERT statements and then telling the server to reload the grant tables:

shell mysql --user=root mysql
mysql INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
    -          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
    -          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql INSERT INTO user SET Host='localhost',User='admin',
    -           Reload_priv='Y', Process_priv='Y';
mysql INSERT INTO user (Host,User,Password)
    -                  VALUES('localhost','dummy','');
mysql FLUSH PRIVILEGES;

Depending on your MySQL version, you may have to use a different number of 'Y' values above. (Versions prior to Version 3.22.11 have fewer privilege columns, and versions from 4.0.2 on have more.) For the admin user, the more readable extended INSERT syntax using SET that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

In the last INSERT statement (for the dummy user), only the Host, User, and Password columns in the user table record are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can access the bankaccount database only from the local host, the expenses database only from the host whitehouse.gov, and the customer database only from the host server.domain. He wants to use the password obscure from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

shell mysql --user=root mysql
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -     ON bankaccount.*
    -     TO custom@localhost
    -     IDENTIFIED BY 'obscure';
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -     ON expenses.*
    -     TO custom@'whitehouse.gov'
    -     IDENTIFIED BY 'obscure';
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -     ON customer.*
    -     TO custom@'server.domain'
    -     IDENTIFIED BY 'obscure';

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shell mysql --user=root mysql
mysql INSERT INTO user (Host,User,Password)
    - VALUES('localhost','custom',PASSWORD('obscure'));
mysql INSERT INTO user (Host,User,Password)
    - VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql INSERT INTO user (Host,User,Password)
    - VALUES('server.domain','custom',PASSWORD('obscure'));
mysql INSERT INTO db
    - (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    -  Create_priv,Drop_priv)
    - VALUES
    - ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql INSERT INTO db
    - (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    -  Create_priv,Drop_priv)
    - VALUES
    - ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql INSERT INTO db
    - (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    -  Create_priv,Drop_priv)
    - VALUES('server.domain','customer','custom','Y','Y','Y','Y','Y','Y');
mysql FLUSH PRIVILEGES;

As in the preceding example that used INSERT statements, you may need to use a different number of 'Y' values, depending on your version of MySQL.

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, after you modify the grant tables directly , you must tell the server to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain (for example, mydomain.com), you can issue a GRANT statement like the following:

mysql GRANT ...
    -     ON *.*
    -     TO myusername@'%.mydomain.com'
    -     IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysql INSERT INTO user VALUES ('%.mydomain.com', 'myusername',
    -             PASSWORD('mypassword'),...);
mysql FLUSH PRIVILEGES;

Starting from MySQL 4.0.2 one can limit certain resources per user.

So far, the only available method of limiting usage of MySQL server resources has been setting the max_user_connections startup variable to a non-zero value. But this method is strictly global and does not allow for management of individual users, which could be of particular interest to Internet Service Providers.

Therefore, management of three resources is introduced on the individual user level:

A user in the aforementioned context is a single entry in the user table, which is uniquely identified by its user and host columns.

All users are by default not limited in using the above resources, unless the limits are granted to them. These limits can be granted only via global GRANT (*.*), using this syntax:

GRANT ... WITH MAX_QUERIES_PER_HOUR N1
               MAX_UPDATES_PER_HOUR N2
               MAX_CONNECTIONS_PER_HOUR N3;

One can specify any combination of the above resources. N1, N2, and N3 are integers and represent counts per hour.

If a user reaches the limit on number of connections within one hour, no further connections will be accepted until that hour is up. Similarly, if the user reaches the limit on number of queries or updates, further queries or updates will be rejected until the hour is up. In all cases, an appropriate error message shall be issued.

Current usage values for a particular user can be flushed (set to zero) by issuing a GRANT statement with any of the above clauses, including a GRANT statement with the current values.

Also, current values for all users will be flushed if privileges are reloaded (in the server or using mysqladmin reload) or if the FLUSH USER_RESOURCES command is issued.

The feature is enabled as soon as a single user is granted with any of the limiting GRANT clauses.

As a prerequisite for enabling this feature, the user table in the mysql database must contain the additional columns, as defined in the table creation scripts mysql_install_db and mysql_install_db.sh in scripts subdirectory.

In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. Section 4.3.1, “GRANT and REVOKE Syntax ”.

The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:

shell mysql -u root mysql
mysql INSERT INTO user (Host,User,Password)
    - VALUES('%','jeffrey','biscuit');
mysql FLUSH PRIVILEGES;

The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD(), generates an authentification vector based on encrypted password and a random number, obtained from server, and sends the result to the server. The server uses the password value in the user table (that is not encrypted value 'biscuit') to perform the same calculations, and compares results. The comparison fails and the server rejects the connection:

shell mysql -u jeffrey -pbiscuit test
Access denied

Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead:

mysql INSERT INTO user (Host,User,Password)
    - VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD() function when you use SET PASSWORD statements:

mysql SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this:

mysql GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

or

shell mysqladmin -u jeffrey password biscuit

Note: PASSWORD() is different from Unix password encryption. Section 4.3.2, “MySQL User Names and Passwords ”.

It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method:

All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected .my.cnf file.