Provided by: mysql-utilities_1.6.4-1build1_all 
NAME
mysqlgrants - Display grants per object
SYNOPSIS
mysqlgrants [options]
DESCRIPTION
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 will ignore the errant privilege. For example, specifying
the SELECT privilege for a routine causes the utility to exclude it from the filter
check.
OPTIONS.PP 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 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 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.PP 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 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.
COPYRIGHT
Copyright © 2006, 2016, Oracle and/or its affiliates. All rights reserved.
This documentation is free software; you can redistribute it and/or modify it only under
the terms of the GNU General Public License as published by the Free Software Foundation;
version 2 of the License.
This documentation is distributed in the hope that it will be useful, but WITHOUT ANY
WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with the program;
if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.
SEE ALSO
For more information, please refer to the MySQL Utilities and Fabric documentation, which
is available online at http://dev.mysql.com/doc/index-utils-fabric.html
AUTHOR
Oracle Corporation (http://dev.mysql.com/).