1MK-SHOW-GRANTS(1)     User Contributed Perl Documentation    MK-SHOW-GRANTS(1)
2
3
4

NAME

6       mk-show-grants - Canonicalize and print MySQL grants so you can
7       effectively replicate, compare and version-control them.
8

SYNOPSIS

10       Usage: mk-show-grants [OPTION...] [DSN]
11
12       mk-show-grants shows grants (user privileges) from a MySQL server.
13
14       Examples:
15
16          mk-show-grants
17
18          mk-show-grants --separate --revoke | diff othergrants.sql -
19

RISKS

21       The following section is included to inform users about the potential
22       risks, whether known or unknown, of using this tool.  The two main
23       categories of risks are those created by the nature of the tool (e.g.
24       read-only tools vs. read-write tools) and those created by bugs.
25
26       mk-show-grants is read-only by default, and very low-risk.  If you
27       specify "--flush", it will execute "FLUSH PRIVILEGES".
28
29       At the time of this release, we know of no bugs that could cause
30       serious harm to users.
31
32       The authoritative source for updated information is always the online
33       issue tracking system.  Issues that affect this tool will be marked as
34       such.  You can see a list of such issues at the following URL:
35       <http://www.maatkit.org/bugs/mk-show-grants>.
36
37       See also "BUGS" for more information on filing bugs and getting help.
38

DESCRIPTION

40       mk-show-grants extracts, orders, and then prints grants for MySQL user
41       accounts.
42
43       Why would you want this?  There are several reasons.
44
45       The first is to easily replicate users from one server to another; you
46       can simply extract the grants from the first server and pipe the output
47       directly into another server.
48
49       The second use is to place your grants into version control.  If you do
50       a daily automated grant dump into version control, you'll get lots of
51       spurious changesets for grants that don't change, because MySQL prints
52       the actual grants out in a seemingly random order.  For instance, one
53       day it'll say
54
55         GRANT DELETE, INSERT, UPDATE ON `test`.* TO 'foo'@'%';
56
57       And then another day it'll say
58
59         GRANT INSERT, DELETE, UPDATE ON `test`.* TO 'foo'@'%';
60
61       The grants haven't changed, but the order has.  This script sorts the
62       grants within the line, between 'GRANT' and 'ON'.  If there are
63       multiple rows from SHOW GRANTS, it sorts the rows too, except that it
64       always prints the row with the user's password first, if it exists.
65       This removes three kinds of inconsistency you'll get from running SHOW
66       GRANTS, and avoids spurious changesets in version control.
67
68       Third, if you want to diff grants across servers, it will be hard
69       without "canonicalizing" them, which mk-show-grants does.  The output
70       is fully diff-able.
71
72       With the "--revoke", "--separate" and other options, mk-show-grants
73       also makes it easy to revoke specific privileges from users.  This is
74       tedious otherwise.
75

OPTIONS

77       This tool accepts additional command-line arguments.  Refer to the
78       "SYNOPSIS" and usage information for details.
79
80       --ask-pass
81           Prompt for a password when connecting to MySQL.
82
83       --charset
84           short form: -A; type: string
85
86           Default character set.  If the value is utf8, sets Perl's binmode
87           on STDOUT to utf8, passes the mysql_enable_utf8 option to
88           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
89           other value sets binmode on STDOUT without the utf8 layer, and runs
90           SET NAMES after connecting to MySQL.
91
92       --config
93           type: Array
94
95           Read this comma-separated list of config files; if specified, this
96           must be the first option on the command line.
97
98       --database
99           short form: -D; type: string
100
101           The database to use for the connection.
102
103       --defaults-file
104           short form: -F; type: string
105
106           Only read mysql options from the given file.  You must give an
107           absolute pathname.
108
109       --drop
110           Add DROP USER before each user in the output.
111
112       --flush
113           Add FLUSH PRIVILEGES after output.
114
115           You might need this on pre-4.1.1 servers if you want to drop a user
116           completely.
117
118       --[no]header
119           default: yes
120
121           Print dump header.
122
123           The header precedes the dumped grants.  It looks like:
124
125             -- Grants dumped by mk-show-grants 1.0.19
126             -- Dumped from server Localhost via UNIX socket, MySQL 5.0.82-log at 2009-10-26 10:01:04
127
128           See also "--[no]timestamp".
129
130       --help
131           Show help and exit.
132
133       --host
134           short form: -h; type: string
135
136           Connect to host.
137
138       --ignore
139           type: array
140
141           Ignore this comma-separated list of users.
142
143       --only
144           type: array
145
146           Only show grants for this comma-separated list of users.
147
148       --password
149           short form: -p; type: string
150
151           Password to use when connecting.
152
153       --pid
154           type: string
155
156           Create the given PID file.  The file contains the process ID of the
157           script.  The PID file is removed when the script exits.  Before
158           starting, the script checks if the PID file already exists.  If it
159           does not, then the script creates and writes its own PID to it.  If
160           it does, then the script checks the following: if the file contains
161           a PID and a process is running with that PID, then the script dies;
162           or, if there is no process running with that PID, then the script
163           overwrites the file with its own PID and starts; else, if the file
164           contains no PID, then the script dies.
165
166       --port
167           short form: -P; type: int
168
169           Port number to use for connection.
170
171       --revoke
172           Add REVOKE statements for each GRANT statement.
173
174       --separate
175           List each GRANT or REVOKE separately.
176
177           The default output from MySQL's SHOW GRANTS command lists many
178           privileges on a single line.  With "--flush", places a FLUSH
179           PRIVILEGES after each user, instead of once at the end of all the
180           output.
181
182       --set-vars
183           type: string; default: wait_timeout=10000
184
185           Set these MySQL variables.  Immediately after connecting to MySQL,
186           this string will be appended to SET and executed.
187
188       --socket
189           short form: -S; type: string
190
191           Socket file to use for connection.
192
193       --[no]timestamp
194           default: yes
195
196           Add timestamp to the dump header.
197
198           See also "--[no]header".
199
200       --user
201           short form: -u; type: string
202
203           User for login if not current user.
204
205       --version
206           Show version and exit.
207

DSN OPTIONS

209       These DSN options are used to create a DSN.  Each option is given like
210       "option=value".  The options are case-sensitive, so P and p are not the
211       same option.  There cannot be whitespace before or after the "=" and if
212       the value contains whitespace it must be quoted.  DSN options are
213       comma-separated.  See the maatkit manpage for full details.
214
215       ·   A
216
217           dsn: charset; copy: yes
218
219           Default character set.
220
221       ·   D
222
223           dsn: database; copy: yes
224
225           Default database.
226
227       ·   F
228
229           dsn: mysql_read_default_file; copy: yes
230
231           Only read default options from the given file
232
233       ·   h
234
235           dsn: host; copy: yes
236
237           Connect to host.
238
239       ·   p
240
241           dsn: password; copy: yes
242
243           Password to use when connecting.
244
245       ·   P
246
247           dsn: port; copy: yes
248
249           Port number to use for connection.
250
251       ·   S
252
253           dsn: mysql_socket; copy: yes
254
255           Socket file to use for connection.
256
257       ·   u
258
259           dsn: user; copy: yes
260
261           User for login if not current user.
262

DOWNLOADING

264       You can download Maatkit from Google Code at
265       <http://code.google.com/p/maatkit/>, or you can get any of the tools
266       easily with a command like the following:
267
268          wget http://www.maatkit.org/get/toolname
269          or
270          wget http://www.maatkit.org/trunk/toolname
271
272       Where "toolname" can be replaced with the name (or fragment of a name)
273       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
274       installation is needed.  The first URL gets the latest released version
275       of the tool, and the second gets the latest trunk code from Subversion.
276

ENVIRONMENT

278       The environment variable "MKDEBUG" enables verbose debugging output in
279       all of the Maatkit tools:
280
281          MKDEBUG=1 mk-....
282

SYSTEM REQUIREMENTS

284       You need the following Perl modules: DBI and DBD::mysql.
285

BUGS

287       For a list of known bugs see
288       <http://www.maatkit.org/bugs/mk-show-grants>.
289
290       Please use Google Code Issues and Groups to report bugs or request
291       support: <http://code.google.com/p/maatkit/>.  You can also join
292       #maatkit on Freenode to discuss Maatkit.
293
294       Please include the complete command-line used to reproduce the problem
295       you are seeing, the version of all MySQL servers involved, the complete
296       output of the tool when run with "--version", and if possible,
297       debugging output produced by running with the "MKDEBUG=1" environment
298       variable.
299

COPYRIGHT, LICENSE AND WARRANTY

301       This program is copyright 2007-2011 Baron Schwartz.  Feedback and
302       improvements are welcome.
303
304       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
305       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
306       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
307
308       This program is free software; you can redistribute it and/or modify it
309       under the terms of the GNU General Public License as published by the
310       Free Software Foundation, version 2; OR the Perl Artistic License.  On
311       UNIX and similar systems, you can issue `man perlgpl' or `man
312       perlartistic' to read these licenses.
313
314       You should have received a copy of the GNU General Public License along
315       with this program; if not, write to the Free Software Foundation, Inc.,
316       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
317

SEE ALSO

319       Someone pointed out that this has been done before (not surprising, as
320       it's not all that complicated).  Visit
321       <http://www.futhark.ch/mysql/139.html> for a simpler implementation of
322       the same general concept, though without the canonicalization.  I
323       borrowed the idea of adding DROP USER from that script, and it inspired
324       me to add the REVOKE functionality too.
325

AUTHOR

327       Baron Schwartz
328

ABOUT MAATKIT

330       This tool is part of Maatkit, a toolkit for power users of MySQL.
331       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
332       primary code contributors.  Both are employed by Percona.  Financial
333       support for Maatkit development is primarily provided by Percona and
334       its clients.
335

VERSION

337       This manual page documents Ver 1.0.23 Distrib 7540 $Revision: 7477 $.
338
339
340
341perl v5.32.0                      2020-07-28                 MK-SHOW-GRANTS(1)
Impressum