Provided by: mysql-utilities_1.6.4-1_all bug

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/).