資料庫安全與保護

第一節資料庫完整性

資料庫完整性是指資料庫中資料的正確性和相容性，
資料完整性約束是為了防止資料庫中存在不符合語意的資料，為了維護資料的完整性，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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548939.html

標籤：MySQL

上一篇：Linux服務器MySQL操作總結

下一篇：hadoop3.3 安裝配置sqoop1.4.7

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