w3resource

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:


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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.