MySQL數(shù)據(jù)庫原理與應(yīng)用項(xiàng)目化教程（微課版） 課件 (含思政) 項(xiàng)目11-數(shù)據(jù)庫的安全管理

項(xiàng)目十一數(shù)據(jù)庫的安全管理

MySQL提供了一套完整的安全性機(jī)制保證數(shù)據(jù)的安全性：要防止普通用戶隨意訪問，另外，如果發(fā)生軟硬件故障、自然災(zāi)害和操作失誤等意外情況時(shí)，要能恢復(fù)數(shù)據(jù)，盡可能地挽回或減少數(shù)據(jù)的損失。本項(xiàng)目學(xué)習(xí)MySQL8.0的用戶管理、權(quán)限管理、數(shù)據(jù)的備份與還原等操作。知識(shí)目標(biāo)：識(shí)記系統(tǒng)數(shù)據(jù)庫mysql中user、db、tables_priv等權(quán)限表的作用。識(shí)記創(chuàng)建用戶、修改用戶密碼、刪除用戶語句的語法。識(shí)記查看、授予、收回用戶權(quán)限語句的語法。識(shí)記mysqldump命令備份數(shù)據(jù)庫的語法。能力目標(biāo)：能用語句創(chuàng)建用戶、修改用戶密碼、刪除用戶。能用語句查看、授予、收回用戶權(quán)限。能選擇一個(gè)、多個(gè)或所有數(shù)據(jù)庫進(jìn)行備份并還原。任務(wù)11.1任務(wù)11.2用戶管理權(quán)限管理任務(wù)11.3數(shù)據(jù)的備份與還原

任務(wù)11.1用戶管理針MySQL主要包含兩種用戶：root用戶和普通用戶。root用戶是系統(tǒng)安裝時(shí)自帶的，為超級(jí)管理員，擁有軟件提供的一切權(quán)限，普通用戶只擁有創(chuàng)建后賦予它的權(quán)限。通過root用戶完成查看用戶、創(chuàng)建用戶、修改用戶密碼、刪除用戶等操作。【任務(wù)描述】用戶管理11.1【相關(guān)知識(shí)】11.1.311.1.111.1.2

修改用戶密碼查看用戶

創(chuàng)建用戶11.1.4

刪除用戶用戶管理11.1查看用戶并沒有直接的SQL語句，而要通過系統(tǒng)數(shù)據(jù)庫mysql的user表，直接用SELECT*FROMuser來查看有哪些用戶，會(huì)列出數(shù)據(jù)庫所有用戶及其權(quán)限。user表有幾十個(gè)字段，大致分為4類：用戶列，權(quán)限列、安全列和資源控制列，user表的主鍵是Host和User列的組合。用戶登錄服務(wù)器時(shí)，服務(wù)器會(huì)根據(jù)user表中Host、User、authentication_string、account_locked（分別存儲(chǔ)主機(jī)名、用戶名、密碼、帳戶鎖定狀態(tài)）這幾個(gè)字段的值進(jìn)行判斷是否接受用戶的登錄。說明：

MySQL5.7版本開始user表的password字段改為了authentication_stringMySQL8.0版本開始移除了加密函數(shù)password()。查看用戶11.1.1創(chuàng)建用戶用CREATEUSER語句。語法格式：CREATEUSER'用戶名'@'主機(jī)名'

[IDENTIFIED[WITH身份驗(yàn)證加密規(guī)則]BY'密碼']；說明：用戶名：登錄數(shù)據(jù)庫服務(wù)器使用的用戶名。主機(jī)名：表示允許用戶從哪臺(tái)機(jī)器登陸，本機(jī)登陸填'localhost'，遠(yuǎn)程登陸則填'%'

IDENTIFIEDBY子句用來設(shè)置用戶登錄密碼。身份驗(yàn)證加密規(guī)則：可選擇caching_sha2_password或mysql_native_password，如果省略WITH子句，默認(rèn)為配置文件my.ini中參數(shù)default_authentication_plugin的值。創(chuàng)建用戶11.1.21.使用ALTERUSER語句

語法格式：ALTERUSER'用戶名'@'主機(jī)名'[IDENTIFIEDBY'密碼']；

2.使用SETPASSWORD命令

語法格式：

SETPASSWORD[FOR'用戶名'@'主機(jī)名']='新密碼';說明：FOR子句用來指定修改密碼的帳戶，如果省略表示設(shè)置當(dāng)前用戶密碼。普通用戶可以用設(shè)置當(dāng)前用戶密碼的方式修改自己的密碼。修改用戶密碼11.1.3刪除用戶帳號(hào)用DROPUSER

語句。

語法格式：

DROPUSER'用戶名'@'主機(jī)名'[,…];說明：一次可以刪除多個(gè)用戶帳號(hào)，用戶帳號(hào)之間用逗號(hào)隔開。刪除用戶11.1.4【任務(wù)實(shí)施】查看所有用戶的主機(jī)名、用戶名、密碼及賬戶鎖定狀態(tài)。USEmysql

SELECTHost,User,authentication_string,account_lockedFROMuser;用戶管理11.1圖11.1查看所有用戶的主機(jī)名、用戶名、密碼及賬戶鎖定狀態(tài)【任務(wù)實(shí)施】2．創(chuàng)建一個(gè)新用戶：用戶名為“zhang”，密碼為“z123”，只允許本機(jī)登陸。CREATEUSER'zhang'@'localhost'IDENTIFIEDBY'z123';用戶管理11.1圖11.2創(chuàng)建“zhang”用戶并查看【任務(wù)實(shí)施】3.創(chuàng)建一個(gè)新用戶：用戶名為“wang”，密碼為“w123”，允許其從其他電腦遠(yuǎn)程登陸。

CREATEUSER'wang'@'%'IDENTIFIEDBY'w123';用戶管理11.1圖11.3創(chuàng)建“wang”用戶并查看【任務(wù)實(shí)施】重開一個(gè)cmd窗口，試著以“wang”用戶連接MySQL服務(wù)器，結(jié)果如圖11.4所示。用戶管理11.1圖11.4“wang”用戶連接MySQL服務(wù)器【任務(wù)實(shí)施】4.修改“wang”用戶的密碼，新密碼為“123456”。（1）通過root用戶修改ALTERUSER'wang'@'%'IDENTIFIEDBY'123456';或者SETPASSWORDFOR'wang'@'%'='123456';（2）用戶自己修改SETPASSWORD='123456';用戶管理11.1【任務(wù)實(shí)施】用戶管理11.1圖11.5修改“wang”用戶密碼【任務(wù)實(shí)施】5.刪除“wang”、“zhang”這二個(gè)用戶。

DROPUSER'wang'@'%','zhang'@'localhost';用戶管理11.1圖11.6刪除“wang”用戶、“zhang”用戶思政小貼士【數(shù)據(jù)泄密案例，結(jié)合騷擾電話、詐騙電話的生活體驗(yàn)】重視信息泄露和信息安全問題，樹立正確的職業(yè)道德和職業(yè)操守加強(qiáng)責(zé)任感11.1用戶管理任務(wù)11.2權(quán)限管理新創(chuàng)建的用戶可以連接服務(wù)器，但不具備訪問數(shù)據(jù)庫的實(shí)質(zhì)權(quán)限。

通過給指定用戶授權(quán)使其能訪問“學(xué)生成績(jī)管理”數(shù)據(jù)庫的數(shù)據(jù)，還可以根據(jù)需要隨時(shí)收回權(quán)限?！救蝿?wù)描述】權(quán)限管理11.2【相關(guān)知識(shí)】11.2.311.2.111.2.2

收回權(quán)限

查看權(quán)限

授予權(quán)限權(quán)限管理11.2用戶權(quán)限存儲(chǔ)在mysql數(shù)據(jù)庫的user、db、tables_priv、columns_priv、procs_priv等權(quán)限表中。user表存儲(chǔ)全局權(quán)限，全局權(quán)限對(duì)任何數(shù)據(jù)庫有效；db表存儲(chǔ)特定數(shù)據(jù)庫的權(quán)限；tables_priv表、columns_priv表和procs_priv表分別存儲(chǔ)特定表、特定列和特定存儲(chǔ)過程及存儲(chǔ)函數(shù)的權(quán)限。用戶通過身份驗(yàn)證成功連接MySQL服務(wù)器后，服務(wù)器要對(duì)用戶進(jìn)行操作權(quán)限驗(yàn)證，權(quán)限驗(yàn)證按照user表、db表，tables_priv表，columns_priv表的順序進(jìn)行。查看權(quán)限11.2.1MySQL提供的常用權(quán)限請(qǐng)參見教材中的表11-1。通過SELECT語句查詢權(quán)限表，可以查看用戶不同級(jí)別的權(quán)限。MySQL提供了SHOWGRANTS語句可以方便地查看某個(gè)用戶所擁有的各級(jí)權(quán)限，語法格式如下：SHOWGRANTS[FOR'用戶名'@'主機(jī)名'];說明：如果省略FOR子句，表示當(dāng)前用戶查看自己的權(quán)限。通過FOR子句，root用戶可以查看指定用戶的權(quán)限。查看權(quán)限11.2.1授予用戶權(quán)限使用

GRANT語句，語法格式如下：

GRANT權(quán)限[(列名列表)]ON庫名.表名

TO'用戶名'@'主機(jī)名'[,…]

[WITHwith-option[withoption]…];說明：權(quán)限可以是多個(gè)，用逗號(hào)隔開，如果是全部權(quán)限，可以使用allprivileges，簡(jiǎn)寫為all。列名列表可選，表示權(quán)限作用于哪些列上，沒有此選項(xiàng)則表示權(quán)限作用于整個(gè)表上。如果授予的權(quán)限對(duì)任何數(shù)據(jù)庫有效，則“庫名.表名”要寫成“*.*”；如果權(quán)限對(duì)指定數(shù)據(jù)庫的所有表都有效，則“庫名.表名”要寫成“庫名.*”。

可以把權(quán)限一次授予多個(gè)用戶，用戶帳號(hào)之間用逗號(hào)隔開。WITH子句指定授權(quán)選項(xiàng)，最常用的是WITHGRANTOPTION，表示允許被授權(quán)用戶將此權(quán)限授予其他用戶

。授予權(quán)限11.2.2收回用戶權(quán)限使用REVOKE語句，語法格式如下：REVOKE權(quán)限[(列名列表)]ON庫名.表名

FROM'用戶名'@'主機(jī)名'[,…]可以把一個(gè)用戶的權(quán)限用一條語句全部收回，語法格式如下：REVOKEALL,GRANTOPTIONFROM'用戶名'@'主機(jī)名'[,…];說明：MySQL的權(quán)限不能級(jí)聯(lián)收回。收回某個(gè)用戶的全部權(quán)限后，用戶權(quán)限回到剛創(chuàng)建時(shí)狀態(tài)，除了登錄連接服務(wù)器，幾乎沒有什么權(quán)限。收回權(quán)限11.2.3【任務(wù)實(shí)施】1.查看二個(gè)新建用戶的權(quán)限（帳號(hào)分別為‘zhang’@’localhost’、‘wang’@’localhost’）。（1）root用戶查看普通用戶權(quán)限SHOWGRANTSFOR’wang’@’localhost’;權(quán)限管理11.2圖11.7root用戶查看wang用戶的權(quán)限【任務(wù)實(shí)施】（2）zhang用戶查看自己的權(quán)限再開一個(gè)cmd窗口，以zhang用戶身份連接服務(wù)器，輸入以下代碼：SHOWGRANTS;權(quán)限管理11.2圖11.8zhang用戶查看自己的權(quán)限【任務(wù)實(shí)施】2.授予“zhang”用戶查詢及修改studb數(shù)據(jù)庫中所有表數(shù)據(jù)的權(quán)限，并允許其將此權(quán)限授予其他用戶。

GRANTSELECT,UPDATEONstudb.*TO'zhang'@'localhost'WITHGRANTOPTION;權(quán)限管理11.2圖11.9給zhang用戶授權(quán)并查看權(quán)限【任務(wù)實(shí)施】3.通過“zhang”用戶給“wang”用戶授予查看stuinfo表的權(quán)限。GRANTSELECTONstudb.stuinfoTO'wang'@'localhost';權(quán)限管理11.2圖11.10zhang用戶給wang用戶授權(quán)【任務(wù)實(shí)施】4.授予用戶“zhang”在studb數(shù)據(jù)庫中創(chuàng)建表的權(quán)限。GRANTCREATEONstudb.*TO'zhang'@'localhost';權(quán)限管理11.2圖11.11給zhang用戶再次授權(quán)并查看權(quán)限【任務(wù)實(shí)施】5.收回“zhang”、“wang”二個(gè)用戶所有權(quán)限。

（1）收回zhang用戶的所有權(quán)限

REVOKEALLPRIVILEGES,GRANTOPTIONFROM'zhang'@'localhost';權(quán)限管理11.2圖11.12收回zhang用戶的全部權(quán)限【任務(wù)實(shí)施】zhang用戶權(quán)限全部收回了，但是通過查看wang用戶的權(quán)限，證明授予wang用戶的權(quán)限沒有受到影響。權(quán)限管理11.2圖11.13zhang授予wang的權(quán)限不受影響【任務(wù)實(shí)施】（2）收回wang用戶的查看權(quán)限REVOKESELECTONstudb.stuinfoFROM'wang'@'localhost';權(quán)限管理11.2圖11.14收回wang用戶的權(quán)限并查看任務(wù)11.3數(shù)據(jù)的備份與還原數(shù)據(jù)的備份與還原操作不但可以避免因意外發(fā)生造成的數(shù)據(jù)損失，還可以實(shí)現(xiàn)數(shù)據(jù)庫的遷移，因?yàn)閿?shù)據(jù)庫的遷移不能通過簡(jiǎn)單的復(fù)制（或剪切）、粘貼數(shù)據(jù)文件操作來實(shí)現(xiàn)。利用mysqldump工具導(dǎo)出“學(xué)生成績(jī)管理”數(shù)據(jù)庫的sql腳本文件實(shí)現(xiàn)數(shù)據(jù)的備份，通過執(zhí)行該腳本文件實(shí)現(xiàn)數(shù)據(jù)還原?！救蝿?wù)描述】11.3數(shù)據(jù)的備份與還原【相關(guān)知識(shí)】21

還原數(shù)據(jù)

備份數(shù)據(jù)11.3數(shù)據(jù)的備份與還原mysqldump

工具支持一次備份單個(gè)數(shù)據(jù)庫、多個(gè)數(shù)據(jù)庫和所有數(shù)據(jù)庫。備份一個(gè)數(shù)據(jù)庫

語法格式如下：

mysqldump-uusername-pdbname[tbname1tbname2…]>backupname.sql說明：Username：執(zhí)行備份的用戶名。dbname：要備份的數(shù)據(jù)庫的名稱，tbname1、tbname2表示數(shù)據(jù)庫中的表名。backupname.sql：備份導(dǎo)出的SQL腳本文件名，可以包含該文件所在路徑，文件擴(kuò)展名“sql”表示是SQL腳本文件。備份產(chǎn)生的SQL腳本文件中不包含創(chuàng)建數(shù)據(jù)庫的語句。備份數(shù)據(jù)11.3.1(2)備份多個(gè)數(shù)據(jù)庫語法格式如下：mysqldump-uusername-p--databasesdbname1dbname2….>backupname.sql說明：databases前面有2個(gè)“-”，“—databases”后面跟多個(gè)數(shù)據(jù)庫名稱，多個(gè)數(shù)據(jù)庫名之間用空格分隔。備份產(chǎn)生的SQL腳本文件中包含了創(chuàng)建數(shù)據(jù)庫的語句。備份數(shù)據(jù)11.3.1(3)

備份所有數(shù)據(jù)庫語法格式如下：mysqldump-uusername-p--all-databases>backupname.sql說明：“--all-databases”表示備份所有數(shù)據(jù)庫。備份產(chǎn)生的SQL腳本文件中包含了創(chuàng)建數(shù)據(jù)庫的語句。備份數(shù)據(jù)11.3.1MySQL還原數(shù)據(jù)有兩種常用的方式。

(1)使用mysql工具語法格式如下：mysql–uusername-p[dbname]<backupname.sql說明：dbname：要還原數(shù)據(jù)庫的名稱，只有還原一個(gè)數(shù)據(jù)庫時(shí)需要提供。backupname.sql：需要還原的SQL腳本文件，如果不在當(dāng)前路徑下，要指定該文件所在路徑。

由于只有在備份一個(gè)數(shù)據(jù)庫時(shí)，導(dǎo)出的SQL腳本文件中沒有創(chuàng)建數(shù)據(jù)庫的語句，因此，在還原一個(gè)數(shù)據(jù)庫的數(shù)據(jù)前，要確認(rèn)該數(shù)據(jù)庫已存在，如果不存在要先創(chuàng)建。還原數(shù)據(jù)11.3.2(2)使用source命令

source命令是mysql客戶端程序提供的命令，語法格式如下：

sourcebackupname.sql說明：backupname.sql：需要還原的SQL腳本文件，如果不在當(dāng)前路徑下，要指定該文件所在路徑。如果backupname.sql是單個(gè)數(shù)據(jù)庫的備份文件，執(zhí)行source命令前需要先用USE命令切換到需要還原的數(shù)據(jù)庫。還原數(shù)據(jù)11.3.2思政小貼士【備份一個(gè)數(shù)據(jù)庫的備份文件與多個(gè)數(shù)據(jù)庫的備份文件的不同（一個(gè)有建庫語句，一個(gè)沒有），因此還原一個(gè)數(shù)據(jù)庫和多個(gè)數(shù)據(jù)庫的操作不同】學(xué)習(xí)要知其然知其所以然（知道是這樣，也知道為什么是這樣），知其然是知識(shí)，所以然是方法，學(xué)習(xí)方法比單純的知識(shí)要重要的多。11.3數(shù)據(jù)的備份與還原【任務(wù)實(shí)施】備份studb數(shù)據(jù)庫并還原。（1）備份數(shù)據(jù)

打開一個(gè)cmd窗口，在DOS提示符后輸入命令：

mysqldump-uroot-pstudb>d:\backup\studb.sql11.3數(shù)據(jù)的備份與還原圖11.15備份一個(gè)數(shù)據(jù)庫（studb）【任務(wù)實(shí)施】（2）還原數(shù)據(jù)①用mysql工具

mysql-uroot-pstudb<d:\backup\studb.sql11.3數(shù)據(jù)的備份與還原圖11.16用mysql工具還原studb數(shù)據(jù)庫【任務(wù)實(shí)施】②用source命令啟動(dòng)mysql客戶端程序并切換到studb數(shù)據(jù)庫，輸入下面的命令：sourced:\back