MySQL创建及授权账号

用户的定义
用户名@‘白名单’

wordpress@’%’
wordpress@‘localhost’
wordpress@‘127.0.0.1’
wordpress@‘10.0.0.%’
wordpress@‘10.0.0.5%’
wordpress@‘10.0.0.0/255.255.254.0’
wordpress@‘10.0.%’

grant all on *.* to wen@"localhost" identified by "111111";

grant 授权命令(8.0版本之前可以自动创建用户并授权,8.0必须先创建在授权)
all 对应权限
on *.* 目标：库和表
to wen@“localhost” 用户名和客户端主机
identified by “111111” 用户密码

操作案例：创建master用户对test库具备所有权限，允许从localhost主机登录管理数据库，密码为master123
mysql> grant all on test.* to master@“localhost” identified by “master123”;
mysql> flush privileges;
授权后查看用户
mysql> select user,host from mysql.user;
查看授权用户权限
mysql> show grants for master@localhost;

额外添加管理员账号

grant all privileges on *.* to gao@"localhost" identified by '111111' with grant option;

with grant option 这个参数表示gao这个用户有授权权限
mysql> flush privileges;
mysql> select user,host from mysql.user;
±-----±----------+
| user | host |
±-----±----------+
| root | 127.0.0.1 |
| gao | localhost |
| root | localhost |

撤销用户授权 revoke
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

1 grant  all on *.* to dba@localhost;  
2 revoke all on *.* from dba@localhost;
3 mysql> revoke delete on *.* from dba@localhost;  撤销dab账号delete权限
4 mysql> revoke grant option on *.* dba@localhost;   #撤销授权权限

授权用户登录收修改自己的密码
set password=password(“新密码”)
数据库管理员重置授权用户密码
set password for 用户名@“ip”=password(“新密码”)

删除远程登录地址为10.125.192.6上面用户为root的用户，使其不能远程登录数据库服务器
mysql> delete from mysql.user
-> where
->user=“root” and host=“10.125.192.6”;
Query OK, 2 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

单独创建用户不授权
mysql> create user gao@localhost identified by ‘123’;

修改账号密码
alter user peng@’%’ identified by ‘123’;

删除账号
drop user gao@‘localhost’;

授权

授权所有库所有表
grant all on . to dba@‘localhost’;

授权单库所有表
grant all on mysql.* to dba@‘localhost’;

授权单库单表
grant all on mysql.user to dba@‘localhost’;

授权单库单表单列(只能查询表中指定的一列)
grant select(user) on mysql.user to dba@‘localhost’ identified by ‘123’;

注:如果需要授权多列用逗号分隔select(user,host,password)