MySQL Bugs: #113937: Changing user host limit filter results into Access denied for user error

Bug #113937	Changing user host limit filter results into Access denied for user error
Submitted:	8 Feb 2024 17:48	Modified:	9 Feb 2024 21:27
Reporter:	sakkanan Packiaraj	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Host Limit

Description:
When views are created mysql by default adds DEFINER & SQL SECURITY context. For instance created view is like
------------------------------
CREATE
ALGORITHM = UNDEFINED
DEFINER = `user`@`%`
SQL SECURITY DEFINER
VIEW `somestat` AS
SELECT * FROM
`some_db`.`somestats`
-------------------------------
When host limit is added to user like "10.10.%,%" (for security reasons) getting "Access denied for user 'user'@'10.10.%.%' (using password: YES)" while doing "select" on the view

Looks like the DEFINER match is not working. The only workaround is to recreate the view, which is not practical (for us) as there are 100s of DB and each DB has 100s of views.

Is there any other way to add host restriction to user or any other workaround available

Also its not easy to correlate that adding host restriction on user needs views recreation

How to repeat:
1. create user with default host limiting (`user`@`%`)
2. create a view 
    CREATE OR REPLACE VIEW `somestat` AS
      SELECT * FROM some_db`.`somestats`
3. do select on the view. ( confirm view works as expected)
4. change host limit filter for user to something that matches your IP ( like 'user'@'10.10.%.%' )
5. do a select on the view ( make sure that you are in the network that matches the new restriction)
    This will return error Access denied for user 'user'@'10.10.%.%' (using password: YES)

Suggested fix:
Restrict host update for user that are referenced in the view and/or recreate the view automatically when host limit is updated for such user.

Hi Mr. Packiaraj,

Thank you for your bug report.

You have two choices here. 

You do not have to specify the DEFINER, but if you have to follow the constraints as explained here:

https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html

Hence, this is all that we can advise you to do.

Not a bug.

Thanks for taking a look. 

Since DEFINER is not specified in the create view SQL, its added by default. The default selection is causing the issue. That is why I believe this is a bug.  
Either it needs to be required value ( or configuration driven ) or somehow force recreation/refresh views (dependant objects) upon changing the account settings such as host

thanks again

Bug #113969 marked as duplicate of this one.