| 剑指offer：一年又13天 |
|---|
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
10 rows in set (0.00 sec)
mysql> use mysql; -- 选择数据库 mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
. . .
. . .
| user | -- 找到 user表，MySQL所有注册用户的信息都保存在这里
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from user\G -- 默认有三行记录
*************************** 1. row ***************************
Host: localhost -- 登录主机：本地主机（只能从本地主机登录）
User: root -- 用户名
Select_priv: Y -- 各种操作权限，这个是查询权限
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
. . .
. . .
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
. . .
. . .
plugin: mysql_native_password
authentication_string: *0EE49BEF4A01530FDD960C259978FF754862A592 -- 加密后的登录密码
password_expired: N
password_last_changed: 2023-11-19 15:32:07
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
. . .
. . .
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
. . .
. . .
语法：
CREATE USER 'user_name'@'登录位置' IDENTIFIED BY 'user_password';
示例：
mysql> create user 'zhangsan'@'localhost' identified by '123456'; -- 密码这里报错就往下看：安全设置那里
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user\G
*************************** 4. row ***************************
Host: localhost -- 登录主机
User: zhangsan -- 用户名
Select_priv: N -- 新用户操作权限都是：N
Insert_priv: N
Update_priv: N
. . .
. . .
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 -- 密码
password_expired: N
password_last_changed: 2023-12-11 20:10:04
password_lifetime: NULL
account_locked: N
4 rows in set (0.00 sec)
注意：
上方登录位置设置为本地主机是无法远端登录的。
想要远端登录登录位置可以设置为指定的IP或者是 ‘%’， '%'表示任意主机登录。
mysql> create user 'zhangsan'@'%' identified by '123456'; -- 登录位置设置为 % 表示可以从任一主机登录，实际使用是绝对不用这样
语法：
DROP USER 'user_name'@'登录位置';
示例：
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | zhangsan |
+-----------+---------------+
4 rows in set (0.00 sec)
mysql> drop user 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
语法
-- 默认修改自己的密码
SET PASSWORD = PASSWORD('新密码');
-- 可以修改自己的，或者root修改其他人的
SET PASSWORD FOR 'user_name'@'登录位置' = PASSWORD('新密码');
示例：
mysql> set password for 'zhangsan'@'localhost' = password('123abc');
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL对密码的要求：数字、大小写字母和特殊字符都要有，如果不符合要求就不能使用。
mysql> create user 'test'@'localhost' identified by '123456'; -- 只有数字
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements -- 密码不符合要求
mysql> create user 'test'@'localhost' identified by '123AAA'; -- 只有数字和大写字母
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test'@'localhost' identified by '123Aaa@@@'; -- 数字、大小写字母和特殊字符都有
Query OK, 0 rows affected (0.00 sec)
这样的检查策略是为了保证账户的安全，但同时也会让我们的密码过于复杂，因此为了简化密码，我们可以修改密码配置，让MySQL的密码检查减弱一点。
首先查看一下MySQL中全局密码配置，我们这次只看两个配置：密码长度和密码策略
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 | -- 密码长度 *
| validate_password_mixed_case_count | 1 | -- 大小写字母最少个数
| validate_password_number_count | 1 | -- 数字最少个数
| validate_password_policy | MEDIUM | -- 密码策略 *
| validate_password_special_char_count | 1 | -- 特殊字符最少个数
+--------------------------------------+--------+
7 rows in set (0.00 sec)
只需要修改两个全局配置：
mysql> set global validate_password_policy=low; -- 降低检查策略
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6; -- 减少最小密码长度
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
设置成功后就可以使用 '123456’这样的密码了。
mysql> create user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
补充：
新创建的用户是没有任何权限的，需要 root 给他分配各种操作权限，比如：谁谁谁 可以在 数据库什么什么 中 查看 哪张表，
语法：
GRANT 权限列表 ON 库名.表名 TO 'user_name'@'登录位置';
权限列表有两种写法：
直接写各种各样的操作 ：select, drop, insert 等
可以使用 ：all 表示添加所有权限
数据库与表名这里有三种写法：
*.* : 所有数据库中的所有表
db1.* : db1数据库中的所有表
db1.stu : db1数据库中的stu表
示例1：
-- 登录 zhangsan 账号
mysql> show databases; -- 什么都查不到
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
-- 登录 root 账号
-- 赋予张三 对数据库db3所有表的查看 和 在db3中创建表的权限
mysql> grant select, create on db3.* to 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
-- zhangsan端
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db3 | -- 可以看到 db3
+--------------------+
2 rows in set (0.00 sec)
mysql> use db3;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| EMP |
| user |
+---------------+
2 rows in set (0.00 sec)
mysql> select empid, ename, job from EMP limit 3; -- 查看表信息
+--------+--------+----------+
| empid | ename | job |
+--------+--------+----------+
| 100002 | NRUZlg | SALESMAN |
| 100003 | DSDpOb | SALESMAN |
| 100004 | TbynUK | SALESMAN |
+--------+--------+----------+
3 rows in set (0.00 sec)
mysql> create table tb(id int); -- 建表
Query OK, 0 rows affected (0.03 sec)
mysql> drop table tb; -- 删除表
ERROR 1142 (42000): DROP command denied to user 'zhangsan'@'localhost' for table 'tb' -- 权限拒绝
-- root端
mysql> grant drop on db3.* to 'zhangsan'@'localhost'; -- 赋予删除表权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost'; -- 查看一下 'zhangsan'@'localhost'所有权限
+-----------------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT, CREATE, DROP ON `db3`.* TO 'zhangsan'@'localhost' | -- 查看、创建、删除
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
-- zhangsan端
mysql> drop table tb;
Query OK, 0 rows affected (0.02 sec)
示例2：
-- root端
mysql> grant all on db3.* to 'zhangsan'@'localhost'; -- 赋予张三对db3数据库的所有操作权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost';
+-----------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT ALL PRIVILEGES ON `db3`.* TO 'zhangsan'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)
语法：
REVOKE 权限列表 ON 库名.表名 FROM 'user_name'@'登录位置';
示例1：
-- root端
mysql> show grants for 'zhangsan'@'localhost';
+-----------------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT, CREATE, DROP ON `db3`.* TO 'zhangsan'@'localhost' |
+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke drop on db3.* from 'zhangsan'@'localhost'; -- 删除drop权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost';
+-----------------------------------------------------------+
| Grants for zhangsan@localhost |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
| GRANT SELECT, CREATE ON `db3`.* TO 'zhangsan'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)
-- zhangsan端
mysql> drop table EMP;
ERROR 1142 (42000): DROP command denied to user 'zhangsan'@'localhost' for table 'EMP' -- 权限拒绝
示例2：
-- root端
mysql> revoke all on db3.* from 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'localhost';
+----------------------------------------------+
| Grants for zhangsan@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' | -- 没有任何权限了
+----------------------------------------------+
1 row in set (0.00 sec)
有一点需要注意：
'user_name'@'登录位置'
一般当做一个整体来使用。