mysql 客户端连接和权限管理

客户端能够连接到MySQL服务器的条件有

用户名、密码、IP地址

示例1：

(root@localhost)[performance_schema]> create user 'david'@'%' identified by 'david';
Query OK, 0 rows affected (0.02 sec)

上述命令创建用户：david，密码：david，IP：客户端的IP任意

(root@localhost)[performance_schema]> drop user 'david'@'%';
Query OK, 0 rows affected (0.00 sec)

删除用户david，重新连接，报出错误：

2003:Can not connect to MySQL server on '192.168.150.149'(10060);

示例2：

(root@localhost)[performance_schema]> create user 'david'@'192.165.1.%' identified by 'david';
Query OK, 0 rows affected (0.00 sec)

创建用户david，密码：david，IP地址要求是：192.165.1.% 此时在主机192.168.%.%无法连接到用户david的MySQL服务器

查看用户权限

（1）查看当前用户权限

(root@localhost)[performance_schema]> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

（2）查看指定用户权限

(root@localhost)[performance_schema]> show grants for 'david'@'%';
+-----------------------------------+
| Grants for david@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

给用户授权

(1) 添加权限

(root@localhost)[performance_schema]> grant select,insert,update,delete on test.* to 'david'@'%';
Query OK, 0 rows affected (0.00 sec)

给用户 david 授予test库的增删改查权限


(root@localhost)[performance_schema]> show grants for 'david'@'%';
+-----------------------------------------------------------------+
| Grants for david@%                                              |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'david'@'%' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

用户 david 不仅具有 USAGE 连接权限，并且具有 test库的增删改查权限

（2）在已有权限基础上，增加权限

(root@localhost)[performance_schema]> grant create,index on test.* to 'david'@'%';
Query OK, 0 rows affected (0.00 sec)

(root@localhost)[performance_schema]> show grants for 'david'@'%';
+--------------------------------------------------------------------------------+
| Grants for david@%                                                             |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%'                                              |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON `test`.* TO 'david'@'%' |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(3) 授予用户权限，并且被授予用户可以将所拥有的权限授予其他用户以上（1）（2）中授权的方式，david所拥有的权限是不可以授予其他用户

通过以下方式，david可将拥有的权限授予其他用户

(root@localhost)[performance_schema]> grant create,index on test.* to 'david'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

删除权限

(root@localhost)[performance_schema]> revoke create,index on test.* from 'david'@'%';
Query OK, 0 rows affected (0.01 sec)

(root@localhost)[performance_schema]> show grants for 'david'@'%';
+-----------------------------------------------------------------+
| Grants for david@%                                              |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'david'@'%' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

创建用户后，修改用户密码

(root@localhost)[performance_schema]> alter user 'david'@'%' identified by 'david0';
Query OK, 0 rows affected (0.00 sec)

创建的用户存放在何处？

存在于 mysql 数据库下的 user 表中，密码采用了password()函数进行了加密