[AddUserAndGrantPrivileges.sql] Manage users and Grant Privileges of the uses #MySql

AddUserAndGrantPrivileges.sql

------------- 查看用户 -----------
use mysql;
select host,user,password from user;

------------- 查看用户权限 -------- 
-- 查看本地root的权限，没有指定后面的localhost则默认为'%'
show grants for root@'localhost';
-- 查看指定host的root的权限
show grants for root@'192.168.122.12';

------------- 创建本地用户 --------
-- 选择mysql数据库
use mysql;
-- 创建本地用户
create user 'superboy'@'localhost' identified by 'iamsuperboy';
-- 刷新MySQL的系统权限相关表，使添加用户操作生效，以免会出现拒绝访问
flush privileges;

------------ 创建远程用户 ---------
-- 从192.168.122.12登陆的用户
create user 'superboy'@'192.168.122.12' identified by 'iamsuperboy';
-- 从任意ip登陆的用户
create user 'superboy'@'%' identified by 'iamsuperboy';
-- 不做指定默认为'%'
create user 'superboy' identified by 'iamsuperboy';

------------- 修改用户密码 --------
-- 使用update指令，注意这里的password需要进行加密
use mysql;
update user set password = password('iamsuperman') where user = 'superboy';
flush privileges;
-- 或者
set password for superboy@'localhost'= password('iamsuperman');
flush privileges;

---------------- 删除用户 --------
use mysql;
delete from user where user='superboy' and host='localhost' ;
flush privileges;

-------------- 赋予用户权限 -------
-- 赋予部分权限，其中的shopping.*表示对以shopping所有文件操作。
grant select,delete,update,insert on simpleshop.* to superboy@'localhost' identified by 'superboy';
flush privileges;
-- 赋予所有权限
grant all privileges on simpleshop.* to superboy@localhost identified by 'iamsuperboy';
flush privileges;

-------------- 撤销用户权限 -------
-- 撤销update权限
revoke update on simpleshop.* from superboy@localhost;
-- 撤销所有权限
revoke all on simpleshop.* from superboy@localhost;