1.授权语法
1 GRANT 权限 ON 权限级别 TO 用户 #其中英文大写为MySQL 语法，汉字为需要填写 2 3 存储文件 4 *.* ----全库级别 ---> 管理员 mySQL.user 5 test.* --- 单库级别 -----> 业务层面 mySQL.db 6 test.t1 ------ 单表级别 mySQL.table_priv 7 select(id,name)---columns ----进行单表列进行授权（用的少） mySQL.columns_priv 8 9 #权限列表 10 mySQL> show privileges; 11 | Privilege | Context | Comment 12 | Alter | Tables | To alter the table 13 | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | 14 | Create | Databases,Tables,Indexes | To create new databases and tables 15 | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE 16 | Create role | Server Admin | To create new roles 17 | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE 18 | Create view | Tables | To create new views 19 | Create user | Server Admin | To create new users 20 | Delete | Tables | To delete existing rows 21 | Drop | Databases,Tables | To drop databases, tables, and views 22 | Drop role | Server Admin | To drop roles 23 | Event | Server Admin | To create, alter, drop and execute events 24 | Execute | Functions,Procedures | To execute stored routines 25 | File | File access on server | To read and write files on the server | 26 | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | 27 | Index | Tables | To create or drop indexes 28 | Insert | Tables | To insert data into tables 29 | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | 30 | Process | Server Admin | To view the plain text of currently executing queries | 31 | Proxy | Server Admin | To make proxy user possible 32 | References | Databases,Tables | To have references on tables 33 | Reload | Server Admin | To reload or refresh tables, logs and privileges | 34 | Replication client | Server Admin | To ask where the slave or master servers are | 35 | Replication slave | Server Admin | To read binary log events from the master | 36 | Select | Tables | To retrieve rows from table 37 | Show databases | Server Admin | To see all databases with SHOW DATABASES | 38 | Show view | Tables | To see views with SHOW CREATE VIEW | 39 | Shutdown | Server Admin | To shut down the server 40 | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | 41 | Trigger | Tables | To use triggers 42 | Create tablespace | Server Admin | To create/alter/drop tablespaces | 43 | Update | Tables | To update existing rows 44 | Usage | Server Admin | No privileges - allow connect only | 45 | XA_RECOVER_ADMIN | Server Admin | 46 | SHOW_ROUTINE | Server Admin | 47 | RESOURCE_GROUP_USER | Server Admin | 48 | REPLICATION_APPLIER | Server Admin | 49 | INNODB_REDO_LOG_ENABLE | Server Admin | 50 | GROUP_REPLICATION_ADMIN | Server Admin | 51 | FLUSH_USER_RESOURCES | Server Admin | 52 | PERSIST_RO_VARIABLES_ADMIN | Server Admin | 53 | ROLE_ADMIN | Server Admin | 54 | BACKUP_ADMIN | Server Admin | 55 | CONNECTION_ADMIN | Server Admin | 56 | SET_USER_ID | Server Admin | 57 | SESSION_VARIABLES_ADMIN | Server Admin | 58 | RESOURCE_GROUP_ADMIN | Server Admin | 59 | INNODB_REDO_LOG_ARCHIVE | Server Admin | 60 | BINLOG_ENCRYPTION_ADMIN | Server Admin | 61 | REPLICATION_SLAVE_ADMIN | Server Admin | 62 | SYSTEM_VARIABLES_ADMIN | Server Admin | 63 | SYSTEM_USER | Server Admin | 64 | APPLICATION_PASSWORD_ADMIN | Server Admin | 65 | TABLE_ENCRYPTION_ADMIN | Server Admin | 66 | SERVICE_CONNECTION_ADMIN | Server Admin | 67 | AUDIT_ADMIN | Server Admin | 68 | BINLOG_ADMIN | Server Admin | 69 | ENCRYPTION_KEY_ADMIN | Server Admin | 70 | CLONE_ADMIN | Server Admin | 71 | FLUSH_OPTIMIZER_COSTS | Server Admin | 72 | FLUSH_STATUS | Server Admin | 73 | FLUSH_TABLES | Server Admin | 74 75 #生产库： 76 管理员： 77 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.%';
