MySQL心得9–用戶和數據安全
1. 添加用戶
可以使用CREATE USER語法添加一個或多個用戶，並設置相應的密碼。 語法格式：
CREATE USER user [identified BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] …
www.aiwalls.com
其中，user的格式為： 'user_name'@ 'host name'
說明：
user_name為用戶名，host_name為主機名，password為該用戶的密碼。在大多數SQL產品中，用戶名和密碼隻由字母和數字組成。
使用自選的IDENTIFIED BY子句，可以為賬戶給定一個密碼。特別是要在純文本中指定密碼，需忽略PASSWORD關鍵詞。如果不想以明文發送密碼，而且知道PASSWORD()函數返回給密碼的混編值，則可以指定該混編值，但要加關鍵字PASSWORD。
CREATE USER用於創建新的MySQL賬戶。CREATE USER會在系統本身的mysql數據庫的user表中添加一個新記錄。要使用CREATE USER，必須擁有mysql數據庫的全局CREATE USER權限或INSERT權限。如果賬戶已經存在，則出現錯誤。
例： 添加兩個新的用戶，king的密碼為queen，palo的密碼為530415。
CREATE USER
'king'@'localhost'IDENTIFIED BY 'queen',
www.aiwalls.com
'palo'@'localhost'IDENTIFIED BY '530415';
說明：
在用戶名的後面聲明瞭關鍵字localhost。這個關鍵字指定瞭用戶創建的使用MySQL的連接所來自的主機。如果一個用戶名和主機名中包含特殊符號如“_”，或通配符如“%”，則需要用單引號將其括起。“%”表示一組主機，所有人都可以訪問。
如果兩個用戶具有相同的用戶名但主機不同，MySQL將其視為不同的用戶，允許為這兩個用戶分配不同的權限集合。
如果沒有輸入密碼，那麼MySQL允許相關的用戶不使用密碼登錄。但是從安全的角度並不推薦這種做法。
剛剛創建的用戶還沒有很多權限。它們可以登錄到MySQL，但是它們不能使用USE語句來讓用戶已經創建的任何數據庫成為當前數據庫，因此，它們無法訪問那些數據庫的表，隻允許進行不需要權限的操作，例如，用一條SHOW語句查詢所有存儲引擎和字符集的列表。
2. 刪除用戶
語法格式：DROP USER user [, user_name] …
DROP USER語句用於刪除一個或多個MySQL賬戶，並取消其權限。要使用DROPUSER，必須擁有mysql數據庫的全局CREATE USER權限或DELETE權限。
例： 刪除用戶TOM ：DROP USER TOM@localhost;
如果刪除的用戶已經創建瞭表、索引或其他的數據庫對象，它們將繼續保留，因為MySQL並沒有記錄是誰創建瞭這些對象。
3. 修改用戶
可以使用rename USER語句來修改一個已經存在的SQL用戶的名字。 語法格式：RENAME USER old_user TO new_user,
[, old_user TO new_user] …
說明：
old_user為已經存在的SQL用戶。new_user為新的SQL用戶。
RENAME USER語句用於對原有MySQL賬戶進行重命名。要使用RENAME USER，必須擁有全局CREATE USER權限或mysql數據庫UPDATE權限。如果舊賬戶不存在或者新賬戶已存在，則會出現錯誤。
例: 將用戶king1和king2的名字分別修改為ken1和ken2。
RENAME USER
www.aiwalls.com
'king1'@'localhost' TO' ken1'@'localhost',
'king2'@'localhost' TO' ken2'@'localhost';
4. 修改密碼
要修改某個用戶的登錄密碼，可以使用SET PASSWORD語句。
語法格式：SET PASSWORD [FOR user]= PASSWORD('newpassword')
說明：
如果不加FOR user，表示修改當前用戶的密碼。加瞭FORuser則是修改當前主機上的特定用戶的密碼，user為用戶名。user的值必須以'user_name'@'host_name'的格式給定。
例： 將用戶king的密碼修改為queen1。
SET PASSWORD FOR 'king'@'localhost' =PASSWORD('queen1');
5. 授予權限
新的SQL用戶不允許訪問屬於其他SQL用戶的表，也不能立即創建自己的表，它必須被授權。可以授予的權限有以下幾組。
（1）列權限：和表中的一個具體列相關。例如，使用UPDATE語句更新表XS學號列的值的權限。
（2）表權限：和一個具體表中的所有數據相關。例如，使用SELECT語句查詢表XS的所有數據的權限。
（3）數據庫權限：和一個具體的數據庫中的所有表相關。例如，在已有的XSCJ數據庫中創建新表的權限。
（4）用戶權限：和MySQL所有的數據庫相關。例如，刪除已有的數據庫或者創建一個新的數據庫的權限。
給某用戶授予權限可以使用grant語句。使用showgrants語句可以查看當前賬戶擁有什麼權限。
GRANT語法格式： GRANT priv_type [(column_list)] [, priv_type[(column_list)]] …
ON [object_type] {表名 | * | *.* | 庫名.*}
TO user [IDENTIFIED BY [PASSWORD]'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] …
[WITH with_option [with_option] …]
其中，object_type：
TABLE
| FUNCTION
| PROCEDURE
with_option ：
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
www.aiwalls.com
說明：priv_type為權限的名稱，如SELECT、UPDATE等，給不同的對象授予權限priv_type的值也不相同。TO子句用來設定用戶的密碼。ON關鍵字後面給出的是要授予權限的數據庫或表名，下面將一一介紹。
（1）授予表權限和列權限
授予表權限時，priv_type可以是以下值：
● SELECT：給予用戶使用SELECT語句訪問特定的表的權力。用戶也可以在一個視圖公式中包含表。然而，用戶必須對視圖公式中指定的每個表（或視圖）都有SELECT權限。
● INSERT：給予用戶使用INSERT語句向一個特定表中添加行權力.
● DELETE：給予用戶使用DELETE語句向一個特定表中刪除行權力。
● UPDATE：給予用戶使用UPDATE語句修改特定表中值的權力。
● REFERENCES：給予用戶創建一個外鍵來參照特定的表的權力。
● CREATE：給予用戶使用特定的名字創建一個表的權力。
● ALTER：給予用戶使用ALTERTABLE語句修改表的權力。
● INDEX：給予用戶在表上定義索引的權力。
● DROP：給予用戶刪除表的權力。
● ALL或ALL PRIVILEGES：表示所有權限名。
在授予表權限時，ON關鍵字後面跟tb1_name，tb1_name為表名或視圖名。
例： 授予用戶king在XS表上的SELECT權限。
USE XSCJ;
GRANT SELECT ON XS TOking@use_name;
說明：
若在TO子句中給存在的用戶指定密碼，則新密碼將原密碼覆蓋。如果權限授予瞭一個不存在的用戶，MySQL會自動執行一條CREATE USER語句來創建這個用戶，但必須為該用戶指定密碼。
例：用戶liu和zhang不存在，授予它們在XS表上的SELECT和UPDATE權限。
GRANT SELECT,UPDATE ON XS
TO liu@localhost IDENTIFIED BY 'LPWD',
zhang@localhostIDENTIFIED BY 'ZPWD';
對於列權限，priv_type的值隻能取SELECT、INSERT和UPDATE。權限的後面需要加上列名column_list。
例： 授予king在XS表上的學號列和姓名列的UPDATE權限。
GRANT UPDATE(姓名, 學號) ON XS TO king@localhost;
（2）授予數據庫權限 www.aiwalls.com
表權限適用於一個特定的表。MySQL還支持針對整個數據庫的權限。例如，在一個特定的數據庫中創建表和視圖的權限。
授予數據庫權限時，priv_type可以是以下值：
● SELECT：給予用戶使用SELECT語句訪問特定數據庫中所有表和視圖的權力。
● INSERT：給予用戶使用INSERT語句向特定數據庫中所有表添加行的權力。
● DELETE：給予用戶使用DELETE語句刪除特定數據庫中所有表的行的權力。
● UPDATE：給予用戶使用UPDATE語句更新特定數據庫中所有表的值的權力。
● REFERENCES：給予用戶創建指向特定的數據庫中的表外鍵的權力。
● CREATE：給予用戶使用CREATETABLE語句在特定數據庫中創建新表的權力。
● ALTER：給予用戶使用ALTERTABLE語句修改特定數據庫中所有表的權力。
● INDEX：給予用戶在特定數據庫中的所有表上定義和刪除索引的權力。
● DROP：給予用戶刪除特定數據庫中所有表和視圖的權力。
● CREATE TEMPORARY TABLES：給予用戶在特定數據庫中創建臨時表的權力。
● CREATE VIEW：給予用戶在特定數據庫中創建新的視圖的權力。
● SHOW VIEW：給予用戶查看特定數據庫中已有視圖的視圖定義的權力。
● CREATE ROUTINE：給予用戶為特定的數據庫創建存儲過程和存儲函數等權力。
● ALTER ROUTINE：給予用戶更新和刪除數據庫中已有的存儲過程和存儲函數等權力。
● EXECUTE ROUTINE：給予用戶調用特定數據庫的存儲過程和存儲函數的權力。
● LOCK TABLES：給予用戶鎖定特定數據庫的已有表的權力。
● ALL或ALL PRIVILEGES：表示以上所有權限名。
在GRANT語法格式中，授予數據庫權限時ON關鍵字後面跟“*”和“db_name.*”。“*”表示當前數據庫中的所有表；“db_name.*”表示某個數據庫中的所有表；*.*表示所有庫下所有表。
例： 授予king在XSCJ數據庫中的所有表的SELECT權限。
GRANT SELECT ON XSCJ.* TO king@localhost;
說明：這個權限適用於所有已有的表，以及此後添加到XSCJ數據庫中的任何表。
例： 授予king在XSCJ數據庫中所有的數據庫權限。
USE XSCJ; www.aiwalls.com
GRANT ALL ON * TO king@localhost;
和表權限類似，授予一個數據庫權限也不意味著擁有另一個權限。如果用戶被授予可以創建新表和視圖，但是還不能訪問它們。要訪問它們，它還需要單獨被授予SELECT權限或更多權限。
（3）授予用戶權限
最有效率的權限就是用戶權限，對於需要授予數據庫權限的所有語句，也可以定義在用戶權限上。例如，在用戶級別上授予某人CREATE權限，這個用戶可以創建一個新的數據庫，也可以在所有的數據庫（而不是特定的數據庫）中創建新表。
MySQL授予用戶權限時priv_type還可以是以下值。
● CREATE USER：給予用戶創建和刪除新用戶的權力。
● SHOW DATABASES：給予用戶使用SHOW DATABASES語句查看所有已有的數據庫的定義的權利。
在GRANT語法格式中，授予用戶權限時ON子句中使用“*.*”，表示所有數據庫的所有表。
例： 授予Peter對所有數據庫中的所有表的CREATE、ALTERT和DROP權限。
GRANT CREATE ,ALTER,DROP
ON *.* TO Peter@localhost IDENTIFIED BY 'ppwd';
例： 授予Peter創建新用戶的權力。
GRANT CREATE USER
www.aiwalls.com
ON *.* TO Peter@localhost;
6. 權限的轉移和限制
GRANT語句的最後可以使用WITH子句。如果指定為WITH GRANT OPTION，則表示TO子句中指定的所有用戶都有把自己所擁有的權限授予其他用戶的權利，而不管其他用戶是否擁有該權限。
例： 授予David在XS表上的SELECT權限，並允許其將該權限授予其他用戶。
GRANT SELECT ON XSCJ.XS
TO David@localhost IDENTIFIED BY '123456'
WITH GRANT OPTION;
可以把這個權限傳遞給其他用戶，這裡假設用戶Jim已經創建：
GRANT SELECT ON XSCJ.XS TO Jim@localhost;
說明：使用瞭WITH GRANT OPTION子句後，如果David在該表上還擁有其他權限，他可以將其他權限也授予Jim而不僅限於SELECT。
WITH子句也可以對一個用戶授予使用限制，其中，MAX_queries_PER_HOURcount表示每小時可以查詢數據庫的次數；MAX_connections_PER_HOURcount表示每小時可以連接數據庫的次數；MAX_UPDATES_PER_HOURcount表示每小時可以修改數據庫的次數。count表示同時連接MySQL的最大用戶數。count是一個數值，對於前三個指定，count如果為0則表示不起限制作用。
例： 授予Jim每小時隻能處理一條SELECT語句的權限。
GRANT SELECT ON XS TO Jim@localhost
WITH MAX_QUERIES_PER_HOUR1;
7. 收回權限
要從一個用戶回收權限，但不從USER表中刪除該用戶，可以使用REVOKE語句，這條語句和GRANT語句格式相似，但具有相反的效果。要使用REVOKE，用戶必須擁有mysql數據庫的全局CREATE USER權限或UPDATE權限。
語法格式： REVOKE priv_type[(column_list)] [, priv_type [(column_list)]] …
ON {表名 | * |*.* | 庫名.*}
FROM user [, user] …
或者： www.aiwalls.com
REVOKE ALL privileges, GRANT OPTION FROM user [, user] …
說明：第一種格式用來回收某些特定的權限，第二種格式回收所有該用戶的權限。
例： 回收用戶David在XS表上的SELECT權限。
REVOKE SELECT ON XS FROM David@localhost;
由於David用戶對XS表的SELECT權限被回收瞭，那麼包括直接或間接地依賴於它的所有權限也回收瞭，在這個例子中，Jim也失去瞭對XS表的SELECT權限。但以上語句執行之後WITH GRANT OPTION還保留，當再次授予David對於同一個表的表權限時，它會立刻把這個權限傳遞給Jim。
以下非重點，屬於瞭解內容：（表維護語句）
1．analyze table語句
在一個定義瞭索引的列上，該列上不同值的數目被稱為該索引列的可壓縮性，可以使用SHOWINDEX FROM tb_name語句來顯示它。
一個索引列的可壓縮性不是自動更新的。就是說，用戶在某列創建瞭一個索引，而該列的可壓縮性是不會立即計算出來的。這時需要使用ANALYZE TABLE語句來更新它。
語法格式：ANALYZE [LOCAL |NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
在MySQL上執行的所有更新都將寫入到一個二進制日志文件中。這裡如果直接使用ANALYZE TABLE語句，結果數據也會寫入日志文件中。如果指定瞭NO_eriteTO_binlog選項，則關閉這個功能（LOCAL是NO_ERITE_TO_BINLOG的同義詞），這樣ANALYZETABLE語句也將會更快完成。
例： 更新表XS的索引的可壓縮性，並隨後顯示。
www.aiwalls.com
ANALYZE TABLE XS;
SHOW INDEX FROM XS;
2. check table 語句
這條語句用來檢查一個或多個表是否有錯誤，隻對MyISAM和InnoDB表起作用。 語法格式：
CHECK TABLE tbl_name [, tbl_name] …[option] …
其中，option為：
QUICK | FAST | MEDIUM | EXTENDED |CHANGED
說明：
使用該語句有多個選項。
● quick：不掃描行，不檢查錯誤的鏈接，這是最快的方法。
● fast：檢查表是否已經正確關閉。
● changed：檢查上次檢查後被更改的表，以及沒有被正確關閉的表。
● medium：掃描行，以驗證被刪除的鏈接是有效的。也可以計算各行的關鍵字校驗和，並使用計算出的校驗和驗證這一點。
● extended：對每行的所有關鍵字進行全面的關鍵字查找。這可以確保表是100％一致的，但是花的時間較長。
例： 檢查XS表是否正確。
CHECK TABLE XS;
3. checksum table 語句
對於數據庫中的每一個表，都可以使用CHECKSUM TABLE語句獲得一個校驗和。 語法格式：
CHECKSUM TABLE tbl_name [, tbl_name]… [ QUICK | EXTENDED ]
說明：如果表是MyISAM表，如果指定瞭QUICK，則報告表校驗和，否則報告NULL。指定EXTENDED則表示無論表是否是MyISAM表，都隻計算檢驗和。
www.aiwalls.com
4. optimize table 語句
如果用戶不斷地使用DELETE、INSERT和UPDATE語句更新一個表，那麼表的內部結構就會出現很多碎片和未利用的空間。這時可以使用OPTIMIZE TABLE語句來重新利用未使用的空間，並整理數據文件的碎片。OPTIMIZE TABLE語句隻對MyISAM、BDB和InnoDB表起作用。
語法格式： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name] …
例： 優化XS表。
5. repair table 語句
如果一個表或索引已經損壞，可以使用REPAIR TABLE語句嘗試修復它。REPAIR TABLE隻對MyISAM和ARCHIVE表起作用。
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_name [, tbl_name]… [QUICK] [EXTENDED] [USE_FRM]
說明：
REPAIR TABLE語句支持以下選項：
● QUICK：如果指定瞭該選項，則REPAIR TABLE會嘗試隻修復索引樹。
● EXTENDED：使用該選項，則MySQL會一行一行地創建索引行，代替使用分類一次創建一個索引。
● USE_FRM：如果MYI索引文件缺失或標題被破壞，則必須使用此選項。
另外，還有兩個表維護語句：backup TABLE和restore TABLE語句。
使用BACKUP TABLE語句可以對一個或多個MyISAM表備份。
www.aiwalls.com
語法格式為：
BACKUP TABLE tbl_name [, tbl_name] … TO'/path/to/backup/directory'
使用RESTORE TABLE語句可以獲取BACKUPTABLE創建的一個或多個表的備份，將數據讀取到數據庫中。
語法格式為：
RESTORE TABLE tbl_name [, tbl_name] … FROM'/path/to/backup/directory'
但是這兩條語句不是很理想，已經不推薦使用瞭，這裡隻是大概瞭解一下。OPTIMIZE TABLEKC;
作者 tianyazaiheruan