mysql 5.7.31 创建账号并赋予权限

前言

• mysql 5.7.31

mysql创建账号并赋予权限

为新的数据库flowdb，创建账户testdba。将flow库的所有权限赋予testdba账户。

操作步骤

1. 创建testdba账户

mysql> use mysql;
Database changed
mysql> create user 'testdba'@'%' identified  by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string from user;
+-------------------------+---------+-------------------------------------------                                    +
| host                    | user    | authentication_string                                                         |
+-------------------------+---------+-------------------------------------------                                    +
| localhost               | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                                     |
| izm5e0k0bexacam32zw837z | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                                     |
| 127.0.0.1               | root    |                                                                               |
| ::1                     | root    |                                                                               |
| localhost               |         |                                                                               |
| izm5e0k0bexacam32zw837z |         |                                                                               |
| %                       | testdba | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                                     |
+-------------------------+---------+-------------------------------------------                                    +
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2. 授权testdba账户

# 将flow数据库所有权限赋给testdba用户
mysql> grant all privileges on `test`.* to 'testdba'@'%';
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6

3. 查看testdba账户权限

查看赋权结果：

mysql> show grants FOR 'testdba'@'%';
mysql> use mysql;
# 表级的用户权限
mysql> select * from `db` where `user`='testdba';
# 系统级的用户权限
mysql> select * from `user` where user='testdba';
1
2
3
4
5
6

4. 其它命令：

mysql> use mysql;
# 表级的用户权限
mysql> select `host`,`user`, `db` from `db`;
# 系统级的用户权限
mysql> select `host`,`user` from `user`;
1
2
3
4
5

取消授权

取消某个数据库的授权

mysql> revoke all privileges on test.* from 'testdba'@'%';
mysql> show grants for 'testdba'@'%';
1
2

取消所有的授权

mysql> revoke all privileges on *.* from 'testdba'@'%';
mysql> show grants for 'testdba'@'%';
1
2

删除用户

mysql5.6 删除用户/ drop user

参考

https://www.cnblogs.com/anzerong2012/p/10738186.html
mysql 5.6.47 创建账号并赋予权限