mysql用户和权限管理

一、mysql用户和权限管理

1.1 用户管理包括：

• 创建用户 create user xwp@'%' identified by '12345678';
• 修改用户密码 set password for xwp = password('123456');
• 用户重命名 rename user xwp to xwphs;
• 删除用户 drop user xwphs@'%';

1.2 权限管理

• 查看权限 show grants for xwphs@'%';
• 用户授权 grant select(all privileges) on *.* to xwphs@'%' with grant option;
• 撤销授权 revoke all privileges on *.* from xwphs@'%';

1.3 示例

-- 查看用户表
select * from mysql.user;

-- 创建用户
create user xwp@'%' identified by 'nsbg1111*';

-- 修改密码1
set password for xwp@'%' = password('xwphshaha');
-- 修改密码2
update mysql.user set authentication_string = password('xwphsheihei') where `user`='xwp' and `host`='%';
flush privileges;

-- 重命名
rename user xwp to xwphs;

-- 查询用户权限
show grants for xwphs@'%';

-- 用户授权
grant select on *.* to xwphs@'%';
grant all privileges on *.* to xwphs@'%' with grant option;

-- 撤销权限
revoke select on *.* from xwphs@'%';
revoke all privileges on *.* from xwphs@'%';

-- 删除用户
drop user xwphs@'%';