When we start using MySQL, we should have a user account and their credentials, such as username and password. These initial credentials will grant us “root” access or full control of all our databases and tables. Sometimes, we want to give the database access to someone else without granting them full control or just want to grant full access to one database, the “GRANT ALL PRIVILEGES ON” statement can be used.
This guide provides the method to make a new user in MySQL and grants it full access to a single database.
How to Create New User in MySQL and Grant it Full Access to Single Database?
Follow the provided steps to make a new user in MySQL and grants it full access to a single database:
- Access the MySQL server.
- List and change the database.
- Run the “CREATE USER ‘<databasename>’@'<hostname>’ IDENTIFIED BY ‘<password>’;” command to create a new user.
- To give full access to one database to the new user, use the “GRANT ALL PRIVILEGES ON <database-name>.* TO ‘<username>’@'<hostname>’;” command.
- Execute the “SHOW GRANTS FOR ‘<username>’@'<hostname>’;” command for verification.
Step 1: Connect Terminal With MySQL Server
Initially, access the MySQL server by executing the following command along with the username and default password:
Step 2: Display Databases
Now, run the below-stated command to list all the databases:
From the displayed list, we have selected the “testdb” database for further procedure:
Step 3: Create New User
To create a new user for a previously selected database, execute the “CREATE USER” command:
Here:
- “CREATE USER” statement is used for creating a new MySQL account.
- “testdb1” is our new username.
- “localhost” is our hostname.
- “IDENTIFIED BY” keyword is used for setting the password for the new user.
- “testdb1234” is our new user account password.
From the given output, the “Query OK” represents that the query has been executed successfully:
Step 4: View User List
Use the following command to ensure that the new user created or not:
According to the below-given output, a new user “testdb1” exists in the list:
Step 5: Grant Full Access to One Database
Finally, give full access to one database in MySQL, execute the “GRANT ALL PRIVILEGES” query with the database name, username, and hostname:
Here:
- “GRANT ALL PRIVILEGES ON” statement is used for privileges to a user which enables that user full control over a particular database.
- “testdb” is our database name.
- “.*” indicates full access.
- “testdb1” is our new username.
- “localhost” is our hostname.
The following output shows, the specified query has been executed successfully:
Step 6: Verify Grant Access
Lastly, execute the “SHOW” command to verify if access to one database is fully granted to the new user or not:
It can be observed that full access to one database has been granted to the new user:
That’s all! We have compiled the easiest way of creating a new user in MySQL and giving it full access to a single database.
Conclusion
To create a new user in MySQL, execute the “CREATE USER ‘<databasename>’@'<hostname>’ IDENTIFIED BY ‘<password>’;” command. To give full access to one database to the new user, execute the “GRANT ALL PRIVILEGES ON <database-name>.* TO ‘<username>’@'<hostname>’;” command. For verification, run the “SHOW GRANTS FOR ‘<username>’@'<hostname>’;” command. This guide provided the method of creating a new user in MySQL and giving it full access to a single database.
