MySQL Bugs: #18478: GRANT ALL PRIVILEGES does not give all the privileges for CREATE

Bug #18478	GRANT ALL PRIVILEGES does not give all the privileges for CREATE
Submitted:	24 Mar 2006 7:36	Modified:	30 Mar 2006 12:00
Reporter:	vadim likhota	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
With the granting a user all the rights to work with one database by means of GRANT ALL PRIVILEGES statement the user does not receive the rights to create triggers, though he is able to create tables. 
Example: The database test01 and the user test01 managing it are created. Then the user test01 creats the table and the trigger.

How to repeat:
[vadim@vadim ~]$ mysql -u root -h myserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.19-standard

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

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

mysql> grant all privileges on test01.* to 'test01'@'%' with grant option;
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
[vadim@vadim ~]$ mysql -u test01 -h myserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.19-standard

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

mysql> use test01
Database changed
mysql> create table test01
-> (
->     test_id             integer(5) not null AUTO_INCREMENT,
->     test_str            varchar(30) not null,
->     user_ins            varchar(30) not null,
->     date_ind            char(10) not null,
->     constraint test01_p primary key (test_id)
-> )
-> AUTO_INCREMENT = 0
-> engine MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> create trigger test01_bi
->         before insert
->         on test01
->         for each row
-> begin
->         set NEW.user_ins := user();
->         set NEW.date_ins := curdate();
-> end;
-> //
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
mysql>

Suggested fix:
Statements GRANT ALL and GRANT CREATE must grant the rights to create any objects within specified bounds.

The exclusion: statement GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'  does not able to give the rights to create objects in system database (mysql database).

Thank you for a problem report. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html) carefully and send the results of the following statement:

SHOW VARIABLES LIKE 'log_bin%';

from your system. It may be not a bug, really.

I added "log-bin" in my.cnf in section [server] and restarted mysql.

[vadim@vadim ~]$ mysql -u test01 -h myserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-standard-log

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

mysql> use test01
Database changed
mysql> delimiter //
mysql> create trigger test01_bi
    -> before insert
    -> on test01
    -> for each row
    -> begin
    -> set NEW.user_ins := user();
    -> set NEW.date_ins := curdate();
    -> end;
    -> //
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
mysql> SHOW VARIABLES LIKE 'log_bin%';
    -> //
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[vadim@vadim ~]$ mysql -u root -h myserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-standard-log

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

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[vadim@vadim ~]$ mysql -u test01 -h myserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19-standard-log

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

mysql> use test01
Database changed
mysql> delimiter //
mysql> create trigger test01
    -> before insert
    -> on test01
    -> for each row
    -> begin
    ->   set NEW.user_ins := user();
    ->   set NEW.date_ins := curdate();
    -> end;
    -> //
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
mysql> SHOW VARIABLES LIKE 'log_bin%';
    -> //
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

The results observed are as documented at:

http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html

The restrictions noted there for stored functions also apply
to triggers.

Sorry, but in version 5.0.x it is not a bug. You need SUPER priviledge just to CREATE TRIGGER, anyway (http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html):

"This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. CREATE TRIGGER was added in MySQL 5.0.2. Currently, its use requires the SUPER  privilege."

Compare to http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html:

"This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Currently, CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. (This statement requires the SUPER  privilege prior to MySQL 5.1.6.)"

In 5.1.6 and newer versions that notes from http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html should apply as I supposed.