How to assign Roles to MySQL Users

Grant a Role to a User

Write a MySQL query to assign the role "db_reader" to the user "alice".

Solution:

-- This command assigns the 'db_reader' role to the user 'alice'
-- The role is granted for connections from localhost
GRANT 'db_reader' TO 'alice'@'localhost'; 

Explanation:

• Purpose of the Query:
• The goal is to assign a pre-defined role to a user, enabling them to inherit its privileges.
• This demonstrates the GRANT statement for role assignment.
• Key Components:
• GRANT 'db_reader' : Specifies the role to be granted.
• TO 'alice'@'localhost' : Identifies the user receiving the role.
• Real-World Application:
• Simplifies the management of user permissions by assigning roles instead of individual privileges.

Notes:

• Verify that the role "db_reader" exists before assignment.

For more Practice: Solve these Related Problems:

• Write a MySQL query to grant the role "data_analyst" to the user "john_doe"@'localhost'.
• Write a MySQL query to assign the role "db_admin" to "admin_user"@'%' and verify the assignment takes effect immediately.
• Write a MySQL query to grant the role "app_user" to "app_dev"@'192.168.1.150' ensuring proper host matching.
• Write a MySQL query to assign the role "reporting_role" to both "user1"@'localhost' and "user2"@'localhost' in one command.

Go to:

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

PREV : Create a Role.
NEXT : Revoke a Role from a User.

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