w3resource

Audit MySQL user Privilege Assignments


Show user Privileges

Write a MySQL query to display the privileges assigned to the user "alice".

Solution:

-- This command displays all privileges and roles assigned to the user 'alice'
-- Shows grants specifically for connections from localhost
SHOW GRANTS FOR 'alice'@'localhost'; 

Explanation:

  • Purpose of the Query:
    • The goal is to list all privileges and roles assigned to the user "alice".
    • This demonstrates the use of the SHOW GRANTS statement for auditing purposes.
  • Key Components:
    • SHOW GRANTS FOR 'alice'@'localhost' : Specifies the user whose privileges are being displayed.
  • Real-World Application:
    • Useful for security audits and troubleshooting user access issues.

Notes:

  • The output can be used to recreate the user's privileges if needed.

For more Practice: Solve these Related Problems:

  • Write a MySQL query to display all grants for "alice"@'localhost'.
  • Write a MySQL query to show the privileges for "bob"@'%' including both direct grants and roles.
  • Write a MySQL query to list the grants for "charlie"@'192.168.1.200' and format the output for clarity.
  • Write a MySQL query to display all privileges and role assignments for "david"@'remote.host.com'.

Go to:


PREV : Change a User's Password.
NEXT : Flush Privileges.

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.