mysqlgrants usage examples

MySQL utility mysqlgrants usage

mysqlgrants - display grants per object
Usage: mysqlgrants --server=user:pass@host:port  [db_name[.obj_name]]

mysqlgrants utility allows DBAs to see which users have what level of access for each MySQL database objects like databases, tables, functions, and procedures. 

mysqlgrants --help
mysqlgrants --version

mysqlgrants --server=user:pass@localhost:3311 --show=user_grants testdb test.t3 test.t2 test.t1 test.p1 test.f1
mysqlgrants --server=user:pass@localhost:3312 --show=raw devdb test.t2 test.t1 test.p1 test.f1
mysqlgrants --server=mysql_admin --show=raw --privileges=ALL staging test.t3 test.t2 test.p1 test.f1

mysqlgrants --server=user:pass@localhost:3313 --show=users --privileges=SELECT,INSERT,EXECUTE prod test.t3 test.t1 test.p1 test.f1
mysqlgrants --server=master db1.* --inherit-level=object --show raw
mysqlgrants --server=local_dba db1.* --inherit-level=database --show-raw

mysqlgrants Options:
  --version             show program's version number and exit
  --help                 display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        user[:password]@host[:port][:socket] or login-path[:port][:socket] or config-path[[group]].

  --ssl-ca=SSL_CA       path to a file that contains a list of trusted SSL  CAs.
  --ssl-cert=SSL_CERT   name of the SSL certificate file to use for  establishing a secure connection.
  --ssl-key=SSL_KEY     name of the SSL key file to use for establishing a  secure connection.
  --ssl=SSL             specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).

  -v, --verbose         control how much information is displayed. e.g., -v =  verbose, -vv = more verbose, -vvv = debug
  --show=SHOW_MODE      controls the content of the report. If the value USERS is specified, the report shows only the list of users  with any kind of grant over the object. If USER_GRANTS  is specified the reports shows each user along with her list of privileges for each object. Finally,  specifying RAW the utility returns each user along with the list of SQL grant statements that have influence over the specific object. Default is USER_GRANTS.
  --privileges=PRIVILEGES  minimum set of privileges that a user must have for any given object. Unless a user has all the privileges listed for a specific object, she will not appear in the list of users with privileges for that specific object. To list multiple privileges, use a comma-separated list.

  --inherit-level=INHERIT_LEVEL specify the level of inheritance that should be taken  into account. If OBJECT is specified, global level and database level grants are not inherited by objects. If  DATABASE level is specified global level grants are  not inherited by databases and objects inside those  databases. Finally, if GLOBAL level is specified, normal inheritance rules are applied, global grants apply to both databases and objects and database level grants apply to the objects.

Introduction
------------
The mysqlgrants utility is designed to display the users who have access to a list of objects and/or databases. It can also display the privileges grouped by user and the raw GRANT statements.

Furthermore, if the user specifies a list of privileges, the utility shall display those users who have all of the privileges listed (they are AND conditions).

In order to use the utility, you need to specify at least one object to check.
To specify several objects at once, you should list each object as a separate argument for the utility, using full qualified names as shown by the following examples:

  # Get the list of users with their respective privileges for the 'db1' database and 'db1'.'table1' table.

  $ mysqlgrants --server=root:pass@host1:3306 --show=user_grants db1 db1.table1

  # Get the list of users with both SELECT and UPDATE privileges on the 'db1' database and 'db1'.'table1' table.

  $ mysqlgrants --server=root:pass@host1:3306 --show=users --privileges=SELECT,UPDATE db1 db1.table1

  # Get the list of users that have at least the TRIGGER and DROP privileges
  # for database 'db1' and 'db1'.'table1' table and show the list of SQL GRANT statements that grant them those privileges.

  $ mysqlgrants --server=root:pass@host1:3306 --show=raw --privileges=TRIGGER,DROP db1 db1.table1

  # Get the list of users with specific privileges at the object level, for all the objects of the database 'db1'.

  $ mysqlgrants --server=root:pass@host1:3306 --inherit-level=object db1.*

Helpful Hints
-------------
  - To use the --show=users option you need to specify at least one privilege using the --privilege option.
  - You can list the users that have specific privileges using the option --privileges. The user must have all privileges listed in order to be included in the result.
  - 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.
  - The --inherit-level option can be used for filtering out global users, and also users with the same database level privileges at the object level.

Related MySQL Articles: