mysql开启安全审计功能。

mysql服务器自身没有提供审计功能,但是我们可以使用init-connect + binlog的方法进行mysql的 *** 作审计。由于mysql binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id但是却没有记录connection_id对应的详细用户信息。在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id 结合 之前连接日志记录 进行分析，得出最后的结论。

1. 设置init-connect

1.1 创建用于存放连接日志的数据库和表

create database accesslog

CREATE TABLE accesslog.accesslog (`id` int(11) primary key auto_increment, `time` timestamp, `localname` varchar(30), `matchname` varchar(30))

1.2 创建用户权限

可用现成的root用户用于信息的读取

grant select on accesslog.* to root

如果存在具有to *.* 权限的用户需要进行限制。

这里还需要注意用户必须对accesslog表具有insert权限

grant select on accesslog.* to user@’%’

1.3 设置init-connect

在[mysqld]下添加以下设置：

init-connect=’insertinto accesslog.accesslog(id, time, localname, matchname)

values(connection_id(),now(),user(),current_user())’

------注意user()和current_user()的区别

log-bin=xxx

这里必须开启binlog

1.4 重启数据库生效

shell>/etc/init.d/mysql restart

2. 记录追踪

2.1 thread_id确认

可以用以下语句定位语句执行人

Tencent:~ # mysqlbinlog --start-datetime='2011-01-26 16:00:00'

--stop-datetime='2011-01-26 17:00:00' /var/lib/mysql/mysql-bin.000010

| grep -B 5 'wsj'

COMMIT/*!*/

# at 767

#110126 16:16:43 server id 1 end_log_pos 872 Query thread_id=19exec_time=0 error_code=0

use test/*!*/

SET TIMESTAMP=1296029803/*!*/

create table wsj(id int unsigned not null)

--

BEGIN

/*!*/

# at 940

#110126 16:16:57 server id 1 end_log_pos 1033 Query thread_id=19exec_time=0 error_code=0

SET TIMESTAMP=1296029817/*!*/

insert into wsj(id) values (1)

--

BEGIN

/*!*/

# at 1128

#110126 16:16:58 server id 1 end_log_pos 1221 Query thread_id=19exec_time=0 error_code=0

SET TIMESTAMP=1296029818/*!*/

insert into wsj(id) values (2)

2.2 用户确认

thread_id 确认以后，找到元凶就只是一条sql语句的问题了。

mysql>select * from accesslog where id=19

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

| id | time| localname | matchname |

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

| 19 | 2011-01-26 16:15:54 | test@10.163.164.216 | test@%|

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

1 row in set (0.00 sec)

在oracle11g中，数据库的审计功能是默认开启的（这和oracle10g的不一样，10g默认是关闭的），

oracle11gr2的官方文档上写的是错的，当上说default是none，而且是审计到db级别的，这样就会

往aud$表里记录统计信息。

1.如果审计不是必须的，可以关掉审计功能；

sql>

show

parameter

audit_trail

name

type

value

------------------------------------

-----------

------------------------------

audit_trail

string

db

sql>

alter

system

set

audit_trail=none

scope=spfile

sql>

shut

immediate

sql>startup

2.删除已有的审计信息

可以直接truncate表aud$,

truncate

table

sys.aud$

3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险。

附：11g中有关audit_trail参数的设置说明：

audit_trail

property

description

parameter

type

string

syntax

audit_trail

=

{

none

|

os

|

db

[,

extended]

|

xml

[,

extended]

}

default

value

none

modifiable

no

basic

no

audit_trail

enables

or

disables

database

auditing.

values:

none

disables

standard

auditing.

this

value

is

the

default

if

the

audit_trail

parameter

was

not

set

in

the

initialization

parameter

file

or

if

you

created

the

database

using

a

method

other

than

database

configuration

assistant.

if

you

created

the

database

using

database

configuration

assistant,

then

the

default

is

db.

os

directs

all

audit

records

to

an

operating

system

file.

oracle

recommends

that

you

use

the

os

setting,

particularly

if

you

are

using

an

ultra-secure

database

configuration.

db

directs

audit

records

to

the

database

audit

trail

(the

sys.aud$

table),

except

for

records

that

are

always

written

to

the

operating

system

audit

trail.

use

this

setting

for

a

general

database

for

manageability.

if

the

database

was

started

in

read-only

mode

with

audit_trail

set

to

db,

then

oracle

database

internally

sets

audit_trail

to

os.

check

the

alert

log

for

details.

db,

extended

performs

all

actions

of

audit_trail=db,

and

also

populates

the

sql

bind

and

sql

text

clob-type

columns

of

the

sys.aud$

table,

when

available.

these

two

columns

are

populated

only

when

this

parameter

is

specified.

if

the

database

was

started

in

read-only

mode

with

audit_trail

set

to

db,

extended,

then

oracle

database

internally

sets

audit_trail

to

os.

check

the

alert

log

for

details.

xml

writes

to

the

operating

system

audit

record

file

in

xml

format.

records

all

elements

of

the

auditrecord

node

except

sql_text

and

sql_bind

to

the

operating

system

xml

audit

file.

xml,

extended

performs

all

actions

of

audit_trail=xml,

and

populates

the

sql

bind

and

sql

text

clob-type

columns

of

the

sys.aud$

table,

wherever

possible.

these

columns

are

populated

only

when

this

parameter

is

specified.

you

can

use

the

sql

audit

statement

to

set

auditing

options

regardless

of

the

setting

of

this

parameter.

---