How to create and grant permissions to a MySQL user

In this tutorial, we'll describe the common tasks you'll have to deal with when managing access to a MySQL database.

Access MySQL command line

In order to perform these actions you'll need to access MySQL command line with root access.

sudo mysql

If your administrator account requires a password you'll need to use the below command:

mysql -u YOUR_USER_NAME -p

Now that you're logged in, you'll be able to perform below actions from MySQL command line.

How to create a User

You need to consider 3 elements while creating a user:

• username
• password
• hostname - If you only need to access this database locally use the value "localhost"

CREATE USER ‘username’@’hostname’ IDENTIFIED BY ‘password’;

If you need a user to only have access to your database from a certain I, use the following:

CREATE USER ‘username’@’10.0.0.3’ IDENTIFIED BY ‘password’;

Where 10.0.0.3 is the IP the user will be able to access the database.

How to show all MySQL users

To list all users of your MySQL instance, use the following statement:

SELECT * FROM mysql.user;

Understanding MySQL Privileges

MySQL privileges allow you to define the rights given to a user to perform certain actions on your MySQL instance. You'll see in the screenshot below that these rights can be scoped down with granularity on some specific actions and resources.

You can define MySQL privileges on 2 levels:

• Global Privileges: Administrative privileges and privileges applied to all databases
• Database Privileges: Privileges applied to specific databases

MySQL offers a large granularity of privileges some of which are: ALL PRIVILEGES, INSERT, SELECT, UPDATE, DELETE, CREATE, ALTER.

The most common permissions are:

• CREATE – Allows users to create databases/tables
• SELECT – Allows users to read data
• INSERT – Allows users to insert data
• UPDATE – Allows users to update existing data
• DELETE – Allows users to delete data
• DROP – Allows users to drop databases/tables

How to grant ALL privileges to a MySQL User

In some cases you might want to provide full access to a MySQL User, which can be done as follow:

GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’hostname’;

Make sure to replace database_name with your database name, username with your user name and hostname with your hostname or IP address (as described above).

Note that in most cases, and for obvious security reasons, you'll need to grant only specific privileges to a given user on a specific database.

How to grant specific privileges to a MySQL User

You can grant one or multiple privileges to a user using the below statement:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, on *.* TO 'username'@'localhost'

Show privileges granted to a MySQL User

You can list all permissions given to a user running the SHOW GRANTS statement:

SHOW GRANTS FOR “username”@”localhost” ;

This will output something like:

+-----------------------------------------------------------------------------+
| Grants for username@localhost                                         |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'localhost'                          |
| GRANT ALL PRIVILEGES ON 'database'.* TO 'username'@'localhost'.|
| REVOKE ALL PRIVILEGES ON * . * FROM 'username'@'localhost';  |
+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Remove privileges to a MySQL User

You can remove privileges to a MySQL User using the below command:

REVOKE permission_1, permission_2 ON database_name.table_name FROM 'username'@'localhost';

Replace permission_1, permission_2 with the permissions you want to revoke (like INSERT, SELECT).
You can remove all privileges with one simple statement, like below:

REVOKE ALL PRIVILEGES ON *.* FROM 'usernale'@'localhost';