Skip to content

MySQL User Permissions

A quick introduction at User Permissions in a MySQL Database

Let's see how to grant permissions (called privileges) to a user of the MySQL database

By default when you create a new MySQL user using the syntax

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

the user cannot do much. We can say that it can't to anything, actually.

It can't read data from any existing database, let alone modifying the data. And it can't even create a new database.

To make a user do anything, you have to grant privileges to it.

You can do so using the GRANT command.

We can use GRANT <permission>, using the following permission keywords:

  • CREATE
  • DROP
  • DELETE
  • INSERT
  • SELECT
  • UPDATE
  • ALL PRIVILEGES

Give privilege to create new databases to a user

GRANT CREATE ON *.* TO '<username>'@'localhost';

Give privileges to a user to create new tables in a specific database

GRANT CREATE ON <database>.* TO '<username>'@'localhost';

Give privilege to read (query) a specific database to a user

GRANT SELECT ON <database>.* TO '<username>'@'localhost';

Give privilege to read a specific database [object Object] to a user

GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';

Give privilege to insert, update and delete rows in a specific database to a user

GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';

Give privilege to delete tables in a specific database to a user

GRANT DROP ON <database>.* TO '<username>'@'localhost';

Give privilege to delete databases to a user

GRANT DROP ON *.* TO '<username>'@'localhost';

Give all privilege on a specific database to a user

GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';

Give all privileges to a user

GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

Revoke a privilege

Example to revoke the DROP privilege on <database>:

REVOKE DROP ON <database>.* TO '<username>'@'localhost';

To revoke all privileges, run:

REVOKE ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

You can visualize the privileges of a single user by running:

SHOW GRANTS FOR '<username>'@'localhost';
→ Download my free SQL Handbook!

THE VALLEY OF CODE

THE WEB DEVELOPER's MANUAL

You might be interested in those things I do:

  • Learn to code in THE VALLEY OF CODE, your your web development manual
  • Find a ton of Web Development projects to learn modern tech stacks in practice in THE VALLEY OF CODE PRO
  • I wrote 16 books for beginner software developers, DOWNLOAD THEM NOW
  • Every year I organize a hands-on cohort course coding BOOTCAMP to teach you how to build a complex, modern Web Application in practice (next edition February-March-April-May 2024)
  • Learn how to start a solopreneur business on the Internet with SOLO LAB (next edition in 2024)
  • Find me on X

Related posts that talk about database: