mysql查看用户和权限设置

-- 查看所有用户及其权限
SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.user;

-- 查看特定用户的权限
SHOW GRANTS FOR 'username'@'host';

mysql> SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.user;
+------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
| User | Host      | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv   |
+------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
| root | localhost | Y           | Y           | Y           | Y           | Y           | Y           |
| root | %         | Y           | Y           | Y           | Y           | Y           | Y           |
+------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+

mysql> SHOW GRANTS FOR 'root'@'localhost';
+------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                       |
+------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------+

REVOKE SELECT ON *.* FROM 'username'@'host';

GRANT SELECT ON database_name.* TO 'username'@'host';