Assign Database Privileges for MySQL User

Grant Privileges to a User

Write a MySQL query to grant SELECT and INSERT privileges on the SalesDB database to the user "gaioz_amira".

Solution:

-- This command grants specific privileges to the user 'gaioz_amira' connecting from localhost
-- Grants permission to SELECT (read) and INSERT (add) data in all tables of SalesDB
GRANT SELECT, INSERT ON SalesDB.* TO 'gaioz_amira'@'localhost';

Explanation:

• Purpose of the Query:
• The goal is to provide the user "gaioz_amira" with the ability to read and add data in the SalesDB database.
• This demonstrates the GRANT statement for assigning specific privileges.
• Key Components:
• GRANT SELECT, INSERT : Specifies the privileges to be granted.
• ON SalesDB.* : Applies the privileges to all tables within the SalesDB database.
• TO 'gaioz_amira'@'localhost' : Identifies the user who receives these privileges.
• Real-World Application:
• Used in systems where users need limited access to perform specific operations on a database.

Notes:

• Always grant only the necessary privileges following the principle of least privilege.

For more Practice: Solve these Related Problems:

• Write a MySQL query to grant SELECT and UPDATE privileges on all tables in the "HR" database to "test_user"@'192.168.1.100'.
• Write a MySQL query to grant EXECUTE privileges on all stored procedures in the "ProcDB" database to "script_user"@'localhost'.
• Write a MySQL query to grant INSERT, DELETE, and UPDATE privileges on the "Orders" table in the "Sales" database to "order_manager"@'localhost'.
• Write a MySQL query to grant SELECT privileges on all databases whose names start with "prod_" to "readonly_user"@'%'.

Go to:

• MySQL Security and User Management Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Create a New User.
NEXT : Revoke Privileges from a User.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.