MySQL 用户设置与权限管理

---

添加 MySQL 用户

方法一：在 mysql 数据库中的 user 表添加新用户

以下为添加用户的的实例，用户名为 guest，密码为 guest123，并授权用户可进行 SELECT，INSERT 和 UPDATE 操作权限：

mysql> use mysql;
Database changed
mysql> insert into user (host, user, authentication_string, select_priv, insert_priv, update_priv) values ('localhost', 'guest', MD5('guest123'), 'Y', 'Y', 'Y');
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

报错！！

解决办法：在 my.ini 配置文件中添加

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

原因：可能默认模式为严格模式 sql_mode=NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
为了安全，严格模式禁止通过 insert 这种形式直接修改 mysql 库中的 user 表进行添加新用户。

重启 MySQL，执行命令：

mysql> use mysql;
Database changed
mysql> insert into user (host, user, authentication_string, select_priv, insert_priv, update_priv) values ('localhost', 'guest', MD5('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected, 3 warnings (0.10 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT host, user, authentication_string FROM user WHERE user = 'guest';
+-----------+-------+----------------------------------+
| host      | user  | authentication_string            |
+-----------+-------+----------------------------------+
| localhost | guest | fcf41657f02f88137a1bcf068a32c0a3 |
+-----------+-------+----------------------------------+
1 row in set (0.05 sec)

注意事项：

1. 在 MySQL5.7 中 user 表的 password 已换成了 authentication_string。
2. MD5() 函数表示加密函数。8.0.11 之前使用 PASSWORD()。
3. 需要执行 FLUSH PRIVILEGES; 语句。这个命令执行后会重新载入授权表。如果不使用该命令，就无法使用新创建的用户来连接 mysql 服务器，除非重启 mysql 服务器。

可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 'Y' 即可，用户权限列表如下：

select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, reload_priv, shutdown_priv, process_priv, file_priv, grant_priv, references_priv, index_priv, alter_priv

方法二：使用 SQL 的 CREATE 和 GRANT 命令（建议）

使用 create 命令创建用户。下面命令创建了用户 test，密码是 test123。

mysql> use mysql;
Database changed
mysql> create user 'test'@localhost identified by 'test123';
Query OK, 0 rows affected (0.06 sec)

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> quit
Bye

F:\web\mysql-8.0.17-winx64\bin>mysql -u test -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

下面的命令会给指定数据库 testdb 添加用户 test，并赋予权限。

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON testdb.*
    -> TO 'test'@localhost;
Query OK, 0 rows affected (0.07 sec)

mysql> exit
Bye

F:\web\mysql-8.0.17-winx64\bin>mysql -u test -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)

MySQL 的 SQL 语句以分号 ; 作为结束标识。
MySQL 语法对大小写不敏感，但是大写更容易看出。

---

GRANT 命令

一、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权限

grant select, insert, update, delete on testdb.* to common_user@'%';

二、grant 数据库开发人员，创建表、索引、视图、存储过程、函数等权限

grant 创建、修改、删除 MySQL 数据表结构权限：

grant create, alter, drop on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 外键权限：

grant references on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 临时表权限：

grant create temporary tables on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 索引权限：

grant index on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 创建视图权限：

grant create view on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 查看视图权限：

grant show view on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 存储过程、函数的权限：

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status

grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure

grant execute on testdb.* to developer@'192.168.0.%';

三、grant 普通 DBA 管理某个 MySQL 数据库的权限

grant all privileges on testdb to dba@'localhost';

其中，关键字 privileges 可以省略。

四、grant 高级 DBA 管理 MySQL 中所有数据库的权限

grant all on *.* to dba@'localhost';

五、grant 权限，分别可以作用在多个层次上

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to 'dba'@localhost;
grant execute on function testdb.fn_add to 'dba'@localhost;

六、查看 MySQL 用户权限

查看当前用户（自己）权限：

show grants;

查看其他 MySQL 用户权限：

show grants for dba@localhost;

七、撤销已经赋予给 MySQL 用户权限的权限

revoke 跟 grant 的语法差不多，只需要把关键字 to 换成 from 即可：

grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;

八、grant、revoke 用户权限注意事项

1. grant、revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 grant option。这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。

grant select on testdb.* to dba@localhost with grant option;

注意：创建完成后需要执行 FLUSH PRIVILEGES; 语句。

---

学习 MySQL：目录