43.MySQL管理

1.用户管理

当我们做项目开发时，可以根据不同的开发人员，赋予其相应的MySQL操作权限。

所以MySQL数据库管理人员（root）可以根据需要创建不同的用户，赋给相应的权限，供开发人员使用。

1.1字段说明

mysql中的用户，都存储在系统数据库 mysql 中 user 表中：

其中user表的重要字段说明：

1. host: 允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
2. user: 用户名;
3. authentication_string: 密码，是通过mysql的password()函数加密之后的密码。

1.2添加用户

基础语法：

create user '用户名'@'允许登录位置' identified by '密码';

示例：

CREATE USER 'ming_code'@'localhost' IDENTIFIED BY '123456';

查看一下：

SELECT * FROM mysql.user;

解读：

（1）'ming_code'@'localhost' 合起来才表示用户的完整信息

用户名	登录的 IP
'ming_code'	'localhost'

（2）123456 密码，但是注意存放到 mysql.user 表时，是 password ('123456') 加密后的密码；
可以调用系统函数查看一下是否一致：

SELECT PASSWORD('123456');

1.3删除用户

基础语法：

drop user '用户名'@'允许登录位置';

注意要将用户名和允许登录位置一起写上才可以表示要删除一个完整的用户信息：

drop user 'ming_code'@'localhost';

1.4用户登录

创建一个新连接，使用刚刚创建的用户登录：

我们会发现在刚刚创建的用户视角下可以使用的数据库与root管理员是不同的：

不同的数据库用户，登录到 DBMS 后，根据相应的权限，可以操作的数据库和
数据对象 (表，视图，触发器) 都不一样；

当然这也是我们希望的效果。

管理员：
root → Mysql DBMS → 可以操作的 DB → 可以操作的表 / 视图等对象

用户：
ming_code → Mysql DBMS → 可以操作的 DB → 可以操作的表 / 视图等对象

1.5用户修改密码

修改自己的密码：

set password = password('密码');

修改他人的密码（需要有修改用户密码权限）：

set password for '用户名'@'登录位置' = password('密码');

2.权限管理

2.1MySQL中的权限

权限	意义
ALL [PRIVILEGES]	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT... INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

2.2给用户授权

● 给用户授权

基本语法:

grant 权限列表 on 库.对象名 to '用户名'@'登录位置' 【identified by '密码'】

说明:

1. 权限列表，多个权限用逗号分开

• • grant select on .......
  • grant select, delete, create on .......
  • grant all [privileges] on ...... // 表示赋予该用户在该对象上的所有权限

1. 特别说明

• • *.* ：代表本系统中的所有数据库的所有对象(表，视图，存储过程)
  • 库.* ：表示某个数据库中的所有数据对象(表，视图，存储过程等)

1. identified by 可以省略，也可以写出：
   (1) 如果用户存在，就是修改该用户的密码。
   (2) 如果该用户不存在，就是创建该用户！

2.3回收用户授权

基本语法:

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

2.4权限生效指令

如果权限没有生效，可以执行下面命令。

基本语法:

FLUSH PRIVILEGES;

2.5示例

用户管理练习题

1. 创建一个用户(你的名字，拼音)，密码 123，并且只可以从本地登录，不让远程登录 mysql
2. 创建库和表 testdb 下的 news 表 ，要求: 使用root 用户创建
3. 给用户分配查看 news 表和添加数据的权限
4. 测试看看用户是否只有这几个权限
5. 修改密码为 abc ，要求: 使用root 用户完成
6. 重新登录
7. 使用 root 用户删除你的用户

创建用户：

create user 'exp'@'localhost' identified by '123';

root创建数据库：

CREATE DATABASE IF NOT EXISTS testdb;

root创建表news：

use testdb;

create table news(
  id int,
  content text
);

授予新用户exp权限：

grant insert,select on testdb.news to 'exp'@'localhost';

发现该用户可以查看news表：

SELECT * FROM testdb.news;

发现该用户可以在该表中插入数据：

INSERT INTO testdb.news VALUES(1,'疯狂疯狂星期四！');

但是该用户不可以修改表中数据，因为没有给其授予该项权限：

UPDATE testdb.news SET content = '9.9' WHERE id = 1;

1 queries executed, 0 success, 1 errors, 0 warnings

查询：update testdb.news set content = '9.9' where id = 1

错误代码： 1142
UPDATE command denied to user 'exp'@'localhost' for table 'news'

执行耗时   : 0 sec
传送时间   : 0 sec
总耗时      : 0 sec

root修改一下该用户密码：使用新密码可以再次登录

set password for 'exp'@'localhost' = password('abc');

root删除该用户：

drop user 'exp'@'localhost';

3.管理细节

1. 在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有连接权限
   create user xxx;
2. 你也可以这样指定
   create user 'xxx'@'192.168.1.%' 表示 xxx用户在 192.168.1.*的IP可以登录MySQL
3. 在删除用户的时候，如果 host 不是 %，需要明确指定 '用户'@'host值'