~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION

215

revoke all privileges on mysqltest.* from mysqltest_1@localhost;

216

delete from mysql.user where user='mysqltest_1';

217

flush privileges;

218

grant usage on test.* to mysqltest_1@localhost with grant option;

219

show grants for mysqltest_1@localhost;

220

Grants for mysqltest_1@localhost

221

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

222

GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION

223

GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION

224

delete from mysql.user where user='mysqltest_1';

225

delete from mysql.db where user='mysqltest_1';

226

delete from mysql.tables_priv where user='mysqltest_1';

227

delete from mysql.columns_priv where user='mysqltest_1';

228

flush privileges;

229

show grants for mysqltest_1@localhost;

230

ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host 'localhost'

231

create table t1 (a int);

232

GRANT select,update,insert on t1 to mysqltest_1@localhost;

233

GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;

234

show grants for mysqltest_1@localhost;

235

Grants for mysqltest_1@localhost

236

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

237

GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'

238

select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";

239

table_priv column_priv

240

Select,Insert,Update Select,Insert,Update,References

241

REVOKE select (a), update on t1 from mysqltest_1@localhost;

242

show grants for mysqltest_1@localhost;

243

Grants for mysqltest_1@localhost

244

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

245

GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'

246

REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;

247

show grants for mysqltest_1@localhost;

248

Grants for mysqltest_1@localhost

249

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

250

GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'

251

GRANT select,references on t1 to mysqltest_1@localhost;

252

select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";

253

table_priv column_priv

254

Select,References References

255

grant all on test.* to mysqltest_3@localhost with grant option;

256

revoke all on test.* from mysqltest_3@localhost;

257

show grants for mysqltest_3@localhost;

258

Grants for mysqltest_3@localhost

259

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

260

GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION

261

revoke grant option on test.* from mysqltest_3@localhost;

262

show grants for mysqltest_3@localhost;

263

Grants for mysqltest_3@localhost

264

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

265

grant all on test.t1 to mysqltest_2@localhost with grant option;

266

revoke all on test.t1 from mysqltest_2@localhost;

267

show grants for mysqltest_2@localhost;

268

Grants for mysqltest_2@localhost

269

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

270

GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION

271

revoke grant option on test.t1 from mysqltest_2@localhost;

272

show grants for mysqltest_2@localhost;

273

Grants for mysqltest_2@localhost

274

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

275

delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";

276

delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";

277

delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";

278

delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";

279

flush privileges;

280

drop table t1;

281

GRANT FILE on mysqltest.* to mysqltest_1@localhost;

282

ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

283

select 1;

284

285

286

insert into mysql.user (host, user) values ('localhost', 'test11');

287

Warnings:

288

Warning 1364 Field 'ssl_cipher' doesn't have a default value

289

Warning 1364 Field 'x509_issuer' doesn't have a default value

290

Warning 1364 Field 'x509_subject' doesn't have a default value

291

insert into mysql.db (host, db, user, select_priv) values

292

('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');

293

alter table mysql.db order by db asc;

294

flush privileges;

295

show grants for test11@localhost;

296

Grants for test11@localhost

297

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

298

GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'

299

GRANT SELECT ON `a%`.* TO 'test11'@'localhost'

300

alter table mysql.db order by db desc;

301

flush privileges;

302

show grants for test11@localhost;

303

Grants for test11@localhost

304

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

305

GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'

306

GRANT SELECT ON `a%`.* TO 'test11'@'localhost'

307

delete from mysql.user where user='test11';

308

delete from mysql.db where user='test11';

309

create database mysqltest1;

310

grant usage on mysqltest1.* to test6123 identified by 'magic123';

311

select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";

312

host db user select_priv insert_priv

313

delete from mysql.user where user='test6123';

314

drop database mysqltest1;

315

create table t1 (a int);

316

grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;

317

show grants for drop_user2@localhost;

318

Grants for drop_user2@localhost

319

GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION

320

revoke all privileges, grant option from drop_user2@localhost;

321

drop user drop_user2@localhost;

322

grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;

323

grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;

324

grant select(a) on test.t1 to drop_user@localhost;

325

show grants for drop_user@localhost;

326

Grants for drop_user@localhost

327

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

328

GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION

329

GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'

330

set sql_mode=ansi_quotes;

331

show grants for drop_user@localhost;

332

Grants for drop_user@localhost

333

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

334

GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION

335

GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'

336

set sql_mode=default;

337

set sql_quote_show_create=0;

338

show grants for drop_user@localhost;

339

Grants for drop_user@localhost

340

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

341

GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION

342

GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'

343

set sql_mode="ansi_quotes";

344

show grants for drop_user@localhost;

345

Grants for drop_user@localhost

346

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

347

GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION

348

GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'

349

set sql_quote_show_create=1;

350

show grants for drop_user@localhost;

351

Grants for drop_user@localhost

352

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

353

GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION

354

GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'

355

set sql_mode="";

356

show grants for drop_user@localhost;

357

Grants for drop_user@localhost

358

GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION

359

GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION

360

GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'

361

revoke all privileges, grant option from drop_user@localhost;

362

show grants for drop_user@localhost;

363

Grants for drop_user@localhost

364

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

365

drop user drop_user@localhost;

366

revoke all privileges, grant option from drop_user@localhost;

367

ERROR HY000: Can't revoke all privileges for one or more of the requested users

368

grant select(a) on test.t1 to drop_user1@localhost;

369

grant select on test.t1 to drop_user2@localhost;

370

grant select on test.* to drop_user3@localhost;

371

grant select on *.* to drop_user4@localhost;

372

drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,

373

drop_user4@localhost;

374

revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,

375

drop_user3@localhost, drop_user4@localhost;

376

ERROR HY000: Can't revoke all privileges for one or more of the requested users

377

drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,

378

drop_user4@localhost;

379

ERROR HY000: Operation DROP USER failed for 'drop_user1'@'localhost','drop_user2'@'localhost','drop_user3'@'localhost','drop_user4'@'localhost'

380

drop table t1;

381

grant usage on *.* to mysqltest_1@localhost identified by "password";

382

grant select, update, insert on test.* to mysqltest_1@localhost;

383

show grants for mysqltest_1@localhost;

384

Grants for mysqltest_1@localhost

385

GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'

386

GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'mysqltest_1'@'localhost'

387

drop user mysqltest_1@localhost;

388

SET NAMES koi8r;

389

CREATE DATABASE ��;

390

USE ��;

391

CREATE TABLE �� (�� INT);

392

GRANT SELECT ON ��.* TO ��@localhost;

393

SHOW GRANTS FOR ��@localhost;

394

Grants for ��@localhost

395

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

396

GRANT SELECT ON `��`.* TO '��'@'localhost'

397

REVOKE SELECT ON ��.* FROM ��@localhost;

398

GRANT SELECT ON ��.�� TO ��@localhost;

399

SHOW GRANTS FOR ��@localhost;

400

Grants for ��@localhost

401

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

402

GRANT SELECT ON `��`.`��` TO '��'@'localhost'

403

REVOKE SELECT ON ��.�� FROM ��@localhost;

404

GRANT SELECT (��) ON ��.�� TO ��@localhost;

405

SHOW GRANTS FOR ��@localhost;

406

Grants for ��@localhost

407

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

408

GRANT SELECT (��) ON `��`.`��` TO '��'@'localhost'

409

REVOKE SELECT (��) ON ��.�� FROM ��@localhost;

410

DROP USER ��@localhost;

411

DROP DATABASE ��;

412

SET NAMES latin1;

413

USE test;

414

CREATE TABLE t1 (a int );

415

CREATE TABLE t2 LIKE t1;

416

CREATE TABLE t3 LIKE t1;

417

CREATE TABLE t4 LIKE t1;

418

CREATE TABLE t5 LIKE t1;

419

CREATE TABLE t6 LIKE t1;

420

CREATE TABLE t7 LIKE t1;

421

CREATE TABLE t8 LIKE t1;

422

CREATE TABLE t9 LIKE t1;

423

CREATE TABLE t10 LIKE t1;

424

CREATE DATABASE testdb1;

425

CREATE DATABASE testdb2;

426

CREATE DATABASE testdb3;

427

CREATE DATABASE testdb4;

428

CREATE DATABASE testdb5;

429

CREATE DATABASE testdb6;

430

CREATE DATABASE testdb7;

431

CREATE DATABASE testdb8;

432

CREATE DATABASE testdb9;

433

CREATE DATABASE testdb10;

434

GRANT ALL ON testdb1.* TO testuser@localhost;

435

GRANT ALL ON testdb2.* TO testuser@localhost;

436

GRANT ALL ON testdb3.* TO testuser@localhost;

437

GRANT ALL ON testdb4.* TO testuser@localhost;

438

GRANT ALL ON testdb5.* TO testuser@localhost;

439

GRANT ALL ON testdb6.* TO testuser@localhost;

440

GRANT ALL ON testdb7.* TO testuser@localhost;

441

GRANT ALL ON testdb8.* TO testuser@localhost;

442

GRANT ALL ON testdb9.* TO testuser@localhost;

443

GRANT ALL ON testdb10.* TO testuser@localhost;

444

GRANT SELECT ON test.t1 TO testuser@localhost;

445

GRANT SELECT ON test.t2 TO testuser@localhost;

446

GRANT SELECT ON test.t3 TO testuser@localhost;

447

GRANT SELECT ON test.t4 TO testuser@localhost;

448

GRANT SELECT ON test.t5 TO testuser@localhost;

449

GRANT SELECT ON test.t6 TO testuser@localhost;

450

GRANT SELECT ON test.t7 TO testuser@localhost;

451

GRANT SELECT ON test.t8 TO testuser@localhost;

452

GRANT SELECT ON test.t9 TO testuser@localhost;

453

GRANT SELECT ON test.t10 TO testuser@localhost;

454

GRANT SELECT (a) ON test.t1 TO testuser@localhost;

455

GRANT SELECT (a) ON test.t2 TO testuser@localhost;

456

GRANT SELECT (a) ON test.t3 TO testuser@localhost;

457

GRANT SELECT (a) ON test.t4 TO testuser@localhost;

458

GRANT SELECT (a) ON test.t5 TO testuser@localhost;

459

GRANT SELECT (a) ON test.t6 TO testuser@localhost;

460

GRANT SELECT (a) ON test.t7 TO testuser@localhost;

461

GRANT SELECT (a) ON test.t8 TO testuser@localhost;

462

GRANT SELECT (a) ON test.t9 TO testuser@localhost;

463

GRANT SELECT (a) ON test.t10 TO testuser@localhost;

464

REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;

465

SHOW GRANTS FOR testuser@localhost;

466

Grants for testuser@localhost

467

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

468

DROP USER testuser@localhost;

469

DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;

470

DROP DATABASE testdb1;

471

DROP DATABASE testdb2;

472

DROP DATABASE testdb3;

473

DROP DATABASE testdb4;

474

DROP DATABASE testdb5;

475

DROP DATABASE testdb6;

476

DROP DATABASE testdb7;

477

DROP DATABASE testdb8;

478

DROP DATABASE testdb9;

479

DROP DATABASE testdb10;

480

create table t1(a int, b int, c int, d int);

481

grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;

482

show grants for grant_user@localhost;

483

Grants for grant_user@localhost

484

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

485

GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost'

486

select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;

487

Host Db User Table_name Column_name Column_priv

488

localhost test grant_user t1 a Insert

489

localhost test grant_user t1 b Insert

490

localhost test grant_user t1 c Insert

491

localhost test grant_user t1 d Insert

492

revoke ALL PRIVILEGES on t1 from grant_user@localhost;

493

show grants for grant_user@localhost;

494

Grants for grant_user@localhost

495

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

496

select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;

497

Host Db User Table_name Column_name Column_priv

498

drop user grant_user@localhost;

499

drop table t1;

500

create database mysqltest_1;

501

create database mysqltest_2;

502

create table mysqltest_1.t1 select 1 a, 2 q;

503

create table mysqltest_1.t2 select 1 b, 2 r;

504

create table mysqltest_2.t1 select 1 c, 2 s;

505

create table mysqltest_2.t2 select 1 d, 2 t;

506

grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;

507

grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;

508

grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;

509

grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;

510

SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

511

WHERE GRANTEE = '''mysqltest_3''@''localhost'''

512

ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;

513

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE

514

'mysqltest_3'@'localhost' def mysqltest_1 t1 a UPDATE NO

515

'mysqltest_3'@'localhost' def mysqltest_2 t1 c SELECT NO

516

'mysqltest_3'@'localhost' def mysqltest_1 t2 b SELECT NO

517

'mysqltest_3'@'localhost' def mysqltest_2 t2 d UPDATE NO

518

SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

519

WHERE GRANTEE = '''mysqltest_3''@''localhost'''

520

ORDER BY TABLE_NAME,PRIVILEGE_TYPE;

521

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE

522

SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES

523

WHERE GRANTEE = '''mysqltest_3''@''localhost'''

524

ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;

525

GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE

526

SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES

527

WHERE GRANTEE = '''mysqltest_3''@''localhost'''

528

ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;

529

GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE

530

'mysqltest_3'@'localhost' def USAGE NO

531

update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;

532

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

533

update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;

534

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

535

update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;

536

ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'

537

update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;

538

ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'

539

update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;

540

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

541

update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;

542

update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;

543

select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;

544

a q b r

545

10 2 1 2

546

select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;

547

c s d t

548

1 2 10 2

549

revoke all on mysqltest_1.t1 from mysqltest_3@localhost;

550

revoke all on mysqltest_1.t2 from mysqltest_3@localhost;

551

revoke all on mysqltest_2.t1 from mysqltest_3@localhost;

552

revoke all on mysqltest_2.t2 from mysqltest_3@localhost;

553

grant all on mysqltest_2.* to mysqltest_3@localhost;

554

grant select on *.* to mysqltest_3@localhost;

555

grant select on mysqltest_2.t1 to mysqltest_3@localhost;

556

flush privileges;

557

use mysqltest_1;

558

update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;

559

update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;

560

ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'

561

use mysqltest_2;

562

update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;

563

ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'

564

update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;

565

ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't2'

566

update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;

567

ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'

568

select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;

569

a q b r

570

10 2 1 2

571

select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;

572

c s d t

573

500 2 600 2

574

delete from mysql.user where user='mysqltest_3';

575

delete from mysql.db where user="mysqltest_3";

576

delete from mysql.tables_priv where user="mysqltest_3";

577

delete from mysql.columns_priv where user="mysqltest_3";

578

flush privileges;

579

drop database mysqltest_1;

580

drop database mysqltest_2;

581

SHOW PRIVILEGES;

582

Privilege Context Comment

583

Alter Tables To alter the table

584

Alter routine Functions,Procedures To alter or drop stored functions/procedures

585

Create Databases,Tables,Indexes To create new databases and tables

586

Create routine Databases To use CREATE FUNCTION/PROCEDURE

587

Create temporary tables Databases To use CREATE TEMPORARY TABLE

588

Create view Tables To create new views

589

Create user Server Admin To create new users

590

Delete Tables To delete existing rows

591

Drop Databases,Tables To drop databases, tables, and views

592

Event Server Admin To create, alter, drop and execute events

593

Execute Functions,Procedures To execute stored routines

594

File File access on server To read and write files on the server

595

Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess

596

Index Tables To create or drop indexes

597

Insert Tables To insert data into tables

598

Lock tables Databases To use LOCK TABLES (together with SELECT privilege)

599

Process Server Admin To view the plain text of currently executing queries

600

Proxy Server Admin To make proxy user possible

601

References Databases,Tables To have references on tables

602

Reload Server Admin To reload or refresh tables, logs and privileges

603

Replication client Server Admin To ask where the slave or master servers are

604

Replication slave Server Admin To read binary log events from the master

605

Select Tables To retrieve rows from table

606

Show databases Server Admin To see all databases with SHOW DATABASES

607

Show view Tables To see views with SHOW CREATE VIEW

608

Shutdown Server Admin To shut down the server

609

Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.

610

Trigger Tables To use triggers

611

Create tablespace Server Admin To create/alter/drop tablespaces

612

Update Tables To update existing rows

613

Usage Server Admin No privileges - allow connect only

614

create database mysqltest;

615

create table mysqltest.t1 (a int,b int,c int);

616

grant all on mysqltest.t1 to mysqltest_1@localhost;

617

alter table t1 rename t2;

618

ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'

619

revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;

620

delete from mysql.user where user=_binary'mysqltest_1';

621

drop database mysqltest;

622

CREATE USER dummy@localhost;

623

CREATE DATABASE mysqltest;

624

CREATE TABLE mysqltest.dummytable (dummyfield INT);

625

CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;

626

GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost;

627

GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost;

628

SHOW GRANTS FOR dummy@localhost;

629

Grants for dummy@localhost

630

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

631

GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

632

GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

633

use INFORMATION_SCHEMA;

634

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

635

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

636

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

637

TABLE_SCHEMA TABLE_NAME PRIVILEGES

638

mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE

639

mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE

640

FLUSH PRIVILEGES;

641

SHOW GRANTS FOR dummy@localhost;

642

Grants for dummy@localhost

643

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

644

GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

645

GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

646

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

647

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

648

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

649

TABLE_SCHEMA TABLE_NAME PRIVILEGES

650

mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE

651

mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE

652

SHOW FIELDS FROM mysql.tables_priv;

653

Field Type Null Key Default Extra

654

Host char(60) NO PRI

655

Db char(64) NO PRI

656

User char(16) NO PRI

657

Table_name char(64) NO PRI

658

Grantor char(77) NO MUL

659

Timestamp timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

660

Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO

661

Column_priv set('Select','Insert','Update','References') NO

662

use test;

663

REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;

664

DROP USER dummy@localhost;

665

DROP DATABASE mysqltest;

666

CREATE USER dummy@localhost;

667

CREATE DATABASE mysqltest;

668

CREATE TABLE mysqltest.dummytable (dummyfield INT);

669

CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;

670

GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost;

671

GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost;

672

SHOW GRANTS FOR dummy@localhost;

673

Grants for dummy@localhost

674

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

675

GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

676

GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

677

use INFORMATION_SCHEMA;

678

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

679

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

680

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

681

TABLE_SCHEMA TABLE_NAME PRIVILEGES

682

mysqltest dummytable CREATE VIEW

683

mysqltest dummyview CREATE VIEW

684

FLUSH PRIVILEGES;

685

SHOW GRANTS FOR dummy@localhost;

686

Grants for dummy@localhost

687

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

688

GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

689

GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

690

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

691

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

692

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

693

TABLE_SCHEMA TABLE_NAME PRIVILEGES

694

mysqltest dummytable CREATE VIEW

695

mysqltest dummyview CREATE VIEW

696

use test;

697

REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;

698

DROP USER dummy@localhost;

699

DROP DATABASE mysqltest;

700

CREATE USER dummy@localhost;

701

CREATE DATABASE mysqltest;

702

CREATE TABLE mysqltest.dummytable (dummyfield INT);

703

CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;

704

GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost;

705

GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost;

706

SHOW GRANTS FOR dummy@localhost;

707

Grants for dummy@localhost

708

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

709

GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

710

GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

711

use INFORMATION_SCHEMA;

712

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

713

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

714

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

715

TABLE_SCHEMA TABLE_NAME PRIVILEGES

716

mysqltest dummytable SHOW VIEW

717

mysqltest dummyview SHOW VIEW

718

FLUSH PRIVILEGES;

719

SHOW GRANTS FOR dummy@localhost;

720

Grants for dummy@localhost

721

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

722

GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'

723

GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'

724

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY

725

PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE

726

= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;

727

TABLE_SCHEMA TABLE_NAME PRIVILEGES

728

mysqltest dummytable SHOW VIEW

729

mysqltest dummyview SHOW VIEW

730

use test;

731

REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;

732

DROP USER dummy@localhost;

733

DROP DATABASE mysqltest;

734

use mysql;

735

insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select');

736

flush privileges;

737

delete from tables_priv where host = '' and user = 'mysqltest_1';

738

flush privileges;

739

use test;

740

set @user123="non-existent";

741

select * from mysql.db where user=@user123;

742

Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv

743

set names koi8r;

744

create database ��;

745

grant select on ��.* to root@localhost;

746

select hex(Db) from mysql.db where Db='��';

747

hex(Db)

748

D0B1D0B4

749

show grants for root@localhost;

750

Grants for root@localhost

751

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

752

GRANT SELECT ON `��`.* TO 'root'@'localhost'

753

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

754

flush privileges;

755

show grants for root@localhost;

756

Grants for root@localhost

757

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

758

GRANT SELECT ON `��`.* TO 'root'@'localhost'

759

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

760

drop database ��;

761

revoke all privileges on ��.* from root@localhost;

762

show grants for root@localhost;

763

Grants for root@localhost

764

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

765

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

766

set names latin1;

767

create user mysqltest_7@;

768

set password for mysqltest_7@ = password('systpass');

769

show grants for mysqltest_7@;

770

Grants for mysqltest_7@

771

GRANT USAGE ON *.* TO 'mysqltest_7'@'' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517'

772

drop user mysqltest_7@;

773

show grants for mysqltest_7@;

774

ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host ''

775

create database mysqltest;

776

use mysqltest;

777

create table t1(f1 int);

778

GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%';

779

GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%';

780

show grants for mysqltest1@'192.%';

781

Grants for mysqltest1@192.%

782

GRANT USAGE ON *.* TO 'mysqltest1'@'192.%'

783

GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%'

784

show grants for mysqltest1@'%';

785

Grants for mysqltest1@%

786

GRANT USAGE ON *.* TO 'mysqltest1'@'%'

787

GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%'

788

delete from mysql.user where user='mysqltest1';

789

delete from mysql.db where user='mysqltest1';

790

delete from mysql.tables_priv where user='mysqltest1';

791

flush privileges;

792

drop database mysqltest;

793

create database db27515;

794

use db27515;

795

create table t1 (a int);

796

grant alter on db27515.t1 to user27515@localhost;

797

grant insert, create on db27515.t2 to user27515@localhost;

798

rename table t1 to t2;

799

ERROR 42000: DROP command denied to user 'user27515'@'localhost' for table 't1'

800

revoke all privileges, grant option from user27515@localhost;

801

drop user user27515@localhost;

802

drop database db27515;

803

End of 4.1 tests

804

use test;

805

create table t1 (a int);

806

create table t2 as select * from mysql.user where user='';

807

delete from mysql.user where user='';

808

flush privileges;

809

create user mysqltest_8@'';

810

create user mysqltest_8;

811

create user mysqltest_8@host8;

812

create user mysqltest_8@'';

813

ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@''

814

create user mysqltest_8;

815

ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%'

816

create user mysqltest_8@host8;

817

ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'host8'

818

select user, QUOTE(host) from mysql.user where user="mysqltest_8";

819

user QUOTE(host)

820

mysqltest_8 ''

821

mysqltest_8 '%'

822

mysqltest_8 'host8'

823

Schema privileges

824

grant select on mysqltest.* to mysqltest_8@'';

825

show grants for mysqltest_8@'';

826

Grants for mysqltest_8@

827

GRANT USAGE ON *.* TO 'mysqltest_8'@''

828

GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''

829

grant select on mysqltest.* to mysqltest_8@;

830

show grants for mysqltest_8@;

831

Grants for mysqltest_8@

832

GRANT USAGE ON *.* TO 'mysqltest_8'@''

833

GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''

834

grant select on mysqltest.* to mysqltest_8;

835

show grants for mysqltest_8;

836

Grants for mysqltest_8@%

837

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

838

GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%'

839

select * from information_schema.schema_privileges

840

where grantee like "'mysqltest_8'%";

841

GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE

842

'mysqltest_8'@'%' def mysqltest SELECT NO

843

'mysqltest_8'@'' def mysqltest SELECT NO

844

select * from t1;

845

846

revoke select on mysqltest.* from mysqltest_8@'';

847

revoke select on mysqltest.* from mysqltest_8;

848

show grants for mysqltest_8@'';

849

Grants for mysqltest_8@

850

GRANT USAGE ON *.* TO 'mysqltest_8'@''

851

show grants for mysqltest_8;

852

Grants for mysqltest_8@%

853

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

854

select * from information_schema.schema_privileges

855

where grantee like "'mysqltest_8'%";

856

GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE

857

flush privileges;

858

show grants for mysqltest_8@'';

859

Grants for mysqltest_8@

860

GRANT USAGE ON *.* TO 'mysqltest_8'@''

861

show grants for mysqltest_8@;

862

Grants for mysqltest_8@

863

GRANT USAGE ON *.* TO 'mysqltest_8'@''

864

grant select on mysqltest.* to mysqltest_8@'';

865

flush privileges;

866

show grants for mysqltest_8@;

867

Grants for mysqltest_8@

868

GRANT USAGE ON *.* TO 'mysqltest_8'@''

869

GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''

870

revoke select on mysqltest.* from mysqltest_8@'';

871

flush privileges;

872

Column privileges

873

grant update (a) on t1 to mysqltest_8@'';

874

grant update (a) on t1 to mysqltest_8;

875

show grants for mysqltest_8@'';

876

Grants for mysqltest_8@

877

GRANT USAGE ON *.* TO 'mysqltest_8'@''

878

GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''

879

show grants for mysqltest_8;

880

Grants for mysqltest_8@%

881

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

882

GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'

883

flush privileges;

884

show grants for mysqltest_8@'';

885

Grants for mysqltest_8@

886

GRANT USAGE ON *.* TO 'mysqltest_8'@''

887

GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''

888

show grants for mysqltest_8;

889

Grants for mysqltest_8@%

890

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

891

GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'

892

select * from information_schema.column_privileges;

893

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE

894

'mysqltest_8'@'%' def test t1 a UPDATE NO

895

'mysqltest_8'@'' def test t1 a UPDATE NO

896

select * from t1;

897

898

revoke update (a) on t1 from mysqltest_8@'';

899

revoke update (a) on t1 from mysqltest_8;

900

show grants for mysqltest_8@'';

901

Grants for mysqltest_8@

902

GRANT USAGE ON *.* TO 'mysqltest_8'@''

903

show grants for mysqltest_8;

904

Grants for mysqltest_8@%

905

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

906

select * from information_schema.column_privileges;

907

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE

908

flush privileges;

909

show grants for mysqltest_8@'';

910

Grants for mysqltest_8@

911

GRANT USAGE ON *.* TO 'mysqltest_8'@''

912

show grants for mysqltest_8;

913

Grants for mysqltest_8@%

914

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

915

Table privileges

916

grant update on t1 to mysqltest_8@'';

917

grant update on t1 to mysqltest_8;

918

show grants for mysqltest_8@'';

919

Grants for mysqltest_8@

920

GRANT USAGE ON *.* TO 'mysqltest_8'@''

921

GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''

922

show grants for mysqltest_8;

923

Grants for mysqltest_8@%

924

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

925

GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'

926

flush privileges;

927

show grants for mysqltest_8@'';

928

Grants for mysqltest_8@

929

GRANT USAGE ON *.* TO 'mysqltest_8'@''

930

GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''

931

show grants for mysqltest_8;

932

Grants for mysqltest_8@%

933

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

934

GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'

935

select * from information_schema.table_privileges;

936

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE

937

'mysqltest_8'@'%' def test t1 UPDATE NO

938

'mysqltest_8'@'' def test t1 UPDATE NO

939

select * from t1;

940

941

revoke update on t1 from mysqltest_8@'';

942

revoke update on t1 from mysqltest_8;

943

show grants for mysqltest_8@'';

944

Grants for mysqltest_8@

945

GRANT USAGE ON *.* TO 'mysqltest_8'@''

946

show grants for mysqltest_8;

947

Grants for mysqltest_8@%

948

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

949

select * from information_schema.table_privileges;

950

GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE

951

flush privileges;

952

show grants for mysqltest_8@'';

953

Grants for mysqltest_8@

954

GRANT USAGE ON *.* TO 'mysqltest_8'@''

955

show grants for mysqltest_8;

956

Grants for mysqltest_8@%

957

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

958

"DROP USER" should clear privileges

959

grant all privileges on mysqltest.* to mysqltest_8@'';

960

grant select on mysqltest.* to mysqltest_8@'';

961

grant update on t1 to mysqltest_8@'';

962

grant update (a) on t1 to mysqltest_8@'';

963

grant all privileges on mysqltest.* to mysqltest_8;

964

show grants for mysqltest_8@'';

965

Grants for mysqltest_8@

966

GRANT USAGE ON *.* TO 'mysqltest_8'@''

967

GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''

968

GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''

969

show grants for mysqltest_8;

970

Grants for mysqltest_8@%

971

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

972

GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'

973

select * from information_schema.user_privileges

974

where grantee like "'mysqltest_8'%";

975

GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE

976

'mysqltest_8'@'host8' def USAGE NO

977

'mysqltest_8'@'%' def USAGE NO

978

'mysqltest_8'@'' def USAGE NO

979

select * from t1;

980

981

flush privileges;

982

show grants for mysqltest_8@'';

983

Grants for mysqltest_8@

984

GRANT USAGE ON *.* TO 'mysqltest_8'@''

985

GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''

986

GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''

987

show grants for mysqltest_8;

988

Grants for mysqltest_8@%

989

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

990

GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'

991

drop user mysqltest_8@'';

992

show grants for mysqltest_8@'';

993

ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host ''

994

show grants for mysqltest_8;

995

Grants for mysqltest_8@%

996

GRANT USAGE ON *.* TO 'mysqltest_8'@'%'

997

GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'

998

select * from information_schema.user_privileges

999

where grantee like "'mysqltest_8'%";

1000

GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE

1001

'mysqltest_8'@'host8' def USAGE NO

1002

'mysqltest_8'@'%' def USAGE NO

1003

drop user mysqltest_8;

1004

connect(localhost,mysqltest_8,,test,MASTER_PORT,MASTER_SOCKET);

1005

ERROR 28000: Access denied for user 'mysqltest_8'@'localhost' (using password: NO)

1006

show grants for mysqltest_8;

1007

ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%'

1008

drop user mysqltest_8@host8;

1009

show grants for mysqltest_8@host8;

1010

ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host 'host8'

1011

insert into mysql.user select * from t2;

1012

flush privileges;

1013

drop table t2;

1014

drop table t1;

1015

CREATE DATABASE mysqltest3;

1016

USE mysqltest3;

1017

CREATE TABLE t_nn (c1 INT);

1018

CREATE VIEW v_nn AS SELECT * FROM t_nn;

1019

CREATE DATABASE mysqltest2;

1020

USE mysqltest2;

1021

CREATE TABLE t_nn (c1 INT);

1022

CREATE VIEW v_nn AS SELECT * FROM t_nn;

1023

CREATE VIEW v_yn AS SELECT * FROM t_nn;

1024

CREATE VIEW v_gy AS SELECT * FROM t_nn;

1025

CREATE VIEW v_ny AS SELECT * FROM t_nn;

1026

CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55;

1027

GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';

1028

GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';

1029

GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';

1030

GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';

1031

SHOW CREATE VIEW mysqltest2.v_nn;

1032

ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'

1033

SHOW CREATE TABLE mysqltest2.v_nn;

1034

ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'

1035

SHOW CREATE VIEW mysqltest2.v_yn;

1036

ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'

1037

SHOW CREATE TABLE mysqltest2.v_yn;

1038

ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'

1039

SHOW CREATE TABLE mysqltest2.v_ny;

1040

View Create View character_set_client collation_connection

1041

v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci

1042

SHOW CREATE VIEW mysqltest2.v_ny;

1043

View Create View character_set_client collation_connection

1044

v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci

1045

SHOW CREATE TABLE mysqltest3.t_nn;

1046

ERROR 42000: SHOW command denied to user 'mysqltest_1'@'localhost' for table 't_nn'

1047

SHOW CREATE VIEW mysqltest3.t_nn;

1048

ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'

1049

SHOW CREATE VIEW mysqltest3.v_nn;

1050

ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'

1051

SHOW CREATE TABLE mysqltest3.v_nn;

1052

ERROR 42000: SHOW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'

1053

SHOW CREATE TABLE mysqltest2.t_nn;

1054

Table Create Table

1055

t_nn CREATE TABLE `t_nn` (

1056

`c1` int(11) DEFAULT NULL

1057

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1058

SHOW CREATE VIEW mysqltest2.t_nn;

1059

ERROR HY000: 'mysqltest2.t_nn' is not VIEW

1060

SHOW CREATE VIEW mysqltest2.v_yy;

1061

View Create View character_set_client collation_connection

1062

v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55) latin1 latin1_swedish_ci

1063

SHOW CREATE TABLE mysqltest2.v_yy;

1064

View Create View character_set_client collation_connection

1065

1066

SHOW CREATE TABLE mysqltest2.v_nn;

1067

View Create View character_set_client collation_connection

1068

v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci

1069

SHOW CREATE VIEW mysqltest2.v_nn;

1070

View Create View character_set_client collation_connection

1071

v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci

1072

SHOW CREATE TABLE mysqltest2.t_nn;

1073

Table Create Table

1074

t_nn CREATE TABLE `t_nn` (

1075

`c1` int(11) DEFAULT NULL

1076

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1077

SHOW CREATE VIEW mysqltest2.t_nn;

1078

ERROR HY000: 'mysqltest2.t_nn' is not VIEW

1079

DROP VIEW mysqltest2.v_nn;

1080

DROP VIEW mysqltest2.v_yn;

1081

DROP VIEW mysqltest2.v_ny;

1082

DROP VIEW mysqltest2.v_yy;

1083

DROP TABLE mysqltest2.t_nn;

1084

DROP DATABASE mysqltest2;

1085

DROP VIEW mysqltest3.v_nn;

1086

DROP TABLE mysqltest3.t_nn;

1087

DROP DATABASE mysqltest3;

1088

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';

1089

DROP USER 'mysqltest_1'@'localhost';

1090

USE test;

1091

create user mysqltest1_thisisreallytoolong;

1092

ERROR HY000: String 'mysqltest1_thisisreallytoolong' is too long for user name (should be no longer than 16)

1093

CREATE DATABASE mysqltest1;

1094

CREATE TABLE mysqltest1.t1 (

1095

int_field INTEGER UNSIGNED NOT NULL,

1096

char_field CHAR(10),

1097

INDEX(`int_field`)

1098

);

1099

CREATE TABLE mysqltest1.t2 (int_field INT);

1100

"Now check that we require equivalent grants for "

1101

"RENAME TABLE and ALTER TABLE"

1102

CREATE USER mysqltest_1@localhost;

1103

GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;

1104

SELECT USER();

1105

USER()

1106

mysqltest_1@localhost

1107

SHOW GRANTS;

1108

Grants for mysqltest_1@localhost

1109

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

1110

GRANT SELECT ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost'

1111

RENAME TABLE t1 TO t2;

1112

ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1'

1113

ALTER TABLE t1 RENAME TO t2;

1114

ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1'

1115

GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost;

1116

RENAME TABLE t1 TO t2;

1117

ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1'

1118

ALTER TABLE t1 RENAME TO t2;

1119

ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1'

1120

GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost;

1121

SHOW GRANTS;

1122

Grants for mysqltest_1@localhost

1123

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

1124

GRANT SELECT, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost'

1125

RENAME TABLE t1 TO t2;

1126

ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'

1127

ALTER TABLE t1 RENAME TO t2;

1128

ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'

1129

GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost;

1130

SHOW GRANTS;

1131

Grants for mysqltest_1@localhost

1132

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

1133

GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost'

1134

GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost;

1135

DROP TABLE mysqltest1.t2;

1136

SHOW GRANTS;

1137

Grants for mysqltest_1@localhost

1138

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

1139

GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost'

1140

GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost'

1141

RENAME TABLE t1 TO t2;

1142

RENAME TABLE t2 TO t1;

1143

ALTER TABLE t1 RENAME TO t2;

1144

ALTER TABLE t2 RENAME TO t1;

1145

REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost;

1146

REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost;

1147

SHOW GRANTS;

1148

Grants for mysqltest_1@localhost

1149

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

1150

GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost'

1151

GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost'

1152

RENAME TABLE t1 TO t2;

1153

ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1'

1154

ALTER TABLE t1 RENAME TO t2;

1155

ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1'

1156

DROP USER mysqltest_1@localhost;

1157

DROP DATABASE mysqltest1;

1158

USE test;

1159

GRANT CREATE ON mysqltest.* TO 1234567890abcdefGHIKL@localhost;

1160

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1161

GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1162

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1163

REVOKE CREATE ON mysqltest.* FROM 1234567890abcdefGHIKL@localhost;

1164

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1165

REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1166

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1167

GRANT CREATE ON t1 TO 1234567890abcdefGHIKL@localhost;

1168

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1169

GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1170

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1171

REVOKE CREATE ON t1 FROM 1234567890abcdefGHIKL@localhost;

1172

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1173

REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1174

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1175

GRANT EXECUTE ON PROCEDURE p1 TO 1234567890abcdefGHIKL@localhost;

1176

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1177

GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1178

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1179

REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;

1180

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1181

REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;

1182

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1183

CREATE USER bug23556@localhost;

1184

CREATE DATABASE bug23556;

1185

GRANT SELECT ON bug23556.* TO bug23556@localhost;

1186

USE bug23556;

1187

CREATE TABLE t1 (a INT PRIMARY KEY);

1188

INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

1189

GRANT DELETE ON t1 TO bug23556@localhost;

1190

USE bug23556;

1191

TRUNCATE t1;

1192

ERROR 42000: DROP command denied to user 'bug23556'@'localhost' for table 't1'

1193

USE bug23556;

1194

REVOKE DELETE ON t1 FROM bug23556@localhost;

1195

GRANT DROP ON t1 TO bug23556@localhost;

1196

USE bug23556;

1197

TRUNCATE t1;

1198

USE bug23556;

1199

DROP TABLE t1;

1200

USE test;

1201

DROP DATABASE bug23556;

1202

DROP USER bug23556@localhost;

1203

GRANT PROCESS ON * TO user@localhost;

1204

ERROR 3D000: No database selected

1205

DROP DATABASE IF EXISTS mysqltest1;

1206

DROP DATABASE IF EXISTS mysqltest2;

1207

DROP DATABASE IF EXISTS mysqltest3;

1208

DROP DATABASE IF EXISTS mysqltest4;

1209

CREATE DATABASE mysqltest1;

1210

CREATE DATABASE mysqltest2;

1211

CREATE DATABASE mysqltest3;

1212

CREATE DATABASE mysqltest4;

1213

CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER

1214

SELECT 1;

1215

CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER

1216

SELECT 1;

1217

CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER

1218

RETURN 1;

1219

CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER

1220

RETURN 1;

1221

GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost;

1222

GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost;

1223

GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost;

1224

GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost;

1225

GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;

1226

1227

---> connection: bug9504_con1

1228

use mysqltest1;

1229

use mysqltest2;

1230

use mysqltest3;

1231

use mysqltest4;

1232

use test;

1233

CALL mysqltest1.p_def();

1234

1235

1236

CALL mysqltest2.p_inv();

1237

1238

1239

SELECT mysqltest3.f_def();

1240

mysqltest3.f_def()

1241

1242

SELECT mysqltest4.f_inv();

1243

mysqltest4.f_inv()

1244

1245

1246

---> connection: default

1247

DROP DATABASE mysqltest1;

1248

DROP DATABASE mysqltest2;

1249

DROP DATABASE mysqltest3;

1250

DROP DATABASE mysqltest4;

1251

DROP USER mysqltest_1@localhost;

1252

DROP DATABASE IF EXISTS mysqltest1;

1253

DROP DATABASE IF EXISTS mysqltest2;

1254

CREATE DATABASE mysqltest1;

1255

CREATE DATABASE mysqltest2;

1256

GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost;

1257

GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost;

1258

CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER

1259

SELECT 1;

1260

1261

---> connection: bug27337_con1

1262

CREATE TABLE t1(c INT);

1263

ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1'

1264

CALL mysqltest1.p1();

1265

1266

1267

CREATE TABLE t1(c INT);

1268

ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1'

1269

1270

---> connection: bug27337_con2

1271

CREATE TABLE t1(c INT);

1272

ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1'

1273

SHOW TABLES;

1274

Tables_in_mysqltest2

1275

1276

---> connection: default

1277

DROP DATABASE mysqltest1;

1278

DROP DATABASE mysqltest2;

1279

DROP USER mysqltest_1@localhost;

1280

DROP DATABASE IF EXISTS mysqltest1;

1281

DROP DATABASE IF EXISTS mysqltest2;

1282

CREATE DATABASE mysqltest1;

1283

CREATE DATABASE mysqltest2;

1284

CREATE TABLE mysqltest1.t1(c INT);

1285

CREATE TABLE mysqltest2.t2(c INT);

1286

GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;

1287

GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost;

1288

1289

---> connection: bug27337_con1

1290

SHOW TABLES FROM mysqltest1;

1291

Tables_in_mysqltest1

1292

1293

PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1';

1294

EXECUTE stmt1;

1295

Tables_in_mysqltest1

1296

1297

1298

---> connection: bug27337_con2

1299

SHOW COLUMNS FROM mysqltest2.t2;

1300

Field Type Null Key Default Extra

1301

c int(11) YES NULL

1302

PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2';

1303

EXECUTE stmt2;

1304

Field Type Null Key Default Extra

1305

c int(11) YES NULL

1306

1307

---> connection: default

1308

REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost;

1309

REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost;

1310

1311

---> connection: bug27337_con1

1312

SHOW TABLES FROM mysqltest1;

1313

ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest1'

1314

EXECUTE stmt1;

1315

ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest1'

1316

1317

---> connection: bug27337_con2

1318

SHOW COLUMNS FROM mysqltest2.t2;

1319

ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'

1320

EXECUTE stmt2;

1321

ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'

1322

1323

---> connection: default

1324

DROP DATABASE mysqltest1;

1325

DROP DATABASE mysqltest2;

1326

DROP USER mysqltest_1@localhost;

1327

DROP USER mysqltest_2@localhost;

1328

USE test;

1329

CREATE TABLE t1 (f1 int, f2 int);

1330

INSERT INTO t1 VALUES(1,1), (2,2);

1331

CREATE DATABASE db27878;

1332

GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';

1333

GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';

1334

GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';

1335

USE db27878;

1336

CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;

1337

USE db27878;

1338

UPDATE v1 SET f2 = 4;

1339

ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

1340

SELECT * FROM test.t1;

1341

f1 f2

1342

1 1

1343

2 2

1344

REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost';

1345

REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost';

1346

REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost';

1347

DROP USER mysqltest_1@localhost;

1348

DROP DATABASE db27878;

1349

USE test;

1350

DROP TABLE t1;

1351

1352

# Bug#33275 Server crash when creating temporary table mysql.user

1353

1354

CREATE TEMPORARY TABLE mysql.user (id INT);

1355

FLUSH PRIVILEGES;

1356

DROP TABLE mysql.user;

1357

drop table if exists test;

1358

Warnings:

1359

Note 1051 Unknown table 'test.test'

1360

drop function if exists test_function;

1361

Warnings:

1362

Note 1305 FUNCTION test.test_function does not exist

1363

drop view if exists v1;

1364

Warnings:

1365

Note 1051 Unknown table 'test.v1'

1366

create table test (col1 varchar(30));

1367

create function test_function() returns varchar(30)

1368

begin

1369

declare tmp varchar(30);

1370

select col1 from test limit 1 into tmp;

1371

return '1';

1372

end|

1373

create view v1 as select test.* from test where test.col1=test_function();

1374

grant update (col1) on v1 to 'greg'@'localhost';

1375

drop user 'greg'@'localhost';

1376

drop view v1;

1377

drop table test;

1378

drop function test_function;

1379

SELECT CURRENT_USER();

1380

CURRENT_USER()

1381

root@localhost

1382

SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin");

1383

SET PASSWORD FOR CURRENT_USER() = PASSWORD("");

1384

1385

# Bug#57952

1386

1387

DROP DATABASE IF EXISTS mysqltest1;

1388

DROP DATABASE IF EXISTS mysqltest2;

1389

CREATE DATABASE mysqltest1;

1390

CREATE DATABASE mysqltest2;

1391

use mysqltest1;

1392

CREATE TABLE t1(a INT, b INT);

1393

INSERT INTO t1 VALUES (1, 1);

1394

CREATE TABLE t2(a INT);

1395

INSERT INTO t2 VALUES (2);

1396

CREATE TABLE mysqltest2.t3(a INT);

1397

INSERT INTO mysqltest2.t3 VALUES (4);

1398

CREATE USER testuser@localhost;

1399

GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost;

1400

GRANT SELECT(b) ON t1 TO testuser@localhost;

1401

GRANT SELECT ON t2 TO testuser@localhost;

1402

GRANT SELECT ON mysqltest2.* TO testuser@localhost;

1403

1404

# Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)

1405

PREPARE s1 FROM 'SELECT b FROM t1';

1406

PREPARE s2 FROM 'SELECT a FROM t2';

1407

PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2';

1408

CREATE PROCEDURE p1() SELECT b FROM t1;

1409

CREATE PROCEDURE p2() SELECT a FROM t2;

1410

CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2;

1411

CALL p1;

1412

1413

1414

CALL p2;

1415

1416

1417

CALL p3;

1418

Tables_in_mysqltest2

1419

1420

1421

# Connection: default

1422

REVOKE SELECT ON t1 FROM testuser@localhost;

1423

GRANT SELECT(a) ON t1 TO testuser@localhost;

1424

REVOKE SELECT ON t2 FROM testuser@localhost;

1425

REVOKE SELECT ON mysqltest2.* FROM testuser@localhost;

1426

1427

# Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)

1428

# - Check column-level privileges...

1429

EXECUTE s1;

1430

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

1431

SELECT b FROM t1;

1432

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

1433

EXECUTE s1;

1434

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

1435

CALL p1;

1436

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

1437

# - Check table-level privileges...

1438

SELECT a FROM t2;

1439

ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2'

1440

EXECUTE s2;

1441

ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2'

1442

CALL p2;

1443

ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2'

1444

# - Check database-level privileges...

1445

SHOW TABLES FROM mysqltest2;

1446

ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2'

1447

EXECUTE s3;

1448

ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2'

1449

CALL p3;

1450

ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2'

1451

1452

# Connection: default

1453

DROP DATABASE mysqltest1;

1454

DROP DATABASE mysqltest2;

1455

DROP USER testuser@localhost;

1456

use test;

1457

1458

1459

# Test for bug #36544 "DROP USER does not remove stored function

1460

# privileges".

1461

1462

create database mysqltest1;

1463

create function mysqltest1.f1() returns int return 0;

1464

create procedure mysqltest1.p1() begin end;

1465

1466

# 1) Check that DROP USER properly removes privileges on both

1467

# stored procedures and functions.

1468

1469

create user mysqluser1@localhost;

1470

grant execute on function mysqltest1.f1 to mysqluser1@localhost;

1471

grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;

1472

# Quick test that granted privileges are properly reflected

1473

# in privilege tables and in in-memory structures.

1474

show grants for mysqluser1@localhost;

1475

Grants for mysqluser1@localhost

1476

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

1477

GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost'

1478

GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost'

1479

select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';

1480

db routine_name routine_type proc_priv

1481

mysqltest1 f1 FUNCTION Execute

1482

mysqltest1 p1 PROCEDURE Execute

1483

1484

# Create connection 'bug_36544_con1' as 'mysqluser1@localhost'.

1485

call mysqltest1.p1();

1486

select mysqltest1.f1();

1487

mysqltest1.f1()

1488

1489

1490

# Switch to connection 'default'.

1491

drop user mysqluser1@localhost;

1492

1493

# Test that dropping of user is properly reflected in

1494

# both privilege tables and in in-memory structures.

1495

1496

# Switch to connection 'bug36544_con1'.

1497

# The connection cold be alive but should not be able to

1498

# access to any of the stored routines.

1499

call mysqltest1.p1();

1500

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1'

1501

select mysqltest1.f1();

1502

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1'

1503

1504

# Switch to connection 'default'.

1505

1506

# Now create user with the same name and check that he

1507

# has not inherited privileges.

1508

create user mysqluser1@localhost;

1509

show grants for mysqluser1@localhost;

1510

Grants for mysqluser1@localhost

1511

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

1512

select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';

1513

db routine_name routine_type proc_priv

1514

1515

# Create connection 'bug_36544_con2' as 'mysqluser1@localhost'.

1516

# Newly created user should not be able to access any of the routines.

1517

call mysqltest1.p1();

1518

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1'

1519

select mysqltest1.f1();

1520

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1'

1521

1522

# Switch to connection 'default'.

1523

1524

# 2) Check that RENAME USER properly updates privileges on both

1525

# stored procedures and functions.

1526

1527

grant execute on function mysqltest1.f1 to mysqluser1@localhost;

1528

grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;

1529

1530

# Create one more user to make in-memory hashes non-trivial.

1531

# User names 'mysqluser11' and 'mysqluser10' were selected

1532

# to trigger bug discovered during code inspection.

1533

create user mysqluser11@localhost;

1534

grant execute on function mysqltest1.f1 to mysqluser11@localhost;

1535

grant execute on procedure mysqltest1.p1 to mysqluser11@localhost;

1536

# Also create a couple of tables to test for another bug

1537

# discovered during code inspection (again table names were

1538

# chosen especially to trigger the bug).

1539

create table mysqltest1.t11 (i int);

1540

create table mysqltest1.t22 (i int);

1541

grant select on mysqltest1.t22 to mysqluser1@localhost;

1542

grant select on mysqltest1.t11 to mysqluser1@localhost;

1543

# Quick test that granted privileges are properly reflected

1544

# in privilege tables and in in-memory structures.

1545

show grants for mysqluser1@localhost;

1546

Grants for mysqluser1@localhost

1547

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

1548

GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser1'@'localhost'

1549

GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser1'@'localhost'

1550

GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost'

1551

GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost'

1552

select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';

1553

db routine_name routine_type proc_priv

1554

mysqltest1 f1 FUNCTION Execute

1555

mysqltest1 p1 PROCEDURE Execute

1556

select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';

1557

db table_name table_priv

1558

mysqltest1 t11 Select

1559

mysqltest1 t22 Select

1560

1561

# Switch to connection 'bug36544_con2'.

1562

call mysqltest1.p1();

1563

select mysqltest1.f1();

1564

mysqltest1.f1()

1565

1566

select * from mysqltest1.t11;

1567

1568

select * from mysqltest1.t22;

1569

1570

1571

# Switch to connection 'default'.

1572

rename user mysqluser1@localhost to mysqluser10@localhost;

1573

1574

# Test that there are no privileges left for mysqluser1.

1575

1576

# Switch to connection 'bug36544_con2'.

1577

# The connection cold be alive but should not be able to

1578

# access to any of the stored routines or tables.

1579

call mysqltest1.p1();

1580

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1'

1581

select mysqltest1.f1();

1582

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1'

1583

select * from mysqltest1.t11;

1584

ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't11'

1585

select * from mysqltest1.t22;

1586

ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't22'

1587

1588

# Switch to connection 'default'.

1589

1590

# Now create user with the old name and check that he

1591

# has not inherited privileges.

1592

create user mysqluser1@localhost;

1593

show grants for mysqluser1@localhost;

1594

Grants for mysqluser1@localhost

1595

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

1596

select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';

1597

db routine_name routine_type proc_priv

1598

select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';

1599

db table_name table_priv

1600

1601

# Create connection 'bug_36544_con3' as 'mysqluser1@localhost'.

1602

# Newly created user should not be able to access to any of the

1603

# stored routines or tables.

1604

call mysqltest1.p1();

1605

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1'

1606

select mysqltest1.f1();

1607

ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1'

1608

select * from mysqltest1.t11;

1609

ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't11'

1610

select * from mysqltest1.t22;

1611

ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't22'

1612

1613

# Switch to connection 'default'.

1614

1615

# Now check that privileges became associated with a new user

1616

# name - mysqluser10.

1617

1618

show grants for mysqluser10@localhost;

1619

Grants for mysqluser10@localhost

1620

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

1621

GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser10'@'localhost'

1622

GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser10'@'localhost'

1623

GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser10'@'localhost'

1624

GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser10'@'localhost'

1625

select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost';

1626

db routine_name routine_type proc_priv

1627

mysqltest1 f1 FUNCTION Execute

1628

mysqltest1 p1 PROCEDURE Execute

1629

select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost';

1630

db table_name table_priv

1631

mysqltest1 t11 Select

1632

mysqltest1 t22 Select

1633

1634

# Create connection 'bug_36544_con4' as 'mysqluser10@localhost'.

1635

call mysqltest1.p1();

1636

select mysqltest1.f1();

1637

mysqltest1.f1()

1638

1639

select * from mysqltest1.t11;

1640

1641

select * from mysqltest1.t22;

1642

1643

1644

# Switch to connection 'default'.

1645

1646

# Clean-up.

1647

drop user mysqluser1@localhost;

1648

drop user mysqluser10@localhost;

1649

drop user mysqluser11@localhost;

1650

drop database mysqltest1;

1651

End of 5.0 tests

1652

set names utf8;

1653

grant select on test.* to юзер_юзер@localhost;

1654

user()

1655

юзер_юзер@localhost

1656

revoke all on test.* from юзер_юзер@localhost;

1657

drop user юзер_юзер@localhost;

1658

grant select on test.* to очень_длинный_юзер@localhost;

1659

ERROR HY000: String 'очень_длинный_юзер' is too long for user name (should be no longer than 16)

1660

set names default;

1661

create database mysqltest;

1662

use mysqltest;

1663

grant create on mysqltest.* to mysqltest@localhost;

1664

create table t1 (i INT);

1665

insert into t1 values (1);

1666

ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1'

1667

create table t2 (i INT);

1668

create table t4 (i INT);

1669

grant select, insert on mysqltest.t2 to mysqltest@localhost;

1670

grant insert on mysqltest.t4 to mysqltest@localhost;

1671

grant create, insert on mysqltest.t5 to mysqltest@localhost;

1672

grant create, insert on mysqltest.t6 to mysqltest@localhost;

1673

flush privileges;

1674

insert into t2 values (1);

1675

create table if not exists t1 select * from t2;

1676

ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1'

1677

create table if not exists t3 select * from t2;

1678

ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't3'

1679

create table if not exists t4 select * from t2;

1680

Warnings:

1681

Note 1050 Table 't4' already exists

1682

create table if not exists t5 select * from t2;

1683

create table t6 select * from t2;

1684

create table t7 select * from t2;

1685

ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't7'

1686

create table t4 select * from t2;

1687

ERROR 42S01: Table 't4' already exists

1688

create table t1 select * from t2;

1689

ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1'

1690

drop table t1,t2,t4,t5,t6;

1691

revoke create on mysqltest.* from mysqltest@localhost;

1692

revoke select, insert on mysqltest.t2 from mysqltest@localhost;

1693

revoke insert on mysqltest.t4 from mysqltest@localhost;

1694

revoke create, insert on mysqltest.t5 from mysqltest@localhost;

1695

revoke create, insert on mysqltest.t6 from mysqltest@localhost;

1696

drop user mysqltest@localhost;

1697

drop database mysqltest;

1698

use test;

1699

FLUSH PRIVILEGES without procs_priv table.

1700

RENAME TABLE mysql.procs_priv TO mysql.procs_gone;

1701

FLUSH PRIVILEGES;

1702

ERROR 42S02: Table 'mysql.procs_priv' doesn't exist

1703

Assigning privileges without procs_priv table.

1704

CREATE DATABASE mysqltest1;

1705

CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER

1706

SELECT 1;

1707

CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1;

1708

GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost;

1709

ERROR 42S02: Table 'mysql.procs_priv' doesn't exist

1710

GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;

1711

CALL mysqltest1.test();

1712

1713

1714

DROP DATABASE mysqltest1;

1715

RENAME TABLE mysql.procs_gone TO mysql.procs_priv;

1716

DROP USER mysqltest_1@localhost;

1717

FLUSH PRIVILEGES;

1718

CREATE DATABASE dbbug33464;

1719

CREATE USER 'userbug33464'@'localhost';

1720

GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost';

1721

1722

userbug33464@localhost dbbug33464

1723

CREATE PROCEDURE sp3(v1 char(20))

1724

BEGIN

1725

SELECT * from dbbug33464.t6 where t6.f2= 'xyz';

1726

END//

1727

CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER

1728

BEGIN

1729

return 1;

1730

END//

1731

CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER

1732

BEGIN

1733

return 2;

1734

END//

1735

USE dbbug33464;

1736

1737

root@localhost dbbug33464

1738

SELECT fn1();

1739

fn1()

1740

1741

SELECT fn2();

1742

fn2()

1743

1744

DROP USER 'userbug33464'@'localhost';

1745

DROP FUNCTION fn1;

1746

DROP FUNCTION fn2;

1747

DROP PROCEDURE sp3;

1748

DROP USER 'userbug33464'@'localhost';

1749

USE test;

1750

DROP DATABASE dbbug33464;

1751

SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;

1752

CREATE USER user1;

1753

CREATE USER user2;

1754

GRANT CREATE ON db1.* TO 'user1'@'localhost';

1755

GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost';

1756

GRANT CREATE ON db1.* TO 'user2'@'%';

1757

GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%';

1758

FLUSH PRIVILEGES;

1759

SHOW GRANTS FOR 'user1'@'localhost';

1760

Grants for user1@localhost

1761

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

1762

GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost'

1763

** Connect as user1 and create a procedure.

1764

** The creation will imply implicitly assigned

1765

** EXECUTE and ALTER ROUTINE privileges to

1766

** the current user user1@localhost.

1767

SELECT @@GLOBAL.sql_mode;

1768

@@GLOBAL.sql_mode

1769

NO_ENGINE_SUBSTITUTION

1770

SELECT @@SESSION.sql_mode;

1771

@@SESSION.sql_mode

1772

NO_ENGINE_SUBSTITUTION

1773

CREATE DATABASE db1;

1774

CREATE PROCEDURE db1.proc1(p1 INT)

1775

BEGIN

1776

SET @x = 0;

1777

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

1778

END ;||

1779

** Connect as user2 and create a procedure.

1780

** Implicitly assignment of privileges will

1781

** fail because the user2@localhost is an

1782

** unknown user.

1783

CREATE PROCEDURE db1.proc2(p1 INT)

1784

BEGIN

1785

SET @x = 0;

1786

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

1787

END ;||

1788

Warnings:

1789

Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges

1790

SHOW GRANTS FOR 'user1'@'localhost';

1791

Grants for user1@localhost

1792

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

1793

GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost'

1794

GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc1` TO 'user1'@'localhost'

1795

SHOW GRANTS FOR 'user2';

1796

Grants for user2@%

1797

GRANT USAGE ON *.* TO 'user2'@'%'

1798

GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%'

1799

DROP PROCEDURE db1.proc1;

1800

DROP PROCEDURE db1.proc2;

1801

REVOKE ALL ON db1.* FROM 'user1'@'localhost';

1802

REVOKE ALL ON db1.* FROM 'user2'@'%';

1803

DROP USER 'user1';

1804

DROP USER 'user1'@'localhost';

1805

DROP USER 'user2';

1806

DROP DATABASE db1;

1807

1808

# Bug #25863 No database selected error, but documentation

1809

# says * for global allowed

1810

1811

GRANT ALL ON * TO mysqltest_1;

1812

ERROR 3D000: No database selected

1813

GRANT ALL ON *.* TO mysqltest_1;

1814

SHOW GRANTS FOR mysqltest_1;

1815

Grants for mysqltest_1@%

1816

GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%'

1817

DROP USER mysqltest_1;

1818

USE test;

1819

GRANT ALL ON * TO mysqltest_1;

1820

SHOW GRANTS FOR mysqltest_1;

1821

Grants for mysqltest_1@%

1822

GRANT USAGE ON *.* TO 'mysqltest_1'@'%'

1823

GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'%'

1824

DROP USER mysqltest_1;

1825

GRANT ALL ON *.* TO mysqltest_1;

1826

SHOW GRANTS FOR mysqltest_1;

1827

Grants for mysqltest_1@%

1828

GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%'

1829

DROP USER mysqltest_1;

1830

CREATE DATABASE db1;

1831

CREATE DATABASE db2;

1832

GRANT SELECT ON db1.* to 'testbug'@localhost;

1833

USE db2;

1834

CREATE TABLE t1 (a INT);

1835

USE test;

1836

SELECT * FROM `../db2/tb2`;

1837

ERROR 42S02: Table 'db1.../db2/tb2' doesn't exist

1838

SELECT * FROM `../db2`.tb2;

1839

ERROR 42000: SELECT command denied to user 'testbug'@'localhost' for table 'tb2'

1840

SELECT * FROM `#mysql50#/../db2/tb2`;

1841

ERROR 42S02: Table 'db1.#mysql50#/../db2/tb2' doesn't exist

1842

DROP USER 'testbug'@localhost;

1843

DROP TABLE db2.t1;

1844

DROP DATABASE db1;

1845

DROP DATABASE db2;

1846

1847

# Bug #36742

1848

1849

grant usage on Foo.* to myuser@Localhost identified by 'foo';

1850

grant select on Foo.* to myuser@localhost;

1851

select host,user from mysql.user where User='myuser';

1852

host user

1853

localhost myuser

1854

revoke select on Foo.* from myuser@localhost;

1855

delete from mysql.user where User='myuser';

1856

flush privileges;

1857

#########################################################################

1858

1859

# Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.

1860

1861

#########################################################################

1862

1863

# --

1864

# -- Prepare the environment.

1865

# --

1866

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

1867

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

1868

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

1869

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

1870

FLUSH PRIVILEGES;

1871

DROP DATABASE IF EXISTS mysqltest_db1;

1872

CREATE DATABASE mysqltest_db1;

1873

CREATE TABLE mysqltest_db1.t1(a INT);

1874

1875

# --

1876

# -- Check that global privileges don't allow SHOW CREATE TABLE.

1877

# --

1878

GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost;

1879

GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;

1880

GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;

1881

GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost;

1882

GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost;

1883

GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost;

1884

GRANT FILE ON *.* TO mysqltest_u1@localhost;

1885

GRANT CREATE USER ON *.* TO mysqltest_u1@localhost;

1886

GRANT PROCESS ON *.* TO mysqltest_u1@localhost;

1887

GRANT RELOAD ON *.* TO mysqltest_u1@localhost;

1888

GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost;

1889

GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost;

1890

GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost;

1891

GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost;

1892

GRANT USAGE ON *.* TO mysqltest_u1@localhost;

1893

1894

SHOW GRANTS FOR mysqltest_u1@localhost;

1895

Grants for mysqltest_u1@localhost

1896

GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'mysqltest_u1'@'localhost'

1897

GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

1898

1899

# connection: con1 (mysqltest_u1@mysqltest_db1)

1900

1901

SHOW CREATE TABLE t1;

1902

ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1'

1903

1904

# connection: default

1905

1906

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

1907

SHOW GRANTS FOR mysqltest_u1@localhost;

1908

Grants for mysqltest_u1@localhost

1909

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

1910

1911

# --

1912

# -- Check that global SELECT allows SHOW CREATE TABLE.

1913

# --

1914

1915

GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost;

1916

1917

SHOW GRANTS FOR mysqltest_u1@localhost;

1918

Grants for mysqltest_u1@localhost

1919

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

1920

GRANT SELECT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

1921

1922

# connection: con1 (mysqltest_u1@mysqltest_db1)

1923

1924

SHOW CREATE TABLE t1;

1925

Table Create Table

1926

t1 CREATE TABLE `t1` (

1927

`a` int(11) DEFAULT NULL

1928

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1929

1930

# connection: default

1931

1932

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

1933

SHOW GRANTS FOR mysqltest_u1@localhost;

1934

Grants for mysqltest_u1@localhost

1935

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

1936

1937

# --

1938

# -- Check that global INSERT allows SHOW CREATE TABLE.

1939

# --

1940

1941

GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost;

1942

1943

SHOW GRANTS FOR mysqltest_u1@localhost;

1944

Grants for mysqltest_u1@localhost

1945

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

1946

GRANT INSERT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

1947

1948

# connection: con1 (mysqltest_u1@mysqltest_db1)

1949

1950

SHOW CREATE TABLE t1;

1951

Table Create Table

1952

t1 CREATE TABLE `t1` (

1953

`a` int(11) DEFAULT NULL

1954

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1955

1956

# connection: default

1957

1958

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

1959

SHOW GRANTS FOR mysqltest_u1@localhost;

1960

Grants for mysqltest_u1@localhost

1961

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

1962

1963

# --

1964

# -- Check that global UPDATE allows SHOW CREATE TABLE.

1965

# --

1966

1967

GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost;

1968

1969

SHOW GRANTS FOR mysqltest_u1@localhost;

1970

Grants for mysqltest_u1@localhost

1971

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

1972

GRANT UPDATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

1973

1974

# connection: con1 (mysqltest_u1@mysqltest_db1)

1975

1976

SHOW CREATE TABLE t1;

1977

Table Create Table

1978

t1 CREATE TABLE `t1` (

1979

`a` int(11) DEFAULT NULL

1980

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1981

1982

# connection: default

1983

1984

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

1985

SHOW GRANTS FOR mysqltest_u1@localhost;

1986

Grants for mysqltest_u1@localhost

1987

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

1988

1989

# --

1990

# -- Check that global DELETE allows SHOW CREATE TABLE.

1991

# --

1992

1993

GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;

1994

1995

SHOW GRANTS FOR mysqltest_u1@localhost;

1996

Grants for mysqltest_u1@localhost

1997

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

1998

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

1999

2000

# connection: con1 (mysqltest_u1@mysqltest_db1)

2001

2002

SHOW CREATE TABLE t1;

2003

Table Create Table

2004

t1 CREATE TABLE `t1` (

2005

`a` int(11) DEFAULT NULL

2006

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2007

2008

# connection: default

2009

2010

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2011

SHOW GRANTS FOR mysqltest_u1@localhost;

2012

Grants for mysqltest_u1@localhost

2013

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

2014

2015

# --

2016

# -- Check that global CREATE allows SHOW CREATE TABLE.

2017

# --

2018

2019

GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost;

2020

2021

SHOW GRANTS FOR mysqltest_u1@localhost;

2022

Grants for mysqltest_u1@localhost

2023

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

2024

GRANT CREATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2025

2026

# connection: con1 (mysqltest_u1@mysqltest_db1)

2027

2028

SHOW CREATE TABLE t1;

2029

Table Create Table

2030

t1 CREATE TABLE `t1` (

2031

`a` int(11) DEFAULT NULL

2032

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2033

2034

# connection: default

2035

2036

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2037

SHOW GRANTS FOR mysqltest_u1@localhost;

2038

Grants for mysqltest_u1@localhost

2039

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

2040

2041

# --

2042

# -- Check that global DROP allows SHOW CREATE TABLE.

2043

# --

2044

2045

GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost;

2046

2047

SHOW GRANTS FOR mysqltest_u1@localhost;

2048

Grants for mysqltest_u1@localhost

2049

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

2050

GRANT DROP ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2051

2052

# connection: con1 (mysqltest_u1@mysqltest_db1)

2053

2054

SHOW CREATE TABLE t1;

2055

Table Create Table

2056

t1 CREATE TABLE `t1` (

2057

`a` int(11) DEFAULT NULL

2058

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2059

2060

# connection: default

2061

2062

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2063

SHOW GRANTS FOR mysqltest_u1@localhost;

2064

Grants for mysqltest_u1@localhost

2065

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

2066

2067

# --

2068

# -- Check that global ALTER allows SHOW CREATE TABLE.

2069

# --

2070

2071

GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost;

2072

2073

SHOW GRANTS FOR mysqltest_u1@localhost;

2074

Grants for mysqltest_u1@localhost

2075

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

2076

GRANT ALTER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2077

2078

# connection: con1 (mysqltest_u1@mysqltest_db1)

2079

2080

SHOW CREATE TABLE t1;

2081

Table Create Table

2082

t1 CREATE TABLE `t1` (

2083

`a` int(11) DEFAULT NULL

2084

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2085

2086

# connection: default

2087

2088

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2089

SHOW GRANTS FOR mysqltest_u1@localhost;

2090

Grants for mysqltest_u1@localhost

2091

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

2092

2093

# --

2094

# -- Check that global INDEX allows SHOW CREATE TABLE.

2095

# --

2096

2097

GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost;

2098

2099

SHOW GRANTS FOR mysqltest_u1@localhost;

2100

Grants for mysqltest_u1@localhost

2101

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

2102

GRANT INDEX ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2103

2104

# connection: con1 (mysqltest_u1@mysqltest_db1)

2105

2106

SHOW CREATE TABLE t1;

2107

Table Create Table

2108

t1 CREATE TABLE `t1` (

2109

`a` int(11) DEFAULT NULL

2110

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2111

2112

# connection: default

2113

2114

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2115

SHOW GRANTS FOR mysqltest_u1@localhost;

2116

Grants for mysqltest_u1@localhost

2117

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

2118

2119

# --

2120

# -- Check that global REFERENCES allows SHOW CREATE TABLE.

2121

# --

2122

2123

GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost;

2124

2125

SHOW GRANTS FOR mysqltest_u1@localhost;

2126

Grants for mysqltest_u1@localhost

2127

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

2128

GRANT REFERENCES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2129

2130

# connection: con1 (mysqltest_u1@mysqltest_db1)

2131

2132

SHOW CREATE TABLE t1;

2133

Table Create Table

2134

t1 CREATE TABLE `t1` (

2135

`a` int(11) DEFAULT NULL

2136

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2137

2138

# connection: default

2139

2140

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2141

SHOW GRANTS FOR mysqltest_u1@localhost;

2142

Grants for mysqltest_u1@localhost

2143

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

2144

2145

# --

2146

# -- Check that global GRANT OPTION allows SHOW CREATE TABLE.

2147

# --

2148

2149

GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost;

2150

2151

SHOW GRANTS FOR mysqltest_u1@localhost;

2152

Grants for mysqltest_u1@localhost

2153

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

2154

GRANT USAGE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION

2155

2156

# connection: con1 (mysqltest_u1@mysqltest_db1)

2157

2158

SHOW CREATE TABLE t1;

2159

Table Create Table

2160

t1 CREATE TABLE `t1` (

2161

`a` int(11) DEFAULT NULL

2162

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2163

2164

# connection: default

2165

2166

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2167

SHOW GRANTS FOR mysqltest_u1@localhost;

2168

Grants for mysqltest_u1@localhost

2169

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

2170

2171

# --

2172

# -- Check that global CREATE VIEW allows SHOW CREATE TABLE.

2173

# --

2174

2175

GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;

2176

2177

SHOW GRANTS FOR mysqltest_u1@localhost;

2178

Grants for mysqltest_u1@localhost

2179

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

2180

GRANT CREATE VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2181

2182

# connection: con1 (mysqltest_u1@mysqltest_db1)

2183

2184

SHOW CREATE TABLE t1;

2185

Table Create Table

2186

t1 CREATE TABLE `t1` (

2187

`a` int(11) DEFAULT NULL

2188

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2189

2190

# connection: default

2191

2192

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2193

SHOW GRANTS FOR mysqltest_u1@localhost;

2194

Grants for mysqltest_u1@localhost

2195

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

2196

2197

# --

2198

# -- Check that global SHOW VIEW allows SHOW CREATE TABLE.

2199

# --

2200

2201

GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;

2202

2203

SHOW GRANTS FOR mysqltest_u1@localhost;

2204

Grants for mysqltest_u1@localhost

2205

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

2206

GRANT SHOW VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'

2207

2208

# connection: con1 (mysqltest_u1@mysqltest_db1)

2209

2210

SHOW CREATE TABLE t1;

2211

Table Create Table

2212

t1 CREATE TABLE `t1` (

2213

`a` int(11) DEFAULT NULL

2214

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2215

2216

# connection: default

2217

2218

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2219

SHOW GRANTS FOR mysqltest_u1@localhost;

2220

Grants for mysqltest_u1@localhost

2221

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

2222

2223

# --

2224

# -- Check that table-level SELECT allows SHOW CREATE TABLE.

2225

# --

2226

2227

GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2228

2229

SHOW GRANTS FOR mysqltest_u1@localhost;

2230

Grants for mysqltest_u1@localhost

2231

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

2232

GRANT SELECT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2233

2234

# connection: con1 (mysqltest_u1@mysqltest_db1)

2235

2236

SHOW CREATE TABLE t1;

2237

Table Create Table

2238

t1 CREATE TABLE `t1` (

2239

`a` int(11) DEFAULT NULL

2240

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2241

2242

# connection: default

2243

2244

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2245

SHOW GRANTS FOR mysqltest_u1@localhost;

2246

Grants for mysqltest_u1@localhost

2247

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

2248

2249

# --

2250

# -- Check that table-level INSERT allows SHOW CREATE TABLE.

2251

# --

2252

2253

GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2254

2255

SHOW GRANTS FOR mysqltest_u1@localhost;

2256

Grants for mysqltest_u1@localhost

2257

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

2258

GRANT INSERT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2259

2260

# connection: con1 (mysqltest_u1@mysqltest_db1)

2261

2262

SHOW CREATE TABLE t1;

2263

Table Create Table

2264

t1 CREATE TABLE `t1` (

2265

`a` int(11) DEFAULT NULL

2266

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2267

2268

# connection: default

2269

2270

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2271

SHOW GRANTS FOR mysqltest_u1@localhost;

2272

Grants for mysqltest_u1@localhost

2273

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

2274

2275

# --

2276

# -- Check that table-level UPDATE allows SHOW CREATE TABLE.

2277

# --

2278

2279

GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2280

2281

SHOW GRANTS FOR mysqltest_u1@localhost;

2282

Grants for mysqltest_u1@localhost

2283

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

2284

GRANT UPDATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2285

2286

# connection: con1 (mysqltest_u1@mysqltest_db1)

2287

2288

SHOW CREATE TABLE t1;

2289

Table Create Table

2290

t1 CREATE TABLE `t1` (

2291

`a` int(11) DEFAULT NULL

2292

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2293

2294

# connection: default

2295

2296

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2297

SHOW GRANTS FOR mysqltest_u1@localhost;

2298

Grants for mysqltest_u1@localhost

2299

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

2300

2301

# --

2302

# -- Check that table-level DELETE allows SHOW CREATE TABLE.

2303

# --

2304

2305

GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2306

2307

SHOW GRANTS FOR mysqltest_u1@localhost;

2308

Grants for mysqltest_u1@localhost

2309

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

2310

GRANT DELETE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2311

2312

# connection: con1 (mysqltest_u1@mysqltest_db1)

2313

2314

SHOW CREATE TABLE t1;

2315

Table Create Table

2316

t1 CREATE TABLE `t1` (

2317

`a` int(11) DEFAULT NULL

2318

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2319

2320

# connection: default

2321

2322

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2323

SHOW GRANTS FOR mysqltest_u1@localhost;

2324

Grants for mysqltest_u1@localhost

2325

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

2326

2327

# --

2328

# -- Check that table-level CREATE allows SHOW CREATE TABLE.

2329

# --

2330

2331

GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2332

2333

SHOW GRANTS FOR mysqltest_u1@localhost;

2334

Grants for mysqltest_u1@localhost

2335

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

2336

GRANT CREATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2337

2338

# connection: con1 (mysqltest_u1@mysqltest_db1)

2339

2340

SHOW CREATE TABLE t1;

2341

Table Create Table

2342

t1 CREATE TABLE `t1` (

2343

`a` int(11) DEFAULT NULL

2344

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2345

2346

# connection: default

2347

2348

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2349

SHOW GRANTS FOR mysqltest_u1@localhost;

2350

Grants for mysqltest_u1@localhost

2351

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

2352

2353

# --

2354

# -- Check that table-level DROP allows SHOW CREATE TABLE.

2355

# --

2356

2357

GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2358

2359

SHOW GRANTS FOR mysqltest_u1@localhost;

2360

Grants for mysqltest_u1@localhost

2361

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

2362

GRANT DROP ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2363

2364

# connection: con1 (mysqltest_u1@mysqltest_db1)

2365

2366

SHOW CREATE TABLE t1;

2367

Table Create Table

2368

t1 CREATE TABLE `t1` (

2369

`a` int(11) DEFAULT NULL

2370

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2371

2372

# connection: default

2373

2374

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2375

SHOW GRANTS FOR mysqltest_u1@localhost;

2376

Grants for mysqltest_u1@localhost

2377

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

2378

2379

# --

2380

# -- Check that table-level ALTER allows SHOW CREATE TABLE.

2381

# --

2382

2383

GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2384

2385

SHOW GRANTS FOR mysqltest_u1@localhost;

2386

Grants for mysqltest_u1@localhost

2387

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

2388

GRANT ALTER ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2389

2390

# connection: con1 (mysqltest_u1@mysqltest_db1)

2391

2392

SHOW CREATE TABLE t1;

2393

Table Create Table

2394

t1 CREATE TABLE `t1` (

2395

`a` int(11) DEFAULT NULL

2396

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2397

2398

# connection: default

2399

2400

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2401

SHOW GRANTS FOR mysqltest_u1@localhost;

2402

Grants for mysqltest_u1@localhost

2403

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

2404

2405

# --

2406

# -- Check that table-level INDEX allows SHOW CREATE TABLE.

2407

# --

2408

2409

GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2410

2411

SHOW GRANTS FOR mysqltest_u1@localhost;

2412

Grants for mysqltest_u1@localhost

2413

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

2414

GRANT INDEX ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2415

2416

# connection: con1 (mysqltest_u1@mysqltest_db1)

2417

2418

SHOW CREATE TABLE t1;

2419

Table Create Table

2420

t1 CREATE TABLE `t1` (

2421

`a` int(11) DEFAULT NULL

2422

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2423

2424

# connection: default

2425

2426

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2427

SHOW GRANTS FOR mysqltest_u1@localhost;

2428

Grants for mysqltest_u1@localhost

2429

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

2430

2431

# --

2432

# -- Check that table-level REFERENCES allows SHOW CREATE TABLE.

2433

# --

2434

2435

GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2436

2437

SHOW GRANTS FOR mysqltest_u1@localhost;

2438

Grants for mysqltest_u1@localhost

2439

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

2440

GRANT REFERENCES ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2441

2442

# connection: con1 (mysqltest_u1@mysqltest_db1)

2443

2444

SHOW CREATE TABLE t1;

2445

Table Create Table

2446

t1 CREATE TABLE `t1` (

2447

`a` int(11) DEFAULT NULL

2448

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2449

2450

# connection: default

2451

2452

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2453

SHOW GRANTS FOR mysqltest_u1@localhost;

2454

Grants for mysqltest_u1@localhost

2455

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

2456

2457

# --

2458

# -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE.

2459

# --

2460

2461

GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2462

2463

SHOW GRANTS FOR mysqltest_u1@localhost;

2464

Grants for mysqltest_u1@localhost

2465

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

2466

GRANT USAGE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION

2467

2468

# connection: con1 (mysqltest_u1@mysqltest_db1)

2469

2470

SHOW CREATE TABLE t1;

2471

Table Create Table

2472

t1 CREATE TABLE `t1` (

2473

`a` int(11) DEFAULT NULL

2474

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2475

2476

# connection: default

2477

2478

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2479

SHOW GRANTS FOR mysqltest_u1@localhost;

2480

Grants for mysqltest_u1@localhost

2481

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

2482

2483

# --

2484

# -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE.

2485

# --

2486

2487

GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2488

2489

SHOW GRANTS FOR mysqltest_u1@localhost;

2490

Grants for mysqltest_u1@localhost

2491

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

2492

GRANT CREATE VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2493

2494

# connection: con1 (mysqltest_u1@mysqltest_db1)

2495

2496

SHOW CREATE TABLE t1;

2497

Table Create Table

2498

t1 CREATE TABLE `t1` (

2499

`a` int(11) DEFAULT NULL

2500

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2501

2502

# connection: default

2503

2504

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2505

SHOW GRANTS FOR mysqltest_u1@localhost;

2506

Grants for mysqltest_u1@localhost

2507

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

2508

2509

# --

2510

# -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE.

2511

# --

2512

2513

GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;

2514

2515

SHOW GRANTS FOR mysqltest_u1@localhost;

2516

Grants for mysqltest_u1@localhost

2517

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

2518

GRANT SHOW VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'

2519

2520

# connection: con1 (mysqltest_u1@mysqltest_db1)

2521

2522

SHOW CREATE TABLE t1;

2523

Table Create Table

2524

t1 CREATE TABLE `t1` (

2525

`a` int(11) DEFAULT NULL

2526

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2527

2528

# connection: default

2529

2530

REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;

2531

SHOW GRANTS FOR mysqltest_u1@localhost;

2532

Grants for mysqltest_u1@localhost

2533

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

2534

2535

# --

2536

# -- Cleanup.

2537

# --

2538

2539

DROP DATABASE mysqltest_db1;

2540

DROP USER mysqltest_u1@localhost;

2541

2542

# End of Bug#38347.

2543

2544

2545

# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES

2546

# DIFFERENTLY'.

2547

2548

drop database if exists mysqltest_db1;

2549

create database mysqltest_db1;

2550

create user mysqltest_u1;

2551

# Both GRANT statements below should fail with the same error.

2552

grant execute on function mysqltest_db1.f1 to mysqltest_u1;

2553

ERROR 42000: FUNCTION or PROCEDURE f1 does not exist

2554

grant execute on procedure mysqltest_db1.p1 to mysqltest_u1;

2555

ERROR 42000: FUNCTION or PROCEDURE p1 does not exist

2556

# Let us show that GRANT behaviour for routines is consistent

2557

# with GRANT behaviour for tables. Attempt to grant privilege

2558

# on non-existent table also results in an error.

2559

grant select on mysqltest_db1.t1 to mysqltest_u1;

2560

ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist

2561

show grants for mysqltest_u1;

2562

Grants for mysqltest_u1@%

2563

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

2564

drop database mysqltest_db1;

2565

drop user mysqltest_u1;

2566

2567

# Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS

2568

# DATABASE SECURITY

2569

2570

DROP DATABASE IF EXISTS secret;

2571

DROP DATABASE IF EXISTS no_such_db;

2572

CREATE DATABASE secret;

2573

GRANT USAGE ON *.* TO 'untrusted'@localhost;

2574

# Connection con1

2575

SHOW GRANTS;

2576

Grants for untrusted@localhost

2577

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

2578

SHOW DATABASES;

2579

Database

2580

information_schema

2581

test

2582

# Both statements below should fail with the same error.

2583

# They used to give different errors, thereby

2584

# hinting that the secret database exists.

2585

CREATE PROCEDURE no_such_db.foo() BEGIN END;

2586

ERROR 42000: Access denied for user 'untrusted'@'localhost' to database 'no_such_db'

2587

CREATE PROCEDURE secret.peek_at_secret() BEGIN END;

2588

ERROR 42000: Access denied for user 'untrusted'@'localhost' to database 'secret'

2589

# Connection default

2590

DROP USER 'untrusted'@localhost;

2591

DROP DATABASE secret;

2592

2593

# Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY -

2594

# REQUIRES FLUSH PRIVILEGES

2595

2596

CREATE USER foo@'127.0.0.1';

2597

GRANT ALL ON *.* TO foo@'127.0.0.1';

2598

# First attempt, should connect successfully

2599

SELECT user(), current_user();

2600

user() current_user()

2601

foo@localhost foo@127.0.0.1

2602

# Rename the user

2603

RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0';

2604

# Second attempt, should connect successfully as its valid mask

2605

# This was failing without fix

2606

SELECT user(), current_user();

2607

user() current_user()

2608

foo@localhost foo@127.0.0.0/255.0.0.0

2609

# Rename the user back to original

2610

RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1';

2611

# Third attempt, should connect successfully

2612

SELECT user(), current_user();

2613

user() current_user()

2614

foo@localhost foo@127.0.0.1

2615

# Clean-up

2616

DROP USER foo@'127.0.0.1';

2617

# End of Bug#12766319

Older »