MySQL Manual
4 Администрирование баз данных
- 4.5 Справочник по языку администрирования баз данных
- 4.5.6 Синтаксис команды
SHOW- 4.5.6.1 Получение информации по базам данных, таблицам, столбцам и индексам
- 4.5.6.2
SHOW TABLE STATUS - 4.5.6.3
SHOW STATUS - 4.5.6.4
SHOW VARIABLES - 4.5.6.5
SHOW LOGS - 4.5.6.6
SHOW PROCESSLIST - 4.5.6.7
SHOW GRANTS - 4.5.6.8
SHOW CREATE TABLE - 4.5.6.9
SHOW WARNINGS | ERRORS - 4.5.6.10
SHOW TABLE TYPES - 4.5.6.11
SHOW PRIVILEGES
Additional languages
Additional formats
4.5.6.7 SHOW GRANTS
SHOW GRANTS FOR user выводит список команд назначения привилегий, которые нужны при дублировании привилегий для пользователя.
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'ĺocalhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
Для получения информации по привилегии в текущей сессии можно использовать
функцию CURRENT_USER() (новшество в версии 4.0.6) для того, чтобы
выяснить, по какому пользователю в действительности аутентифицировалась сессия.
See section 6.3.6.2 Разные функции.
User Comments
Selecting everything from mysql.user isn't quite the same as doing a SHOW GRANTS for user@host. Ideally, MySQL should allow a subquery on "show", where you could do "SHOW grants for (select concat(user,'@',host) from mysql.user)". However, until then, this Perl script might help (substitute "youruser" and "yourpassword" with details of a suitably privileged user):
#!/usr/bin/perl -w
use strict;
use DBI;
use Text::Wrap qw($columns &wrap);
my $dbase = "mysql";
my $dbuser = "youruser";
my $dbpassword = "yourpassword";
my $dbhost = "localhost";
my $dbh;
$dbh = DBI->connect( "DBI:mysql:$dbase:$dbhost", $dbuser, $dbpassword ) or die "can't open database ", $dbh->errstr, __LINE__;
my $statement = qq|SELECT User, Host from user |;
my $que = $dbh->prepare($statement);
my $result = $que->execute or die "error on database statement ", $que->errstr, __LINE__;
my $tmp;
my $columns = 120;
while ( $tmp = $que->fetchrow_hashref ) {
my $statement2 = qq| SHOW GRANTS for | . "'" . $tmp->{User} . "'\@'" . $tmp->{Host} . "'";
my $que2 = $dbh->prepare($statement2);
my $result2 = $que2->execute or die "error on database statement ", $que2->errstr, __LINE__;
print qq(Privileges for $tmp->{User}\@$tmp->{Host}:\n\n);
while ( my $tmp2 = $que2->fetchrow_hashref ) {
print wrap( "", "", $tmp2->{ "Grants for $tmp->{User}\@$tmp->{Host}" } ), "\n\n";
}
print "-" x 120, "\n\n";
}
The perl script provided by simon.ransome is very good, and runs as is. However, the print formatting is not perfect. I think the author intended the $columns variable to set the width of wrapping. If you remove the "my" from this line: my $columns = 120; then the script will work as the author intended. (in my case, I wanted to wrap at 200). Also, the line print "-" x 120, "\n\n"; could be changed to print "-" x $columns, "\n\n"; so that the separator bar will be the same width as the wrapped text. Finally, it's not a bad idea to put this at the end of the script (will occur implicitly, but I like to clean up anyway) $dbh->disconnect;
Add your own comment.