下面跟著筆者一起來了解下如何更好的管理MySQL權(quán)限，相信大家看完肯定會受益匪淺，文字在精不在多，希望如何更好的管理MySQL權(quán)限這篇短內(nèi)容是你想要的。
成都創(chuàng)新互聯(lián)公司，專注為中小企業(yè)提供官網(wǎng)建設(shè)、營銷型網(wǎng)站制作、響應(yīng)式網(wǎng)站設(shè)計(jì)、展示型成都網(wǎng)站建設(shè)、做網(wǎng)站等服務(wù)，幫助中小企業(yè)通過網(wǎng)站體現(xiàn)價值、有效益。幫助企業(yè)快速建站、解決網(wǎng)站建設(shè)與網(wǎng)站營銷推廣問題。
1# 查看權(quán)限
比如，我們想要看看MySQL的root用戶，擁有什么權(quán)限：
(root@localhost)[(none)]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) (root@localhost)[(none)]> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
一條show grants解決了問題，并且連grant語句都給我們了。這樣就是說，我們稍微修修改改就可用重造出另一個和root一樣的超級用戶了。
或者使用：
show grants for 'xxx'@'xxxxx';
其中第一條：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION
不光光grant 了 . 上的ALL PRIVILEGES 給'root'@'localhost' ，甚至還有驗(yàn)證密碼和 WITH 信息。實(shí)際上這條語句是可用拿來創(chuàng)建這個用戶的。這也是一個另類的創(chuàng)建用戶的方法。
查看他人的權(quán)限：
(root@localhost)[(none)]> show grants for test1 -> ; +------------------------------------------------------------------------------------------------------+ | Grants for test1@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
這里看到用戶'test1'@'%'只有一條權(quán)限，這條權(quán)限也是默認(rèn)的創(chuàng)建用戶語句。
2# 授予權(quán)限：
用戶必然是需要使用數(shù)據(jù)庫的。所以如果用戶只有usage這個沒用的權(quán)限的話，這個用戶就不需要存在了。
語法：
重慶分公司
重慶分公司
(root@localhost)[(none)]> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification #這個代理也是語句也是單獨(dú)存在
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type: { #對象類型
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: { #權(quán)限的等級分類
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_specification: #用戶
user [ auth_option ]
auth_option: { #驗(yàn)證信息
IDENTIFIED BY 'auth_string'
| IDENTIFIED BY PASSWORD 'hash_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}
tsl_option: { #SSL類型
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: { #資源使用定義
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}用戶權(quán)限列表，見官方文檔：https://dev.mysql.com/doc/refman/5.6/en/grant.html
用戶test1當(dāng)前是沒有任何權(quán)限的。假設(shè)我們需要讓他訪問mysql.user表
(test1@localhost)[(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (test1@localhost)[mysql]> select count(*) from user; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) (test1@localhost)[mysql]>
建立一個新數(shù)據(jù)庫，建立一個新表，對這個表進(jìn)行訪問以及控制:
(root@localhost)[mysql]> create database sample; Query OK, 1 row affected (0.00 sec) (root@localhost)[mysql]> use sample; Database changed (root@localhost)[sample]> show tables; Empty set (0.00 sec) (root@localhost)[sample]> create table smp (id int,name char(20)); Query OK, 0 rows affected (0.07 sec) (root@localhost)[sample]> grant all privileges on sample.smp to test1; Query OK, 0 rows affected (0.00 sec) (root@localhost)[sample]> select User,Table_name,Table_priv from mysql.tables_priv; +-------+------------+----------------------------------------------------------------------------------------------+ | User | Table_name | Table_priv | +-------+------------+----------------------------------------------------------------------------------------------+ | test1 | user | Select | | test1 | smp | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger | +-------+------------+----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) (root@localhost)[sample]>
更多的怎刪改和刪除表
(test1@localhost)[sample]> insert into smp values (1,'abc'); Query OK, 1 row affected (0.00 sec) (test1@localhost)[sample]> select * from smp; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) (test1@localhost)[sample]> delete from smp; Query OK, 1 row affected (0.00 sec) (test1@localhost)[sample]> select * from smp; Empty set (0.00 sec) (test1@localhost)[sample]> drep table smp; 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 'drep table smp' at line 1 (test1@localhost)[sample]> drop table smp; Query OK, 0 rows affected (0.00 sec)
drop table 是DDL, 這個時候table已經(jīng)刪除了，再看看權(quán)限：
(root@localhost)[sample]> select User,Table_name,Table_priv from mysql.tables_priv; +-------+------------+----------------------------------------------------------------------------------------------+ | User | Table_name | Table_priv | +-------+------------+----------------------------------------------------------------------------------------------+ | test1 | user | Select | | test1 | smp | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger | +-------+------------+----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
權(quán)限依然存在。說明刪除表是不會刪除用戶所擁有的對象權(quán)限的。
試試看建回來：
(test1@localhost)[sample]> create table smp (id int,name char(20)); Query OK, 0 rows affected (0.00 sec) (test1@localhost)[sample]> create table smp1 (id int,name char(20)); ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table 'smp1' (test1@localhost)[sample]>
成功建回來。那么是否可用在這個數(shù)據(jù)庫中建立另外一張表呢？不行。
對 sample. 層面授予權(quán)限。
(root@localhost)[sample]> grant create on sample.* to test1; Query OK, 0 rows affected (0.00 sec) (root@localhost)[sample]> select User,Table_name,Table_priv from mysql.tables_priv; +-------+------------+----------------------------------------------------------------------------------------------+ | User | Table_name | Table_priv | +-------+------------+----------------------------------------------------------------------------------------------+ | test1 | user | Select | | test1 | smp | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger | +-------+------------+----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) (root@localhost)[sample]> show grants for test1; +------------------------------------------------------------------------------------------------------+ | Grants for test1@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' | | GRANT CREATE ON `sample`.* TO 'test1'@'%' | | GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%' | | GRANT SELECT ON `mysql`.`user` TO 'test1'@'%' | +------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) (root@localhost)[sample]> flush privileges; Query OK, 0 rows affected (0.00 sec)
這個時候，test1用戶有了CREATE ON sample. 。嘗試在數(shù)據(jù)庫中創(chuàng)建表對象。
(test1@localhost)[sample]> create table smp1 (id int,name char(20)); ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table 'smp1'
失??！很奇怪，已經(jīng)有了權(quán)限，還是失敗。嘗試重新登陸
(test1@localhost)[sample]> exit Bye [mysql@mysql01 ~]$ mysql -utest1 -S /data/mysqldata/3306/mysql.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 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)]> (test1@localhost)[(none)]> (test1@localhost)[(none)]> create table smp1 (id int,name char(20)); ERROR 1046 (3D000): No database selected (test1@localhost)[(none)]> use sample; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (test1@localhost)[sample]> create table smp1 (id int,name char(20)); Query OK, 0 rows affected (0.01 sec)
重新登陸后建立表對象成功。說明普通用戶的權(quán)限需要在登陸的時候刷新。
另類方法查看MYSQL所有權(quán)限有哪些：
mysql> grant ALL PRIVILEGES ON *.* to test@'localhost' IDENTIFIED BY 'oldboy123' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +------------+---------------------------+ | user | host | +------------+---------------------------+ | mysql_user | % | | root | 127.0.0.1 | | rep | 192.168.1.% | | alrin | 192.168.1.0/24 | | alrin | 192.168.1.0/255.255.255.0 | | root | localhost | | test | localhost | +------------+---------------------------+ 7 rows in set (0.00 sec) mysql> show grants for test@localhost -> ; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> revoke insert on *.* from 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye [root@mysql01 3307]# mysql -utest -poldboy123 -S /data/3307/mysql.sock -e "show grants for 'test'@'localhost'"| grep GRANT|tail -1| tr ',' '\n' > mysql_all_privileges.txt Warning: Using a password on the command line interface can be insecure. [root@mysql01 3307]# mysql -utest -poldboy123 -S /data/3307/mysql.sock -e "show grants for 'test'@'localhost'"| grep GRANT|tail -1| tr ',' '\n' > mysql_all_privileges.txt [root@mysql01 3307]# cat mysql_all_privileges.txt GRANT SELECT UPDATE DELETE CREATE DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CREATE TABLESPACE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION [root@mysql01 3307]# vi mysql_all_privileges.txt GRANT SELECT UPDATE DELETE INSERT CREATE DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CREATE TABLESPACE ON *.* TO 'test'@'localhost' WITH GRANT OPTION ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ "mysql_all_privileges.txt" 28L, 370C written[root@mysql01 3307]#
看完如何更好的管理MySQL權(quán)限這篇文章后，很多讀者朋友肯定會想要了解更多的相關(guān)內(nèi)容，如需獲取更多的行業(yè)信息，可以關(guān)注我們的行業(yè)資訊欄目。
