摘要:實(shí)例字段表示賬號(hào)名,表示當(dāng)前數(shù)據(jù)庫有和兩個(gè)賬號(hào)。實(shí)例為數(shù)據(jù)庫添加賬號(hào)密碼為使用的賬號(hào)和密碼登錄,成功登錄。實(shí)例刪除賬號(hào)二權(quán)限管理訪問控制服務(wù)器的安全基礎(chǔ)是用戶應(yīng)該對他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問權(quán),既不能多也不能少。
MySQL基礎(chǔ)知識(shí)點(diǎn)整理 - 賬號(hào)和權(quán)限管理 一、賬號(hào)管理 1. 查看賬號(hào)列表
MySQL用戶賬號(hào)和信息存儲(chǔ)在名為 mysql 的數(shù)據(jù)庫中。一般不需要直接訪問 mysql 數(shù)據(jù)庫和表,但有時(shí)需要直接訪問。例如,查看數(shù)據(jù)庫所有用戶賬號(hào)列表時(shí)。
語法USE mysql; SELECT DISTINCT(`user`) FROM user;
數(shù)據(jù)庫 mysql 有一個(gè)名為 user 的表,它包含所有用戶賬號(hào)。 user 表有一個(gè)名為 user 的字段,它存儲(chǔ)賬號(hào)名。
進(jìn)入數(shù)據(jù)庫 mysql,查看 user 表中的 user 列,由于有些賬號(hào)會(huì)分多行記錄,DISTINCT 用于去重。
實(shí)例mysql> USE mysql; Database changed mysql> SELECT DISTINCT(`user`) FROM user; +-----------+ | user | +-----------+ | root | | mysql.sys | +-----------+ 2 rows in set (0.07 sec)
user字段 表示賬號(hào)名,表示當(dāng)前數(shù)據(jù)庫有 root 和 mysql.sys 兩個(gè)賬號(hào)。
2. 創(chuàng)建賬號(hào)可以使用 CREATE USER 語句創(chuàng)建一個(gè)新用戶賬號(hào)。
語法CREATE USER account_name IDENTIFIED BY "password";
IDENTIFIED BY 用于設(shè)定密碼,MySQL 會(huì)先將密碼進(jìn)行加密,在將其保存到 user 表。
使用 GRANT 或 INSERT GRANT 語句也可以創(chuàng)建用戶賬號(hào),但一般來說 CREATE USER 是最清楚和最簡單的句子。實(shí)例
使用 CREATE USER 創(chuàng)建用戶賬號(hào),必須接著分配訪問權(quán)限。新創(chuàng)建的用戶賬號(hào)沒有訪問權(quán)限。它們能登錄MySQL,但不能看到數(shù)據(jù),不能執(zhí)行任何數(shù)據(jù)庫操作。
可以使用 GRANT 語句創(chuàng)建用戶賬號(hào)并授權(quán),該語句會(huì)在文章授權(quán)部分講解。用于賬號(hào)都存儲(chǔ)在數(shù)據(jù)庫 mysql 的 user 表中,理論上也可以通過直接插入行到 user 表來增加用戶,不過為安全起見,一般不建議這樣做。MySQL用來存儲(chǔ)用戶賬號(hào)信息的表(以及表模式等)極為重要,對它們的任何毀壞都可能嚴(yán)重地傷害到MySQL服務(wù)器。因此,最好不要直接修改數(shù)據(jù)庫 mysql 中表的數(shù)據(jù)。
為數(shù)據(jù)庫添加賬號(hào) zhangsan 密碼為 123456
mysql> CREATE USER zhangsan IDENTIFIED BY "123456"; Query OK, 0 rows affected (0.06 sec) mysql> SELECT DISTINCT(`user`) FROM user; +-----------+ | user | +-----------+ | root | | zhangsan | | mysql.sys | +-----------+ 3 rows in set (0.07 sec)
使用 zhangsan 的賬號(hào)和密碼登錄,成功登錄 MySQL。
[vagrant~] ]$mysql -uzhangsan -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. ...... Type "help;" or "h" for help. Type "c" to clear the current input statement. mysql>
注意,不要直接在命令行中輸入密碼,因?yàn)槊钚械妮斎霘v史都會(huì)被記錄下來,很同意導(dǎo)致密碼泄露。3. 賬號(hào)重命名
可以使用 RENAME USER 語句為賬號(hào)重命名。
語法RENAME USER old_name TO new_name;
僅 MySQL 5及之后的版本支持 RENAME USER。實(shí)例
MySQL 5以前的版本,要重命名一個(gè)用戶,可使用 UPDATE 直接更新 user 表(謹(jǐn)慎操作)。
將數(shù)據(jù)庫賬號(hào) zhangsan 重命名為 lisi
mysql> RENAME USER zhangsan TO lisi; Query OK, 0 rows affected (0.34 sec) mysql> SELECT DISTINCT(`user`) FROM user; +-----------+ | user | +-----------+ | lisi | | root | | mysql.sys | +-----------+ 3 rows in set (0.08 sec)4. 重置賬號(hào)密碼
可以使用 SET PASSWORD 語句重置賬號(hào)密碼。
語法SET PASSWORD FOR account_name = Password("password");
使用 SET PASSWORD 重置賬號(hào)密碼。新密碼必須通過 Password() 函數(shù)進(jìn)行加密。
當(dāng)不指定用戶名時(shí), SET PASSWORD 會(huì)重置當(dāng)前登錄用戶的密碼。
語法SET PASSWORD = Password("password");實(shí)例
將賬號(hào) lisi 的密碼改為 abcdef
mysql> SET PASSWORD FOR lisi = Password("abcdef"); Query OK, 0 rows affected (0.03 sec)
使用 lisi 的賬號(hào)和新密碼登錄,成功登錄 MySQL。
[vagrant~] ]$mysql -ulisi -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. ...... Type "help;" or "h" for help. Type "c" to clear the current input statement. mysql>
將當(dāng)前登錄賬號(hào)的密碼重置為 10086
mysql> SET PASSWORD = Password("10086"); Query OK, 0 rows affected (0.00 sec)5. 刪除賬號(hào)
可以使用 DROP USER 語句刪除賬號(hào)(以及相關(guān)的權(quán)限)。
語法DROP USER account_name;
MySQL 5及之后的版本,DROP USER 刪除用戶賬號(hào)時(shí)會(huì)自動(dòng)刪除所有相關(guān)的賬號(hào)權(quán)限。實(shí)例
在MySQL 5以前, DROP USER 只能用來刪除用戶賬號(hào),不能刪除相關(guān)的權(quán)限。因此,如果使用舊版本的MySQL,需要先用 REVOKE 刪除與賬號(hào)相關(guān)的權(quán)限,然后再用 DROP USER 刪除賬號(hào)。
刪除賬號(hào) lisi
mysql> DROP USER lisi; Query OK, 0 rows affected (0.00 sec) mysql> SELECT DISTINCT(`user`) FROM user; +-----------+ | user | +-----------+ | root | | mysql.sys | +-----------+ 2 rows in set (0.07 sec)二、權(quán)限管理 0. 訪問控制
MySQL服務(wù)器的安全基礎(chǔ)是:用戶應(yīng)該對他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問權(quán),既不能多也不能少。
考慮以下情況:
多數(shù)用戶只需要對表進(jìn)行讀和寫,但少數(shù)用戶甚至需要能創(chuàng)建和刪除表;
某些用戶需要讀表,但可能不需要更新表;
你可能想允許用戶添加數(shù)據(jù),但不允許他們刪除數(shù)據(jù);
某些用戶(管理員)可能需要處理用戶賬號(hào)的權(quán)限,但多數(shù)用戶不需要;
你可能想讓用戶通過存儲(chǔ)過程訪問數(shù)據(jù),但不允許他們直接訪問數(shù)據(jù);
你可能想根據(jù)用戶登錄的地點(diǎn)限制對某些功能的訪問。
這些都只是例子,但有助于說明一個(gè)重要的事實(shí),即你需要給用戶提供他們所需的訪問權(quán),且僅提供他們所需的訪問權(quán)。這就是所謂的訪問控制,管理訪問控制需要?jiǎng)?chuàng)建和管理用戶賬號(hào)。
嚴(yán)肅對待 root 賬號(hào)的使用MySQL 會(huì)默認(rèn)創(chuàng)建一個(gè)名為 root 的用戶賬號(hào),它對整個(gè) MySQL 服務(wù)器具有完全的控制。不過在日常的 MySQL 操作中(特別是生產(chǎn)環(huán)境),決不能使用 root 賬號(hào)登錄。應(yīng)該創(chuàng)建一系列的賬號(hào),有的用于管理,有的供用戶使用,有的供開發(fā)人員使用,等等。應(yīng)該嚴(yán)肅對待 root 賬號(hào)的使用,僅在絕對需要時(shí)使用它。
訪問控制的目的防止用戶的惡意企圖。
防止用戶無意識(shí)錯(cuò)誤造成數(shù)據(jù)錯(cuò)亂。這是更為常見的情況。如錯(cuò)打 SQL 語句,在不合適的數(shù)據(jù)庫中操作或其他一些用戶錯(cuò)誤。
通過保證用戶不能執(zhí)行他們不應(yīng)該執(zhí)行的語句,訪問控制有助于避免這些情況的發(fā)生。
1. 查看賬號(hào)權(quán)限 語法SHOW GRANTS[ FOR account_name][@host];
當(dāng)不使用 FOR 指定用戶時(shí),默認(rèn)是查看自己的賬號(hào)權(quán)限。
當(dāng)使用 SHOW GRANTS FOR account_name@host 時(shí),可查看指定賬號(hào)在指定主機(jī)下的權(quán)限。可以在數(shù)據(jù)庫 mysql 中使用 SELECT user,host FROM user; 查看賬號(hào)的主機(jī)列表。
mysql> USE mysql; Database changed mysql> SELECT user,host FROM user; +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 3 rows in set (0.06 sec)
host字段: 表示賬號(hào)可以在哪些主機(jī)或IP地址登錄。% 代表任何IP地址都可以登錄(生產(chǎn)環(huán)境中這樣做是非常危險(xiǎn)的),localhost 表示只允許本機(jī)登錄。
將 host 設(shè)為 %,就代表任何IP地址都可以訪問該數(shù)據(jù)庫,在生產(chǎn)環(huán)境中這樣做是非常危險(xiǎn)的。實(shí)例
也可以使用其他手段來提高數(shù)據(jù)庫安全性:比如設(shè)置防火墻、iptable;如果是云平臺(tái)的數(shù)據(jù)庫還可以通過安全組等方式提高安全性。
查看自己的賬號(hào)(root)權(quán)限。
mysql> SHOW GRANTS; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO "root"@"%" WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
查看賬號(hào) root 在 localhost 下的權(quán)限。
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO "root"@"localhost" WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
輸出結(jié)果顯示賬號(hào) root 有一個(gè)權(quán)限 ALL PRIVILEGES ON *.*,表示 root 賬號(hào)可以操作所有數(shù)據(jù)庫和所有表。
使用 CREATE USER 創(chuàng)建一個(gè)賬號(hào) zhangsan,并查看 zhangsan 的賬號(hào)權(quán)限。
mysql> CREATE USER zhangsan IDENTIFIED BY "123456"; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR zhangsan; +---------------------------------------------------------------------------------------------------------+ | Grants for zhangsan@% | +---------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO "zhangsan"@"%" IDENTIFIED BY PASSWORD "*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9" | +---------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
輸出結(jié)果顯示賬號(hào) zhangsan 有一個(gè)權(quán)限 USAGE ON *.* 。 USAGE 表示根本沒有權(quán)限,所以,此結(jié)果表示 zhangsan 對任意數(shù)據(jù)庫和任意表上對任何東西都沒有操作權(quán)限。
登錄賬號(hào) zhangsan,并嘗試進(jìn)入 test 數(shù)據(jù)庫,被拒絕。
[vagrant~] ]$mysql -uzhangsan -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. ...... Type "help;" or "h" for help. Type "c" to clear the current input statement. mysql> use test; ERROR 1044 (42000): Access denied for user "zhangsan"@"%" to database "test"
使用 CREATE USER 創(chuàng)建的用戶賬號(hào)默認(rèn)沒有訪問權(quán)限。它們能登錄MySQL,但不能看到數(shù)據(jù),不能執(zhí)行任何數(shù)據(jù)庫操作。2. 為已存在的賬號(hào)設(shè)置權(quán)限
可以使用 GRANT 語句為賬號(hào)設(shè)置權(quán)限。至少給出以下信息:
賬號(hào)名。
被授予訪問權(quán)限的數(shù)據(jù)庫或表。
要授予的權(quán)限。
語法GRANT <權(quán)限> ON <數(shù)據(jù)庫名>.<表名> TO <賬戶名>;實(shí)例
給賬號(hào) zhangsan 賦予在 test 數(shù)據(jù)庫內(nèi)的任意表查找和添加數(shù)據(jù)的權(quán)限。
mysql> GRANT SELECT, INSERT ON test.* TO "zhangsan"; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR zhangsan; +---------------------------------------------------------------------------------------------------------+ | Grants for zhangsan@% | +---------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO "zhangsan"@"%" IDENTIFIED BY PASSWORD "*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9" | | GRANT SELECT, INSERT ON `test`.* TO "zhangsan"@"%" | +---------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
授權(quán)后必須FLUSH PRIVILEGES,否則無法立即生效。
登錄賬號(hào) zhangsan,可以成功進(jìn)入到數(shù)據(jù)庫 test,在 user 表中插入一條數(shù)據(jù),并從 user 表查找數(shù)據(jù)。
mysql> USE test; Database changed mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.00 sec) mysql> INSERT INTO user (username, email) VALUES ("zhangsan", "[email protected]"); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT * FROM user; +----+----------+--------------------+----------+--------+------------+ | id | username | email | password | status | created_at | +----+----------+--------------------+----------+--------+------------+ | 1 | zhangsan | [email protected] | NULL | 0 | 0 | +----+----------+--------------------+----------+--------+------------+ 1 row in set (0.00 sec)
當(dāng) zhangsan 想要使用 UPDATE 和 DELETE 命令修改和刪除這條數(shù)據(jù)時(shí),被提示沒有權(quán)限。
mysql> UPDATE user SET email="[email protected]" WHERE username="zhangsan"; ERROR 1142 (42000): UPDATE command denied to user "zhangsan"@"localhost" for table "user" mysql> DELETE FROM user WHERE username="zhangsan"; ERROR 1142 (42000): DELETE command denied to user "zhangsan"@"localhost" for table "user"3. 創(chuàng)建賬號(hào)并設(shè)置權(quán)限 語法
GRANT <權(quán)限> ON <數(shù)據(jù)庫名>.<表名> TO <賬戶名>@<主機(jī)名/IP> IDENTIFIED BY "<密碼>"[ WITH GRANT OPTION];
WITH GRANT OPTION 用于賦予賬號(hào)使用 GRANT 和 REVOKE 命令的權(quán)限,用于給賬號(hào)授權(quán)和取消授權(quán)。此權(quán)限級(jí)別極高,一般只會(huì)將此權(quán)限授予數(shù)據(jù)庫管理員賬號(hào)。實(shí)例
創(chuàng)建賬號(hào) lisi 密碼為 abcdef,在任何IP地址都可以登錄,同時(shí)賦予 lisi 在 test 數(shù)據(jù)庫內(nèi)的任意表數(shù)據(jù)的增刪改查權(quán)限。
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO "lisi"@"%" IDENTIFIED BY "abcdef"; Query OK, 0 rows affected (0.00 sec)
將 host 設(shè)為 %,就代表任何IP地址都可以訪問該數(shù)據(jù)庫,在生產(chǎn)環(huán)境中這樣做是非常危險(xiǎn)的。
也可以使用其他手段來提高數(shù)據(jù)庫安全性:比如設(shè)置防火墻、iptable;如果是云平臺(tái)的數(shù)據(jù)庫還可以通過安全組等方式提高安全性。
登錄賬號(hào) lisi,可以成功進(jìn)入到數(shù)據(jù)庫 test,并對 user 表數(shù)據(jù)進(jìn)行增刪改查。
[vagrant~] ]$mysql -ulisi -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. ...... Type "help;" or "h" for help. Type "c" to clear the current input statement. mysql> use test; Database changed mysql> INSERT INTO user (username, email) VALUES ("lisi", "[email protected]"); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT * FROM user; +----+----------+--------------------+----------+--------+------------+ | id | username | email | password | status | created_at | +----+----------+--------------------+----------+--------+------------+ | 1 | zhangsan | [email protected] | NULL | 0 | 0 | | 2 | lisi | [email protected] | NULL | 0 | 0 | +----+----------+--------------------+----------+--------+------------+ 2 rows in set (0.00 sec) mysql> UPDATE user SET email="[email protected]" WHERE username="lisi"; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> DELETE FROM user WHERE username="zhangsan"; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM user; +----+----------+---------------------+----------+--------+------------+ | id | username | email | password | status | created_at | +----+----------+---------------------+----------+--------+------------+ | 2 | lisi | [email protected] | NULL | 0 | 0 | +----+----------+---------------------+----------+--------+------------+ 1 row in set (0.00 sec)4. 撤銷賬號(hào)指定權(quán)限
可以使用 REVOKE 語句撤銷賬號(hào)指定權(quán)限。REVOKE 是 GRANT 的反操作。
語法REVOKE <權(quán)限> ON <數(shù)據(jù)庫名>.<表名> FROM <賬戶名>;實(shí)例
刪除賬號(hào) lisi 的 DELETE 權(quán)限。
mysql> REVOKE DELETE ON test.* FROM lisi; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR lisi; +-----------------------------------------------------------------------------------------------------+ | Grants for lisi@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO "lisi"@"%" IDENTIFIED BY PASSWORD "*C2D24DCA38E9E862098B85BF0AB35CAA52803797" | | GRANT SELECT, INSERT, UPDATE ON `test`.* TO "lisi"@"%" | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.04 sec)
當(dāng) lisi 想要使用 DELETE 命令刪除數(shù)據(jù)時(shí),被提示沒有權(quán)限。
mysql> DELETE FROM user WHERE username="lisi"; ERROR 1142 (42000): DELETE command denied to user "lisi"@"localhost" for table "user"5. 權(quán)限說明
GRANT 和 REVOKE 可在幾個(gè)層次上控制訪問權(quán)限:
整個(gè)服務(wù)器,使用 GRANT ALL 和 REVOKE ALL;
整個(gè)數(shù)據(jù)庫,使用 ON database.*;
特定的表,使用 ON database.table;
特定的列;
特定的存儲(chǔ)過程。
權(quán) 限 | 說 明 |
---|---|
ALL | 除 GRANT OPTION 外的所有權(quán)限 |
ALTER | 使用 ALTER TABLE |
ALTER ROUTINE | 使用 ALTER PROCEDURE 和 DROP PROCEDURE |
CREATE | 使用 CREATE TABLE |
CREATE ROUTINE | 使用 CREATE PROCEDURE |
CREATE TEMPORARY TABLES | 使用 CREATE TEMPORARY TABLE |
CREATE USER | 使用 CREATE USER、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES |
CREATE VIEW | 使用 CREATE VIEW |
DELETE | 使用 DELETE |
DROP | 使用 DROP TABLE |
EXECUTE | 使用 CALL 和存儲(chǔ)過程 |
FILE | 使用 SELECT INTO OUTFILE 和 LOAD DATA INFILE |
GRANT OPTION | 使用 GRANT 和 REVOKE |
INDEX | 使用 CREATE INDEX 和 DROP INDEX |
INSERT | 使用 INSERT |
LOCK TABLES | 使用 LOCK TABLES |
PROCESS | 使用 SHOW FULL PROCESSLIST |
RELOAD | 使用 FLUSH |
REPLICATION CLIENT | 服務(wù)器位置的訪問 |
REPLICATION SLAVE | 由復(fù)制從屬使用 |
SELECT | 使用 SELECT |
SHOW DATABASES | 使用 SHOW DATABASES |
SHOW VIEW | 使用 SHOW CREATE VIEW |
SHUTDOWN | 使用 mysqladmin shutdown(用來關(guān)閉MySQL) |
SUPER | 使用 CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和 SET GLOBAL。還允許 mysqladmin 調(diào)試登錄 |
UPDATE | 使用 UPDATE |
USAGE | 無訪問權(quán)限 |
最后附一張《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》中的權(quán)限控制流程圖。
以 SELECT id,name FROM test.t4 where status = "deleted"; 為例。
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://systransis.cn/yun/31835.html
摘要:基礎(chǔ)知識(shí)點(diǎn)整理數(shù)據(jù)表管理數(shù)據(jù)類型數(shù)值數(shù)據(jù)類型數(shù)值數(shù)據(jù)類型存儲(chǔ)數(shù)值。支持多種數(shù)值數(shù)據(jù)類型,每種存儲(chǔ)的數(shù)值具有不同的取值范圍。是定長字符串,會(huì)直接根據(jù)定義字符串時(shí)指定的長度分配足夠的空間。 MySQL基礎(chǔ)知識(shí)點(diǎn)整理 - 數(shù)據(jù)表管理 〇、數(shù)據(jù)類型 1. 數(shù)值數(shù)據(jù)類型 數(shù)值數(shù)據(jù)類型存儲(chǔ)數(shù)值。MySQL支持多種數(shù)值數(shù)據(jù)類型,每種存儲(chǔ)的數(shù)值具有不同的取值范圍。 整數(shù) 類型 大小 范圍(有符號(hào))...
閱讀 997·2021-11-23 09:51
閱讀 3489·2021-11-22 12:04
閱讀 2732·2021-11-11 16:55
閱讀 2964·2019-08-30 15:55
閱讀 3243·2019-08-29 14:22
閱讀 3365·2019-08-28 18:06
閱讀 1254·2019-08-26 18:36
閱讀 2140·2019-08-26 12:08