mysql用户与授权

  • 1.创建用户
insert into mysql.user(Host,User,Password) values("localhost","test",password("123456"));
  • 2.用户授权
权限 on 数据库.* to 用户名@登录主机 identified by "密码";

grant all privileges on testDB.* to test@localhost identified by "123456"; //all privileges 所有权限

注:授权test用户拥有testDB数据库的所有权限(指定数据库的所有权限) | @"%"表示对所有非本地主机授权,不包括localhost(127.0.0.1)

flush privileges; //刷新系统权限表
grant select,update,create,drop,delete on testDB.* to test@"%" identified by "123456";

注:授权test用户拥有对testDB数据库select,update,create,drop,delete权限(指定数据库的部分权限)

  • 3.修改用户密码
update mysql.user set Password = password("新密码") where User = "test" and Host = "localhost";
flush privileges;
  • 4.删除用户
delete from mysql.user where User="test" and Host="localhost";
flush privileges;