MySQL Bugs: #108085: Permissions to set DEFINER aren't granular enough

Bug #108085	Permissions to set DEFINER aren't granular enough
Submitted:	8 Aug 2022 5:05	Modified:	9 Aug 2022 15:33
Reporter:	Andy Edwards	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The permissions model for creating views and stored procedures is too all-or-nothing.  Either:

1. A user has SET_USER_ID privilege and can run stored procedure code as any user
2. A user doesn't have SET_USER_ID and can only create views/stored procedures for the hostname/IP address they're connected on.

There should at least be a way to allow a user `foo` to set DEFINER = `foo` regardless of host.

I'm working with MySQL on shared hosting and this is extremely annoying because I could totally lose access to my view and stored procedure definitions if I lose access to a given hostname or IP address.  Even if I use SQL SECURITY INVOKER, I could lose the ability to dump views/stored procedures `foo` created.

How to repeat:
CREATE VIEW temp AS ...

Log in as same user/different host or IP

SHOW CREATE VIEW temp;

You can't see the definition.

CREATE DEFINER = `foo` VIEW TEMP AS ...

You get a 1227 error unless you have SET_USER_ID privilege.

Hi Mr. Edwards,

Thank you for your feature request report.

However, we do not see how we can implement what you are asking for ....

First and first of all, our security system is based on the SQL standard and behaviour that has become industry standard. 

Next, we already have the feature to allow same user to connect from a range of hostnames or IP addresses by using the wildcards.

This is fully described in our Reference Manual.

Not a bug.

Hi Mr. Edwards,

This is just to inform you that we decided to verify your report. Since your report is actually a feature request, by verifying it we are leaving the decision on the feasibility of the feature to our internal department, which is in charge of planning the feature requests.

Verified as a feature request.

Yes.  If I dump the remote database and restore it on a mysql instance on my local machine, the tables work fine on my local machine, regardless of what client host I created them on.  But the views and stored procedures don't unless I created them from a localhost socket connection to the remote database.

This difference feels wrong; it's like I don't get to fully be the 'foo' user I created myself.  And as far as I can tell I wouldn't have to deal with this hassle on Postgres, Oracle, T-Sql, IBM, or Teradata.

So I don't understand why this is desirable behavior.   It seems to me it would be desirable for a user 'foo' without SET_USER_ID to be able to set DEFINER = 'foo'@'%'.

And I wish I could disable any distinction between @'%' and @'localhost' accounts so that a user is a user.

Thank you, Mr. Edwards,

We shall forward your additional thoughts to the department in charge.