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.
