Description:
When "activate_all_roles_on_login" is "on", privileges
which given to target user through roles, is activated to other
objects than views.
How to repeat:
Tested with MySQL commynity 8.0.21 and enterprise 8.0.28
In /etc/my.cnf
activate_all_roles_on_login = on
Noticed that parameter is active during prestented test
root@localhost:mysql.sock [(none)]> select @@activate_all_roles_on_login;
+-------------------------------+
| @@activate_all_roles_on_login |
+-------------------------------+
| 1 |
+-------------------------------+
1) With root-privileges..;
create database olli;
create role olli_all;
grant all on olli.* to olli_all;
create user olli@'%' identified by 'kolli';
grant olli_all to olli@'%';
\q
2) Login as "olli"..;
mysql -u olli -p olli
3) Noticed that assigned role is active..;
select current_role();
+----------------+
| current_role() |
+----------------+
| `olli_all`@`%` |
+----------------+
1 row in set (0.00 sec)
4) Create table + view for it;
create table kolli (id int, data varchar(25));
insert into kolli values(1,'Blabla');
create view olli as select * from kolli;
5) Noticed that view is not able to work..;
select * from olli;
ERROR 1356 (HY000): View 'olli.olli' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
6) View is dropped away..;
drop view olli;
7) Jut for curiosity, checked alternate security for view..;
create sql security invoker view olli as select * from kolli;
8) Noticed that view works properly..;
select * from olli;
+------+--------+
| id | data |
+------+--------+
| 1 | Blabla |
+------+--------+
1 row in set (0.00 sec)
9) Recreate table + view
drop view olli;
drop table kolli;
create table kolli (id int, data varchar(25));
insert into kolli values(1,'Blabla');
create view olli as select * from kolli;
10) Noticed that view not work..;
select * from olli;
ERROR 1356 (HY000): View 'olli.olli' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
11) As root-privileges..;
set default role all to 'olli'@'%';
12) As "olli", noticed that role is still assigned as in beginning of test..;
select current_role();
+----------------+
| current_role() |
+----------------+
| `olli_all`@`%` |
+----------------+
1 row in set (0.00 sec)
13) As "olli", noticed that view works well..;
select * from olli;
+------+--------+
| id | data |
+------+--------+
| 1 | Blabla |
+------+--------+
1 row in set (0.00 sec)
Suggested fix:
Privileges through roles must be affect to views as well privileges
affects to other objects, when "activate_all_roles_on_login" is used.
