MySQL权限总共29个
ALTER, CREATE VIEW, DELETE, DROP, GRANT, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER,UPDATE,CREATE, EVENT, LOCK TABLES, REFERENCES,ALTER ROUTINE, CREATE ROUTINE, EXECUTE,CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN,SUPER
权限级别 | 可设置的设置权限 |
表权限 | ALTER, CREATE VIEW,, DELETE, DROP, GRANT, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER,UPDATE |
数据库权限 | CREATE, DROP, EVENT, GRANT, LOCK TABLES, REFERENCES |
存储过程权限 | ALTER ROUTINE, CREATE ROUTINE, EXECUTE |
全局权限 | CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, SUPER |
授权用户流程
1、创建用户
CREATE USER 用户@'用户地址IP'IDENTIFIED BY '密码';
例如：CREATE USER 'user1'@'192.168.23.1' IDENTIFIED BY '123456';
2、收回所有权限
REVOKE ALL PRIVILEGES ON 数据库名称.表名称 FROM '用户';
例如：REVOKE ALL PRIVILEGES ON *.* FROM 'user1';
3、赋予权限
grant 权限1,权限2,…权限n on 数据库名称.表名称 to '用户名'@'用户地址IP' ;
例如：grant select , insert, update, create, drop on vtdc.employee to 'user1'@'192.168.23.1';