In Ubuntu systems running MySQL 5.7 and later versions, the default authentication method for the MySQL root user is auth_socket. Unlike a password-based authentication method, auth_socket requires that the operating system user’s name matches the MySQL user’s name specified in the command.
So, to gain access to the root MySQL user, you need to invoke the mysql command with sudo privileges.
sudo mysql
If you’ve enabled password authentication for root, you can access the MySQL shell with the following command:
mysql -u root -p
1. Creating User in MySQL:
Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement.
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
CREATE USER statement specifies username, hostname (use localhost if connecting from the same server), authentication plugin (optional), and password (optional).
Create MySQL user using auth_socket plugin:
CREATE MySQL USER 'username'@'localhost' IDENTIFIED WITH auth_socket;
The IDENTIFIED WITH auth_socket clause specifies that the new user will authenticate using the auth_socket authentication plugin. This means the user will be authenticated based on the operating system user account they are logged in as rather than a password.
Create MySQL user using the default caching_sha2_password plugin:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
To create a user with the caching_sha2_password authentication plugin, use the CREATE USER command with your desired username and a strong password.
Note: Consider using mysql_native_password instead of caching_sha2_password if you experience issues with PHP.
Create MySQL user using the mysql_native_password plugin:
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Updating the Authentication Plugin for an Existing MySQL User.
If you are unsure which authentication plugin to use, you can create a user with the caching_sha2_plugin and later modify it using the ALTER command.
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
2. Granting User Privileges in MySQL
Once we have created a user account, we can grant privileges to that user using the GRANT statement. The following command grants the user ‘username’ all privileges on the database ‘exampledb’:
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
If we only want to grant certain privileges, we can replace ALL PRIVILEGES with a comma-separated list of the desired privileges.
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on dbname.* TO 'username'@'localhost';
3. Revoking privileges from a user
If we need to revoke privileges from a user, we can use the REVOKE statement. The following command revokes all privileges from the user ‘username’:
REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';
If we only want to revoke certain privileges, we can replace ALL PRIVILEGES with a comma-separated list of the desired privileges.
REVOKE SELECT, INSERT ON dbname.* FROM 'username'@'localhost';
4. Viewing user privileges
To view the privileges granted to a user, we can use the SHOW GRANTS statement.
SHOW GRANTS FOR 'username'@'localhost';