We are happy to announce mysqlgrants, a new utility that allows users to display the privileges of grantees over database objects. Together with mysqlbinlogmove, these are the new utilities included in MySQL Utilities release-1.6.0 Alpha.
Mysqlgrants allows you to know which users have access to a specific object or list of objects. Furthermore, it can also show the list of privileges that each user has over said object(s). In short, mysqlgrants simplifies the task of monitoring grants in MySQL helping you ensure users do not have more permissions than necessary, thus keeping data more secure.
Main Features
Below is a summary of the main features of the mysqlgrants utility:
- Helps DBAs to see which users have what level of access for each object listed.
- Supports several types of reporting: list just the grantees, the grantees and their respective grants or the grantees together with the DCL statements.
- Displays global and object-level privileges following the grant hierarchy within MySQL. For example, when showing the grants for a specific table, the global and database privileges that apply are also displayed.
- Provides a –privileges option that allows DBAs to filter out all grantees that do not have at least a specific set of privileges over the specified objects.
Permissions
The mysqlgrants utility requires the SELECT privilege over the mysql database.
Examples
Below is an example of how to check the grantees and respective privileges
over a set of different object types: databases, procedures and functions.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
shell> mysqlgrants.py --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 |
Here is an example of how to 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
shell> mysqlgrants.py --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. |
The following shows how to list just the grantees with some specific privileges
over a set of objects.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
shell> mysqlgrants.py --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. |
Do you want to know more ?
Try it now and send us your feedback!
MySQL Utilities release-1.6.0 alpha is available for download from the following links.
MySQL developers website: http://dev.mysql.com/downloads/utilities/1.6.html
Launchpad project: https://launchpad.net/mysql-utilities
The documentation of MySQL Utilities can be obtained from the following link:
http://dev.mysql.com/doc/index-gui.html
Contributing Ideas:
- Community users: http://bugs.mysql.com (MySQL Workbench: Utilities)
- Fabric, Sharding, HA, Utilities Forum: http://forums.mysql.com/list.php?144
Meet us at MySQL Central @ OpenWorld
Do you want to get your hands dirty and ask your questions directly to the MySQL Utilities team?
Participate in the Hands-on Lab session “DevOps Made Easy with MySQL Utilities” at the 2014 MySQL Central @ OpenWorld conference. See this link for details about the session: https://oracleus.activeevents.com/2014/connect/sessionDetail.ww?SESSION_ID=9234
MySQL Community Reception at Oracle OpenWorld: http://eventreg.oracle.com/profile web/index.cfm?PKWebId=0x11859974f4