MySQL的权限有哪些 mysql有关权限的表都有哪几个
MySQL\u7684\u6743\u9650\u6709\u54ea\u4e9b\u4e0d\u77e5\u9053\u4f60\u95ee\u7684\u4ec0\u4e48\u610f\u601d\uff0cmysql\u4e2d\u7528\u6237\u7684\u6743\u5982\u4e0b\uff1a
Select_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u901a\u8fc7SELECT\u547d\u4ee4\u9009\u62e9\u6570\u636e\u3002
Insert_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u901a\u8fc7INSERT\u547d\u4ee4\u63d2\u5165\u6570\u636e\u3002
Update_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u901a\u8fc7UPDATE\u547d\u4ee4\u4fee\u6539\u73b0\u6709\u6570\u636e\u3002
Delete_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u901a\u8fc7DELETE\u547d\u4ee4\u5220\u9664\u73b0\u6709\u6570\u636e\u3002
Create_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u521b\u5efa\u65b0\u7684\u6570\u636e\u5e93\u548c\u8868\u3002
Drop_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u5220\u9664\u73b0\u6709\u6570\u636e\u5e93\u548c\u8868\u3002
Reload_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u6267\u884c\u5237\u65b0\u548c\u91cd\u65b0\u52a0\u8f7dMySQL\u6240\u7528\u5404\u79cd\u5185\u90e8\u7f13\u5b58\u7684\u7279\u5b9a\u547d\u4ee4\uff0c\u5305\u62ec\u65e5\u5fd7\u3001\u6743\u9650\u3001\u4e3b\u673a\u3001\u67e5\u8be2\u548c\u8868\u3002
Shutdown_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u5173\u95edMySQL\u670d\u52a1\u5668\u3002\u5728\u5c06\u6b64\u6743\u9650\u63d0\u4f9b\u7ed9root\u8d26\u6237\u4e4b\u5916\u7684\u4efb\u4f55\u7528\u6237\u65f6\uff0c\u90fd\u5e94\u5f53\u975e\u5e38\u8c28\u614e\u3002
Process_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u901a\u8fc7SHOW
PROCESSLIST\u547d\u4ee4\u67e5\u770b\u5176\u4ed6\u7528\u6237\u7684\u8fdb\u7a0b\u3002
File_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u6267\u884cSELECT INTO OUTFILE\u548cLOAD DATA
INFILE\u547d\u4ee4\u3002
Grant_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u5c06\u5df2\u7ecf\u6388\u4e88\u7ed9\u8be5\u7528\u6237\u81ea\u5df1\u7684\u6743\u9650\u518d\u6388\u4e88\u5176\u4ed6\u7528\u6237\u3002\u4f8b\u5982\uff0c\u5982\u679c\u7528\u6237\u53ef\u4ee5\u63d2\u5165\u3001\u9009\u62e9\u548c\u5220\u9664foo\u6570\u636e\u5e93\u4e2d\u7684\u4fe1\u606f\uff0c\u5e76\u4e14\u6388\u4e88\u4e86GRANT\u6743\u9650\uff0c\u5219\u8be5\u7528\u6237\u5c31\u53ef\u4ee5\u5c06\u5176\u4efb\u4f55\u6216\u5168\u90e8\u6743\u9650\u6388\u4e88\u7cfb\u7edf\u4e2d\u7684\u4efb\u4f55\u5176\u4ed6\u7528\u6237\u3002
References_priv\u3002\u76ee\u524d\u53ea\u662f\u67d0\u4e9b\u672a\u6765\u529f\u80fd\u7684\u5360\u4f4d\u7b26\uff1b\u73b0\u5728\u6ca1\u6709\u4f5c\u7528\u3002
Index_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u521b\u5efa\u548c\u5220\u9664\u8868\u7d22\u5f15\u3002
Alter_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u91cd\u547d\u540d\u548c\u4fee\u6539\u8868\u7ed3\u6784\u3002
Show_db_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u67e5\u770b\u670d\u52a1\u5668\u4e0a\u6240\u6709\u6570\u636e\u5e93\u7684\u540d\u5b57\uff0c\u5305\u62ec\u7528\u6237\u62e5\u6709\u8db3\u591f\u8bbf\u95ee\u6743\u9650\u7684\u6570\u636e\u5e93\u3002\u53ef\u4ee5\u8003\u8651\u5bf9\u6240\u6709\u7528\u6237\u7981\u7528\u8fd9\u4e2a\u6743\u9650\uff0c\u9664\u975e\u6709\u7279\u522b\u4e0d\u53ef\u6297\u62d2\u7684\u539f\u56e0\u3002
Super_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u6267\u884c\u67d0\u4e9b\u5f3a\u5927\u7684\u7ba1\u7406\u529f\u80fd\uff0c\u4f8b\u5982\u901a\u8fc7KILL\u547d\u4ee4\u5220\u9664\u7528\u6237\u8fdb\u7a0b\uff0c\u4f7f\u7528SET
GLOBAL\u4fee\u6539\u5168\u5c40MySQL\u53d8\u91cf\uff0c\u6267\u884c\u5173\u4e8e\u590d\u5236\u548c\u65e5\u5fd7\u7684\u5404\u79cd\u547d\u4ee4\u3002
Create_tmp_table_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u521b\u5efa\u4e34\u65f6\u8868\u3002
Lock_tables_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u4f7f\u7528LOCK
TABLES\u547d\u4ee4\u963b\u6b62\u5bf9\u8868\u7684\u8bbf\u95ee/\u4fee\u6539\u3002
Execute_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u6267\u884c\u5b58\u50a8\u8fc7\u7a0b\u3002\u6b64\u6743\u9650\u53ea\u5728MySQL
5.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u6709\u610f\u4e49\u3002
Repl_slave_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u8bfb\u53d6\u7528\u4e8e\u7ef4\u62a4\u590d\u5236\u6570\u636e\u5e93\u73af\u5883\u7684\u4e8c\u8fdb\u5236\u65e5\u5fd7\u6587\u4ef6\u3002\u6b64\u7528\u6237\u4f4d\u4e8e\u4e3b\u7cfb\u7edf\u4e2d\uff0c\u6709\u5229\u4e8e\u4e3b\u673a\u548c\u5ba2\u6237\u673a\u4e4b\u95f4\u7684\u901a\u4fe1\u3002
Repl_client_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u786e\u5b9a\u590d\u5236\u4ece\u670d\u52a1\u5668\u548c\u4e3b\u670d\u52a1\u5668\u7684\u4f4d\u7f6e\u3002
Create_view_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u521b\u5efa\u89c6\u56fe\u3002\u6b64\u6743\u9650\u53ea\u5728MySQL
5.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u6709\u610f\u4e49\u3002\u5173\u4e8e\u89c6\u56fe\u7684\u66f4\u591a\u4fe1\u606f\uff0c\u53c2\u89c1\u7b2c34\u7ae0\u3002
Show_view_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u67e5\u770b\u89c6\u56fe\u6216\u4e86\u89e3\u89c6\u56fe\u5982\u4f55\u6267\u884c\u3002\u6b64\u6743\u9650\u53ea\u5728MySQL
5.0\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u6709\u610f\u4e49\u3002\u5173\u4e8e\u89c6\u56fe\u7684\u66f4\u591a\u4fe1\u606f\uff0c\u53c2\u89c1\u7b2c34\u7ae0\u3002
Create_routine_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u66f4\u6539\u6216\u653e\u5f03\u5b58\u50a8\u8fc7\u7a0b\u548c\u51fd\u6570\u3002\u6b64\u6743\u9650\u662f\u5728MySQL
5.0\u4e2d\u5f15\u5165\u7684\u3002
Alter_routine_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u4fee\u6539\u6216\u5220\u9664\u5b58\u50a8\u51fd\u6570\u53ca\u51fd\u6570\u3002\u6b64\u6743\u9650\u662f\u5728MySQL
5.0\u4e2d\u5f15\u5165\u7684\u3002
Create_user_priv\u3002\u786e\u5b9a\u7528\u6237\u662f\u5426\u53ef\u4ee5\u6267\u884cCREATE
USER\u547d\u4ee4\uff0c\u8fd9\u4e2a\u547d\u4ee4\u7528\u4e8e\u521b\u5efa\u65b0\u7684MySQL\u8d26\u6237\u3002
Event_priv\u3002\u786e\u5b9a\u7528\u6237\u80fd\u5426\u521b\u5efa\u3001\u4fee\u6539\u548c\u5220\u9664\u4e8b\u4ef6\u3002\u8fd9\u4e2a\u6743\u9650\u662fMySQL
5.1.6\u65b0\u589e\u7684\u3002
Trigger_priv\u3002\u786e\u5b9a\u7528\u6237\u80fd\u5426\u521b\u5efa\u548c\u5220\u9664\u89e6\u53d1\u5668\uff0c\u8fd9\u4e2a\u6743\u9650\u662fMySQL 5.1.6\u65b0\u589e\u7684\u3002
\u4e00\uff0e\u6743\u9650\u8868
mysql\u6570\u636e\u5e93\u4e2d\u76843\u4e2a\u6743\u9650\u8868\uff1auser \u3001db\u3001 host
\u6743\u9650\u8868\u7684\u5b58\u53d6\u8fc7\u7a0b\u662f\uff1a
1)\u5148\u4eceuser\u8868\u4e2d\u7684host\u3001 user\u3001 password\u8fd93\u4e2a\u5b57\u6bb5\u4e2d\u5224\u65ad\u8fde\u63a5\u7684IP\u3001\u7528\u6237\u540d\u3001\u5bc6\u7801\u662f\u5426\u5b58\u5728\u8868\u4e2d\uff0c\u5b58\u5728\u5219\u901a\u8fc7\u8eab\u4efd\u9a8c\u8bc1\uff1b
2)\u901a\u8fc7\u6743\u9650\u9a8c\u8bc1\uff0c\u8fdb\u884c\u6743\u9650\u5206\u914d\u65f6\uff0c\u6309\u7167user\u00e0db\u00e0tables_priv\u00e0columns_priv\u7684\u987a\u5e8f\u8fdb\u884c\u5206\u914d\u3002\u5373\u5148\u68c0\u67e5\u5168\u5c40\u6743\u9650\u8868user\uff0c\u5982\u679cuser\u4e2d\u5bf9\u5e94\u7684\u6743\u9650\u4e3aY\uff0c\u5219\u6b64\u7528\u6237\u5bf9\u6240\u6709\u6570\u636e\u5e93\u7684\u6743\u9650\u90fd\u4e3aY\uff0c\u5c06\u4e0d\u518d\u68c0\u67e5db, tables_priv,columns_priv\uff1b\u5982\u679c\u4e3aN\uff0c\u5219\u5230db\u8868\u4e2d\u68c0\u67e5\u6b64\u7528\u6237\u5bf9\u5e94\u7684\u5177\u4f53\u6570\u636e\u5e93\uff0c\u5e76\u5f97\u5230db\u4e2d\u4e3aY\u7684\u6743\u9650\uff1b\u5982\u679cdb\u4e2d\u4e3aN\uff0c\u5219\u68c0\u67e5tables_priv\u4e2d\u6b64\u6570\u636e\u5e93\u5bf9\u5e94\u7684\u5177\u4f53\u8868\uff0c\u53d6\u5f97\u8868\u4e2d\u7684\u6743\u9650Y\uff0c\u4ee5\u6b64\u7c7b\u63a8\u3002
\u4e8c\uff0eMySQL\u5404\u79cd\u6743\u9650\uff08\u517127\u4e2a\uff09
\uff08\u4ee5\u4e0b\u64cd\u4f5c\u90fd\u662f\u4ee5root\u8eab\u4efd\u767b\u9646\u8fdb\u884cgrant\u6388\u6743\uff0c\u4ee5p1@localhost\u8eab\u4efd\u767b\u9646\u6267\u884c\u5404\u79cd\u547d\u4ee4\u3002\uff09
1. usage
\u8fde\u63a5\uff08\u767b\u9646\uff09\u6743\u9650\uff0c\u5efa\u7acb\u4e00\u4e2a\u7528\u6237\uff0c\u5c31\u4f1a\u81ea\u52a8\u6388\u4e88\u5176usage\u6743\u9650\uff08\u9ed8\u8ba4\u6388\u4e88\uff09\u3002
mysql> grant usage on *.* to \u2018p1\u2032@\u2019localhost\u2019 identified by \u2018123\u2032;
\u8be5\u6743\u9650\u53ea\u80fd\u7528\u4e8e\u6570\u636e\u5e93\u767b\u9646\uff0c\u4e0d\u80fd\u6267\u884c\u4efb\u4f55\u64cd\u4f5c\uff1b\u4e14usage\u6743\u9650\u4e0d\u80fd\u88ab\u56de\u6536\uff0c\u4e5f\u5373REVOKE\u7528\u6237\u5e76\u4e0d\u80fd\u5220\u9664\u7528\u6237\u3002
2. select
\u5fc5\u987b\u6709select\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528select table
mysql> grant select on pyt.* to \u2018p1\u2032@\u2019localhost\u2019;
mysql> select * from shop;
3. create
\u5fc5\u987b\u6709create\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528create table
mysql> grant create on pyt.* to \u2018p1\u2032@\u2019localhost\u2019;
4. create routine
\u5fc5\u987b\u5177\u6709create routine\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528{create |alter|drop} {procedure|function}
mysql> grant create routine on pyt.* to \u2018p1\u2032@\u2019localhost\u2019;
\u5f53\u6388\u4e88create routine\u65f6\uff0c\u81ea\u52a8\u6388\u4e88EXECUTE, ALTER ROUTINE\u6743\u9650\u7ed9\u5b83\u7684\u521b\u5efa\u8005\uff1a
mysql> show grants for \u2018p1\u2032@\u2019localhost\u2019;
+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014+
Grants for p1@localhost
+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
| GRANT USAGE ON *.* TO \u2018p1\u2032@\u2019localhost\u2019 IDENTIFIED BY PASSWORD \u2018*23AE809DDACAF96AF0FD78ED04B6A265E05AA257\u2032 |
| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO \u2018p1\u2032@\u2019localhost\u2019|
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO \u2018p1\u2032@\u2019localhost\u2019 |
+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
5. create temporary tables(\u6ce8\u610f\u8fd9\u91cc\u662ftables\uff0c\u4e0d\u662ftable)
\u5fc5\u987b\u6709create temporary tables\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528create temporary tables.
mysql> grant create temporary tables on pyt.* to \u2018p1\u2032@\u2019localhost\u2019;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> create temporary table tt1(id int);
6. create view
\u5fc5\u987b\u6709create view\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528create view
mysql> grant create view on pyt.* to \u2018p1\u2032@\u2019localhost\u2019;
mysql> create view v_shop as select price from shop;
7. create user
\u8981\u4f7f\u7528CREATE USER\uff0c\u5fc5\u987b\u62e5\u6709mysql\u6570\u636e\u5e93\u7684\u5168\u5c40CREATE USER\u6743\u9650\uff0c\u6216\u62e5\u6709INSERT\u6743\u9650\u3002
mysql> grant create user on *.* to \u2018p1\u2032@\u2019localhost\u2019;
\u6216\uff1amysql> grant insert on *.* to p1@localhost;
8. insert
\u5fc5\u987b\u6709insert\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528insert into \u2026.. values\u2026.
9. alter
\u5fc5\u987b\u6709alter\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528alter table
alter table shop modify dealer char(15);
10. alter routine
\u5fc5\u987b\u5177\u6709alter routine\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528{alter |drop} {procedure|function}
mysql>grant alter routine on pyt.* to \u2018p1\u2032@\u2019 localhost \u2018;
mysql> drop procedure pro_shop;
Query OK, 0 rows affected (0.00 sec)
mysql> revoke alter routine on pyt.* from \u2018p1\u2032@\u2019localhost\u2019;
[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt
mysql> drop procedure pro_shop;
ERROR 1370 (42000): alter routine command denied to user \u2018p1\u2032@\u2019localhost\u2019 for routine \u2018pyt.pro_shop\u2019
11. update
\u5fc5\u987b\u6709update\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528update table
mysql> update shop set price=3.5 where article=0001 and dealer=\u2019A';
12. delete
\u5fc5\u987b\u6709delete\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528delete from \u2026.where\u2026.(\u5220\u9664\u8868\u4e2d\u7684\u8bb0\u5f55)
13. drop
\u5fc5\u987b\u6709drop\u7684\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528drop database db_name; drop table tab_name;
drop view vi_name; drop index in_name;
14. show database
\u901a\u8fc7show database\u53ea\u80fd\u770b\u5230\u4f60\u62e5\u6709\u7684\u67d0\u4e9b\u6743\u9650\u7684\u6570\u636e\u5e93\uff0c\u9664\u975e\u4f60\u62e5\u6709\u5168\u5c40SHOW DATABASES\u6743\u9650\u3002
\u5bf9\u4e8ep1@localhost\u7528\u6237\u6765\u8bf4\uff0c\u6ca1\u6709\u5bf9mysql\u6570\u636e\u5e93\u7684\u6743\u9650\uff0c\u6240\u4ee5\u4ee5\u6b64\u8eab\u4efd\u767b\u9646\u67e5\u8be2\u65f6\uff0c\u65e0\u6cd5\u770b\u5230mysql\u6570\u636e\u5e93\uff1a
mysql> show databases;
+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
| Database |
+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
| information_schema|
| pyt |
| test |
+\u2014\u2014\u2014\u2014\u2014\u2014\u2013+
15. show view
\u5fc5\u987b\u62e5\u6709show view\u6743\u9650\uff0c\u624d\u80fd\u6267\u884cshow create view\u3002
mysql> grant show view on pyt.* to p1@localhost;
mysql> show create view v_shop;
16. index
\u5fc5\u987b\u62e5\u6709index\u6743\u9650\uff0c\u624d\u80fd\u6267\u884c[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
\u6267\u884c\u5b58\u5728\u7684Functions,Procedures
mysql> call pro_shop1(0001,@a)\uff1b
+\u2014\u2014\u2014+
| article |
+\u2014\u2014\u2014+
| 0001 |
| 0001 |
+\u2014\u2014\u2014+
mysql> select @a;
+\u2014\u2014+
| @a |
+\u2014\u2014+
| 2 |
+\u2014\u2014+
18. lock tables
\u5fc5\u987b\u62e5\u6709lock tables\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u4f7f\u7528lock tables
mysql> grant lock tables on pyt.* to p1@localhost;
mysql> lock tables a1 read;
mysql> unlock tables;
19. references
\u6709\u4e86REFERENCES\u6743\u9650\uff0c\u7528\u6237\u5c31\u53ef\u4ee5\u5c06\u5176\u5b83\u8868\u7684\u4e00\u4e2a\u5b57\u6bb5\u4f5c\u4e3a\u67d0\u4e00\u4e2a\u8868\u7684\u5916\u952e\u7ea6\u675f\u3002
20. reload
\u5fc5\u987b\u62e5\u6709reload\u6743\u9650\uff0c\u624d\u53ef\u4ee5\u6267\u884cflush [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 \u2018p1\u2032@\u2019localhost\u2019;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables;
21. replication client
\u62e5\u6709\u6b64\u6743\u9650\u53ef\u4ee5\u67e5\u8be2master server\u3001slave server\u72b6\u6001\u3002
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;
\u6216\uff1amysql> grant super on *.* to p1@localhost;
mysql> show master status;
+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
| mysql-bin.000006 | 2111 | | |
+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
mysql> show slave status;
22. replication slave
\u62e5\u6709\u6b64\u6743\u9650\u53ef\u4ee5\u67e5\u770b\u4ece\u670d\u52a1\u5668\uff0c\u4ece\u4e3b\u670d\u52a1\u5668\u8bfb\u53d6\u4e8c\u8fdb\u5236\u65e5\u5fd7\u3002
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;
+\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+
| Log_name | Pos | Event_type | Server_id| End_log_pos|Info | +\u2014\u2014\u2014\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014+
| 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|
\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026
23. Shutdown
\u5173\u95edMySQL\uff1a
[mysql@mydev ~]$ mysqladmin shutdown
\u91cd\u65b0\u8fde\u63a5\uff1a
[mysql@mydev ~]$ mysql
ERROR 2002 (HY000): Can\u2019t connect to local MySQL server through socket \u2018/tmp/mysql.sock\u2019 (2)
[mysql@mydev ~]$ cd /u01/mysql/bin
[mysql@mydev bin]$ ./mysqld_safe &
[mysql@mydev bin]$ mysql
24. grant option
\u62e5\u6709grant option\uff0c\u5c31\u53ef\u4ee5\u5c06\u81ea\u5df1\u62e5\u6709\u7684\u6743\u9650\u6388\u4e88\u5176\u4ed6\u7528\u6237\uff08\u4ec5\u9650\u4e8e\u81ea\u5df1\u5df2\u7ecf\u62e5\u6709\u7684\u6743\u9650\uff09
mysql> grant Grant option on pyt.* to p1@localhost;
mysql> grant select on pyt.* to p2@localhost;
25. file
\u62e5\u6709file\u6743\u9650\u624d\u53ef\u4ee5\u6267\u884c select ..into outfile\u548cload data infile\u2026\u64cd\u4f5c\uff0c\u4f46\u662f\u4e0d\u8981\u628afile, process, super\u6743\u9650\u6388\u4e88\u7ba1\u7406\u5458\u4ee5\u5916\u7684\u8d26\u53f7\uff0c\u8fd9\u6837\u5b58\u5728\u4e25\u91cd\u7684\u5b89\u5168\u9690\u60a3\u3002
mysql> grant file on *.* to p1@localhost;
mysql> load data infile \u2018/home/mysql/pet.txt\u2019 into table pet;
26. super
\u8fd9\u4e2a\u6743\u9650\u5141\u8bb8\u7528\u6237\u7ec8\u6b62\u4efb\u4f55\u67e5\u8be2\uff1b\u4fee\u6539\u5168\u5c40\u53d8\u91cf\u7684SET\u8bed\u53e5\uff1b\u4f7f\u7528CHANGE MASTER\uff0cPURGE MASTER LOGS\u3002
mysql> grant super on *.* to p1@localhost;
mysql> purge master logs before \u2018mysql-bin.000006\u2032;
27. process
\u901a\u8fc7\u8fd9\u4e2a\u6743\u9650\uff0c\u7528\u6237\u53ef\u4ee5\u6267\u884cSHOW PROCESSLIST\u548cKILL\u547d\u4ee4\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6bcf\u4e2a\u7528\u6237\u90fd\u53ef\u4ee5\u6267\u884cSHOW PROCESSLIST\u547d\u4ee4\uff0c\u4f46\u662f\u53ea\u80fd\u67e5\u8be2\u672c\u7528\u6237\u7684\u8fdb\u7a0b\u3002
mysql> show processlist;
+\u2014-+\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014+
| Id | User | Host | db | Command | Time | State | Info |
+\u2014-+\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014+
| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |
+\u2014-+\u2014\u2014+\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014+\u2014\u2014+\u2014\u2014-+\u2014\u2014\u2014\u2014\u2014\u2014+
\u53e6\u5916\uff0c
\u7ba1\u7406\u6743\u9650\uff08\u5982 super\uff0c process\uff0c file\u7b49\uff09\u4e0d\u80fd\u591f\u6307\u5b9a\u67d0\u4e2a\u6570\u636e\u5e93\uff0con\u540e\u9762\u5fc5\u987b\u8ddf*.*
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的权限有哪些
一．权限表
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各种权限（共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 的权限列表。
权限列表大体分为服务级别和表级别，列级别以及大而广的角色（也是MySQL 8.0 新增）存储程序等权限。我们看到有一个特殊的 SUPER 权限，可以做好多个操作。比如 SET 变量，在从机重新指定相关主机信息以及清理二进制日志等。那这里可以看到，SUPER 有点太过强大，导致了仅仅想实现子权限变得十分困难，比如用户只能 SET 变量，其他的都不想要。那么 MySQL 8.0 之前没法实现，权限的细分不够明确，容易让非法用户钻空子。
那么 MySQL 8.0 把权限细分为静态权限和动态权限，下面我画了两张详细的区分图，图 1 为静态权限，图 2 为动态权限。
图 1- MySQL 静态权限的权限管理图
图 2-动态权限图
那我们看到其实动态权限就是对 SUPER 权限的细分。 SUPER 权限在未来将会被废弃掉。
我们来看个简单的例子，
比如， 用户 'ytt2@localhost', 有 SUPER 权限。
mysql> show grants for ytt2@'localhost';+---------------------------------------------------------------------------------+| Grants for ytt2@localhost |+---------------------------------------------------------------------------------+| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER, SUPER ON *.* TO ytt2@localhost |+---------------------------------------------------------------------------------+1 row in set (0.00 sec)
但是现在我只想这个用户有 SUPER 的子集，设置变量的权限。那么单独给这个用户赋予两个能设置系统变量的动态权限，完了把 SUPER 给拿掉。
mysql> grant session_variables_admin,system_variables_admin on *.* to ytt2@'localhost';Query OK, 0 rows affected (0.03 sec)mysql> revoke super on *.* from ytt2@'localhost';Query OK, 0 rows affected, 1 warning (0.02 sec)
我们看到这个 WARNINGS 提示 SUPER 已经废弃了。
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)`
mysql> show grants for ytt2@'localhost';
+-----------------------------------------------------------------------------------+
| Grants for ytt2@localhost |
+-----------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, ALTER ON *.* TO ytt2@localhost |
| GRANT SESSION_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO ytt2@localhost |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
当然图 2 上还有其它的动态权限，这里就不做特别说明了。
绛旓細浜岋紟MySQL鍚勭鏉冮檺锛堝叡27涓級锛堜互涓嬫搷浣滈兘鏄互root韬唤鐧婚檰杩涜grant鎺堟潈锛屼互p1@localhost韬唤鐧婚檰鎵ц鍚勭鍛戒护銆傦級1. usage 杩炴帴锛堢櫥闄嗭級鏉冮檺锛屽缓绔嬩竴涓敤鎴凤紝灏变細鑷姩鎺堜簣鍏秛sage鏉冮檺锛堥粯璁ゆ巿浜堬級銆mysql> grant usage on *.* to 鈥榩1鈥睝鈥檒ocalhost鈥 identified by 鈥123鈥;璇ユ潈闄愬彧鑳界敤浜庢暟鎹簱鐧婚檰...
绛旓細1銆鏉冮檺瀛樺偍浣嶇疆 鏉冮檺瀛樺偍鍦mysql搴撶殑user,db, tables_priv, columns_priv, andprocs_priv杩欏嚑涓郴缁熻〃涓紝寰MySQL瀹炰緥鍚姩鍚庡氨鍔犺浇鍒板唴瀛樹腑 User琛細瀛樻斁鐢ㄦ埛璐︽埛淇℃伅浠ュ強鍏ㄥ眬绾у埆锛堟墍鏈夋暟鎹簱锛夋潈闄愶紝鍐冲畾浜嗘潵鑷摢浜涗富鏈鐨勫摢浜鐢ㄦ埛鍙互璁块棶鏁版嵁搴撳疄渚嬶紝濡傛灉鏈夊叏灞鏉冮檺鍒欐剰鍛崇潃瀵规墍鏈夋暟鎹簱閮芥湁姝ゆ潈闄 Db...
绛旓細locktables鍜宻elect鏉冮檺銆mysql閿佹槸璁＄畻鏈虹敤浠ュ崗璋冨涓繘绋嬮棿骞跺彂璁块棶鍚屼竴鍏变韩璧勬簮鐨勪竴绉嶆満鍒讹紝鐢ㄦ埛闇瑕佹嫢鏈塴ocktables鍜宻elect鏉冮檺鎵嶅彲浠ャ
绛旓細tables_priv琛ㄦ寚瀹氳〃绾ф潈闄愶紝鍦ㄨ繖閲屾寚瀹氱殑涓涓潈闄愰傜敤浜庝竴涓〃鐨勬墍鏈夊垪銆俢olumns_priv琛 columns_priv琛ㄦ寚瀹氬垪绾ф潈闄愩傝繖閲屾寚瀹鐨勬潈闄閫傜敤浜庝竴涓〃鐨勭壒瀹氬垪銆傛敞锛氬浜嶨RANT USAGE ON锛屾煡鐪嬫墜鍐屾湁濡備笅浠嬬粛鍜屽疄渚嬶細mysql> GRANT USAGE ON *.* TO 鈥榸hangkh鈥橜'localhost鈥;涓涓处鎴锋湁鐢ㄦ埛鍚峼hangkh锛...
绛旓細Mysql鐨鏈楂樼敤鎴峰叿鏈夋墍鏈鏉冮檺锛鍖呮嫭锛(鏇村璇︽儏璇峰弬鑰mysql鐨鐢ㄦ埛鎵嬪唽,娴忚鍦板潃锛 http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/)CREATECreate_privdatabases,tables,orindexes DROPDrop_privdatabasesortables GRANTOPTIONGrant_privdatabases,tables,orstoredroutines REFERENCESReferences_privdata...
绛旓細select,insert,update,delete,create,drop,references,index,alter,create temporary tables,lock tables,execute,create view,show view,create routine,alter routine,event,trigger
绛旓細user銆傚洜涓簎ser琛ㄧ殑璁捐纭繚浜嗗畨鍏ㄦэ紝鍙湁鍏锋湁鏉冮檺鐨勭敤鎴锋墠鑳芥洿鏀硅琛ㄤ腑鐨勬暟鎹紝鍦MySQL涓锛屽瓨鍌ㄧ敤鎴峰叏灞鏉冮檺鐨勮〃鍚嶄负user锛寀ser鏄疢ySQL绯荤粺涓殑鏍稿績琛ㄤ箣涓锛岃褰曚簡鎵鏈夌敤鎴风殑淇℃伅浠ュ強浠栦滑鎵鎷ユ湁鐨勬潈闄锛屾墍浠mysql涓瀛樺偍鐢ㄦ埛鍏ㄥ眬鏉冮檺鐨勮〃鏄痷ser銆
绛旓細鐗瑰埆鐨勬潈闄: ALL: 鍏佽鍋氫换浣曚簨(鍜宺oot涓鏍)銆 USAGE: 鍙厑璁哥櫥褰曗撳叾瀹冧粈涔堜篃涓嶅厑璁稿仛銆 MySQL鏁版嵁搴撳鍏ユ柟娉: MySQL鏁版嵁搴撶殑瀵煎叆,鏈変袱绉嶆柟娉: 1) 鍏堝鍑烘暟鎹簱SQL鑴氭湰,鍐嶅鍏; 2) 鐩存帴鎷疯礉鏁版嵁搴撶洰褰曞拰鏂囦欢銆 鍦ㄤ笉鍚屾搷浣滅郴缁熸垨MySQL鐗堟湰鎯呭喌涓,鐩存帴鎷疯礉鏂囦欢鐨勬柟娉曞彲鑳戒細鏈変笉鍏煎鐨勬儏鍐靛彂鐢熴 鎵浠ヤ竴鑸帹鑽愮敤...
绛旓細濡備綍鍒涘缓MySQL鐢ㄦ埛骞舵巿浜鏉冮檺锛熶负浜嗗疄鐜拌壇濂界殑瀹夊叏鎬э紝闇瑕佷负姣忎釜搴旂敤绋嬪簭鍒涘缓鍗曠嫭鐨勭敤鎴峰笎鎴凤紝鑰屼笉鏄痳oot鐢ㄦ埛璁块棶鏁版嵁搴撱傝繖灏嗙‘淇濆簲鐢ㄧ▼搴忔棤娉曡闂叾浠栧簲鐢ㄧ▼搴忕殑鏁版嵁搴撱傚洜姝ら渶瑕mysql绠＄悊鍛橈紙root锛夋潈闄愭潵鍒涘缓鐢ㄦ埛甯愭埛骞朵负鏁版嵁搴撳垎閰嶆潈闄愩傚浜庝俊鎭紝MySQL root甯愭埛涓庣郴缁焤oot甯愭埛涓嶅悓锛屽畠浠箣闂存病鏈変换浣曞叧绯汇...
绛旓細2.濡傛灉宸茬粡瀹夎浜唒hpmyadmin锛屾墦寮127.0.0.1/phpmyadmin锛屽湪鑿滃崟鏍忔壘鍒扮敤鎴凤紝鐒跺悗鎵惧埌娌℃湁鏉冮檺鐨勭敤鎴凤紝鐐瑰嚮缂栬緫鏉冮檺锛3.璺宠浆鍒拌缃〉闈紝鎵惧埌鍏ㄥ眬鏉冮檺锛屽湪鍏ㄩ塩heckbox鎵撳嬀锛岀偣鍑诲彸涓嬭鐨勬墽琛岋紝瀹屾垚鍚庤鐢ㄦ埛灏辫幏寰椾簡mysql鏁版嵁搴撶殑鎵鏈夋潈闄愶紱4.鍐嶆鐢ㄨ璐﹀彿鍐嶆閾炬帴mysql锛岄摼鎺ユ垚鍔熷悗鎵ц锛歴how databases锛涗綘...
