MySQL 用户授权管理

1.授权语法

GRANT 权限 ON 权限级别 TO 用户                             #其中英文大写为MySQL 语法，汉字为需要填写

                                                             存储文件
*.* ----全库级别      ---> 管理员                               mySQL.user
test.* ---   单库级别  -----> 业务层面                          mySQL.db
test.t1 ------ 单表级别                                        mySQL.table_priv
select(id,name)---columns  ----进行单表列进行授权（用的少）       mySQL.columns_priv

#权限列表   
mySQL> show privileges;
| Privilege                  | Context                               | Comment                       
| Alter                      | Tables                                | To alter the table             
| Alter routine              | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                     | Databases,Tables,Indexes              | To create new databases and tables
| Create routine             | Databases                             | To use CREATE FUNCTION/PROCEDURE       
| Create role                | Server Admin                          | To create new roles           
| Create temporary tables    | Databases                             | To use CREATE TEMPORARY TABLE 
| Create view                | Tables                                | To create new views           
| Create user                | Server Admin                          | To create new users           
| Delete                     | Tables                                | To delete existing rows       
| Drop                       | Databases,Tables                      | To drop databases, tables, and views               
| Drop role                  | Server Admin                          | To drop roles                 
| Event                      | Server Admin                          | To create, alter, drop and execute events           
| Execute                    | Functions,Procedures                  | To execute stored routines     
| File                       | File access on server                 | To read and write files on the server                 |
| Grant option               | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                      | Tables                                | To create or drop indexes     
| Insert                     | Tables                                | To insert data into tables     
| Lock tables                | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                    | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                      | Server Admin                          | To make proxy user possible   
| References                 | Databases,Tables                      | To have references on tables   
| Reload                     | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client         | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave          | Server Admin                          | To read binary log events from the master             |
| Select                     | Tables                                | To retrieve rows from table   
| Show databases             | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                  | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                   | Server Admin                          | To shut down the server       
| Super                      | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                    | Tables                                | To use triggers               
| Create tablespace          | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                     | Tables                                | To update existing rows       
| Usage                      | Server Admin                          | No privileges - allow connect only                    |
| XA_RECOVER_ADMIN           | Server Admin                          |                               
| SHOW_ROUTINE               | Server Admin                          |                               
| RESOURCE_GROUP_USER        | Server Admin                          |                               
| REPLICATION_APPLIER        | Server Admin                          |                               
| INNODB_REDO_LOG_ENABLE     | Server Admin                          |                               
| GROUP_REPLICATION_ADMIN    | Server Admin                          |                               
| FLUSH_USER_RESOURCES       | Server Admin                          |                               
| PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                               
| ROLE_ADMIN                 | Server Admin                          |                               
| BACKUP_ADMIN               | Server Admin                          |                               
| CONNECTION_ADMIN           | Server Admin                          |                               
| SET_USER_ID                | Server Admin                          |                               
| SESSION_VARIABLES_ADMIN    | Server Admin                          |                               
| RESOURCE_GROUP_ADMIN       | Server Admin                          |                               
| INNODB_REDO_LOG_ARCHIVE    | Server Admin                          |                               
| BINLOG_ENCRYPTION_ADMIN    | Server Admin                          |                               
| REPLICATION_SLAVE_ADMIN    | Server Admin                          |                               
| SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                               
| SYSTEM_USER                | Server Admin                          |                               
| APPLICATION_PASSWORD_ADMIN | Server Admin                          |                               
| TABLE_ENCRYPTION_ADMIN     | Server Admin                          |                               
| SERVICE_CONNECTION_ADMIN   | Server Admin                          |                               
| AUDIT_ADMIN                | Server Admin                          |                               
| BINLOG_ADMIN               | Server Admin                          |                               
| ENCRYPTION_KEY_ADMIN       | Server Admin                          |                               
| CLONE_ADMIN                | Server Admin                          |                               
| FLUSH_OPTIMIZER_COSTS      | Server Admin                          |                               
| FLUSH_STATUS               | Server Admin                          |                               
| FLUSH_TABLES               | Server Admin                          |   

#生产库：
管理员：
ALL 以上权限中不包含 Grant option

2.普通权限授权

mySQL> grant all on *.* to test@'10.0.0.%' ;
mySQL> grant select ,update ,delete ,insert on *.* to lss@'10.0.0.%' ;
mySQL> grant select ,update ,delete ,insert on test.* to test@'10.0.0.%';
mySQL> grant select(id) on test.t1 to user1@'10.0.0.%';

3.角色创建及授权

#角色，如果要用角色需要启用才可以，默认是关闭状态，修改系统变量activate_all_roles_on_login,默认为OFF，或用set default role 来激活角色。
mySQL> help  SET DEFAULT ROLE
Syntax:
SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...

#创建角色
mysql> create role dev@'10.0.0.%';
Query OK, 0 rows affected (0.04 sec)

#给角色授权  角色不可登录，无密码
mysql> grant select on *.* to dev@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

#将角色授权给用户
mysql> grant dev@'10.0.0.%' to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

#查询角色
mysql> select * from mysql.role_edges;
+-----------+-----------+----------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST  | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+----------+---------+-------------------+
| 10.0.0.%  | dev       | 10.0.0.% | test    | N                 |
+-----------+-----------+----------+---------+-------------------+
1 row in set (0.00 sec)

#通过表来查询相关用户权限
select * from information_schema.user_privileges;

#授权管理员用户
 #创建远程登录用户
mySQL> create user test@'10.0.0.%' identified with mySQL_native_password by '123';
Query OK, 0 rows affected (0.03 sec)
 #创建本地登录用户
mySQL> create user test@'localhost' identified with mySQL_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
 #授权给用户
mySQL> grant all on *.* to test@'10.0.0.%' ;
Query OK, 0 rows affected (0.00 sec)
 
mySQL> grant all on *.* to test@'localhost' ;
Query OK, 0 rows affected (0.01 sec)

#创建开发用户
mySQL> create user dev_user1@'10.0.0.%' identified with mySQL_native_password by '123';

grant create ,alter,create view,show databases ,show views ,update ,delete ,insert on dev_db.* to dev_user1@'10.0.0.%';

#创建复制用户
mySQL> #  创建主从复制相关用户
mySQL> #  repl@'10.0.0.%' ,复制用户
mySQL> create user repl@'10.0.0.%' identified with mySQL_native_password by '123';
Query OK, 0 rows affected (0.01 sec)

mySQL> grant replication slave,replication client on *.* to repl@'10.0.0.%';
Query OK, 0 rows affected (0.02 sec)

4.生产中用户类型规范

管理员 : ALL  (除Grant option   “To give to other users those privileges you possess ”)
开发 : Create ,Create routine,Create temporary tables,Create view,Delete ,Event,Execute,Insert ,References,Select,Show databases ,Show view ,Trigger,Update
监控 : select , replication slave , client supper
备份 : ALL
主从 : replication slave
业务 : insert , update , delete ,select

5.查看权限

show grants for test@'10.0.0.%' ;
+-----------------------------------------+
| Grants for test@10.0.0.%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `test`@`10.0.0.%` |
+-----------------------------------------+
1 row in set (0.01 sec)

#通过表来查询权限信息
select * from mysql.user where user='test'; #只能查到用户信息，没有权限信息，需要查mysql.db

select * from mysql.db where user='test';

6.回收权限

mySQL> revoke delete on *.* from lss@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

mySQL> show grants for lsso@'10.0.0.%';

mySQL> revoke select(id) on test.t1 from user1@'10.0.0.%';