MySQL Security and User Management Exercises with Solutions
MySQL Security and user Management [20 exercises with solution]
1. Create a New User
Write a MYSQL query to create a new user "gaioz_amira" with password "SecurePass123".
2. Grant Privileges to a User
Write a MySQL query to grant SELECT and INSERT privileges on the SalesDB database to the user "gaioz_amira".
3. Revoke Privileges from a User
Write a MySQL query to revoke the INSERT privilege from the user "gaioz_amira" on the SalesDB database.
4. Delete a User
Write a MySQL query to remove the user "gaioz_amira" from the MySQL server.
5. Create a Role
Write a MySQL query to create a new role named "db_reader".
6. Grant a Role to a User
Write a MySQL query to assign the role "db_reader" to the user "alice".
7. Revoke a Role from a User
Write a MySQL query to revoke the role "db_reader" from the user "alice".
8. Change a User's Password
Write a MySQL query to update the password of the user "alice" to "NewPass456".
9. Show user Privileges
Write a MySQL query to display the privileges assigned to the user "alice".
10. Flush Privileges
Write a MySQL query to reload the grant tables and apply any changes made to user privileges.
11. Grant Privileges on a Specific Database
Write a MySQL query to grant all privileges on the "InventoryDB" database to the user "bob".
12. Grant Read-Only Privileges
Write a MySQL query to grant read-only access (SELECT privilege) on the "ReportsDB" database to the user "carol".
13. Grant All Privileges with GRANT OPTION
Write a MySQL query to grant all privileges on the "FinanceDB" database to the user "dave" along with the ability to grant privileges to others.
14. Create a User with Host Restriction
Write a MySQL query to create a new user "emma" that can only connect from the IP address "192.168.1.100".
15. Restrict User Connection with SSL Requirement
Write a MySQL query to create a new user "frank" that is required to use SSL for connections.
16. Rename a User
Write a MySQL query to rename the user "frank" to "franklin".
17. Create a User with Limited Privileges on a Specific Table
Write a MySQL query to create a user "george" and grant him SELECT privileges on the "Customers" table in the SalesDB database.
18. Revoke All Privileges from a User
Write a MySQL query to revoke all privileges from the user "george".
19. Show All Users from the MySQL System Database
Write a MySQL query to list all users from the MySQL system database.
20. Grant Execution Privilege on Stored Procedures
Write a MySQL query to grant the EXECUTE privilege on all stored procedures in the "HRDB" database to the user "helen".
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics