~vkolesnikov/pbxt/pbxt-07-diskfull

Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

169

170

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;

171

TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER

172

NULL mysqltest_db1 trg1 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL

173

NULL mysqltest_db1 trg2 INSERT NULL mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL @

174

NULL mysqltest_db1 trg3 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL @abc@def@@

175

NULL mysqltest_db1 trg4 UPDATE NULL mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL @hostname

176

NULL mysqltest_db1 trg5 DELETE NULL mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL @abcdef@@@hostname

177

178

---> connection: default

179

DROP USER mysqltest_dfn@localhost;

180

DROP USER mysqltest_inv@localhost;

181

DROP DATABASE mysqltest_db1;

182

Warnings:

183

184

DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';

185

DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';

186

DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';

187

DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';

188

FLUSH PRIVILEGES;

189

DROP DATABASE IF EXISTS mysqltest_db1;

190

CREATE DATABASE mysqltest_db1;

191

use mysqltest_db1;

192

CREATE TABLE t1(col CHAR(20));

193

CREATE TABLE t2(col CHAR(20));

194

CREATE TABLE t3(col CHAR(20));

195

CREATE TABLE t4(col CHAR(20));

196

CREATE USER mysqltest_u1@localhost;

197

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

198

GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;

199

SET @mysqltest_var = NULL;

200

201

---> connection: default

202

use mysqltest_db1;

203

GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;

204

SHOW GRANTS FOR mysqltest_u1@localhost;

205

Grants for mysqltest_u1@localhost

206

GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'

207

GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

208

209

---> connection: bug15166_u1_con

210

use mysqltest_db1;

211

CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1

212

FOR EACH ROW

213

SET @mysqltest_var = 'Hello, world!';

214

215

---> connection: default

216

use mysqltest_db1;

217

GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

218

GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;

219

GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;

220

GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;

221

222

---> connection: bug15166_u1_con

223

use mysqltest_db1;

224

CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1

225

FOR EACH ROW

226

SET @mysqltest_var = NEW.col;

227

DROP TRIGGER t1_trg_err_1;

228

CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1

229

FOR EACH ROW

230

SET @mysqltest_var = OLD.col;

231

DROP TRIGGER t1_trg_err_2;

232

CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2

233

FOR EACH ROW

234

SET NEW.col = 't2_trg_before_insert';

235

CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3

236

FOR EACH ROW

237

SET @mysqltest_var = NEW.col;

238

DROP TRIGGER t3_trg_err_1;

239

CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3

240

FOR EACH ROW

241

SET @mysqltest_var = OLD.col;

242

DROP TRIGGER t3_trg_err_2;

243

CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4

244

FOR EACH ROW

245

SET NEW.col = 't4_trg_before_insert';

246

247

---> connection: default

248

use mysqltest_db1;

249

REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;

250

REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;

251

GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

252

GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;

253

REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;

254

REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;

255

GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;

256

GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;

257

258

---> connection: bug15166_u1_con

259

use mysqltest_db1;

260

CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1

261

FOR EACH ROW

262

SET @mysqltest_var = NEW.col;

263

CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1

264

FOR EACH ROW

265

SET @mysqltest_var = OLD.col;

266

CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2

267

FOR EACH ROW

268

SET NEW.col = 't2_trg_err_1';

269

DROP TRIGGER t2_trg_err_1;

270

CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2

271

FOR EACH ROW

272

SET NEW.col = CONCAT(OLD.col, '(updated)');

273

DROP TRIGGER t2_trg_err_2;

274

CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3

275

FOR EACH ROW

276

SET @mysqltest_var = NEW.col;

277

CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3

278

FOR EACH ROW

279

SET @mysqltest_var = OLD.col;

280

CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4

281

FOR EACH ROW

282

SET NEW.col = 't4_trg_err_1';

283

DROP TRIGGER t4_trg_err_1;

284

CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4

285

FOR EACH ROW

286

SET NEW.col = CONCAT(OLD.col, '(updated)');

287

DROP TRIGGER t4_trg_err_2;

288

289

---> connection: default

290

use mysqltest_db1;

291

REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;

292

REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;

293

GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

294

GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;

295

REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;

296

REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;

297

GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;

298

GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;

299

INSERT INTO t1 VALUES('line1');

300

ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1'

301

SELECT * FROM t1;

302

col

303

SELECT @mysqltest_var;

304

@mysqltest_var

305

NULL

306

INSERT INTO t2 VALUES('line2');

307

SELECT * FROM t2;

308

col

309

t2_trg_before_insert

310

INSERT INTO t3 VALUES('t3_line1');

311

ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3'

312

SELECT * FROM t3;

313

col

314

SELECT @mysqltest_var;

315

@mysqltest_var

316

NULL

317

INSERT INTO t4 VALUES('t4_line2');

318

SELECT * FROM t4;

319

col

320

t4_trg_before_insert

321

322

---> connection: default

323

use mysqltest_db1;

324

REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;

325

REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;

326

GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

327

GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;

328

REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;

329

REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;

330

GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;

331

GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;

332

INSERT INTO t1 VALUES('line3');

333

SELECT * FROM t1;

334

col

335

line3

336

SELECT @mysqltest_var;

337

@mysqltest_var

338

line3

339

INSERT INTO t2 VALUES('line4');

340

ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2'

341

SELECT * FROM t2;

342

col

343

t2_trg_before_insert

344

INSERT INTO t3 VALUES('t3_line2');

345

SELECT * FROM t3;

346

col

347

t3_line2

348

SELECT @mysqltest_var;

349

@mysqltest_var

350

t3_line2

351

INSERT INTO t4 VALUES('t4_line2');

352

ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4'

353

SELECT * FROM t4;

354

col

355

t4_trg_before_insert

356

DELETE FROM t1;

357

SELECT @mysqltest_var;

358

@mysqltest_var

359

Hello, world!

360

DROP USER mysqltest_u1@localhost;

361

DROP DATABASE mysqltest_db1;

362

DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';

363

DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';

364

DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';

365

DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';

366

FLUSH PRIVILEGES;

367

DROP DATABASE IF EXISTS mysqltest_db1;

368

CREATE DATABASE mysqltest_db1;

369

USE mysqltest_db1;

370

CREATE TABLE t1 (i1 INT);

371

CREATE TABLE t2 (i1 INT);

372

CREATE USER mysqltest_dfn@localhost;

373

CREATE USER mysqltest_inv@localhost;

374

GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;

375

GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;

376

CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;

377

CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;

378

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

379

CALL p1(NEW.i1);

380

CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW

381

CALL p2(NEW.i1);

382

INSERT INTO t1 VALUES (7);

383

ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'

384

INSERT INTO t2 VALUES (11);

385

ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'

386

DROP TRIGGER t2_bi;

387

DROP TRIGGER t1_bi;

388

GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;

389

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

390

CALL p1(NEW.i1);

391

CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW

392

CALL p2(NEW.i1);

393

INSERT INTO t1 VALUES (13);

394

ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'

395

INSERT INTO t2 VALUES (17);

396

ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'

397

REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;

398

DROP TRIGGER t2_bi;

399

DROP TRIGGER t1_bi;

400

GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;

401

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

402

CALL p1(NEW.i1);

403

CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW

404

CALL p2(NEW.i1);

405

INSERT INTO t1 VALUES (19);

406

INSERT INTO t2 VALUES (23);

407

ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'

408

REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;

409

DROP TRIGGER t2_bi;

410

DROP TRIGGER t1_bi;

411

GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;

412

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

413

CALL p1(NEW.i1);

414

CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW

415

CALL p2(NEW.i1);

416

INSERT INTO t1 VALUES (29);

417

INSERT INTO t2 VALUES (31);

418

REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;

419

DROP TRIGGER t2_bi;

420

DROP TRIGGER t1_bi;

421

DROP PROCEDURE p2;

422

DROP PROCEDURE p1;

423

GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;

424

CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;

425

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

426

CALL p1(NEW.i1);

427

INSERT INTO t1 VALUES (41);

428

DROP PROCEDURE p1;

429

CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;

430

INSERT INTO t1 VALUES (47);

431

ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'

432

DROP PROCEDURE p1;

433

CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;

434

INSERT INTO t1 VALUES (53);

435

ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'

436

DROP PROCEDURE p1;

437

REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;

438

DROP TRIGGER t1_bi;

439

DROP USER mysqltest_inv@localhost;

440

DROP USER mysqltest_dfn@localhost;

441

DROP TABLE t2;

442

DROP TABLE t1;

443

DROP DATABASE mysqltest_db1;

444

USE test;

445

End of 5.0 tests.

Older »