溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下面跟著筆者一起來了解下如何更好的管理MySQL權限，相信大家看完肯定會受益匪淺，文字在精不在多，希望如何更好的管理MySQL權限這篇短內容是你想要的。
1# 查看權限
比如，我們想要看看MySQL的root用戶，擁有什么權限：
(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' ，甚至還有驗證密碼和 WITH 信息。實際上這條語句是可用拿來創建這個用戶的。這也是一個另類的創建用戶的方法。
查看他人的權限：
(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'@'%'只有一條權限，這條權限也是默認的創建用戶語句。
2# 授予權限：
用戶必然是需要使用數據庫的。所以如果用戶只有usage這個沒用的權限的話，這個用戶就不需要存在了。
語法：
(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 #這個代理也是語句也是單獨存在
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type: { #對象類型
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: { #權限的等級分類
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user_specification: #用戶
user [ auth_option ]
auth_option: { #驗證信息
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
}用戶權限列表，見官方文檔：https://dev.mysql.com/doc/refman/5.6/en/grant.html
用戶test1當前是沒有任何權限的。假設我們需要讓他訪問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]>
建立一個新數據庫，建立一個新表，對這個表進行訪問以及控制:
(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已經刪除了，再看看權限：
(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)
權限依然存在。說明刪除表是不會刪除用戶所擁有的對象權限的。
試試看建回來：
(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]>
成功建回來。那么是否可用在這個數據庫中建立另外一張表呢？不行。
對 sample. 層面授予權限。
(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. 。嘗試在數據庫中創建表對象。
(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]> 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)
重新登陸后建立表對象成功。說明普通用戶的權限需要在登陸的時候刷新。
另類方法查看MYSQL所有權限有哪些：
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權限這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
