涂抹mysql笔记-数据库中的权限体系

涂抹mysql笔记-数据库中的权限体系
<>能不能连接，主机名是否匹配、登陆使用的用户名和密码是否正确。
mysql验证用户需要检查3项值：用户名、密码和主机来源（user、password、host）
<>能不能执行操作。涉及到的字典表 mysql.user mysql.db mysql.tables_priv mysql.columns_priv mysql.proc_priv
<>权限变更何时生效。mysql数据库启动时将上面的权限字典表的内容读到内存中，有用户连接或执行操作时根据内存总的数据来检查用户是否有权限进行执行相应的操作。
管理员对用户权限进行了修改操作是否即时生效呢，不一定。
如果是通过GRANT REVOKE SET PASSWORD RENAME USER等mysql提供的命令执行修改，那么权限马上生效。因为这些命令将触发系统重新载入授权表到内存。如果是手动修改字典表（INSERT UPDATE DELETE）这种情况权限不会马上生效，除非重启mysql服务，或者dba主动触发授权表的重新装载授权表被重新加载后，对当前已连接的客户端又会产生哪些影响？具体如下：
表或列粒度的权限将在客户端下次执行操作时生效。
数据库级的权限将在客户端执行 use dba_name语句切换数据库时生效
全局权限和密码修改对当前已连接的客户端无效，下次连接时才会生效
<>创建用户，用户的长度不能超过16字符，用户名和密码对大小写敏感
create user jason;
select user,host,password from mysql.user where user='jason';
system@mysql>select user,host,password from mysql.user where user='jason';
+-------+------+----------+
| user | host | password |
+-------+------+----------+
| jason | % | |
+-------+------+----------+
1 row in set (0.00 sec)
我们刚创建的用户即没有登录的密码也没有指定来源主机，因此该用户可以从任意安装了mysql客户端并能够访问目标服务器的机器上创建连接。
修改用户密码：set password for jason=password('oralinux');
设置用户密码过期：alter user jason password expire;
system@mysql>select user,host,password from mysql.user where user='jason';
+-------+------+----------+
| user | host | password |
+-------+------+----------+
| jason | % | |
+-------+------+----------+
1 row in set (0.00 sec)
user字典表中的host列值为空或者%均代表任意主机。如果希望创建的用户只能从某个主机或者某个IP端访问，那么在创建用户时就必须明确指定host，指定的host既可以是IP也可是主机名，或者可正确解析至IP地址的其他自定义名称。创建jason_ip的用户，该用户仅允许从192.168.1.1的主机连接至mysql服务端，执行命令如下：
system@mysql>create user jason_ip@'192.168.1.1' identified by 'oralinux';
Query OK, 0 rows affected (0.00 sec)
这样使用jason_ip用户登录时只有从192.168.1.1主机发出登录请求才能成功。从非192.168.1.1的主机使用jason_ip用户连接时，不管密码是否正确，都会抛出“ERROR 1045（28000）：Access denied”的错误信息。
如果系统192.168.1.%网段的主机均能够使用jason_ip用户连接需要使用通配符：
create user jason_ip@'192.168.1.%' identified by 'oralinux';
其他大型数据库软件直接指定用户即可登录数据库，但在mysql数据库中则还需要主机这一维度，用户名和主机*('user'@'host')构成一个唯一的账户。
GRANT方式创建用户：
system@mysql>grant select on jason.* to jason_grant@192.168.1.1 identified by 'oralinux';
Query OK, 0 rows affected (0.00 sec)

system@mysql>select user,host,password from mysql.user where user='jason_grant';
+-------------+-------------+-------------------------------------------+
| user | host | password |
+-------------+-------------+-------------------------------------------+
| jason_grant | 192.168.1.1 | *5F24E62754E18D89BEFCAC099A7AE43B472BE631 |
+-------------+-------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql.user字典表中插入方式创建账户
system@mysql>insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values('192.168.1.1','jason_insert',password('oralinux'),'','','');
Query OK, 1 row affected (0.00 sec)

system@mysql>select user,host,password from mysql.user where user='jason_insert';
+--------------+-------------+-------------------------------------------+
| user | host | password |
+--------------+-------------+-------------------------------------------+
| jason_insert | 192.168.1.1 | *5F24E62754E18D89BEFCAC099A7AE43B472BE631 |
+--------------+-------------+-------------------------------------------+
1 row in set (0.00 sec)
手动修改权限字典表后需要执行flush privileges语句重新加载收钱信息到内存中。否则权限不会生效。
system@mysql>flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改权限字段表修改jason用户的密码为redhatlinux：
system@mysql>update mysql.user set password=('redhatlinux') where user='jason' and host='%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
system@mysql>flush privileges;
Query OK, 0 rows affected (0.01 sec)

授予权限
mysql用户权限
权限类型　　　　　　　　　　　　　　　　　　简要说明
ALL　　　　　　　　　　　　　　授予除GRANT OPTION外的所有权限
ALTER　　　　　　　　　允许执行ALTER TABLE操作
ALTER ROUTINE 　　　　　允许修改或删除存储过程和函数
CREATE 　　　　　　　　　　　允许创建数据库和创建表对象
CREATE ROUTINE 　　　　　　允许创建存储过程和函数
CREATE TABLESPACE　　　　允许创建、修改或删除表空间及日志文件组
CREATE TEMPORARY TABLES 允许执行CREATE TEMPORARY TABLE语句创建临时表
CREATE USER 　　　　　　　　允许执行CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES语句
CREATE VIEW 　　　　　　　　允许创建修改视图
DELETE 　　　　　　　　允许执行DELETE语句
DROP 　　　　　　　　　　　　允许执行删除数据库表或视图
EVENT　　　　　　　　　　　　允许使用EVENT对象
EXECUTE　　　　　　　允许用户执行存储过程
FILE 　　　　　　　　　　　　允许用户读写文件
GRANT OPTION　　　　　允许将授予的权限再由该用户授予其他用户
INDEX 　　　　　　　　　　允许创建删除索引
INSERT　　　　　　　　　　允许执行INSERT语句
LOCK TABLESPACE 　　　　允许对拥有SELECT权限的表对象执行LOCK TABLESPACE
PROCESS　　　　　　　　允许用户执行SHOW PROCESSLIST命令查看当前所有连接
PROXY 　　　　　　　　　　允许使用PROXY
REFERENCES　　　　　　　　尚未应用
RELOAD　　　　　　　　　　允许执行FLUSH操作
REPLICATION CLIENT　　　　允许用户连接复制环境中的Master/Slave
REPLICATION SLAVE　　　　允许复制环境的Slave端从Mater端读取数据
SELECT 　　　　　　　　　　允许执行SELECT语句
SHOW DATABASES 　　　　　　允许执行SHOW DATABASES语句显示所有数据库
SHOW VIEW 　　　　　　　　允许执行SHOW CREATE VIEW查看视图定义
SHUTDOWN　　　　　　允许通过mysqladmin命令关闭数据库
SUPER 　　　　　　允许执行管理操作。如：CHANGE MASTER TO、KILL、PURGE BINARY LOGS、SET GLOBSL等
TRIGGER 　　　　　　　　允许创建或删除触发器
UPDATE 　　　　　　　　允许执行UPDATE操作
USAGE 　　　　　　　　意指没有前线（no privileges）但可以登录

授予jason_grant@'192.168.1.1'用户查询mysql.user表的权限：
system@mysql>grant select on mysql.user to jason_grant@'192.168.1.1';
Query OK, 0 rows affected (0.01 sec)
一个用户有建库的权限那么它也有建表（但不能创建视图）权限。drop权限类似但是drop权限也能删除视图对象。

with_option的几个选项：
GRANT OPTION：允许用户再将该权限授予其他用户。
MAX_QUERIRES_PER_HOUR:允许用户每次是执行的查询语句的数量。
MAX_UPDATES_PER_HOUR:允许用户每小时执行的更新语句的数量。
MAX_CONNECTIONS_PER_HOUR:允许用户每小时连接的次数。
MAX_USER_CONNECTIONS:允许用户同时连接服务器的数量。

<>查看和收回用户权限。
show grants [for user]
system@mysql>show grants G
*************************** 1. row ***************************
Grants for system@localhost: GRANT ALL PRIVILEGES ON *.* TO 'system'@'localhost' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631' WITH GRANT OPTION
1 row in set (0.00 sec)

system@(none)>show grants for jason_grant@192.168.1.1 G
*************************** 1. row ***************************
Grants for jason_grant@192.168.1.1: GRANT USAGE ON *.* TO 'jason_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631'
*************************** 2. row ***************************
Grants for jason_grant@192.168.1.1: GRANT SELECT ON `jason`.* TO 'jason_grant'@'192.168.1.1'
*************************** 3. row ***************************
Grants for jason_grant@192.168.1.1: GRANT SELECT ON `mysql`.`user` TO 'jason_grant'@'192.168.1.1'
3 rows in set (0.00 sec)

收回权限：收回jason_grant@'192.168.1.1'的用户拥有的mysql.user表对消的select权限、执行revoke命令如下：
system@(none)> revoke select on mysql.user from jason_grant@192.168.1.1;
Query OK, 0 rows affected (0.00 sec)
system@(none)>show grants for jason_grant@192.168.1.1 G
*************************** 1. row ***************************
Grants for jason_grant@192.168.1.1: GRANT USAGE ON *.* TO 'jason_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631'
*************************** 2. row ***************************
Grants for jason_grant@192.168.1.1: GRANT SELECT ON `jason`.* TO 'jason_grant'@'192.168.1.1'
2 rows in set (0.00 sec)
注：usage权限用户一经创建就会拥有，并且无法通过revoke语句收回。
system@(none)> revoke usage on *.* from 'jason_grant'@'192.168.1.1';
Query OK, 0 rows affected (0.00 sec)
system@(none)>show grants for jason_grant@192.168.1.1 G
*************************** 1. row ***************************
Grants for jason_grant@192.168.1.1: GRANT USAGE ON *.* TO 'jason_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631'
*************************** 2. row ***************************
Grants for jason_grant@192.168.1.1: GRANT SELECT ON `jason`.* TO 'jason_grant'@'192.168.1.1'
2 rows in set (0.00 sec)
可以看出usage权限还在。

revoke all privileges
system@(none)> revoke all privileges on *.* from 'jason_grant'@'192.168.1.1';
Query OK, 0 rows affected (0.00 sec)
system@(none)>show grants for jason_grant@192.168.1.1 G
*************************** 1. row ***************************
Grants for jason_grant@192.168.1.1: GRANT USAGE ON *.* TO 'jason_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631'
*************************** 2. row ***************************
Grants for jason_grant@192.168.1.1: GRANT SELECT ON `jason`.* TO 'jason_grant'@'192.168.1.1'
2 rows in set (0.00 sec)
可以看到usage和select on权限还在。为什么呢？
这是因为mysql数据库中的权限操作时授予和收回的权限级别(priv_level)必须对应，否则无法成功回收。
就上面例子授予jason_grant@'192.168.1.1'用户select权限时是基于jason这样一个库级授予的。那么回收时也必须制定是基于库级回收。如果制定all on *.*,则无法收回jason.*的权限，这正是mysql数据库权限力度分级的特点。
所以如果要让revoke all privileges语句正确执行。就应该明确指定对应的数据库jason例如：
system@(none)> revoke all privileges on jason.* from jason_grant@192.168.1.1;
Query OK, 0 rows affected (0.01 sec)
system@(none)>show grants for jason_grant@192.168.1.1 G
*************************** 1. row ***************************
Grants for jason_grant@192.168.1.1: GRANT USAGE ON *.* TO 'jason_grant'@'192.168.1.1' IDENTIFIED BY PASSWORD '*5F24E62754E18D89BEFCAC099A7AE43B472BE631'
1 row in set (0.00 sec)
可以看到权限已经收回。

收回用户的所有权限不管授予用户是什么权限级别、什么对象。
system@(none)> revoke all,grant option from jason_grant@192.168.1.1;
Query OK, 0 rows affected (0.00 sec)

<>删除用户
mysql数据库中的对象保存并不依赖于用户，而是依赖于库(database)，用户被删除没有任何关系，对象仍在数据库中保存着。这点和oracle用户区别很大。
system@(none)>drop user jason_grant@192.168.1.1,jason_insert@192.168.1.1,jason_ip@192.168.1.1;
Query OK, 0 rows affected (0.01 sec)

<>权限级别
分为：全局、数据库、表、列、程序
提示：user/db/host几个字典表中，host列的值对大小写不敏感。User、Password、Db和Table_name几个列值对大小写敏感
Column_name列值对大小写不敏感。

全局：与全局相关的权限信息记录在mysql.user表中。用来控制用户操作所有数据库的权限（以及管理mysql服务）。
创建一个用户授予它create权限：
system@(none)>grant create on *.* to jason_global;
Query OK, 0 rows affected (0.00 sec)

system@(none)>select * from mysql.user where user='jason_global' G
*************************** 1. row ***************************
Host: %
User: jason_global
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: Y
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_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
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
列值为N表示没有这个权限，只有被授予了全局操作权限mysql.user表中的权限对于列值才是Y，这种情况下该用户就拥有在所连接的mysql服务器下所有数据库中执行相应的操作权限。jason_global授予create权限之后该用户可以查看（不仅能创建还能查看）当前连接的mysql数据库中创建的所有数据库。并能够在任意数据库中创建表对象（information_schema库除外）但是不能删除对象。不能查看。

<>数据库级别的前线主要用于控制账户（user@host）操作某个数据库的权限，在这一粒度对用户做了授权后用户就拥有了该数据库下的所有对象的所有权限。权限信息记录在mysql.db表中。

全局：与全局相关的权限信息记录在mysql.user表中。用来控制用户操作所有数据库的权限（以及管理mysql服务）。若只希望授予用户操作某个数据库的权限，要用mysql.db。mysql.user和mysql.db表结构相差不大。mysql.user有服务级的管理权限。可以说mysql.db是mysql.user表的子集。

创建jason_database用户并授予jason库下创建对象的权限：
system@(none)>grant create on jason.* to jason_database;
Query OK, 0 rows affected (0.00 sec)

查看刚创建的用户jason_database在mysql.user全局权限表中的信息：
system@(none)>select * from mysql.user where user='jason_database' G
*************************** 1. row ***************************
Host: %
User: jason_database
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_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
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
操作权限类型都是N（相当于仅拥有USAGE权限）运行该用户登录mysql数据库。因为查看的是全局权限，而我们授予的是操作jason库的权限，那么操作jason库的权限写在哪里了？看看mysql.db库级权限字典表：
system@(none)>select * from mysql.db where user='jason_database' G
*************************** 1. row ***************************
Host: %
Db: jason
User: jason_database
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: Y
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)

Create_priv: Y 说明我们刚才授予库级create的权限是正确的。
使用jason_database权限登录mysql数据库查看可访问的数据库：
[mysql@linux01 ~]$ mysql -ujason_database
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.6.34-log JASON

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

并不存在的information_schema库类似oracle数据库中的v$*视图、user_*、all_*等字典表。所有成功连接到数据库的用户都可以访问这些对象，无需额外授权。mysql中的information_schema并不是真正的数据库。在操作系统层没有与之对应的物理文件，这个数据库及库中的对象全是由mysql自动维护的一些虚拟对象。这些对象用户能看到却不能改，与oracle中的数据字典表类似。用户查询这些对象中的记录时，看到的都是自己有权限看到的对象。除这个库外mysql中的test库所有可连接的用户都有操作权限。查看mysql.db可知。
mysql> select * from mysql.db where db like 'test%' G;
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
所有test库我们一定要清除。

Host 来源主机
Db 对象所属数据库
User 用户名
Table_name 表对象名称
Grantor 执行权限授予的用户
Timestamp 授予权限的时间
Table_priv 能够授予的表粒度的权限，也就是我们最关注的信息。
Column_priv 能够授予的列粒度的权限

Host+Db+User+Table_name四个维度的共同作用就成就一条权限。粒度比较细。

向jason_tables用户授予users表的全部权限
system@(none)>grant all on jason.user to jason_tables;
Query OK, 0 rows affected (0.00 sec)

system@(none)>select * from mysql.tables_priv where user='jason_tables' and table_name='user' G
*************************** 1. row ***************************
Host: %
Db: jason
User: jason_tables
Table_name: user
Grantor: system@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv:
1 row in set (0.00 sec)

总之Column_priv列在声明表级权限时没用但在授予列级权限时就有反应了

<>列级权限保存在mysql.columns_priv字典中
system@(none)>desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)
列级权限需要Host+Db+User+Table_name+Column_name五个粒度
Select 查询权限
Insert 插入权限
Update 修改权限
Reference 尚未应用

授予jason_cols用户查询jason.users表phoneno列的权限：
system@(none)> grant select (phoneno) on jason.users to jason_cols;
Query OK, 0 rows affected (0.00 sec)

对同一个表对象再授予另一个权限，看看字典表如何存储
system@(none)> grant insert (address) on jason.users to jason_cols;
Query OK, 0 rows affected (0.00 sec)

对比两个字典表可以发现table_priv只记录表级粗粒度的记录，columns_priv才是决定列级权限粒度的核心。

[mysql@linux01 ~]$ mysql -ujason_cols
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.6.34-log JASON

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

jason_cols@(none)>use jason
Database changed
jason_cols@jason>show tables;
+-----------------+
| Tables_in_jason |
+-----------------+
| users |
+-----------------+
1 row in set (0.00 sec)

查看当前用户所拥有的权限
jason_cols@jason> show grants;
+---------------------------------------------------------------------------------+
| Grants for jason_cols@% |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jason_cols'@'%' |
| GRANT SELECT (phoneno), INSERT (address) ON `jason`.`users` TO 'jason_cols'@'%' |
+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

<>程序，mysql中的程序（routine）主要指procedure和function两类对象。对于已存在的procedure和function dba可以对用户授予执行（execute）修改（alter routine）授予（grant）这部分权限体现在mysql.procs_priv表中。

system@(none)>desc mysql.procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | char(77) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)