How To Create a New User and Grant Permissions in MySQL

John D K -

1. Overview

In this tutorial, we'll show how to create a new user and grant permissions to it in MySQL.

The article is tested on both MySQL 5 and MySQL 8.

2. Create a New User in MySQL

To create a new user in MySQL with SQL you need to be logged into the MySQL shell. To log into MySQL with existing user and password use:

mysql -u [username] -p

if you are logging for first time on Ubuntu:

sudo mysql -u root -p

and use your root password

2.1 Create new user

The command for creation of user in MySQL expects 3 parameters:

  • 'some_user' - the user name
  • 'localhost' - database hostname. Most often it will be the localhost - which means the local machine. In some cases the IP of the server of MySQL can be used
  • 'some_pass' - the password
CREATE USER 'some_user'@'localhost' IDENTIFIED BY 'some_pass';

Next step is to grant permissions to this user in order to log, view, update or delete data.

2.2 Drop user

Users in MySQL can be deleted by DROP command. You need to provide the user name and the host:

DROP USER 'some_user'@'localhost';

3. Grant User Permissions in MySQL

New users need permissions before to do anything in MySQL. If we like to give all permissions for every table in the DB - we can use:

GRANT ALL PRIVILEGES ON * . * TO 'some_user'@'localhost';

Where:

  • ALL PRIVILEGES - grant all possible privileges
  • * . * - means all databases and all tables
  • 'some_user'@'localhost' - the user and the host

After that we need to activate permissions by:

FLUSH PRIVILEGES;

4. Grant User Permissions per table and privilege

If we like to give only few privileges to set of tables/databases we can use:

GRANT SELECT ON db_name.table_name TO 'some_user'@'localhost';

The list of all privileges can be found below:

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

All of them correspond to basic SQL or DB operations

5. Revoke User Permissions

To revoke permissions from users in MySQL we can use - REVOKE command. Again we need to provide the user and the host, the privilege, table and database:

REVOKE SELECT ON db_name.table_name FROM 'some_user'@'localhost';

6. Show User Permissions

Finally to check permissions for a given user we can use - SHOW GRANTS and specify the user and the host:

SHOW GRANTS FOR 'some_user'@'localhost';

7. Conclusion

In this post we saw how to create a new user and grant to it privileges. We covered different privileges, how to grant them per user or table.

Finally we saw how to drop user or revoke privileges in MySQL.