Creating and removing users on MySQL

Creating users

The CREATE USER command is important in MySQL for creating new user accounts that can access the database. The database administrators use this command to define which users are authorized to connect to the MySQL server and specify their login credentials.

The user account details in MySQL contain two details: the username and the host from which the user is trying to connect. The format will be username@host-name

If the admin user connects from the localhost then the user account will be admin@localhost

To allow a user to connect from any host, the % wildcard is used like username@’%’

MySQL stores the user account in the user grant table of the MySQL database.

SYNTAX

CREATE USER user_account IDENTIFIED BY password;

Here,

user_account is the name that the user wants to give to the account. It should be in the format username@host-name

password is the credentials assigned to the account. It is specified in the IDENTIFIED BY clause.

EXAMPLE:

CREATE USER ‘student’@’localhost’ IDENTIFIED BY ‘DataAnalysisRules’;

NOTE:

This statement only creates a new user. It does not grant any permissions to the user.

To show the permissions of the user account, we can use SHOW GRANTS statement.

Removing users

The DROP USER statement is used to delete an existing account from the MySQL server. When a user is dropped, all the rights assigned to the user are revoked and the user can no longer log into the MySQL server.

SYNTAX

DROP USER ‘username’@’host’;

Here,

user_account is the name of the user who needs to be deleted

host is the hostname or IP address from which the user is allowed to connect. Use ‘%’ wildcard to indicate any host

EXAMPLE:

DROP USER ‘student’@’localhost’;