更改mysql 权限设置_Mysql 设置用户，增，删，改，查权限

创建用户

CREATE USER 'zhidu'@'%' IDENTIFIED BY '123456'; //所有用户可远程

CREATE USER 'zhidu002'@'192.168.1.1' IDENTIFIED BY '123456';//指定用户连接

CREATE USER 'zhidu002'@'locahost' IDENTIFIED BY '123456';//只能本地连接

创建只读用户

GRANT privileges ON databasename.tablename TO 'username'@'host';

GRANT SELECT ON *.* TO 'reader'@'%'  IDENTIFIED BY "123456";

GRANT INSERT ON *.* TO 'reader'@'%'  IDENTIFIED BY "123456";

GRANT UPDATE ON *.* TO 'reader'@'%'  IDENTIFIED BY "123456";

GRANT SELECT ON *.* TO reader@192.168.10.89  IDENTIFIED BY "123456";

移除用户权限

REVOKE SELECT, INSERT, UPDATE ON *.* FROM 'reader'@'%';

REVOKE INSERT, UPDATE ON *.* FROM 'reader'@'%';

创建用户所有权限

grant all privileges on *.* to 'zhidu'@'%'identified by 'zhidu123456' with grant option;

GRANT ALL PRIVILEGES ON database1.table1 TO 'monty'@'%'

刷新

flush privileges;

用户可远程

#mysql -u root -proot

mysql>use mysql;

mysql>update user set host = '192.168.10.85' where user = 'zhidu';

【update user set host = '%' where user = 'root';】

mysql>select host, user from user;

修改指定用户密码 update mysql.user set password=password('zhidu') where User="zhidu" and Host="192.168.10.85";

删除用户 delete from user where User='zhidu' and Host='192.168.10.85';

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

CREATE USER 'liujunqi'@'%' IDENTIFIED BY 'liujun~acb*DBF';

GRANT privileges ON liujunqi_edu.* TO 'username'@'%';

GRANT ALL ON liujunqi_edu.* TO 'liujunqi'@'%';

flush privileges;

本文为daivid原创www.oaiyou.com