/

MySQL User Permissions

MySQL User Permissions

In this blog, we will discuss user permissions in a MySQL database and how to grant privileges to a user.

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

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

the user has very limited capabilities. They cannot read or modify data from any existing database, nor can they create a new database. To allow a user to perform actions, you need to grant them privileges.

You can grant privileges using the GRANT command, followed by the specific permissions keywords. Here are some examples:

  1. Granting privilege to create new databases to a user:
1
GRANT CREATE ON *.* TO '<username>'@'localhost';
  1. Granting privileges to a user to create new tables in a specific database:
1
GRANT CREATE ON <database>.* TO '<username>'@'localhost';
  1. Granting privilege to read (query) a specific database to a user:
1
GRANT SELECT ON <database>.* TO '<username>'@'localhost';
  1. Granting privilege to read a specific database table to a user:
1
GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';
  1. Granting privilege to insert, update, and delete rows in a specific database to a user:
1
GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';
  1. Granting privilege to delete tables in a specific database to a user:
1
GRANT DROP ON <database>.* TO '<username>'@'localhost';
  1. Granting privilege to delete databases to a user:
1
GRANT DROP ON *.* TO '<username>'@'localhost';
  1. Granting all privileges on a specific database to a user:
1
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';
  1. Granting all privileges to a user:
1
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';

To revoke a privilege, you can use the REVOKE command. For example, to revoke the DROP privilege on a specific database:

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

To revoke all privileges, you can run:

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

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

1
SHOW GRANTS FOR '<username>'@'localhost';

Tags: MySQL, Database, User Permissions, GRANT, REVOKE