注:以下數(shù)據(jù)庫用戶以及權限操作,只有再刷新權限后才會生效.下面有刷新權限方法
查看所有數(shù)據(jù)庫用戶及對應端口和密碼
注:數(shù)據(jù)庫所有的用戶都存儲在user表中
查看user表結(jié)構
desc user;
創(chuàng)建賬戶&添加權限
注:只有root賬戶可以設置權限添加用戶
grant 權限列表 on 數(shù)據(jù)庫 to '用戶名'@'訪問主機' identified by '密碼';
權限列表:select update delete insert alter drop create all
數(shù)據(jù)庫:只寫數(shù)據(jù)庫代表該數(shù)據(jù)庫下所有的表都會添加相應的權限,如果要精確到表的權限,使用數(shù)據(jù)庫.數(shù)據(jù)表 注意加.
訪問主機:localhost代表只運行本機訪問,%代表所有ip都可以訪問,具體ip表示只具體ip可以訪問
grant select on test to 'laowang'@'localhost' identified by '123456';
grant select on test.table1 to 'xiaowang'@'localhost' identified by '123456';
grant all on test to "laoli"@"%" identified by "12345678"
給用戶賦予所有數(shù)據(jù)庫權限, 如果不設置密碼可以刪除后面的密碼
grant all privileges on *.* to '用戶'@'localhost' identified by '123456';
查看用戶具有的權限
show grants for 用戶@"ip";
收回數(shù)據(jù)庫權限
revoke 權限 on 數(shù)據(jù)庫.數(shù)據(jù)表 from "用戶"@"主機名"
修改權限
grant 權限名稱 on 數(shù)據(jù)庫 to "用戶"@"ip" with grant option;
刪除用戶
drop user "用戶名"@"ip"
or
delete from user where user='用戶名';
修改指定用戶的密碼
update mysql.user set authentication_string=password("新密碼") where User = "test" and Host = "localhost"
刷新數(shù)據(jù)庫權限
方法一:使用命令 FLUSH PRIVILEGES
# flush privileges
方法二:重啟mysql服務
windows:
net stop mysql
net start mysql
linux:
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restart
