如何查看/获取MySQL/MariaDB用户帐户列表
时间：2020-01-09 10:39:45 来源:igfitidea点击:
如何在服务器上存储的MySQL/MariaDB数据库中看到MySQL用户？
您需要使用mysql数据库。
用户表存储用户名/密码，用户权限和所有其他信息。
在本快速教程中，您将学习如何找出所有MySQL和MariaDB用户以及授予每个用户的权限。
步骤1登录到mysql
首先使用mysql客户端以root用户身份登录到MySQL/MariaDB服务器。
执行以下命令：
$ mysql -u root -p
或者
$ mysql -u root -h localhost -p mysql
登录后，使用以下各种SQL查询向用户显示MariaDB或MySQL数据库中的帐户。
步骤2向用户显示
在mysql>提示符下执行以下查询，以查看MySQL数据库中的用户列表：
mysql> SELECT User FROM mysql.user;
输出示例：
如何获取MySQL用户帐户列表
步骤3向用户显示允许登录的主机名
语法为：
mysql> SELECT host, user FROM mysql.user;
或者
mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
或者
mysql> SELECT host, user, password FROM mysql.user;
输出示例：
+---------------+----------------+ | User | Host | +---------------+----------------+ | Hyman | 10.175.18.1 | | raj | 127.0.0.1 | | sai | 192.168.12.6 | | blog | 192.168.12.6 | | wordpress | 192.168.12.6 | | sales | localhost | | accounting | localhost | +---------------+----------------+ 7 rows in set (0.00 sec)
步骤4如何避免用户名重复？
尝试以下sql查询：
mysql> SELECT User distinct from mysql.user;
SELECT DISTINCT语句仅用于返回不同的值。
步骤5获得mysql.user中的字段列表
执行以下sql命令以查看与mysql.user表关联的所有字段名称：
mysql> DESC mysql.user;
输出示例：
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
44 rows in set (0.00 sec)
步骤6找出用户权限
执行以下命令：
mysql> SELECT User, Db, Host from mysql.db;
输出示例：
+---------------+-------------------+----------------+ | User | Db | Host | +---------------+-------------------+----------------+ | Hyman | wordpress | 10.174.111.100 | | raj | forum | 127.0.0.1 | | sai | cms | 192.168.1.1 | +---------------+-------------------+----------------+ 15 rows in set (0.00 sec)
以下提供有关mysql.db的更多信息：
mysql> desc mysql.db
输出示例：
MariaDB [(none)]> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
步骤7如何查找授予特定MySQL用户的特权
执行以下sql语句：
mysql> show grants for 'Hyman'@'%';
或者
mysql> show grants for 'Hyman'@'192.168.1.1';
或者
mysql> show grants for 'root'@'localhost';
输出示例：
+------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
为了协助获得补助金，请执行：
mysql> select distinct concat('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') as query from mysql.user;
输出示例：
+---------------------------------------------------+ | query | +---------------------------------------------------+ | SHOW GRANTS FOR 'Hyman'@'10.175.1.5'; | | SHOW GRANTS FOR 'raj'@'127.0.0.1'; | | SHOW GRANTS FOR 'sai'@'192.168.1.1'; | | SHOW GRANTS FOR 'root'@'localhost'; | | SHOW GRANTS FOR 'wiki'@'localhost'; | | SHOW GRANTS FOR 'forum'@'localhost'; | +---------------------------------------------------+ 6 rows in set (0.00 sec)
现在，您可以复制并粘贴" SHOW GRANTS FOR'Hyman'@'10.175.1.5";`您可以规范化和打印MySQL授权，因此可以使用pt-show-grants有效地复制，比较和版本控制。
使用apt-get命令或apt命令安装pt-show-grants：
$ sudo apt-get install percona-toolkit
输入命令：
$ pt-show-grants
