Note

This utility was added in MySQL Utilities 1.6.0.

The utility allows the users to choose among three reports: users, user_grants and raw.

• users

displays a list of users who have access to the list of objects

• user_grants

displays a list of users sorted by object including their access level (privileges)

• raw

display the GRANT statements that define the user's privileges

The utility also provides an optional --privileges option that permits users to specify a list of privileges that form the minimal set for access. The list of privileges forms a filter such that a user must have all of the privileges specified for a specific object.

Note

It is possible that the combination of specified privileges can form an invalid set. In such cases, the utility will ignore the errant privilege. For example, specifying the SELECT privilege for a routine causes the utility to exclude it from the filter check.

• --help

Display a help message and exit.

• --inherit-level=<level>

Specifies the inheritance level of the GRANT operations. This parameter has three options; global, database, and object. The default value is global.

• global: (default) indicates grants shown will be at the global level, such as "GRANT ... ON *.*". All grants are shown.

• database: indicates grants will be shown at the database level, such as "GRANT ... ON db1.*". Global level grants are not shown.

• object: indicates grants will be shown at the object level, such as "GRANT ... ON db1.tbl1". Database and global level grants are not shown.

This option was added in MySQL Utilities 1.6.2.

• --license

Display license information and exit.

• --privileges=<list of required privileges>

Minimum set of privileges that a user must have for any given object.

• --server=<source>

Connection information for the server.

To connect to a server, it is necessary to specify connection parameters such as user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

• Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : <login-path>[:<port>][:<socket>]

• Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : <configuration-file-path>[:<section>]

• Specify the data on the command-line (unencrypted, visible). Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]

• --ssl-ca

The path to a file that contains a list of trusted SSL CAs.

• --ssl-cert

The name of the SSL certificate file to use for establishing a secure connection.

• --ssl-key

The name of the SSL key file to use for establishing a secure connection.

• --ssl

Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

• --show=<output_type>

Type of report. Options include users, user_grants and raw.

• --verbose, -v

Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

• --version

Display version information and exit.

If you specify some privileges on the --privileges option that are not valid for all the specified objects, any that do not apply are not included in the list. For example, the SELECT privilege will be ignored for stored routines and the EXECUTE privilege will be ignored for tables but both will be taken into account for databases. EXAMPLES.PP Check the grantees and respective privileges over different object types: databases, tables, procedures and functions.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=user_grants util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1
# DATABASE `util_test`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : EXECUTE, SELECT, UPDATE
# - 'priv_test_user3'@'%' : ALTER ROUTINE, DELETE, DROP, EXECUTE, TRIGGER, UPDATE
# TABLE `util_test`.`t1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : ALL PRIVILEGES, GRANT OPTION
# - 'priv_test_user3'@'%' : DELETE, DROP, TRIGGER, UPDATE
# TABLE `util_test`.`t2`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : SELECT, UPDATE
# - 'priv_test_user3'@'%' : DELETE, DROP, TRIGGER, UPDATE
# TABLE `util_test`.`t3`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : GRANT OPTION, SELECT, TRIGGER, UPDATE
# - 'priv_test_user2'@'%' : SELECT, UPDATE
# - 'priv_test_user3'@'%' : DELETE, DROP, SELECT, TRIGGER, UPDATE
# ROUTINE `util_test`.`f1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION
# - 'priv_test_user2'@'%' : ALL PRIVILEGES, GRANT OPTION
# - 'priv_test_user3'@'%' : ALL PRIVILEGES
# ROUTINE `util_test`.`p1`:
# - 'joe'@'user' : ALL PRIVILEGES
# - 'joe_wildcard'@'%' : ALL PRIVILEGES
# - 'priv_test_user'@'%' : EXECUTE, GRANT OPTION
# - 'priv_test_user2'@'%' : EXECUTE
# - 'priv_test_user3'@'%' : ALL PRIVILEGES, GRANT OPTION
#...done.

Show the grantees and respective SQL grant statements over a list of objects.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=raw util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1
# DATABASE `util_test`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# TABLE `util_test`.`t1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `util_test`.`t1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# TABLE `util_test`.`t2`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# TABLE `util_test`.`t3`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON *.* TO 'priv_test_user'@'%'
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT SELECT ON `util_test`.`t3` TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# ROUTINE `util_test`.`f1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
# - For 'priv_test_user2'@'%'
GRANT ALTER ROUTINE ON FUNCTION `util_test`.`f1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# ROUTINE `util_test`.`p1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user'@'%'
GRANT EXECUTE, TRIGGER ON `util_test`.* TO 'priv_test_user'@'%' WITH GRANT OPTION
# - For 'priv_test_user2'@'%'
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO 'priv_test_user3'@'%' WITH GRANT OPTION
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
#...done.

Show only the users that have all privileges over a set of specified objects and the respective SQL grant statements. Notice that while some grantees do not explicitly have the ALL PRIVILEGES grant over a given object, they are still shown as a result of having the set of privileges that is equivalent to ALL PRIVILEGES for the given object type.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=raw  --privileges=ALL util_test util_test.t3 util_test.t2 \
          util_test.t1 util_test.p1 util_test.f1
# DATABASE `util_test`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# TABLE `util_test`.`t1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user2'@'%'
GRANT INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `util_test`.`t1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# TABLE `util_test`.`t2`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# TABLE `util_test`.`t3`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# ROUTINE `util_test`.`f1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user2'@'%'
GRANT ALTER ROUTINE ON FUNCTION `util_test`.`f1` TO 'priv_test_user2'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE, SHUTDOWN, EXECUTE ON *.* TO 'priv_test_user2'@'%'
# - For 'priv_test_user3'@'%'
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
# ROUTINE `util_test`.`p1`:
# - For 'joe'@'user'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe'@'user'
# - For 'joe_wildcard'@'%'
GRANT ALL PRIVILEGES ON `util_test`.* TO 'joe_wildcard'@'%'
# - For 'priv_test_user3'@'%'
GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO 'priv_test_user3'@'%' WITH GRANT OPTION
GRANT DROP, EXECUTE, TRIGGER ON *.* TO 'priv_test_user3'@'%'
GRANT UPDATE, DELETE, ALTER ROUTINE ON `util_test`.* TO 'priv_test_user3'@'%'
#...done.

Show just the list of users with some specific privileges over a set of objects.

shell> mysqlgrants --server=user:pass@localhost:3310 \
          --show=users  --privileges=SELECT,INSERT,EXECUTE \
          util_test util_test.t3 util_test.t2 util_test.t1 util_test.p1 util_test.f1
# WARNING: EXECUTE does not apply to tables and will be ignored for: `util_test`.`t2`, `util_test`.`t3` and `util_test`.`t1`.
# WARNING: INSERT and SELECT do not apply to routines and will be ignored for: `util_test`.`f1` and `util_test`.`p1`.
# DATABASE `util_test`:
# TABLE `util_test`.`t1`:
# - 'priv_test_user2'@'%'
# TABLE `util_test`.`t2`:
# TABLE `util_test`.`t3`:
# ROUTINE `util_test`.`f1`:
# - 'priv_test_user'@'%', 'priv_test_user2'@'%'
# ROUTINE `util_test`.`p1`:
# - 'priv_test_user'@'%', 'priv_test_user2'@'%', 'priv_test_user3'@'%'
#...done.

The following command will show all of the grants for users that have access to any object in the db1 database, by passing in the --inherit-level option:

shell> mysqlgrants --server=localhost1 db1.* --inherit-level=object --show raw
# Source on localhost: ... connected.
# TABLE `db1`.`tbl1`:
# - For 'joe'@'host1'
GRANT INSERT ON `db1`.`tbl1` TO 'joe'@'host1'
#...done.  

The following command will show all of the grants for users that have access to the db1 database, by passing in the --inherit-level option:

shell> mysqlgrants --server=localhost1 db1.* --inherit-level=database --show-raw
# Source on localhost: ... connected.
# TABLE `db1`.`tbl1`:
# - For 'joe'@'host1'
GRANT INSERT ON `db1`.`tbl1` TO 'joe'@'host1'
# - For 'sally'@'host2'
GRANT SELECT ON `db1`.* TO 'sally'@'host2'
#...done.

PRIVILEGES REQUIRED.PP This utility requires the SELECT privilege on the mysql database.