~drizzle-trunk/drizzle/development : revision 28.1.30

1

#!/usr/bin/perl

2

#

3

# Testing of grants.

4

# Note that this will delete all table and column grants !

5

#

6

7

use DBI;

8

use Getopt::Long;

9

use strict;

10

11

use vars qw($dbh $user_dbh $opt_help $opt_Information $opt_force $opt_debug

12

$opt_verbose $opt_server $opt_root_user $opt_password $opt_user

13

$opt_database $opt_host $version $user $tables_cols $columns_cols

14

$tmp_table $opt_silent);

15

16

$version="1.1";

17

$opt_help=$opt_Information=$opt_force=$opt_debug=$opt_verbose=$opt_silent=0;

18

$opt_host="localhost",

19

$opt_server="mysql";

20

$opt_root_user="root";

21

$opt_password="";

22

$opt_user="grant_user";

23

$opt_database="grant_test";

24

25

GetOptions("Information","help","server=s","root-user=s","password=s","user","database=s","force","host=s","debug","verbose","silent") || usage();

26

usage() if ($opt_help || $opt_Information);

27

28

$user="$opt_user\@$opt_host";

29

30

if (!$opt_force)

31

{

32

print_info()

33

}

34

35

$|=1;

36

37

$tables_cols="Host, Db, User, Table_name, Grantor, Table_priv, Column_priv";

38

$columns_cols="Host, Db, User, Table_name, Column_name, Column_priv";

39

$tmp_table="/tmp/mysql-grant.test"; # Can't use $$ as we are logging result

40

unlink($tmp_table);

41

42

#

43

# clear grant tables

44

#

45

46

$dbh = DBI->connect("DBI:mysql:mysql:$opt_host",

47

$opt_root_user,$opt_password,

48

{ PrintError => 0}) || die "Can't connect to mysql server with user '$opt_root_user': $DBI::errstr\n";

49

50

safe_query("delete from user where user='$opt_user' or user='${opt_user}2'");

51

safe_query("delete from db where user='$opt_user'");

52

safe_query("delete from tables_priv");

53

safe_query("delete from columns_priv");

54

safe_query("lock tables mysql.user write"); # Test lock tables

55

safe_query("flush privileges");

56

safe_query("unlock tables"); # should already be unlocked

57

safe_query("drop database $opt_database",3); # Don't print possible error

58

safe_query("create database $opt_database");

59

60

# check that the user can't login yet

61

62

user_connect(1);

63

#goto test;

64

65

#

66

# Enable column grant code

67

#

68

safe_query("grant select(user) on mysql.user to $user");

69

safe_query("revoke select(user) on mysql.user from $user");

70

71

#

72

# Test grants on user level

73

#

74

75

safe_query("grant select on *.* to $user");

76

safe_query("set password FOR ${opt_user}2\@$opt_host = password('test')",1);

77

safe_query("set password FOR $opt_user\@$opt_host=password('test')");

78

user_connect(1);

79

safe_query("set password FOR $opt_user\@$opt_host=''");

80

user_connect(0);

81

user_query("select * from mysql.user where user = '$opt_user'");

82

user_query("select * from mysql.db where user = '$opt_user'");

83

safe_query("grant select on *.* to $user,$user");

84

safe_query("show grants for $user");

85

user_connect(0);

86

87

# The following should fail

88

user_query("insert into mysql.user (host,user) values ('error','$opt_user')",1);

89

user_query("update mysql.user set host='error' WHERE user='$opt_user'",1);

90

user_query("create table $opt_database.test (a int,b int)",1);

91

user_query("grant select on *.* to ${opt_user}2\@$opt_host",1);

92

safe_query("revoke select on $opt_database.test from $opt_user\@opt_host",1);

93

safe_query("revoke select on $opt_database.* from $opt_user\@opt_host",1);

94

safe_query("revoke select on *.* from $opt_user",1);

95

safe_query("grant select on $opt_database.not_exists to $opt_user",1);

96

safe_query("grant FILE on $opt_database.test to $opt_user",1);

97

safe_query("grant select on *.* to wrong___________user_name",1);

98

safe_query("grant select on $opt_database.* to wrong___________user_name",1);

99

user_connect(0);

100

user_query("grant select on $opt_database.test to $opt_user with grant option",1);

101

safe_query("set password FOR ''\@''=''",1);

102

user_query("set password FOR root\@$opt_host = password('test')",1);

103

104

# Change privileges for user

105

safe_query("revoke select on *.* from $user");

106

safe_query("grant create,update on *.* to $user");

107

user_connect(0);

108

safe_query("flush privileges");

109

user_query("create table $opt_database.test (a int,b int)");

110

user_query("update $opt_database.test set b=b+1 where a > 0",1);

111

safe_query("show grants for $user");

112

safe_query("revoke update on *.* from $user");

113

user_connect(0);

114

safe_query("grant select(c) on $opt_database.test to $user",1);

115

safe_query("revoke select(c) on $opt_database.test from $user",1);

116

safe_query("grant select on $opt_database.test to wrong___________user_name",1);

117

user_query("INSERT INTO $opt_database.test values (2,0)",1);

118

119

safe_query("grant ALL PRIVILEGES on *.* to $user");

120

safe_query("REVOKE INSERT on *.* from $user");

121

user_connect(0);

122

user_query("INSERT INTO $opt_database.test values (1,0)",1);

123

safe_query("grant INSERT on *.* to $user");

124

user_connect(0);

125

user_query("INSERT INTO $opt_database.test values (2,0)");

126

user_query("select count(*) from $opt_database.test");

127

safe_query("revoke SELECT on *.* from $user");

128

user_connect(0);

129

user_query("select count(*) from $opt_database.test",1);

130

user_query("INSERT INTO $opt_database.test values (3,0)");

131

safe_query("grant SELECT on *.* to $user");

132

user_connect(0);

133

user_query("select count(*) from $opt_database.test");

134

safe_query("revoke ALL PRIVILEGES on *.* from $user");

135

user_connect(1);

136

safe_query("delete from user where user='$opt_user'");

137

safe_query("flush privileges");

138

if (0) # Only if no anonymous user on localhost.

139

{

140

safe_query("grant select on *.* to $opt_user");

141

user_connect(0);

142

safe_query("revoke select on *.* from $opt_user");

143

user_connect(1);

144

}

145

safe_query("delete from user where user='$opt_user'");

146

safe_query("flush privileges");

147

148

#

149

# Test grants on database level

150

#

151

safe_query("grant select on $opt_database.* to $user");

152

safe_query("select * from mysql.user where user = '$opt_user'");

153

safe_query("select * from mysql.db where user = '$opt_user'");

154

user_connect(0);

155

user_query("select count(*) from $opt_database.test");

156

# The following should fail

157

user_query("select * from mysql.user where user = '$opt_user'",1);

158

user_query("insert into $opt_database.test values (4,0)",1);

159

user_query("update $opt_database.test set a=1",1);

160

user_query("delete from $opt_database.test",1);

161

user_query("create table $opt_database.test2 (a int)",1);

162

user_query("ALTER TABLE $opt_database.test add c int",1);

163

user_query("CREATE INDEX dummy ON $opt_database.test (a)",1);

164

user_query("drop table $opt_database.test",1);

165

user_query("grant ALL PRIVILEGES on $opt_database.* to ${opt_user}2\@$opt_host",1);

166

167

# Change privileges for user

168

safe_query("grant ALL PRIVILEGES on $opt_database.* to $user WITH GRANT OPTION");

169

user_connect(0);

170

user_query("insert into $opt_database.test values (5,0)");

171

safe_query("REVOKE ALL PRIVILEGES on * from $user",1);

172

safe_query("REVOKE ALL PRIVILEGES on *.* from $user");

173

safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user");

174

safe_query("REVOKE ALL PRIVILEGES on $opt_database.* from $user");

175

user_connect(0);

176

user_query("insert into $opt_database.test values (6,0)",1);

177

safe_query("REVOKE GRANT OPTION on $opt_database.* from $user");

178

user_connect(1);

179

safe_query("grant ALL PRIVILEGES on $opt_database.* to $user");

180

181

user_connect(0);

182

user_query("select * from mysql.user where user = '$opt_user'",1);

183

user_query("insert into $opt_database.test values (7,0)");

184

user_query("update $opt_database.test set a=3 where a=2");

185

user_query("delete from $opt_database.test where a=3");

186

user_query("create table $opt_database.test2 (a int not null)");

187

user_query("alter table $opt_database.test2 add b int");

188

user_query("create index dummy on $opt_database.test2 (a)");

189

user_query("update test,test2 SET test.a=test2.a where test.a=test2.a");

190

user_query("drop table $opt_database.test2");

191

user_query("show tables from grant_test");

192

# These should fail

193

user_query("insert into mysql.user (host,user) values ('error','$opt_user',0)",1);

194

195

# Revoke database privileges

196

safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user");

197

safe_query("select * from mysql.user where user = '$opt_user'");

198

safe_query("select * from mysql.db where user = '$opt_user'");

199

200

# Test multi-updates

201

safe_query("grant CREATE,UPDATE,DROP on $opt_database.* to $user");

202

user_connect(0);

203

user_query("create table $opt_database.test2 (a int not null)");

204

user_query("update test,test2 SET test.a=1 where 1",1);

205

user_query("update test,test2 SET test.a=test2.a where 1",1);

206

safe_query("grant SELECT on $opt_database.* to $user");

207

user_connect(0);

208

user_query("update test,test2 SET test.a=test2.a where test2.a=test.a");

209

user_query("drop table $opt_database.test2");

210

211

# Revoke database privileges

212

safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user");

213

user_connect(1);

214

215

#

216

# Test of grants on table level

217

#

218

219

safe_query("grant create on $opt_database.test2 to $user");

220

user_connect(0);

221

user_query("create table $opt_database.test2 (a int not null)");

222

user_query("show tables"); # Should only show test, not test2

223

user_query("show columns from test",1);

224

user_query("show keys from test",1);

225

user_query("show columns from test2");

226

user_query("show keys from test2");

227

user_query("select * from test",1);

228

safe_query("grant insert on $opt_database.test to $user");

229

user_query("show tables");

230

user_query("insert into $opt_database.test values (8,0)");

231

user_query("update $opt_database.test set b=1",1);

232

safe_query("grant update on $opt_database.test to $user");

233

user_query("update $opt_database.test set b=2");

234

235

user_query("update $opt_database.test,test2 SET test.b=3",1);

236

safe_query("grant select on $opt_database.test2 to $user");

237

user_query("update $opt_database.test,test2 SET test.b=3");

238

safe_query("revoke select on $opt_database.test2 from $user");

239

240

user_query("delete from $opt_database.test",1);

241

safe_query("grant delete on $opt_database.test to $user");

242

user_query("delete from $opt_database.test where a=1",1);

243

user_query("update $opt_database.test set b=3 where b=1",1);

244

user_query("update $opt_database.test set b=b+1",1);

245

user_query("update $opt_database.test,test2 SET test.a=test2.a",1);

246

247

#

248

# Test global SELECT privilege combined with table level privileges

249

#

250

251

safe_query("grant SELECT on *.* to $user");

252

user_connect(0);

253

user_query("update $opt_database.test set b=b+1");

254

user_query("update $opt_database.test set b=b+1 where a > 0");

255

user_query("update $opt_database.test,test2 SET test.a=test2.a");

256

user_query("update $opt_database.test,test2 SET test2.a=test.a",1);

257

safe_query("revoke SELECT on *.* from $user");

258

safe_query("grant SELECT on $opt_database.* to $user");

259

user_connect(0);

260

user_query("update $opt_database.test set b=b+1");

261

user_query("update $opt_database.test set b=b+1 where a > 0");

262

safe_query("grant UPDATE on *.* to $user");

263

user_connect(0);

264

user_query("update $opt_database.test set b=b+1");

265

user_query("update $opt_database.test set b=b+1 where a > 0");

266

safe_query("revoke UPDATE on *.* from $user");

267

safe_query("revoke SELECT on $opt_database.* from $user");

268

user_connect(0);

269

user_query("update $opt_database.test set b=b+1 where a > 0",1);

270

user_query("update $opt_database.test set b=b+1",1);

271

272

# Add one privilege at a time until the user has all privileges

273

user_query("select * from test",1);

274

safe_query("grant select on $opt_database.test to $user");

275

user_query("delete from $opt_database.test where a=1");

276

user_query("update $opt_database.test set b=2 where b=1");

277

user_query("update $opt_database.test set b=b+1");

278

user_query("select count(*) from test");

279

user_query("update test,test2 SET test.b=4",1);

280

user_query("update test,test2 SET test2.a=test.a",1);

281

user_query("update test,test2 SET test.a=test2.a",1);

282

283

user_query("create table $opt_database.test3 (a int)",1);

284

user_query("alter table $opt_database.test2 add c int",1);

285

safe_query("grant alter on $opt_database.test2 to $user");

286

user_query("alter table $opt_database.test2 add c int");

287

user_query("create index dummy ON $opt_database.test (a)",1);

288

safe_query("grant index on $opt_database.test2 to $user");

289

user_query("create index dummy ON $opt_database.test2 (a)");

290

user_query("insert into test2 SELECT a,a from test",1);

291

safe_query("grant insert on test2 to $user",1); # No table: mysql.test2

292

safe_query("grant insert(a) on $opt_database.test2 to $user");

293

user_query("insert into test2 SELECT a,a from test",1);

294

safe_query("grant insert(c) on $opt_database.test2 to $user");

295

user_query("insert into test2 SELECT a,a from test");

296

user_query("select count(*) from test2,test",1);

297

user_query("select count(*) from test,test2",1);

298

user_query("replace into test2 SELECT a from test",1);

299

safe_query("grant update on $opt_database.test2 to $user");

300

user_query("update test,test2 SET test2.a=test.a");

301

user_query("update test,test2 SET test.b=test2.a where 0",1);

302

user_query("update test,test2 SET test.a=2 where test2.a>100",1);

303

user_query("update test,test2 SET test.a=test2.a",1);

304

user_query("replace into test2 SELECT a,a from test",1);

305

safe_query("grant DELETE on $opt_database.test2 to $user");

306

user_query("replace into test2 SELECT a,a from test");

307

user_query("insert into test (a) SELECT a from test2",1);

308

safe_query("grant SELECT on $opt_database.test2 to $user");

309

user_query("update test,test2 SET test.b=test2.a where 0");

310

user_query("update test,test2 SET test.a=test2.a where test2.a>100");

311

312

safe_query("revoke UPDATE on $opt_database.test2 from $user");

313

safe_query("grant UPDATE (c) on $opt_database.test2 to $user");

314

user_query("update test,test2 SET test.b=test2.a where 0");

315

user_query("update test,test2 SET test.a=test2.a where test2.a>100");

316

user_query("update test,test2 SET test2.a=test2.a where test2.a>100",1);

317

user_query("update test,test2 SET test2.c=test2.a where test2.a>100");

318

319

safe_query("revoke SELECT,UPDATE on $opt_database.test2 from $user");

320

safe_query("grant UPDATE on $opt_database.test2 to $user");

321

322

user_query("drop table $opt_database.test2",1);

323

user_query("grant select on $opt_database.test2 to $user with grant option",1);

324

safe_query("grant drop on $opt_database.test2 to $user with grant option");

325

user_query("grant drop on $opt_database.test2 to $user with grant option");

326

user_query("grant select on $opt_database.test2 to $user with grant option",1);

327

328

# check rename privileges

329

user_query("rename table $opt_database.test2 to $opt_database.test3",1);

330

safe_query("grant CREATE,DROP on $opt_database.test3 to $user");

331

user_query("rename table $opt_database.test2 to $opt_database.test3",1);

332

user_query("create table $opt_database.test3 (a int)");

333

safe_query("grant INSERT on $opt_database.test3 to $user");

334

user_query("drop table $opt_database.test3");

335

user_query("rename table $opt_database.test2 to $opt_database.test3");

336

user_query("rename table $opt_database.test3 to $opt_database.test2",1);

337

safe_query("grant ALTER on $opt_database.test3 to $user");

338

user_query("rename table $opt_database.test3 to $opt_database.test2");

339

safe_query("revoke DROP on $opt_database.test2 from $user");

340

user_query("rename table $opt_database.test2 to $opt_database.test3");

341

user_query("drop table if exists $opt_database.test2,$opt_database.test3",1);

342

safe_query("drop table if exists $opt_database.test2,$opt_database.test3");

343

344

# Check that the user doesn't have some user privileges

345

user_query("create database $opt_database",1);

346

user_query("drop database $opt_database",1);

347

user_query("flush tables",1);

348

safe_query("flush privileges");

349

350

safe_query("select $tables_cols from mysql.tables_priv");

351

safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user");

352

safe_query("revoke ALL PRIVILEGES on $opt_database.test2 from $user");

353

safe_query("revoke ALL PRIVILEGES on $opt_database.test3 from $user");

354

safe_query("revoke GRANT OPTION on $opt_database.test2 from $user");

355

safe_query("select $tables_cols from mysql.tables_priv");

356

user_query("select count(a) from test",1);

357

358

#

359

# Test some grants on column level

360

#

361

362

safe_query("grant create,update on $opt_database.test2 to $user");

363

user_query("create table $opt_database.test2 (a int not null)");

364

user_query("delete from $opt_database.test where a=2",1);

365

user_query("delete from $opt_database.test where A=2",1);

366

user_query("update test set b=5 where b>0",1);

367

user_query("update test,test2 SET test.b=5 where b>0",1);

368

369

safe_query("grant update(b),delete on $opt_database.test to $user");

370

safe_query("revoke update(a) on $opt_database.test from $user",1);

371

user_query("delete from $opt_database.test where a=2",1);

372

user_query("update test set b=5 where b>0",1);

373

safe_query("grant select(a),select(b) on $opt_database.test to $user");

374

user_query("delete from $opt_database.test where a=2");

375

user_query("delete from $opt_database.test where A=2");

376

user_query("update test set b=5 where b>0");

377

user_query("update test set a=11 where b>5",1);

378

user_query("update test,test2 SET test.b=5 where b>0",1);

379

user_query("update test,test2 SET test.a=11 where b>0",1);

380

user_query("update test,test2 SET test.b=test2.a where b>0",1);

381

user_query("update test,test2 SET test.b=11 where test2.a>0",1);

382

user_query("select a,A from test");

383

384

safe_query("select $tables_cols from mysql.tables_priv");

385

safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user");

386

safe_query("select $tables_cols from mysql.tables_priv");

387

safe_query("revoke GRANT OPTION on $opt_database.test from $user",1);

388

safe_query("drop table $opt_database.test2");

389

safe_query("revoke create,update on $opt_database.test2 from $user");

390

391

#

392

# Test grants on database level

393

#

394

395

safe_query("grant select(a) on $opt_database.test to $user");

396

user_query("show full columns from test");

397

safe_query("grant insert (b), update (b) on $opt_database.test to $user");

398

399

user_query("select count(a) from test");

400

user_query("select count(skr.a) from test as skr");

401

user_query("select count(a) from test where a > 5");

402

user_query("insert into test (b) values (5)");

403

user_query("insert into test (b) values (a)");

404

user_query("update test set b=3 where a > 0");

405

406

user_query("select * from test",1);

407

user_query("select b from test",1);

408

user_query("select a from test where b > 0",1);

409

user_query("insert into test (a) values (10)",1);

410

user_query("insert into test (b) values (b)",1);

411

user_query("insert into test (a,b) values (1,5)",1);

412

user_query("insert into test (b) values (1),(b)",1);

413

user_query("update test set b=3 where b > 0",1);

414

415

safe_query("select $tables_cols from mysql.tables_priv");

416

safe_query("select $columns_cols from mysql.columns_priv");

417

safe_query("revoke select(a), update (b) on $opt_database.test from $user");

418

safe_query("select $tables_cols from mysql.tables_priv");

419

safe_query("select $columns_cols from mysql.columns_priv");

420

421

user_query("select count(a) from test",1);

422

user_query("update test set b=4",1);

423

424

safe_query("grant select(a,b), update (a,b) on $opt_database.test to $user");

425

user_query("select count(a),count(b) from test where a+b > 0");

426

user_query("insert into test (b) values (9)");

427

user_query("update test set b=6 where b > 0");

428

429

safe_query("flush privileges"); # Test restoring privileges from disk

430

safe_query("select $tables_cols from mysql.tables_priv");

431

safe_query("select $columns_cols from mysql.columns_priv");

432

433

# Try mixing of table and database privileges

434

435

user_query("insert into test (a,b) values (12,12)",1);

436

safe_query("grant insert on $opt_database.* to $user");

437

user_connect(0);

438

user_query("insert into test (a,b) values (13,13)");

439

440

# This grants and revokes SELECT on different levels.

441

safe_query("revoke select(b) on $opt_database.test from $user");

442

user_query("select count(a) from test where a+b > 0",1);

443

user_query("update test set b=5 where a=2");

444

safe_query("grant select on $opt_database.test to $user");

445

user_connect(0);

446

user_query("select count(a) from test where a+b > 0");

447

safe_query("revoke select(b) on $opt_database.test from $user");

448

user_query("select count(a) from test where a+b > 0");

449

safe_query("revoke select on $opt_database.test from $user");

450

user_connect(0);

451

user_query("select count(a) from test where a+b > 0",1);

452

safe_query("grant select(a) on $opt_database.test to $user");

453

user_query("select count(a) from test where a+b > 0",1);

454

safe_query("grant select on *.* to $user");

455

user_connect(0);

456

user_query("select count(a) from test where a+b > 0");

457

safe_query("revoke select on *.* from $user");

458

safe_query("grant select(b) on $opt_database.test to $user");

459

user_connect(0);

460

user_query("select count(a) from test where a+b > 0");

461

462

463

safe_query("select * from mysql.db where user = '$opt_user'");

464

safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'");

465

safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'");

466

467

safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user");

468

user_query("select count(a) from test",1);

469

user_query("select * from mysql.user order by hostname",1);

470

safe_query("select * from mysql.db where user = '$opt_user'");

471

safe_query("select $tables_cols from mysql.tables_priv where user = '$opt_user'");

472

safe_query("select $columns_cols from mysql.columns_priv where user = '$opt_user'");

473

474

#

475

# Clear up privileges to make future tests easier

476

477

safe_query("delete from user where user='$opt_user'");

478

safe_query("delete from db where user='$opt_user'");

479

safe_query("flush privileges");

480

safe_query("show grants for $user",1);

481

482

#

483

# Test IDENTIFIED BY

484

#

485

486

safe_query("grant ALL PRIVILEGES on $opt_database.test to $user identified by 'dummy', ${opt_user}\@127.0.0.1 identified by 'dummy2'");

487

user_connect(0,"dummy");

488

safe_query("grant SELECT on $opt_database.* to $user identified by ''");

489

user_connect(0);

490

safe_query("revoke ALL PRIVILEGES on $opt_database.test from $user identified by '', ${opt_user}\@127.0.0.1 identified by 'dummy2'");

491

safe_query("revoke ALL PRIVILEGES on $opt_database.* from $user identified by ''");

492

493

safe_query("show grants for $user");

494

495

#

496

# Test bug reported in SELECT INTO OUTFILE

497

#

498

499

safe_query("create table $opt_database.test3 (a int, b int)");

500

safe_query("grant SELECT on $opt_database.test3 to $user");

501

safe_query("grant FILE on *.* to $user");

502

safe_query("insert into $opt_database.test3 values (1,1)");

503

user_connect(0);

504

user_query("select * into outfile '$tmp_table' from $opt_database.test3");

505

safe_query("revoke SELECT on $opt_database.test3 from $user");

506

safe_query("grant SELECT(a) on $opt_database.test3 to $user");

507

user_query("select a from $opt_database.test3");

508

user_query("select * from $opt_database.test3",1);

509

user_query("select a,b from $opt_database.test3",1);

510

user_query("select b from $opt_database.test3",1);

511

512

safe_query("revoke SELECT(a) on $opt_database.test3 from $user");

513

safe_query("revoke FILE on *.* from $user");

514

safe_query("drop table $opt_database.test3");

515

516

#

517

# Test privileges needed for LOCK TABLES

518

#

519

520

safe_query("create table $opt_database.test3 (a int)");

521

user_connect(1);

522

safe_query("grant INSERT on $opt_database.test3 to $user");

523

user_connect(0);

524

user_query("select * into outfile '$tmp_table' from $opt_database.test3",1);

525

safe_query("grant SELECT on $opt_database.test3 to $user");

526

user_connect(0);

527

user_query("LOCK TABLES $opt_database.test3 READ",1);

528

safe_query("grant LOCK TABLES on *.* to $user");

529

safe_query("show grants for $user");

530

safe_query("select * from mysql.user where user='$opt_user'");

531

user_connect(0);

532

user_query("LOCK TABLES $opt_database.test3 READ");

533

user_query("UNLOCK TABLES");

534

safe_query("revoke SELECT,INSERT,UPDATE,DELETE on $opt_database.test3 from $user");

535

user_connect(0);

536

safe_query("revoke LOCK TABLES on *.* from $user");

537

user_connect(1);

538

safe_query("drop table $opt_database.test3");

539

540

#

541

# test new privileges in 4.0.2

542

#

543

544

safe_query("show grants for $user");

545

safe_query("grant all on *.* to $user WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3");

546

safe_query("show grants for $user");

547

safe_query("revoke LOCK TABLES on *.* from $user");

548

safe_query("flush privileges");

549

safe_query("show grants for $user");

550

safe_query("revoke ALL PRIVILEGES on *.* from $user");

551

safe_query("show grants for $user");

552

553

#

554

# Clean up things

555

#

556

557

unlink($tmp_table);

558

safe_query("drop database $opt_database");

559

safe_query("delete from user where user='$opt_user'");

560

safe_query("delete from db where user='$opt_user'");

561

safe_query("delete from tables_priv");

562

safe_query("delete from columns_priv");

563

safe_query("flush privileges");

564

565

print "end of test\n";

566

exit 0;

567

568

sub usage

569

{

570

print <<EOF;

571

$0 Ver $version

572

573

This program tests that the GRANT commands works by creating a temporary

574

database ($opt_database) and user ($opt_user).

575

576

Options:

577

578

--database (Default $opt_database)

579

In which database the test tables are created.

580

581

--force

582

Don''t ask any question before starting this test.

583

584

--host='host name' (Default $opt_host)

585

Host name where the database server is located.

586

587

--Information

588

--help

589

Print this help

590

591

--password

592

Password for root-user.

593

594

--server='server name' (Default $opt_server)

595

Run the test on the given SQL server.

596

597

--user (Default $opt_user)

598

A non-existing user on which we will test the GRANT commands.

599

600

--verbose

601

Write all queries when we are execute them.

602

603

--root-user='user name' (Default $opt_root_user)

604

User with privileges to modify the 'mysql' database.

605

EOF

606

exit(0);

607

}

608

609

610

sub print_info

611

{

612

my $tmp;

613

print <<EOF;

614

This test will clear your table and column grant table and recreate the

615

$opt_database database !

616

All privileges for $user will be destroyed !

617

618

Don\'t run this test if you have done any GRANT commands that you want to keep!

619

EOF

620

for (;;)

621

{

622

print "Start test (yes/no) ? ";

623

$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);

624

last if ($tmp =~ /^yes$/i);

625

exit 1 if ($tmp =~ /^n/i);

626

print "\n";

627

}

628

}

629

630

631

sub user_connect

632

{

633

my ($ignore_error,$password)=@_;

634

$password="" if (!defined($password));

635

636

print "Connecting $opt_user\n" if ($opt_verbose);

637

$user_dbh->disconnect if (defined($user_dbh));

638

639

$user_dbh=DBI->connect("DBI:mysql:$opt_database:$opt_host",$opt_user,

640

$password, { PrintError => 0});

641

if (!$user_dbh)

642

{

643

if ($opt_verbose || !$ignore_error)

644

{

645

print "Error on connect: $DBI::errstr\n";

646

}

647

if (!$ignore_error)

648

{

649

die "The above should not have failed!";

650

}

651

}

652

elsif ($ignore_error)

653

{

654

die "Connect succeeded when it shouldn't have !\n";

655

}

656

}

657

658

sub safe_query

659

{

660

my ($query,$ignore_error)=@_;

661

if (do_query($dbh,$query, $ignore_error))

662

{

663

if (!defined($ignore_error))

664

{

665

die "The above should not have failed!";

666

}

667

}

668

elsif (defined($ignore_error) && $ignore_error == 1)

669

{

670

die "Query '$query' succeeded when it shouldn't have !\n";

671

}

672

}

673

674

675

sub user_query

676

{

677

my ($query,$ignore_error)=@_;

678

if (do_query($user_dbh,$query, $ignore_error))

679

{

680

if (!defined($ignore_error))

681

{

682

die "Query '$query' should not have failed!";

683

}

684

}

685

elsif (defined($ignore_error) && $ignore_error == 1)

686

{

687

die "Query '$query' succeeded when it shouldn't have !\n";

688

}

689

}

690

691

692

sub do_query

693

{

694

my ($my_dbh, $query, $ignore_error)=@_;

695

my ($sth, $row, $tab, $col, $found, $fatal_error);

696

697

print "$query\n" if ($opt_debug || $opt_verbose);

698

if (!($sth= $my_dbh->prepare($query)))

699

{

700

print "Error in prepare: $DBI::errstr\n";

701

return 1;

702

}

703

if (!$sth->execute)

704

{

705

$fatal_error= ($DBI::errstr =~ /parse error/);

706

if (!$ignore_error || ($opt_verbose && $ignore_error != 3) || $fatal_error)

707

{

708

print "Error in execute: $DBI::errstr\n";

709

}

710

die if ($fatal_error);

711

$sth->finish;

712

return 1;

713

}

714

$found=0;

715

if (!$opt_silent)

716

{

717

while (($row=$sth->fetchrow_arrayref))

718

{

719

$found=1;

720

$tab="";

721

foreach $col (@$row)

722

{

723

print $tab;

724

print defined($col) ? $col : "NULL";

725

$tab="\t";

726

}

727

print "\n";

728

}

729

print "\n" if ($found);

730

}

731

$sth->finish;

732

return 0;

733

}