mysql授权

1，创建mysql用及授予权限：

在mysql中输入help grant 会出现下面信息：

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;

View Code

通过grant 命令创建用户并授权：

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

生产环境针对主库（写入主读为辅）用户的授权；

普通环境：

本机：lnmp，lamp环境数据库授权
grant all privileges ON blog.* to blog@localhost identified by ‘123456’
应用服务器和数据库服务器不在一个主机上授权；
grant all privileges ON blog.* to blog@10.0.0.% identified by ‘123’
严格的授权：重视安全，忽略了方便；
grant select,insert,update,delete ON blog.* to blog@10.0.0.% identified by ‘123’
生产环境从库（只读）用户的授权；
grant select ON blog.* to blog@10.0.0.% identified by ‘123’
查看授权用户oldboy的具体的授权权限
show grants for ‘oldboy’@’localhost’;

第一种：授权用户

grant all on test.* to oldboy@127.0.0.% identified by ‘oldboy123’
show grants for oldboy@’127.0.0.%’; 查看授权用户
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@127.0.0.1|
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'127.0.0.1' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

■ 第二种：授权方法

create user bbs@'172.16.1.1/255.255.255.0' identified by '123456'；
先授权可以登录的
mysql> show grants for bbs@'172.16.1.1/255.255.255.0';
mysql> grant select on wordpress.* to bbs@'172.16.1.1/255.255.255.0';

授权局域网主机连接远程数据库

a.一条命令百分号匹配法

grant all on *.* to‘test@10.0.0.%’identified by ‘test123’;

b、一条命令子网掩码配置法

grant all on *.* to test@’10.0.0.0/255.255.255.0’ identified by ‘test123’;

c、两条命令实现
先创建用户并设置密码；

create user test@’10.0.0.%’ identified by ‘test123’;
再对用户授权指定权限和管理库表
grant all on *.* to test@10.0.0.0/255.255.255.0

最后记得上述每条grant命令都要刷新权限

flush privilege

数据库远程登录

mysql -uwordpress -poldboy123 -h 172.16.1.51 -P3306
-h指定IP地址，-P指定服务端口号

创建类似于root系列的管理员用户，可以创建下级用户的用户

grant all privileges on *.* to root@'127.0.0.1' identified by 'oldboy123' with grant option;
只需要在最后输入with grant option

回收用户权限

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';