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.
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.
Step 4: Change or Revoke the Access Rights of a MySQL User
If you want to change or revoke the permissions of a MySQL user, you can use the REVOKE command. The general format of this command is as follows:
REVOKE privileges ON database.table FROM 'username''host';
Here, you should replace the privileges, database, table, username, and host with the corresponding values. privileges is the access right that you want to change or revoke from the user. You can use specific or global access rights as described earlier. database, table, username, and host are the names of databases, tables, users, and hosts that you have previously granted access rights to.
For example, if you want to revoke DELETE permissions from a user1 user on all tables in db1 database, you can use the following command:
REVOKE DELETE ON db1.* FROM '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 the access rights have been revoked correctly by using the SHOW GRANTS command:
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 ON `db1`.* TO `user1``%` | +--------------------------------------------------------------+ 2 rows in set (0.00 sec)
Here, you can see that DELETE access rights have been revoked from user1 users on all tables in db1 database.
Step 5: Delete MySQL Users
If you want to delete a MySQL user, you can use the DROP USER command. The general format of this command is as follows:
DROP USER 'username''host';
Here, you’ll need to replace the username and host with the username and host you want to remove. This command deletes the user and all permissions it has.
For example, if you want to delete a user1 user, you can use the following command:
DROP USER '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 a user has been deleted correctly by using the SELECT command in the table mysql.user:
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 | +------------------+-----------+ 3 rows in set (0.00 sec)
Here, you can see that user1 user has been removed from the MySQL user list.
Conclusion
MySQL has several useful commands for managing users and access rights. Using these commands, you can organize your MySQL database better and more securely.
Hope you find this article useful. Thanks for reading.