How to Create a New MySQL User and Grant Access Rights Through the Command Line

MySQL is a popular and widely used relational database management system for various web applications. One of the important tasks that database administrators have to perform is to create new MySQL users and grant them the appropriate access rights.

Access rights or privileges are rules that define what a user can do with a database, table, column, or other object. By granting proper access rights, you can improve the security and performance of your database.

In this article, Bardimin will discuss how to create a new MySQL user and grant access rights via the command line. Bardimin will also discuss some MySQL commands that are useful for managing users and access rights.

Other Interesting Articles

Step 1: Login to MySQL as Root

Before you can create a new MySQL user or grant permissions, you must be logged in to MySQL as the root user or another user who has GRANT access rights. The root user is the user who has full access rights to all MySQL databases and objects. You can log in to MySQL as root by using the following command:

mysql -u root -p

This command will prompt you to enter the root password you created when installing MySQL. Once you’ve entered the correct password, you’ll see a MySQL prompt like this:

mysql>

Step 2: Create a New MySQL User

After you log in to MySQL as root, you can create a new MySQL user by using the CREATE USER command. The general format of this command is as follows:

CREATE USER 'username''host' IDENTIFIED BY 'password';

Here, you have to replace the username, host, and password with the corresponding values. username is the name of the user you want to create. host is the hostname or IP address from which a user can connect to MySQL.

You can use % to indicate that users can connect from anywhere, or you can restrict users to connect from specific hosts only. password is the password you want to set for the user.

For example, if you want to create a user named user1 who can connect from anywhere with a pass1 password, you can use the following command:

CREATE USER 'user1''%' IDENTIFIED BY 'pass1';

If the command is successful, you’ll see a message like this:

Query OK, 0 rows affected (0.01 sec)

You can check if the user has been created correctly by using the SELECT command in the mysql.user table:

SELECT User, Host FROM mysql.user;

This command will display all existing MySQL users and their hosts. You should see results like this:

+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| user1 | % |
+------------------+-----------+
4 rows in set (0.00 sec)

Here, you can see that user1 user has been created with the % host.

Step 3: Grant Access Rights to New MySQL Users

After you create a new MySQL user, you must grant the appropriate permissions to that user. You can grant permissions by using the GRANT command. The general format of this command is as follows:

GRANT privileges ON database.table TO ‘username”host’;

Here, you should replace the privileges, database, table, username, and host with the corresponding values. privileges is the access right you want to grant to a user. You can grant specific access rights such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, EXECUTE, GRANT OPTION, and more.

You can also grant global access rights such as ALL PRIVILEGES or ALL that includes all existing access rights. database is the name of the database to which you want to grant access rights. You can use * to show all databases. table is the name of the table to which you want to grant access rights. You can also use * to show all tables. username and host are usernames and hosts that you have created before.

For example, if you want to grant SELECT, INSERT, UPDATE, and DELETE access rights to user1 users on all tables in db1 database, you can use the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'user1''%';

If the command is successful, you’ll see a message like this:

Query OK, 0 rows affected (0.01 sec)

You can check if permissions have been granted correctly by using the command SHOW GRANTS:

SHOW GRANTS FOR 'user1''%';

This command will display all the access rights owned by the user1 user. You should see results like this:

+--------------------------------------------------------------+
| Grants for user1% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user1``%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO `user1``%`|
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

Here, you can see that user1 user has access rights SELECT, INSERT, UPDATE, and DELETE on all tables in db1 database.

Latest Articles

Related Articles