How To Create User And Grant Privileges In MySQL

Introduction

In this Article, We will discuss creating a User and granting all MySQL privileges.

MySQL is an open-source relational database management system (RDBMS) widely used to store DATA to organize, and manage data. MySQL is one of the most famous RDBMS databases. It is popular among developers and businesses because of its scalability, security, and ease of use for developing good software. In this article, we will talk about creating users and granting privileges in MySQL step-by-step.

How to Create a User in MySQL?

Before granting privileges, we need to create a user in MySQL. To create a user in MySQL. Follow these steps,

Step 1. Login to MySQL Server

To create a user, we must log in to the MySQL server using the command line client or GUI client like MySQL Workbench. The command line client is the most commonly used client for MySQL. Open the MySQL Command Line Client.

Now you have to Enter a Password to Login into the MySQL server.

Step 2. Create a User

Once you are logged in to the MySQL server, use the following command to create a new user.

CREATE USER 'SachinMishra'@'localhost' IDENTIFIED BY 'sachin';

Replace username and password with your desired username and password. This command creates a user with the name 'username' and a password 'password'. The user will only access the MySQL server from the local host.

Now User has created the password. We can show all users by this command.

select user from mysql.user;

Output

Now User has been successfully created.

Granting Privileges in MySQL

Once the user is created, we need to grant privileges to the user to allow access to specific databases, tables, or functions. To grant privileges in MySQL,

To grant privileges, use the following command.

GRANT ALL PRIVILEGES ON *.* TO 'SachinMishra'@'localhost';

Now grant all privileges to the created user.

The privilege_name can be any of the following,

• ALL PRIVILEGES- Grants all privileges to the user.
• SELECT- Grants permission to select data from the table.
• INSERT- Grants permission to insert data into the table.
• UPDATE- Grants permission to update data in the table.
• DELETE- Grants permission to delete data from the table.

The database_name and table_name specify the database and table where the privileges will be granted. If you want to grant privileges to all databases and tables, use an asterisk * instead during grant permissions.

Step 3. Flush Privileges

After granting privileges, run the following command to apply the changes.

GRANT ALL PRIVILEGES ON *.* TO 'SachinMishra'@'localhost';
FLUSH PRIVILEGES;

Step 4. Verify Privileges

To verify the privileges, use the following command.

SHOW GRANTS FOR 'SachinMishra'@'localhost';

Now User has created with grant all privileges.

Conclusion

Creating users and granting privileges is an essential task for database administrators. In this article, we have seen how to create a user in MySQL and grant privileges to the user step-by-step. Remember to use strong passwords for user accounts and grant only the necessary privileges to ensure the security of your database; It is a core part of MySQL.