MySQL操作权限整理
用戶權限管理主要有以下作用：
1. 可以限制用戶訪問哪些庫、哪些表
2. 可以限制用戶對哪些表執行SELECT、CREATE、DELETE、DELETE、ALTER等操作
3. 可以限制用戶登錄的IP或域名
4. 可以限制用戶自己的權限是否可以授權給別的用戶
一、用戶授權
?
mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
添加權限（和已有權限合并，不會覆蓋已有權限）
GRANT Insert ON `your database`.* TO `user`@`host`;刪除權限
REVOKE Delete ON `your database`.* FROM `user`@`host`;
all privileges：表示將所有權限授予給用戶。也可指定具體的權限，如：SELECT、CREATE、DROP等。
on：表示這些權限對哪些數據庫和表生效，格式：數據庫名.表名，這里寫“*”表示所有數據庫，所有表。如果我要指定將權限應用到test庫的user表中，可以這么寫：test.user
to：將權限授予哪個用戶。格式：”用戶名”@”登錄IP或域名”。%表示沒有限制，在任何主機都可以登錄。比如：”yangxin”@”192.168.0.%”，表示yangxin這個用戶只能在192.168.0IP段登錄
identified by：指定用戶的登錄密碼
with grant option：表示允許用戶將自己的權限授權給其它用戶
可以使用GRANT給用戶添加權限，權限會自動疊加，不會覆蓋之前授予的權限，比如你先給用戶添加一個SELECT權限，后來又給用戶添加了一個INSERT權限，那么該用戶就同時擁有了SELECT和INSERT權限。
用戶詳情的權限列表請參考MySQL官網說明：http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
二、刷新權限
對用戶做了權限變更之后，一定記得重新加載一下權限，將權限信息從內存中寫入數據庫。
mysql> flush privileges;
三、查看用戶權限
mysql> grant select,create,drop,update,alter on *.* to 'yangxin'@'localhost' identified by 'yangxin0917' with grant option;
mysql> show grants for 'yangxin'@'localhost';
四、回收權限
刪除yangxin這個用戶的create權限，該用戶將不能創建數據庫和表。
mysql> revoke create on *.* from 'yangxin@localhost'; mysql> flush privileges;
五、刪除用戶
mysql> select host,user from user;
+---------------+---------+
| host | user |
+---------------+---------+
| % | root |
| % | test3 |
| % | yx |
| 192.168.0.% | root |
| 192.168.0.% | test2 |
| 192.168.0.109 | test |
| ::1 | yangxin |
| localhost | yangxin |
+---------------+---------+
8 rows in set (0.00 sec)
mysql> drop user 'yangxin'@'localhost';
六、用戶重命名
shell> rename user 'test3'@'%' to 'test1'@'%';
七、修改密碼
1> 更新mysql.user表
mysql> use mysql;# mysql5.7之前 mysql> update user set password=password('123456') where user='root'; # mysql5.7之后 mysql> update user set authentication_string=password('123456') where user='root'; mysql> flush privileges;2> 用set password命令
語法：set password for ‘用戶名’@’登錄地址’=password(‘密碼’) mysql> set password for 'root'@'localhost'=password('123456');3> mysqladmin
語法：mysqladmin -u用戶名 -p舊的密碼 password 新密碼mysql> mysqladmin -uroot -p123456 password 1234abcd注意：mysqladmin位于mysql安裝目錄的bin目錄下八、忘記密碼
1> 添加登錄跳過權限檢查配置
修改my.cnf，在mysqld配置節點添加skip-grant-tables配置
[mysqld]
skip-grant-tables
2> 重新啟動mysql服務
shell> service mysqld restart
3> 修改密碼
此時在終端用mysql命令登錄時不需要用戶密碼，然后按照修改密碼的第一種方式將密碼修改即可。
注意：mysql庫的user表，5.7以下版本密碼字段為password，5.7以上版本密碼字段為authentication_string
4> 還原登錄權限跳過檢查配置
將my.cnf中mysqld節點的skip-grant-tables配置刪除，然后重新啟動服務即可。
?
Mysql 有多個個權限？經常記不住，今天總結一下，看后都能牢牢的記在心里啦！！
很明顯總共28個權限：下面是具體的權限介紹：轉載的，記錄一下：
?
一．權限表
mysql數據庫中的3個權限表：user?、db、?host
權限表的存取過程是：
1)先從user表中的host、?user、?password這3個字段中判斷連接的IP、用戶名、密碼是否存在表中，存在則通過身份驗證；
2)通過權限驗證，進行權限分配時，按照useràdbàtables_privàcolumns_priv的順序進行分配。即先檢查全局權限表user，如果user中對應的權限為Y，則此用戶對所有數據庫的權限都為Y，將不再檢查db,?tables_priv,columns_priv；如果為N，則到db表中檢查此用戶對應的具體數據庫，并得到db中為Y的權限；如果db中為N，則檢查tables_priv中此數據庫對應的具體表，取得表中的權限Y，以此類推。
二．MySQL各種權限（共27個）
（以下操作都是以root身份登陸進行grant授權，以p1@localhost身份登陸執行各種命令。）
1.?usage
連接（登陸）權限，建立一個用戶，就會自動授予其usage權限（默認授予）。
mysql>?grant?usage?on?*.*?to?‘p1′@’localhost’?identified?by?‘123′;
該權限只能用于數據庫登陸，不能執行任何操作；且usage權限不能被回收，也即REVOKE用戶并不能刪除用戶。
2.?select
必須有select的權限，才可以使用select?table
mysql>?grant?select?on?pyt.*?to?‘p1′@’localhost’;
mysql>?select?*?from?shop;
3.?create
必須有create的權限，才可以使用create?table
mysql>?grant?create?on?pyt.*?to?‘p1′@’localhost’;
4.?create?routine
必須具有create?routine的權限，才可以使用{create?|alter|drop}?{procedure|function}
mysql>?grant?create?routine?on?pyt.*?to?‘p1′@’localhost’;
當授予create?routine時，自動授予EXECUTE,?ALTER?ROUTINE權限給它的創建者：
mysql>?show?grants?for?‘p1′@’localhost’;
+—————————————————————————+
Grants?for?p1@localhost
+————————————————————————–+
|?GRANT?USAGE?ON?*.*?TO?‘p1′@’localhost’?IDENTIFIED?BY?PASSWORD?‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′?|
|?GRANT?SELECT,?CREATE,?CREATE?ROUTINE?ON?`pyt`.*?TO?‘p1′@’localhost’|
|?GRANT?EXECUTE,?ALTER?ROUTINE?ON?PROCEDURE?`pyt`.`pro_shop1`?TO?‘p1′@’localhost’?|
+————————————————————————————-+
5.?create?temporary?tables(注意這里是tables，不是table)
必須有create?temporary?tables的權限，才可以使用create?temporary?tables.
mysql>?grant?create?temporary?tables?on?pyt.*?to?‘p1′@’localhost’;
[mysql@mydev?~]$?mysql?-h?localhost?-u?p1?-p?pyt
mysql>?create?temporary?table?tt1(id?int);
6.?create?view
必須有create?view的權限，才可以使用create?view
mysql>?grant?create?view?on?pyt.*?to?‘p1′@’localhost’;
mysql>?create?view?v_shop?as?select?price?from?shop;
7.?create?user
要使用CREATE?USER，必須擁有mysql數據庫的全局CREATE?USER權限，或擁有INSERT權限。
mysql>?grant?create?user?on?*.*?to?‘p1′@’localhost’;
或：mysql>?grant?insert?on?*.*?to?p1@localhost;
8.?insert
必須有insert的權限，才可以使用insert?into?…..?values….
9.?alter
必須有alter的權限，才可以使用alter?table
alter?table?shop?modify?dealer?char(15);
10.?alter?routine
必須具有alter?routine的權限，才可以使用{alter?|drop}?{procedure|function}
mysql>grant?alter?routine?on?pyt.*?to?‘p1′@’?localhost?‘;
mysql>?drop?procedure?pro_shop;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?revoke?alter?routine?on?pyt.*?from?‘p1′@’localhost’;
[mysql@mydev?~]$?mysql?-h?localhost?-u?p1?-p?pyt
mysql>?drop?procedure?pro_shop;
ERROR?1370?(42000):?alter?routine?command?denied?to?user?‘p1′@’localhost’?for?routine?‘pyt.pro_shop’
11.?update
必須有update的權限，才可以使用update?table
mysql>?update?shop?set?price=3.5?where?article=0001?and?dealer=’A';
12.?delete
必須有delete的權限，才可以使用delete?from?….where….(刪除表中的記錄)
13.?drop
必須有drop的權限，才可以使用drop?database?db_name;?drop?table?tab_name;
drop?view?vi_name;?drop?index?in_name;
14.?show?database
通過show?database只能看到你擁有的某些權限的數據庫，除非你擁有全局SHOW?DATABASES權限。
對于p1@localhost用戶來說，沒有對mysql數據庫的權限，所以以此身份登陸查詢時，無法看到mysql數據庫：
mysql>?show?databases;
+——————–+
|?Database?|
+——————–+
|?information_schema|
|?pyt?|
|?test?|
+——————–+
15.?show?view
必須擁有show?view權限，才能執行show?create?view。
mysql>?grant?show?view?on?pyt.*?to?p1@localhost;
mysql>?show?create?view?v_shop;
16.?index
必須擁有index權限，才能執行[create?|drop]?index
mysql>?grant?index?on?pyt.*?to?p1@localhost;
mysql>?create?index?ix_shop?on?shop(article);
mysql>?drop?index?ix_shop?on?shop;
17.?excute
執行存在的Functions,Procedures
mysql>?call?pro_shop1(0001,@a)；
+———+
|?article?|
+———+
|?0001?|
|?0001?|
+———+
mysql>?select?@a;
+——+
|?@a?|
+——+
|?2?|
+——+
18.?lock?tables
必須擁有lock?tables權限，才可以使用lock?tables
mysql>?grant?lock?tables?on?pyt.*?to?p1@localhost;
mysql>?lock?tables?a1?read;
mysql>?unlock?tables;
19.?references
有了REFERENCES權限，用戶就可以將其它表的一個字段作為某一個表的外鍵約束。
20.?reload
必須擁有reload權限，才可以執行flush?[tables?|?logs?|?privileges]
mysql>?grant?reload?on?pyt.*?to?p1@localhost;
ERROR?1221?(HY000):?Incorrect?usage?of?DB?GRANT?and?GLOBAL?PRIVILEGES
mysql>?grant?reload?on?*.*?to?‘p1′@’localhost’;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?flush?tables;
21.?replication?client
擁有此權限可以查詢master?server、slave?server狀態。
mysql>?show?master?status;
ERROR?1227?(42000):?Access?denied;?you?need?the?SUPER,REPLICATION?CLIENT?privilege?for?this?operation
mysql>?grant?Replication?client?on?*.*?to?p1@localhost;
或：mysql>?grant?super?on?*.*?to?p1@localhost;
mysql>?show?master?status;
+——————+———-+————–+——————+
|?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|
+——————+———-+————–+——————+
|?mysql-bin.000006?|?2111?|?|?|
+——————+———-+————–+——————+
mysql>?show?slave?status;
22.?replication?slave
擁有此權限可以查看從服務器，從主服務器讀取二進制日志。
mysql>?show?slave?hosts;
ERROR?1227?(42000):?Access?denied;?you?need?the?REPLICATION?SLAVE?privilege?for?this?operation
mysql>?show?binlog?events;
ERROR?1227?(42000):?Access?denied;?you?need?the?REPLICATION?SLAVE?privilege?for?this?operation
mysql>?grant?replication?slave?on?*.*?to?p1@localhost;
mysql>?show?slave?hosts;
Empty?set?(0.00?sec)
mysql>show?binlog?events;
+—————+——-+—————-+———–+————-+————–+
|?Log_name?|?Pos?|?Event_type?|?Server_id|?End_log_pos|Info?|?
+—————+——-+————–+———–+————-+—————+
|?mysql-bin.000005?|?4?|?Format_desc?|?1?|?98?|?Server?ver:?5.0.77-log,?Binlog?ver:?4?|?|mysql-bin.000005|98|Query|1|197|use?`mysql`;?create?table?a1(i?int)engine=myisam|
……………………………………
23.?Shutdown
關閉MySQL：
[mysql@mydev?~]$?mysqladmin?shutdown
重新連接：
[mysql@mydev?~]$?mysql
ERROR?2002?(HY000):?Can’t?connect?to?local?MySQL?server?through?socket?‘/tmp/mysql.sock’?(2)
[mysql@mydev?~]$?cd?/u01/mysql/bin
[mysql@mydev?bin]$?./mysqld_safe?&
[mysql@mydev?bin]$?mysql
24.?grant?option
擁有grant?option，就可以將自己擁有的權限授予其他用戶（僅限于自己已經擁有的權限）
mysql>?grant?Grant?option?on?pyt.*?to?p1@localhost;
mysql>?grant?select?on?pyt.*?to?p2@localhost;
25.?file
擁有file權限才可以執行?select?..into?outfile和load?data?infile…操作，但是不要把file,?process,?super權限授予管理員以外的賬號，這樣存在嚴重的安全隱患。
mysql>?grant?file?on?*.*?to?p1@localhost;
mysql>?load?data?infile?‘/home/mysql/pet.txt’?into?table?pet;
26.?super
這個權限允許用戶終止任何查詢；修改全局變量的SET語句；使用CHANGE?MASTER，PURGE?MASTER?LOGS。
mysql>?grant?super?on?*.*?to?p1@localhost;
mysql>?purge?master?logs?before?‘mysql-bin.000006′;
27.?process
通過這個權限，用戶可以執行SHOW?PROCESSLIST和KILL命令。默認情況下，每個用戶都可以執行SHOW?PROCESSLIST命令，但是只能查詢本用戶的進程。
mysql>?show?processlist;
+—-+——+———–+——+———+——+——-+——————+
|?Id?|?User?|?Host?|?db?|?Command?|?Time?|?State?|?Info?|
+—-+——+———–+——+———+——+——-+——————+
|?12?|?p1?|?localhost?|?pyt?|?Query?|?0?|?NULL?|?show?processlist?|
+—-+——+———–+——+———+——+——-+——————+
另外，
管理權限（如?super，?process，?file等）不能夠指定某個數據庫，on后面必須跟*.*
mysql>?grant?super?on?pyt.*?to?p1@localhost;
ERROR?1221?(HY000):?Incorrect?usage?of?DB?GRANT?and?GLOBAL?PRIVILEGES
mysql>?grant?super?on?*.*?to?p1@localhost;
Query?OK,?0?rows?affected?(0.01?sec)
總結
以上是生活随笔為你收集整理的MySQL操作权限整理的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Acme CAD Converter 2
- 下一篇： easyui edatagrid 触发编