1295
1295
SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin");
1296
1296
SET PASSWORD FOR CURRENT_USER() = PASSWORD("");
1299
# Bug#57952: privilege change is not taken into account by EXECUTE.
1307
DROP DATABASE IF EXISTS mysqltest1;
1308
DROP DATABASE IF EXISTS mysqltest2;
1311
CREATE DATABASE mysqltest1;
1312
CREATE DATABASE mysqltest2;
1315
CREATE TABLE t1(a INT, b INT);
1316
INSERT INTO t1 VALUES (1, 1);
1318
CREATE TABLE t2(a INT);
1319
INSERT INTO t2 VALUES (2);
1321
CREATE TABLE mysqltest2.t3(a INT);
1322
INSERT INTO mysqltest2.t3 VALUES (4);
1324
CREATE USER testuser@localhost;
1325
GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost;
1326
GRANT SELECT(b) ON t1 TO testuser@localhost;
1327
GRANT SELECT ON t2 TO testuser@localhost;
1328
GRANT SELECT ON mysqltest2.* TO testuser@localhost;
1331
--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)
1332
--connect (bug57952_con1,localhost,testuser,,mysqltest1)
1333
PREPARE s1 FROM 'SELECT b FROM t1';
1334
PREPARE s2 FROM 'SELECT a FROM t2';
1335
PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2';
1337
CREATE PROCEDURE p1() SELECT b FROM t1;
1338
CREATE PROCEDURE p2() SELECT a FROM t2;
1339
CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2;
1346
--echo # Connection: default
1347
--connection default
1348
REVOKE SELECT ON t1 FROM testuser@localhost;
1349
GRANT SELECT(a) ON t1 TO testuser@localhost;
1350
REVOKE SELECT ON t2 FROM testuser@localhost;
1351
REVOKE SELECT ON mysqltest2.* FROM testuser@localhost;
1354
--echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1)
1355
--connection bug57952_con1
1356
--echo # - Check column-level privileges...
1357
--error ER_COLUMNACCESS_DENIED_ERROR
1360
--error ER_COLUMNACCESS_DENIED_ERROR
1363
--error ER_COLUMNACCESS_DENIED_ERROR
1366
--error ER_COLUMNACCESS_DENIED_ERROR
1369
--echo # - Check table-level privileges...
1370
--error ER_TABLEACCESS_DENIED_ERROR
1373
--error ER_TABLEACCESS_DENIED_ERROR
1376
--error ER_TABLEACCESS_DENIED_ERROR
1379
--echo # - Check database-level privileges...
1380
--error ER_DBACCESS_DENIED_ERROR
1381
SHOW TABLES FROM mysqltest2;
1383
--error ER_DBACCESS_DENIED_ERROR
1386
--error ER_DBACCESS_DENIED_ERROR
1390
--echo # Connection: default
1391
--connection default
1392
--disconnect bug57952_con1
1393
DROP DATABASE mysqltest1;
1394
DROP DATABASE mysqltest2;
1395
DROP USER testuser@localhost;
1401
--echo # Test for bug #36544 "DROP USER does not remove stored function
1402
--echo # privileges".
1404
create database mysqltest1;
1405
create function mysqltest1.f1() returns int return 0;
1406
create procedure mysqltest1.p1() begin end;
1408
--echo # 1) Check that DROP USER properly removes privileges on both
1409
--echo # stored procedures and functions.
1411
create user mysqluser1@localhost;
1412
grant execute on function mysqltest1.f1 to mysqluser1@localhost;
1413
grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;
1415
--echo # Quick test that granted privileges are properly reflected
1416
--echo # in privilege tables and in in-memory structures.
1417
show grants for mysqluser1@localhost;
1418
select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
1420
--echo # Create connection 'bug_36544_con1' as 'mysqluser1@localhost'.
1421
--connect (bug36544_con1,localhost,mysqluser1,,)
1422
call mysqltest1.p1();
1423
select mysqltest1.f1();
1426
--echo # Switch to connection 'default'.
1427
--connection default
1428
drop user mysqluser1@localhost;
1431
--echo # Test that dropping of user is properly reflected in
1432
--echo # both privilege tables and in in-memory structures.
1434
--echo # Switch to connection 'bug36544_con1'.
1435
--connection bug36544_con1
1436
--echo # The connection cold be alive but should not be able to
1437
--echo # access to any of the stored routines.
1438
--error ER_PROCACCESS_DENIED_ERROR
1439
call mysqltest1.p1();
1440
--error ER_PROCACCESS_DENIED_ERROR
1441
select mysqltest1.f1();
1442
--disconnect bug36544_con1
1445
--echo # Switch to connection 'default'.
1446
--connection default
1448
--echo # Now create user with the same name and check that he
1449
--echo # has not inherited privileges.
1450
create user mysqluser1@localhost;
1451
show grants for mysqluser1@localhost;
1452
select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
1454
--echo # Create connection 'bug_36544_con2' as 'mysqluser1@localhost'.
1455
--connect (bug36544_con2,localhost,mysqluser1,,)
1456
--echo # Newly created user should not be able to access any of the routines.
1457
--error ER_PROCACCESS_DENIED_ERROR
1458
call mysqltest1.p1();
1459
--error ER_PROCACCESS_DENIED_ERROR
1460
select mysqltest1.f1();
1462
--echo # Switch to connection 'default'.
1463
--connection default
1466
--echo # 2) Check that RENAME USER properly updates privileges on both
1467
--echo # stored procedures and functions.
1469
grant execute on function mysqltest1.f1 to mysqluser1@localhost;
1470
grant execute on procedure mysqltest1.p1 to mysqluser1@localhost;
1472
--echo # Create one more user to make in-memory hashes non-trivial.
1473
--echo # User names 'mysqluser11' and 'mysqluser10' were selected
1474
--echo # to trigger bug discovered during code inspection.
1475
create user mysqluser11@localhost;
1476
grant execute on function mysqltest1.f1 to mysqluser11@localhost;
1477
grant execute on procedure mysqltest1.p1 to mysqluser11@localhost;
1478
--echo # Also create a couple of tables to test for another bug
1479
--echo # discovered during code inspection (again table names were
1480
--echo # chosen especially to trigger the bug).
1481
create table mysqltest1.t11 (i int);
1482
create table mysqltest1.t22 (i int);
1483
grant select on mysqltest1.t22 to mysqluser1@localhost;
1484
grant select on mysqltest1.t11 to mysqluser1@localhost;
1486
--echo # Quick test that granted privileges are properly reflected
1487
--echo # in privilege tables and in in-memory structures.
1488
show grants for mysqluser1@localhost;
1489
select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
1490
select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';
1492
--echo # Switch to connection 'bug36544_con2'.
1493
--connection bug36544_con2
1494
call mysqltest1.p1();
1495
select mysqltest1.f1();
1496
select * from mysqltest1.t11;
1497
select * from mysqltest1.t22;
1500
--echo # Switch to connection 'default'.
1501
--connection default
1502
rename user mysqluser1@localhost to mysqluser10@localhost;
1505
--echo # Test that there are no privileges left for mysqluser1.
1507
--echo # Switch to connection 'bug36544_con2'.
1508
--connection bug36544_con2
1509
--echo # The connection cold be alive but should not be able to
1510
--echo # access to any of the stored routines or tables.
1511
--error ER_PROCACCESS_DENIED_ERROR
1512
call mysqltest1.p1();
1513
--error ER_PROCACCESS_DENIED_ERROR
1514
select mysqltest1.f1();
1515
--error ER_TABLEACCESS_DENIED_ERROR
1516
select * from mysqltest1.t11;
1517
--error ER_TABLEACCESS_DENIED_ERROR
1518
select * from mysqltest1.t22;
1519
--disconnect bug36544_con2
1522
--echo # Switch to connection 'default'.
1523
--connection default
1525
--echo # Now create user with the old name and check that he
1526
--echo # has not inherited privileges.
1527
create user mysqluser1@localhost;
1528
show grants for mysqluser1@localhost;
1529
select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost';
1530
select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost';
1532
--echo # Create connection 'bug_36544_con3' as 'mysqluser1@localhost'.
1533
--connect (bug36544_con3,localhost,mysqluser1,,)
1534
--echo # Newly created user should not be able to access to any of the
1535
--echo # stored routines or tables.
1536
--error ER_PROCACCESS_DENIED_ERROR
1537
call mysqltest1.p1();
1538
--error ER_PROCACCESS_DENIED_ERROR
1539
select mysqltest1.f1();
1540
--error ER_TABLEACCESS_DENIED_ERROR
1541
select * from mysqltest1.t11;
1542
--error ER_TABLEACCESS_DENIED_ERROR
1543
select * from mysqltest1.t22;
1544
--disconnect bug36544_con3
1547
--echo # Switch to connection 'default'.
1548
--connection default
1550
--echo # Now check that privileges became associated with a new user
1551
--echo # name - mysqluser10.
1553
show grants for mysqluser10@localhost;
1554
select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost';
1555
select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost';
1557
--echo # Create connection 'bug_36544_con4' as 'mysqluser10@localhost'.
1558
--connect (bug36544_con4,localhost,mysqluser10,,)
1559
call mysqltest1.p1();
1560
select mysqltest1.f1();
1561
select * from mysqltest1.t11;
1562
select * from mysqltest1.t22;
1563
--disconnect bug36544_con4
1566
--echo # Switch to connection 'default'.
1567
--connection default
1570
drop user mysqluser1@localhost;
1571
drop user mysqluser10@localhost;
1572
drop user mysqluser11@localhost;
1573
drop database mysqltest1;
1298
1576
--echo End of 5.0 tests