《MySQL數(shù)據(jù)庫原理與應用案例教程》 課件 項目10 用戶安全性管理

《MySQL數(shù)據(jù)庫項目化教程》項目10用戶安全性管理MySQL是一個多用戶數(shù)據(jù)庫，具有功能強大的訪問控制系統(tǒng)。處于安全方面的考慮，需要創(chuàng)建多個普通用戶，并且為每一用戶賦于對不同數(shù)據(jù)庫的訪問限制，以滿足不同用戶的需求。本項目通過典型任務學習新用戶的創(chuàng)建、查看用戶記錄和用戶管理，以及設置用戶權限和用戶權限管理。前言掌握如何創(chuàng)建新用戶、查看用戶記錄。掌握用戶管理。掌握如何設置、查看用戶權限。掌握如何取消用戶權限。學習目標知識重點創(chuàng)建新用戶。設置用戶權限。知識難點用戶管理。用戶權限管理。知識重點與難點用戶管理任務1任務2用戶權限管理學習任務用戶管理任務1任務描述任務目標任務分析任務實施123知識鏈接45MySQL數(shù)據(jù)庫中，root用戶是超級管理員，擁有所有權限，包括創(chuàng)建用戶、刪除用戶和修改用戶密碼等管理權限。在實際工作過程中，經(jīng)常需要創(chuàng)建擁有不同權限的普通用戶。根據(jù)需求，完成新用戶的創(chuàng)建、查看用戶記錄、修改用戶名、修改用戶密碼和刪除用戶等操作。任務描述（1）會創(chuàng)建用戶。（2）會修改用戶名、修改用戶密碼。（3）會刪除用戶。（4）通過用戶管理，提升學生的數(shù)據(jù)安全意識。任務目標學習user權限表，了解user表中字段的含義。學習使用createuser語句和insert語句創(chuàng)建新用戶的方法，以及用戶管理的語句，完成新用戶的創(chuàng)建、查看用戶記錄、修改用戶名、修改用戶密碼和刪除用戶等操作。任務分析使用createuser語句創(chuàng)建用戶使用insert語句創(chuàng)建用戶修改用戶名user權限表任務實施修改用戶密碼刪除用戶（2）使用createuser創(chuàng)建兩個新用戶，用戶user2的密碼是user2，用戶user3的密碼是user3，連接的主機名都是locahost。mysql>createuser'user2'@'localhost'identifiedby'user2','user3'@'127.0.0.1'identifiedby'user3';任務實施小貼士：在創(chuàng)建用戶的過程中，如果只指定了用戶名而沒指定主機名，則主機名默認為“%”，表示一組主機。用戶名后面連接的如果是本機，可以用關鍵字“l(fā)ocalhost”或者“127.0.0.1”表示。多個用戶之間用“,”分割。步驟2使用insert語句創(chuàng)建用戶使用insert創(chuàng)建一個新用戶，用戶名是user4，密碼是user4，主機名是locahost。mysql>insertintomysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)values('localhost','user4','user4','','','');mysql>flushprivileges;);任務實施

步驟3查看創(chuàng)建的用戶記錄切換到mysql數(shù)據(jù)庫，通過select語句查看user數(shù)據(jù)表中步驟1和步驟2創(chuàng)建的用戶信息。mysql>usemysql;mysql>selecthost,userfromuser;任務實施步驟4修改用戶名將用戶名user1修改為testuser1。mysql>renameuser'user1'@'localhost'to'testuser1'@'localhost';執(zhí)行結(jié)果顯示如圖所示。任務實施步驟5修改用戶密碼修改用戶user2的密碼為testuser2。mysql>setpasswordfor'user2'@'localhost'='testuser2';任務實施步驟6刪除用戶刪除用戶user4。mysql>dropuseruser4@localhost;MySQL在安裝時會自動創(chuàng)建一個名為mysql的數(shù)據(jù)庫，mysql數(shù)據(jù)庫中存儲的都是用戶權限表。用戶登錄以后，MySQL會根據(jù)這些權限表的內(nèi)容為每個用戶賦予相應的權限。user數(shù)據(jù)表是MySQL中最重要的一個權限表，用來記錄允許連接到服務器的賬號信息，以及一些權限信息。在user數(shù)據(jù)表里啟用的所有權限都是全局級的，適用于所有數(shù)據(jù)庫。user數(shù)據(jù)表中的字段大致可以分為4類，分別是用戶列、權限列、安全列和資源控制列。可以使用“desc”語句查看user數(shù)據(jù)表的表結(jié)構(gòu)。（1）用戶列用戶列存儲了用戶連接MySQL數(shù)據(jù)庫時需要輸入的信息。包括Host、User和authentication_string，分別表示主機名、用戶名和密碼。用戶登錄時，如果這3個字段同時匹配，MySQL數(shù)據(jù)庫系統(tǒng)才會允許其登錄。創(chuàng)建新用戶時，也是設置這3個字段的值。修改知識鏈接——user權限表用戶密碼時，實際就是修改user表的authentication_string字段的值。因此，這3個字段決定了用戶能否登錄。（2）權限列權限列的字段決定用戶的權限，用來描述在全局范圍內(nèi)允許對數(shù)據(jù)和數(shù)據(jù)庫進行的操作。權限大致分為兩大類，分別是高級管理權限和普通權限：高級管理權限主要對數(shù)據(jù)庫進行管理，例如關閉服務的權限、超級權限和加載用戶等；普通權限主要操作數(shù)據(jù)庫，例如查詢權限、修改權限等。user表的權限列包括Select_priv、Insert_priv等以priv結(jié)尾的字段，這些字段值的數(shù)據(jù)類型為ENUM，可取的值只有Y和N：Y表示該用戶有對應的權限，N表示該用戶沒有對應的權限。從安全角度考慮，這些字段的默認值都為N。知識鏈接——user權限表（3）安全列安全列主要用來管理用戶的安全信息，其中包括6個字段，具體如下。ssl_type和ssl_cipher：用于加密。x509_issuer和x509_subject：用于標識用戶。plugin和authentication_string：用于存儲與授權相關的插件。（4）資源控制列資源控制列的字段用來限制用戶使用的資源。所有字段的默認值都為0，表示沒有限制。一個小時內(nèi)用戶查詢或者連接數(shù)量超過資源控制限制，用戶將被鎖定，直到下一個小時才可以在此執(zhí)行對應的操作。資源控制列包括以下4個字段：max_questions ：規(guī)定每小時允許執(zhí)行查詢的操作次數(shù)知識鏈接——user權限表max_updates：規(guī)定每小時允許執(zhí)行更新的操作次數(shù)max_connections：規(guī)定每小時允許執(zhí)行的連接操作次數(shù)max_user_connections：規(guī)定允許同時建立的連接次數(shù)知識鏈接——user權限表CREATEUSER：創(chuàng)建用戶關鍵字。user：指定創(chuàng)建用戶賬號，格式為'user_name'@'host_name'。其中user_name是用戶名，host_name為主機名，即用戶連接MySQL時所用主機的名字。如果在創(chuàng)建的過程中，只給出了用戶名，而沒指定主機名，那么主機名默認為“%”，表示一組主機，即對所有主機開放權限。[IDENTIFIEDBY'auth_string']：用于指定用戶密碼。新用戶可以沒有初始密碼，若該用戶不設密碼，可省略此子句。'auth_string'表示用戶登錄時使用的密碼，需要用單引號括起來。知識鏈接——使用createuser語句創(chuàng)建用戶知識鏈接——使用insert語句創(chuàng)建用戶可以使用insert語句直接將用戶的信息添加到mysql.user數(shù)據(jù)表中。使用insert語句創(chuàng)建用戶需要注意以下幾點：（1）通常使用insert語句創(chuàng)建用戶，只添加Host、User和authentication_string這3個字段的值。（2）由于mysql數(shù)據(jù)庫的user表中，ssl_cipher、x509_issuer和x509_subject這3個字段沒有默認值，所以向user表插入新記錄時，一定要設置這3個字段的值，否則insert語句將不能執(zhí)行。（3）user表中的User和Host字段區(qū)分大小寫，創(chuàng)建用戶時要指定正確的用戶名稱或主機名。知識鏈接——使用insert語句創(chuàng)建用戶（4）使用insert語句創(chuàng)建用戶后。這時如果通過該賬戶登錄MySQL服務器，不會登錄成功，因為創(chuàng)建的用戶還沒有生效。需要使用flushprivileges命令刷新系統(tǒng)權限相關數(shù)據(jù)表，讓用戶生效。insert語句創(chuàng)建新用戶的基本語句為：知識鏈接——修改用戶名語法分析：RENAMEUSER：修改用戶名關鍵字。old_user：已經(jīng)存在的用戶名。new_user：修改后的新用戶名。知識鏈接——修改用戶密碼在MySQL中，只有root用戶可以通過更新MySQL數(shù)據(jù)庫來更改密碼。使用root用戶登錄到MySQL服務器后，可以使用set語句來修改普通用戶密碼。SETPASSWORD：修改用戶密碼關鍵字。[FORuser]：表示修改指定主機上特定普通用戶的密碼，此選項為可選項。如果省略，則修改當前用戶密碼。auth_string：為設置的新密碼。知識鏈接——刪除用戶DROPUSER：刪除用戶關鍵字。[IFEXISTS]：用于防止刪除用戶時，在用戶不存在的情況下發(fā)生錯誤。user：需要刪除的用戶，由用戶的用戶名和主機名組成。用戶權限管理任務2任務描述任務目標任務分析任務實施123知識鏈接45

用戶權限管理主要是對數(shù)據(jù)庫的用戶進行權限驗證。所有用戶的權限都存儲在MySQL的權限表中。數(shù)據(jù)庫管理員要對用戶權限進行管理，合理的權限管理能夠保證數(shù)據(jù)庫系統(tǒng)的安全，不合理的權限設置可能會給數(shù)據(jù)庫系統(tǒng)帶來意想不到的危害。根據(jù)需求，完成對用戶權限的設置、查看用戶權限和取消用戶權限等操作。任務描述（1）會設置用戶權限。（2）會查看用戶權限。（3）會取消用戶權限。（4）通過用戶權限管理，提升學生的數(shù)據(jù)安全意識。任務目標學習設置用戶權限、查看用戶權限、取消用戶權限的命令，根據(jù)實際任務需求，完成對用戶權限的設計和管理。任務分析設置用戶權限查看用戶權限取消用戶權限任務實施MySQL的權限步驟1設置用戶權限（1）創(chuàng)建一個新的用戶user10，密碼為user10。授予用戶user10對所有的數(shù)據(jù)庫的所有表有查詢、插入權限，并授予grant權限。mysql>createuser'user10'@'localhost'identifiedby'user10';mysql>grantselect,inserton*.*to'user10'@'localhost'withgrantoption;任務實施（2）創(chuàng)建一個新的用戶user11，密碼為user11。授予用戶user11在數(shù)據(jù)庫chjgl_db的所有權限。mysql>createuser'user11'@'localhost'identifiedby'user11';mysql>grantallonchjgl_db.*to'user11'@'localhost';任務實施（3）創(chuàng)建一個新的用戶user12，密碼為user12。授予用戶user12在數(shù)據(jù)庫chjgl_db中的tb_student數(shù)據(jù)表上查詢、刪除權限。mysql>createuser'user12'@'localhost'identifiedby'user12';mysql>grantselect,deleteonchjgl_db.tb_studentto'user12'@'localhost';任務實施（4）創(chuàng)建一個新的用戶user13，密碼為user13。授予用戶user13在數(shù)據(jù)庫chjgl_db中的tb_student數(shù)據(jù)表上，對sname、ssex字段值具有修改的權限。mysql>createuser'user13'@'localhost'identifiedby'user13';mysql>grantupdate(sname,ssex)onchjgl_db.tb_studentto'user13'@'localhost';任務實施任務實施步驟2查看用戶權限查看用戶user10的權限。mysql>showgrantsfor'user10'@'localhost';小貼士：由于用戶的基本權限存儲在mysql.user數(shù)據(jù)表中，因此，查看用戶權限也可以使用“select*frommysql.user;”語句。任務實施步驟3取消用戶權限（1）取消用戶user10的所有權限，并查看取消后user10的權限。mysql>revokeallprivileges,grantoptionfrom'user10'@'localhost';mysql>showgrantsfor'user10'@'localhost';（2）取消用戶user11在數(shù)據(jù)庫chjgl_db的所有數(shù)據(jù)表的update和delete權限。mysql>revokeupdate,deleteonchjgl_db.*from'user11'@'localhost';任務實施MySQL中的權限信息被存儲在MySQL數(shù)據(jù)庫的user、db、host、columns_priv和tables_priv等數(shù)據(jù)表中。MySQL的相關權限以及在user數(shù)據(jù)表中對應的字段和權限范圍見表10-1。知識鏈接——MySQL的權限知識鏈接——MySQL的權限知識鏈接——MySQL的權限在特定的SQL語句中對MySQL權限有更具體的要求，表中部分權限說明如下：（1）create權限，可以創(chuàng)建數(shù)據(jù)庫、數(shù)據(jù)表、索引。（2）drop權限，可以刪除已有的數(shù)據(jù)庫、數(shù)據(jù)表、索引。（3）insert、delete、update、select權限，可以對數(shù)據(jù)庫中的數(shù)據(jù)表進行增加、刪除、更新和查詢操作。（4）alter權限，可以用于修改數(shù)據(jù)表的結(jié)構(gòu)或重命名數(shù)據(jù)表。（5）grantoption權限，允許為其他用戶授權，可用于數(shù)據(jù)庫和數(shù)據(jù)表。（6）all[privileges]權限，授予在指定的訪問級別的所有權限，除了grantoption和proxy。（7）usage權限，“noprivileges”的同義詞，沒有權限。知識鏈接——MySQL的權限知識鏈接——設置用戶權限一般情況下，數(shù)據(jù)庫管理員首先使用createuser創(chuàng)建用戶并定義其非特權特征，比如密碼，是否使用安全連接以及對服務器資源的訪問限制，然后使用grant語句來設置用戶的權限。其基本語法格式為：GRANT：設置用戶權限關鍵字。priv_type：表示設置權限的類型。例如，select、insert、delete等。column_list：表示權限作用于哪些字段，如果省略該參數(shù)，則作用于整個數(shù)據(jù)表。priv_level：用于指定權限級別的值，有以下幾類格式：知識鏈接——設置用戶權限（1）*：表示當前數(shù)據(jù)庫中的所有表。（2）*.*：表示所有數(shù)據(jù)庫中的所有表。（3）db_name.*：表示某個數(shù)據(jù)庫中的所有表，db_name指定數(shù)據(jù)庫名。（4）db_name.tbl_name：表示某個數(shù)據(jù)庫中的某個表或視圖，db_name指定數(shù)據(jù)庫名，tbl_name指定表名或視圖名。（5）db_name.routine_name：表示某個數(shù)據(jù)庫中的某個存儲過程或函數(shù)，routine_name指定存儲過程名或函數(shù)名。user：設置權限的用戶，由用戶的用戶名和主機名組成。格式為'username'@'hostname'。WI