資料庫安全與保護第一節資料庫完整性資料庫完整性是指資料庫中數據的正確性和相容性。數據完整性約束是為了防止資料庫中存在不符合語義的數據，為了維護數據的完整性，DBMS 必須提供一種機制來檢查資料庫中的數據，以判斷其是否滿足語義規定的條件。這些加在資料庫數據之上的語義約束條件就是數據完整性約束 ...

資料庫安全與保護

第一節資料庫完整性

資料庫完整性是指資料庫中數據的正確性和相容性。
數據完整性約束是為了防止資料庫中存在不符合語義的數據，為了維護數據的完整性，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" 語法項的定義：

REFERENCES tbl_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

限制策略    級聯策略    置空策略    不採取實施策略

例子：在資料庫 mysql_test 中創建一個商品訂單表 orders，該表包含的訂單信息有：訂單號 oder_id、訂購商品名 order_product、訂購商品類型 order_product_type、訂購客戶 id 號 cust_id、訂購時間 order_date、訂購價格 order_price、訂購數量 order_amount。要求商品訂單表 orders 中的所有訂購客戶信息均已在表 customers 中記錄在冊。

mysql> use mysql_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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,
    -> primart key (order_id),
    -> foreign key (cust_id)
    -> references customers(cust_id)
    -> on delete restrict
    -> on update restrict
    -> );
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),
foreign key (cust_id)
references customers(cust_id)
on delete r' at line 10
mysql> desc customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int      | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(20) | YES  |     | NULL    |                |
| cust_sex     | char(1)  | YES  |     | M       |                |
| cust_city    | char(10) | NO   |     | Beijing |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_contact | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

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)

mysql> desc orders;
+--------------------+----------+------+-----+---------+----------------+
| Field              | Type     | Null | Key | Default | Extra          |
+--------------------+----------+------+-----+---------+----------------+
| order_id           | int      | NO   | PRI | NULL    | auto_increment |
| order_product      | char(50) | NO   |     | NULL    |                |
| order_product_type | char(50) | NO   |     | NULL    |                |
| cust_id            | int      | NO   | MUL | NULL    |                |
| order_date         | datetime | NO   |     | NULL    |                |
| order_price        | double   | NO   |     | NULL    |                |
| order_amount       | int      | NO   |     | NULL    |                |
+--------------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql>

當指定一個外鍵時，需要遵守下列原則：

被參照表必須已經用一條 CREATE TABLE 語句創建了，或者必須是當前正在創建的表。
- 如若是後一種情形，則被參照表與參照表是同一個表，這樣的表稱為自參照表(self-referencing table)，這種結構稱為自參照完整性(self-referential integrity)。
必須為被參照表定義主鍵。
必須在被參照表的表名後面指定列名或列名的組合。
- 這個列或列組合必須是這個被參照表的主鍵或候選鍵。
儘管主鍵是不能夠包含空值的，但允許在外鍵中出現一個空值。
- 這意味著，只要外鍵的每個非空值出現在指定的主鍵中，這個外鍵的內容就是正確的。
外鍵中的列的數目必須和被參照表的主鍵中的列的數目相同。
外鍵中的列的數據類型必須和被參照表的主鍵中的對應列的數據類型相同。

3 用戶定義的完整性

非空約束
- NOT NULL
CHECK 約束
- CHECK (expr)
- 基於列的 CHECK 約束
- 基於表的 CHECK 約束
觸發器

三、命名完整性約束

CONSTRAINT [symbol]

只能給基於表的完整性約束指定名字，而無法給基於列的完整性約束指定名字

四、更新完整性約束

添加約束：ALTER TABLE 語句中使用 ADD CONSTRAINT子句

完整性約束不能直接被修改。
- 若要修改某個約束，實際上是用 ALTER TABLE 語句先刪除該約束，然後再增加一個與該約束同名的新約束
使用 ALTER TABLE 語句，可以獨立地刪除完整性約束，而不會刪除表本身。
- 若使用 DROP TABLE 語句刪除一個表，則表中所有的完整性約束都會自動被刪除。

第二節觸發器

觸發器（Trigger）是用戶定義在關係表上的一類由事件驅動的資料庫對象，也是一種保證數據完整性的方法。

觸發器一旦定義，無須用戶調用，任何對錶的修改操作均由資料庫伺服器自動激活相應的觸發器。

一、創建觸發器

CREATE TRIGGER trigger_name trigger_time trigger_event
	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!”。

mysql> use mysql_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> insert into mysql_test.customers
    -> values(null,'萬華','F','長沙市','芙蓉區');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> desc customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int      | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(20) | YES  |     | NULL    |                |
| cust_sex     | char(1)  | YES  |     | M       |                |
| cust_city    | char(10) | NO   |     | Beijing |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_contact | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> alter table mysql_test.customers drop column cust_contact;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int      | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(20) | YES  |     | NULL    |                |
| cust_sex     | char(1)  | YES  |     | M       |                |
| cust_city    | char(10) | NO   |     | Beijing |                |
| cust_address | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> insert into mysql_test.customers values(null,'萬華','F','長沙市','芙蓉區');
Query OK, 1 row affected (0.00 sec)

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

mysql>

二、刪除觸發器

DROP TRIGGER [IF EXISTS] [schema_name.] trigger_name

例子：刪除資料庫 mysql_test 中的觸發器 customers_insert_trigger。

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

mysql>

當刪除一個表的同時，也會自動地刪除該表上的觸發器，且觸發器不能更新或覆蓋，為了修改一個觸發器，必須先刪除它，然後再重新創建。

三、使用觸發器

1 INSERT 觸發器

在 INSERT 觸發器代碼內，可引用一個名為 NEW（不區分大小寫）的虛擬表，來訪問被插入的行。
在 BEFORE INSERT 觸發器中，NEW 中的值也可以被更新，即允許更改被插入的值（只要具有對應的操作許可權）。
對於 AUTO_INCREMENT 列，NEW 在 INSERT 執行之前包含的是0值，在 INSERT 執行之後將包含新的自動生成值。

例子：在資料庫 mysql_test 的表 customers 中重新創建觸發器 customers_insert_trigger，用於每次向表 customers插入一行數據時，將用戶變數 str 的值設置為新插入客戶的 id 號。

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

mysql> insert into mysql_test.customers values(null,'曾偉','F','長沙市','芙蓉區');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from customers;
+---------+-----------+----------+-----------+--------------+
| cust_id | cust_name | cust_sex | cust_city | cust_address |
+---------+-----------+----------+-----------+--------------+
|     901 | 張三      | F        | 北京市    | 武漢市       |
|     902 | 李四      | M        | 武漢市    | 上海市       |
|     903 | 李四      | M        | Beijing   | 上海市       |
|     904 | 李四      | M        | Beijing   | 上海市       |
|     910 | 萬華      | F        | 長沙市    | 芙蓉區       |
|     911 | 曾偉      | F        | 長沙市    | 芙蓉區       |
+---------+-----------+----------+-----------+--------------+
6 rows in set (0.00 sec)

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 列的值。

mysql> create trigger mysql_test.customers_update_trigger before update
    -> on mysql_test.customers for each row
    -> set new.cust_address=old.cust_contact;
ERROR 1054 (42S22): Unknown column 'cust_contact' in 'OLD'
mysql> alter table mysql_test.customers
    -> add column cust_contact char(50) null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> update mysql_test.customers set cust_address='武漢市' where cust_name='曾偉';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select cust_address from mysql_test.customers where cust_name='曾偉';
+--------------+
| cust_address |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

mysql> select * from customers;
+---------+-----------+----------+-----------+--------------+--------------+
| cust_id | cust_name | cust_sex | cust_city | cust_address | cust_contact |
+---------+-----------+----------+-----------+--------------+--------------+
|     901 | 張三      | F        | 北京市    | 武漢市       | NULL         |
|     902 | 李四      | M        | 武漢市    | 上海市       | NULL         |
|     903 | 李四      | M        | Beijing   | 上海市       | NULL         |
|     904 | 李四      | M        | Beijing   | 上海市       | NULL         |
|     910 | 萬華      | F        | 長沙市    | 芙蓉區       | NULL         |
|     911 | 曾偉      | F        | 長沙市    | NULL         | NULL         |
+---------+-----------+----------+-----------+--------------+--------------+
6 rows in set (0.00 sec)

mysql>

第三節安全性與訪問控制

一、用戶賬號管理

查看MySQL資料庫的使用者賬號

mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
+------------------+
4 rows in set (0.01 sec)

1 創建用戶賬號

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

"user" 格式：'user_name'@'host_name'
沒指定主機名，則主機名會預設為是"%"，其表示一組主機

例子：在MySQL伺服器中添加兩個新的用戶，其用戶名分別為 zhangsan 和 lisi，他們的主機名均為 localhost，用戶 zhangsan的口令設置為明文 123，用戶 lisi的口令設置為對明文456使用 PASSWORD()函數加密返回的散列值。

# 查看 mysql 初始的密碼策略
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

# 設置密碼的驗證強度等級，設置 validate_password_policy 的全局參數為 LOW 
mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.01 sec)


mysql>  create user 'zhangsan'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.01 sec)

mysql> select md5(12345678);  # MySQL 8.0+以上版本 password() 不可用
+----------------------------------+
| md5(12345678)                    |
+----------------------------------+
| 25d55ad283aa400af464c76d713c07ad |
+----------------------------------+
1 row in set (0.00 sec)

mysql> create user 'lisi'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.00 sec)

mysql>

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

CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD HISTORY 5
  ACCOUNT LOCK;

要使用 CREATE USER 語句，必須擁有 MySQL 中 mysql 資料庫的 INSERT 許可權或全局 CREATE USER 許可權。
使用 CREATE USER 語句創建一個用戶賬號後，會在系統自身的mysql資料庫的user表中添加一條新記錄。如果創建的賬戶已經存在，則語句執行會出現錯誤。
如果兩個用戶具有相同的用戶名和不同的主機名，MySQL會將他們視為不同的用戶，並允許為這兩個用戶分配不同的許可權集合。
如果在 CREATE USER 語句的使用中，沒有為用戶指定口令，那麼MySQL允許該用戶可以不使用口令登錄系統，然而從安全的角度而言，不推薦這種做法。
新創建的用戶擁有的許可權很少。

2 刪除用戶

DROP USER user [,user]...

例子：刪除lisi用戶

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)

mysql>

DROP USER 語句可用於刪除一個或多個MySQL賬戶，並消除其許可權。
要使用DROP USER 語句，必須擁有MySQL中mysql資料庫的DELETE許可權或全局 CREATE USER 許可權。
在 DROP USER 語句的使用中，如果沒有明確地給出賬戶的主機名，則該主機名會預設為是 %。
用戶的刪除不會影響到他們之前所創建的表、索引或其他資料庫對象，這是因為MySQL並沒有記錄是誰創建了這些對象。

3 修改用戶賬號

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

例子：將用戶 zhangsan 的名字修改成 wangwu

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

mysql>

RENAME USER 語句用於對原有MySQL賬戶進行重命名。
要使用 RENAME USER 語句，必須擁有MySQL中mysql資料庫的UPDATE許可權或全局CREATE USER 許可權。
倘若系統中舊賬戶不存在或者新賬戶已存在，則語句執行會出現錯誤。

4 修改用戶口令

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

例子：

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

mysql>

二、賬戶許可權管理

mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| wangwu           |
+------------------+
5 rows in set (0.00 sec)

mysql> show grants for 'wangwu'@'localhost';
+--------------------------------------------+
| Grants for wangwu@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `wangwu`@`localhost` |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql>

1 許可權的授予

GRANT
	priv_type [(column_list)]
		[, priv_type [(column_list)]] ...
  ON [object_type] priv_level
  TO user_specification [, user_specification] ...
  [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 許可權

➜ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1105
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select (cust_id, cust_name) on mysql_test.customers to 'zhangsan'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| wangwu           |
+------------------+
5 rows in set (0.00 sec)

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

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


➜ mysql -uzhangsan -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1107
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from mysql_test.customers;
ERROR 1142 (42000): SELECT command denied to user 'zhangsan'@'localhost' for table 'customers'
mysql> select cust_id,cust_name from mysql_test.customers;
+---------+-----------+
| cust_id | cust_name |
+---------+-----------+
|     901 | 張三      |
|     902 | 李四      |
|     903 | 李四      |
|     904 | 李四      |
|     910 | 萬華      |
|     911 | 曾偉      |
+---------+-----------+
6 rows in set (0.00 sec)

例子2：創建 liming 和 huang 兩個用戶，並設置對應的系統登錄口令，同時授予他們在資料庫 mysql_test 的表 customers 上擁有 SELECT 和 UPDATE 的許可權。

mysql> grant select, update on mysql_test.customers to 'liming'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT 
# mysql 8 最新的MySQL8不允許直接創建並授權，必須先讓自己有GRANT許可權，然後創建用戶，再授權。

mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+------------+------------+
| host      | user             | Grant_priv | Super_priv |
+-----------+------------------+------------+------------+
| %         | root             | Y          | Y          |
| localhost | mysql.infoschema | N          | N          |
| localhost | mysql.session    | N          | Y          |
| localhost | mysql.sys        | N          | N          |
| localhost | zhangsan         | N          | N          |
+-----------+------------------+------------+------------+
5 rows in set (0.00 sec)

mysql>

例子3：授予zhangsan 可以在資料庫 mysql_test 中執行所有資料庫操作的許可權

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

例子4：授予 zhangsan 創建用戶的許可權

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

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 子句中所指定的所有用戶都具有把自己所擁有授予給其他用戶的權利，而無論那些其他用戶是否擁有該許可權。

例子：

~
➜ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1173
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost' identified by '123' with grant option;
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
# mysql 8.0+ 報錯　先創建用戶　後授權


mysql> create user 'zhou'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost' identified by '12345678' with grant option;
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

mysql> grant select,update on mysql_test.customers to 'zhou'@'localhost'  with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql>

３許可權的撤銷

當需要回收某些特定的許可權時

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

例子：

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

mysql>

要使用 REVOKE 語句，必須擁有 mysql 資料庫的全局 CREATE USER 許可權或 UPDATE 許可權

第四節事物與併發控制

事務就是為保證數據的一致性而產生的一個概念和基本手段

一、事務的概念

所謂事務是用戶定義的一個數據操作序列，這些操作可作為一個完整的工作單元，要麼全部執行，要麼全部不執行，是一個不可分割的工作單位。
事務與程式的區別：
- 程式是靜止的
- 事務是動態的，是程式的執行而不是程式本身
- 同一程式的多個獨立執行可以同時進行，而每一步執行則是一個不同的事務

二、事務的特征 ACID

原子性（Atomicity）
- 事務是不可分割的最小工作單位，所包含的這些操作是一個整體
- 要麼全做，要麼全不做
一致性（Consistency）
- 完整性約束
隔離性（Isolation）
- 事務彼此獨立的、隔離的
- 可串列性串列調度
持續性（Durability）
- 永久性（Permanence）

例子：依據事物的ACID特性，分析並編寫銀行資料庫系統中的轉賬事務T：從賬戶A轉賬S金額資金到賬戶B。

BEGIN TRANSACTION
	read(A);
	A=A-S;
	write(A);
	if(A<0) ROLLBACK
	else {
	read(B);
	B=B+S
	write(B);
	COMMIT;
	}

三、併發操作問題

事務是併發控制的基本單位，保證事務的ACID特征是事務處理的重要任務，而事務的ACID特征可能遭到破壞的原因之一是多個事務對資料庫的併發操作造成的。

完整性檢驗可以保證一個事務單獨執行時，若輸入的資料庫狀態是正確的，則其輸出的資料庫狀態也是正確的。

當多個事務交錯執行時，可能出現不一致問題，這也稱為併發操作問題。

（1）丟失更新

（2）不可重覆讀

（3）讀“臟”數據

併發控制機制就是用正確的方式調度併發操作，使一個用戶事務的執行不受其他事務的干擾，從而避免造成數據的不一致性。

解決併發操作所帶來的數據不一致性問題的方法有封鎖、時間戳、樂觀控製法和多版本併發控制等。

四、封鎖

1 鎖

一個鎖實質上就是允許或阻止一個事務對一個數據對象的存取特權。
基本的封鎖類型有兩種：
- 排他鎖（Exclusive Look，X鎖）寫操作
- 共用鎖（Shared Lock，S鎖）讀操作

2 用封鎖進行併發控制

封鎖的工作原理如下：

若事務T對數據D加了X鎖，則所有別的事務對數據D的鎖請求都必須等待直到事務T釋放鎖。
若事務T對數據D加了S鎖，則別的事務還可對數據D請求S鎖，而對數據D的X鎖請求必須等待直到事務T釋放鎖
事務執行資料庫操作時都要先請求相應的鎖，即對讀請求S鎖，對更新（插入、刪除、修改）請求X鎖。這個過程一般是由DBMS在執行操作時自動隱含地進行。
事務一直占有獲得的鎖直到結束（COMMIT 或 ROLLBACK）時釋放。

3 封鎖的粒度

通常由粒度來描述封鎖的數據單元的大小。

大多數高性能系統都選擇折中的鎖粒度，至於哪一層最合適，則與應用環境下事務量、數據量及數據的易變特征等都緊密相關。

4 封鎖的級別

封鎖的級別又稱為一致性級別或隔離度。

0級封鎖：封鎖的事物不重寫其他非0級封鎖事務的未提交的更新數據。這種狀態實際上實用價值不大。
1級封鎖：被封鎖的實物不允許重寫未提交的更新數據。這防止了丟失更新的發生。
2級封鎖：被封鎖的事物即不重寫也不讀未提交的更新數據。這除了1級封鎖的效果外還防止了讀臟數據。
3級封鎖：被封鎖的事物不讀未提交的更新數據，不寫任何（包括讀操作）未提交數據。
- 這除了包含2級封鎖外，還不寫未提交的讀數據，因而防止了不可重讀的問題。
- 這是嚴格的封鎖，它保證了多個事務併發執行的“可串列化”。

5 活鎖與死鎖

封鎖帶來的一個重要問題是可能引起“活鎖”與“死鎖”。

在併發事務處理過程中，由於鎖會使一事務處於等待狀態而調度其他事務處理，因而該事務可能會因優先順序低而永遠等待下去，這種現象稱為“活鎖”。

活鎖問題的解決與調度演算法有關，一種最簡單的辦法是“先來先服務”。

兩個以上事務迴圈等待被同組中另一事務鎖住的數據單元的情形，稱為“死鎖”。

在任何一個多任務程式設計系統中，死鎖總是潛在的，所以在這種環境下的DBMS需要提供死鎖預防、死鎖檢測和死鎖發生後的處理技術與方法。

預防死鎖的方法：

一次性鎖請求
鎖請求排序
序列化處理
資源剝奪

對待死鎖的另一種辦法是不去防止，而讓其發生並隨時進行檢測，一旦檢測到系統已發生了死鎖再進行解除處理。

死鎖檢測可以用圖論的方法實現，並以正在執行的事物為結點。

6 可串列性

若一個調度等價於某一串列高度，即它所產生的結果與某一串列調度的結果一樣，則說調度是可串列化的（Serializable）。

一組事務的串列調度不是唯一的，因而可串列化的調度也不是唯一的。

通常，在資料庫系統中，可串列性就是併發執行的正確性準則，即當且僅當一組事務的併發執行調度是可串列化的，才認為它們是正確的。

7 兩段封鎖法

採用兩段封鎖法（Two-Phase Locking，2PL）是一種最簡單而有效的保障封鎖其調度是可串列性的方法。

兩段封鎖法是事務遵循兩段鎖協議的調度方法。

協議就是所有事務都必須遵循的關於基本操作執行順序的一種限制。

兩段鎖協議規定在任何一個事務中，所有加鎖操作都必須在所有釋放鎖操作之前。

事務劃分成如下兩個階段：

發展（Growing）或加鎖階段
收縮（Shrinking）或釋放鎖階段

定理6.1：遵循兩段鎖協議的事務的任何併發調度都是可串列化的。

2PL 是可串列化的充分條件，不是必要條件，即存在不全是2PL的事務的可串列化調度。

第五節備份與恢復

資料庫的實際使用過程中，存在著一些不可預估的因素：

電腦硬體故障
電腦軟體故障
病毒
人為操作
自然災害
盜竊

資料庫備份是指通過導出數據或者複製表文件的方式來製作資料庫的複本

資料庫恢復則是當資料庫出現故障或遭到破壞時，將備份的資料庫載入到系統，從而使資料庫從錯誤狀態恢復到備份時的正確狀態。

資料庫的恢復是以備份為基礎的。它是與備份相對應的系統維護和管理操作。

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']

在文件中，導出的數據行會以一定的形式存儲，其中空值是用"\N" 表示。

2 使用 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']
  ]

例子：備份資料庫mysql_test中表customers的全部數據。

➜ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2354
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from mysql_test.customers
    -> into outfile '/Users/qiaopengjun/backupfile.txt'
    -> fields terminated by ','
    -> optionally enclosed by ""
    -> lines terminated by '?';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like "%datadir%";
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| datadir       | /opt/homebrew/var/mysql/ |
+---------------+--------------------------+
1 row in set (0.02 sec)

mysql> select * from mysql_test.customers into outfile '/Users/qiaopengjun/backupfile.txt' fields terminated by ',' optionally enclosed by "" lines terminated by '?';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

Query OK, 6 rows affected (0.02 sec)

mysql> show variables like '%secure%';
+--------------------------+---------------------+
| Variable_name            | Value               |
+--------------------------+---------------------+
| require_secure_transport | OFF                 |
| secure_file_priv         | /Users/qiaopengjun/ |
+--------------------------+---------------------+
2 rows in set (0.01 sec)

mysql>

mysql> select * from mysql_test.customers into outfile '/Users/qiaopengjun/backupfile.txt' fields terminated by ',' optionally enclosed by "" lines terminated by '?';
Query OK, 6 rows affected (0.01 sec)

mysql>

mysql複製表的兩種方式：

第一、只複製表結構到新表
create table 新表 select * from 舊表 where 1=2
或者
create table 新表 like 舊表

第二、複製表結構及數據到新表
create table新表 select * from 舊表

homebrew/var/mysql on  stable took 5.4s
➜ mysql --verbose --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf

homebrew/var/mysql on  stable
➜

brew services restart mysql

將備份數據導入到一個和customers表結構相同的空表 customers_copy 中

mysql> create table customers_copy select * from customers where 1=2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| customers            |
| customers_copy       |
| customers_view       |
| orders               |
| seller               |
+----------------------+
5 rows in set (0.00 sec)

mysql> load data infile '/Users/qiaopengjun/backupfile.txt' into table mysql_test.customers_copy fields terminated by ',' optionally enclosed by "" lines terminated by '?';
Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from customers_copy;
+---------+-----------+----------+-----------+--------------+--------------+
| cust_id | cust_name | cust_sex | cust_city | cust_address | cust_contact |
+---------+-----------+----------+-----------+--------------+--------------+
|     901 | 張三      | F        | 北京市    | 武漢市       | NULL         |
|     902 | 李四      | M        | 武漢市    | 上海市       | NULL         |
|     903 | 李四      | M        | Beijing   | 上海市       | NULL         |
|     904 | 李四      | M        | Beijing   | 上海市       | NULL         |
|     910 | 萬華      | F        | 長沙市    | 芙蓉區       | NULL         |
|     911 | 曾偉      | F        | 長沙市    | NULL         | NULL         |
+---------+-----------+----------+-----------+--------------+--------------+
6 rows in set (0.00 sec)

mysql>

在多個用戶同時使用MySQL資料庫的情況下，為了得到一個一致的備份，需要在指定的表上使用 LOCK TABLES table_name READ 語句做一個讀鎖定，以防止在備份過程中表被其他用戶更新。
當恢複數據時，則需要使用 LOCK TABLES table_name WRITE 語句做一個寫鎖定，以避免發生數據衝突。
在資料庫備份或恢復完畢之後需要使用 UNLOCK TABLES 語句對該表進行解鎖。

本文來自博客園，作者：QIAOPENGJUN，轉載請註明原文鏈接：https://www.cnblogs.com/QiaoPengjun/p/17279396.html

資料庫系統原理之資料庫安全與保護