MySQL用户权限管理详解

来源：互联网发布：部分便笺元数据已损坏编辑：程序博客网时间：2024/06/03 20:46

一、查看MYSQL数据库中所有用户及拥有权限

1.查看MYSQL数据库中所有用户

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;+---------------------------------+| query                           |+---------------------------------+| User: 'anemometer'@'%';         || User: 'root'@'%';               || User: 'zabbix-monitor'@'%';     || User: 'root'@'127.0.0.1';       || User: 'root'@'192.168.1.90';    || User: 'root'@'::1';             || User: 'anemometer'@'localhost'; || User: 'redmine'@'localhost';    || User: 'root'@'localhost';       || User: 'zabbix'@'localhost';     |+---------------------------------+10 rows in set (0.00 sec)

2.查看数据库中具体某个用户的权限

mysql> show grants for 'anemometer'@'%';+------------------------------------------------------------------------------------------------------------+| Grants for anemometer@%                                                                                    |+------------------------------------------------------------------------------------------------------------+| GRANT SELECT ON *.* TO 'anemometer'@'%' IDENTIFIED BY PASSWORD '*3E68D69BA58D0A50F00FCCF5F15DEDC9EBD432EA' || GRANT ALL PRIVILEGES ON `slow_query_log`.* TO 'anemometer'@'%'                                             |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

mysql> select * from mysql.user where user='anemometer' \G*************************** 1. row ***************************                  Host: localhost                  User: anemometer              Password: *3E68D69BA58D0A50F00FCCF5F15DEDC9EBD432EA           Select_priv: Y           Insert_priv: N           Update_priv: N           Delete_priv: N           Create_priv: N             Drop_priv: N           Reload_priv: N         Shutdown_priv: N          Process_priv: NFile_priv: N            Grant_priv: N       References_priv: N            Index_priv: N            Alter_priv: N          Show_db_priv: N            Super_priv: N Create_tmp_table_priv: N      Lock_tables_priv: N          Execute_priv: N       Repl_slave_priv: N      Repl_client_priv: N      Create_view_priv: N        Show_view_priv: N   Create_routine_priv: N

3.查看user表结构　需要具体的项可结合表结构来查询

mysql> desc mysql.user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field                  | Type                              | Null | Key | Default               | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host                   | char(60)                          | NO   | PRI |                       |       || User                   | char(16)                          | 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 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) unsigned                  | NO   |     | 0                     |       || plugin                 | char(64)                          | YES  |     | mysql_native_password |       || authentication_string  | text                              | YES  |     | NULL                  |       || password_expired       | enum('N','Y')                     | NO   |     | N                     |       |+------------------------+-----------------------------------+------+-----+-----------------------+-------+43 rows in set (0.01 sec)

二、用户权限管理主要有以下作用：

1. 可以限制用户访问哪些库、哪些表
2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
3. 可以限制用户登录的IP或域名
4. 可以限制用户自己的权限是否可以授权给别的用户

（一）、用户授权

mysql> grant all privileges on *.* to 'alex'@'%' identified by '123456' with grant option;•all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。 •on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user •to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”alex”@”192.168.0.%”，表示alex这个用户只能在192.168.0IP段登录 •identified by：指定用户的登录密码 •with grant option：表示允许用户将自己的权限授权给其它用户

可以使用GRANT给用户添加权限，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

（二）、刷新权限

对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

mysql> flush privileges;

（三）、查看用户权限（同前文所叙）

（四）、回收权限

删除alex这个用户的create权限，该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'alex@localhost';mysql> flush privileges;

(五)、删除用户

mysql> select host,user from user;+---------------+---------+| host   | user |+---------------+---------+| %    | root || %    | test3 || %    | yx  || 192.168.0.% | root || 192.168.0.% | test2 || 192.168.0.109 | test || ::1   | alex || localhost  | alex |+---------------+---------+8 rows in set (0.00 sec)mysql> drop user 'alex'@'localhost';

(六)、用户重命名

shell> rename user 'test3'@'%' to 'test1'@'%';

(七)、修改密码

1> 更新mysql.user表mysql> use mysql;# mysql5.7之前mysql> update user set password=password('123456') where user='root';# mysql5.7之后mysql> update user set authentication_string=password('123456') where user='root';mysql> flush privileges;2> 用set password命令语法：set password for ‘用户名'@'登录地址'=password(‘密码')mysql> set password for 'root'@'localhost'=password('123456');3> mysqladmin语法：mysqladmin -u用户名 -p旧的密码 password 新密码mysql> mysqladmin -uroot -p123456 password 1234abcd注意：mysqladmin位于mysql安装目录的bin目录下

(八)、忘记密码

1> 添加登录跳过权限检查配置修改my.cnf，在mysqld配置节点添加skip-grant-tables配置[mysqld]skip-grant-tables2> 重新启动mysql服务shell> service mysqld restart3> 修改密码此时在终端用mysql命令登录时不需要用户密码，然后按照修改密码的第一种方式将密码修改即可。