.jpg)
這篇文章主要介紹了mysql5.7創(chuàng)建用戶授權(quán)mysql用戶撤銷授權(quán)的方法,非常不錯，具有參考借鑒價值，需要的朋友可以參考下
一, 創(chuàng)建用戶:
?????? 命令:
CREATE?USER?'username'@'host'?IDENTIFIED?BY?'password';
?????? 說明:username – 你將創(chuàng)建的用戶名, host – 指定該用戶在哪個主機(jī)上可以登陸,如果是本地用戶可用localhost, 如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸,可以使用mysql%. password – 該用戶的登陸密碼,密碼可以為空,如果為空則該用戶可以不需要密碼登陸服務(wù)器.
????? 例子:
?CREATE?USER?'dog'@'localhost'?IDENTIFIED?BY?'password'; ????CREATE?USER?'pig'@'192.168.1.100'?IDENDIFIED?BY?'password'; ????CREATE?USER?'pig'@'192.168.1.%'?IDENDIFIED?BY?'password'; ????CREATE?USER?'pig'@'%'?IDENTIFIED?BY?'password'; ????CREATE?USER?'pig'@'%'?IDENTIFIED?BY?''; ????CREATE?USER?'pig'@'%';
二,授權(quán):
????? 命令:
GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'
???? 說明: privileges – 用戶的操作權(quán)限,如SELECT , INSERT , UPmysql 等(詳細(xì)列表見該文最后面).如果要授予所的權(quán)限則使用mysql.;databasename – 數(shù)據(jù)庫名,tablename-表名,如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用*表示, 如*.*.
????? 例子:
GRANT?SELECT,?INSERT?ON?test.user?TO?'pig'@'%'; ????GRANT?ALL?ON?*.*?TO?'pig'@'%';
???? 注意:用以上命令授權(quán)的用戶不能給其它用戶授權(quán),如果想讓該用戶可以授權(quán),用以下命令:? ? ? ? ??
GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'?WITH?GRANT?OPTION;
權(quán)限信息用user、db、host、tables_priv和columns_priv表被存儲在mysql中(即在名為mysql的數(shù)據(jù)庫中)。
權(quán)限 列 Context
select Select_priv 表
insert Insert_priv 表
update Update_priv 表
delete Delete_priv 表
index Index_priv 表
alter Alter_priv 表
create Create_priv 數(shù)據(jù)庫、表或索引
drop Drop_priv 數(shù)據(jù)庫或表
grant Grant_priv 數(shù)據(jù)庫或表
references References_priv 數(shù)據(jù)庫或表
reload Reload_priv 服務(wù)器管理
shutdown Shutdown_priv 服務(wù)器管理
process Process_priv 服務(wù)器管理
mysql File_priv 在服務(wù)器上的文件存取
三.設(shè)置與更改用戶密碼
???? 命令:
SET?PASSWORD?FOR?'username'@'host'?=?PASSWORD('newpassword');如果是當(dāng)前登陸用戶用 SET?PASSWORD?=?PASSWORD("newpassword");
????? 例子:
SET?PASSWORD?FOR?'pig'@'%'?=?PASSWORD("123456");
四.撤銷用戶權(quán)限
????? 命令:
REVOKE?privilege?ON?databasename.tablename?FROM?'username'@'host';
???? 說明: privilege, databasename, tablename – 同授權(quán)部分.
????? 例子:
REVOKE?SELECT?ON?*.*?FROM?'pig'@'%';
????? 注意: 假如你在給用戶’pig’@’%’授權(quán)的時候是這樣的(或類似的):GRANT SELECT ON test.user TO ‘pig’@’%’, 則在使用REVOKE SELECT ON *.* FROM ‘pig’@’%’;命令并不能撤銷該用戶對test數(shù)據(jù)庫中user表的SELECT 操作.相反,如果授權(quán)使用的是GRANT SELECT ON *.* TO ‘pig’@’%’;則REVOKE SELECT ON test.user FROM ‘pig’@’%’;命令也不能撤銷該用戶對test數(shù)據(jù)庫中user表的Select 權(quán)限.
????? 具體信息可以用命令SHOW GRANTS FOR ‘pig’@’%’; 查看.
五.刪除用戶
????? 命令:
DROP?USER?'username'@'host';
六 查看用戶的授權(quán)?
mysql>?show?grants?for?'test01'@'localhost'; +--------------------------------------------------------------------------------------------------------------------------------------------+ |?Grants?for?test01@localhost??????????????????????????????????????????????????| +--------------------------------------------------------------------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?'test01'@'localhost'??????????????????????????????????????????????| |?GRANT?INSERT,?UPDATE,?DELETE,?CREATE,?DROP,?REFERENCES,?INDEX,?ALTER,?CREATE?TEMPORARY?TABLES,?LOCK?TABLES,? EXECUTE,?CREATE?VIEW,?SHOW?VIEW,?CREATE?ROUTINE,?ALTER?ROUTINE,?EVENT,?TRIGGER?ON?`test001`.*?TO?'test01'@'localhost'?| +--------------------------------------------------------------------------------------------------------------------------------------------+ 2?rows?in?set?(0.01?sec) mysql>?show?grants?for?'test02'@'localhost';? +-------------------------------------------------------------+ |?Grants?for?test02@localhost?????????| +-------------------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?'test02'@'localhost'?????| |?GRANT?ALL?PRIVILEGES?ON?`test001`.*?TO?'test02'@'localhost'?| +-------------------------------------------------------------+ 2?rows?in?set?(0.00?sec)
.png)
