Secure Read Access Setup for carol on ReportsDB

Grant Read-Only Privileges

Write a MySQL query to grant read-only access (SELECT privilege) on the "ReportsDB" database to the user "carol".

Solution:

-- This command gives the user 'carol' read-only access to the ReportsDB database
-- Grants only the SELECT privilege on all tables in ReportsDB for localhost connections
GRANT SELECT ON ReportsDB.* TO 'carol'@'localhost';

Explanation:

• Purpose of the Query:
• The goal is to allow the user "carol" to view data in the ReportsDB database without making changes.
• This demonstrates the assignment of limited, read-only privileges.
• Key Components:
• GRANT SELECT : Specifies the read-only privilege.
• ON ReportsDB.* : Applies the privilege to all tables in the ReportsDB database.
• TO 'carol'@'localhost' : Identifies the user receiving these privileges.
• Real-World Application:
• Useful for users involved in data analysis or reporting who should not modify data.

Notes:

• Limiting privileges helps maintain data integrity and security.

For more Practice: Solve these Related Problems:

• Write a MySQL query to grant SELECT privileges on the "ReportsDB" database to the user "carol" for tables beginning with "rep_".
• Write a MySQL query to grant read-only access on "ReportsDB" to "carol" but restrict connections to 'localhost' only.
• Write a MySQL query to grant SELECT privileges on "ReportsDB" to "carol" and ensure the privileges are active only during business hours.
• Write a MySQL query to grant SELECT privileges on "ReportsDB" to "carol" and then verify the assignment using a SHOW GRANTS query.

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

Previous MySQL Exercise: Grant Privileges on a Specific Database.
Next MySQL Exercise: Grant All Privileges with GRANT OPTION.