mysql建立使用者以及授權

標籤：進入   許可權   user   sel   ted   ret   授權   show   ip）   

預設使用者有root超級管理員，要做一個網站，要串連mysql要一個使用者名稱和密碼，不可能是root，防止誤操作。Mysql服務裡面可以跑多個庫，所以需要給單獨的使用者作一些授權，只需要他對某一個資料庫或者某個資料庫的某個表有許可權。

grant all on . to ‘user1‘ identified by ‘passwd‘; // grant是授權的意思 all全部的
1.mysql> grant all on . to ‘user1‘@‘127.0.0.1‘ identified by ‘123‘; //授權user1隻能通過127這個ip登入mysql（源ip） identified by密碼 .前面這個表示庫名，後面是表。Ip也可以使用%表示所有的ip,
Query OK, 0 rows affected (0.63 sec)
2.[[email protected] ~]# mysql -uuser1 -p123 -h127.0.0.1 //使用者登入。如果授權ip是localhost那麼可以不用-h

3.mysql> grant all on db1.* to ‘user1‘@‘192.168.222.%‘ identified by ‘1‘;
4.[[email protected] ~]# mysql -uuser1 -p1 -h192.168.222.51

5.grant SELECT,UPDATE,INSERT on db1. to ‘user2‘@‘192.168.133.1‘ identified by ‘passwd‘;
6.grant all on db1. to ‘user3‘@‘%‘ identified by ‘passwd‘;

7.show grants;//查看目前使用者的授權
mysql> show grants; //查看許可權必須進入要查詢的使用者裡面
+-------------------------------------------------------------------------------+
| Grants for [email protected]% |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘user1‘@‘192.168.222.%‘ IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON db1.* TO ‘user1‘@‘192.168.222.%‘ |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user();
+----------------------+
| user() |
+----------------------+
| [email protected] |
+----------------------+
1 row in set (0.00 sec)

8.show grants for [email protected]; //查看指定使用者的授權
mysql> select user();
+----------------+
| user() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

mysql> grant SELECT,UPDATE,INSERT on db1.* to ‘user3‘@‘192.168.222.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for [email protected]‘192.168.222.%‘;
+------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘user3‘@‘192.168.222.%‘ IDENTIFIED BY PASSWORD ‘6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
| GRANT SELECT, INSERT, UPDATE ON db1. TO ‘user3‘@‘192.168.222.%‘ |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql建立使用者以及授權