æ°å»ºç¨æ·
CREATE USER
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
CREATE USERç¨äºå建æ°çMySQLè´¦æ·ãè¦ä½¿ç¨CREATE USERï¼æ¨å¿
é¡»æ¥æmysqlæ°æ®åºçå
¨å±CREATE USERæéï¼ææ¥æINSERTæéã对äºæ¯ä¸ªè´¦æ·ï¼CREATE USERä¼å¨æ²¡ææéçmysql.user表ä¸å建ä¸ä¸ªæ°è®°å½ãå¦æ è´¦æ·å·²ç»åå¨ï¼ååºç°é误ã
使ç¨èªéçIDENTIFIED BYåå¥ï¼å¯ä»¥ä¸ºè´¦æ·ç»å®ä¸ä¸ªå¯ç ãuserå¼å å¯ç çç»å®æ¹æ³åGRANTè¯å¥ä¸æ ·ãç¹å«æ¯ï¼è¦å¨çº¯ææ¬ä¸æå®å¯ç ï¼é忽ç¥PASSWORDå
³é®è¯ãè¦æ å¯ç æå®ä¸ºç±PASSWORD()å½æ°è¿åçæ··ç¼å¼ï¼éå
å«å
³é®åPASSWORDã
å¦å¤ï¼
å¯ä»¥ç¨ä¸¤ç§æ¹å¼å建MySQLè´¦æ·ï¼
· 使ç¨GRANTè¯å¥
· ç´æ¥æä½MySQLææ表
æ好çæ¹æ³æ¯ä½¿ç¨GRANTè¯å¥ï¼å 为è¿æ ·æ´ç²¾ç¡®ï¼é误å°ãä»MySQL 3.22.11èµ·æä¾äºGRANTï¼
å建账æ·çå
¶å®æ¹æ³æ¯ä½¿ç¨MySQLè´¦æ·ç®¡çåè½ç第ä¸æ¹ç¨åºãphpMyAdminå³æ¯ä¸ä¸ªç¨åºã
ä¸é¢ç示ä¾è¯´æå¦ä½ä½¿ç¨MySQL客æ·ç«¯ç¨åºæ¥è®¾ç½®æ°ç¨æ·ãè¿è¯´æ为äºæ´æ¹ï¼ä½ å¿
须以MySQLrootç¨æ·è¿æ¥MySQLæå¡å¨ï¼å¹¶ä¸rootè´¦æ·å¿
é¡»æmysqlæ°æ®åºçINSERTæéåRELOAD管çæéã
é¦å
ï¼ä½¿ç¨MySQLç¨åºä»¥MySQL rootç¨æ·æ¥è¿æ¥æå¡å¨ï¼
shell> MySQL --user=root MySQL
å¦æä½ ä¸ºrootè´¦æ·æå®äºå¯ç ï¼è¿éè¦ä¸ºè¯¥MySQLå½ä»¤åæ¬èä¸çå
¶å®å½ä»¤æä¾--passwordæ-pé项ã
以rootè¿æ¥å°æå¡å¨ä¸åï¼å¯ä»¥æ·»å æ°è´¦æ·ãä¸é¢çè¯å¥ä½¿ç¨GRANTæ¥è®¾ç½®å个æ°è´¦æ·ï¼
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
ç¨GRANTè¯å¥å建çè´¦æ·æä¸é¢çå±æ§ï¼
· å
¶ä¸ä¸¤ä¸ªè´¦æ·æç¸åçç¨æ·åmontyåå¯ç some_passã两个账æ·å为è¶
级ç¨æ·è´¦æ·ï¼å
·æå®å
¨çæéå¯ä»¥åä»»ä½äºæ
ãä¸ä¸ªè´¦æ· ('monty'@'localhost')åªç¨äºä»æ¬æºè¿æ¥æ¶ãå¦ä¸ä¸ªè´¦æ·('monty'@'%')å¯ç¨äºä»å
¶å®ä¸»æºè¿æ¥ã请注æmontyç两个账æ·å¿
é¡»è½ä»ä»»ä½ä¸»æºä»¥montyè¿æ¥ã没ælocalhostè´¦æ·ï¼å½montyä»æ¬æºè¿æ¥æ¶ï¼mysql_install_dbå建çlocalhostçå¿åç¨æ·è´¦æ·å°å å
ãç»ææ¯ï¼montyå°è¢«è§ä¸ºå¿åç¨æ·ãåå æ¯å¿åç¨æ·è´¦æ·çHoståå¼æ¯'monty'@'%'è´¦æ·æ´å
·ä½ï¼è¿æ ·å¨user表æåºé¡ºåºä¸æå¨åé¢ã
· ä¸ä¸ªè´¦æ·æç¨æ·åadminï¼æ²¡æå¯ç ã该账æ·åªç¨äºä»æ¬æºè¿æ¥ãæäºäºRELOADåPROCESS管çæéãè¿äºæéå
许adminç¨æ·æ§è¡mysqladmin reloadãmysqladmin refreshåmysqladmin flush-xxxå½ä»¤ï¼ä»¥åmysqladmin processlistãæªæäºè®¿é®æ°æ®åºçæéãä½ å¯ä»¥éè¿GRANTè¯å¥æ·»å æ¤ç±»æéã
· ä¸ä¸ªè´¦æ·æç¨æ·ådummyï¼æ²¡æå¯ç ã该账æ·åªç¨äºä»æ¬æºè¿æ¥ãæªæäºæéãéè¿GRANTè¯å¥ä¸çUSAGEæéï¼ä½ å¯ä»¥å建账æ·èä¸æäºä»»ä½æéãå®å¯ä»¥å°ææå
¨å±æé设为'N'ãåå®ä½ å°å¨ä»¥åå°å
·ä½æéæäºè¯¥è´¦æ·ã
é¤äºGRANTï¼ä½ å¯ä»¥ç´æ¥ç¨INSERTè¯å¥å建ç¸åçè´¦æ·ï¼ç¶å使ç¨FLUSH PRIVILEGESåè¯æå¡å¨éè½½ææ表ï¼
shell> mysql --user=root mysql
mysql> INSERT INTO user
-> VALUES('localhost','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
-> VALUES('%','monty',PASSWORD('some_pass'),
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
-> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
å½ä½ ç¨INSERTå建账æ·æ¶ä½¿ç¨FLUSH PRIVILEGESçåå æ¯åè¯æå¡å¨é读ææ表ãå¦åï¼åªæéå¯æå¡å¨åæ´æ¹æ¹ä¼è¢«æ³¨æå°ã使ç¨GRANTï¼åä¸éè¦ä½¿ç¨FLUSH PRIVILEGESã
ç¨INSERT使ç¨PASSWORD()å½æ°æ¯ä¸ºäºå å¯å¯ç ãGRANTè¯å¥ä¸ºä½ å å¯å¯ç ï¼å æ¤ä¸éè¦PASSWORD()ã
'Y'å¼å¯ç¨è´¦æ·æéã对äºadminè´¦æ·ï¼è¿å¯ä»¥ä½¿ç¨æ´å å¯è¯»çINSERTæ©å
çè¯æ³ï¼ä½¿ç¨SETï¼ã
å¨ä¸ºdummyè´¦æ·çINSERTè¯å¥ä¸ï¼åªæuser表ä¸çHostãUseråPasswordåè®°å½ä¸ºæå®çå¼ã没æä¸ä¸ªæéå为æ¾å¼è®¾ç½®ï¼å æ¤MySQLå°å®ä»¬åæå®ä¸ºé»è®¤å¼'N'ãè¿æ ·çåäºGRANT USAGEçæä½ã
请注æè¦è®¾ç½®è¶
级ç¨æ·è´¦æ·ï¼åªéè¦å建ä¸ä¸ªæéå设置为'Y'çuser表æ¡ç®ãuser表æé为å
¨å±æéï¼å æ¤å
¶å®ææ表ä¸åéè¦æ¡ç®ã
ä¸é¢çä¾åå建3个账æ·ï¼å
许å®ä»¬è®¿é®ä¸ç¨æ°æ®åºãæ¯ä¸ªè´¦æ·çç¨æ·å为customï¼å¯ç 为obscureã
è¦æ³ç¨GRANTå建账æ·ï¼ä½¿ç¨ä¸é¢çè¯å¥ï¼
shell> MySQL --user=root MySQL
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';
è¿3个账æ·å¯ä»¥ç¨äºï¼
· 第1个账æ·å¯ä»¥è®¿é®bankaccountæ°æ®åºï¼ä½åªè½ä»æ¬æºè®¿é®ã
· 第2个账æ·å¯ä»¥è®¿é®expensesæ°æ®åºï¼ä½åªè½ä»ä¸»æºwhitehouse.gov访é®ã
· 第3个账æ·å¯ä»¥è®¿é®customeræ°æ®åºï¼ä½åªè½ä»ä¸»æºserver.domain访é®ã
è¦æ³ä¸ç¨GRANT设置customè´¦æ·ï¼ä½¿ç¨INSERTè¯å¥ç´æ¥ä¿®æ¹ææ表ï¼
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('localhost','bankaccount','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('whitehouse.gov','expenses','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
-> (Host,Db,User,Select_priv,Insert_priv,
-> Update_priv,Delete_priv,Create_priv,Drop_priv)
-> VALUES('server.domain','customer','custom',
-> 'Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
å3个INSERTè¯å¥å¨user表ä¸å å
¥æ¡ç®ï¼å
许ç¨æ·customä»åç§ä¸»æºç¨ç»å®çå¯ç è¿è¡è¿æ¥ï¼ä½ä¸æäºå
¨å±æé(æææé设置为é»è®¤å¼'N')ãåé¢3个INSERTè¯å¥å¨user表ä¸å å
¥æ¡ç®ï¼ä¸ºcustomæäºbankaccountãexpensesåcustomeræ°æ®åºæéï¼ä½åªè½ä»åéç主æºè®¿é®ãé常è¥ç´æ¥ä¿®æ¹ææ表ï¼ååºåè¯æå¡å¨ç¨FLUSH PRIVILEGESéè½½ææ表ï¼ä½¿æéæ´æ¹çæã
å¦æä½ æ³è¦è®©æ个ç¨æ·ä»ç»å®åçæææºå¨è®¿é®(ä¾å¦ï¼mydomain.com)ï¼ä½ å¯ä»¥å¨è´¦æ·åç主æºé¨å使ç¨å«â%âéé
符çGRANTè¯å¥ï¼
mysql> GRANT ...
-> ON *.*
-> TO 'myname'@'%.mydomain.com'
-> IDENTIFIED BY 'mypass';
è¦æ³éè¿ç´æ¥ä¿®æ¹ææ表æ¥å®ç°ï¼
mysql> INSERT INTO user (Host,User,Password,...)
-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;
设置账æ·å¯ç
å¯ä»¥ç¨mysqladminå½ä»¤å¨å½ä»¤è¡æå®å¯ç ï¼shell> mysqladmin -u user_name -h host_name password "newpwd"
该å½ä»¤é设å¯ç çè´¦æ·ä¸ºuser表å
å¹é
Useråçuser_nameåHoståä½ åèµ·è¿æ¥ç客æ·ç«¯çè®°å½ã
为账æ·èµäºå¯ç çå¦ä¸ç§æ¹æ³æ¯æ§è¡SET PASSWORDè¯å¥ï¼
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
åªærootçå¯ä»¥æ´æ°mysqlæ°æ®åºçç¨æ·å¯ä»¥æ´æ¹å
¶å®ç¨æ·çå¯ç ãå¦æä½ æ²¡æ以å¿åç¨æ·è¿æ¥ï¼çç¥FORåå¥ä¾¿å¯ä»¥æ´æ¹èªå·±çå¯ç ï¼
mysql> SET PASSWORD = PASSWORD('biscuit');
ä½ è¿å¯ä»¥å¨å
¨å±çº§å«ä½¿ç¨GRANT USAGEè¯å¥(å¨*.*)æ¥æå®æ个账æ·çå¯ç èä¸å½±åè´¦æ·å½åçæéï¼
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
ä¸è¬æ
åµä¸æ好使ç¨ä¸è¿°æ¹æ³æ¥æå®å¯ç ï¼ä½ è¿å¯ä»¥ç´æ¥ä¿®æ¹user表ï¼
· è¦æ³å¨å建æ°è´¦æ·æ¶å»ºç«å¯ç ï¼å¨Passwordåæä¾ä¸ä¸ªå¼ï¼
· shell> mysql -u root mysql
· mysql> INSERT INTO user (Host,User,Password)
· -> VALUES('%','jeffrey',PASSWORD('biscuit'));
· mysql> FLUSH PRIVILEGES;
·
· è¦æ³æ´æ¹å·²æè´¦æ·çå¯ç ï¼ä½¿ç¨UPDATEæ¥è®¾ç½®Passwordåå¼ï¼
· shell> mysql -u root mysql
· mysql> UPDATE user SET Password = PASSWORD('bagel')
· -> WHERE Host = '%' AND User = 'francis';
· mysql> FLUSH PRIVILEGES;
å½ä½ 使ç¨SET PASSWORDãINSERTæUPDATEæå®è´¦æ·çå¯ç æ¶ï¼å¿
é¡»ç¨PASSWORD()å½æ°å¯¹å®è¿è¡å å¯ã(å¯ä¸çç¹ä¾æ¯å¦æå¯ç 为空ï¼ä½ ä¸éè¦ä½¿ç¨PASSWORD())ãéè¦ä½¿ç¨PASSWORD()æ¯å 为user表以å å¯æ¹å¼ä¿åå¯ç ï¼èä¸æ¯ææãå¦æä½ å¿è®°äºï¼ä½ å¯è½ä¼è±¡è¿æ ·è®¾ç½®å¯ç ï¼
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
ç»ææ¯å¯ç 'biscuit'ä¿åå°user表å没æå å¯ãå½jeffrey使ç¨è¯¥å¯ç è¿æ¥æå¡å¨æ¶ï¼å¼è¢«å å¯å¹¶åä¿åå¨user表ä¸çè¿è¡æ¯è¾ãä½æ¯ï¼ä¿åçå¼ä¸ºå符串'biscuit'ï¼å æ¤æ¯è¾å°å¤±è´¥ï¼æå¡å¨æç»è¿æ¥ï¼
shell> mysql -u jeffrey -pbiscuit test
Access denied
å¦æä½ ä½¿ç¨GRANT ... IDENTIFIED BYè¯å¥æmysqladmin passwordå½ä»¤è®¾ç½®å¯ç ï¼å®ä»¬åä¼å å¯å¯ç ãå¨è¿ç§æ
åµä¸ï¼ä¸éè¦ä½¿ç¨PASSWORD()å½æ°ã
2ã建åº
mysql>create database ****;
3ãåæé
GRANT ALL PRIVILEGES ON åºå.* TO 'ç¨æ·å'@'%';
温馨提示:答案为网友推荐,仅供参考