1set local sql_mode=""; 2set global sql_mode=""; 3# 4# Tests for checking permission denied on CREATE OR REPLACE if DROP 5# access is revoked 6# 7# These statements do not need special tests for CREATE OR REPLACE, 8# because they do not have separate permissions for create and drop: 9# CREATE OR REPLACE EVENT (uses EVENT_ACL for both CREATE and DROP) 10# CREATE OR DROP SERVER (uses SUPER_ALC for both CREATE and DROP) 11# CREATE OR DROP TRIGGER (uses TRIGGER_ACL for both CREATE and DROP) 12SELECT CURRENT_USER; 13CURRENT_USER 14root@localhost 15CREATE DATABASE db1; 16GRANT ALL ON db1.* TO mysqltest_1@localhost; 17REVOKE DROP ON db1.* FROM mysqltest_1@localhost; 18REVOKE ALTER ROUTINE ON db1.* FROM mysqltest_1@localhost; 19GRANT DELETE ON mysql.* TO mysqltest_1@localhost; 20REVOKE DELETE ON mysql.* FROM mysqltest_1@localhost; 21FLUSH PRIVILEGES; 22connect user_a, localhost, mysqltest_1,,; 23connection user_a; 24SELECT CURRENT_USER; 25CURRENT_USER 26mysqltest_1@localhost 27CREATE DATABASE db1; 28ERROR HY000: Can't create database 'db1'; database exists 29CREATE OR REPLACE DATABASE db1; 30ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'db1' 31CREATE OR REPLACE DATABASE db2; 32ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'db2' 33USE db1; 34CREATE OR REPLACE TABLE t1(id INT); 35ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' 36CREATE OR REPLACE PROCEDURE proc1 (OUT cnt INT) BEGIN END; 37ERROR 42000: alter routine command denied to user 'mysqltest_1'@'localhost' for routine 'db1.proc1' 38CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; 39ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' 40CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!'); 41ERROR 42000: alter routine command denied to user 'mysqltest_1'@'localhost' for routine 'db1.hello' 42CREATE OR REPLACE USER u1@localhost; 43ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 44CREATE OR REPLACE ROLE developer; 45ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation 46connection default; 47SELECT CURRENT_USER; 48CURRENT_USER 49root@localhost 50REVOKE ALL ON db1.* FROM mysqltest_1@localhost; 51DROP DATABASE IF EXISTS db2; 52Warnings: 53Note 1008 Can't drop database 'db2'; database doesn't exist 54DROP DATABASE db1; 55DROP USER mysqltest_1@localhost; 56set global sql_mode=default; 57