MySQL add user

Introduction to MySQL add user

The default user that is present in MySQL after installing it is a root user. Besides that in real-time many users need to be created so that access privileges can be assigned accordingly to maintain the security of the database. Here we will see how we can create new users in MySQL. The creation of a new user in MySQL involves using the CREATE query statement to create a new user.

Syntax of MySQL add user

Given below is the following syntax of CREATE USER statement to create a new user in MySQL:

CREATE USER [IF NOT EXISTS] name_of_account
IDENTIFIED BY 'password_to_be_used';

name of the account is the account name that you wish to give to the new user. The account name consists of two things the name of the user and the hostname. Username and hostname are separated with @ symbol while specifying them togetherly as account name while creating the new user in user_name@host_name format. Hostname helps us specify from which host the user will be accessing and connecting to the MySQL database.

The hostname is optional and when not specified the account name is equivalent to user_name@% that indicated that the user can access the database from any host.

When we want to add space or dash(-) or any other character in the user name of hostname then we need to quote the username and hostname in either single quotes, double quotes or backticks as shown below:

`user_name`@`host_name`

The password_to_be_used field helps us specify the password of the newly created user that will be used at the time of login to the MySQL database by that user. When a new user is created, it is not assigned with any of the privileges. We need to use the GRANT statement to assign privileges to users to access and manipulate the database and its contents.

The use of IF EXISTS statement prevents from creating the user if it already exists in the database with the same user name and is considered as a good practice to use it in the query.

Example of MySQL add user

Given below is the example mentioned:

Firstly, we will log in to our database using the root user that is created by default at the time of installation of MySQL and has nearly all the privileges including the privilege to create new users and grant them the necessary privileges.

We can log in using the root user with the help of the following statement.

Code:

sudo mysql -u root -p

That results in the following output once the password of sudo and set against root is entered. Using sudo is optional. It depends upon the permissions assigned. Most of the time, you can log in using just mysql -u root -p statement.

Output:

To retrieve the list of all the users and their names that exist on your MySQL database, we can make the use of the following query statement.

Code:

select user from mysql.user;

Output:

Let us create a new user named grantdemo using the following create user statement which we can use to grant privileges to.

Code:

CREATE USER 'grantdemo'@'localhost' IDENTIFIED BY '123';

Output:

Let us check all the granted privileges for this user using the following query statement.

Code:

SHOW GRANTS FOR grantdemo@localhost;

Output:

We can see from the output that the newly created user does not have any grants assigned to it. It can just log in to the database but not access the contents and can neither modify them. Let us grant the select privilege on all the educba_writers table of the educba database by using the GRANT statement.

Code:

GRANT SELECT
ON educba.educba_writers
TO grantdemo@localhost;

Output:

We can even assign multiple privileges to the grantdemo user using the GRANT statement. For example, let us assign UPDATE, DELETE and INSERT privileges to the grantdemo user on the table educba_writers located in the educba database. For this, we will make the use of the following query statement where the privileges to be granted are mentioned in comm-separated format.

Code:

GRANT UPDATE, DELETE, INSERT
ON educba.educba_writers
TO grantdemo@localhost;

Output:

Now, suppose we try to create the same user with name grantdemo again using the CREATE USER query statement as shown below.

Code:

CREATE USER 'grantdemo'@'localhost' IDENTIFIED BY '123';

Output along with the error after the execution:

Now, instead of just CREATE USER statement, we would have used IF NOT EXISTS clause in it then the error must have been prevented. Instead, it just shows the output saying query executed successfully and issues a warning.

Code:

CREATE USER IF NOT EXISTS 'grantdemo'@'localhost' IDENTIFIED BY '123';

Output:

To see the warning that has been arisen while executing the statement we can make the use of the following query statement.

Code:

SHOW WARNINGS;

That gives following output after executing saying that the user with grantdemo name and the localhost hostname is already present in the current MySQL database.

Output:

Conclusion

The root is the default user created in MySQL at the time of the installation of MySQL. However, we can create multiple new users by using the CREATE USER query statement in MySQL. This method provides flexibility to specify user-related properties and other details required while user creation in MySQL. However, when a new user is created in MySQL, it does not have any privileges assigned to it. Hence, it can’t perform any operations on the database or its contents and can neither access the contents. For this, we need to grant privileges to the user.

This is a guide to MySQL add user. Here we discuss the introduction to MySQL add user along with query example. You may also have a look at the following articles to learn more –

1. MySQL REGEXP_REPLACE()
2. MySQL Root Password
3. MySQL WHILE LOOP
4. MySQL INSTR()