访问控制语句 DCL

访问数据库必须要有相应权限的账户，root 是一个超级账户，需要为程序建立一般账户，这类语句就属于 DCL（Data Control Language）

• 建立数据库账户：create user
• 对用户授权：grant
• 收回用户权限：revoke

更详细的请参考 官网文档，下面进行一些常用知识的讲解；

主要就是针对账户权限的一些操作

创建数据库账户 create user

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']
    
-- 上面的语法怎么看？有中括号的是可选的，
-- 中括号里面的是啥意思？记得查看上面的官方文档，在语法的下面部分就有详细的说明
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}
-- ssl 加密链接配置
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}
-- 资源限制
resource_option: {
    MAX_QUERIES_PER_HOUR count		-- 该用户每小时查询数量
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]  -- 密码过期时间
  | PASSWORD HISTORY {DEFAULT | N}		-- 历史密码重复使用次数
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK		-- 账户锁定，锁定后无法登录
  | ACCOUNT UNLOCK
}

-- 还可以通过客户端执行 help 语句查询语法
mysql> help create user;

• user：由两部分组成 用户名@访问控制列表

  访问控制列表：决定用户可以从哪些客户端访问

-- 建立程序使用的数据库账户
-- @ 后面的 % 号就是通配符，比如 mysql 里面的 like 语法中的 % 
CREATE USER mc_class@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' by '1234567';

-- 示例 1：创建一个测试账户
-- 这里没有指定密码插件，会使用默认的，这个在上述语法中有写明不加插件的语法
CREATE USER mc_test@'%' IDENTIFIED BY '123456'

-- 示例 2：创建带有资源限制的账户
-- 这里限制该用户只能建立一个连接
CREATE USER mc_test1@'%' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 1;

-- 示例 3：测试上面带资源限制的账户，
-- 使用 mysql 客户端连接
[root@study ~]# mysql -u mc_test1  -p
Enter password: 
-- 连接成功一个后，换一个窗口再次连接
[root@study ~]# mysql -u mc_test1  -p
Enter password: 
ERROR 1226 (42000): User 'mc_test1' has exceeded the 'max_user_connections' resource (current value: 1)
-- 看到被拒绝了

用户创建之后，需要给用户权限，才能让它进行对表的读写等操作

数据库用户授权 grant

MySQL 的常用权限

• Insert：向表中插入数据的权限
• Delete：删除表中数据的权限
• update：修改表中数据的权限
• Select：查询表中数据的权限
• Execute：执行存储过程的权限

GRANT 使用

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...  -- 权限列表，可以通过  show privileges 获取所有的权限
    ON [object_type] priv_level		-- 在哪个对象中授权
    TO user_or_role [, user_or_role] ... -- 把权限授予哪个用户或则是角色
    [WITH GRANT OPTION]

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

下面进行示例练习，现在由于还没有到创建库表知识的章节，先以 mysql 库中的 user 表来演示。

user 表记录了 mysql 账户相关的设置，比如上面创建的账户就存储在这个表里面

-- mc_test 账户新建的时候没有授权，因此登录后，只能看到 information_schema 库
-- 范例 1：给 mc_test 授权，只能查询 mysql.user 表中的 user 和 host 列
mysql> GRANT select(user,host) ON mysql.user TO mc_test@'%';

-- 范例 2：给账户授权 mysql.user 表的所有列的 select 权限
mysql> GRANT select ON mysql.user TO mc_test@'%';
-- 上述设置之后，用 GUI 程序登录后，就可以看到 mysql 库中的 user 表，也能查询到信息了

-- 可以通过以下语句查看某一个用户授予的权限
mysql> show grants for 'mc_test'@'%';
+--------------------------------------------------------------------------+
| Grants for mc_test@%                                                     |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`                                      |
| GRANT SELECT, SELECT (`host`, `user`) ON `mysql`.`user` TO `mc_test`@`%` |
+--------------------------------------------------------------------------+
-- 上面就能看到我赋予的两个 select 权限

-- 范例 3：赋予该账户 mysql 库下的所有表的查询权限
mysql> GRANT select ON mysql.* TO mc_test@'%';

-- 范例 4：同时赋予该账户 select 和 update 权限
-- 多个使用逗号分隔
mysql> GRANT select,update ON mysql.* TO mc_test@'%';

GRANT 命令注意事项

• mysql 8.0 后，使用 grant 授权的数据库账户必须存在

• 使用 grant 命令授权的账户，必须具有 grant optional 的权限

  就是说，只能授予自己拥有的权限

• 获取命令帮助信息 /h grant 或 help grant

数据库用户授权收回 revoke

REVOKE
    priv_type [(column_list)]			-- 这里与 GRANT 中一致，只能收回已有的
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...

REVOKE PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user
  | role
}

练习

-- 查看该账户拥有的权限
mysql> show grants for 'mc_test'@'%';
+--------------------------------------------------------------------------+
| Grants for mc_test@%                                                     |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`                                      |
| GRANT SELECT ON `mysql`.* TO `mc_test`@`%`                               |
| GRANT SELECT, SELECT (`host`, `user`) ON `mysql`.`user` TO `mc_test`@`%` |
+--------------------------------------------------------------------------+

-- 收回 SELECT (`host`, `user`) 的权限
mysql> REVOKE SELECT(`host`, `user`) ON mysql.user from mc_test@'%';
-- 再次查看就发现被收回了
mysql> show grants for 'mc_test'@'%';
+-------------------------------------------------+
| Grants for mc_test@%                            |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_test`@`%`             |
| GRANT SELECT ON `mysql`.* TO `mc_test`@`%`      |
| GRANT SELECT ON `mysql`.`user` TO `mc_test`@`%` |
+-------------------------------------------------+