Restricted Table Access Setup for George

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.

Solution:

-- This command creates a new user 'george' who can connect from localhost
-- The user is authenticated with the password 'UserPass321'
CREATE USER 'george'@'localhost' IDENTIFIED BY 'UserPass321';

-- This command grants the user 'george' read-only access to the Customers table
-- The SELECT privilege is specific to the Customers table in the SalesDB database
GRANT SELECT ON SalesDB.Customers TO 'george'@'localhost';

Explanation:

• Purpose of the Query:
• The goal is to create a new user with restricted access, allowing only data retrieval on a specific table.
• This demonstrates combining user creation with granular privilege assignment.
• Key Components:
• CREATE USER 'george'@'localhost' : Establishes the new user account.
• GRANT SELECT ON SalesDB.Customers : Provides read-only access on the specified table.
• Real-World Application:
• Ideal for users who require access only for data analysis or reporting on a specific dataset.

Notes:

• Ensure that the table exists and that only the necessary privileges are granted.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a new user "george" and grant SELECT privileges only on the "Customers" table in the "SalesDB" database.
• Write a MySQL query to create a new user "george" with read-only access on the "Orders" table in "SalesDB".
• Write a MySQL query to create a new user "george" and grant SELECT privileges on specific columns of the "Customers" table in "SalesDB".
• Write a MySQL query to create a new user "george" with limited privileges on "SalesDB.Customers" using a view to enforce row-level restrictions.

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

Previous MySQL Exercise: Rename a User.
Next MySQL Exercise: Revoke All Privileges from a User.