similar_grants

NAME

similar_grants: listing GRANTEEs sharing the same set of privileges (i.e. share same role)

TYPE

View

DESCRIPTION

similar_grants analyzes the GRANTEEs on a server, and groups them by their set of privileges. GRANTEEs with the exact same set of privileges will reside in same group. Such groups are commonly referred to as "roles" (though MySQL does not provide with roles per se).

The view merely presents the list of GRANTEEs within each role; for complete listing of the privileges given to such GRANTEEs, join with sql_grants or sql_show_grants.

Passwords are not taken into account when comparing GRANTEEs. It is possible that GRANTEEs sharing the exact same set of privileges will have different passwords.

STRUCTURE

mysql> DESC similar_grants;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| sample_grantee   | varchar(81) | YES  |     | NULL    |       |
| count_grantees   | bigint(21)  | NO   |     | 0       |       |
| similar_grantees | longtext    | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+

SYNOPSIS

Columns of this view:

  • sample_grantee: a single, representative GRANTEE, in a group of GRANTEEs sharing same set of privileges.
  • count_grantees: number of GRANTEEs in group.
  • similar_grantees: list of GRANTEEs sharing exact same set of privileges. This includes the sample_grantee.

EXAMPLES

List all similar grants on a server:

mysql> SELECT * FROM similar_grants;
+-------------------------------+----------------+-------------------------------------------------------+
| sample_grantee                | count_grantees | similar_grantees                                      |
+-------------------------------+----------------+-------------------------------------------------------+
| 'root'@'127.0.0.1'            |              3 | 'root'@'127.0.0.1','root'@'myhost','root'@'localhost' |
| 'repl'@'10.%'                 |              2 | 'repl'@'10.%','replication'@'10.0.0.%'                |
| 'apps'@'%'                    |              1 | 'apps'@'%'                                            |
| 'gromit'@'localhost'          |              1 | 'gromit'@'localhost'                                  |
| 'monitoring_user'@'localhost' |              1 | 'monitoring_user'@'localhost'                         |
+-------------------------------+----------------+-------------------------------------------------------+

In the above, three root accounts have identical grants (set of privileges); two accounts, 'repl'@'10.%' and 'replication'@'10.0.0.%', share identical grants; three other accounts have a distinct set privileges.

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

duplicate_grantee(), sql_grants, sql_show_grants

AUTHOR

Shlomi Noach
 
common_schema documentation