（2）用户与权限【MySQL】

权限管理

1、创建新用户

• CREATE USER 用户名[@域] IDENTIFIED BY '密码' ;

• 加域和不加域是有区别的

  • 若不指定域，则默认用户的域为【’%’】，表示为所有域
  • CREATE USER ecuter IDENTIFIED BY 'ecuter' ;
  • CREATE USER ecuter@localhost IDENTIFIED BY 'ecuter' ;
  • 若ip地址明确，则可用 用户名 本地登录/远程登录

• 拥有管理员权限的root用户可以创建新用户

2、修改用户名

• RENAME USER 原用户名 TO 新用户名 ;

3、为用户授权

• GRANT 权限名称 [column] ON 数据库对象 TO 用户名@'域'
• 例：GRANT all ON ecut.* TO ecuter@'%' ;
• 例：GRANT usage ON ecut.* TO ecuter@localhost ;
  • 为用户ecuter的localhost域 授予 数据库ecut所有对象的 使用权限usage

另：
远程连接虚拟机中的mysql数据库时，需要为root用户授予权限

• grant all privileges on *.* to 'root'@'%' identified by 'root';
• 刷新权限：flush privileges;
• 将当前user表的用户信息和privilige表的权限设置从mysql库（即mysql数据库的内置库）提取到内存里，在修改用户信息或者权限后，不重启mysql服务而使修改的内容直接生效

-- all privileges包含以下权限：
select,
insert,
update,
delete,
create,
drop,
references,
index,
alter,
create temporary tables,
lock tables,
execute,
create view,
show view,
create routine,
alter routine,
event,
trigger

4、查看当前用户

• SELECT user() ;
• SELECT user() FROM dual ;

5、查看用户权限

• 使用root用户身份查看某个用户的权限

  • show grants for '用户名'@'域' ;
  • show grants for 'ecuter'@'%' ;

• 用户查看自身权限

  • SHOW GRANTS ;

6、删除用户

• DROP USER 用户名 ;
• 数据库和用户是相互独立的，删除用户后，数据库还在（在数据库下创建用户）

7、修改用户密码

• 方法1：用SET PASSWORD命令

  set password for 用户名@localhost = password('新密码');

  • set password for root@localhost = password('123');

• 方法2：用mysqladmin

  mysqladmin -u用户名 -p旧密码 password 新密码

  • mysqladmin -uroot -p123456 password 123

• 方法3：在忘记root密码的时候，用UPDATE直接编辑user表

  1、关闭正在运行的MySQL服务

  2、打开DOS窗口，转到mysql\bin目录，输入`mysqld --skip-grant-tables ``

  • --skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证

  3、再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录

  4、输入mysql回车，如果成功，将出现MySQL提示符 >

  5、连接权限数据库：use mysql;

  6、改密码：update user set password=password('123') where user='root' and host='localhost';，后面的host可不要

  7、刷新权限：flush privileges;

  8、退出：quit

  9、注销系统，再进入，使用用户名root和刚才设置的新密码123登录

8、更新用户密码并设置加密规则

• 修改加密规则为：mysql_native_password
  • ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password';
• 刷新权限：flush privileges;
• 如果用Navicat连接Mysql时，出现1251错误，可重新设置加密规则

9、退出当前用户

• exit
• quit

二、Code

用户与权限（1）

• 登录：mysql -u root -p

• 列出当前用户root所能支配的所有数据库

  • show databases ;

• 首先创建一个数据库：CREATE DATABASE ecut ;

• 然后选择数据库：USE ecut ;

• 查看当前数据库下所有的表：show tables ;

• 创建表

  • CREATE TABLE t_students ( 
        id INT(5) , name VARCHAR(30) , gender CHAR(6) , birthdate DATE
    ) ;
    

  • 查看表结构：DESC t_students ;

• 创建新用户：CREATE USER ecuter IDENTIFIED BY 'ecuter' ; ;

• 通过 root 用户 将 ecut 数据库的所有权限统统授予给 ecuter 用户

  • GRANT all ON ecut.* TO ecuter@'%' ;

• 使用root用户身份查看某个用户的权限

  • show grants for 'ecuter'@'%';
  • 写对域才能查找成功！！

D:\>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> -- 首先创建一个数据库
mysql>
mysql> CREATE DATABASE ecut ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> -- 选择数据库
mysql>
mysql> USE ecut ;
Database changed
mysql>
mysql> -- 查看当前数据库下所有的表
mysql>
mysql> show tables ;
Empty set (0.00 sec)

mysql>
mysql> -- 创建表
mysql>
mysql> CREATE TABLE t_students ( id INT(5) , name VARCHAR(30) , gender CHAR(6) , birthdate DATE ) ;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> -- 查看表结构
mysql>
mysql> DESC t_students ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| gender    | char(6)     | YES  |     | NULL    |       |
| birthdate | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- 查看当前用户
mysql> SELECT user() FROM dual ;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> -- 创建新用户
mysql> -- CREATE USER 用户名 IDENTIFIED BY '密码' ;
mysql>
mysql> CREATE USER ecuter IDENTIFIED BY 'ecuter' ;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 列出当前用户root所能支配的所有数据库
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| ecut               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> -- 通过 root 用户 将 ecut 数据库的所有权限统统授予给 ecuter 用户
mysql>
mysql> -- GRANT 权限名称 ON 数据库对象 TO 用户名@域 ;
mysql>
mysql> GRANT all ON ecut.* TO ecuter@localhost ;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql>
mysql> select user() from dual ;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> GRANT all ON ecut.* TO ecuter@'%' ;
Query OK, 0 rows affected (0.01 sec)

mysql> rename user ecuter to yelaoshi ;
Query OK, 0 rows affected (0.01 sec)

mysql> rename user yelaoshi to ecuter ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> --使用root用户身份查看某个用户的权限
mysql> -- show grants for '用户名'@'域' ;
mysql> show grants for 'ecuter'@'localhost' ;
ERROR 1141 (42000): There is no such grant defined for user 'ecuter' on host 'localhost'
mysql>
mysql> -- 写对域才能查找成功！！
mysql> show grants for 'ecuter'@'%'
    -> ;
+--------------------------------------------------+
| Grants for ecuter@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `ecuter`@`%`               |
| GRANT ALL PRIVILEGES ON `ecut`.* TO `ecuter`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

用户和权限（2）

• 用ecuter用户身份登录：

  • mysql -u ecuter -p
  • -p后直接回车即可，不要在-p后显式写密码

• 查看当前用户：SELECT user() FROM dual ;

• 列出当前用户所能支配的所有数据库：show databases ;

• 选择数据库：use ecut ;

• 列出当前连接的数据库下的所有表：show tables ;

• 用户查看自身权限：show grants ;

• 退出用户登录：exit

Windows PowerShell
版权所有 (C) Microsoft Corporation。保留所有权利。

-- 用ecuter用户身份登录（-p后直接回车即可）
PS C:\Users\Administrator> mysql -u ecuter -p ecuter
Enter password:
ERROR 1045 (28000): Access denied for user 'ecuter'@'localhost' (using password: NO)
PS C:\Users\Administrator> mysql -u ecuter -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> -- 查看当前用户
mysql> SELECT user() FROM dual ;
+------------------+
| user()           |
+------------------+
| ecuter@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> -- 列出数据库
mysql>
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| ecut               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> -- 选择数据库
mysql> use ecut ;
Database changed
mysql>
mysql> -- 列出当前连接的数据库下的所有表
mysql> show tables ;
+----------------+
| Tables_in_ecut |
+----------------+
| t_students     |
+----------------+
1 row in set (0.00 sec)

mysql> desc t_students ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| gender    | char(6)     | YES  |     | NULL    |       |
| birthdate | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| ecut               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

-- 重新登录
PS C:\Users\Administrator> mysql -u ecuter -p
Enter password:
ERROR 1045 (28000): Access denied for user 'ecuter'@'localhost' (using password: NO)
PS C:\Users\Administrator> mysql -u ecuter -p
Enter password: ******
ERROR 1045 (28000): Access denied for user 'ecuter'@'localhost' (using password: YES)
PS C:\Users\Administrator> mysql -u yelaoshi -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> exit
Bye
PS C:\Users\Administrator> mysql -u ecuter -p
Enter password: ********
ERROR 1045 (28000): Access denied for user 'ecuter'@'localhost' (using password: YES)
PS C:\Users\Administrator> mysql -u ecuter -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show grants ;
+--------------------------------------------------+
| Grants for ecuter@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `ecuter`@`%`               |
| GRANT ALL PRIVILEGES ON `ecut`.* TO `ecuter`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql>