安全性控制

安全性一般要从全方位、多层次考虑具体的安全措施，比如不仅要考虑MySQL的安全性，还要考虑服务器主机的安全性(如防eavesdropping, altering, playback, 以及DoS攻击等）。本实训仅考察MySQL安全性中的一部分：存取控制。

MySQL采用自主存取控制策略，主要机制有：用户，数据对象，权限，角色，授权，收回权限等。

MySQL还支持加密连接(SSL和RSA),以及支持第三方安全插件。

用户和权限

任务描述

本关任务：
在金融应用场景数据库环境中，创建用户，并给用户授予指定的权。

相关知识

为了完成本关任务，你需要掌握：

  1. MySQL的安全控制机制
  2. create user语句的使用
  3. grant和revoke语句的使用。

MySQL的安全控制机制

与大多数商用DBMS一样，MySQL采用自主存取控制(DAC)机制进行安全性管理。通过用户，数据对象，权限，授权，收回权限等要素进行存取控制。另外，为了方便批量授权给同一类用户，引入了角色。

用户(User)

MySQL创建用户的语句：
create user 用户名 identified by 用户登录密码；

通常用户名可包含域名，限定用户在该域名内登录再有效。例：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
该语句创建用户jeffrey,密码为'password'，仅限在MySQL服务器本机上登录才有效。用户名与域合起来，被称为账户(account)。
注意不要写成：'jeffrey@localhost'，它代表账户：
'jeffrey@localhost'@'%'
意即用户名为jefrrey@localhost，在任何机器上登录都有效。两者的含义完全不同。

省略域名，即默认为’%‘,表示用户可远程登录。在本实训中，可以省略域，这时，用户名可以不加引号。

drop user语句可删除用户。用户被删除时，该用户拥有的权限自动被收回。
alter user语句可重置用户密码：
ALTER USER user IDENTIFIED BY 'new_password';

MySQL在安装时，初始用户名为root，此为系统管理员用户，其余用户均由root创建，并授权。经授权的用户也可以创建用户。

在生产环境中，除了管理员，其它人应当使用由管理员创建的账户，不得使用root帐户。出于安全考虑，一般root用户也仅限在服务器本地登录，不轻易开放远程登录。

权限

MySQL常用的权限有：

  • all: 所有权限(grant option除外)
  • alter: alter table权限
  • alter routine: alter 存储过程
  • create: create database/table
  • create role: create role
  • create foutine: create 存储过程和函数
  • create user： create/alter/rename/drop user
  • create view: create view
  • delete: delete语句
  • drop: drop database/table
  • drop role: drop role
  • execute: 调用存储过程或函数
  • index：create/drop index
  • insert: insert语句
  • select: select语句
  • trigger: 触发器相关操作
  • update: update语句 等。 select,update,insert,delete还可以用在列上，如select(c_id),update(b_balance)等。

角色(Role)

角色是权限的集合。如果有一组人(承担相同职责的小组,或者説小组成员扮演相同的角色)应该被授予一组相同的权限，不妨创建一个角色，将那组权限授予该角色，然后再将角色授予该组的每个成员。这比一个个地给每个组员授予一批权限要方便得多。

创建角色的语句：
CREATE ROLE [IF NOT EXISTS] role [, role ] ...
一次可以创建多个角色。

删除角色：
DROP ROLE [IF EXISTS] role [, role ] ...
角色被删除后，拥有该角色的用户立即失去角色定义的权限组合。不过，如果用户同时拥有多个角色，两个角色代表的权限集合如果有交集，则该用户仍拥有交集代表的权限。

GRANT授权语句

以下语句授予权限给用户或角色：
grant 权限[,权限] ... on 数据库对象 to user|role,[user|role]... [with grant option]
可以同时将多个权限授予多个用户或角色。
with grant option表示被授权用户可以传播权限，即授权该用户将其拥有的权限（之前获得的权限，通过本语句获得的权限，以及今后获得的权限）再授予其它用户。

以下语句授予角色所代表的权限集给用户或角色：
GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]

总之，GRANT语句可以将权限或角色（权限集合）授予用户或角色。但是不能将权限和角色混合授予用户(或角色)。不过，你可以分开用两条不同的GRANT语句来实现：直接授权语句有关键词ON，间接授权(角色代表的权限集合)语句不带ON关键词。

REVOKE收回权限语句

以下语句将对象的权限从用户或角色手中收回：
revoke 权限[,权限]... on 数据库对象 from user|role[,user|role]...

下列语句把role所代表的权限集合从用户或角色中收回：
REVOKE role [, role ] ... FROM user_or_role [, user_or_role ] ...

如果用户本身拥有多个角色所代表的权限集合，而这些集合存在交集，收回其中部分角色代表的权限集后，用户可能仍拥有那个角色所代表的部分权限(交集代表的那部分权限)。

编程要求

在右侧代码文件编辑器填写语句，完成以下创建用户和授权操作：
(1) 创建用户tom和jerry，初始密码均为'123456'；
(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；
(3) 授予用户jerry修改银行卡余额的权限；
(4) 收回用户Cindy查询银行卡信息的权限。

说明：

  • 本实训的运行环境是之前查询实训中曾经使用过的金融场景数据库。
  • 除root用户外，已预设了一个名为Cindy的用户，Cindy已被root授予了一定权限。
  • 由于MySQL的用户密码采用加密存储，不可识别，评测程序不会也无法比较密码是否按要求设置，实际上密码可以任意设置。如果你想用创建的用户在命令行登录到MySQL检验的话，自己记得所设密码即可。
  • 命令行窗口与评测用的是不同的MySQL实例，你需要自己还原数据库(/data/workspace/myshixun/src/test1/finance1.sql)

附上相关表结构：
表1 client(客户表)

字段名称 数据类型 约束 说明
c_id INTEGER PRIMARY KEY
c_name VARCHAR(100) NOT NULL
c_mail CHAR(30) UNIQUE 客户邮箱
c_id_card CHAR(20) UNIQUE NOT NULL 客户身份证
c_phone CHAR(20) UNIQUE NOT NULL 客户手机号
c_password CHAR(20) NOT NULL 客户登录密码

表2 bank_card(银行卡)

字段名称 数据类型 约束 说明
b_number CHAR(30) PRIMARY KEY 银行卡号
b_type CHAR(20) 无 银行卡类型(储蓄卡/信用卡)
b_c_id INTEGER NOT NULL FOREIGN KEY 所属客户编号,引用自client表的c_id字段。
b_balance NUMERIC(10,2) NOT NULL 余额,信用卡余额系指已透支的金额

测试说明

评测程序将运行脚本，然后检查所有用户及其权限，当所有用户及其权限都正确时，即可通关。

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
# 请填写语句，完成以下功能：
#(1) 创建用户tom和jerry，初始密码均为'123456'；
create user 'tom'@'%' identified by '123456';
create user jerry@'%' identified by '123456';

#(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；
grant select(c_mail, c_name, c_phone) ON finance.client to tom@'%' with grant option;

#(3) 授予用户jerry修改银行卡余额的权限；
grant update(b_balance) on finance.bank_card to jerry@'%';

#(4) 收回用户Cindy查询银行卡信息的权限。
revoke all on finance.bank_card from Cindy@'%';

用户、角色与权限

任务描述

本关任务：
创建角色，授予角色一组权限，并将角色代表的权限授予指定的一组用户。

相关知识

为了完成本关任务，你需要掌握：

  1. 创建角色；
  2. 通过角色授权。

编程要求

在右侧代码文件编辑器填写语句，完成以下创建用户和授权操作：
(1) 创建角色client_manager和fund_manager；
(2) 授予client_manager对client表拥有select,insert,update的权限；
(3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限；
(4) 授予fund_manager对fund表的select,insert,update权限； (5) 将client_manager的权限授予用户tom和jerry； (6) 将fund_manager权限授予用户Cindy`.

说明：
在本关开始前，用户Cindy,tom,jerry的所有权限都已提前被revoke。

测试说明

评测程序将运行脚本，然后检查所有用户、角色及其权限，当所有用户、角色及其权限都正确时，即可通关。

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 请填写语句，完成以下功能：
# (1) 创建角色client_manager和fund_manager
create role client_manager,fund_manager;

# (2) 授予client_manager对client表拥有select,insert,update的权限；
grant select,insert,update on finance.client to client_manager;

# (3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限；
grant select(b_number,b_type,b_c_id) on finance.bank_card to client_manager;

# (4) 授予fund_manager对fund表的select,insert,update权限；
grant select,insert,update on finance.fund to fund_manager;

# (5) 将client_manager的权限授予用户tom和jerry；
grant client_manager to tom,jerry;

# (6) 将fund_manager权限授予用户Cindy.
grant fund_manager to Cindy;