Documentation Home
MySQL Utilities 1.6 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
HTML Download (TGZ) - 210.7Kb
HTML Download (Zip) - 223.2Kb


MySQL Utilities 1.6 Manual  /  Manual Pages  /  mysqlgrants — Display grants by object

5.14 mysqlgrants — Display grants by object

Managing privileges can be a challenge. Sometimes all a DBA needs to know is which users have access to a given list of objects such as a list of databases, tables, etc. This utility allows DBAs to see which users have what level of access for each object listed. Objects supported include databases, tables, functions, and procedures. The utility follows the grant hierarchy within MySQL displaying global- and object-level access GRANT statements.

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 ignores the errant privilege. For example, specifying the SELECT privilege for a routine causes the utility to exclude it from the filter check.

OPTIONS

mysqlgrants accepts the following command-line options:

  • --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 at the global level, such as "GRANT ... ON *.*". All grants are shown.

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

    • object: indicates grants 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 the 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.

NOTES

To use the users value in the --show option, you must specify at least one privilege using the --privileges option.

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 is ignored for stored routines and the EXECUTE privilege is ignored for tables but both are taken into account for databases.

EXAMPLES

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 shows 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 shows 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

This utility requires the SELECT privilege on the mysql database.


User Comments
Sign Up Login You must be logged in to post a comment.