用户与权限管理（命令）

1. 用户管理

1.1 登录 MySQL 服务器

启动 MySQL 服务后，可以通过 mysql 命令来登录 MySQL 服务器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

下面详细介绍命令中的参数：

- h 参数 后面接主机名或者主机 IP ， hostname 为主机， hostIP 为主机 IP 。

- P 参数 后面接 MySQL 服务的端口，通过该参数连接到指定的端口。 MySQL 服务的默认端口是 3306 ，

不使用该参数时自动连接到 3306 端口， port 为连接的端口号。

- u 参数 后面接用户名， username 为用户名。

- p 参数 会提示输入密码。

DatabaseName 参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到 MySQL 数据库

中，然后可以使用 USE 命令来选择数据库。

- e 参数 后面可以直接加 SQL 语句。登录 MySQL 服务器以后即可执行这个 SQL 语句，然后退出 MySQL 服务器。

1.2 创建用户

CREATE USER 语句的基本语法形式如下：

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

用户名参数表示新建用户的账户，由 用户（ User ） 和 主机名（ Host ） 构成；

“[ ]” 表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户

可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用

IDENTIFIED BY 指定明文密码值。

CREATE USER 语句可以同时创建多个用户。

1.3 修改用户

修改用户名：

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; FLUSH PRIVILEGES;

1.4 删除用户

方式 1 ：使用 DROP 方式删除（推荐）

使用 DROP USER 语句来删除用户时，必须用于 DROP USER 权限。 DROP USER 语句的基本语法形式如下：

DROP USER user[,user]…;

方式 2 ：使用 DELETE方式删除（不推荐）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

执行完 DELETE 命令后要使用 FLUSH 命令来使用户生效，命令如下：

FLUSH PRIVILEGES;

注意：不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保

留。而 drop user 命令会删除用户以及对应的权限，执行命令后你会发现 mysql.user 表和 mysql.db 表

的相应记录都消失了。

1.5 设置当前用户密码

这里介绍 推荐的写法 ：

1. 使用 ALTER USER 命令来修改当前用户密码 用户可以使用 ALTER 命令来修改自身密码，如下语句代表修

改当前登录用户的密码。基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用 SET 语句来修改当前用户密码 使用 root 用户登录 MySQL 后，可以使用 SET 语句来修改密码，具体

SQL 语句如下：

SET PASSWORD='new_password';

该语句会自动将密码加密后再赋给当前用户。

1.6 修改其它用户密码

1. 使用 ALTER 语句来修改普通用户的密码 可以使用 ALTER USER 语句来修改普通用户的密码。基本语法形

式如下：

ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;

2. 使用 SET 命令来修改普通用户的密码 使用 root 用户登录到 MySQL 服务器后，可以使用 SET 语句来修改普

通用户的密码。 SET 语句的代码如下：

SET PASSWORD FOR 'username'@'hostname'='new_password';

2. 权限管理

2.1 权限列表

MySQL 到底都有哪些权限呢？

mysql> show privileges;

1 ） CREATE 和 DROP 权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的 DROP 权限授予某用户，用户就可以删除 MySQL 访问权限保存的数据库。

2 ） SELECT、 INSERT 、 UPDATE 和 DELETE 权限 允许在一个数据库现有的表上实施操作。

3 ） SELECT 权限

只有在它们真正从一个表中检索行时才被用到。

4 ） INDEX 权限 允许创建或删除索引， INDEX 适用于已有的表。如果具有某个表的CREATE 权限，就可以在 CREATE TABLE 语句中包括索引定义。

5 ） ALTER 权

限 可以使用 ALTER TABLE 来更改表的结构和重新命名表。

6 ） CREATE ROUTINE 权限 用来创建保存的 程序（函数和程序），ALTER ROUTINE 权限用来更改和删除保存的程序， EXECUTE 权限 用来执行保存的

程序。 

7 ） GRANT 权限 允许授权给其他用户，可用于数据库、表和保存的程序。

8 ） FILE 权限 使用户可以使用LOAD DATA INFILE 和 SELECT ... INTO OUTFILE 语句读或写服务器上的文件，任何被授予 FILE 权限的用户都能读或写MySQL 服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

2.2 授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：

1 、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给 select 权限就可

以了，不要给用户赋予 update 、 insert 或者 delete 权限。

2 、创建用户的时候 限制用户的登录主机 ，一般是限制成指定 IP 或者内网 IP 段。

3 、为每个用户 设置满足密码复杂度的密码 。

4 、 定期清理不需要的用户 ，回收权限或者删除用户。

2.3 授予权限

授权命令：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

该权限如果发现没有该用户，则会直接新建一个用户。

2.4 查看权限

查看当前用户权限

SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();

查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机地址' ;

2.5 收回权限

注意：在将用户账户从 user 表删除之前，应该收回相应用户的所有权限。

收回权限命令

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

注意： 须用户重新登录后才能生效

3. 角色管理

3.1 创建角色

创建角色使用 CREATE ROLE 语句，语法如下：

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名称的命名规则和用户名类似。如果 host_name 省略，默认为 % ， role_name 不可省略 ，不可为空。

练习：我们现在需要创建一个经理的角色，就可以用下面的代码：

CREATE ROLE 'manager'@'localhost';

 

3.2 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

3.3  查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS 语句，来查看权限是否创建成功了：

mysql> SHOW GRANTS FOR 'manager'; 
+-------------------------------------------------------+
| Grants for manager@% | +-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` | | GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` | | GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` | | GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` | +-------------------------------------------------------+

只要你创建了一个角色，系统就会自动给你一个 “ USAGE ” 权限，意思是 连接登录数据库的权限 。

3.4  回收角色的权限

撤销角色权限的 SQL 语法如下：

REVOKE privileges ON tablename FROM 'rolename';

练习 1 ：撤销 school_write 角色的权限。 

1 ）使用如下语句撤销 school_write 角色的权限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

2 ）撤销后使用 SHOW 语句查看 school_write 对应的权限，语句如下。

SHOW GRANTS FOR 'school_write';

3.5  删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角 色。删除角色的操作很简单，你只要掌握语法结构就行了。

DROP ROLE role [,role2]...

3.6 给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用 GRANT 语句，语 法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。

3.7  激活角色

方式 1 ：使用 set default role 命令激活角色

举例：

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

方式 2 ：将 activate_all_roles_on_login 设置为 ON

设置：

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

3.8  撤销用户的角色

撤销用户角色的 SQL 语法如下：

REVOKE role FROM user;