MySQL-权限管理

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

数据库中有4个默认的表information_schema， mysql ，performance_schema，sys

其中mysql就是用来管理数据库用户的

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

• host：表示可以在哪个IP地址下使用user进行数据库连接，% root表示任何ip地址都可以使用root进行远程连接数据库，localhost mysql.sys表示只有本机可以使用mysql.sys用户进行连接。host可以是ip，ip段，域名以及%
• user：允许登录数据库的用户名

1.用户管理

1.1 创建用户

create user 'zhangsan' identified by '123456';

mysql> create user 'zhangsan' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zhangsan         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

已经创建了一个zhangsan用户，任何主机都可以使用这个用户名进行登录

---

如果想使创建的用户只能在本机连接数据库，需要在用户名后面加上@'localhost'

mysql> create user 'lisi'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | zhangsan         |
| localhost | lisi             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
6 rows in set (0.00 sec)

如果要修改的话就直接修改user表字段就行了，不过在修改完记得更新权限flush privileges;

1.2 删除用户

drop user 'zhangsan' //默认删除的host为%的普通用户

drop user 'lisi'@'localhost';

1.3 修改当前用户的密码

root可以修改自己的密码，普通用户可以登录后修改自己的密码

1. alter user user() identified by 'new_password';

2. set password='new_password';

1.4 修改其他用户的密码

root用户还可以修改其他用户的密码

1. alter user 'zhangsan'@'%' identified by 'new_password';

2. set password for 'zhangsan'@'%'='new_password';

2.权限管理

2.1 查看和授予权限

show grant; 查看当前用户权限

show grant for zhangsan; root用户查看其他用户权限

grant all privileges on *.* to 'lisi'@'%';

权限有：all privileges、select、update、delete、insert

on表示指定权限针对哪些数据库和表，数据库.表

//root用户下
mysql> show grants for zhangsan;
+--------------------------------------+
| Grants for zhangsan@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`%` | //登录权限，所有新创建的用户都具有这个权限
+--------------------------------------+
//把test数据库中的所有表的select，update权限都给zhangsan用户
mysql> grant select,update on test.* to 'zhangsan'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for zhangsan;
+----------------------------------------------------+
| Grants for zhangsan@%                              |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`%`               |
| GRANT SELECT, UPDATE ON `test`.* TO `zhangsan`@`%` |
+----------------------------------------------------+
2 rows in set (0.00 sec)

在zhangsan用户下查看权限

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |//最初是没有这个数据库的，因为上面把test数据库的权限给他了
+--------------------+
2 rows in set (0.01 sec)
mysql> desc tb_user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
//zhangsan并没有insert权限
mysql> insert into tb_user1 values(1,'lucy',15);
ERROR 1142 (42000): INSERT command denied to user 'zhangsan'@'localhost' for table 'tb_user1'

root用户将所有权限授予给lisi

mysql> grant all privileges on *.* to 'lisi'@'%';
Query OK, 0 rows affected (0.01 sec)

进入lisi查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

默认权限不具有传递性，比如root用户给zhangsan select和update权限，但是zhangsan并不能将权限再授权给其他用户

可以使用with grant option，表示将自己拥有的权限授权给他人。

多次给用户授权不同的权限，会权限叠加而不是覆盖。

2.2 收回权限

//收回zhangsan所有库所有表的权限
mysql> revoke all privileges on *.* from 'zhangsan'@'%';
Query OK, 0 rows affected (0.01 sec)

用户退出重新登录数据库后才会生效。

假如你给zhangsan的权限是test数据库的所有表都有select，那么你不能单独收回test的tb_user1(其中的一个表)的select权限，要收回只能将test数据库的所有表的select权限都收回。

2.3 权限表

权限表存放在名为mysql数据库中，mysql服务器通过这些权限表来控制用户对数据库的访问，在mysql启动时，服务器将这些表中权限信息内容读入到内存。

mysql> use mysql
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv   //列层级的权限                          |
| component                                            |
| db       //数据库层级的权限                             |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants   //动态全局授权                         |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv      //表层级的权限                        |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user     //用户账号及权限信息                           |
+------------------------------------------------------+

3.访问控制

3.1 连接核实阶段

检查输入的账号和密码是否与user表中的host、user、authentication_string(加密过的密码)一致。

3.2 请求核实阶段

4.角色

角色是权限的集合，角色可以被授予或者收回权限，它代表了一类”人”的特征。

4.1 创建以及删除角色

创建角色

mysql> create role 'manager';  //等同于create role 'manager'@'%';
Query OK, 0 rows affected (0.00 sec)

删除角色

mysql> drop role 'manager';  
Query OK, 0 rows affected (0.00 sec)

4.2 授予及收回角色权限

授予权限

//root用户下
mysql> grant select,update on test.tb_user1 to 'manager';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'manager'@'%';
+------------------------------------------------------------+
| Grants for manager@%                                       |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`                        |
| GRANT SELECT, UPDATE ON `test`.`tb_user1` TO `manager`@`%` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

取消权限

mysql> revoke select,update on test.tb_user1 from 'manager';
Query OK, 0 rows affected (0.00 sec)

4.3 给用户赋予角色

mysql> grant 'manager'@'%' to 'zhangsan'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'zhangsan'@'%';
+--------------------------------------------+
| Grants for zhangsan@%                      |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`%`       |
| GRANT UPDATE ON `test`.* TO `zhangsan`@`%` |
| GRANT `manager`@`%` TO `zhangsan`@`%`      |
+--------------------------------------------+
3 rows in set (0.00 sec)

由于mysql中创建角色都是默认没有被激活，所以给用户赋予角色也没有角色的权限，需要激活角色。激活后，用户需要重新登录才能看到赋予的角色以及使用角色的权限。

登录zhangsan用户

查询当前角色是否被激活
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |//未被激活，因此用户未具备相应的角色
+----------------+
1 row in set (0.00 sec)
//激活角色
mysql> set default role 'manager'@'%' to 'zhangsan'@'%';
Query OK, 0 rows affected (0.00 sec)
//退出重新登录
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `manager`@`%`  |
+----------------+
1 row in set (0.00 sec)

还有一种方法可以对所有角色永久激活，新创建的角色都默认激活

//ROOT用户下
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)
mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)