MySQL基础教程9——DCL—用户管理
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)
