SQL创建新用户并授权查询、删除和更新权限。

1.查看当前数据库用户和数据库版本。

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| joycheng         | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

2. 创建本地数据库新用户。

mysql> create user joycheng@'%' identified by 'chengjian33';
Query OK, 0 rows affected (0.43 sec)

3.删除不需要的本地用户。

mysql> drop user joycheng@'localhost';
Query OK, 0 rows affected (0.03 sec)

4.刷新数据库

mysql> flush privileges;
Query OK, 0 rows affected (0.44 sec)

5.重新查看已存在的数据库用户。

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| joycheng         | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

6. 显示新用户所拥有的默认权限。

mysql> show grants for joycheng;
+--------------------------------------+
| Grants for joycheng@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `joycheng`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)