MySQL必知必会 -- 安全管理

访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。

考虑以下内容：

• 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
• 某些用户需要读表，但可能不需要更新表；
• 你可能想允许用户添加数据，但不允许他们删除数据；
• 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
• 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
• 你可能想根据用户登录的地点限制对某些功能的访问。

即我们需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访
问控制，管理访问控制需要创建和管理用户账号。

我们还要注意防止无意的错误以及不应该在日常的MySQL操作中使用root。

管理用户

创建用户账号

mysql库下的user表记录了用户的信息。

MariaDB [mysql]> use mysql;
Database changed
MariaDB [mysql]> select user from user;
+------+
| user |
+------+
| root |
| root |
|      |
| root |
|      |
| root |
+------+
6 rows in set (0.00 sec)

1. 创建用户：

MariaDB [mysql]> create user aom@localhost identified by 'caoaoyuan';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user from user;
+------+
| user |
+------+
| root |
| root |
|      |
| aom  |
| root |
|      |
| root |
+------+
7 rows in set (0.00 sec)

账号的组成方式：用户名+@+主机（所以可以出现重复的用户名，跟其他的数据库不一样）

用户名：16字符以内.。（我这里是aom)
@
主机名：可以用主机名和IP地址，也可以用通配符（我这里是localhost，表示本机）
通配符说明：172.18.10.%（IP地址为172.18.10段的所有IP地址都可以访问）
例子：
CREATE USER ‘john’@‘192.168.189.%’ IDENTIFIED BY “123”;
允许192.168.189网段的所有主机

CREATE USER ‘john’@’ %’ ;
允许所有主机

2. 我们也可以直接通过授权的方式创建用户:

MariaDB [mysql]> GRANT SELECT ON test.* TO cao@localhost identified by 'caoaoyuan';
Query OK, 0 rows affected (0.03 sec)

MariaDB [mysql]> select user from user;
+------+
| user |
+------+
| root |
| john |
| root |
|      |
| aom  |
| cao  |
| root |
|      |
| root |
+------+
9 rows in set (0.00 sec)

3. 第三种方法是在user表中插入数据。

更改用户名：

MariaDB [mysql]> RENAME user aom@localhost to west@localhost;
Query OK, 0 rows affected (0.00 sec)

删除用户账号

MariaDB [mysql]> select user from user;
+------+
| user |
+------+
| root |
| john |
| root |
|      |
| aom  |
| root |
|      |
| root |
+------+
8 rows in set (0.00 sec)

设置访问权限

• 查看用户权限

MariaDB [mysql]> show grants for aom@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for aom@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aom'@'localhost' IDENTIFIED BY PASSWORD '*47C22BFC5AC47B6CB4AEF2CAFABD21503F7A4745' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

USAGE表示根本没有权限。

• 授权

MariaDB [mysql]> grant select on test.* to aom@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for aom@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for aom@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aom'@'localhost' IDENTIFIED BY PASSWORD '*47C22BFC5AC47B6CB4AEF2CAFABD21503F7A4745' |
| GRANT SELECT ON `test`.* TO 'aom'@'localhost'                                                              |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 取消授权

MariaDB [mysql]> revoke select on test.* from aom@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for aom@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for aom@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aom'@'localhost' IDENTIFIED BY PASSWORD '*47C22BFC5AC47B6CB4AEF2CAFABD21503F7A4745' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GRANT和REVOKE可在下面几个层次上控制访问权限：

可以授予的权限列表：

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
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	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和R
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

这样我们就可以实现用户的控制了。

Tips

• 当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该
  数据库或表，这些权限仍然起作用。
• 简化授权: grant select,drop,insert on test.* to aom@localhost; 用，隔开即可

更改口令（密码）

MariaDB [(none)]> set password for root@localhost = password('123');
Query OK, 0 rows affected (0.00 sec)

[root@server1 ~]# mysqladmin -uaom -p password caoaoyuan
Enter password: 

MariaDB [(none)]> update mysql.user set Password = password('123') where User = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)