MySQL Bugs: #29939: wrong error message when trying to create an event without the EVENT grant

Bug #29939	wrong error message when trying to create an event without the EVENT grant
Submitted:	20 Jul 2007 16:34	Modified:	4 Dec 2007 16:34
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.1.20	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a user that does not have the EVENT grant is trying to create an event, an error message regarding not having access to the database is displayed instead of an error indocating the user has not priveleges to create an event.

How to repeat:
Start a mysql server and log into the client as root

run the following sql:

mysql> create database ev_test;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'no_ev_privs'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant CREATE, SELECT, INSERT, DELETE on ev_test.* to 'no_ev_privs'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Log into the system as the above created user and run the following SQL:

# Just confirming we are who we think we are
mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for no_ev_privs@localhost                                                 |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'no_ev_privs'@'localhost'                                  |
| GRANT SELECT, INSERT, DELETE, CREATE ON `ev_test`.* TO 'no_ev_privs'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use ev_test;
Database changed
mysql> create EVENT ev1 ON SCHEDULE EVERY 1 HOUR DO SELECT NOW();
ERROR 1044 (42000): Access denied for user 'no_ev_privs'@'localhost' to database 'ev_test'
>>> There is no acess issue to the database and the user has access to it (as 
    clear from the above 'use ev_test' command and show grants

mysql> quit

Suggested fix:
Display the correct error message

I could not reproduce using the following test script, investigating further:

DROP DATABASE IF EXISTS events_test;
CREATE DATABASE events_test;

use events_test;
create user 'no_ev_privs'@'localhost';
grant CREATE, SELECT, INSERT, DELETE on events_test.* to 'no_ev_privs'@'localhost';

connect (conn_no_ev_privs, localhost, no_ev_privs,,);

show grants;
create EVENT ev1 ON SCHEDULE EVERY 1 HOUR DO SELECT NOW();
select event_name,status from information_schema.events;
disconnect conn_no_ev_privs ;

Cannot reproduce using 5.1.23-beta. Here are the results of the previous test script:

DROP DATABASE IF EXISTS events_test;
CREATE DATABASE events_test;
use events_test;
create user 'no_ev_privs'@'localhost';
grant CREATE, SELECT, INSERT, DELETE on events_test.* to 'no_ev_privs'@'localhost';
show grants;
Grants for no_ev_privs@localhost
GRANT USAGE ON *.* TO 'no_ev_privs'@'localhost'
GRANT SELECT, INSERT, DELETE, CREATE ON `events_test`.* TO 'no_ev_privs'@'localhost'
create EVENT ev1 ON SCHEDULE EVERY 1 HOUR DO SELECT NOW();
select event_name,status from information_schema.events;
event_name	status
ev1	ENABLED

The problem still exists in 5.1.23:

omer@linux:~/source/src51_1022/mysql-test> ../client/mysql --user=root --port=9306 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-beta-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database ev_test;
Query OK, 1 row affected (0.04 sec)

mysql> create user 'no_ev_privs'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant CREATE, SELECT, INSERT, DELETE on ev_test.* to 'no_ev_privs'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

omer@linux:~/source/src51_1022/mysql-test> ../client/mysql --user=no_ev_privs --port=9306 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-beta-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for no_ev_privs@localhost                                                 |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'no_ev_privs'@'localhost'                                  |
| GRANT SELECT, INSERT, DELETE, CREATE ON `ev_test`.* TO 'no_ev_privs'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use ev_test;
Database changed
mysql> create EVENT ev1 ON SCHEDULE EVERY 1 MINUTE DO SET @a1=1;
ERROR 1044 (42000): Access denied for user 'no_ev_privs'@'localhost' to database 'ev_test'
mysql> quit
Bye
omer@linux:~/source/src51_1022/mysql-test>

Omer, Indeed my test script was wrong. It didn't do "use events;" to switch to the new db, which was the problem. I've reproduced the problem.

Thank you for a valid feature request.
EVENT privilege is a database-level privilege, it can not be granted to a specific event:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

For all database-level privileges MySQL yields "ER_DBACCESS_DENIED_ERROR" whenever the privilege is missing.
This is also true for CREATE TEMPORARY TABLE,  LOCK TABLES, CALL, CREATE/ALTER PROCEDURE -- the same error message is returned:
mysql> create temporary table t1 (a int);
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'test'
mysql> create procedure p1() begin end;
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'test'

mysql> lock table t1 write;
ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'test'

I agree the error message could be more clear.