MySQL Create User, Drop and Show User

Previous Page
Next Page

MySQL users 

An account that uses MySQL database, MySQL considers as a MySQL database user. MySQL defines a user as a username and client host or hosts that the user can use to connect to the server.

MySQL uses the “user” table of the MySQL system database. You need MySQL Administrator or root-level access to be able to query the user table. The user table consists of the following information.

mysql.user table

The account also has password information. Depending on the Operating system, MySQL has restrictions on the username and passwords. They are as follows

  • MySQL has a specific username and password that is different from the Windows username and password. However, in Unix, we can use the same username and password combination for both the system and the MySQL login.
  • Every user has to have a password associated with it.
  • MySQL can have a maximum of 32 characters in them.
  • MySQL authenticates passwords from the information stored in the user table.
  • The username and password can contain only ASCII characters.

Create users in MySQL

To create a user, we use the “Create USER” statement.

The basic syntax of creating a user is as follows:

CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'password';
  • The account_name has two parts. The first is the username, followed by the ‘@’ sign and then the hostname.
  • The hostname is optional. If we do not specify the hostname, the hostname, a ‘%’ sign, is attached to it by default. The % sign means that the user can access the server through any of the hostnames.
  • If the hostname or the username has any special characters like ‘- ‘, we need to enclose the username in single quotation marks.

The steps to create a user are as follows:

Login to MySQL server as a root user. Trigger the Query :

select user from mysql.user;

This lists all the users that are already present. Next, write the query to create a user. First, we will create a user without a hostname

create user sysadmin_user1 identified by 'password1';

We can select again and see that the user is created.

create user

Next, we will create a user with a hostname. The Query to do so is:

create user test_user@localhost identified by 'password2';

Drop users in MySQL

To drop a user, we need to log in with the root login. To remove an account from MySQL, we use the ‘DROP User’ statement.

The basic syntax is:

DROP USER account_name;

We can also drop multiple users by separating the names by a comma(,)

Dropping a user that does not exist, causes MySQL to throw an error. To circumvent that, we have the syntax:

DROP USER [IF EXISTS] account_name;

While dropping users, we need to specify the hostname if a hostname is attached to the user.

To drop a user in MySQL, the steps are as follows:

Login to MySQL client as root user credentials. Trigger the query to select the users,

select user from mysql.user;

To drop a user with a specific hostname

drop user test_user@localhost;

If we try to drop the above user without the hostname, MySQL will throw an error, as shown in the screenshot below.

To drop a user without a specific hostname, i.e., % attached to it, we can drop the user as

drop user sysadmin_user1;

If we try to drop a user that does not exist and do not use the “If exists” clause, MySQL throws an error, as shown in the screenshot below.

If we use the “If exists” clause, MySQL does not drop any users but suppresses the error.

Retrieve host name in MySQL

To know what are all the hostnames are attached to the user, we can use the below query.

select user, host from mysql.user;

Previous Page
Next Page