权限管理

创建账号

本地账号

CREATE
USER 'ya'@'localhost'
IDENTIFIED BY '123.com;

远程账号

CREATE
USER 'ya1'@'192.168.1.1'
IDENTIFIED BY '123.com;

一个网段都可登陆

CREATE
USER 'ya1'@'192.168.1.%'
IDENTIFIED BY '123.com;

任何机器都可登陆

CREATE
USER 'ya1'@'%'
IDENTIFIED BY '123.com';

授权账号

授权所有库，user表*.*

授权某个库，db表db1.*

授权某个表，tables_priv表

授权某字段，columns_priv表

授权本地账号ya1对所有库的所有权限

GRANT all
ON *.*
TO 'ya1'@'127.0.0.1';

授权本地账号ya1对所有库的查询权限

GRANT select
ON *.*
TO 'ya1'@'127.0.0.1';

针对某个库授权，即对该库所有表都有权限

GRANT all
ON db1.*
TO 'ya1'@'127.0.0.1';

针对某库的某个表授权

GRANT all
ON db1.t1
TO 'ya1'@'127.0.0.1';

授权查询id，name，更新age的权限

GRANT select(id,name),update(age)
ON db1.t1
TO 'ya1'@'127.0.0.1';

删除权限

　　取消本地账号ya1对所有库的查询权限

REVOKE select
ON *.*
FROM 'ya1'@'127.0.0.1';

修改账号密码

mysql -uroot -p
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

 新版本数据库可能不支持以上方法修改密码

SET PASSWORD FOR 'ya'@'%' = PASSWORD('123.Com');