server_audit.test 9.17 KB
Newer Older
1
--source include/have_plugin_auth.inc
2 3 4 5 6 7
--source include/not_embedded.inc

if (!$SERVER_AUDIT_SO) {
  skip No SERVER_AUDIT plugin;
}

8 9 10 11 12 13 14
# An unfortunate wait for check-testcase.test to complete disconnect.
let count_sessions= 1;
source include/wait_until_count_sessions.inc;

let $MYSQLD_DATADIR= `SELECT @@datadir`;
let SEARCH_FILE= $MYSQLD_DATADIR/server_audit.log;

15 16 17
install plugin server_audit soname 'server_audit';

show variables like 'server_audit%';
18 19
set global server_audit_file_path=null;
set global server_audit_incl_users=null;
20 21 22
set global server_audit_file_path='server_audit.log';
set global server_audit_output_type=file;
set global server_audit_logging=on;
23 24 25 26 27 28 29

--error ER_WRONG_VALUE_FOR_VAR
set global server_audit_incl_users= repeat("'root',", 10000);
show variables like 'server_audit_incl_users';
--error ER_WRONG_VALUE_FOR_VAR
set global server_audit_excl_users= repeat("'root',", 10000);
show variables like 'server_audit_excl_users';
30 31
let SEARCH_COUNT= 5;
source include/wait_for_line_count_in_file.inc;
32

33 34
connect (con1,localhost,root,,mysql);
disconnect con1;
35 36 37
let SEARCH_COUNT= 7;
source include/wait_for_line_count_in_file.inc;

38 39 40
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (con1,localhost,no_such_user,,mysql);
41 42 43
let SEARCH_COUNT= 9;
source include/wait_for_line_count_in_file.inc;

44 45 46 47 48 49 50 51 52 53 54 55 56
connection default;
set global server_audit_incl_users='odin, dva, tri';
create table t1 (id int);
set global server_audit_incl_users='odin, root, dva, tri';
create table t2 (id int);
set global server_audit_excl_users='odin, dva, tri';
insert into t1 values (1), (2);
select * from t1;
set global server_audit_incl_users='odin, root, dva, tri';
insert into t2 values (1), (2);
select * from t2;
alter table t1 rename renamed_t1;
set global server_audit_events='connect,query';
57 58
select 1,
        2,
59
# comment
60
        3;
61 62
insert into t2 values (1), (2);
select * from t2;
63
--disable_ps_protocol
64 65
--error ER_NO_SUCH_TABLE
select * from t_doesnt_exist;
66
--enable_ps_protocol
67 68 69 70 71 72 73
--error 1064
syntax_error_query;
drop table renamed_t1, t2;
show variables like 'server_audit%';
set global server_audit_mode=1;
set global server_audit_events='';
create database sa_db;
74 75 76
let SEARCH_COUNT= 41;
source include/wait_for_line_count_in_file.inc;

77 78 79 80 81 82 83 84 85 86 87
connect (con1,localhost,root,,test);
create table t1 (id2 int);
insert into t1 values (1), (2);
select * from t1;
drop table t1;
use sa_db;
create table sa_t1(id int);
insert into sa_t1 values (1), (2);
drop table sa_t1;
drop database sa_db;
disconnect con1;
88 89 90 91
let SEARCH_COUNT= 68;
source include/wait_for_line_count_in_file.inc;

connection default;
Alexey Botchkov's avatar
Alexey Botchkov committed
92 93 94 95 96 97
create database sa_db;
use sa_db;
CREATE USER u1 IDENTIFIED BY 'pwd-123';
GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321";
SET PASSWORD FOR u1 = PASSWORD('pwd 098');
CREATE USER u3 IDENTIFIED BY '';
98
ALTER USER u3 IDENTIFIED BY 'pwd-456';
Alexey Botchkov's avatar
Alexey Botchkov committed
99 100 101 102 103 104 105 106 107 108 109
drop user u1, u2, u3;

set global server_audit_events='query_ddl';
create table t1(id int);
insert into t1 values (1), (2);
select * from t1;
select 2;
(select 2);
/*! select 2*/;
/*comment*/ select 2;
drop table t1;
110 111 112 113 114
create procedure pr1() insert into test.t1 values ("foo", 42);
create function fn1(i int) returns int deterministic return i+1;
drop procedure pr1;
drop function fn1;

Alexey Botchkov's avatar
Alexey Botchkov committed
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
set global server_audit_events='query_ddl,query_dml';
create table t1(id int);
insert into t1 values (1), (2);
select * from t1;
select 2;
drop table t1;
set global server_audit_events='query_dml';
create table t1(id int);
insert into t1 values (1), (2);
select * from t1;
select 2;
(select 2);
/*! select 2*/;
/*comment*/ select 2;
drop table t1;
130 131 132 133 134 135
set global server_audit_events='query_dcl';
create table t1(id int);
insert into t1 values (1), (2);
select * from t1;
CREATE USER u1 IDENTIFIED BY 'pwd-123';
GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321";
136 137 138
SET PASSWORD 
# comment
FOR u1 = PASSWORD('pwd 098');
139 140
--error 1064
SET PASSWORD FOR u1=<secret>;
141 142 143 144 145 146 147
CREATE USER u3 IDENTIFIED BY '';
drop user u1, u2, u3;
select 2;
(select 2);
/*! select 2*/;
/*comment*/ select 2;
drop table t1;
148 149 150 151 152 153
set global server_audit_events='query_dml_no_select';
create table t1(id int);
insert into t1 values (1), (2);
select * from t1;
select 2;
drop table t1;
154 155 156 157
create procedure pr1() insert into test.t1 values ("foo", 42);
create function fn1(i int) returns int deterministic return i+1;
drop procedure pr1;
drop function fn1;
158

159 160 161 162 163 164 165 166 167 168
set global server_audit_events='table';
set global server_audit_incl_users='user1';

create user user1@localhost;
grant all on sa_db.* to user1@localhost;

connect (cn1,localhost,user1,,sa_db);
connection cn1;

create table t1(id int) engine=myisam;
169
insert delayed into t1 values (1);
170
connection default;
171
--echo # Waiting until INSERT DELAYED thread does the insert.
172
let $wait_condition= SELECT COUNT(*) = 1 FROM t1;
173 174 175
--source include/wait_condition.inc
drop table t1;

176
set global server_audit_logging= off;
177
set global server_audit_incl_users='root';
178
set global server_audit_logging= on;
179
disconnect cn1;
180 181 182
let $count_sessions=1;
source include/wait_until_count_sessions.inc;

183
drop user user1@localhost;
184

Alexey Botchkov's avatar
Alexey Botchkov committed
185
set global server_audit_events='';
186
set global server_audit_incl_users='root, plug_dest';
187

188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
--sleep 2
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
--error ER_ACCESS_DENIED_ERROR : this should fail : no grant
connect(plug_con,localhost,plug,plug_dest);
--sleep 2
GRANT PROXY ON plug_dest TO plug;
--sleep 2
connect(plug_con,localhost,plug,plug_dest);
connection plug_con;
select USER(),CURRENT_USER();
connection default;
disconnect plug_con;
--sleep 2
--sleep 2
DROP USER plug;
DROP USER plug_dest;

207 208 209 210
set global server_audit_query_log_limit= 15;
select (1), (2), (3), (4);
select 'A', 'B', 'C', 'D';
set global server_audit_query_log_limit= 1024;
Alexey Botchkov's avatar
Alexey Botchkov committed
211 212
drop database sa_db;

213 214
select length('01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');

215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
set global server_audit_file_path='.';
--replace_regex /\.[\\\/]/HOME_DIR\//
show status like 'server_audit_current_log';
set global server_audit_file_path='';
show status like 'server_audit_current_log';
set global server_audit_file_path='  ';
show status like 'server_audit_current_log';
set global server_audit_file_path='nonexisting_dir/';
show status like 'server_audit_current_log';
show variables like 'server_audit%';
uninstall plugin server_audit;

# replace the timestamp and the hostname with constant values
--replace_regex /[0-9]* [0-9][0-9]:[0-9][0-9]:[0-9][0-9]\,[^,]*\,/TIME,HOSTNAME,/ /\,[1-9][0-9]*\,/,1,/ /\,[1-9][0-9]*/,ID/
cat_file $MYSQLD_DATADIR/server_audit.log;
remove_file $MYSQLD_DATADIR/server_audit.log;