How To Create A User And Grant Permissions In MySQL

Managing users is an integral part of database administration. It involves adding new users, giving them access, and, if necessary, deleting them. In this tutorial, we’ll examine MySQL’s user management procedure, which makes it the most widely used relational database management system available as open-source software.

Table of Contents

Creating a New User in MySQL

To create a new user in MySQL, you need to use the ‘CREATE USER’ statement. Here’s the basic syntax:

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

In this command, “username” refers to the name of the new user you’re creating, “host” designates the host from which the user can access the MySQL server, and “password” refers to the user’s password.

For instance, to create a user named ‘virtono’ who can connect from any host, with the password ‘virtonopassword’, the command would be:

CREATE USER 'virtono'@'%' IDENTIFIED BY 'virtonopassword';

Granting Permissions to a User in MySQL

Now, let’s create a new database. Use the following command:

CREATE DATABASE mydatabase;

Replace “mydatabase” with the name you desire for your database. Remember to end the command with a semicolon (;); MySQL uses it to identify the end of the command.

To ensure that your database has been created, use the following command:

SHOW DATABASES;

Once a user has been created, you must give them access to certain functions. This can be done using the ‘GRANT’ statement. Here is the fundamental syntax:

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

In this command, ‘privilege_type’ is the type of permission (like SELECT, INSERT, UPDATE, DELETE, ALL, etc.), ‘database_name’ is the name of the database, and ‘table_name’ is the name of the table.

For instance, the command would be as follows to grant the user “virtono” full access to the database “mydatabase”:

GRANT ALL ON mydatabase.* TO 'virtono'@'%';

To apply the changes, use the ‘FLUSH PRIVILEGES’ command:

FLUSH PRIVILEGES;

Revoking Permissions from a User in MySQL

There may be times when you need to remove all of a user’s permissions. For this, the ‘REVOKE’ statement will be used. The basic syntax is as follows:

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

For instance, the command would be as follows to remove all privileges from the user “virtono” on the database “mydatabase”:

REVOKE ALL ON mydatabase.* FROM 'virtono'@'%';

Again, use the ‘FLUSH PRIVILEGES’ command to apply the changes:

FLUSH PRIVILEGES;

Removing a User in MySQL

When a user is no longer needed, you can remove them using the ‘DROP USER’ statement. Here’s the basic syntax:

DROP USER 'username'@'host';

For example, to remove the user ‘virtono’ who can connect from any host, the command would be:

DROP USER 'virtono'@'%';

Note: This article makes the assumption that you are familiar with MySQL fundamentals and have access to a MySQL server where you can run these commands. Always be careful when changing a user’s permissions or deleting a user because these changes can have a big impact on your databases.

Final Thoughts

Although user management in MySQL may initially seem difficult, with some practice, you can quickly become an expert in these procedures. Keep in mind that efficient user management is essential to the safety and effectiveness of your database. Give users only the permissions they need to complete their tasks, and periodically review and revoke any unused permissions.