数据库系统原理之数据库安全与保护

浪子之升

数据库安全与保护

第一节 数据库完整性

• 数据库完整性是指数据库中数据的正确性和相容性。
  
• 数据完整性约束是为了防止数据库中存在不符合语义的数据，为了维护数据的完整性，DBMS 必须提供一种机制来检查数据库中的数据，以判断其是否满足语义规定的条件。
  
• 这些加在数据库数据之上的语义约束条件就是数据完整性约束。
  
• DBMS 检查数据是否满足完整性约束条件的机制就称为完整性检查。
  

一、完整性约束条件的作用对象

• 完整性约束条件是完整性控制机制的核心。
  
• 完整性约束条件的作用对象可以是列、元组和表。
  

（1）列级约束

列级约束主要指对列的类型、取值范围、精度等的约束，具体包括如下内容：

• 对数据类型的约束，其包括数据类型、长度、精度等。
  
• 对数据格式的约束。
  
• 对取值范围或取值集合的约束。
  
• 对空值的约束。
  

（2）元组约束

元组约束指元组中各个字段之间的相互约束。
（3）表级约束

表级约束指若干元组之间、关系之间的联系的约束。
二、定义与实现完整性约束

1 实体完整性

在MySQL中，实体完整性是通过主键约束和候选键约束来实现的。
（1）主键约束

主键可以是表中的某一列，也可以是表中多个列所构成的一个组合。
其中，由多个列组合而成的主键也称为复合主键。
在MySQL中，主键列必须遵守如下一些规则：

• 每一个表只能定义一个主键。
  
• 主键的值，也称为键值，必须能够唯一标志表中的每一行记录，且不能为NULL。也就是说，表中两个不同的行在主键上不能具有相同的值。这是唯一性原则。
  
• 复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成主键仍能满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。
  
• 一个列名在复合主键的列表中只能出现一次。
  

主键约束可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字“PRIMARY KEY”来实现。
其方式有两种：

• 一种是作为列的完整性约束，此时只需在表中某个列的属性定义后加上关键字 “PRIMART KEY” 即可。
  
• 一种是作为表的完整性约束，需要再表中所有列的属性定义后添加一条 PRIMARY KEY(index_col_name, ...) 格式的子句。
  

注意：
如果主键仅由一个表中的某一列所构成，上述两种方法均可以定义主键约束。
如果主键是由表中多个列所构成的一个组合，则只能用上述第二种方法定义主键约束。
定义主键约束后，MySQL 会自动为主键创建一个唯一性索引，用于在查询中使用主键对数据进行快速检索，该索引名默认为 PRIMARY，也可以重新自定义命名。
（2）候选键约束

任何时候，候选键的值必须是唯一的，且不能为 NULL。
候选键可以在 CREATE TABLE 或 ALTER TABLE 语句中使用关键字“UNIQUE”来定义，其实现方法与主键约束相似，同样可作为列或者表（关系）的完整性约束两种方式。
MySQL中候选键与主键之间存在以下几点区别：

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

2 参照完整性

外键声明的方式：

• 在表中某个列的属性定义后直接加上”reference_definition“语法项。
  
• 在表中所有列的属性定义后添加”FOREIGN KEY (index_col_name, ...) reference_definition“ 子句的语法项。
  

"reference_definition" 语法项的定义：

1. REFERENCES tbl_name (index_col_name, ...)
2.         [ON DELETE reference_option]
3.         [ON UPDATE reference_option]

复制代码

index_col_name 的语法格式：

1. col_name [(length)] [ASC | DESC]

复制代码

reference_option 的语法格式：

1. RESTRICT | CASCADE | SET NULL | NO ACTION
3. 限制策略    级联策略    置空策略    不采取实施策略

复制代码

例子：在数据库 mysql_test 中创建一个商品订单表 orders，该表包含的订单信息有：订单号 oder_id、订购商品名 order_product、订购商品类型 order_product_type、订购客户 id 号 cust_id、订购时间 order_date、订购价格 order_price、订购数量 order_amount。要求商品订单表 orders 中的所有订购客户信息均已在表 customers 中记录在册。

1. mysql> use mysql_test;
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
6. mysql> create table orders
7.     -> (
8.     -> order_id int not null auto_increment,
9.     -> order_product char(50) not null,
10.     -> order_product_type char(50) not null,
11.     -> cust_id int not null,
12.     -> order_date datetime not null,
13.     -> order_price double not null,
14.     -> order_amount int not null,
15.     -> primart key (order_id),
16.     -> foreign key (cust_id)
17.     -> references customers(cust_id)
18.     -> on delete restrict
19.     -> on update restrict
20.     -> );
21. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key (order_id),
22. foreign key (cust_id)
23. references customers(cust_id)
24. on delete r' at line 10
25. mysql> desc customers;
26. +--------------+----------+------+-----+---------+----------------+
27. | Field        | Type     | Null | Key | Default | Extra          |
28. +--------------+----------+------+-----+---------+----------------+
29. | cust_id      | int      | NO   | PRI | NULL    | auto_increment |
30. | cust_name    | char(20) | YES  |     | NULL    |                |
31. | cust_sex     | char(1)  | YES  |     | M       |                |
32. | cust_city    | char(10) | NO   |     | Beijing |                |
33. | cust_address | char(50) | YES  |     | NULL    |                |
34. | cust_contact | char(50) | YES  |     | NULL    |                |
35. +--------------+----------+------+-----+---------+----------------+
36. 6 rows in set (0.01 sec)
38. 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 );
39. Query OK, 0 rows affected (0.05 sec)
41. mysql> desc orders;
42. +--------------------+----------+------+-----+---------+----------------+
43. | Field              | Type     | Null | Key | Default | Extra          |
44. +--------------------+----------+------+-----+---------+----------------+
45. | order_id           | int      | NO   | PRI | NULL    | auto_increment |
46. | order_product      | char(50) | NO   |     | NULL    |                |
47. | order_product_type | char(50) | NO   |     | NULL    |                |
48. | cust_id            | int      | NO   | MUL | NULL    |                |
49. | order_date         | datetime | NO   |     | NULL    |                |
50. | order_price        | double   | NO   |     | NULL    |                |
51. | order_amount       | int      | NO   |     | NULL    |                |
52. +--------------------+----------+------+-----+---------+----------------+
53. 7 rows in set (0.00 sec)
55. mysql>

复制代码

当指定一个外键时，需要遵守下列原则：

• 被参照表必须已经用一条 CREATE TABLE 语句创建了，或者必须是当前正在创建的表。
  
  
  
  • 如若是后一种情形，则被参照表与参照表是同一个表，这样的表称为自参照表(self-referencing table)，这种结构称为自参照完整性(self-referential integrity)。
    
  
  
• 必须为被参照表定义主键。
  
• 必须在被参照表的表名后面指定列名或列名的组合。
  
  
  
  • 这个列或列组合必须是这个被参照表的主键或候选键。
    
  
  
• 尽管主键是不能够包含空值的，但允许在外键中出现一个空值。
  
  
  
  • 这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
    
  
  
• 外键中的列的数目必须和被参照表的主键中的列的数目相同。
  
• 外键中的列的数据类型必须和被参照表的主键中的对应列的数据类型相同。
  

3 用户定义的完整性

• 非空约束
  
  
  
  • NOT NULL
    
  
  
• CHECK 约束
  
  
  
  • CHECK (expr)
    
  • 基于列的 CHECK 约束
    
  • 基于表的 CHECK 约束
    
  
  
• 触发器
  

三、命名完整性约束

1. CONSTRAINT [symbol]

复制代码

• 只能给基于表的完整性约束指定名字，而无法给基于列的完整性约束指定名字
  

四、更新完整性约束

添加约束：ALTER TABLE 语句中使用 ADD CONSTRAINT子句

• 完整性约束不能直接被修改。
  
  
  
  • 若要修改某个约束，实际上是用 ALTER TABLE 语句先删除该约束，然后再增加一个与该约束同名的新约束
    
  
  
• 使用 ALTER TABLE 语句，可以独立地删除完整性约束，而不会删除表本身。
  
  
  
  • 若使用 DROP TABLE 语句删除一个表，则表中所有的完整性约束都会自动被删除。
    
  
  

第二节 触发器

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。
触发器一旦定义，无须用户调用，任何对表的修改操作均由数据库服务器自动激活相应的触发器。
一、创建触发器

1. CREATE TRIGGER trigger_name trigger_time trigger_event
2.         ON tbl_name FOR EACH ROW trigger_body

复制代码

• trigger_time
  
  
  
  • BEFORE
    
  • AFTER
    
  
  
• trigger_event
  
  
  
  • INSERT
    
  • UPDATE
    
  • DELETE
    
  
  
• tbl_name
  
  
  
  • 用于指定与触发器相关联的表名，必须引用永久性表
    
  • 不能将触发器与临时表或视图关联起来
    
  • 同一个表不能拥有两个具有相同触发时刻和事件的触发器
    
  
  
• 在触发器的创建中，每个表每个事件每次只允许一个触发器
  
• 每个表最多支持6个触发器，即每条 INSERT、UPDATE 和 DELETE 的“之前”与“之后”
  
• 单一触发器不能与多个事件或多个表关联
  

例子：在数据库 mysql_test 的表 customers 中创建一个触发器 customers_insert_trigger，用于每次向表 customers 插入一行数据时，将用户变量 str 的值设置为 “one customer added!”。

1. mysql> use mysql_test;
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
6. mysql> create trigger mysql_test.customers_insert_trigger after insert
7.     -> on mysql_test.customers for each row set @str='one customer added!';
8. Query OK, 0 rows affected (0.02 sec)
10. mysql> insert into mysql_test.customers
11.     -> values(null,'万华','F','长沙市','芙蓉区');
12. ERROR 1136 (21S01): Column count doesn't match value count at row 1
13. mysql> desc customers;
14. +--------------+----------+------+-----+---------+----------------+
15. | Field        | Type     | Null | Key | Default | Extra          |
16. +--------------+----------+------+-----+---------+----------------+
17. | cust_id      | int      | NO   | PRI | NULL    | auto_increment |
18. | cust_name    | char(20) | YES  |     | NULL    |                |
19. | cust_sex     | char(1)  | YES  |     | M       |                |
20. | cust_city    | char(10) | NO   |     | Beijing |                |
21. | cust_address | char(50) | YES  |     | NULL    |                |
22. | cust_contact | char(50) | YES  |     | NULL    |                |
23. +--------------+----------+------+-----+---------+----------------+
24. 6 rows in set (0.01 sec)
26. mysql> alter table mysql_test.customers drop column cust_contact;
27. Query OK, 0 rows affected (0.03 sec)
28. Records: 0  Duplicates: 0  Warnings: 0
30. mysql> desc customers;
31. +--------------+----------+------+-----+---------+----------------+
32. | Field        | Type     | Null | Key | Default | Extra          |
33. +--------------+----------+------+-----+---------+----------------+
34. | cust_id      | int      | NO   | PRI | NULL    | auto_increment |
35. | cust_name    | char(20) | YES  |     | NULL    |                |
36. | cust_sex     | char(1)  | YES  |     | M       |                |
37. | cust_city    | char(10) | NO   |     | Beijing |                |
38. | cust_address | char(50) | YES  |     | NULL    |                |
39. +--------------+----------+------+-----+---------+----------------+
40. 5 rows in set (0.01 sec)
42. mysql> insert into mysql_test.customers values(null,'万华','F','长沙市','芙蓉区');
43. Query OK, 1 row affected (0.00 sec)
45. mysql> select @str;
46. +---------------------+
47. | @str                |
48. +---------------------+
49. | one customer added! |
50. +---------------------+
51. 1 row in set (0.00 sec)
53. mysql>

复制代码

二、删除触发器

1. DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

复制代码

例子：删除数据库 mysql_test 中的触发器 customers_insert_trigger。

1. mysql> drop trigger if exists mysql_test.customers_insert_trigger;
2. Query OK, 0 rows affected (0.00 sec)
4. mysql>

复制代码

• 当删除一个表的同时，也会自动地删除该表上的触发器，且触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。
  

三、使用触发器

1 INSERT 触发器

• 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表，来访问被插入的行。
  
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
  
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的是0值，在 INSERT 执行之后将包含新的自动生成值。
  

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

1. mysql> create trigger mysql_test.customers_insert_trigger after insert
2.     -> on mysql_test.customers for each row set @str=new.cust_id;
3. Query OK, 0 rows affected (0.00 sec)
5. mysql> insert into mysql_test.customers values(null,'曾伟','F','长沙市','芙蓉区');
6. Query OK, 1 row affected (0.00 sec)
8. mysql> select @str;
9. +------+
10. | @str |
11. +------+
12. |  911 |
13. +------+
14. 1 row in set (0.00 sec)
16. mysql> select * from customers;
17. +---------+-----------+----------+-----------+--------------+
18. | cust_id | cust_name | cust_sex | cust_city | cust_address |
19. +---------+-----------+----------+-----------+--------------+
20. |     901 | 张三      | F        | 北京市    | 武汉市       |
21. |     902 | 李四      | M        | 武汉市    | 上海市       |
22. |     903 | 李四      | M        | Beijing   | 上海市       |
23. |     904 | 李四      | M        | Beijing   | 上海市       |
24. |     910 | 万华      | F        | 长沙市    | 芙蓉区       |
25. |     911 | 曾伟      | F        | 长沙市    | 芙蓉区       |
26. +---------+-----------+----------+-----------+--------------+
27. 6 rows in set (0.00 sec)
29. mysql>

复制代码

2 DELETE 触发器

• 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表，来访问被删除的表。
  
• OLD 中的值全部是只读的，不能被更新。
  

3 UPDATE 触发器

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

例子：在数据库 mysql_test 的表 customers 中创建一个触发器 customers_update_trigger，用于每次更新表 customers时，将该表中 cust_address 列的值设置为 cust_contact 列的值。

1. mysql> create trigger mysql_test.customers_update_trigger before update
2.     -> on mysql_test.customers for each row
3.     -> set new.cust_address=old.cust_contact;
4. ERROR 1054 (42S22): Unknown column 'cust_contact' in 'OLD'
5. mysql> alter table mysql_test.customers
6.     -> add column cust_contact char(50) null;
7. Query OK, 0 rows affected (0.02 sec)
8. Records: 0  Duplicates: 0  Warnings: 0
10. mysql> create trigger mysql_test.customers_update_trigger before update on mysql_test.customers for each row set new.cust_address=old.cust_contact;
11. Query OK, 0 rows affected (0.01 sec)
13. mysql> update mysql_test.customers set cust_address='武汉市' where cust_name='曾伟';
14. Query OK, 1 row affected (0.00 sec)
15. Rows matched: 1  Changed: 1  Warnings: 0
17. mysql> select cust_address from mysql_test.customers where cust_name='曾伟';
18. +--------------+
19. | cust_address |
20. +--------------+
21. | NULL         |
22. +--------------+
23. 1 row in set (0.00 sec)
25. mysql> select * from customers;
26. +---------+-----------+----------+-----------+--------------+--------------+
27. | cust_id | cust_name | cust_sex | cust_city | cust_address | cust_contact |
28. +---------+-----------+----------+-----------+--------------+--------------+
29. |     901 | 张三      | F        | 北京市    | 武汉市       | NULL         |
30. |     902 | 李四      | M        | 武汉市    | 上海市       | NULL         |
31. |     903 | 李四      | M        | Beijing   | 上海市       | NULL         |
32. |     904 | 李四      | M        | Beijing   | 上海市       | NULL         |
33. |     910 | 万华      | F        | 长沙市    | 芙蓉区       | NULL         |
34. |     911 | 曾伟      | F        | 长沙市    | NULL         | NULL         |
35. +---------+-----------+----------+-----------+--------------+--------------+
36. 6 rows in set (0.00 sec)
38. mysql>

复制代码

第三节 安全性与访问控制

一、用户账号管理

查看MySQL数据库的使用者账号

1. mysql> select user from mysql.user;
2. +------------------+
3. | user             |
4. +------------------+
5. | root             |
6. | mysql.infoschema |
7. | mysql.session    |
8. | mysql.sys        |
9. +------------------+
10. 4 rows in set (0.01 sec)

复制代码

1 创建用户账号

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

复制代码

• "user" 格式：'user_name'@'host_name'
  
• 没指定主机名，则主机名会默认为是"%"，其表示一组主机
  

例子：在MySQL服务器中添加两个新的用户，其用户名分别为 zhangsan 和 lisi，他们的主机名均为 localhost，用户  zhangsan的口令设置为明文 123，用户 lisi的口令设置为对明文456使用 PASSWORD()函数加密返回的散列值。

1. # 查看 mysql 初始的密码策略
2. mysql> show variables like 'validate_password%';
3. +--------------------------------------+--------+
4. | Variable_name                        | Value  |
5. +--------------------------------------+--------+
6. | validate_password.check_user_name    | ON     |
7. | validate_password.dictionary_file    |        |
8. | validate_password.length             | 8      |
9. | validate_password.mixed_case_count   | 1      |
10. | validate_password.number_count       | 1      |
11. | validate_password.policy             | MEDIUM |
12. | validate_password.special_char_count | 1      |
13. +--------------------------------------+--------+
14. 7 rows in set (0.01 sec)
16. # 设置密码的验证强度等级，设置 validate_password_policy 的全局参数为 LOW
17. mysql> set global validate_password.policy=LOW;
18. Query OK, 0 rows affected (0.01 sec)
21. mysql>  create user 'zhangsan'@'localhost' identified by '12345678';
22. Query OK, 0 rows affected (0.01 sec)
24. mysql> select md5(12345678);  # MySQL 8.0+以上版本 password() 不可用
25. +----------------------------------+
26. | md5(12345678)                    |
27. +----------------------------------+
28. | 25d55ad283aa400af464c76d713c07ad |
29. +----------------------------------+
30. 1 row in set (0.00 sec)
32. mysql> create user 'lisi'@'localhost' identified by '12345678';
33. Query OK, 0 rows affected (0.00 sec)
35. mysql>

复制代码

官网：https://dev.mysql.com/doc/refman/8.0/en/create-user.html

1. CREATE USER
2.   'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
3.                                    BY 'new_password1',
4.   'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password
5.                                   BY 'new_password2'
6.   REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
7.   PASSWORD HISTORY 5
8.   ACCOUNT LOCK;

复制代码

• 要使用 CREATE USER 语句，必须拥有 MySQL 中 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
  
• 使用 CREATE USER 语句创建一个用户账号后，会在系统自身的mysql数据库的user表中添加一条新记录。如果创建的账户已经存在，则语句执行会出现错误。
  
• 如果两个用户具有相同的用户名和不同的主机名，MySQL会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合。
  
• 如果在 CREATE USER 语句的使用中，没有为用户指定口令，那么MySQL允许该用户可以不使用口令登录系统，然而从安全的角度而言，不推荐这种做法。
  
• 新创建的用户拥有的权限很少。
  

2 删除用户

1. DROP USER user [,user]...

复制代码

例子：删除lisi用户

1. mysql> drop user lisi;
2. ERROR 1396 (HY000): Operation DROP USER failed for 'lisi'@'%'
3. mysql> drop user lisi@localhost;
4. Query OK, 0 rows affected (0.00 sec)
6. mysql>

复制代码

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

3 修改用户账号

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

复制代码

例子：将用户 zhangsan 的名字修改成 wangwu

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

复制代码

• RENAME USER 语句用于对原有MySQL账户进行重命名。
  
• 要使用 RENAME USER 语句，必须拥有MySQL中mysql数据库的UPDATE权限或全局CREATE USER 权限。
  
• 倘若系统中旧账户不存在或者新账户已存在，则语句执行会出现错误。
  

4 修改用户口令

1. SET PASSWORD [FOR user] =
2.         {
3.         PASSWORD('new_password') | 'encrypted password'
4.         }

复制代码

例子：

1. mysql> set password for 'wangwu'@'localhost' = '88888888';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql>

复制代码

二、账户权限管理

1. mysql> select user from mysql.user;
2. +------------------+
3. | user             |
4. +------------------+
5. | root             |
6. | mysql.infoschema |
7. | mysql.session    |
8. | mysql.sys        |
9. | wangwu           |
10. +------------------+
11. 5 rows in set (0.00 sec)
13. mysql> show grants for 'wangwu'@'localhost';
14. +--------------------------------------------+
15. | Grants for wangwu@localhost                |
16. +--------------------------------------------+
17. | GRANT USAGE ON *.* TO `wangwu`@`localhost` |
18. +--------------------------------------------+
19. 1 row in set (0.01 sec)
21. mysql>

复制代码

1 权限的授予

1. GRANT
2.         priv_type [(column_list)]
3.                 [, priv_type [(column_list)]] ...
4.   ON [object_type] priv_level
5.   TO user_specification [, user_specification] ...
6.   [WITH GRANT OPTION]

复制代码

• “priv_level”：用于指定权限的级别
  
  
  
  • "*" 表示当前数据库中的所有表
    
  • "*.*" 表示所有数据库中的所有表
    
  • "db_name.*" 表示某个数据库中的所有表
    
  
  
• 如果权限被授予给一个不存在的用户，MySQL会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户指定口令。
  
• "user_specification" 是 TO 子句中的具体描述部分
  
  
  
  • user[IDENTIFIED BY [PASSWORD] 'password']
    
  
  

例子：授予用户 zhangsan 在数据库 mysql_test 的表 customers 上拥有对列 cust_id 和列 cust_name 的 SELECT 权限

1. ➜ mysql -uroot -p
2. Enter password:
3. Welcome to the MySQL monitor.  Commands end with ; or \g.
4. Your MySQL connection id is 1105
5. Server version: 8.0.32 Homebrew
7. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15. mysql> grant select (cust_id, cust_name) on mysql_test.customers to 'zhangsan'@'localhost';
16. ERROR 1410 (42000): You are not allowed to create a user with GRANT
17. mysql> select user from mysql.user;
18. +------------------+
19. | user             |
20. +------------------+
21. | root             |
22. | mysql.infoschema |
23. | mysql.session    |
24. | mysql.sys        |
25. | wangwu           |
26. +------------------+
27. 5 rows in set (0.00 sec)
29. mysql> rename user 'wangwu'@'localhost' to 'zhangsan'@'localhost';
30. Query OK, 0 rows affected (0.01 sec)
32. mysql> grant select (cust_id, cust_name) on mysql_test.customers to 'zhangsan'@'localhost';
33. Query OK, 0 rows affected (0.01 sec)
36. ➜ mysql -uzhangsan -p
37. Enter password:
38. Welcome to the MySQL monitor.  Commands end with ; or \g.
39. Your MySQL connection id is 1107
40. Server version: 8.0.32 Homebrew
42. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
44. Oracle is a registered trademark of Oracle Corporation and/or its
45. affiliates. Other names may be trademarks of their respective
46. owners.
48. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
50. mysql> select * from mysql_test.customers;
51. ERROR 1142 (42000): SELECT command denied to user 'zhangsan'@'localhost' for table 'customers'
52. mysql> select cust_id,cust_name from mysql_test.customers;
53. +---------+-----------+
54. | cust_id | cust_name |
55. +---------+-----------+
56. |     901 | 张三      |
57. |     902 | 李四      |
58. |     903 | 李四      |
59. |     904 | 李四      |
60. |     910 | 万华      |
61. |     911 | 曾伟      |
62. +---------+-----------+
63. 6 rows in set (0.00 sec)

复制代码

例子2：创建 liming 和 huang 两个用户，并设置对应的系统登录口令，同时授予他们在数据库 mysql_test 的表 customers 上拥有 SELECT 和 UPDATE 的权限。

1. mysql> grant select, update on mysql_test.customers to 'liming'@'localhost';
2. ERROR 1410 (42000): You are not allowed to create a user with GRANT
3. # mysql 8 最新的MySQL8不允许直接创建并授权，必须先让自己有GRANT权限，然后创建用户，再授权。
5. mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
6. +-----------+------------------+------------+------------+
7. | host      | user             | Grant_priv | Super_priv |
8. +-----------+------------------+------------+------------+
9. | %         | root             | Y          | Y          |
10. | localhost | mysql.infoschema | N          | N          |
11. | localhost | mysql.session    | N          | Y          |
12. | localhost | mysql.sys        | N          | N          |
13. | localhost | zhangsan         | N          | N          |
14. +-----------+------------------+------------+------------+
15. 5 rows in set (0.00 sec)
17. mysql>

复制代码

例子3：授予zhangsan 可以在数据库 mysql_test 中执行所有数据库操作的权限

1. mysql> grant all on mysql_test.* to 'zhangsan'@'localhost';
2. Query OK, 0 rows affected (0.01 sec)

复制代码

例子4：授予 zhangsan 创建用户的权限

1. mysql> grant create user on *.* to 'zhangsan'@'localhost';
2. Query OK, 0 rows affected (0.01 sec)
4. mysql>

复制代码

"priv_type"  的使用

• 授予表权限时，可以指定为以下值
  
  
  
  • SELECT
    
  • INSERT
    
  • DELETE
    
  • UPDATE
    
  • REFERENCES
    
  • CREATE
    
  • ALTER
    
  • INDEX
    
  • DROP
    
  • ALL 或 ALL PRIVILEGES
    
  
  
• 授予列权限时：
  
  
  
  • SELECT
    
  • INSERT
    
  • UPDATE
    
  • 权限的后面需要加上列名列表 column_list
    
  
  
• 授予数据库权限时：
  
  
  
  • SELECT
    
  • INSERT
    
  • DELETE
    
  • UPDATE
    
  • REFERENCES
    
  • CREATE
    
  • ALTER
    
  • INDEX
    
  • DROP
    
  • CREATE TEMPORARY TABLES
    
  • CREATE VIEW
    
  • SHOW VIEW
    
  • CREATE ROUTINE
    
  • ALTER ROUTINE
    
  • EXECUTE ROUTINE
    
  • LOCK TABLES
    
  • ALL 或 ALL PRIVILEGES
    
  
  
• 授予用户权限时：
  
  
  
  • 授予数据库权限时的所有值
    
  • CREATE USER
    
  • SHOW DATABASES
    
  
  

2 权限的转移

• 权限的转移可以通过在 GRANT 语句中使用 WITH 子句来实现。
  
• 如果将 WITH 子句指定为关键字 "WITH GRANT OPTION"，则表示 TO 子句中所指定的所有用户都具有把自己所拥有授予给其他用户的权利，而无论那些其他用户是否拥有该权限。
  

例子：

1. ~
2. ➜ mysql -uroot -p
3. Enter password:
4. Welcome to the MySQL monitor.  Commands end with ; or \g.
5. Your MySQL connection id is 1173
6. Server version: 8.0.32 Homebrew
8. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16. mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost' identified by '123' with grant option;
17. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123' with grant option' at line 1
18. # mysql 8.0+ 报错　先创建用户　后授权
21. mysql> create user 'zhou'@'localhost' identified by '12345678';
22. Query OK, 0 rows affected (0.01 sec)
24. mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost' identified by '12345678' with grant option;
25. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '12345678' with grant option' at line 1
27. mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost'  with grant option;
28. Query OK, 0 rows affected (0.01 sec)
30. mysql>

复制代码

３权限的撤销

• 当需要回收某些特定的权限时
  

1. REVOKE
2.         priv_type [(column_list)]
3.                 [, priv_type [(column_list)]] ...
4.   ON [object_type] priv_level
5.   FROM user [, user] ...

复制代码

• 当需要回收特定用户的所有权限时
  

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

复制代码

例子：

1. mysql> revoke select on mysql_test.customers from 'zhou'@'localhost';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql>

复制代码

• 要使用 REVOKE 语句，必须拥有 mysql 数据库的全局 CREATE USER 权限或 UPDATE 权限
  

第四节 事物与并发控制

事务就是为保证数据的一致性而产生的一个概念和基本手段
一、事务的概念

• 所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。
  
• 事务与程序的区别：
  
  
  
  • 程序是静止的
    
  • 事务是动态的，是程序的执行而不是程序本身
    
  • 同一程序的多个独立执行可以同时进行，而每一步执行则是一个不同的事务
    
  
  

二、事务的特征 ACID

• 原子性（Atomicity）
  
  
  
  • 事务是不可分割的最小工作单位，所包含的这些操作是一个整体
    
  • 要么全做，要么全不做
    
  
  
• 一致性（Consistency）
  
  
  
  • 完整性约束
    
  
  
• 隔离性（Isolation）
  
  
  
  • 事务彼此独立的、隔离的
    
  • 可串行性 串行调度
    
  
  
• 持续性（Durability）
  
  
  
  • 永久性（Permanence）
    
  
  

例子：依据事物的ACID特性，分析并编写银行数据库系统中的转账事务T：从账户A转账S金额资金到账户B。

1. BEGIN TRANSACTION
2.         read(A);
3.         A=A-S;
4.         write(A);
5.         if(A<0) ROLLBACK
6.         else {
7.         read(B);
8.         B=B+S
9.         write(B);
10.         COMMIT;
11.         }

复制代码

mysql复制表的两种方式：
第一、只复制表结构到新表
create table 新表 select * from 旧表 where 1=2
或者
create table 新表 like 旧表
第二、复制表结构及数据到新表
create table新表 select * from 旧表

1. SELECT * INTO OUTFILE 'file_name' export_options
2.         | INTO DUMPFILE 'file_name'

复制代码

将备份数据导入到一个和customers表结构相同的空表 customers_copy 中

1. [FIELDS
2.         [TERMINATED BY 'string']
3.         [[OPTIONALLY] ENCLOSED BY 'char']
4.         [ESCAPED BY 'char']
5. ]
6. [LINES TERMINATED BY 'string']

复制代码

• 在多个用户同时使用MySQL数据库的情况下，为了得到一个一致的备份，需要在指定的表上使用 LOCK TABLES table_name READ 语句做一个读锁定，以防止在备份过程中表被其他用户更新。
  
• 当恢复数据时，则需要使用 LOCK TABLES table_name WRITE 语句做一个写锁定，以避免发生数据冲突。
  
• 在数据库备份或恢复完毕之后需要使用 UNLOCK TABLES 语句对该表进行解锁。
  

来源:https://www.cnblogs.com/QiaoPengjun/p/17279396.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！