- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to create MySQL user with limited privileges?
To create MySQL user with limited privileges, following is the syntax −
CREATE USER 'yourUserName'@'yourHostName' IDENTIFIED BY 'yourPassword';
Following is the syntax to set limited privileges for user −
GRANT SELECT, INSERT, UPDATE, etc. REFERENCES ON yourDatabaseName.* TO 'yourUserName'@'yourHostName';
Let us implement the above syntaxes in order to create a MySQL user with limited privileges −
mysql> CREATE USER 'David'@'localhost' IDENTIFIED BY 'david'; Query OK, 0 rows affected (0.20 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, REFERENCES ON test.* TO 'David'@'localhost'; Query OK, 0 rows affected (0.21 sec)
Let us check the user has been created with name ‘David’ or not.
mysql> select user, host from MySQL.user;
This will produce the following output −
+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | User2 | % | | mysql.infoschema | % | | mysql.session | % | | mysql.sys | % | | root | % | | @UserName@ | localhost | | Adam Smith | localhost | | David | localhost | | James | localhost | | John | localhost | | John Doe | localhost | | User1 | localhost | | am | localhost | | hbstudent | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | +------------------+-----------+ 17 rows in set (0.00 sec)
- How can we grant privileges to a MySQL user?
- How can we revoke privileges from a MySQL user?
- How can I restore the MySQL root user full privileges?
- Grant all privileges of a database to a MySQL user?
- MySQL new user access denied even after giving privileges?
- Check privileges (grants) for a specific user in MySQL?
- MySQL CREATE USER with a variable?
- How to check privileges in MySQL?
- What are the minimum MySQL user privileges to allow optimize and repair?
- How to add super privileges to MySQL database?
- Create a new user with password in MySQL 8?
- MySQL query to create user and grant permission
- Check user rights before attempting to CREATE MySQL DATABASE?
- How can we create user accounts in MySQL database server?
- How to create MongoDB user on existing database with correct role?