数据库安全与保护

作者: 薛落花随泪绽放 | 来源:发表于2022-08-20 10:23 被阅读0次

一、数据库完整性

是指数据库中数据的正确性和相容性。

1.完整性约束条件的作用对象

可以是列、元组和表。
①列级约束，主要指对列的类型、取值范围、精度等内容，包括
ⅰ对数据类型的约束，包括数据类型、长度、精度等
ⅱ对数据格式的约束。
ⅲ对取值范围或取值集合的约束
ⅳ对空值的约束

②元组约束，指元组中各个字段之间的互相约束。

③表级约束，指若干元组之间、关系之间的联系的约束。

2.定义与实现完整性约束

①实体完整性
通过主键约束和候选键约束来实现。
⑴主键约束
可以是表中的某一列，也可以是表中多个列所构成的一个组合(也称为复合主键)。规则。
ⅰ 每一个表只能定义一个主键
ⅱ 主键的值，也称为键值，必须能够唯一标志表中的每一行记录，且不能为NULL。
ⅲ 复合主键不能包含不必要的多余列。
ⅳ 一个列名在复合主键的列表中只能出现一次。

⑵候选键约束
与主键约束区别
ⅰ 一个表中只能创建一个主键，但可以定义若干个候选键
ⅱ 定义主键约束时，系统会自动产生PRIMARY KEY索引，而定义候选键约束时，系统会自动产生UNIQUE索引。

②参照完整性
通过在创建表或更新表的同时定义一个外键声明来实现。有两种方式。
ⅰ在表中某个列的属性定义后直接加上“reference_definition"语法项
ⅱ 在表中所有列的属性定义后面添加”FOREIGN KEY(index_col_name,...) reference_definition".

“reference_definition”语法

REFERENCES tb1_name(index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

index_col_name的语法
col_name[(length)][ASC | DESC]

reference_option的语法
RESTRICT | CASCADE | SET NULL | NO ACTION

例如

在数据库mysqltest中创建一个商品订单表orders，该表包含的订单信息有:订单号oder id、订购商品名order_prouct订购商品类型order_producttype订购客户id号 custid、订购时间 order date、订购价格order_price订购数量orderamount要求商品订单表orders中的所有订购客户信息均已在表 customers 中记录在册。

mysql> create table orders
    -> (
    -> order_id int not null auto_increment,
    -> order_product char(50) not null,
    -> order_product_type char(50) not null,
    -> cust_id int not null,
    -> order_date datetime not null,
    -> order_price double not null,
    -> order_amount int not null,
    -> primary key(order_id),
    -> foreign key(cust_id)
    -> references customers(cust_id)
    -> on delete restrict
    -> on update restrict
    -> );
Query OK, 0 rows affected (0.05 sec)

③用户定义的完整性
支持非空约束、CHECK约束和触发器。

3.命名完整性约束

命名语法

CONSTRAINT[symbol]

4.更新完整性约束

使用ALTER TABLE ADD CONSTRAINT语句。

二、触发器

是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。
主要作用是实现主键和外键不能保证的复杂的参照完整性和数据的一致性，从而有效地保护表中的数据。

1.创建触发器

使用CREATE TRIGGER语句

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tb1_name FOR EACH ROW trigger_body

例如，在数据库mysql test的表 customers中创建一个触发器 customers_insert_trigger用于每次向表customers插入一行数据时，将用户变量str的值设置为“onecustomer added!”.

mysql> create trigger xue.customers_insert_trigger after insert
    -> on xue.customers for each row set @str='one customer added';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into customers
    -> values(null,'万华','F','长沙市','芙蓉区');
Query OK, 1 row affected (0.05 sec)

mysql> select @str;
+--------------------+
| @str               |
+--------------------+
| one customer added |
+--------------------+
1 row in set (0.00 sec)

2.删除触发器

使用DROP

DROP TRIGGER [IF EXISTS][schema_name.]trigger_name

mysql> drop trigger if exists customers_insert_trigger;
Query OK, 0 rows affected (0.00 sec)

3.使用触发器

MySQL支持的有INSERT触发器、DELETE触发器和UPDATE触发器。
⑴INSERT触发器
需要注意的是
i)在INSERT 触发器代码内，可引用一个名为 NEW(不区分大小写)的虚拟表，来访问被插入的行。
ii)在BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值(只要具有对应的操作权限)。
iii)对于AUTO_INCREMENT 列，NEW在INSERT执行之前包含的是0值，在 INSERT 执行之后将包含新的自动生成值。

例6.4 在数据库mysq_test的表 customers中重新创建触发器 customers inserttrigker用于每次向表 custoners 插入一行数据时，将用户变量str的值设置为新插入客户的id 号。

mysql> create trigger customers_insert_trigger after insert
    -> on customers for each row set @str=NEW.cust_id;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into customers
    -> values(null,'曾伟','F','苏州市','相城区');
Query OK, 1 row affected (0.00 sec)

mysql> select @str;
+------+
| @str |
+------+
|  912 |
+------+
1 row in set (0.00 sec)

⑵DELETE触发器
需要注意的是
ⅰ在DELETE触发器代码内，可以引用一个名为OLD(不区分大小写)的虚拟表，来访问被删除的行。
ⅱOLD中的值全部是只读的，不能被更新。

⑶UPDATE触发器
需要注意的是
i)在UPDATE 触发器代码内，可以引用一个名为OLD(不区分大小写)的虚拟表访问以前(UPDATE 语句执行前)的值，也可以引用一个名为NEW(不区分大小写)的虚拟表访间新更新的值。
i)在BEFORE UPDATE 触发器中，NEW中的值可能也被更新，即允许更改将用于 UPDATE语句中的值(只要具有对应的操作权限)，
)OLD中的值全部是只读的，不能被更新，
iv)当触发器涉及对触发表自身的更新操作时，只能使用BEFORE UPDATE 触发器，而 AFTER UPDATE 触发器将不被允许。

例6.5 在数据库mysql test的表customers中创建一个触发器customers_update trigger，用于每次更新表 customers时，将该表中cust_address 列的值设置为 cust_contact 列的值。

mysql> create trigger customers_update_trigger before update
    -> on customers for each row
    -> set NEW.cust_address=OLD.cust_contact;
Query OK, 0 rows affected (0.01 sec)

 update customers set cust_address='武汉市'
    -> where cust_name='曾伟';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select cust_address from customers
    -> where cust_name='曾伟';
+--------------+
| cust_address |
+--------------+
| 苏州市       |
+--------------+
1 row in set (0.00 sec)

三、安全性与访问控制

数据库的安全性是指保护数据库以防止不合法的使用而造成数据泄露、更改或破坏。

1.用户账号管理

MySQL的用户账号和相关信息都在一个名为mysql的数据库中。

mysql> select user from mysql.user;
+------+
| user |
+------+
|      |
| root |
|      |
| root |
+------+
4 rows in set (0.02 sec)

⑴创建用户账号
语法

CREATE USER user[IDENTIFIED BY [ PASSWORD]'password']

例如，在MySQL 服务器中添加两个新的用户。其用户名分别为 zhangsan 和 liss。他们的主机名均为 localhost，用户 zhangsan 的口令设置为明文 123，用户lisi的口令设置为对明文456 使用PASSWORDO函数加密返回的散列值。
首先，在MySQL 的命令行客户端输入下面的 SQL 语句，得到明文 456 所对应的 PASSWORDO函数返回的散列值;

mysql> select password(456);
+-------------------------------------------+
| password(456)                             |
+-------------------------------------------+
| *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create user 'zhangsan'@'localhost' identified by '123',
    -> 'lisi'@'localhost' identified by password
    -> '*531E182E2F72080AB0740FE2F2D689DBE0146E04';
Query OK, 0 rows affected (0.06 sec)

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

⑵删除用户

DROP USER user[,user]...

mysql> drop user lisi;--没有明确给出主机名，系统默认%
ERROR 1396 (HY000): Operation DROP USER failed for 'lisi'@'%'

mysql> drop user lisi@localhost;
Query OK, 0 rows affected (0.00 sec)

此外，在DROP USER 语句的使用中，需要注意以下几点。
i)DROP USER 语句可用于除一个或多个MySQL 账户，并消除其权限。
ii)要使用DROP USER 语句，必须拥有MySQL中mysql数据库的DELETE 权限或全局 CREATE USER 权限。
iii)在DROP USER 语句的使用中，如果没有明确地给出账户的主机名，则该主机名会默认为是%。
lv)用户的删除不会影响到他们之前所创建的表、索引或其他数据库对象，这是因为 MySQL 并没有记录是谁创建了这些对象。

⑶修改用户账号

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

mysql> rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';
Query OK, 0 rows affected (0.00 sec)

⑷修改用户口令

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

mysql> select password('hello');
+-------------------------------------------+
| password('hello')                         |
+-------------------------------------------+
| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> set password for 'wangwu'@'localhost'
    -> ='*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119';
Query OK, 0 rows affected (0.00 sec)

2.账户权限管理

使用SHOW GRANTS FOR语句查看授权表

mysql> show grants for 'zhangsan'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhangsan@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

⑴权限的授予

GRANT
  priv_type[(column_list)]
    [,priv_type[(column_list)]]...
ON [object_type]priv_level
TO user_specification[,user_specification]...
[WITH GRANT OPTION]

在此语法格式中:
I)语法项“priv_type”用于指定权限的名称。

ii)可选语法项“column_list”用于指定权限要授予给表中哪些具体的列。

iii)语法项“ON子旬”用于指定权限授予的对象和级别，例如可在关键字“ON”后面给出要授予权限的数据库名或表名等。

iv)可选项“object_type”用于指定权限授予的对象类型，包括表、函数和存储过程，分别用关键字“TABLE”“FUNCTION”和“PROCEDURE"标识。

v)语法项“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 指定存储过程名或函数名。

vi)语法项“TO子句”用来设定用户的口令，以及指定被授予权限的用户user。

vii)语法项“user_specification”是TO子句中的具体描述部分，其常用的语法格式是:
user[IDENTIFIED BY[PASSWORD]'password']

viii)语法项“WITH 子句”为可选项，用于实现权限的转移或限制。

例如。

mysql> grant select(cust_id,cust_name)
    -> on xue.customers
    -> to 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update
    -> on xue.customers
    -> to 'liming'@'localhost' identified by '123',
    -> 'huang'@'localhost' identified by '789';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all
    -> on xue.*
    -> to 'wangwu'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant create user
    -> on *.*
    -> to 'wangwu'@'localhost';
Query OK, 0 rows affected (0.00 sec)

此外，关于GRANT语句中语法项“priv_type”的使用，需要注意以下几点。
1)授予表权限时，语法项“priv_type”可以指定为以下值。
·SELECT:表示授予用户可以使用SELECT语句访问特定表的权限。

●INSERT:表示授予用户使用INSERT语句向一个特定表中添加数据行的权限。

●DELETE:表示授予用户可以使用DELETE 语句向一个特定表中删除数据行的权限

●UPDATE:表示授予用户可以使用UPDATE 语句修改特定数据表中值的权限

●REFERENCES:表示授予用户可以创建一个外键来参照特定数据表的权限。

●CREATE:表示授予用户可以使用特定的名字创建一个数据表的权限。

●ALTER:表示授予用户可以使用ALTER TABLE 语句修改数据表的权限。

●INDEX:表示授予用户可以在表上定义索引的权限。

●DROP;表示授予用户可以删除数据表的权限。

●ALL或ALLPRIVILEGES:表示所有的权限名。

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 OUTINE表示用户可以为特定的数据库创建存储过程和存储的数多权限。
●ALTERROUTINE;表示授予用户可以更新和删除数据库中已有的存储过程和存储承数等权限。
●EXECUTE ROUTINE:表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
●LOCK TABLES:表示授予用户可以锁定特定数据库的已有数据表的权限
●ALL 或 ALL PRIVILEGES:表示以上所有的权限名。
4)最有效率的权限是用户权限。授予用户权限时，语法项“priv_type”除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值。
●CREATE USER:表示授予用户可以创建和删除新用户的权限
●SHOW DATABASES:表示授予用户可以使用SHOW DATABASES 语句查看所有己
有的数据库的定义的权限。

⑵权限的转移
例如

mysql> grant select,update
    -> on xue.customers
    -> to 'zhou'@'localhost' identified by '123'
    -> with grant option;
Query OK, 0 rows affected (0.00 sec)

⑶权限的撤销
撤销一个用户权限，不删除该用户语法

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

回收特定用户的所有权限语法

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

mysql> revoke select
    -> on xue.customers
    -> from 'zhou'@'localhost';
Query OK, 0 rows affected (0.00 sec)

四、事务与并发控制

1. 事务的概念

是用户定义的一个数据操作序列。
用户显式定义事务的语句有BEGIN TRANSACTION、COMMIT和ROLLBACK。
事务通常以BEGIN TRANSACTION语句开始，以COMMIT语句或ROLLBACK结束。

事务中的操作一般是对数据的更新操作，包括增、删、改。

2. 事务的特征

⑴原子性

⑵一致性

⑶隔离性

⑷持续性。

3.并发操作问题

事务是并发控制的基本单位。

当多个事务交错执行时，可能出现不一致问题，称为并发操作问题，有丢失更新、不可重复读和读“脏”数据。

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

4.封锁

基本思想：需要时，事务通过向系统请求对它所希望的数据对象加锁，以确保它不被非预期改变。
⑴锁
实质上是允许或阻止一个事务对一个数据对象的存取特权。
基本的封锁类型有：排他锁(Exclusive Lock,X锁)和共享锁(Shared Lock,S锁)。

⑵用封锁进行并发控制

⑶封锁的粒度
通常以粒度来描述封锁的数据单元的大小。

⑷封锁的级别

⑸活锁与死锁
预防死锁的办法
①一次性锁请求

②锁请求排序

③序列化处理

④资源剥夺

⑹可串行性
是并发执行的正确性准则。

⑺两段封锁法

五、备份与恢复

影响数据库的因素：
ⅰ计算机硬件故障
ⅱ计算机软件故障
ⅲ病毒
ⅳ人为误操作
ⅴ自然灾害
ⅵ盗窃

备份是指通过导出数据或者复制表文件的方式来制作数据库的复本；
恢复是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

数据库恢复是以备份为基础。

1. 使用SELECT INTO...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']

2. 使用LOAD DATA...INFILE语句恢复数据

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

例如,备份

mysql> select * from xue.customers
    -> into outfile 'D:/backup/backupfile.txt'
    -> fields terminated by ','
    -> optionally enclosed by ""
    -> lines terminated by '?';
Query OK, 5 rows affected (0.00 sec)

在写出的时候会出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement的错误解决方法：

出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。

首先使用下面的命令 show variables like '%secure%'; 查看数据库的存储路径。如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。

这时候就可以在 my.ini 文件的 [mysqld] 代码下增加secure_file_priv=,在服务中重启 mysql 就可以了。

恢复

mysql> load data infile 'D:/backup/backupfile.txt'
    -> into table xue.customers_copy
    -> fields terminated by ','
    -> optionally enclosed by ""
    -> lines terminated by '?';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0