新建用户
#“localhost”:本地登录权限;“%”:远程登录权限
CREATE USER ‘test’@‘localhost’ IDENTIFIED BY ‘123456’;
#这种创建方式只能本地登录
CREATE USER ‘test’@‘%’ IDENTIFIED BY ‘123456’;
#这种创建方式可以远程登录,即别的地方可以登录
查询数据库下所有账户
select user from mysql.user;
更改用户名
输入update user set user =‘新用户名’ where user =‘旧用户名’,
例如:update user set user =‘user_new’ where user =‘user_old’;
查看账户权限
方法一:show grants for 账户名;
方法二:select * from mysql.user WHERE user='账户名‘\G;
给用户授权
MySQL5.0: grant all privileges on testDemo.* to “test”@“localhost” identified by “123456”;
MySQL8.0: grant privileges on databasename.tablename to ‘用户名’@‘本地或远程’;
privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL
databasename:数据库名
tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示,如.*
例子:
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
注意:
用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO ‘username’@‘host’ WITH GRANT OPTION;
撤销用户权限
撤销语句要和授权语句完全一致
REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM ‘user_name’@‘host’;
删除子账号及权限
1.删除远程test账号(此时本地的test账号依然可以使用)
drop user test@“%”;
2.删除本地test账号
drop user test@“localhost”;