MySQL数据库中的权限体系

efn阿克说

MySQL数据库验证权限有3个维度：我是谁、从哪里来、到那里去
哲学家探究人生的终极命题
 
能不能连接：
在MySQL数据库中验证用户，需要检查3项值：用户名、用户密码和来源主机。分别对应mysql库中的user表的user、password和host三列
 
能不能执行操作：
连接到数据库之后，能不能执行操作，比如说建库、建表、改表、查询或修改数据等，除了mysql.user外，还有mysql.db，mysql.tables_priv，mysql.columns_priv，mysql.proc_priv
 
权限变更何时生效：
如果是通过GRANT、REVOKE、SET PASSWORD、RENAME USER等MySQL提供的命令执行修改，那么权限将马上生效
如果是手动修改字典表方式(INSERT、UPDATE、DELETE)达到变更用户权限的目的，要重启MySQL服务才能生效
 
创建用户：
MySQL对用户名和密码是大小写敏感的
(system@localhost) [mysql]> create user jss;
(system@localhost) [mysql]> select user,host,password from mysql.user where user='hugc';
如果user字典表中的host列值为空或%，均代表任意主机
 
在MySQL数据库中，用户和主机(‘user’@’host’)组成一个唯一账户
(system@localhost) [mysql]> create user hugc@’192.168.1.201’ identified by ‘hugc’;
(system@localhost) [mysql]> create user hugc@’192.168.1.%’ identified by ‘hugc’;
 
修改用户密码：
(hugc@localhost) [(none)]> set password for jss=password('hugc');
 
将用户密码设置为过期：
(system@localhost) [mysql]> alter user hugc password expire;
 
GRANT方式创建用户：
(system@localhost) [mysql]> grant select on test.* to hugc_test@’192.168.1.201’ identified by ‘hugc’;
(system@localhost) [mysql]> select user,host,password from mysql.user;
 
修改数据字典方式创建用户：
(system@localhost) [mysql]> insert into mysql.user (host,user,password) values (‘192.168.1.201’,’test_test’,password(‘123456’));
(system@localhost) [mysql]> update mysql.user set password=password(‘1234567’) where user=’test_test’;
 
授予权限：
priv_type
object_type
priv_level
ssl_option
with_option
(system@localhost) [mysql]> grant select on mysql.user to hugc;
 
查看用户权限：
(system@localhost) [mysql]> show grants for hugc;
 
收回用户权限：
(system@localhost) [mysql]> revoke select on mysql.user from hugc;
MySQL数据库中的权限，操作时授予和收回是的权限级别(priv_level)必须对应，否则无法成功回收
(system@localhost) [mysql]> show grants for hugc;
收回用户所有权限，不管授予用户的是什么权限级别，什么对象的什么权限，直接将用户恢复至裸身(USAGE)状态
(system@localhost) [mysql]> revoke all,grant option from hugc;
(system@localhost) [mysql]> show grants for hugc;
USAGE权限意指没有权限(no privileges)，这个权限用户一经创建就会拥有，并且无法通过REVOKE语句收回
 
删除用户：
(system@localhost) [mysql]> drop user hugc;
MySQL数据库中的对象保存并不是依赖于用户，而是依赖于库(database)，用户被删除没有任何关系，对象仍在，好好的保存在存储它的数据库中
 
权限级别：
MySQL数据库权限从大的粒度可以分为5类：全局、数据库、表、列、程序。可以精确的为某个用户分配从某台机器连接进来访问某个数据库下某个表的某个列的某部分记录权限。
 
全局权限：
与全局权限相关的权限信息记在mysql.user表中
(system@localhost) [mysql]> grant create on *.* to hugc;
授予了在任何库下创建任何对象的权限，没有select权限，没有update权限，没有delete权限
(system@localhost) [mysql]> select * from mysql.user where user=’ hugc’;
 
数据库级别权限：
主要用于控制账户(‘user’@’host’)操作某个数据库的权限，数据库级别的权限信息记录在mysql.db表中
(system@localhost) [mysql]> grant create on hugcdb.* to hugc;
(system@localhost) [mysql]> select * from mysql.db where user=’ hugc’;
进行验证
(jss@localhost) [mysql]>show databases;
 
并不存在的information_schema库，用户不能对information_schema数据中的对象做授权。这个数据库及库中的对象全是由MySQL自动维护的一系列虚拟对象
 
表级权限：
表对象的授权信息保存在mysql.tables_priv字典表中
(system@localhost) [mysql]> grant all on hugcdb.users to hugc;
 
列级权限：
是MySQL权限体系中的最细粒度，保存在mysql.columns_priv，属于权限认证体系中的高精尖武器。通过对表中列的授权，可以实现只允许从某主机来的某用户访问某库的某表的某列。
(system@localhost) [mysql]> grant select (phoneno) on hugcdb.users to hugc;
(system@localhost) [mysql]> select * from mysql.columns_priv;
(system@localhost) [mysql]> select * from mysql.table_priv where user=’hugc’;
tables_priv只是表级粗粒度的记录，columns_priv才是决定列级权限粒度的核心
尽管通过查看表结构，或者使用select语句查询表数据时只能查到被授予权限的列，但是，该用户查询information_schema.tables或其他相关字典表时，看到的表的信息仍然是完整的，比如表的大小、索引大小、平均列长度等，这也是information_schema库比较特殊的另一个体现
 
程序：
MySQL中的程序(ROUTINE)主要是指procedure和function两类对象，在mysql.procs_priv表中。拥有create routine权限的用户能够创建procedure、function对象。这个权限是用户/库一级权限，而execute、alter routine、grant这3个权限则是对象级，都是针对某个指定的procedure、function做授权
 
用户与权限设定原则：
最终确定以库为单位创建账户，在达到安全设定目标的前提下，尽可能简化流程，将权限级别设为3级：主要用于应用端的业务
{user}_oper：定义为操作用户，拥有指定库下所有对象的操作权限，授予增加(insert)，删除(delete)，修改(update)，查询(select)记录的权限，主要用于前端应用程序，连接数据库读写数据。
{user}_read：定义为只读用户，拥有指定库下对象的读取权限，授予查询记录的权限，可用于数据查询、SQL调试、数据验证、数据导出等操作，对于做了多谢分离的应用，只读访问也使用本账户。
{user}_mgr：定义为管理账户，拥有多个库下对象操作权限，用于各项目负责人实时操作对象数据。
所有系统维护账户两个原则：
MySQL数据库的管理员账户改名，不允许出现root名称的用户
　　
用户访问域设定为服务器所在IP段