init-connectMysql对用户操作加审计功能

忘是亡心i 2024-02-18 20:53 4阅读 0赞

在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。

实验过程：  
1：创建登录日志库，登录日志表

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>CREATE</code> 
      <code>DATABASE</code> 
      <code>`accesslog`;</code> 
     </div> 
     <div> 
      <code>USE `accesslog`;</code> 
     </div> 
     <div> 
      <code>CREATE</code> 
      <code>TABLE</code> 
      <code>`accesslog` </code> 
     </div> 
     <div> 
      <code>(</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`id` </code> 
      <code>int</code> 
      <code>(11) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>AUTO_INCREMENT,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`thread_id` </code> 
      <code>int</code> 
      <code>(11) </code> 
      <code>DEFAULT</code> 
      <code>NULL</code> 
      <code>, #线程ID，这个值很重要</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`log_time` </code> 
      <code>timestamp</code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>DEF AULT </code> 
      <code>CURRENT_TIMESTAMP</code> 
      <code>ON</code> 
      <code>UPDATE</code> 
      <code>CURRENT_TIMESTAMP</code> 
      <code>, #登录时间</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`localname` </code> 
      <code>varchar</code> 
      <code>(30) </code> 
      <code>DEFAULT</code> 
      <code>NULL</code> 
      <code>, #登录名称</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`matchname` </code> 
      <code>varchar</code> 
      <code>(30) </code> 
      <code>DEFAULT</code> 
      <code>NULL</code> 
      <code>, #登录用户</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>PRIMARY</code> 
      <code>KEY</code> 
      <code>(`id`)</code> 
     </div> 
     <div> 
      <code>) ENGINE=InnoDB AUTO_INCREMENT=1 </code> 
      <code>DEFAULT</code> 
      <code>CHARSET=utf8;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

2：在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句，登录就会失败。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>init-</code> 
      <code>connect</code> 
      <code>=</code> 
      <code>'insert into accesslog.accesslog values(null,connection_id(),now(),user(),current_user());'</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

3：创建普通用户，不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须要有INSERT权限，如果没有，登录后的任何操作都会导致MYSQL登录失败。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>grant</code> 
      <code>insert</code> 
      <code>,</code> 
      <code>select</code> 
      <code>,</code> 
      <code>update</code> 
      <code>on</code> 
      <code>*.* </code> 
      <code>to</code> 
      <code>'user1'</code> 
      <code>@</code> 
      <code>'localhost'</code> 
      <code>; #带</code> 
      <code>INSERT</code> 
      <code>权限</code> 
     </div> 
     <div> 
      <code>grant</code> 
      <code>select</code> 
      <code>,</code> 
      <code>update</code> 
      <code>on</code> 
      <code>*.* </code> 
      <code>to</code> 
      <code>'user2'</code> 
      <code>@</code> 
      <code>'localhost'</code> 
      <code>; #不带</code> 
      <code>INSERT</code> 
      <code>权限</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

4：SESSION1登录，并查看日志

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>D:\mysql6\bin&gt;mysql -uuser1 -p</code> 
     </div> 
     <div> 
      <code>Enter </code> 
      <code>password</code> 
      <code>:</code> 
     </div> 
     <div> 
      <code>Welcome </code> 
      <code>to</code> 
      <code>the MySQL monitor. Commands </code> 
      <code>end</code> 
      <code>with</code> 
      <code>; </code> 
      <code>or</code> 
      <code>\g.</code> 
     </div> 
     <div> 
      <code>Your MySQL </code> 
      <code>connection</code> 
      <code>id </code> 
      <code>is</code> 
      <code>65</code> 
     </div> 
     <div> 
      <code>Server version: 5.1.45-community-log MySQL Community Server (GPL)</code> 
     </div> 
     <div> 
      <code>Type </code> 
      <code>'help;'</code> 
      <code>or</code> 
      <code>'\h'</code> 
      <code>for</code> 
      <code>help. Type </code> 
      <code>'\c'</code> 
      <code>to</code> 
      <code>clear the </code> 
      <code>current</code> 
      <code>input statement.</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>* </code> 
      <code>FROM</code> 
      <code>accesslog.accesslog;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>| id | thread_id | log_time | localname | matchname |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>| 1 | 65 | 2011-03-11 19:18:25 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
      <code>(0.00 sec)</code> 
     </div> 
     <div> 
      <code>mysql&gt; show processlist;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>| Id | </code> 
      <code>User</code> 
      <code>| Host | db | Command | </code> 
      <code>Time</code> 
      <code>| State | Info |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>| 65 | user1 | localhost:1339 | </code> 
      <code>NULL</code> 
      <code>| Query | 0 | </code> 
      <code>NULL</code> 
      <code>| show processlist |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
      <code>(0.00 sec)</code> 
     </div> 
     <div> 
      <code>mysql&gt;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

5：再用user2登录

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>D:\mysql6\bin&gt;mysql -uuser2 -p</code> 
     </div> 
     <div> 
      <code>Enter </code> 
      <code>password</code> 
      <code>:</code> 
     </div> 
     <div> 
      <code>Welcome </code> 
      <code>to</code> 
      <code>the MySQL monitor. Commands </code> 
      <code>end</code> 
      <code>with</code> 
      <code>; </code> 
      <code>or</code> 
      <code>\g.</code> 
     </div> 
     <div> 
      <code>Your MySQL </code> 
      <code>connection</code> 
      <code>id </code> 
      <code>is</code> 
      <code>76</code> 
     </div> 
     <div> 
      <code>Server version: 5.1.45-community-log</code> 
     </div> 
     <div> 
      <code>Type </code> 
      <code>'help;'</code> 
      <code>or</code> 
      <code>'\h'</code> 
      <code>for</code> 
      <code>help. Type </code> 
      <code>'\c'</code> 
      <code>to</code> 
      <code>clear the </code> 
      <code>current</code> 
      <code>input statement.</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>* </code> 
      <code>FROM</code> 
      <code>accesslog.accesslog;</code> 
     </div> 
     <div> 
      <code>ERROR 2006 (HY000): MySQL server has gone away</code> 
     </div> 
     <div> 
      <code>No</code> 
      <code>connection</code> 
      <code>. Trying </code> 
      <code>to</code> 
      <code>reconnect...</code> 
     </div> 
     <div> 
      <code>Connection</code> 
      <code>id: 77</code> 
     </div> 
     <div> 
      <code>Current</code> 
      <code>database</code> 
      <code>: *** NONE ***</code> 
     </div> 
     <div> 
      <code>ERROR 2013 (HY000): Lost </code> 
      <code>connection</code> 
      <code>to</code> 
      <code>MySQL server during query</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>* </code> 
      <code>FROM</code> 
      <code>accesslog.accesslog;</code> 
     </div> 
     <div> 
      <code>ERROR 2006 (HY000): MySQL server has gone away</code> 
     </div> 
     <div> 
      <code>No</code> 
      <code>connection</code> 
      <code>. Trying </code> 
      <code>to</code> 
      <code>reconnect...</code> 
     </div> 
     <div> 
      <code>Connection</code> 
      <code>id: 78</code> 
     </div> 
     <div> 
      <code>Current</code> 
      <code>database</code> 
      <code>: *** NONE ***</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

看下错误日志

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>110311 19:23:47 [Warning] Aborted </code> 
      <code>connection</code> 
      <code>77 </code> 
      <code>to</code> 
      <code>db: </code> 
      <code>'unconnected'</code> 
      <code>user</code> 
      <code>: </code> 
      <code>'user2'</code> 
      <code>host: </code> 
      <code>'localhost'</code> 
      <code>(init_connect command failed)</code> 
     </div> 
     <div> 
      <code>110311 19:23:47 [Warning] </code> 
      <code>INSERT</code> 
      <code>command denied </code> 
      <code>to</code> 
      <code>user</code> 
      <code>'user2'</code> 
      <code>@</code> 
      <code>'localhost'</code> 
      <code>for</code> 
      <code>table</code> 
      <code>'accesslog'</code> 
     </div> 
     <div> 
      <code>110311 19:23:53 [Warning] Aborted </code> 
      <code>connection</code> 
      <code>78 </code> 
      <code>to</code> 
      <code>db: </code> 
      <code>'unconnected'</code> 
      <code>user</code> 
      <code>: </code> 
      <code>'user2'</code> 
      <code>host: </code> 
      <code>'localhost'</code> 
      <code>(init_connect command failed)</code> 
     </div> 
     <div> 
      <code>110311 19:23:53 [Warning] </code> 
      <code>INSERT</code> 
      <code>command denied </code> 
      <code>to</code> 
      <code>user</code> 
      <code>'user2'</code> 
      <code>@</code> 
      <code>'localhost'</code> 
      <code>for</code> 
      <code>table</code> 
      <code>'accesslog'</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

6：下面以USER1登录，并做一个INSERT操作，查看日志文件。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>insert</code> 
      <code>into</code> 
      <code>t3 </code> 
      <code>values</code> 
      <code>(10,10,</code> 
      <code>'2011-10-10 00:00:00'</code> 
      <code>);</code> 
     </div> 
     <div> 
      <code>Query OK, 1 row affected (0.00 sec)</code> 
     </div> 
     <div> 
      <code>mysql&gt; show processlist;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+-----------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>| Id | </code> 
      <code>User</code> 
      <code>| Host | db | Command | </code> 
      <code>Time</code> 
      <code>| State | Info |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+-----------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>| 69 | user1 | localhost:1439 | accesslog | Query | 0 | </code> 
      <code>NULL</code> 
      <code>| show processlist |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------+----------------+-----------+---------+------+-------+------------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
      <code>(0.00 sec)</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>accesslog.accesslog;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>| id | thread_id | log_time | localname | matchname |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>| 1 | 65 | 2011-03-11 19:18:25 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>| 2 | 91 | 2011-03-11 19:28:33 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>| 3 | 2 | 2011-03-11 19:31:49 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>| 4 | 2 | 2000-10-10 10:10:10 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>| 5 | 21 | 2000-10-10 11:11:11 | root@localhost | root@% |</code> 
     </div> 
     <div> 
      <code>| 6 | 69 | 2011-03-12 21:35:43 | user1@localhost | user1@localhost |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-----------+---------------------+-----------------+-----------------+</code> 
     </div> 
     <div> 
      <code>6 </code> 
      <code>rows</code> 
      <code>in</code> 
      <code>set</code> 
      <code>(0.01 sec)</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

查看日志文件的内容

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code># </code> 
      <code>at</code> 
      <code>340</code> 
     </div> 
     <div> 
      <code>#110312 21:36:01 server id 1 end_log_pos 453 Query thread_id=69 exec_time=0 error_code=0</code> 
     </div> 
     <div> 
      <code>use text</code> 
      <code>/*!*/</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>SET</code> 
      <code>TIMESTAMP</code> 
      <code>=1299936961</code> 
      <code>/*!*/</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>insert</code> 
      <code>into</code> 
      <code>t3 </code> 
      <code>values</code> 
      <code>(10,10,</code> 
      <code>'2011-10-10 00:00:00'</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>/*!*/</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code># </code> 
      <code>at</code> 
      <code>453</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

thread\_id=69

在日志表里记录的和日志文件里面记录的相同。可以通过这个thread\_id来追踪到是谁，什么时间，做了什么操作。