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'.

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

Previous MySQL Exercise: Change a User's Password.
Next MySQL Exercise: Flush Privileges.