鏂囩珷鐩綍
鉁ㄤ竴 鍒涘缓鐢ㄦ埛
鉁ㄤ簩 鎺堟潈
鉁ㄦ巿鏉冨師鍒欒鏄?/p>
鉁ㄤ簩 鏌ョ湅鐢ㄦ埛鏉冮檺
鉁?鍥?璁剧疆鍜屾洿鏀圭敤鎴峰瘑鐮?/p>
鉁?浜?鎾ら攢鐢ㄦ埛鏉冮檺
鉁?鍏?鍒犻櫎鐢ㄦ埛
attention锛歁ySQL8.0涓棤娉曞湪鍒涘缓鐢ㄦ埛鏃讹紝鐩存帴鎺堜簣鏉冮檺銆?/p>
蹇呴』鍏堝垱寤虹敤鎴凤紝鍐嶇粰涓庢潈闄?/p>
鉁ㄤ竴 鍒涘缓鐢ㄦ埛
聽 聽 1銆丆REATE USER 'username'@'host' IDENTIFIED BY 'password';
璇存槑锛歶sername锛?鍒涘缓鐨勭敤鎴峰悕
host锛?鎸囧畾璇ョ敤鎴峰湪鍝釜涓绘満涓婂彲浠ョ櫥闄嗭紝濡傛灉鏄湰鍦扮敤鎴峰彲鐢╨ocalhost锛屽鏋滄兂璁╄鐢ㄦ埛鍙互浠庝换鎰忚繙绋嬩富鏈虹櫥闄嗭紝鍙互浣跨敤閫氶厤绗?
鈥?鈥?锛氬摢鍙颁富鏈轰笂鍙互鐧诲綍mysql锛?鏄€氶厤绗︽寚鐨勬槸浠绘剰IP锛屼篃鍙互鎸囧畾鍏蜂綋鐨処P锛屾垨鑰卨ocalhost浠ｈ〃鏈満鎵嶅彲浠ョ櫥褰曘€?/p>
password锛?璇ョ敤鎴风殑鐧婚檰瀵嗙爜锛屽瘑鐮佸彲浠ヤ负绌猴紝濡傛灉涓虹┖鍒欒鐢ㄦ埛鍙互涓嶉渶瑕佸瘑鐮佺櫥闄嗘湇鍔″櫒渚嬪瓙锛?/p>
1銆丆REATE USER 'niuben'@'localhost' IDENTIFIED BY '123456';
2銆丆REATE USER 'niuben'@'192.168.1.101_' IDENDIFIED BY '123456';
3銆丆REATE USER 'niuben'@'%' IDENTIFIED BY '123456';
4銆丆REATE USER 'niuben'@'%' IDENTIFIED BY '';
5銆丆REATE USER 'niuben'@'%';
渚嬪锛?/p>
mysql> create user 'u1'@'%' identified by 'dir99';
Query OK, 0 rows affected (0.01 sec)
鉁ㄤ簩 鎺堟潈
GRANT privileges ON databasename.tablename TO 'username'@'host' [with grant option];
grant鍛戒护璇存槑锛?/p>
鈥?馃挦priveleges (鏉冮檺鍒楄〃)锛屽彲浠ユ槸all锛岃〃饨版墍鏈夋潈闄愶紝涔熷彲浠ユ槸select銆乽pdate绛夋潈闄愶紝澶氫釜鏉冮檺涔嬮棿饨ら€楀彿鍒嗗紑銆?/p>
鈥?馃挦ON 饨ゆ潵鎸囧畾鏉冮檺閽堝鍝簺搴撳拰琛紝鏍煎紡涓烘暟鎹簱.琛ㄥ悕 锛岀偣鍙峰墠饩饯鏉ユ寚瀹氭暟鎹簱鍚嶏紝鐐瑰彿鍚庘警饨ゆ潵鎸囧畾琛ㄥ悕锛? 琛ㄢ桨鎵€鏈夋暟鎹簱鎵€鏈夎〃銆?/p>
鈥?馃挦TO 琛ㄢ桨灏嗘潈闄愯祴浜堟煇涓饯鎴? 鏍煎紡涓簎sername@host锛孈鍓嶁警涓衡饯鎴峰悕锛孈鍚庘警鎺ラ檺鍒剁殑涓绘満锛屽彲浠ユ槸IP銆両P娈点€佸煙鍚嶄互鍙?锛?琛ㄢ桨浠讳綍鍦扳絽銆?/p>
鈥?馃挦WITH GRANT OPTION 杩欎釜閫夐」琛ㄢ桨璇モ饯鎴峰彲浠ュ皢饩冣及鎷ユ湁鐨勬潈闄愭巿鏉冪粰鍒紙銆傛敞鎰忥細缁忓父鏈夆紙鍦ㄥ垱寤烘搷浣溾饯鎴风殑鏃跺€欎笉鎸囧畾WITH GRANT OPTION閫夐」瀵艰嚧鍚庢潵璇モ饯鎴蜂笉鑳戒娇饨RANT鍛戒护鍒涘缓饨ゆ埛鎴栬€呯粰鍏跺畠饨ゆ埛鎺堟潈銆?澶囨敞锛氬彲浠ヤ娇饨RANT閲嶅缁欌饯鎴锋坊鍔犳潈闄愶紝鏉冮檺鍙犲姞锛屸綈濡備綘鍏堢粰饨ゆ埛娣诲姞饧€涓猻elect鏉冮檺锛岀劧鍚庡張缁欌饯鎴锋坊鍔犫紑涓猧nsert鏉冮檺锛岄偅涔堣饨ゆ埛灏卞悓鏃舵嫢鏈変簡select鍜宨nsert鏉冮檺銆?/p>
GRANT SELECT, INSERT ON test.user TO 'u2'@'%';
GRANT ALL ON *.* TO 'u3'@'%';
GRANT ALL ON mysc0530.* TO 'u4'@'%';
渚嬪锛?/p>
涓嶈鑼冪殑璇硶锛?/p>
mysql> grant privileges on mysc0530.* to u1;
ERROR 3619 (HY000): Illegal privilege level specified for PRIVILEGES
瑙勮寖鐨勮娉曪細
mysql> grant all privileges on mysc0530.* to u1;
Query OK, 0 rows affected (0.01 sec)
娉ㄦ剰锛?/p>
鐢ㄤ互涓婂懡浠ゆ巿鏉冪殑鐢ㄦ埛涓嶈兘缁欏叾瀹冪敤鎴锋巿鏉冿紝濡傛灉鎯宠璇ョ敤鎴峰彲浠ユ巿鏉冿紝鐢ㄤ互涓嬪懡浠?
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
mysql> grant all privileges on mysc0530.* to u1 with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'u3'@'%' identified by 'dir99';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on mysc0530.* to 'u3'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql>
鉁?鎺堟潈鍘熷垯璇存槑
鈥?鍙巿浜堣兘婊♀緶闇€瑕佺殑鏈€饧╂潈闄愶紝闃测綄饨ゆ埛饧插潖浜嬶紝饨愬饨ゆ埛鍙槸闇€瑕佹煡璇紝閭ｅ氨鍙粰
select鏉冮檺灏卞彲浠ヤ簡锛屼笉瑕佺粰饨ゆ埛璧嬩簣update銆乮nsert鎴栬€卍elete鏉冮檺
鈥?鍒涘缓饨ゆ埛鐨勬椂鍊欓檺鍒垛饯鎴风殑鐧诲綍涓绘満锛屸紑鑸槸闄愬埗鎴愭寚瀹欼P鎴栬€呭唴饨笽P娈?/p>
鈥?鍒濆鍖栨暟鎹簱鐨勬椂鍊欏垹闄ゆ病鏈夊瘑鐮佺殑饨ゆ埛锛屽畨瑁呭畬鏁版嵁搴撶殑鏃跺€欎細饩冨姩鍒涘缓饧€浜涒饯鎴凤紝杩欎簺饨ゆ埛榛樿娌℃湁瀵嗙爜
鈥?涓烘瘡涓饯鎴疯缃弧饩滃瘑鐮佸鏉傚害鐨勫瘑鐮?/p>
鈥?瀹氭湡娓呯悊涓嶉渶瑕佺殑饨ゆ埛锛屽洖鏀舵潈闄愭垨鑰呭垹闄も饯鎴?/p>
鉁ㄤ簩 鏌ョ湅鐢ㄦ埛鏉冮檺
show grants for 'root'@'localhost';
鏄剧ず鏉冮檺锛?/p>
mysql> show grants for u1;
+--------------------------------------------------+
| Grants for u1@%聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 聽 |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`聽 聽 聽 聽 聽 聽 聽 聽 聽 |
| GRANT ALL PRIVILEGES ON `mysc0530`.* TO `u1`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
show grants;
鏌ョ湅褰撳墠饨ゆ埛鐨勬潈闄?/p>
SHOW GRANTS;
GRANT SELECT, INSERT, 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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
鉁?鍥?璁剧疆鍜屾洿鏀圭敤鎴峰瘑鐮?/h2>
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
鏂规硶涓€锛氱洿鎺ユ洿鏂?/p>
alter user '鐢ㄦ埛鍚?@'涓绘満鑼冨洿' identified by '鏂板瘑鐮?;
娴嬭瘯濡備笅锛?/p>
mysql> revoke all privileges on mysc0530.* from 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)
鏂规硶浜岋細閫氳繃淇敼mysql.user琛ㄤ慨鏀瑰瘑鐮?/p>
mysql8.0涓病鏈塸assword鍔犲瘑鍑芥暟锛屽彲浠ョ敤sha\sha1鏉ヨ繘琛屽瘑鐮佺殑鍔犲瘑
use mysql;
update user set authentication_string = sha1('321') where user =
'test1' and host = '%';
flush privileges;
缁忔祴璇曪紝娌℃瘺鐥咃細
mysql> UPDATE user SET authentication_string=sha1("dir999") WHERE user='u1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1聽 Changed: 1聽 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
鉁?浜?鎾ら攢鐢ㄦ埛鏉冮檺
REVOKE privileges ON databasename.tablename FROM 'username'@'host';
璇存槑锛歱rivileges锛氱敤鎴风殑鎿嶄綔鏉冮檺锛屽SELECT锛孖NSERT锛孶PDATE绛夛紝濡傛灉瑕佹巿浜堟墍鐨勬潈闄愬垯浣跨敤ALL銆?/p>
濡傛灉瑕佹巿浜堣鐢ㄦ埛瀵规墍鏈夋暟鎹簱鍜岃〃鐨勭浉搴旀搷浣滄潈闄愬垯鍙敤琛ㄧず锛屽.*
渚嬪锛?/p>
mysql> alter user 'u1'@'%' identified by 'dir999';
Query OK, 0 rows affected (0.01 sec)
鉁?鍏?鍒犻櫎鐢ㄦ埛
鏂规硶涓€锛?/p>
drop user if exists username;
drop鐨勨絽寮忓垹闄も饯鎴蜂箣鍚庯紝饨ゆ埛涓嬫鐧诲綍灏变細璧锋晥銆?/p>
鏂规硶浜岋細
delete from user where user='饨ゆ埛鍚? and host='涓绘満';
flush privileges;
娉ㄦ剰閫氳繃琛ㄧ殑饨呭紡鍒犻櫎鐨勶紝闇€瑕佽皟饨lush privileges;鍒锋柊鏉冮檺淇℃伅锛堟潈闄愬惎鍔ㄧ殑鏃跺€欏湪鍐呭瓨涓繚瀛樼潃锛岄€氳繃琛ㄧ殑饨呭紡淇敼涔嬪悗闇€瑕佸埛鏂扳紑涓嬶級銆?/p>
娴嬭瘯濡備笅锛?/p>
mysql> delete from user where user='u3';
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)