In MySQL how do you grant all privileges on a database to a user?

Richard C.

The Problem

Once you’ve created a new database in MySQL, how do you give users permissions to use it? And how do you grant a new user all privileges to a database?

The Solution

Assume that you just created a new database in MySQL, like so:

CREATE DATABASE Test;

If you have no users you can create one:

CREATE USER 'alice'@'%' IDENTIFIED BY 'thepassword';

In the above command, @'%' specifies from where the user may connect. The wildcard character '%' allows a user to connect from any machine. You might also restrict them to a specific IP address, such as @'127.0.0.1' for the localhost.

To grant permissions to your new user, or your existing users, to do anything with the new database, such as modifying tables and their content, run the following command:

GRANT ALL PRIVILEGES ON Test.* TO 'alice'@'%';

Test.* means every object in the Test database.

Do not append WITH GRANT OPTION to the end of the command. You cannot give a user permissions to alter other users only for a specific database. If you want your user to be able to create and edit other users, you instead have to give the user permissions for the entire server:

GRANT ALL PRIVILEGES ON *.* TO 'alice'@'%';

*.* means every object in every database.

Be careful when using this command, as it creates a superuser.

You do not need to run the command FLUSH PRIVILEGES after altering user permissions. This command is necessary only when directly editing the grant tables manually with updated SQL queries, or when importing users from a backup file. However, your users may need to log out and in again for their session to have the new permissions.

Your user should now be able to create a table in the new database. You can test this by logging in as Alice and running:

CREATE TABLE MyTable (Id INT);

She should not need to be able to create users. You can test this by checking that the following command fails:

CREATE USER 'bob'@'%' IDENTIFIED BY 'thepassword';

If you wish to limit a user’s permissions to only altering data, and not altering table structure, you can grant specific permissions:

GRANT SELECT, INSERT, UPDATE, DELETE ON Test.* TO 'alice'@'%'

Finally, if you wish to revoke Alice’s permissions to the database, you can run the command below. Remember though that it won’t take effect until she logs out.

REVOKE ALL PRIVILEGES ON Test.* FROM 'alice'@'%';