Bug #107139 | CREATE USER IF NOT EXISTS fails if user is definer of view/trigger for nonroot | ||
---|---|---|---|
Submitted: | 27 Apr 2022 12:30 | Modified: | 29 Jul 2024 10:01 |
Reporter: | Martin Bezdíček | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[27 Apr 2022 12:30]
Martin Bezdíček
[27 Apr 2022 13:45]
MySQL Verification Team
Hi Mr. BEzdicek, Thank you for your bug report. However, you are using an ancient release of 8.0. Please try our latest 8.0.29 and let us know if the problem occurs. If the problem occurs again, send us a fully repeatable test case. Last, but not least, you should have a test case that is repeatable on our own 8.0.29 server binary and our own utilities, without using any software from the third parties. We are waiting on your feedback.
[28 Apr 2022 6:49]
Martin Bezdíček
I have tested it on version 8.0.29 now and directly MySQL and not using Percona at all. Steps to reproduce the issue: Create superuser: CREATE USER "superuser"@"localhost"; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO "superuser"@"localhost"; Create testinguser: CREATE USER IF NOT EXISTS 'testinguser'@'localhost'; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO 'testinguser'@'localhost'; (Not all the rights are necessary) Try running create user as superuser and it will work now fine: CREATE USER IF NOT EXISTS 'testinguser'@'localhost'; Query OK, 0 rows affected, 1 warning (0.01 sec) Create trigger with testing user as definer: CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE DEFINER="testinguser"@"localhost" TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; And now try to create user as superuser and it will end in error: CREATE USER IF NOT EXISTS 'testinguser'@'localhost'; ERROR 4006 (HY000): Operation CREATE USER failed for 'testinguser'@'localhost' as it is referenced as a definer account in a trigger. Running the command as root still works correctly, only happens when it is not root running the command.
[28 Apr 2022 6:53]
huahua xu
Hi, Martin Bezdíček It is not a bug. The reason is that your account permission is not enough, which should have SET_USER_ID privilege.
[28 Apr 2022 6:58]
huahua xu
mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for drsroot@% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 `drsroot`@`%` | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (3.89 sec) mysql> CREATE USER IF NOT EXISTS `drstest`@`%`; ERROR 4006 (HY000): Operation CREATE USER failed for 'drstest'@'%' as it is referenced as a definer account in a view. After grants SET_USER_ID privilege TO `drsroot`@`%`; mysql> show grants; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for drsroot@% | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 `drsroot`@`%` | | GRANT SET_USER_ID ON *.* TO `drsroot`@`%` | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (1.99 sec) mysql> CREATE USER IF NOT EXISTS `drstest`@`%`; Query OK, 0 rows affected, 1 warning (4.46 sec)
[28 Apr 2022 7:14]
Martin Bezdíček
Thank you very much for the information, never found this detail on user rights. Really thank you so much. So this can be closed.
[28 Apr 2022 13:26]
MySQL Verification Team
HI Mr. huahua xu, Thank you very much for spotting and solving the problem expounded by Mr. Bezdíček. This bug is closed now.
[25 Aug 2023 15:59]
Mx Dog
This is happening to me on debian 12 with 8.34 as root with all grants and privileges trying to restore from a mysqlsh dump
[28 Aug 2023 5:55]
MySQL Verification Team
Bug #112184 marked as duplicate of this one
[29 Jul 2024 10:01]
MySQL Verification Team
HI Mr. Bezdicek, It turns out that this is still a bug. A very minor bug, but a bug .......... Verified for 8.0 and higher versions. Thank you.
[5 Aug 2024 11:54]
Zoltan Czirkos
Given that https://bugs.mysql.com/bug.php?id=115763 was marked as a duplicate of this one, the solution should be as such: First requirement: MySQL Server should allow creating a trigger with "DEFINER = ..." for a nonexistent (!) user. Like this: CREATE DEFINER = 'nonexistentuser'@'%' TRIGGER ... Otherwise, the script in the first scenario described in https://bugs.mysql.com/bug.php?id=115763 would fail. Second requirement: MySQL Server should keep (not drop) a trigger when the definer user is dropped. Like this: CREATE USER 'user1' IDENTIFIED BY 'password'; CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` ....; DROP USER 'user1'@'%'; ... and at this point, `schema1`.`table1_BEFORE_INSERT` should still exist and work, otherwise it would break the script of the second scenario as described in https://bugs.mysql.com/bug.php?id=115763 . Question is though, which user should "own" the trigger, if the original owner is dropped. Also important to note, that the last paragraph of the documentation on DROP USER at https://dev.mysql.com/doc/refman/8.4/en/drop-user.html is: > DROP USER does not automatically drop or invalidate databases or objects within them that the old user created. This includes stored programs or views for which the DEFINER attribute names the dropped user. Attempts to access such objects may produce an error if they execute in definer security context. (For information about security context, see Section 27.6, “Stored Object Access Control”.) It is unclear how it should be handled then. In layman's terms, this means that a trigger might break if the definer user does not exist. But to make sure to not break the second requirement above, we would have to say that recreating the dropped user with the same name will have to "repair" the broken trigger.
[5 Aug 2024 12:02]
MySQL Verification Team
Thank you, Mr. Czirkos, Your thoughts on the subject have been forwarded to our Development team in charge ......