How to implement the statement to view user role permissions in MySQL?

How to implement the statement to view user role permissions in MySQL?

MySQL is a popular relational database management system with a flexible permission management mechanism. In MySQL, users can be assigned different roles and assign specific permissions to each role. This article will introduce how to use MySQL statements to view user role permissions.

In MySQL, you can use the following statement to view user role permission information:

SHOW GRANTS FOR <username>;

Where, <username> is the user name whose permissions are to be viewed. Through this statement, you can view all permissions of a specified user, including permissions granted directly to the user and permissions indirectly granted to the user through roles.

For example, assuming we want to view the role permissions of the user named test_user, we can execute the following query statement:

SHOW GRANTS FOR 'test_user';

After executing the above statement, MySQL will return a or multiple authorization statements, representing the permission information of a given user.

If we want to view the role permissions of all users, we can use the following statement:

SELECT DISTINCT
  `user`.`Host`,
  `user`.`User`,
  `roles`.`Role_Name`,
  `roles`.`Is_Default`,
  `roles`.`Is_Active`,
  `roles`.`Grantor`
FROM
  `mysql`.`user`
LEFT JOIN
  `mysql`.`roles_mapping`
ON
  `user`.`User` = `roles_mapping`.`Role`
LEFT JOIN
  `mysql`.`roles`
ON
  `roles`.`Role_Name` = `roles_mapping`.`Role`
ORDER BY
  `user`.`User`,
  `roles`.`Role_Name`;

The above statement will return a result set containing the role information associated with each user. Each row of data includes host name (Host), user name (User), role name (Role_Name), and whether the role is default (Is_Default), whether the role is active (Is_Active), and the authorizer of the role (Grantor).

Please note that sufficient permissions are required to execute the above statement. Normally, only users with SELECT privileges can view other users' permission information.

It is worth mentioning that MySQL 8.0 introduces a more powerful role permissions management system. In the new version, you can use the SHOW ROLE GRANTS statement to view the role's permission information. Additionally, roles can be granted to users using the GRANT ROLE statement.

In summary, through the above MySQL statement, the user's role permissions can be easily viewed. This is useful for managing complex MySQL database systems, ensuring users only have access to the data and functionality they need.

The above is the detailed content of How to implement the statement to view user role permissions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!