安全性与访问控制

用户账号管理

MySQL 的用户账号及相关信息都存储在一个名为 mysql 的 MySQL 数据库中，这个数据库里有一个名为 user 的数据表，包含了所有用户账号，并且它用一个名为 user 的列存储用户的登录名。可以使用下面的 SQL 语句查看 MySQL 数据库的使用者账号。

创建用户账号
常用的语法格式是：

CREATE USER user[IDENTIFIED BY [PASSWORD]‘password’ ]

语法格式介绍如下：
（1）语法项“user”指定创建用户账号，其格式为‘user_name‘@’host name’。其中， user_name 表示用户名， host_name 表示主机名，即用户连接 MySQL时所在主机的名字。如果在创建的过程中，只给出了账户中的用户名，而没指定主机名，则主机名会默认为是“%”，其表示一组主机。
（2）语法项“IDENTIFIED BY 子句”是可选项，用于指定用户账号对应的口令，若该用户账号无口令，则可省略此子句。
（3）关键字“PASSWORD”是可选项，用于指定散列口令，即若使用明文设置口令时，而忽略 PASSWORD 关键字；如果不想以明文设置口令，且知道PASSWORD()函数返回给密码的散列值，则可以在此口令设置语句中指定此散列值，但需要加上关键字 PASSWORD。
（4）语法项**“password”指定用户账号的口令**，其在 IDENTIFIED BY 关键字或PASSWORD 关键字之后。设定的口令值可以是只有字母和数字组成的明文，也可以是通过 PASSWORD()函数得到散列值。

在 CREATE USER 语句的使用中，需要注意以下几点。
（1）要使用 CREATE USER 语句，必须拥有 MySQL 中 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
（2）使用 CREATE USER 语句创建一个用户账户后，会在系统自身的 mysql 数据库的 user 表中添加一条新记录。如果创建的账户已经存在，则语句执行会出现错误。
（3）如果两个用户具有相同的用户和不同的主机名， MySQL 会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合。
（4）如果在 CREATE USER 语句的使用中，没有为用户指定口令，那么 MySQL 允许该用户可以不使用口令登录系统，然而从安全的角度而言，不推荐这种做法。
（5） 新创建的用户拥有的权限很少。他们可以登录到 MySQL，只允许进行不需要权限的操作，比如使用 SHOW 语句查询所有存储引擎和字符集的列表等，不能使用 USE 语句来让其他用户已经创建了的任何数据库成为当前数据库，因而无法访问那些数据库的表。
2. 删除用户
为了删除一个或多个用户账号以及相关的权限，可以使用 DROP USER 语句，其使用的语法格式是：

DORP USER user[， user]…

在 DROP USER 语句的使用中，需要注意以下几点：
（1） DROP USER 语句可用于删除一个或多个 MySQL 账户，并消除其权限。
（2）要使用 DROP USER 语句，必须拥有 MySQL 中 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。
（3）在 DROP USER 语句的使用中，如果没有明确地给出账户的主机名，则该主机名会默认为是%。
（4）用户的删除不会影响到他们之前所**创建的表、索引或其他数据库对象，**这是因为 MySQL 并没有记录是谁创建了这些对象。
3. 修改用户账号
可以使用 RENAME USER 语句修改一个或多个已经存在的 MySQL 用户账号，其使用的语法格式是：

RENAME USER old_user TO new_user[,old_user TO new_user]…

在 RENMAE USER 语句的使用中，需要注意以下几点。
（1） RENAME USER 语句对于原有 MySQL 账户进行重命名。
（2）要使用 RENAME USER 语句，必须拥有 MySQL 中的 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限。
（3）倘若系统中旧账户不存在或者新账户已存在，则语句执行会出现错误。
4. 修改用户口令
可以使用 SET PASSWORD 语句修改一个用户的登录口令，其使用的语法格式
是：

SET PASSWORD[FOR user]=
{
PASSWORD(‘new_password’)
|’encrypted password’
}

在SET PASSWORD 语句的使用中，需要注意以下几点。
（1）若不加上 FOR 子句，表示修改当前用户的口令；若加上 FOR 子句，表示修改账户为 user 的用户口令 ，其中 user 的格式必须以*’user_name’@’host_name’*的格式给定， user_name 为账户的用户名，host_name 为账户所在的主机名。该账户必须在系统中存在，否则语句执行会出现错误。
（2）只能使用选项 PASSWORD(‘new_password’)和’encrypted password’中的一项，且必须使用其中的某一项。

账户权限管理

权限的授予
新建的 MySQL 用于必须被授权，可以使用 GRANT 语句来实现，常用的语法格式是：

GRANT
Pri_type[(column_list)]
[,pri_type[(column_list)]]…
ON[object_type]priv_level
TO user_specification[,user_specification]…
[WITH GRANT OPTION]

在此语法中：
（1）语法项“pri_type”用于指定权限的名称。
（2）可选语法**“column_list”用于指定权限要授予给表中哪些具体的列。**
（3）语法项**“ON 子句**”用于指定权限授予的对象和级别。
（4）可选项“object_type”用于指定权限授予的对象类型，包括表、函数和存储过程，分别用关键字“TABLE”“FUNCTION”和“PROCEDURE”标识。
（5）语法项**“priv_level”:用于指定权限的级别**，其可以授予的权限有这样几个：列权限、表权限、数据库权限和用户权限。相应地，在 GRANT 语句中可用于指定权限级别的值有这样几类格式： “*”表示当前数据库中的所有表；“.”表示所有数据库中的所有表； “db_name”表示某个数据库中的所有表， db_name 指定数据库名*； “db_name.tbl_name”表示某个数据库中的某个表或视图， db_name 指定数据库名， tbl_name 指定表名或视图名；“tbl_name” 表示某个 表或视图 ， tbl_name 指定 表名或视图名 “db_name.routine_name”表示某个数据库中的某个存储过程或函数，**routine_name 指定存储过程名或函数名。
（6）语法项“TO 子句”用来设定用户的口令，以及指定被授予权限的用户 user。若在 TO 子句中给系统中存在的用户指定口令，则新密码会将原密码覆盖**；如果权限被授予给一个不存在的用户， MySQL 会自行执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户指定口令。由此可见，GRANT 语句亦可以用于创建用户账号。
（7）语法项**“user_specification”是 TO 子句中的具体描述部分**，其常用的语法格式是：

 user[IDENTIFIED BY[PASSWORD]’password’]

（8）语法项**“WITH 子句”为可选项，用于实现权限的转移或限制。**
关于 GRANT 语句中语法项“priv_type”的使用，需要注意以下几点。
（1）授予表权限时，语法项“priv_type”可以指定为以下值。

SELECT：表示授予用户可以使用 SELECT 语句访问特定表的权限。
INSERT：表示授予用户使用 INSERT 语句向一个特定表中添加数据行的权限。
DELETE: 表示授予用户可以使用 DELETE 语句向一个特定表中删除数据行的权限。
UPDATE: 表示授予用户可以使用 UPDATE 语句修改特定数据表中值的权限。
REFERENCES: 表示授予用户可以创建一个外键来参照特定数据表的权限。
CREATE：表示授予用户可以使用特定的名字创建一个数据表的权限。
ALTER：表示授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
INDEX:表示授予用户可以在表上定义索引的权限。
DROP：表示授予用户可以删除数据表的权限。
ALL 或 ALL PRIVILEGES：表示所有的权限名。
（2）授予权限时，语法项**“priv_type**”的值只能指定为SELECT、INSERT和UPDATE，同时权限的后面需要加上列名列表 column_list。
（3）授予数据库权限时，语法项**“priv_type”**可以指定为以下值。
SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE: 表示授予用户可以使用 DELETE 语句向删除特定数据库中所有表的数据行的权限。
UPDATE: 表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES: 表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE：表示授予用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
INDEX:表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES: 表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数等权限。
ALTER ROUTINE: 表示授予用户可以更新和删除数据库中已有的存储过程和存储函数等权限。
EXECUTE ROUTINE: 表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOOK TABLES: 表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES：表示所有的权限名。
（4）最有效率的权限是用户权限。授予用户权限时，语法项**“priv_type”**除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值。
CREATE USER：表示授予用户可以创建和删除新用户的权限。
SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

权限的转移
权限的转移可以通过 GRANT 语句中使用 WITH 子句来实现。如果将 WITH 子句指定为关键字**“WITH GRANT OPTION”，则表示 TO 子句中所指定的所有用户都具有把自己所拥有的权限授予给其他用户的权利，**而无论那些其他用户是否拥有该权限。
用户的撤销
当需要回收某些特定的权限时，可使用的语法格式是：

REVOKE
priv_type[(column_list)]
[,priv_type[(column_list)]]…
ON[object_type]priv_level
FROM user[,user]…

当需要收回特定用户的所有权限时，可使用的语法格式是：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]…

事务与并发控制

事务的概念

所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整地工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。
事务与程序的不同：程序是静止的，事务是动态的，是程序的执行而不是程序本身；同一程序的多个独立执行可以同时进行，每一步执行则是一个不同的事务。
在 SQL 中，用户显示定义事务的语句一般有三条： BEGIN TRANSACTION、COMMIT 和 ROLLBACK，且事务通常是以 BEGIN TRANSACTION 语句开始，以 COMMIT 语句或 ROOLBACK 语句结束。
事务中的操作一般是对数据的更新操作，包括增、删、改。

事务的特征

原子性：事务的原子性保证事务包含的一组更新操作是原子不可分的，即事务是不可分割的最小工作单位，所包含的这些操作是一个整体。
一致性：一致性要求事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性状态转变到另一个一致性状态。其中，数据库的一致性状态是一种以一致性规则为基础的逻辑属性。
隔离性：隔离性要求事务是彼此独立的、 隔离的，即一个事务的执行不能被其他事务所干扰，一个事务对数据库变更的结果必须在它 COMMIT 后，另一个事务才能存取。
持续性：持续性也称为永久性，是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，且接下来的其他操作或故障不应该对其执行结果又任何影响。

并发操作问题

事务是并发控制的基础单位，保证事务的 ACID 特征是事务处理的重要任务，而事务的 ACID 特征可能遭到破坏的**原因之一是多个事务对数据库的并发操作造成的。**为了保证事务的隔离性和一致性， DBMS 需要对并发操作进行正确调度。其中，完整性检验可以保证一个事务单独执行时，若输入的数据库状态是正确的，则其输出的数据库状态也是正确的。但当多个事务交错执行时，可能出现不一致问题，这也称为并发操作问题，典型的有如下三种：丢失更新、不可重复读和读“脏”数据。

丢失更新
设有两个事务 T1 和 T2，当它们同时读入同一数据并加以修改时，事务 T2 的提交结果会破坏事务 T1 提交的结果，由此导致事务 T1 的修改被丢失。这就是一种由于对数据的并发操作而带来的数据不一致。
不可重复读
不可重复读包括三种情况：
（1）事务 T1 读取某一数据后，事务 T2 对其做了修改，当事务 T1 再次读该数据时，得到与前一次不同的值。
（2）事务 T1 按一定条件从数据库中读取了某些数据记录后，事务 T2 删除了其中部分记录，当事务 T1 再次按相同条件读取数据时，发现某些记录神秘地消失了。
（3）事务 T1 按一定条件从数据库中读取某些数据记录后，事务 T2 插入了一些记录，当事务 T1 再次按相同条件读取数据时，发现多了一些记录。
读“脏”数据
设有两个事务 T1 和 T2，读“脏”数据是指，事务 T1 修改某一数据，并将其写回磁盘，事务 T2 读取同一数据后，事务 T1 由于某种原因被撤销，这时事务 T1已修改过的数据恢复原值，事务 T2 读到的数据就与数据库中的数据不一致，则事务 T2 读到的数据就为“脏”数据，即不正确的数据。

解决并发操作所带来的数据不一致性问题的方法有锁、时间戳、乐观控制法和多版本并发控制等。

封锁

封锁是最常用的并发控制技术，它的基本思想是：需要时，事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变。

锁
一个锁实际上就是允许或组织一个事务对一个数据对象的存取特权。
基本的封锁类型有两种：排他锁和共享锁。
用封锁进行并发控制
工作原理是：
（1）若事务 T 对数据 D 加了 X 锁，则所有别的事务对数据 D 的锁请求都必须等待直到事务 T 释放锁。
（2）若事务 T 对数据 D 加了 S 锁，则别的事务还可对数据 D 请求 S 锁，而对数据 D 的 X 锁请求必须等待直到事务 T 释放锁。
（3）事务执行数据库操作时都要先请求相应的锁，即对读请求 S 锁，对更新（插入、删除、修改）请求 X 锁。这个过程一般是由 DBMS 在执行操作时自动隐含地进行。
（4）事务一直占有获得的锁直到结束（COMMIT 或 ROLLBACK）时释放。因此，利用封锁机制可以解决并发操作所带来的三个不一致问题。
封锁的粒度
以粒度来描述封锁的数据单元的大小。 DBMS 可以决定不同粒度的锁。由最底层的数据元素到最高层的整个数据库，粒度越细，并发性就越大，但软件复杂性和系统开销也就越大。
封锁的级别
封锁的级别又称为一致性级别或隔离度。
由各种锁的类型与其封锁期限组合可形成不同的封锁级别：
（1） 0 级封锁
（2） 1 级封锁
（3） 2 级封锁
（4） 3 级封锁
活锁与死锁
**在并发事务处理过程中，由于锁会使一事务处于等待状态而调度其他事务处理，因而该事务可能会因优先级低而永远等待下去，这种现象称为“活锁”。**活锁问题的解决与调度算法有关，一种最简单的办法是“先来先服务”。
两个以上事务循环等待被同组中另一事务锁住的数据单元的情形，称为“死锁”。 DBMS 需要提供死锁预防、死锁检测和死锁发生后的处理技术与方法。预防死锁的办法在操作系统中已普遍讨论，其中主要有如下几种：
（1）一次性锁请求
（2）锁请求排序
（3）序列化处理
（4）资源剥夺
可串行性
一组事务的一个调度就是它们的基本操作的一种排序。若在一个调度中，对于任意两个事务 T1 和 T2，要么 T1 的所有操作都在 T2 所有操作之前，要么反之，则该调度室串行的，因而是正确的。
通常，在数据库系统中，可串行性就是并发执行的正确性准则，即当且仅当一组事务的并发执行调度是可串行化，才认为它们是正确的。
两段封锁法
采用两段封锁法是一种最简单而有效的保障封锁其调度是可串行性的方法。
两段锁协议规定在任何一个事务中，所有加锁操作都必须在所有释放锁操作之前。其中，事务划分成如下两个阶段。
（1）发展（Growing）或加锁阶段
（2）收缩（Shrinking）或释放锁阶段
定理：遵循两段锁协议的事务的任何并发调度都是可串行化的。

备份与恢复

会造成数据库运行事务异常中断的因素可能是：

计算机硬件故障
计算机软件故障
病毒
人为误操作
自然灾害
盗窃
备份和恢复策略来保证数据库中数据的可靠性和完整性。
数据备份是指通过导出数据或者复制表文件的方式来制作数据库的复本；数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。
数据库的恢复是以备份为基础的，它是与备份相对应的系统维护和管理操作。

使用 SELECTION…OUTFILE 语句备份数据

常用语法格式是：

SELECT*INTO OUTFILE’file_name’export_options
|INTO DUMPFILE’file_name’

“export_options”的格式是：

[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY’char’]
[ESCAPED BY’char’]
]
[LINES TERMINATED BY’string’]

在此语法格式中：
（1）语法项**“file_name”指定数据备份文件的名称**。
（2） 导出语句中使用关键字“OUTFILE”时，可以在语法项“expert_options”中加入以下两个自选的子句，即 FIFLDS 子句和 LINES 子句，它们的作用是决定数据行在备份文件中存储的格式。如果 FIELDS 和 LINES 子句都不指定，则默认声明的是子句 “FIELDS TERMINATED BY’\t’ENCLOSED BY”ESCAPED
BY’\’LINES TERMINATED BY’\n’”。
（3）语法项“FIELDS 子句”中有三个亚子句，分别是**“TERMINATED BY 子句”****“ [OPTIONALLY] ENCLOSED BY 子句”和“ESCAPED BY 子句”。如果指定了FIELDS 子句，则这三个亚子句中至少要求指定一个。其中， TERMINATED BY 子句用来指定字段值之间的符号**。
（4）在 LINES 子句中使用关键字“TERMINATED BY”指定一个数据行结束的标志。
（5） 导出语句中使用的是关键字“DUMPFILE”，而非“OUTFILE”时，导出的备份文件里面所有的数据行都会彼此紧挨着放置，即值和行之间没有任何标记。

使用 LOAD DATA…INFILE 语句恢复数据

常用语法格式是：

LOAD DATA INFILE ‘file_name.txt’
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY ’string’]
[OPTIONALLY] ENCLOSED BY ’char’]
[ESCAPED BY ’char’]
]
[LINES
[STARTING BY ’string’]
[TERMINATED BY ’string’]
]

此语法格式中：
（1）语法项**“file_name”指定待导入的数据库备份文件名**，文件中保持了待载入数据库的所有数据行。
（2）语法项**“tbl_name”指定需要导入数据的表名**。
（3）此处的 FIELDS 子句和 SELECT…INTO OUTFILE 语句中的 **FIELDS 子句类似，**用于判断字段之间和数据行之间的符号。
（4）语法项“LINES子句”中的TERMINATED BY 亚子句用来指定一行结束的标志；STARTING BY 亚子句则指定一个前缀，导入数据行时，忽略数据行中的该前缀和前缀之前的内容。如果某行不包括该前缀，则整个数据行被跳过。