mysql使用者建立、修改、刪除及授權操作的總結
總結
1、mysql命令進行連線如果不帶-h,表示user@localhost連線本地localhost資料庫伺服器,-h後面的ip是資料庫伺服器的IP
2、要讓所有ip都能連,create user時使用者名稱後面加@'%',或不加@(不加@預設就是@'%')
3、mysql執行grant進行授權,許可權是追加的,不用擔心會覆蓋之前的
4、 grant可以同時建立使用者、授權、修改密碼
5、建立使用者建議使用create,因為 create只能建立使用者,不能授權 , mysql沒有類似oracle這樣的connect create session許可權,mysql只要create了使用者就自動擁有了connect create session許可權
6、修改密碼建議使用alter user比較好,因為alter不會涉及到許可權,grant雖然可以修改密碼,但是grant命令要帶許可權選項。
7、如果使用者名稱相同,host對應的網段有相同,哪個使用者先生成,就以哪個使用者為準
即以下兩條,誰先執行,誰先生效,就是說mysql -u test1 -p後面使用誰的密碼才能登陸
grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
8、grant執行的授權,不需要flush privileges就可以生效;update mysql.user表執行的授權,必須要flush privileges才能生效
建立使用者
mysql> help create user;
mysql> help grant;
mysql> create user 'user1'@'192.168.10.0/255.255.255.0' identified by '123456';
mysql> grant select,update on db1.* to 'user2'@'192.168.10.0/255.255.255.0' identified by '123456';--建立使用者的同時進行了授權
修改密碼
mysql> help alter user;
mysql> help grant;
mysql> help set password;
mysql> alter user 'user1'@'192.168.10.0/255.255.255.0' identified by '666666';
mysql> grant select on mysql.user to 'user1'@'192.168.10.0/255.255.255.0' identified by '777777';--修改密碼的同時進行了授權
mysql> set password for 'user1'@'192.168.10.0/255.255.255.0'=password('888888');
SET PASSWORD is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. ALTER USER is now the preferred statement for assigning passwords.
從MySQL 5.7.6開始,不推薦使用SET PASSWORD,這個用法在未來的MySQL版本中會被拋棄。 ALTER USER現在是首選修改密碼的宣告。
授權
mysql> help grant;
mysql> grant select,insert,update on *.* to 'user1'@'192.168.10.0/255.255.255.0';--只授權,沒有涉及修改密碼
刪除使用者
mysql> help drop user;
mysql> drop user 'user1'@'192.168.10.0/255.255.255.0';
密碼相關的引數
mysql> SHOW VARIABLES LIKE '%password%';
mysql> CREATE USER 'mytest'@'192.168.20.0/255.255.255.0' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> show variables like '%validate_password_policy%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+
1 row in set (0.01 sec)
mysql> set global validate_password_policy=0;
mysql> show variables like '%validate_password_length%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| validate_password_length | 8 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> set global validate_password_length=1;
如下實驗,DB伺服器ip是192.168.10.101,在伺服器本地使用mysql登入不加-h表示使用user@localhost登入,-h後面的必須是DB伺服器ip
允許所有ip登入,則create user時後面的使用者名稱不加@或create user時後面的使用者名稱加@'%'
[root@mydb ~]# ifconfig |grep 'inet addr'
inet addr:192.168.10.101 Bcast:192.168.10.255 Mask:255.255.255.0
inet addr:127.0.0.1 Mask:255.0.0.0
inet addr:192.168.122.1 Bcast:192.168.122.255 Mask:255.255.255.0
mysql> CREATE USER 'mytest01'@'192.168.20.0/255.255.255.0' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'mytest02'@'192.168.10.0/255.255.255.0' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'mytest03'@'*' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
[root@mydb ~]# mysql -u mytest01 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mytest01'@'localhost' (using password: YES)
--無法登入,因為沒有加-h表示使用user@localhost
[root@mydb ~]# mysql -u mytest02 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mytest02'@'localhost' (using password: YES)
--無法登入,因為沒有加-h表示使用user@localhost
[root@mydb ~]# mysql -u mytest03 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mytest03'@'localhost' (using password: YES)
--無法登入,因為沒有加-h表示使用user@localhost
--'mytest03'@'*',使用者名稱後面加@'*'不是表示所有ip可以登入,應該加@'%'
[root@mydb ~]# mysql -u mytest01 -p123456 -h 192.168.10.101
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mytest01'@'192.168.10.101' (using password: YES)
[root@mydb ~]# mysql -u mytest02 -p123456 -h 192.168.10.101
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> exit
[root@mydb ~]# mysql -u mytest03 -p123456 -h 192.168.10.101
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mytest03'@'192.168.10.101' (using password: YES)
[root@mydb ~]# mysql -u mytest02 -p123456 -h 192.168.10.102
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)
--無法登入,-h後面必須是DB伺服器ip
mysql> CREATE USER 'mytest04' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
[root@mydb ~]# mysql -u mytest04 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> exit
[root@mydb ~]# mysql -u mytest04 -p123456 -h 192.168.10.101
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
[root@mydb ~]# mysql -u mytest04 -p123456 -h 192.168.10.102
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)
mysql> create user mytest06@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
[root@mydb ~]# mysql -u mytest06 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
[root@mydb ~]# mysql -u mytest06 -p123456 -h 192.168.10.101
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
[root@mydb ~]# mysql -u mytest06 -p123456 -h 192.168.10.102
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)
如下實驗,使用者存在的情況下,grant後面可以不加密碼,使用者不存在的情況下,grant後面必須加密碼
多次執行grant,許可權都是追加的。
mytest02@'192.168.10.0/255.255.255.0'已經存在的情況下
mysql> grant select on test1.* to mytest02@'192.168.10.0/255.255.255.0';
Query OK, 0 rows affected (0.03 sec)
mytest03@'192.168.10.0/255.255.255.0'還不存在的情況下
1、第一步報錯了,因為沒有這個使用者
2、第二步,建立了使用者密碼並授了test1資料庫的select許可權,見表mysql.user和mysql.db
3、第三步,使用者存在的情況下,追加了全域性許可權並修改了密碼,見mysql.user
4、第四步,使用者存在的情況下,追加了全域性許可權並修改了密碼,見mysql.user
5、第五步,使用者存在的情況下,追加了test1資料庫的select許可權並修改了密碼,見mysql.db
mysql> grant select on test1.* to mytest03@'192.168.10.0/255.255.255.0';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> grant select on test1.* to mytest03@'192.168.10.0/255.255.255.0' identified by "123_Tn_123";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant select,update on *.* to mytest03@'192.168.10.0/255.255.255.0' identified by "123_Tn123";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> grant select,insert on *.* to mytest03@'192.168.10.0/255.255.255.0' identified by "123_Tr99";
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> grant select,update on test1.* to mytest03@'192.168.10.0/255.255.255.0' identified by "123_TR00";
Query OK, 0 rows affected, 1 warning (0.00 sec)
如下實驗:如果使用者名稱相同,host對應的網段有相同,哪個使用者先生成,就以哪個使用者為準
資料庫伺服器的IP是192.168.10.101
客戶端的IP是192.168.10.1
mysql> grant select on test1.* to test123 identified by "123456";
mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
mysql> grant select on test1.* to test123@'192.168.10.1' identified by "123456789";
mysql> grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+----------------------------+---------+-----------------------+
| host | user | password_last_changed |
+----------------------------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:24:08 |
| 192.168.0.0/255.255.0.0 | test123 | 2018-09-26 19:24:30 |
| 192.168.10.1 | test123 | 2018-09-26 19:31:00 |
| 192.168.10.101 | test123 | 2018-09-26 19:31:16 |
+----------------------------+---------+-----------------------+
不管在192.168.10.1還是192.168.10.101上執行mysql命令連線,居然發現只有下面的可以連線,即test123@'192.168.10.0/255.255.255.0'有效
mysql -u test123 -p1234567 -h 192.168.10.101
mysql> drop user test123@'192.168.10.0/255.255.255.0';
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+-------------------------+---------+-----------------------+
| host | user | password_last_changed |
+-------------------------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
| 192.168.0.0/255.255.0.0 | test123 | 2018-09-26 19:24:30 |
| 192.168.10.1 | test123 | 2018-09-26 19:31:00 |
| 192.168.10.101 | test123 | 2018-09-26 19:31:16 |
+-------------------------+---------+-----------------------+
再次測試發現不管在192.168.10.1還是192.168.10.101上執行mysql命令連線,只有下面的可以連線,即test123@'192.168.0.0/255.255.0.0'有效
mysql -u test123 -p12345678 -h 192.168.10.101
mysql> drop user test123@'192.168.0.0/255.255.0.0';
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+----------------+---------+-----------------------+
| host | user | password_last_changed |
+----------------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
| 192.168.10.1 | test123 | 2018-09-26 19:31:00 |
| 192.168.10.101 | test123 | 2018-09-26 19:31:16 |
+----------------+---------+-----------------------+
再次測試
發現在192.168.10.1執行mysql命令連線,只有下面的可以連線,即test123@'192.168.10.1'有效
mysql -u test123 -p123456789 -h 192.168.10.101
發現在192.168.10.101執行mysql命令連線,只有下面的可以連線,即test123@'192.168.10.101'有效
mysql -u test123 -p12345678910 -h 192.168.10.101
mysql> drop user test123@'192.168.10.1';
mysql> drop user test123@'192.168.10.101';
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+------+---------+-----------------------+
| host | user | password_last_changed |
+------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
+------+---------+-----------------------+
再次測試發現不管在192.168.10.1還是192.168.10.101上執行mysql命令連線,下面的可以連線,即test123有效了
mysql -u test123 -p123456 -h 192.168.10.101
mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+----------------------------+---------+-----------------------+
| host | user | password_last_changed |
+----------------------------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
| 192.168.0.0/255.255.0.0 | test123 | 2018-09-26 19:45:45 |
| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:45:58 |
+----------------------------+---------+-----------------------+
再次測試發現不管在192.168.10.1還是192.168.10.101上執行mysql命令連線,下面的可以連線,即test123@'192.168.0.0/255.255.0.0'生效了
mysql -u test123 -p12345678 -h 192.168.10.101
得出結論,以下兩條,誰先執行,誰先生效
grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
mysql> drop user test123@'192.168.0.0/255.255.0.0';
mysql> drop user test123@'192.168.10.0/255.255.255.0';
mysql> grant select on test1.* to test123@'192.168.10.1' identified by "123456789";
mysql> grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";
mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;
+----------------------------+---------+-----------------------+
| host | user | password_last_changed |
+----------------------------+---------+-----------------------+
| % | test123 | 2018-09-26 19:21:23 |
| 192.168.10.1 | test123 | 2018-09-26 19:48:59 |
| 192.168.10.101 | test123 | 2018-09-26 19:49:05 |
| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:49:21 |
| 192.168.0.0/255.255.0.0 | test123 | 2018-09-26 19:49:28 |
+----------------------------+---------+-----------------------+
再次測試
發現在192.168.10.1執行mysql命令連線,只有下面的可以連線,即test123@'192.168.10.1'有效
mysql -u test123 -p123456789 -h 192.168.10.101
發現在192.168.10.101執行mysql命令連線,只有下面的可以連線,即test123@'192.168.10.101'有效
mysql -u test123 -p12345678910 -h 192.168.10.101
得出結論,以下三條,誰先執行,192.168.10.1上以誰先生效
grant select on test1.* to test123@'192.168.10.1' identified by "123456789";
grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
得出結論,以下三條,誰先執行,192.168.10.101上以誰先生效
grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";
grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";
grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30126024/viewspace-2215129/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用者建立授權及刪除
- Oracle使用者的新增、修改、刪除及授權Oracle
- MySQL新建使用者,授權,刪除使用者,修改密碼MySql密碼
- MySql建立使用者及授權MySql
- MySQL新增使用者、刪除使用者與授權MySql
- MySQL新增列、刪除列,建立主鍵等常用操作總結MySql
- MySql中新增使用者,新建資料庫,使用者授權,刪除使用者,修改密碼MySql資料庫密碼
- mysql使用者建立與授權MySql
- MySQL建立使用者和授權MySql
- MySQL建立使用者與授權MySql
- [MYSQL][1]建立,修改,刪除表MySql
- 5. 建立使用者、授權以及修改密碼等許可權操作密碼
- MySQL建立使用者授權訪問MySql
- MySQL 5.7 建立使用者並授權MySql
- MySQL 語句大全:建立、授權、查詢、修改等MySql
- mysql各個級別授權的總結MySql
- 表的建立修改及增刪改查-DML操作
- Linux 建立修改刪除使用者和組Linux
- oracle建立/刪除表空間、建立/刪除使用者並賦予許可權Oracle
- MySQL建立索引、修改索引、刪除索引的命令語句MySql索引
- MySQL使用GRANT授權某使用者瀏覽和修改表許可權操作一例MySql
- Mysql 建立資料庫\新增使用者\使用者授權MySql資料庫
- MySQL5.7 實現遠端連線 —— 新增使用者、刪除使用者與授權MySql
- oracle11g同義詞synonym建立及授權且刪除相關測試Oracle
- MySql增加使用者、授權、修改密碼等語句MySql密碼
- Mysql資料庫值的新增、修改、刪除及清空MySql資料庫
- 支付寶怎麼刪除已授權應用?支付寶刪除已授權應用的方法
- mysql建立使用者並且對資料庫授權MySql資料庫
- oracle建立使用者並授權Oracle
- Oracle建立使用者和授權Oracle
- win10怎樣刪除office授權資訊_win10系統刪除office授權資訊的步驟Win10
- Mysql索引的建立與刪除MySql索引
- mysql 8.0.21使用者及許可權操作MySql
- mysql 新增、刪除使用者和許可權分配MySql
- ORACLE表空間的建立修改刪除Oracle
- vertica建立新使用者並授權
- Oracle建立表空間、建立使用者、授權、授權物件的訪問以及檢視許可權集合Oracle物件
- MySQ索引操作命令總結(建立、重建、查詢和刪除索引命令詳解)索引