In this tutorial, we will guide you through the process of creating a new user and granting permissions in MySQL, a popular open-source relational database management system. This is a fundamental task for database administrators and developers who need to control access and manage user privileges within MySQL.
Prerequisites:
- A server or computer with MySQL installed and running.
- Administrative access to MySQL (usually via the root user).
Step 1: Access MySQL
First, you must log into your MySQL server with administrative privileges by using this command in your terminal or command prompt.
mysql -u root -p
When you use the above command, it will prompt you to input the root password. After you input your password and press Enter, the system will authenticate you, granting access to the MySQL command line interface.
Now we go to the next step where we create the new user.
Step 2: Create a New User
To create a new user, you’ll use the CREATE USER command. Replace new_user with the desired username and password with the user’s password:
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
This command creates a user named new_user who can connect to the MySQL server from the localhost (the same machine where MySQL is running). If you want the user to connect from any host, replace 'localhost' with '%', however, it’s recommended to limit access to specific hosts for security.
Step 3: Grant Permissions
After creating the user, you need to grant specific permissions to define what actions the user can perform. Use the GRANT statement to do this. Replace database_name with the database name to which you want to grant access and specify the desired privileges.
For granting permission to a particular database use the below command:
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';
If you want to grant specific privileges like SELECT, INSERT, UPDATE, DELETE, or others, replace ALL PRIVILEGES with the specific privileges you want to grant.
To apply the changes immediately, run:
FLUSH PRIVILEGES;
Step 4: Verify User and Permissions
To ensure that the new user has been created and granted the specified permissions, you can use the SHOW GRANTS command:
SHOW GRANTS FOR 'new_user'@'localhost';
This command will display the privileges assigned to the user.
Step 5: Exit MySQL
After you finished creating and granting permission for the new user you can exit the MySQL command line:
EXIT;
This will return you to your regular command prompt or terminal.
Congratulations! You have successfully created a new user and granted permissions in MySQL. The new user can now connect to the database and perform the specified actions based on the granted privileges.