mysql 用户和权限管理

[BruceOuyang]

follow: https://www.cnblogs.com/keme/p/10288168.html

[BruceOuyang]

赋予用户权限信息

语法

grant [options] on [database].[table] to [user]@[host] identified by [password] [with grant option]
flush privileges;

• options 可选项：SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE 多个用逗号分隔
• database 数据库名称
• table 数据库表名称
• user 用户名
• host 主机地址，指定只允许用户在某台机器登录，% 表示不限制主机登录
• password 用户密码
• with grant option 是否允许用户拥有授权功能

示例
给 root 用户添加所有数据库权限，且可以授权

grant all privileges on *.* to root@'%' identified by 'root' with grant option;
flush privileges;

flush privileges 为刷新mysql内存，赋予权限需刷新内存后生效

查看用户权限信息

查看 mysql 有哪些用户

select user, host from mysql.user;

查看已授权给用户的权限信息
语法

show grants for [user]@[host]

示例

show grants for root@'localhost';

收回用户权限信息

语法

revoke [options] on [database].[table] from [user]@[host]

示例
收回 root 用户的 drop 权限

revoke drop on *.* from root@'%';

收回用户所有权限

revoke all privileges, grant option from [user]@[host];

[BruceOuyang]

用户管理

创建用户
推荐 create user / grant 命令

create user 'test'@'localhost' identified by '123456';

grant  SELECT,INSERT,UPDATE,DELETE,CREATE on [database].[table] to test@'localhost' identified by '123456'

flush privileges;

查看用户

select * from mysql.user;

删除用户

drop user test@'localhost'

修改用户密码 推荐 alter user 和 set password

alter user test@'localhost' identified by 'newpwd';

set password for test@'localhost' = password('newpwd');

修改当前用户密码

alter user user() identified by 'newpwd';
set password = password('newpwd');

[BruceOuyang]

特别补充，高危权限把控

一般普通用户权限推荐设置

select,insert,alter,update,delete,create,execute

高危权限，尽量控制在管理员范围

drop, file, process, super