~linuxjedi/drizzle/trunk-bug-667053 : revision 1

1

# suite/funcs_1/t/is_basics_mixed.test

2

#

3

# Checks of some basic properties of the INFORMATION_SCHEMA which are not

4

# related to a certain INFORMATION_SCHEMA table.

5

#

6

# This test should not check properties related to storage engines.

7

#

8

# Author:

9

# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of

10

# testsuite funcs_1

11

# Create this script based on older scripts and new code.

12

#

13

14

--source suite/funcs_1/datadict/datadict.pre

15

16

# $engine_type must point to storage engine which is all time available.

17

# The fastest engine should be preferred.

18

let $engine_type = MEMORY;

19

20

21

# The INFORMATION_SCHEMA database must exist.

22

SHOW DATABASES LIKE 'information_schema';

23

24

25

--echo #######################################################################

26

--echo # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported

27

--echo #######################################################################

28

# Ensure that USE INFORMATION_SCHEMA allows the user to switch to the

29

# INFORMATION_SCHEMA database, for query purposes only.

30

#

31

# Note: The "for query purposes only" is checked in other tests.

32

# High privileged user (root)

33

--echo # Switch to connection default

34

connection default;

35

USE test;

36

SELECT DATABASE();

37

USE information_schema;

38

SELECT DATABASE();

39

#

40

--error 0,ER_CANNOT_USER

41

DROP USER 'testuser1'@'localhost';

42

CREATE USER 'testuser1'@'localhost';

43

# Low privileged user

44

--echo # Establish connection testuser1 (user=testuser1)

45

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK

46

connect (testuser1, localhost, testuser1, , test);

47

SELECT DATABASE();

48

USE information_schema;

49

SELECT DATABASE();

50

#

51

--echo # Switch to connection default and close connection testuser1

52

connection default;

53

disconnect testuser1;

54

DROP USER 'testuser1'@'localhost';

55

56

57

--echo #######################################################################

58

--echo # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped.

59

--echo #######################################################################

60

--error ER_DBACCESS_DENIED_ERROR

61

DROP DATABASE information_schema;

62

63

64

--echo #######################################################################

65

--echo # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA.

66

--echo #######################################################################

67

--error ER_DBACCESS_DENIED_ERROR

68

CREATE DATABASE information_schema;

69

70

71

--echo ##################################################################################

72

--echo # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view

73

--echo ##################################################################################

74

# 3.2.1.6 Ensure that no user may create an INFORMATION_SCHEMA base table.

75

# 3.2.1.7 Ensure that no user may create an INFORMATION_SCHEMA view

76

#

77

78

# 1. High privileged user (root)

79

--echo # Switch to connection default (user=root)

80

connection default;

81

--source suite/funcs_1/datadict/basics_mixed1.inc

82

83

# 2. High privileged user (testuser1)

84

--error 0,ER_CANNOT_USER

85

DROP USER 'testuser1'@'localhost';

86

CREATE USER 'testuser1'@'localhost';

87

GRANT ALL ON *.* TO testuser1@localhost;

88

SHOW GRANTS FOR testuser1@localhost;

89

--echo # Establish connection testuser1 (user=testuser1)

90

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK

91

connect (testuser1, localhost, testuser1, , test);

92

--source suite/funcs_1/datadict/basics_mixed1.inc

93

94

--echo # Switch to connection default (user=root) and close connection testuser1

95

connection default;

96

disconnect testuser1;

97

DROP USER 'testuser1'@'localhost';

98

99

--echo ###############################################################################

100

--echo # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT

101

--echo ###############################################################################

102

# 3.2.1.1 Ensure that every INFORMATION_SCHEMA table can be queried with

103

# a SELECT statement, just as if it were an ordinary user-defined table.

104

# 3.2.1.2 Ensure that queries on an INFORMATION_SCHEMA table can accept all

105

# SELECT statement options and are always correctly evaluated.

106

#

107

# Some notes(mleich):

108

# - Currently here only a subset of select statement options is checked, it's

109

# still not possible to check here all possible options

110

# - The content of many INFORMATION_SCHEMA tables is checked in other tests.

111

# - We work here only with a subset of the columns of information_schema.tables

112

# because we want have a stable base (all time existing table, stable layout).

113

--disable_warnings

114

DROP DATABASE IF EXISTS db_datadict;

115

--enable_warnings

116

CREATE DATABASE db_datadict;

117

--replace_result $engine_type <some_engine>

118

eval

119

CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)

120

ENGINE = $engine_type;

121

--replace_result $engine_type <some_engine>

122

eval

123

CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)

124

ENGINE = $engine_type;

125

126

# SELECT *

127

--echo # Attention: The protocolling of the next result set is disabled.

128

--disable_result_log

129

SELECT * FROM information_schema.tables;

130

--enable_result_log

131

#

132

# SELECT <some columns> + WHERE

133

--sorted_result

134

SELECT table_name FROM information_schema.tables

135

WHERE table_schema = 'db_datadict';

136

#

137

# SELECT string_function(<some column>) + ORDER BY

138

SELECT LENGTH(table_name) FROM information_schema.tables

139

WHERE table_schema = 'db_datadict' ORDER BY table_name;

140

#

141

# SELECT aggregate_function(<some column>) + WHERE with LIKE

142

SELECT count(table_name) FROM information_schema.tables

143

WHERE table_schema LIKE 'db_datadic%';

144

#

145

# SELECT with addition in column list

146

--sorted_result

147

SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))

148

FROM information_schema.tables

149

WHERE table_schema = 'db_datadict';

150

#

151

# WHERE with IN + LIMIT

152

SELECT table_name FROM information_schema.tables

153

WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;

154

SELECT table_name FROM information_schema.tables

155

WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;

156

#

157

# WHERE with AND

158

SELECT table_name,table_schema AS my_col FROM information_schema.tables

159

WHERE table_name = 't1_first' AND table_schema = 'db_datadict';

160

#

161

# SELECT HIGH_PRIORITY + WHERE with OR

162

--sorted_result

163

SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables

164

WHERE table_name = 't1_first' OR table_name = 't1_second';

165

#

166

# Empty result set

167

SELECT 1 AS my_col FROM information_schema.tables

168

WHERE table_name = 't1_third';

169

#

170

# SELECT INTO USER VARIABLE

171

SELECT table_name,table_schema INTO @table_name,@table_schema

172

FROM information_schema.tables

173

WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;

174

SELECT @table_name,@table_schema;

175

#

176

# SELECT INTO OUTFILE

177

let $OUTFILE = $MYSQL_TMP_DIR/datadict.out;

178

--error 0,1

179

remove_file $OUTFILE;

180

--replace_result $OUTFILE <OUTFILE>

181

eval SELECT table_name,table_schema

182

INTO OUTFILE '$OUTFILE'

183

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

184

LINES TERMINATED BY '\n'

185

FROM information_schema.tables

186

WHERE table_schema = 'db_datadict' ORDER BY table_name;

187

cat_file $OUTFILE;

188

remove_file $OUTFILE;

189

#

190

# UNION

191

--sorted_result

192

SELECT table_name FROM information_schema.tables

193

WHERE table_name = 't1_first'

194

UNION ALL

195

SELECT table_name FROM information_schema.tables

196

WHERE table_name = 't1_second';

197

#

198

# DISTINCT + SUBQUERY

199

SELECT DISTINCT table_schema FROM information_schema.tables

200

WHERE table_name IN (SELECT table_name FROM information_schema.tables

201

WHERE table_schema = 'db_datadict')

202

ORDER BY table_name;

203

#

204

# JOIN

205

SELECT table_name FROM information_schema.tables t1

206

LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)

207

WHERE t2.table_schema = 'db_datadict'

208

ORDER BY table_name;

209

#

210

# No schema assigned in SELECT + we are in SCHEMA test

211

# --> The table tables does not exist

212

USE test;

213

--error ER_NO_SUCH_TABLE

214

SELECT * FROM tables;

215

216

217

--echo #########################################################################

218

--echo # Testcase 3.2.1.17+3.2.1.18

219

--echo #########################################################################

220

# 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH GRANT

221

# OPTION on every INFORMATION_SCHEMA table.

222

#

223

# 3.2.1.18: Ensure that the CREATE VIEW privilege on an INFORMATION_SCHEMA table

224

# may be granted to any user.

225

#

226

# Note (mleich): The requirements are to some extend outdated.

227

# Every user is allowed to SELECT on the INFORMATION_SCHEMA.

228

# But the result sets depend on the privileges of the user.

229

#

230

--disable_warnings

231

DROP DATABASE IF EXISTS db_datadict;

232

--enable_warnings

233

CREATE DATABASE db_datadict;

234

--replace_result $engine_type <some_engine>

235

eval

236

CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)

237

ENGINE = $engine_type;

238

SELECT * FROM db_datadict.t1;

239

240

--error 0,ER_CANNOT_USER

241

DROP USER 'testuser1'@'localhost';

242

CREATE USER 'testuser1'@'localhost';

243

--error 0,ER_CANNOT_USER

244

DROP USER 'testuser2'@'localhost';

245

CREATE USER 'testuser2'@'localhost';

246

GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost

247

WITH GRANT OPTION;

248

GRANT USAGE ON db_datadict.* TO testuser2@localhost;

249

FLUSH PRIVILEGES;

250

251

# Check 0: Reveal that GRANT <some privilege> ON INFORMATION_SCHEMA is no

252

# longer allowed.

253

--error ER_DBACCESS_DENIED_ERROR

254

GRANT SELECT on information_schema.* TO testuser1@localhost;

255

--error ER_DBACCESS_DENIED_ERROR

256

GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';

257

258

# Check 1: Show that a "simple" user (<> root) has the permission to SELECT

259

# on some INFORMATION_SCHEMA table.

260

--echo # Establish connection testuser1 (user=testuser1)

261

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK

262

connect (testuser1, localhost, testuser1, , db_datadict);

263

SELECT table_schema,table_name FROM information_schema.tables

264

WHERE table_schema = 'information_schema' AND table_name = 'tables';

265

266

# Check 2: Show the privileges of the user on some INFORMATION_SCHEMA tables.

267

SELECT * FROM information_schema.table_privileges

268

WHERE table_schema = 'information_schema';

269

SELECT * FROM information_schema.schema_privileges

270

WHERE table_schema = 'information_schema';

271

272

# Check 3: Show the following

273

# 1. If a simple user (testuser1) has the privilege to create a VIEW

274

# than this VIEW could use a SELECT on an INFORMATION_SCHEMA table.

275

# 2. This user (testuser1) is also able to GRANT the SELECT privilege

276

# on this VIEW to another user (testuser2).

277

# 3. The other user (testuser2) must be able to SELECT on this VIEW

278

# but gets a different result set than testuser1.

279

CREATE VIEW db_datadict.v2 AS

280

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE

281

FROM information_schema.tables WHERE table_schema = 'db_datadict';

282

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE

283

FROM db_datadict.v2;

284

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE

285

FROM information_schema.tables WHERE table_schema = 'db_datadict';

286

GRANT SELECT ON db_datadict.v2 to testuser2@localhost;

287

#

288

--echo # Establish connection testuser2 (user=testuser2)

289

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK

290

connect (testuser2, localhost, testuser2, , db_datadict);

291

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE

292

FROM db_datadict.v2;

293

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE

294

FROM information_schema.tables WHERE table_schema = 'db_datadict';

295

296

# Cleanup

297

--echo # Switch to connection default and close connections testuser1 and testuser2

298

connection default;

299

disconnect testuser1;

300

disconnect testuser2;

301

DROP USER 'testuser1'@'localhost';

302

DROP USER 'testuser2'@'localhost';

303

DROP DATABASE db_datadict;

304

305

306

--echo #########################################################################

307

--echo # Testcase 3.2.1.19

308

--echo #########################################################################

309

# Ensure that no other privilege on an INFORMATION_SCHEMA table is granted, or

310

# may be granted, to any user.

311

#

312

--error 0,ER_CANNOT_USER

313

DROP USER 'testuser1'@'localhost';

314

CREATE USER 'testuser1'@'localhost';

315

316

# Initial privileges on the INFORMATION_SCHEMA tables (empty result sets)

317

let $my_select1 = SELECT 'empty result set was expected' AS my_col

318

FROM information_schema.schema_privileges

319

WHERE table_schema = 'information_schema';

320

let $my_select2 = SELECT 'empty result set was expected' AS my_col

321

FROM information_schema.table_privileges

322

WHERE table_schema = 'information_schema';

323

let $my_select3 = SELECT 'empty result set was expected' AS my_col

324

FROM information_schema.column_privileges

325

WHERE table_schema = 'information_schema';

326

eval $my_select1;

327

eval $my_select2;

328

eval $my_select3;

329

330

#FIXME: check GRANT on IS

331

--error ER_DBACCESS_DENIED_ERROR

332

GRANT ALTER ON information_schema.*

333

TO 'testuser1'@'localhost';

334

335

#FIXME: check GRANT on IS

336

--error ER_DBACCESS_DENIED_ERROR

337

GRANT ALTER ROUTINE ON information_schema.*

338

TO 'testuser1'@'localhost';

339

340

#FIXME: check GRANT on IS

341

--error ER_DBACCESS_DENIED_ERROR

342

GRANT CREATE ON information_schema.*

343

TO 'testuser1'@'localhost';

344

345

#FIXME: check GRANT on IS

346

--error ER_DBACCESS_DENIED_ERROR

347

GRANT CREATE ROUTINE ON information_schema.*

348

TO 'testuser1'@'localhost';

349

350

#FIXME: check GRANT on IS

351

--error ER_DBACCESS_DENIED_ERROR

352

GRANT CREATE TEMPORARY TABLES ON information_schema.*

353

TO 'testuser1'@'localhost';

354

355

#FIXME: check GRANT on IS

356

--error ER_DBACCESS_DENIED_ERROR

357

GRANT DELETE ON information_schema.*

358

TO 'testuser1'@'localhost';

359

360

#FIXME: check GRANT on IS

361

--error ER_DBACCESS_DENIED_ERROR

362

GRANT DROP ON information_schema.*

363

TO 'testuser1'@'localhost';

364

365

#FIXME: check GRANT on IS

366

--error ER_DBACCESS_DENIED_ERROR

367

GRANT EXECUTE ON information_schema.*

368

TO 'testuser1'@'localhost';

369

370

#FIXME: check GRANT on IS

371

--error ER_DBACCESS_DENIED_ERROR

372

GRANT INDEX ON information_schema.*

373

TO 'testuser1'@'localhost';

374

375

#FIXME: check GRANT on IS

376

--error ER_DBACCESS_DENIED_ERROR

377

GRANT INSERT ON information_schema.*

378

TO 'testuser1'@'localhost';

379

380

#FIXME: check GRANT on IS

381

--error ER_DBACCESS_DENIED_ERROR

382

GRANT LOCK TABLES ON information_schema.*

383

TO 'testuser1'@'localhost';

384

385

#FIXME: check GRANT on IS

386

--error ER_DBACCESS_DENIED_ERROR

387

GRANT UPDATE ON information_schema.*

388

TO 'testuser1'@'localhost';

389

390

# Has something accidently changed?

391

eval $my_select1;

392

eval $my_select2;

393

eval $my_select3;

394

395

# Cleanup

396

DROP USER 'testuser1'@'localhost';

397

398

399

--echo #########################################################################

400

--echo # Testcase 3.2.1.16

401

--echo #########################################################################

402

# Ensure that no user may use any INFORMATION_SCHEMA table to determine any

403

# information on a database and/or its structure unless authorized to get that

404

# information.

405

# Note: The plan is to create a new database and objects within it so that

406

# any INFORMATION_SCHEMA table gets additional rows if possible.

407

# A user having no rights on the new database and no rights on objects

408

# must nowhere see tha name of the new database.

409

--source suite/funcs_1/datadict/basics_mixed3.inc

410

411

--disable_warnings

412

DROP DATABASE IF EXISTS db_datadict;

413

--enable_warnings

414

CREATE DATABASE db_datadict;

415

--replace_result $engine_type <some_engine>

416

eval

417

CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT,

418

PRIMARY KEY(f1))

419

ENGINE = $engine_type;

420

CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3);

421

CREATE PROCEDURE db_datadict.sproc1() SELECT 'db_datadict';

422

CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0;

423

CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1

424

FOR EACH ROW SET @aux = 1;

425

CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1;

426

CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables;

427

428

--source suite/funcs_1/datadict/basics_mixed3.inc

429

430

--error 0,ER_CANNOT_USER

431

DROP USER 'testuser1'@'localhost';

432

CREATE USER 'testuser1'@'localhost';

433

GRANT ALL ON test.* TO 'testuser1'@'localhost';

434

435

--echo # Establish connection testuser1 (user=testuser1)

436

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK

437

connect (testuser1, localhost, testuser1, , test);

438

--source suite/funcs_1/datadict/basics_mixed3.inc

439

440

# Cleanup

441

--echo # Switch to connection default and close connections testuser1 and testuser2

442

connection default;

443

disconnect testuser1;

444

DROP USER 'testuser1'@'localhost';

445

DROP DATABASE db_datadict;

446

447

--echo ########################################################################

448

--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and

449

--echo # DDL on INFORMATION_SCHEMA tables are not supported

450

--echo ########################################################################

451

# Thorough tests checking the requirements above per every INFORMATION_SCHEMA

452

# table are within other scripts.

453

# We check here only that the requirement is fulfilled even when using a

454

# STORED PROCEDURE.

455

--disable_warnings

456

DROP PROCEDURE IF EXISTS test.p1;

457

--enable_warnings

458

CREATE PROCEDURE test.p1()

459

INSERT INTO information_schema.tables

460

SELECT * FROM information_schema.tables LIMIT 1;

461

--error ER_DBACCESS_DENIED_ERROR

462

CALL test.p1();

463

464

DROP PROCEDURE test.p1;

465

CREATE PROCEDURE test.p1()

466

UPDATE information_schema.columns SET table_schema = 'garbage';

467

--error ER_DBACCESS_DENIED_ERROR

468

CALL test.p1();

469

470

DROP PROCEDURE test.p1;

471

CREATE PROCEDURE test.p1()

472

DELETE FROM information_schema.schemata;

473

--error ER_DBACCESS_DENIED_ERROR

474

CALL test.p1();

475

476

DROP PROCEDURE test.p1;

477

478

479

--echo #########################################################################

480

--echo # Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script

481

--echo #########################################################################

482

# 3.2.17.1 Ensure that every INFORMATION_SCHEMA table shows all the correct

483

# information, and no incorrect information, for a database to which

484

# 100 different users, each of which has a randomly issued set of

485

# privileges and access to a randomly chosen set of database objects,

486

# have access.

487

# The database should contain a mixture of all types of database

488

# objects (i.e. tables, views, stored procedures, triggers).

489

# 3.2.17.2 Ensure that every INFORMATION_SCHEMA table shows all the correct

490

# information, and no incorrect information, for 10 different

491

# databases to which 50 different users, each of which has a randomly

492

# issued set of privileges and access to a randomly chosen set of

493

# database objects in two or more of the databases, have access.

494

# The databases should each contain a mixture of all types of database

495

# objects (i.e. tables, views, stored procedures, triggers).

496

#

497

# Note(mleich): These requirements are kept here so that they do not get lost.

498

# The tests are not yet implemented.

499

# If they are ever developed than they should be stored in other

500

# scripts. They will have most probably a long runtime because

501

# the current INFORMATION_SCHEMA implementation has some performance

502

# issues if a lot of users, privileges and objects are involved.

503

#