grant æé on æ°æ®åºå¯¹è±¡ to ç¨æ·
ä¸ãgrant æ®éæ°æ®ç¨æ·ï¼æ¥è¯¢ãæå
¥ãæ´æ°ãå é¤ æ°æ®åºä¸ææ表æ°æ®çæå©ã
grant select on testdb.* to common_user@â%â
grant insert on testdb.* to common_user@â%â
grant update on testdb.* to common_user@â%â
grant delete on testdb.* to common_user@'%'
æè
grant select, insert, update, delete on testdb.* to common_user@â%â
äºãgrant æ°æ®åºå¼å人åï¼å建表ãç´¢å¼ãè§å¾ãåå¨è¿ç¨ãå½æ°ãããçæéã
grant å建ãä¿®æ¹ãå é¤ MySQL æ°æ®è¡¨ç»ææéã
grant create on testdb.* to developer@â192.168.0.%â;
grant alter on testdb.* to developer@â192.168.0.%â;
grant drop on testdb.* to developer@â192.168.0.%â;
grant æä½ MySQL å¤é®æéã
grant references on testdb.* to developer@â192.168.0.%â;
grant æä½ MySQL 临æ¶è¡¨æéã
grant create temporary tables on testdb.* to developer@â192.168.0.%â;
grant æä½ MySQL ç´¢å¼æéã
grant index on testdb.* to developer@â192.168.0.%â;
grant æä½ MySQL è§å¾ãæ¥çè§å¾æºä»£ç æéã
grant create view on testdb.* to developer@â192.168.0.%â;
grant show view on testdb.* to developer@â192.168.0.%â;
grant æä½ MySQL åå¨è¿ç¨ãå½æ° æéã
grant create routine on testdb.* to developer@â192.168.0.%â; â now, can show procedure status
grant alter routine on testdb.* to developer@â192.168.0.%â; â now, you can drop a procedure
grant execute on testdb.* to developer@â192.168.0.%â;
ä¸ãgrant æ®é DBA 管çæ个 MySQL æ°æ®åºçæéã
grant all privileges on testdb to dba@âlocalhostâ
å
¶ä¸ï¼å
³é®å âprivilegesâ å¯ä»¥çç¥ã
åãgrant é«çº§ DBA 管ç MySQL ä¸æææ°æ®åºçæéã
grant all on *.* to dba@âlocalhostâ
äºãMySQL grant æéï¼åå«å¯ä»¥ä½ç¨å¨å¤ä¸ªå±æ¬¡ä¸ã
1. grant ä½ç¨å¨æ´ä¸ª MySQL æå¡å¨ä¸ï¼
grant select on *.* to dba@localhost; â dba å¯ä»¥æ¥è¯¢ MySQL ä¸æææ°æ®åºä¸ç表ã
grant all on *.* to dba@localhost; â dba å¯ä»¥ç®¡ç MySQL ä¸çæææ°æ®åº
2. grant ä½ç¨å¨å个æ°æ®åºä¸ï¼
grant select on testdb.* to dba@localhost; â dba å¯ä»¥æ¥è¯¢ testdb ä¸ç表ã
3. grant ä½ç¨å¨å个æ°æ®è¡¨ä¸ï¼
grant select, insert, update, delete on testdb.orders to dba@localhost;
è¿éå¨ç»ä¸ä¸ªç¨æ·ææå¤å¼ 表æ¶ï¼å¯ä»¥å¤æ¬¡æ§è¡ä»¥ä¸è¯å¥ãä¾å¦ï¼
grant select(user_id,username) on smp.users to mo_user@â%â identified by â123345â²;
grant select on smp.mo_sms to mo_user@â%â identified by â123345â²;
4. grant ä½ç¨å¨è¡¨ä¸çåä¸ï¼
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. grant ä½ç¨å¨åå¨è¿ç¨ãå½æ°ä¸ï¼
grant execute on procedure testdb.pr_add to âdbaâ@'localhostâ
grant execute on function testdb.fn_add to âdbaâ@'localhostâ
å
ãæ¥ç MySQL ç¨æ·æé
æ¥çå½åç¨æ·ï¼èªå·±ï¼æéï¼
show grants;
æ¥çå
¶ä» MySQL ç¨æ·æéï¼
show grants for zhangkh@localhost;
ä¸ãæ¤éå·²ç»èµäºç» MySQL ç¨æ·æéçæéã
revoke è· grant çè¯æ³å·®ä¸å¤ï¼åªéè¦æå
³é®å âtoâ æ¢æ âfromâ å³å¯ï¼
grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;
å
«ãMySQL grantãrevoke ç¨æ·æé注æäºé¡¹
1. grant, revoke ç¨æ·æéåï¼è¯¥ç¨æ·åªæéæ°è¿æ¥ MySQL æ°æ®åºï¼æéæè½çæã
2. å¦ææ³è®©ææçç¨æ·ï¼ä¹å¯ä»¥å°è¿äºæé grant ç»å
¶ä»ç¨æ·ï¼éè¦é项 âgrant optionâ
grant select on testdb.* to dba@localhost with grant option;
è¿ä¸ªç¹æ§ä¸è¬ç¨ä¸å°ãå®é
ä¸ï¼æ°æ®åºæéæå¥½ç± DBA æ¥ç»ä¸ç®¡çã
mysqlææ表å
±æ5个表ï¼userãdbãhostãtables_privåcolumns_privã
ææ表çå
容æå¦ä¸ç¨éï¼
user表
user表ååºå¯ä»¥è¿æ¥æå¡å¨çç¨æ·åå
¶å£ä»¤ï¼å¹¶ä¸å®æå®ä»ä»¬æåªç§å
¨å±ï¼è¶
级ç¨æ·ï¼æéãå¨user表å¯ç¨çä»»ä½æéåæ¯å
¨å±æéï¼å¹¶éç¨äºæææ°æ®åºãä¾å¦ï¼å¦æä½ å¯ç¨äºDELETEæéï¼å¨è¿éååºçç¨æ·å¯ä»¥ä»ä»»ä½è¡¨ä¸å é¤è®°å½ï¼æ以å¨ä½ è¿æ ·åä¹åè¦è®¤çèèã
db表
db表ååºæ°æ®åºï¼èç¨æ·ææé访é®å®ä»¬ãå¨è¿éæå®çæééç¨äºä¸ä¸ªæ°æ®åºä¸çææ表ã
host表
host表ä¸db表ç»å使ç¨å¨ä¸ä¸ªè¾å¥½å±æ¬¡ä¸æ§å¶ç¹å®ä¸»æºå¯¹æ°æ®åºç访é®æéï¼è¿å¯è½æ¯åç¬ä½¿ç¨db好äºãè¿ä¸ªè¡¨ä¸åGRANTåREVOKEè¯å¥çå½±åï¼æ以ï¼ä½ å¯è½åè§ä½ æ ¹æ¬ä¸æ¯ç¨å®ã
tables_priv表
tables_priv表æå®è¡¨çº§æéï¼å¨è¿éæå®çä¸ä¸ªæééç¨äºä¸ä¸ªè¡¨çææåã
columns_priv表
columns_priv表æå®å级æéãè¿éæå®çæééç¨äºä¸ä¸ªè¡¨çç¹å®åã
注ï¼
对äºGRANT USAGE ONï¼æ¥çæåæå¦ä¸ä»ç»åå®ä¾ï¼
mysql> GRANT USAGE ON *.* TO âzhangkhâ@'localhostâ;
ä¸ä¸ªè´¦æ·æç¨æ·åzhangkhï¼æ²¡æå¯ç ã该账æ·åªç¨äºä»æ¬æºè¿æ¥ãæªæäºæéãéè¿GRANTè¯å¥ä¸çUSAGEæéï¼ä½ å¯ä»¥å建账æ·èä¸æäºä»»ä½æéãå®å¯ä»¥å°ææå
¨å±æé设为âN'ãåå®ä½ å°å¨ä»¥åå°å
·ä½æéæäºè¯¥è´¦æ·ã
温馨提示:答案为网友推荐,仅供参考