MySQL新增用户,授予权限,撤销权限,远程访问
新增用户:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
其中:
username:新增的用户名
host:指定该用户登录地址
1.本地用户使用localhost;
2.任意远程主机登陆,使用通配符%
password:该用户的登陆密码,密码可以为空
例:
CREATE USER ‘zhangsan’@‘localhost’ IDENTIFIED BY ‘123456’;
CREATE USER ‘zhangsan’@‘192.168.1.1’ IDENDIFIED BY ‘123456’;
CREATE USER ‘zhangsan’@‘%’ IDENTIFIED BY ‘123456’;
CREATE USER ‘zhangsan’@‘%’ IDENTIFIED BY ‘’;
CREATE USER ‘zhangsan’@‘%’;
授予权限:
GRANT privileges ON databasename.tablename TO 'username'@'host'
其中:
privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL
databasename:数据库名
tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示,如.*
例:
GRANT SELECT, INSERT,UPDATE ON mysql.user TO ‘zhangsan’@‘%’;
GRANT ALL ON * . * TO ‘zhangsan’@‘%’;
GRANT ALL ON mysql.* TO ‘zhangsan’@‘%’;
另外:
如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
撤销:
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
查看权限:
SHOW GRANTS FOR ‘zhangsan’@‘%’;
SHOW GRANTS FOR ‘zhangsan’@‘localhost’;
远程访问:
mysql -h192.168.1.1 -uadmin -p123456
所有操作完成后记得刷新!!!
FLUSH PRIVILEGES;