How to Create MySQL Users Accounts and Grant Privileges

in MySQL, Ubuntu Server

Connect To MySQL Database

To access the MySQL shell type the following command

mysql -u USERNAME -p

Create MySQL Users Accounts and Grant Privileges

MySQL 8 provides two different ways to create accounts:

Using account management statements : These statements are used to create users and set their privileges.

Using manipulation of grant tables : Using INSERT, UPDATE, and DELETE statements, we can manipulate the grant table.

Out of these two approaches, account management statements are preferable, because they are more concise and less error-prone.

Ex1 : creates ‘user1’ and assigns full privileges to ‘user1’. But ‘user1’@’localhost’ indicates that ‘user1’ is allowed to connect with localhost only.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user1_password';
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' WITH GRANT OPTION;

Ex2 : creates ‘user2’ and assigns full privileges to ‘user2’. But ‘user2’@’%’ is mentioned, which indicates that ‘user2’ is allowed to connect with any host.

CREATE USER 'user2'@'%' IDENTIFIED BY 'user2_password';
GRANT ALL PRIVILEGES ON *.* TO 'user2'@'%' WITH GRANT OPTION;

Ex3 : creates ‘adminuser’ and allows it to connect with localhost only.

CREATE USER 'adminuser'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS ON *.* TO 'adminuser'@'localhost';

we can see that only RELOAD and PROCESS privileges are provided to the ‘adminuser’. It allows ‘adminuser’ to execute the mysqladmin reload, mysqladmin refresh, mysqladmin flush-xxx commands, and the mysqladmin processlist command, but it has no access on any database.

Ex4 : creates ‘user4’ and allows the user to access the database using ‘host4’ only. And indicates ‘user4’ has grant on ‘db1’ for all the mention operations.

CREATE USER 'user4'@'host4.mycompany.com' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db1.* TO 'user4'@'host4.mycompany.com';

Remove a MySQL User Account

To remove a user account, execute the DROP USER command as follows:

DROP USER 'user1'@'localhost';
This command will drop the 'user1' account from the system.

Add Comment