MySQL用户相关操作

钱亿堆

已于 2022-09-21 15:57:19 修改

阅读量164

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2019-11-15 10:26:19 首次发布

本文链接：https://blog.csdn.net/qianyidui/article/details/103080239

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL有关用户的增删改查

增填

增
mysql> create user 李四@'127.0.0.1' identified by 'Aa123456';
Query OK, 0 rows affected (0.00 sec)

修改

改
mysql> alter user 李四@'127.0.0.1' identified by '123456Aa';
Query OK, 0 rows affected (0.00 sec)

查寻

查
mysql> select user,host,authentication_string from mysql.user;
+--------+--------------+-------------------------------------------+
| user   | host         | authentication_string                     |
+--------+--------------+-------------------------------------------+
| root   | localhost    | *4A488726AE5A0B0F0DB967998EE12D87F25C9610 |
| 李四    | 127.0.0.1    | *53C88C16B9903E40CC7CF99344A4FA1ADE213403 |
+--------+--------------+-------------------------------------------+

删除

删
mysql> drop user 李四@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

查看用户拥有哪些权限

mysql> show grants for qianyidui;
+----------------------------------------------------------------+
| Grants for qianyidui@%                                         |
+----------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'qianyidui'@'%' |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

撤销用户的某个权限

mysql> revoke update,delete on *.* from 'qianyidui'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for qianyidui;
+------------------------------------------------+
| Grants for qianyidui@%                         |
+------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'qianyidui'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)

MySQL通用的用户创建及授权

FLUSH PRIVILEGES;
CREATE USER qianyidui@'127.0.0.1' IDENTIFIED BY 'Aa123456@';   -- 先创建用户
GRANT ALL PRIVILEGES ON *.* TO qianyidui@'127.0.0.1';   -- 再授予用户权限
FLUSH PRIVILEGES;    -- 最后刷新权限