mysql管理2

### 创建用户
CREATE USER 'username'@'l主机' IDENTIFIED BY 'password';
#sername – 你将创建的用户名说明:

#主机 – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,  如 果想让该用户可以从任意远程主机登陆,可以使用通配符%

#password –  该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登 陆服务器

CREATE USER 'javai'@'localhost' IDENTIFIED BY '123456'; 
 
CREATE USER 'java'@'172.20.0.0/255.255.0.0' IDENDIFIED BY '123456'; 
 
CREATE USER 'java'@'%' IDENTIFIED BY '123456'; 
 
CREATE USER 'java'@'%' IDENTIFIED BY ''; 
 
CREATE USER 'java'@'%';

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

#privileges – 用户的操作权限,如SELECT , INSERT , UPDATE  等(详细列表见该文最后面).如果要授予所 的权限则使用ALL说明: 

#databasename –  数据库名

#tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用* 表示, 如*.*

GRANT SELECT, INSERT ON test.user TO 'javacui'@'%'; 
 
GRANT ALL ON *.* TO 'javacui'@'%';

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

###设置与更改用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

### 如果是当前登陆用户用
SET PASSWORD = PASSWORD("newpassword");
 
 ###撤销用户权限
 REVOKE privilege ON databasename.tablename FROM 'username'@'host';
 
 REVOKE SELECT ON *.* FROM 'java'@'%';
 
 ####注意
 ##第一种情况
 grant all on mysql.user to 'ls'@'%' identified by '123456';
 show grants for 'ls'@'%';
 +----------------------------------------------------+
| Grants for ls@%                                    |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'ls'@'%'                     |
| GRANT ALL PRIVILEGES ON "mysql"."user" TO 'ls'@'%' |
+----------------------------------------------------+
revoke all on *.* from 'ls'@'%';
 show grants for 'ls'@'%';
 +----------------------------------------------------+
| Grants for ls@%                                    |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'ls'@'%'                     |
| GRANT ALL PRIVILEGES ON "mysql"."user" TO 'ls'@'%' |
+----------------------------------------------------+
##第二种情况
grant select on *.* to 'ww'@'%' identified by '123456';
 show grants from 'ww'@'%';
 +---------------------------------+
| Grants for ww@%                 |
+---------------------------------+
| GRANT SELECT ON *.* TO 'ww'@'%' |
+---------------------------------+
revoke select on mysql.user from 'ww'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'ww' on host '%' on table 'user

###删除用户
DROP USER ‘username’@'host’;

###操作后切记刷新数据库
flush privileges;