您好,登錄后才能下訂單哦!
本文主要給大家簡單講講管理MySQL用戶的詳細(xì)步驟,相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補(bǔ)充一下,這里就不涉獵了,我們就直奔主題吧,希望管理MySQL用戶的詳細(xì)步驟這篇文章可以給大家?guī)硪恍?shí)際幫助。
1# 創(chuàng)建用戶的一些限制和注意點(diǎn)
用戶名長度必須不超過16個(gè)字符
用戶名是大小寫敏感的
2# 創(chuàng)建用戶
語法:
(root@localhost)[(none)]> help create user
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification [, user_specification] ...
user_specification:
user [ identified_option ]
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}
The CREATE USER statement creates new MySQL accounts. An error occurs
if you try to create an account that already exists.
按照語法,最簡答的創(chuàng)建用戶的方法:
c(root@localhost)[mysql]> create user test1;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[mysql]> select user,host,password from user;
+-------+-----------+-------------------------------------------+
| user | host | password |
+-------+-----------+-------------------------------------------+
| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |
| test1 | % | |
+-------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
2 rows in set (0.00 sec)reate user test1;
這個(gè)時(shí)候其實(shí)密碼是空的,可以空密碼登錄的。
[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.31-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test1@localhost)[(none)]>
但是沒有任何權(quán)限:(USAGE這個(gè)權(quán)限,是代表廢物的意思!嗯,就是這樣)
(test1@localhost)[(none)]> show grants;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
3# 給用戶設(shè)置密碼:
命令
(root@localhost)[mysql]> help set password
Name: 'SET PASSWORD'
Description:
Syntax:
SET PASSWORD [FOR user] = password_option
password_option: {
PASSWORD('auth_string')
| OLD_PASSWORD('auth_string')
| 'hash_string'
}
給test1設(shè)置一個(gè)密碼:
(root@localhost)[mysql]> set password for test1=password('passwordtest');
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[mysql]> select user,host,password from user where user='test1';
+-------+------+-------------------------------------------+
| user | host | password |
+-------+------+-------------------------------------------+
| test1 | % | *A76A397AE758994B641D5C456139B88F40610926 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
至于OLD_PASSWORD()函數(shù),是為了兼容老版本的密碼而存在,古老的mysql4。
然而,set password for <user>=password('string'); 這種修改方式已經(jīng)被設(shè)置為要棄用,所以需要使用標(biāo)準(zhǔn)的修改密碼方式:
(root@localhost)[mysql]> alter user test1 identified by 'password4test1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'password4test1'' at line 1
(root@localhost)[mysql]>
以上可見報(bào)錯(cuò)了。原因是5.6還不支持這種密碼修改方式:
(root@localhost)[mysql]> help alter user;
Name: 'ALTER USER'
Description:
Syntax:
ALTER USER user_specification [, user_specification] ...
user_specification:
user PASSWORD EXPIRE
這里只有一個(gè)子句,就是設(shè)置密碼過期
3# 賬號的密碼過期:
(root@localhost)[mysql]> alter user test1 password expire;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[mysql]> select user,host,password,password_expired from user;
+-------+-----------+-------------------------------------------+------------------+
| user | host | password | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N |
| test1 | % | *A76A397AE758994B641D5C456139B88F40610926 | Y |
+-------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)
可以看到賬號密碼已經(jīng)過期。
但是過期以后還是可以登錄,但是什么都干不了,會提示馬上更改密碼:
[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'passwordtest'
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 4
Server version: 5.6.31-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test1@localhost)[(none)]> select 1
-> ;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
(test1@localhost)[(none)]>
#修改當(dāng)前賬戶的密碼:
(test1@localhost)[(none)]> set password = password('password4test1');
Query OK, 0 rows affected (0.00 sec)
(test1@localhost)[(none)]>
#再次嘗試登錄,并做查詢測試
[mysql@mysql01 ~]$ mysql -S /data/mysqldata/3306/mysql.sock -utest1 -p'password4test1'
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 5
Server version: 5.6.31-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test1@localhost)[(none)]> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
#查詢成功,說明密碼更改成功。用管理賬號查詢use表查看賬號狀態(tài):
(root@localhost)[mysql]> select user,host,password,password_expired from user;
+-------+-----------+-------------------------------------------+------------------+
| user | host | password | password_expired |
+-------+-----------+-------------------------------------------+------------------+
| root | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B | N |
| test1 | % | *CFA887C680E792C2DCF622D56FB809E3F8BE63CC | N |
+-------+-----------+-------------------------------------------+------------------+
2 rows in set (0.00 sec)
4# 遠(yuǎn)程登錄
在user表中,test1的host列值為%,代表可以從任意位置登錄mysql
[mysql@mysql01 ~]$ mysql -utest1 -p'password4test1' -h 192.168.199.101 -P 3306
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 11
Server version: 5.6.31-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test1@192.168.199.101)[(none)]>
5# 比較完整方式創(chuàng)建用戶
(root@localhost)[mysql]> create user test2@'%' identified by 'password4test2';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[mysql]> create user test2@'192.168.199.101' identified by 'test2local';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[mysql]> select user,host,password from user where user='test2';
+-------+-----------------+-------------------------------------------+
| user | host | password |
+-------+-----------------+-------------------------------------------+
| test2 | 192.168.199.101 | *74F386E8F5EEC7648BABDD0FCBA4524B97344856 |
| test2 | % | *5AB2E18AD9EE76F76E1C02E4DBF97BC7C3B4588B |
+-------+-----------------+-------------------------------------------+
2 rows in set (0.00 sec)
(root@localhost)[mysql]>
建立了兩個(gè)test2,這兩個(gè)test2是不同的,實(shí)際上應(yīng)該說,用戶test2@'192.168.199.101' 和用戶test2@'%' 是兩個(gè)不同的用戶。
[mysql@mysql01 ~]$ mysql -utest2 -p'test2local' -h 192.168.199.101 -P 3306
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 14
Server version: 5.6.31-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test2@192.168.199.101)[(none)]>
[mysql@mysql01 ~]$ mysql -utest2 -S /data/mysqldata/3306/mysql.sock -p'password4test2'
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 21
Server version: 5.6.31-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(test2@localhost)[(none)]>
5# 修改密碼:
1,set password 方式:
(root@localhost)[mysql]> set password for test1=password('password4test1');
Query OK, 0 rows affected (0.00 sec)
2,直接update系統(tǒng)表user,這種方式需要刷新權(quán)限列表
(root@localhost)[mysql]> update user set password=password('password4test1') where user='test1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
(root@localhost)[mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3,grant 方式
(root@localhost)[mysql]> grant usage on *.* to test1 identified by 'password4test1';
Query OK, 0 rows affected (0.01 sec)
管理MySQL用戶的詳細(xì)步驟就先給大家講到這里,對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。