MySQL Bugs: #41313: network administrator does not have TRIGGER and EVENT privileges

Bug #41313	network administrator does not have TRIGGER and EVENT privileges
Submitted:	8 Dec 2008 23:31	Modified:	8 Oct 12:09
Reporter:	John Kienitz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Any (Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	trigger

Description:
The default privileges for the network administrator 'root'@'%' do not have TRIGGER and EVENT privileges.

They cannot give themselves these privileges, and cannot give them to other users.

Work around is to edit the user table and give these privileges to this user, then reload privileges.

This user has all other privileges SUPER, GRANT, etc, but is unable to give EVENT and TRIGGER to itself.  Very strange.  Should not GRANT allow this?

How to repeat:
Install MySQL 5.1.30.  Look at privileges given to 'root'@'%'

Suggested fix:
add these privileges to the default privileges for this user.

Thank you for the report.

What do you mean by "network administrator 'root'@'%' "? I can not repeat such problem if I give correct rights to an user:

GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost' WITH GRANT OPTION;
show grants for 'foo'@'localhost';
Grants for foo@localhost
GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost' WITH GRANT OPTION
select  Event_priv, Trigger_priv from mysql.user where user='foo';
Event_priv      Trigger_priv
Y       Y

Maybe this is windows only.

Do a new install of MySql 5.1.30.

look at the privileges for the network root user at '%'.

It does NOT have TRIGGER or EVENT privileges.

look at the privileges for the network user at 'localhost'.

It does have TRIGGER and EVENT privileges.

Thank you for the feedback.

Verified as described: no trigger and events rights for root@% on Windows, although there are such rights on UNIX.

Workaround: manually edit mysql.user table, then flush privileges.

Bug #42260 was marked as a duplicate of this one.

Posted by developer:
 
This is fixed now. root does get trigger etc by default. 
Fruthermore: root can grant privileges that are not granted to it. See https://dev.mysql.com/doc/refman/9.3/en/grant.html:

To grant a privilege with GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE privilege for the grant tables in the mysql system schema, you can grant any account any privilege.) 

Here's the current SHOW GRANTS:

mysql> show grants for root@localhost\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,CREATE_SPATIAL_REFERENCE_SYSTEM,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_PRIVILEGES,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,OPTIMIZE_LOCAL_TABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.000 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 9.6.0-debug |
+-------------+
1 row in set (0.000 sec)