MySQL创建用户、受权

MySQL 创建用户、授权 一,?创建用户: ???????命令: CREATE?USER?'username'@'host'?IDENTIFIED?BY?'password'; ???????说明:username?-?你将创建的用户名,?host?-?指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,?如果想让该用户可以从任意远程

???????命令: CREATE?USER?'username'@'host'?IDENTIFIED?BY?'password';

???????说明:username?-?你将创建的用户名,?host?-?指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,?如果想让该用户可以从任意远程主机登陆,可以使用通配符%.?password?-?该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

??????例子:?CREATE?USER?'dog'@'localhost'?IDENTIFIED?BY?'123456';
????????????CREATE?USER?'pig'@'192.168.1.101_'?IDENDIFIED?BY?'123456';
????????????CREATE?USER?'pig'@'%'?IDENTIFIED?BY?'123456';
????????????CREATE?USER?'pig'@'%'?IDENTIFIED?BY?'';
????????????CREATE?USER?'pig'@'%';

二,授权:

??????命令: GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'

?????说明:?privileges?-?用户的操作权限,如SELECT?,?INSERT?,?UPDATE?等.如果要授予所的权限则使用ALL.;databasename?-?数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,?如*.*.

??????例子:?GRANT?SELECT,?INSERT?ON?test.user?TO?'pig'@'%';
????????????GRANT?ALL?ON?*.*?TO?'pig'@'%';

??????注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
???????????GRANT?privileges?ON?databasename.tablename?TO?'username'@'host'?WITH?GRANT?OPTION;

三.设置与更改用户密码

?????命令: SET?PASSWORD?FOR?'username'@'host'?=?PASSWORD('newpassword');
如果是当前登陆用户用SET?PASSWORD?=?PASSWORD("newpassword");

??????例子:?SET?PASSWORD?FOR?'pig'@'%'?=?PASSWORD("123456");

四.撤销用户权限

??????命令:?REVOKE?privilege?ON?databasename.tablename?FROM?'username'@'host';

????? 说明:?privilege,?databasename,?tablename?-?同授权部分.

??????例子:?REVOKE?SELECT?ON?*.*?FROM?'pig'@'%';

??????注意:?假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT?SELECT?ON?test.user?TO?'pig'@'%',?则在使用REVOKE?SELECT?ON?*.*?FROM?'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT?操作.相反,如果授权使用的是GRANT?SELECT?ON?*.*?TO?'pig'@'%';则REVOKE?SELECT?ON?test.user?FROM?'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select?权限.

??????具体信息可以用命令SHOW?GRANTS?FOR?'pig'@'%';?查看.

五.删除用户

??????命令:?DROP?USER?'username'@'host';

PS:

 SELECT * FROM mysql.user;
CREATE USER 'jm-dev'@'%' IDENTIFIED BY '_jiami2013';
GRANT ALL ON *.* TO 'jm-dev'@'%' IDENTIFIED BY '_jiami2013' WITH GRANT OPTION;
FLUSH PRIVILEGES;

远程访问：

xusongqin@xusongqin-A75MG:/usr/local/mysql$ netstat -an | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:35443 127.0.0.1:3306 ESTABLISHED
tcp 0 0 127.0.0.1:3306 127.0.0.1:35443 ESTABLISHED
tcp 0 0 127.0.0.1:35497 127.0.0.1:3306 ESTABLISHED
tcp 0 0 127.0.0.1:3306 127.0.0.1:35498 ESTABLISHED
tcp 0 0 127.0.0.1:3306 127.0.0.1:35493 ESTABLISHED
tcp 0 0 127.0.0.1:35493 127.0.0.1:3306 ESTABLISHED
tcp 0 0 127.0.0.1:3306 127.0.0.1:35497 ESTABLISHED
tcp 0 0 127.0.0.1:35498 127.0.0.1:3306 ESTABLISHED

netstat可以看出第一个监听就是mysql,但是监听的是127.0.0.1,查看sudo gedit /etc/init.d/mysql/my.conf
中的bind-address = 127.0.0.1 将这句注释掉，即可以远程访问.

xusongqin@xusongqin-A75MG:/usr/local/mysql$ sudo netstat -an|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 1 0 127.0.0.1:35443 127.0.0.1:3306 CLOSE_WAIT
tcp 0 0 127.0.0.1:3306 127.0.0.1:35443 FIN_WAIT2
tcp 1 0 127.0.0.1:35497 127.0.0.1:3306 CLOSE_WAIT
tcp 0 0 127.0.0.1:3306 127.0.0.1:35498 FIN_WAIT2
tcp 0 0 127.0.0.1:3306 127.0.0.1:35493 FIN_WAIT2
tcp 1 0 127.0.0.1:35493 127.0.0.1:3306 CLOSE_WAIT
tcp 0 0 127.0.0.1:3306 127.0.0.1:35497 FIN_WAIT2
tcp 1 0 127.0.0.1:35498 127.0.0.1:3306 CLOSE_WAIT