【MySQL】-安全性控制

第一关：用户和权限

用户(User)
MySQL创建用户：

create user 用户名 identified by 用户登录密码；

通常用户名可包含域名，限定用户在该域名内登录再有效。

alter user语句可重置用户密码：

ALTER USER user IDENTIFIED BY ‘new_password’;

权限

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语句可以将权限或角色（权限集合）授予用户或角色。但是不能将权限和角色混合授予用户(或角色)。

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查询银行卡信息的权限。

表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 NULLE	客户登录密码

表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	余额信用卡余额系指已透支的金额

代码实现

输出结果

user
Cindy
jerry
tom
Grants for Cindy@%
GRANT USAGE ON . TO Cindy@%
Grants for tom@%
GRANT USAGE ON . TO tom@%
GRANT SELECT (c_mail, c_name, c_phone) ON finance.client TO tom@% WITH GRANT OPTION
Grants for jerry@%
GRANT USAGE ON . TO jerry@%
GRANT UPDATE (b_balance) ON finance.bank_card TO jerry@%
user table_name table_priv column_priv
jerry bank_card Update
tom client Grant Select
user table_name column_name column_priv
jerry bank_card b_balance Update
tom client c_mail Select
tom client c_name Select
tom client c_phone Select
。

第二关：用户、角色与权限

编程任务

完成以下创建用户和授权操作：

(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.

代码实现

输出结果

user
Cindy
client_manager
fund_manager
jerry
tom
Grants for client_manager@%
GRANT USAGE ON . TO client_manager@%
GRANT SELECT (b_c_id, b_number, b_type) ON finance.bank_card TO client_manager@%
GRANT SELECT, INSERT, UPDATE ON finance.client TO client_manager@%
Grants for fund_manager@%
GRANT USAGE ON . TO fund_manager@%
GRANT SELECT, INSERT, UPDATE ON finance.fund TO fund_manager@%
Grants for Cindy@%
GRANT USAGE ON . TO Cindy@%
GRANT SELECT, INSERT, UPDATE ON finance.fund TO Cindy@%
GRANT fund_manager@% TO Cindy@%
Grants for tom@%
GRANT USAGE ON . TO tom@%
GRANT SELECT (b_c_id, b_number, b_type) ON finance.bank_card TO tom@%
GRANT SELECT, INSERT, UPDATE ON finance.client TO tom@%
GRANT client_manager@% TO tom@%
Grants for jerry@%
GRANT USAGE ON . TO jerry@%
GRANT SELECT (b_c_id, b_number, b_type) ON finance.bank_card TO jerry@%
GRANT SELECT, INSERT, UPDATE ON finance.client TO jerry@%
GRANT client_manager@% TO jerry@%
user table_name table_priv column_priv
client_manager bank_card Select
client_manager client Select,Insert,Update
fund_manager fund Select,Insert,Update
user table_name column_name column_priv
client_manager bank_card b_c_id Select
client_manager bank_card b_number Select
client_manager bank_card b_type Select