Sentry Answers>SQL>

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

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

Richard C.

The ProblemJump To Solution

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:

Click to Copy
CREATE DATABASE Test;

If you have no users you can create one:

Click to Copy
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:

Click to Copy
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:

Click to Copy
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:

Click to Copy
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:

Click to Copy
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:

Click to Copy
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.

Click to Copy
REVOKE ALL PRIVILEGES ON Test.* FROM 'alice'@'%';
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Loved by over 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.