MySQL OCP888题解010-privileges of joe

1、原题

1.1、英文原题

1.2、答案

A、F、G

2、题目解析

2.1、题干解析

本题主要考察MySQL的授权机制。

2.2、选项解析

• 在题干里，由于授予了joe@example.com在world数据库上UPDATE、DELETE并继续授权其他人的权限，又授予了 joe@example.com对所有数据库SELECT的权限，所以joe@example.com就有了在world数据库上SELECT并授予其他人SELECT的权限。所以joe@example.com有权将world数据库的SELECT、UPDATE、DELETE的权限授予其他用户，但对audit数据库只有INSERT权限没有授权其他人的权限。
• 所以选项A、F、G是正确的，选项D、E是错误的（因为试图对audit授权）
• 选项B、C错误，因为并没有说joe@example.com有admin权限，所以并不能去修改密码或者创建用户。

3、知识点

3.1、知识点1：GRANT OPTION

• 如果授予一个用户GRANT OPTION权限，则该用户在该级别拥有的（或将来可能被授予的）所有权限也可以由该用户授予其他用户。例如你在一个数据库上授予一个用户INSERT权限。如果你再在数据库上授予其SELECT权限，并指定WITH GRANT选项，该用户不仅可以给其他用户SELECT权限，还可以给INSERT权限。如果你再在数据库上授予该用户UPDATE权限，该用户可以授予INSERT、SELECT和UPDATE权限。
• 帐户的主机名部分，如果省略，默认为’%'。
• 如果想删除某个用户的所有权限，使用如下语句：mysql> REVOKE ALL PRIVILEGES, GRANT OPTION, USAGE FROM '<user_name>'@'<host>';
• 如果在GRANT语句中指定的账户不存在，所采取的行动取决于NO_AUTO_CREATE_USER SQL模式。
  1. 如果NO_AUTO_CREATE_USER没有被启用，GRANT会创建账户。这是非常不安全的，除非你使用IDENTIFIED BY指定一个非空的密码。
  2. 如果NO_AUTO_CREATE_USER被激活，GRANT会失败，并且不会创建账户，除非你使用IDENTIFIED BY指定一个非空的密码或者使用IDENTIFIED WITH命名一个认证插件。
  3. 如果账户已经存在，IDENTIFIED WITH是被禁止的，因为它只用于创建新账户时。
• 在GRANT语句中指定数据库名称时，允许使用_和%通配符，该语句在数据库级别上授予权限（GRANT ... ON db_name.*）。这意味着，例如，要使用_字符作为数据库名称的一部分，在GRANT语句中使用_转义字符指定它，以防止用户能够访问与通配符模式相匹配的其他数据库（例如，GRANT ... ON `foo\_bar`.* TO ... ）。
• 在GRANT语句中，ALL [PRIVILEGES]或PROXY特权必须单独指定，不能与其他特权一起指定。ALL [PRIVILEGES]代表除了GRANT OPTION和PROXY权限以外的所有要授予权限的级别的可用权限。
• MySQL允许你对不存在的数据库或表授予权限。对于表，要授予的权限必须包括CREATE权限。这种行为是设计出来的，目的是使数据库管理员能够为以后要创建的数据库或表准备用户账户和权限。
• MySQL将权限与主机名和用户名的组合相关联，而不是只与用户名相关联。

官方参考文档

3.2、知识点2：列级权限

• 列级权限适用于给定表中的单个列。在列级授予的每个特权必须紧跟在列的后面，用圆括号括起来。一个列可以授权的权限包括INSERT、REFERENCES、SELECT和UPDATE。MySQL在mysql.columns_priv系统表中存储列的权限。
• 在MySQL中，有可能只对一个表中的某些列拥有INSERT权限。在这种情况下，你仍然可以在表中执行INSERT语句，只要你只为那些你有INSERT权限的列插入值。如果没有启用严格的SQL模式，被省略的列将被设置为其隐含的默认值。在严格模式下，如果任何被省略的列没有默认值，该语句将被拒绝。
• 列授权如下例所示：mysql> GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

3.3、知识点3：代理用户权限

• 代理权限使一个用户可以成为另一个用户的代理。代理用户冒充或取得被代理用户的身份，也就是拥有了被代理用户的权限。当授予PROXY时，它必须是GRANT语句中唯一指定的特权。
• MySQL在mysql.proxies_priv系统表中存储代理权限。
• 代理用户授权如下例所示：mysql> GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

4、实验

4.1、实验1

4.1.1、实验目的

验证如果一个用户对某库被授予了SELECT权限，又被授予了对某库有UPDATE或其他权限的同时有GRANT权限，则该用户对该库的SELECT和UPDATE都有GRANT权限，而不是只对UPDATE有GRANT权限。

4.1.2、实验前准备

MySQL5.7已安装并运行。

4.1.3、实验步骤

1、用root用户创建两个库d1和d2， 2个用户user_a和user_b，如下所示：

mysql> DROP DATABASE IF EXISTS d1;
mysql> DROP DATABASE IF EXISTS d2;
mysql> DROP USER IF EXISTS user_a;
mysql> DROP USER IF EXISTS user_b;
mysql> CREATE DATABASE d1;
mysql> CREATE DATABASE d2;
mysql> CREATE USER user_a IDENTIFIED BY '000000';
mysql> CREATE USER user_b IDENTIFIED BY '000000';

2、对user_a先授予d1的SELECT权限，再授予d2的UPDATE和GRANT权限，然后查看user_a的权限，发现user_a对d1有SELECT权限但没有GRANT权限，而对d2有UPDATE和GRANT权限。

mysql> GRANT SELECT ON d1.* to user_a;
mysql> GRANT UPDATE ON d2.* to user_a WITH GRANT OPTION;
mysql> SHOW GRANTS FOR user_a;
+----------------------------------------------------------+
| Grants for user_a@%                                      |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_a'@'%'                       |
| GRANT SELECT ON `d1`.* TO 'user_a'@'%'                   |
| GRANT UPDATE ON `d2`.* TO 'user_a'@'%' WITH GRANT OPTION |

3、对user_b先授予d1的SELECT权限，再授予d1的UPDATE和GRANT权限，然后查看user_b的权限：我们发现对user_a的两次d1的权限授予被合并成了一条，因此这时user_b不仅有了对d1的UPDATE的GRANT权限，也有了对d1的SELECT的GRANT权限，虽然我们授予user_b的d1的SELECT权限时，并没有授予其GRANT权限，但两条授权被合并了。

mysql> GRANT SELECT ON d1.* to user_b;
mysql> GRANT UPDATE ON d1.* to user_b WITH GRANT OPTION;
mysql> SHOW GRANTS FOR user_b;
+------------------------------------------------------------------+
| Grants for user_b@%                                              |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_b'@'%'                               |
| GRANT SELECT, UPDATE ON `d1`.* TO 'user_b'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------+

4.1.4、实验结论

对同一用户（这里的用户为用户名+主机名）在同一数据库的授权会进行合并。

5、总结

1. MySQL将权限与主机名和用户名的组合相关联，而不是只与用户名相关联。帐户的主机名部分，如果省略，默认为’%'。
2. MySQL5.7中对同一用户在同一数据库的授权会进行合并，因此如果允许该用户在数据库的某个权限上继续往下授权，则该用户就可以将自己在数据库上的所有权限继续往下授权。