MySQL 用户管理
总结自:MySQL Crash Course(MySQL必知必会)
https://e.jd.com/30154378.html?ebook=1
MySQL-用户管理
mysql> use mysql;
mysql> select user from user;
mysql> select user from user;
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
4 rows in set (0.00 sec)
mysql
数据库有一个 user
表,包含所有用户账号。user 表有一个 user 列,用来储存用户登陆名。
@
1. 创建用户账号
使用 CREATE USER (USER) IDENTIFIED BY (PASSWORD)
语句:
mysql> create user ben identified by 'mysql';
mysql> select user from user;
+------------------+
| user |
+------------------+
| ben |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.00 sec)
GRANT 和 INSERT 也能创建用户,但是不建议直接对用户表进行插入操作。
重命名用户账号,使用 RENAME USER (USER) TO (NEW)
语句:
mysql> rename user ben to pineapple;
Query OK, 0 rows affected (0.11 sec)
mysql> select user from user;
+------------------+
| user |
+------------------+
| pineapple |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.00 sec)
2. 删除用户账号
删除用户账号使用DROP USER (USER)
语句:
mysql> drop user pineapple;
Query OK, 0 rows affected (0.08 sec)
mysql> select user from user;
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
4 rows in set (0.00 sec)
注意:mysql5之后的版本使用 DROP USER 可以同时删除账号与账号权限
3. 设置访问权限
新创建的用户没有任何权限,能登录数据库,但看不到任何数据,执行不了任何操作
mysql> create user pineapple identified by 'mysql';
Query OK, 0 rows affected (0.13 sec)
mysql> show grants for pineapple;
+---------------------------------------+
| Grants for pineapple@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `pineapple`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)
结果显示 pineapple 用户有一个权限:USAGE ON \*.*
,表示没有权限。
为了设置权限,可以使用 GRANT
语句,它要求至少有以下信息
- 要授予的权限
- 被授予访问权限的数据库和表
- 用户名
举个例子:
mysql> create database demo;
Query OK, 1 row affected (0.15 sec)
mysql> grant select on demo.* to pineapple;
Query OK, 0 rows affected (0.12 sec)
mysql> show grants for pineapple;
+---------------------------------------------+
| Grants for pineapple@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `pineapple`@`%` |
| GRANT SELECT ON `demo`.* TO `pineapple`@`%` |
+---------------------------------------------+
2 rows in set (0.00 sec)
此 GRANT 表示:允许用户 pineapple 在数据库demo下的所有表使用select查询语句,也就是只有访问权限。
GRANT 的反操作为 REVOKE
,用来撤销用户的特定权限。
mysql> revoke select on demo.* from pineapple;
Query OK, 0 rows affected (0.10 sec)
mysql> show grants for pineapple;
+---------------------------------------+
| Grants for pineapple@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `pineapple`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)
注意:
GRANT TO
REVOKE FROM
GRANT 和 REVOKE 可以在几个层次上控制访问权限
- 整个服务器,使用 GRANT ALL 和 REVOKE ALL
- 整个数据库,使用 ON database.*
- 特定的表,使用 On database.table
- 特定的列
- 特定的存储过程
4. 更改用户口令
为了更改用户口令,可以使用 SET PASSWORD FOR USER = PASSWORD('PASSWORD')
set password for pineapple = Password('Password$123');
注意:
新密码必须传到
Password()
函数进行加密MySQL8版本使用 `ALTER USER 'USER'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'PASSWORD':
alter user 'pineapple'@'%' identified with MYSQL_NATIVE_PASSWORD BY 'Password$123';
总结自:MySQL Crash Course(MySQL必知必会)
https://e.jd.com/30154378.html?ebook=1