创建账号
本地账号
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';