How to create a new user and grant permissions in MySQL

How to create a new user and grant permissions in MySQL

Introduction

MySQL is an open source relational database management system. It is commonly used as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) and, as of this writing, is the most popular open source database in the world.

This guide explains how to create a new MySQL user and grant them the necessary permissions to perform a variety of actions.

Prerequisites

To follow this guide, you will need access to a MySQL database. This guide assumes that the database is installed on a virtual private server running Ubuntu 20.04, although the principles it outlines should apply regardless of how you access your database.

If you don't have access to a MySQL database and want to set it up yourself, you can follow one of our guides on how to install MySQL. Again, regardless of your server's underlying operating system, the procedures for creating a new MySQL user and granting them permissions will usually be the same.

Create a new user

After installation, MySQL creates a user account. root Creates a user that you can use to manage your database. This user has full privileges on the MySQL server, meaning it has complete control over every database, table, user, etc. For this reason, it is best to avoid using this account outside of administrative functions. This step explains how to use the MySQL root user to create a new user account and grant it privileges.

On Ubuntu systems running MySQL 5.7 (and later), the MySQL root user is authenticated by default using the auth_socket plugin, rather than a password. This plugin requires that the operating system user name invoking the MySQL client match the MySQL user name specified in the command. This means that to access the MySQL root user, you must precede the mysql command with sudo to invoke it with the privileges of the Ubuntu root user:

$ sudo mysql

$ mysql -u root -p

mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';

mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

mysql> SHOW GRANTS FOR 'username'@'host';

mysql> DROP USER 'username'@'localhost';

mysql> exit

$ mysql -u sammy -p