MySQL 資料庫使用者和許可權管理

標籤：mysq   要求   password   許可權   AC   控制   inf   資料庫   tca   

MySQL 資料庫使用者和許可權管理技能目標

• 掌握MySQL 使用者管理
• 添加系統管理使用者
• 修改密碼及忘記密碼修改

使用者授權資料庫是資訊系統中非常重要的環節，合理高效的對它進行管理是很重要的工作。通常是由擁有最高許可權的管理員建立不同的管理賬戶，然後分配不同的操作許可權，把這些賬戶交給相應的管理員使用使用者管理1: 建立使用者建立使用者的命令格式如下CREATE USER ‘username‘@‘host‘ [IDENTIFIED BY [PASSWORD]‘password‘] #大寫是固定格式大括弧是一個整體再寫命令的時候沒有

• username 將建立的使用者名稱
• host 指定使用者允許那些主機終端可以登入，可以是IP地址、網段、指定本機使用者localhost、如果讓該使用者可以從任意遠程主機登入可以用萬用字元%
• password 設定登入的密碼

下面是MySQL安裝之後建立的使用者密碼,在資料庫中顯示的密碼是以密文的形式儲存的大大的增強了安全性

mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |+-----------+-------------------------------------------+-----------+2 rows in set (0.01 sec)

建立新使用者

mysql> create user ‘accp‘@‘localhost‘ identified by ‘123123‘;Query OK, 0 rows affected (0.01 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

刪除使用者命令格式如下DROP USER ‘username‘@‘host‘

mysql> drop user ‘accp‘@‘localhost‘; #刪除accpQuery OK, 0 rows affected (0.00 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || bent      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

使用者重新命名，格式如下RENAME USER ‘old_user‘@‘host‘ TO ‘new_user‘ @ ‘host‘

mysql> select User,authentication_string,Host from user;  #這邊我們把bent重新命名為accp+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || bent      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)mysql> rename user ‘bent‘@‘localhost‘ to ‘accp‘@‘localhost‘ ;Query OK, 0 rows affected (0.00 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

給使用者佈建密碼1：給目前使用者設定密碼SET PASSWORD=PASSWORD(‘password‘)

mysql> select User,authentication_string,Host from user; +-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)mysql> set password=password(‘123123‘); #目前使用者是root我把root使用者密碼改為了"123123"與上面的root密碼對比一下秘聞的區別Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

2：使用超級管理員root修改其他使用者密碼，格式如下SET PASSWORD FOR ‘username‘@‘host‘=PASSWORD(‘password‘);

mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *437F1809645E0A92DAB553503D2FE21DB91270FD | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)mysql> set password for ‘accp‘@‘localhost‘=password(‘951116‘); #同樣對比一下密文密碼的區別Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)

忘記root密碼解決方案

[[email protected] ~] systemctl stop mysqld.service  #關閉服務[[email protected] ~] netstat -ntap | grep 3306 #查看連接埠有沒有關閉[[email protected] ~] mysql --skip-grant-tables #會出現以下代碼不要去動它重新開一個終端2018-06-28T02:16:16.399381Z 0 [Note]   - ‘::‘ resolves to ‘::‘;2018-06-28T02:16:16.399402Z 0 [Note] Server socket created on IP: ‘::‘.2018-06-28T02:16:16.400217Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool2018-06-28T02:16:16.401959Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180628 10:16:162018-06-28T02:16:16.410638Z 0 [Note] Executing ‘SELECT * FROM INFORMATION_SCHEMA.TABLES;‘ to get a list of tables using the deprecated partition engine. You may use the startup option ‘--disable-partition-engine-check‘ to skip this check. 2018-06-28T02:16:16.410661Z 0 [Note] Beginning of list of non-natively partitioned tables2018-06-28T02:16:16.423678Z 0 [Note] End of list of non-natively partitioned tables2018-06-28T02:16:16.423748Z 0 [Note] mysqld: ready for connections.Version: ‘5.7.17‘  socket: ‘/usr/local/mysql/mysql.sock‘  port: 3306  Source distribution

[[email protected] ~] mysql -u root #直接這樣登入跳過密碼選項Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> 

登入進去後改使用者密碼

mysql> update mysql.user set authentication_string=password(‘123123‘)where user=‘root‘; #修改root密碼Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges; #重新整理資料庫Query OK, 0 rows affected (0.01 sec) [[email protected] ~]# mysql -u root -p123123mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

授權控制再MySQL中，使用權限設定非常重要，分配許可權可以清晰的劃分責任。管理員只需要關注完成自己的任務即可，最重要的是保證系統資料的安全1：授予許可權（1）:許可權控制主要出於安全因素，需要遵循以下原則1）：只授予能滿足需要的最小許可權，防止誤操作和做壞事2）：建立使用者的時候限制使用者的登入主機，一般限制指定IP或者內網IP網段3）：初始化資料庫時刪除沒有密碼的使用者，MySQL安裝完成是會自動建立沒有密碼的使用者4）：為每個使用者佈建滿足要求的密碼5）：定期清理不需要的使用者（2）：授予許可權使用GRANT命令，命令格式如下GRANT 許可權列表 ON 庫名.表明 TO 使用者@主機地址[IDENTIFIED BY‘密碼‘]命令個是很明確，是指定使用者允許它操作某些表，對這些表擁有相應的操作許可權下面示範GRANT的使用方法

mysql> grant select on ×××表.×××資訊 to ‘accp‘@‘localhost‘ identified by ‘123123‘; Query OK, 0 rows affected, 1 warning (0.00 sec)

上面命令的意思是使使用者accp可以在主機localhost登入，串連密碼是123123，它擁有對資料庫（×××表.×××資訊）的select許可權登入accp使用者驗證以下

[[email protected] ~]# mysql -u accp -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> insert into imployee_英航客戶表.×××資訊 values (2,‘張三‘,‘廣州珠海‘,‘18888888‘);ERROR 1142 (42000): INSERT command denied to user ‘accp‘@‘localhost‘ for table ‘×××資訊‘

顯示select語句可以正常使用，但執行insert語句是沒有足夠許可權噹噹使用者和主機名稱在列表中不存在時，使用者和主機名稱會被自動建立，如果限制使用者密碼與原用密碼不同時會自動覆蓋原密碼

mysql> select User,authentication_string,Host from user; +-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || accp      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |+-----------+-------------------------------------------+-----------+3 rows in set (0.00 sec)#使用者列表中只有三個使用者此時，做一個使用者列表中不存在使用者權限mysql> grant select on ×××表.×××資訊 to ‘benet‘@‘localhost‘ identified by ‘1223123‘;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select User,authentication_string,Host from user;+-----------+-------------------------------------------+-----------+| User      | authentication_string                     | Host      |+-----------+-------------------------------------------+-----------+| root      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || benet     | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost || accp      | *0DB339632B48910F8F0BEF61BD7EAD4441267E6E | localhost |+-----------+-------------------------------------------+-----------+4 rows in set (0.00 sec)#上面自動建立了benet使用者登陸密碼為‘123123’

下面設定benet使用者限制原密碼為123123，我把限制密碼改以新密碼‘321321’然後看一下用原密碼能不能登入

mysql> grant insert on ×××表.×××資訊 to ‘benet‘@‘localhost‘ identified by ‘3221321‘;Query OK, 0 rows affected, 1 warning (0.00 sec)[[email protected] ~]# mysql -u benet -p123123mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user ‘benet‘@‘localhost‘ (using password: YES)#提示你輸入正確的登陸密碼

查看使用者權限SHOW GRANTS FOR ‘username‘@‘主機地址‘

mysql> show grants for ‘accp‘@‘localhost‘;+------------------------------------------------------------------------------+| Grants for [email protected]                                                    |+------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘accp‘@‘localhost‘                                     || GRANT SELECT ON "×××表"."×××資訊" TO ‘accp‘@‘localhost‘            |+------------------------------------------------------------------------------+2 rows in set (0.00 sec)

撤銷使用者權限REVOKE 許可權列表 ON 資料庫名.表名 FROM 使用者@主機地址

mysql> revoke select on ×××表.×××資訊 from ‘accp‘@‘localhost‘;Query OK, 0 rows affected (0.00 sec)mysql> show grants for ‘accp‘@‘localhost‘;+------------------------------------------+| Grants for [email protected]                |+------------------------------------------+| GRANT USAGE ON *.* TO ‘accp‘@‘localhost‘ |+------------------------------------------+1 row in set (0.00 sec)

撤銷使用者所有許可權REVOKE ALL ON 資料庫名.表名 FROM 使用者@主機地址

MySQL 資料庫使用者和許可權管理