MySQL grant授权命令使用方法

参考链接

（1）为mysql数据备份建立最小权限的用户
（2）mysql grant 用户权限总结
（3）MySQL授权命令grant的使用方法
（4）MySQL之权限管理

1.1.用户权限管理主要有以下作用

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

  1. grant 权限 on 数据库对象.表名 to '用户'@'登录IP或域名' identified by '用户登录密码' (with grant option)
  2. -- 添加权限，和已有的权限合并，不会覆盖已有权限
  3. -- with grant option 表示允许用户将自己的权限授权给其他用户
  5. flush privileges;
  6. -- 对用户做出权限变更之后，一定要记得重新加载权限，将权限信息从内存中写入数据库。

1.2.相关权限命令

一、grant普通数据用户，查询、插入、更新、删除数据库中所有表数据的权限

  1. grant select on testdb.* to common_user@'%';
  2. grant insert on testdb.* to common_user@'%';
  3. grant update on testdb.* to common_user@'%';
  4. grant delete on testdb.* to common_user@'%';
  5. 或
  6. grant select, insert, update, delete on testdb.* to common_user@'%';

二、grant数据库开发人员，创建表、索引、视图、存储过程、函数…等权限

  1. //grant 创建、修改、删除 MySQL 数据表结构权限。
  2. grant create,alter,drop on testdb.* to developer@'192.168.0.%';
  4. //grant 操作MySQL外键权限
  5. grant references on testdb.* to developer@'192.168.0.%';
  7. //grant 操作 MySQL 临时表权限
  8. grant create temporary tables on testdb.* to developer@'192.168.0.%';
  10. //grant 操作 MySQL 索引权限。
  11. grant index on testdb.* to developer@'192.168.0.%';
  13. //grant 操作 MySQL 视图、查看视图源代码 权限。
  14. grant create view on testdb.* to developer@'192.168.0.%';
  15. grant show view on testdb.* to developer@'192.168.0.%';
  17. //grant 操作 MySQL 存储过程、函数 权限。
  18. grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
  19. grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
  20. grant execute on testdb.* to developer@'192.168.0.%';

三、grant普通DBA管理某个MySQL数据库的权限。

  1. grant all privileges on testdb to dba@'localhost';

四、grant高级DBA管理MySQL中所有数据库权限

  1. grant all on *.* to dba@'localhost';

五、MySQL grant权限，分别作用在多个层次上。

  1. //1.grant 作用在整个 MySQL 服务器上
  2. grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
  3. grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
  5. //2.grant 作用在单个数据库上
  6. grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
  8. //3.grant 作用在单个数据表上
  9. grant select, insert, update, delete on testdb.orders to dba@localhost;
  11. //4.在给一个用户授权多张表时，可以多次执行以下语句。例如：
  12. grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
  13. grant select on smp.mo_sms to mo_user@'%' identified by '123345';
  15. //5.grant 作用在表中的列上
  16. grant select(id, se, rank) on testdb.apache_log to dba@localhost;
  18. //6.grant 作用在存储过程、函数
  19. grant execute on procedure testdb.pr_add to 'dba'@'localhost'
  20. grant execute on function testdb.fn_add to 'dba'@'localhost'

六、查看MySQL用户权限

查看当前用户（自己）权限

  1. show grants;

查看其他MySQL用户权限

  1. show grants for dba@localhost;

七、撤销已经赋予给MySQL用户权限的权限

  1. revoke all on *.* from dba@localhost;

八、MySQL grant、revoke用户权限注意事项

1、grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。
2、如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

  1. grant select on testdb.* to dba@localhost with grant option;

九、为mysql数据备份建立最小权限

  1. 1、对于table，mysqldump最少要有select权限；
  2. 2、如果要产生一份一致的备份，mysqldump要有lock table权限；
  3. 3、对于view,mysqldump 要有show view权限；
  4. 4、对于trigger，mysqldump要有trigger权限；
  5. 5、对于event，mysqldump要有event权限。
  7. grant select,lock tables,show view, trigger,event on database.* on 'backup'@'localhost';

1.3.相关命令操作

一、用户重命名

  1. rename user 'test1'@'%' to 'test2'@'%';

二、删除用户

  1. drop user 'test'@'%';

三、修改密码

  1. // 修改密码
  2. // 1、更新mysql.user表
  3. use mysql;
  4. # mysql5.7以前
  5. update user set password('123456') where user='root';
  6. # mysql5.7以后
  7. update user set authentication_string=password('123456') where user='root';
  8. flush privileges;
  10. //2、用set password命令
  11. set password for '用户名'@'登录地址'=password('密码');
  12. set password for 'root'@'localhost'=password('123456');
  14. //3、mysqladmin
  15. mysqladmin -u用户名 -p旧密码 password 新密码
  16. mysqladmin -uroot -p123456 password 1234abcd
  17. -- 注意：mysqladmin位于mysql安装目录的bin目录下。

四、忘记密码

  1. //1、添加登录跳过权限检查配置
  2. 修改my.cnf,在mysqld配置节点添加skip=grant-tables配置
  3. [mysqld]
  4. skip-grant-tables
  6. //2、重新启动mysql服务
  7. service mysqld restart
  9. //3、修改密码
  10. 此时在终端可以不需要账号密码登录mysql。
  11. use mysql;
  12. # mysql5.7以前
  13. update user set password('123456') where user='root';
  14. # mysql5.7以后
  15. update user set authentication_string=password('123456') where user='root';
  16. flush privileges;
  18. //4、还原登录权限跳过检查配置
  19. 将my.cnf中得mysqld节点的skip-grant—tables配置删除，然后重新启动mysql服务。

1.4 mysql权限介绍

一、权限表
MySQL数据库中的3个权限表： user、db、host

权限表的存取过程：

（1） 先从user表中得host、user、password这3个字段判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证。
（2） 通过权限验证，进行权限分配时，按照user、db、tables_priv、columns_priv的顺序进行分配。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv,columns_priv;如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，依次列推。

二、MySQL各种权限

权限 作用域 说明
usage server administration（服务器管理） 1、连接（登录权限），建立一个用户，就会自动授权usage权限。
2、该权限智能用户数据库登录，不能执行任何操作；且usage权限不能被回收，即REVOKE用户并不能删除用户。
create user server administration 1、要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
show datbase server adminstration 1、 通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。
reload server administration 1、 必须拥有reload权限，才可以执行flush [tables | logs | privileges]
replication client server administration 1、 拥有此权限可以查询master server、slave server状态。
show master status;
replication slave server administration 1、 拥有此权限可以查看从服务器，从主服务器读取二进制日志。
shutdown server administration 1、 关闭MySQL
super server administration 1、 这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。
process server administration 1、 通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。
all privileges server administration 1、全部权限
create databases,tables or indexes 1、必须要有create的权限，才可以使用create table。
drop databases,tables or views 1、 必须有drop的权限，才可以使用drop database db_name; drop table tab_name;
references databases or tables 1、 有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。
lock tables databases 1、 必须拥有lock tables权限，才可以使用lock tables
grant option databases,tables or stroed routines 1、 拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）
select tables or columns 1、必须要有select的权限，才可以使用select table。
create temporary tables tables 1、 必须有create temporary tables的权限，才可以使用create temporary tables。
2、创建临时表权限
delete tables 1、 必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)
index tables 1、 必须拥有index权限，才能执行[create |drop] index
alter tables 1、 必须有alter的权限，才可以使用alter table
insert tables or columns 1、 必须有insert的权限，才可以使用insert into ….. values….
update tables or columns 1、 必须有update的权限，才可以使用update table
create routine stored routines 1、 必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}。
2、 当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：
show view views 1、 必须拥有show view权限，才能执行show create view。
create view view 1、 必须有create view的权限，才可以使用create view
excute stored routines（存储过程） 1、执行存在的Functions（函数）,Procedures（存储过程）
create routine stored routines 1、创建存储过程权限
alter routine stored routines 1、 必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}
file file access on server host 1、 拥有file权限才可以执行 select ..into outfile和load data infile…操作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。
2、创建文件访问权限

权限分布

权限分布 可能的设置权限
表权限 ‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’
列权限 ‘Select’, ‘Insert’, ‘Update’, ‘References’
过程权限 ‘Execute’, ‘Alter Routine’, ‘Grant’

权限设置原则

权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：
1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
4、为每个用户设置满足密码复杂度的密码。
5、定期清理不需要的用户。回收权限或者删除用户。