MySQL Bugs: #112184: cannot create user if they are listed as creator in views or routines

Bug #112184	cannot create user if they are listed as creator in views or routines
Submitted:	25 Aug 2023 16:47	Modified:	29 Aug 2023 3:47
Reporter:	Mx Dog	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Debian (ver 12 )
Assigned to:		CPU Architecture:	Any
Tags:	Bug #107139

Description:
CREATE USER IF NOT EXISTS `eqemu`@`%`;
ERROR 4006 (HY000): Operation CREATE USER failed for 'eqemu'@'%' as it is referenced as a definer account in a view.

after changing creator in the offending view ......

ERROR 4006 (HY000): Operation CREATE USER failed for 'eqemu'@'%' as it is referenced as a definer account in a stored routine.

this seems to be the same issue as Bug #107139 with the exception that I am running this as root with all grants and privileges so do not see a way to give root any more authority then it has. 

I am not even sure what the point of this check ( on user creation ) is anyway ? The user exists or it does not ? what does it matter if they are listed as a definer from a security or functional standpoint ? 

How to repeat:
in my case i am trying to load a dump from another computer into a fresh mysql instance the dump was created with mysqlsh utilities both instances are 8.34

 util.loadDump("/tmp/dump/", {dryRun :"false"})

also 

create user from terminal as root fails the same as the upload dump
mysql> CREATE USER IF NOT EXISTS `eqemu`@`%`;
ERROR 4006 (HY000): Operation CREATE USER failed for 'eqemu'@'%' as it is referenced as a definer account in a view.

here are the grants for root on this system :

mysql> show grants
    -> ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `*`.* TO `root`@`%` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                    |
| GRANT ALL PRIVILEGES ON `%`.* TO `root`@`%` WITH GRANT OPTION

Suggested fix:
remove this check on definers in user creation unless there is a good reason for it ? 

why root can not do this I do not know , is there an obscure server variable that needs switched to allow this if there are definers defined ?

sorry,  used creator instead of DEFINER in a couple of instances

Hello!

Thank you for the report and feedback.
IMHO this is duplicate of Bug #107139.
Have you tried the suggestion from Bug #107139? GRANT SET_USER_ID ON *.* TO `root`@`%`? Also see - https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_set-user-id

regards,
Umesh

yes i did all that and even granted super to a couple of accounts and root (didn't help) ,I  ended up just removing the offending user names . Will be using regular old mysqldump to csv  next time, had nothing but problems and OMG slow trying to load from the mysql shell utility this one problem turned out to be the least of them.

I  don't report bugs until i waste a few hours searching and working for an answer from people smarter then me.. 

like the "regex binary" problem I have with eqemu .  works in 8.0.21 but not 8.0.34 or from what i can figure out 8.0.22 so after hours setting up my server .. should i regress os to dedian whatever to get mysql 8.21 ( spend days back-porting dependencies to install it on deb12 ? .. another change that has drastic effects for some of my projects i don't understand the reason for ...

sorry beeching .. last couple of updates have caused problems and the changes don't really make sense to me ( or the reasons are pretty lame in the overall scheme of things ... haha )

Hi Mr. Dog,

Please, follow the developments in the original bug report, which is stated above.