分析mysql创建用户的方法 mysql免密码登录下无法创建用户 _免

一、用户与权限管理（一）grant1、help grant和权限相关的命令关键字grant可通过help查看其用法：
mysql> help grant;Name: 'GRANT'Description:Syntax:GRANTpriv_type [(column_list)][, priv_type [(column_list)]] ...ON [object_type] priv_levelTO user_specification [, user_specification] ...[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}][WITH with_option ...]...CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;...2、grant授权对于上述：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';它实际包含了两条命令，先是创建用户jeffrey ，然后才是对这个用户进行授权。如下：
mysql> create user 'jeffrey'@'localhost' identified by 'mypass';mysql> grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';对于授权语句的一些关键字解释如下：

grantall privilegeson dbname.*to username@localhostIdentified by ‘mypass’授权命令对应权限目标：库和表用户名和客户端主机用户密码3、实战练习

查看当前数据库用户情况

mysql> select user,host from mysql.user;+--------+---------------+| user| host|+--------+---------------+| root| 127.0.0.1|| root| ::1|| root| hadoop-slave1 || root| localhost|| system | localhost|+--------+---------------+5 rows in set (0.00 sec)

创建用户并授权

mysql> grant all privileges on test.* to 'admin'@'localhost' identified by 'admin123';Query OK, 0 rows affected (0.01 sec)

查看授权情况

mysql> select user,host from mysql.user;+--------+---------------+| user| host|+--------+---------------+| root| 127.0.0.1|| root| ::1|| root| hadoop-slave1 || admin| localhost|| root| localhost|| system | localhost|+--------+---------------+6 rows in set (0.00 sec)

查看admin具体权限

mysql> show grants for 'admin'@'localhost';+--------------------------------------------------------------------------------------------------------------+| Grants for admin@localhost|+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' || GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'|+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

（二）create和grant配合使用1、使用步骤

创建用户username、主机localhost、密码passwd

mysql> create user 'username'@'localhost' identified by 'passwd';

授权创建的用户管理dbname数据库权限（无需密码）

mysql> grant all privileges to dbname.* to 'username'@'localhost';2、实战练习

查看当前数据库用户情况

mysql> select user,host from mysql.user;+--------+---------------+| user| host|+--------+---------------+| root| 127.0.0.1|| root| ::1|| root| hadoop-slave1 || admin| localhost|| root| localhost|| system | localhost|+--------+---------------+6 rows in set (0.02 sec)

创建用户

mysql> create user 'admin1'@'localhost' identified by 'admin123456';Query OK, 0 rows affected (0.02 sec)注意的是这一步并没有授权，仅仅是创建一个普通用户。

查看用户情况

mysql> select user,host from mysql.user;+--------+---------------+| user| host|+--------+---------------+| root| 127.0.0.1|| root| ::1|| root| hadoop-slave1 || admin| localhost|| admin1 | localhost|| root| localhost|| system | localhost|+--------+---------------+7 rows in set (0.00 sec)

如果对admin1进行授权就参照步使用步骤的第二步完成。
（三）用户授权的权限有什么1、查看用户权限在上面授权过程中可以看出来，使用的基本都是全部权限：
grant all privileges to dbname.* to 'username'@'localhost';然后查看用户的权限后是这样的：

mysql> show grants for 'admin'@'localhost';+--------------------------------------------------------------------------------------------------------------+| Grants for admin@localhost|+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' || GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost'|+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

有时候并不需要给用户给这么多权限，那么怎么收回呢？
2、回收用户权限（revoke）

mysql> revoke insert on test.* from 'admin'@'localhost';#一定要指定在那个数据库上的权限Query OK, 0 rows affected (0.00 sec)

可以再次查看该用户的权限：

mysql> show grants for 'admin'@'localhos+---------------------------------------------------------------------------------------------------------+| Grants for admin@localhost|+---------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' || GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'admin'@'localhost' |+----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

可以看到用户在test数据库上除了insert权限外的权限它都有了。
也就是说数据库的all priveleges包含下面的权限：

INSERT, SELECT, UPDATE, DELETE, CREATE, DROP,REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE,CREATE VIEW, SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EVENT, TRIGGER

所以我们在授权时尽量采用最小化的授权原则，比如：
【分析mysql创建用户的方法 mysql免密码登录下无法创建用户】mysql> grant select,insert,update,delete,create,drop on crm.* to 'admin'@'10.0.0.%' identified by '123456';当admin用户创建表后记得收回create权限：
mysql> revoke create on crm.* from 'admin'@'10.0.0.0.%';注意：可通过help revoke查看用法
二、远程连接通过上面的授权，比如：…’admin1’@’localhost’..中的localhost是授权的主机，也就是说什么样的机器有权限连接MySQL服务器。localhost可以用域名、IP地址、IP端来代替。
（一）匹配方式1、百分号匹配法mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';mysql> flush privileges;2、子网掩码配置法mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.0、255.255.255.0' identified by '123456';mysql>flush privileges;（二）客户端连接客户端本地连接与远程连接是不一样的，如果远程连接首先应该赋予远程连接的权限：
mysql> grant all 0n dbname.* to 'admin1'@'10.0.0.%' identified by '123456';其次，再进行远程连接：
mysql> mysql -uadmin1 -p123456 -h 10.0.0.0.3