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

根据grant 命令语法,我们知道'test'@'localhost'位置为授权访问数据库的主机,localhost可以是域名,ip地址或者IP端来代替,因此,要授权局域网内主机通过下面方法实现

a、百分百匹配法

grant all on lvnian.*  to 'test'@'10.0.0.%' identified by 'lvnian' ; 

b、子网掩码匹配法

grant all on lvnian.*  to 'test'@'10.0.1.0/24' identified by 'lvnian' ;  

grant all on lvnian.*  to 'test'@'10.0.2.0/255.255.0.0' identified by 'lvnian' ;  


mysql> grant all on lvnian.*  to 'test'@'10.0.0.%' identified by 'lvnian' ;          

Query OK, 0 rows affected (0.00 sec)


mysql> grant all on lvnian.*  to 'test'@'10.0.1.0/24' identified by 'lvnian' ;   

Query OK, 0 rows affected (0.00 sec)


mysql> grant all on lvnian.*  to 'test'@'10.0.2.0/255.255.255.0' identified by 'lvnian' ;   

Query OK, 0 rows affected (0.00 sec)


mysql> select user,host from mysql.user;

+-----------+----------------------+

| user      | host                 |

+-----------+----------------------+

| test      | 10.0.0.%             |

| test      | 10.0.1.0/24          |

| test      | 10.0.2.0/255.255.255.0 |

| root      | 127.0.0.1            |

| jeffrey   | localhost            |

| lvnian    | localhost            |            |

| root      | localhost            |

| test      | localhost            |

+-----------+----------------------+

12 rows in set (0.00 sec)


mysql> 

=========================================================================

1、博客、CMS等产品的数据库授权

对于web连接用户尽量采用最小化原则,很多开源软件都是web界面安装,在你安装期间除了授权select、insert、update、delete 这四个权限外,还需要create,drop等比较危险的权限


grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'lvnian';

 show grants for 'blog'@'10.0.0.%'\G ;

 

常规情况下授权select、insert、update、delete 这四个即可,有点开源软件,例如discuz、bbs还需要create,drop等比较危险的权限


2.但安装完成之后把create,drop权限收回了;


revoke create,drop on blog.* from 'blog'@'10.0.0.%';  

show grants for 'blog'@'10.0.0.%'\G ;




##########################

mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'10.0.0.%' identified by 'lvnian';

Query OK, 0 rows affected (0.00 sec)


mysql>  show grants for 'blog'@'10.0.0.%'\G ;

*************************** 1. row ***************************

Grants for blog@10.0.0.%: GRANT USAGE ON *.* TO 'blog'@'10.0.0.%' IDENTIFIED BY PASSWORD '*418F84B88FD68805567E091AE2CF9ADE24935A31'

*************************** 2. row ***************************

Grants for blog@10.0.0.%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `blog`.* TO 'blog'@'10.0.0.%'

2 rows in set (0.00 sec)


ERROR: 

No query specified


mysql> 

########################

mysql> revoke create,drop on blog.* from 'blog'@'10.0.0.%';  

Query OK, 0 rows affected (0.00 sec)


mysql> show grants for 'blog'@'10.0.0.%'\G ;

*************************** 1. row ***************************

Grants for blog@10.0.0.%: GRANT USAGE ON *.* TO 'blog'@'10.0.0.%' IDENTIFIED BY PASSWORD '*418F84B88FD68805567E091AE2CF9ADE24935A31'

*************************** 2. row ***************************

Grants for blog@10.0.0.%: GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'10.0.0.%'

2 rows in set (0.00 sec)


ERROR: 

No query specified


mysql>