Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
-
mariadb in docker
Server version: 10.6.12-MariaDB-1:10.6.12+maria~ubu2004 mariadb.org binary distribution
Description
reproduce steps:
1. pull docker images
docker pull mariadb:10.6 |
docker run --name m6 -e MYSQL_ROOT_PASSWORD=1 -d mariadb:10.6 |
docker exec -it m6 bash
|
2. create user with role
mysql -uroot -p1
|
create schema sbtest;
|
|
|
create user if not exists 'dbausr'@'localhost'; |
set password for 'dbausr'@'localhost' = password('1'); |
create role if not exists adrole; |
GRANT ALL PRIVILEGES ON sbtest.* TO adrole;
|
create role if not exists dbrole; |
grant adrole to dbrole;
|
grant dbrole to 'dbausr'@'localhost'; |
set default role dbrole for 'dbausr'@'localhost'; |
FLUSH PRIVILEGES;
|
\q
|
3. create table and view
mysql -udbausr -p1 sbtest
|
|
|
create table t1 (a int, b varchar(1)); |
create table t2 (a int, b varchar(1)); |
|
|
create or replace view v1 as
|
select t.a, t.b
|
from ((select a, b from t1) union all
|
select a, b from t2 ) t
|
order by 2,1 |
;
|
4. check view define, and query view, but get unexpected results.
MariaDB [sbtest]> show create table v1;
|
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| View | Create View | character_set_client | collation_connection |
|
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`dbausr`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t`.`a` AS `a`,`t`.`b` AS `b` from ((select `sbtest`.`t1`.`a` AS `a`,`sbtest`.`t1`.`b` AS `b` from `t1`) union all select `sbtest`.`t2`.`a` AS `a`,`sbtest`.`t2`.`b` AS `b` from `t2`) `t` order by '','' | utf8mb3 | utf8mb3_general_ci | |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [sbtest]> select * from v1;
|
ERROR 1356 (HY000): View 'sbtest.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
|
