8a审计日志使用方法（供参考）

--审计日志

gccli -uroot -e"set global log_output='file';"

gccli -uroot -e"set global audit_log = 1;"

gccli -uroot -e"create audit policy audit_all;"

cat /opt/10.182.188.87/gcluster/log/gcluster/gclusterd-audit.log

cat /opt/10.182.188.87/gnode/log/gbase/gbased-audit.log

gccli -uroot -e"set global log_output='table';"

gccli -uroot -e"truncate self table gbase.audit_log;"

gccli -uroot -e"select * from gbase.audit_log limit 1 \G"

gccli -uroot -e"set global audit_log = 0;"

gccli -uroot -e"truncate self table gbase.audit_log;"

gccli -uroot -vvv -e"select * from gbase.audit_log limit 1 \G"

--慢跟踪

gccli -uroot -e"set global audit_log = 1;"

gccli -ugbase -pgbase20110531

alter audit policy audit_all set long_query_time = 10;

truncate self table gbase.audit_log;

select now();

select sleep(12);

select * from gbase.audit_log where user = 'gbase'\G

alter audit policy audit_all set long_query_time = 0;

drop audit policy audit_all;

truncate self table gbase.audit_log;

--审计策略设置

gccli -uroot -Dtest

drop table if exists t1;

create table t1(id int,num int);

insert into t1 values(1,1),(2,2),(3,3);

select * from t1;

create user user1 identified by 'User#666';

create user user2 identified by 'User#666';

create user user3 identified by 'User#666';

grant all on test.* to user1;

grant all on test.* to user2;

grant all on test.* to user3;

为user1设置insert、update、delete审计策略项,为user2设置CREATE_TABLE、DROP_TABLE审计策略项,为user3设置select、SUCCESS审计策略项

create audit policy audit_user1 (User='user1',Sql_commands ='insert,update,delete');

create audit policy audit_user2 (User='user2',Sql_commands ='CREATE_TABLE,DROP_TABLE');

create audit policy audit_user3 (User='user3',Sql_commands ='select',Status = 'SUCCESS');

select * from gbase.audit_policy;

truncate self table gbase.audit_log;

使用user1执行insert、update、delete、select操作

gccli -uuser1 -p'User#666' -Dtest

insert into t1 values(4,4);

update t1 set num=100 where id=4;

delete from t1 where id=4;

select * from t1;

使用user2执行CREATE_TABLE、insert、select、DROP_TABLE操作：

gccli -uuser2 -p'User#666' -Dtest

create table t1(id int,num int);

insert into t1 values(1,1);

select * from t1;

drop table t1;

查询审计日志表，user1的insert、update、delete操作会被记录，selelt操作不会被记录：

select user,start_time,end_time,query_time,rows,db,sql_text,status from gbase.audit_log where user like 'user1';

查询审计日志表，user2的CREATE_TABLE、DROP_TABLE操作会被记录，insert、select操作不会被记录：

select user,start_time,end_time,query_time,rows,db,sql_text,status from gbase.audit_log where user like 'user2';

查询审计日志表，user3的人工中断不会被记录

gccli -uuser3 -p'User#666'

select count(*) from (select sum(num)*avg(value),sum(value*(num)),count(*) from test.t_all group by userid) a;

select t1.C_CUSTKEY,t1.C_NAME,t2.O_ORDERKEY,t2.O_TOTALPRICE from tpch.customer t1 inner join tpch.orders t2 on c_custkey=o_custkey and o_orderdate between '1992-08-13' and '1992-08-14' and t1.C_NAME='Customer#732772547';

select user,start_time,end_time,query_time,rows,db,sql_text,status from gbase.audit_log where user like 'user3';

--恢复集群状态

drop audit policy audit_user1;

drop audit policy audit_user2;

drop audit policy audit_user3;

drop user user1;

drop user user2;

drop user user3;

set global audit_log = 0;

truncate self table gbase.audit_log;