Question:Is there a query to run in MariaDB that will show all grants for a User?
Answer: In MariaDB, you can use the SHOW GRANTS command to display all grant information for a user. This would display privileges that were assigned to the user using the GRANT command.
The syntax for the SHOW GRANTS command in MariaDB is:
SHOW GRANTS [ FOR username ]
Parameters or Arguments
The name of the database account for which to display the grant information.
- To view the privileges of a user (that is not the CURRENT_USER), you must have SELECT privilege in the MariaDB database.
Let's look at an example of how to use the SHOW GRANTS command in MariaDB to display grant information for a user.
SHOW GRANTS FOR 'fastread.aitechtonic';
This example would display all grant information for the user called 'fastread.aitechtonic. Each row that is returned by the SHOW GRANTS command is the GRANT statement that can be used to recreate the privileges. This is a great way to capture privileges that you may want to save for later.
In this first example, when you don't specify a host for the username, MariaDB assumes '%' as the host. So the example above would be equivalent to the following SHOW GRANTS command.
SHOW GRANTS FOR 'fastread.aitechtonic'@'%';
Now let's look at an example of how to use the SHOW GRANTS command when we want to specify the host.
SHOW GRANTS FOR 'fastread.aitechtonic'@'localhost';
This SHOW GRANTS example would return the grant information for the user called 'fastread.aitechtonic' on the host called 'localhost'.