可能做開發的多半不太關注這方面,但是要說到做運維,那就不能不關注了。因為我們都知道,root的權限太大了,不是隨便能用的,我們平時最好用一些比較低的權限的用戶,這樣會讓我們的安全性大大提高,也能防止我們平常中因為誤操作而造成不必要的損失。
首先我們需要查看mysql中的所有賬戶,我們在mysql數據庫中的user表中查看信息即可,但是呢,由於我們直接select * from user的話會顯示很多的和權限相關的信息,極大的影響我們的閱讀,因此我們這裡只查看三個比較重要的字段,下面是我的機器中的操作演示:
mysql> select host,user,password from mysql.user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | ::1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | +-----------+------+-------------------------------------------+ 3 rows in set (0.00 sec)
然後我們創建一個用戶,這裡我們需要制定用戶名和連接的地址,也就是一樣的用戶名(user),不一樣的host,也會被當做不一樣的對象,我們可以使用通配符,其中%表示匹配任何多個字符,而-表示匹配一個字符。下面我創建一個用戶test,並且制定密碼為xin,而且可以從任何主機登錄,看我操作:
mysql> create user 'test'@'%' identified by 'xin'; Query OK, 0 rows affected (0.23 sec)
這裡我們用該用戶登錄的信息顯示如下:
C:\Users\Administrator>mysql -u test -pxin mysql: [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.7.3-m13 MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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. mysql>
比如我們此時想查看一下user信息都有什麼,結果卻發現:
mysql> select host,user,password from mysql.user; ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user' mysql>
也就是我們這裡並不用有select該user表的權限,那麼就會涉及到權限管理了,我們這裡可以用show grants來查看一下自己的權限:
mysql> show grants; +-----------------------------------------------------------------------------------------------------+ | Grants for test@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*76995602B7611FA37648852F235D6ECB29D844E2' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
由於這裡的test用戶的權限還是很小的,我們還是使用root用戶登錄,然後此時我們看一下用戶表,就發現多了一個新用戶:
mysql> select host,user,password from mysql.user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | ::1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | % | test | *76995602B7611FA37648852F235D6ECB29D844E2 | +-----------+------+-------------------------------------------+ 4 rows in set (0.00 sec)
root用戶具有至高無上的權限,因此他可以修改密碼,第一種方式就是使用set password語句,比如我們給剛才的新用戶設置一個密碼,我們可以使用如下命令:
mysql> set password for 'test'@'%' = password('qian'); Query OK, 0 rows affected (0.00 sec)
此時我們就可以使用test這個用戶登錄了,下面看我代碼:
C:\Users\Administrator>mysql -u test -pqian mysql: [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 17 Server version: 5.7.3-m13 MySQL Community Server (GPL) Copyright (c) 2000, 2013, 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. mysql>
其實我們還可以直接使用update語句來修改user這個表,但是我們需要使用flush privileges;來讓它立即生效:
mysql> update mysql.user set password = password('nan') where user = 'test'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)
然後我們就可以用'nan‘這個密碼登錄了,我這裡就不給出演示代碼了,畢竟很簡單的操作,我是辛星,求支持。