MySql 8.0.19 权限分配

phpmaster 2020-08-18 PM 245℃ 0条

quanxian.jpg
问题一:You have an error in your SQL syntax ---语法问题
问题二:You are not allowed to create a user with GRANT ---权限问题
问题三:No database selected ---没选择数据库

创建数据库分配权限演示
问题一:You have an error in your SQL syntax —语法问题
MySql8.0.19 版本分配权限这有了一些改变,不需要后面的identified by '123456a'了

mysql> grant all privileges on sonar_scan.* to 'sonar'@'%' identified by '123456
a';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ident
ified by '123456a'' at line 1

分配权限

mysql> grant all privileges on sonar_scan.* to 'sonar'@'%';
Query OK, 0 rows affected (0.24 sec)

问题二:You are not allowed to create a user with GRANT —权限问题
分配权限,% 代表全域,如果要远程使用数据库的话必须分配这个权限。

mysql> grant all privileges on sonar_scan.* to 'sonar'@'%';
Query OK, 0 rows affected (0.24 sec)

如果分配了全域,这个时候再分配本地就会报错,其实 % 已经包含 localhost 了。

mysql> grant all privileges on sonar_scan.* to 'sonar'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

如果硬要是分配本地的话,要执这么一句就好了。要换回全域,在把 host 改成 % 就好了。

mysql>  update user set host='localhost' where user='sonar';
Query OK, 1 row affected (0.53 sec)
Rows matched: 1  Changed: 1  Warnings: 0

问题三:No database selected —没选择数据库
用一句 use mysql 就能解决问题了。

mysql> update user set host='localhost' where user='sonar';
ERROR 1046 (3D000): No database selected
mysql> use mysql;
Database changed
mysql>  update user set host='localhost' where user='sonar';
Query OK, 1 row affected (0.53 sec)
Rows matched: 1  Changed: 1  Warnings: 0

创建数据库分配权限演示
创建数据库

mysql> create database sonar_scan default character set utf8 collate utf8_genera
l_ci;
Query OK, 1 row affected, 2 warnings (0.19 sec)

创建用户

mysql> create user 'sonar' identified by '123456a';
Query OK, 0 rows affected (0.11 sec)

分配权限

mysql> grant all privileges on sonar_scan.* to 'sonar'@'%';

Query OK, 0 rows affected (0.24 sec)
标签: mysql, 权限

非特殊说明,本博所有文章均为博主原创。

评论啦~


点击这里给我发消息
召唤看板娘