Description:
Hi,
When View's definer locked, the view still can be used without any issue, but when terminate the definer, it will show the error.
But under the mysql logic, if the procedure, function's definer was locked then the procedure and function can't not be used. Why there is no access/privilege control under view's logic.
【Background】: We have an internal task to clean up "unused" account, to make sure a easier recovery/rollback we will lock the account first then if there is no function issue or audit log error, then we will terminate the account after few months' monitoring.
But under this case, we met a production issue, after terminated the view, then we can't just easily unlock the user.
But on the other hand, if the user is intended to be locked, then the view's function should also be restricted based on the privilege logic.
How to repeat:
1. Preparation:
CREATE USER 'TEST_VIEW'@'%' IDENTIFIED BY 'acesf2gA)utz@asI!(';
drop view if exists v_test_user_lock_view;
CREATE ALGORITHM=UNDEFINED DEFINER=`TEST_VIEW`@`%` SQL SECURITY DEFINER VIEW `v_test_user_lock_view` AS select 1;
select * from `v_test_user_lock_view`;
2. Set user password expired, still can use the view
SET SQL_SAFE_UPDATES = 0;
update mysql.user set password_expired='Y' where user='TEST_VIEW';
flush privileges ;
select * from `v_test_user_lock_view`;
3. Set user locked, still can use the view
update mysql.user set account_locked='Y' where user='TEST_VIEW';
flush privileges ;
select * from `v_test_user_lock_view`;
4. Drop user, can't use the view
drop USER 'TEST_VIEW';
select * from `v_test_user_lock_view`;
Error Code: 1449. The user specified as a definer ('TEST_VIEW'@'%') does not exist 0.000 sec
Suggested fix:
1. Follow the similar access control with procedure and function, trigger, etc.
Just as I metioned, if the procedure, function's definer is locked then the procedure and function can't not be used, which is correct, due to access/privilege control logic. Why there is no access/privilege control under view's logic, there is also an security risk.
2. If you have a fix or release to fix this, please provide the offical release notes with the audit logic and fix logic. (we already knew how to audit and batch fixing, just make sure all others are noticed about his)
Description: Hi, When View's definer locked, the view still can be used without any issue, but when terminate the definer, it will show the error. But under the mysql logic, if the procedure, function's definer was locked then the procedure and function can't not be used. Why there is no access/privilege control under view's logic. 【Background】: We have an internal task to clean up "unused" account, to make sure a easier recovery/rollback we will lock the account first then if there is no function issue or audit log error, then we will terminate the account after few months' monitoring. But under this case, we met a production issue, after terminated the view, then we can't just easily unlock the user. But on the other hand, if the user is intended to be locked, then the view's function should also be restricted based on the privilege logic. How to repeat: 1. Preparation: CREATE USER 'TEST_VIEW'@'%' IDENTIFIED BY 'acesf2gA)utz@asI!('; drop view if exists v_test_user_lock_view; CREATE ALGORITHM=UNDEFINED DEFINER=`TEST_VIEW`@`%` SQL SECURITY DEFINER VIEW `v_test_user_lock_view` AS select 1; select * from `v_test_user_lock_view`; 2. Set user password expired, still can use the view SET SQL_SAFE_UPDATES = 0; update mysql.user set password_expired='Y' where user='TEST_VIEW'; flush privileges ; select * from `v_test_user_lock_view`; 3. Set user locked, still can use the view update mysql.user set account_locked='Y' where user='TEST_VIEW'; flush privileges ; select * from `v_test_user_lock_view`; 4. Drop user, can't use the view drop USER 'TEST_VIEW'; select * from `v_test_user_lock_view`; Error Code: 1449. The user specified as a definer ('TEST_VIEW'@'%') does not exist 0.000 sec Suggested fix: 1. Follow the similar access control with procedure and function, trigger, etc. Just as I metioned, if the procedure, function's definer is locked then the procedure and function can't not be used, which is correct, due to access/privilege control logic. Why there is no access/privilege control under view's logic, there is also an security risk. 2. If you have a fix or release to fix this, please provide the offical release notes with the audit logic and fix logic. (we already knew how to audit and batch fixing, just make sure all others are noticed about his)