持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第30天，点击查看活动详情
MySQL基础教程9——DCL—用户管理
查询用户
mysql> use mysql;
Database changed
mysql> select * from user;
创建用户
create user 用户名@主机名 identified by 密码;
本机为localhost,任意主机为%
mysql> create user 'look'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.05 sec)
接下来用新注册的账号尝试登入
C:\Users\HP>mysql -u look -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
修改用户
alter user 用户名@主机名 identified with mysql_native_password by 新密码;
mysql> alter user 'look'@'localhost' identified with mysql_native_password by '654321';
Query OK, 0 rows affected (0.02 sec)
删除用户
drop user 用户名@主机名;
mysql> drop user 'look'@'localhost';
Query OK, 0 rows affected (0.04 sec)
查询权限
show grants for 用户名@主机名;
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)
授予权限
grant 权限列表 on 数据库名.表名 to 用户名@主机名;
mysql> show grants for 'look'@'localhost' ;
+------------------------------------------+
| Grants for look@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'look'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> grant all on text.* to 'look'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'look'@'localhost';
+--------------------------------------------------------+
| Grants for look@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'look'@'localhost' |
| GRANT ALL PRIVILEGES ON `text`.* TO 'look'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@主机名;
mysql> revoke all on text.* from 'look'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'look'@'localhost';
+------------------------------------------+
| Grants for look@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'look'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)
