docker pull mariadb:10.6

docker run --name m6 -e MYSQL_ROOT_PASSWORD=1 -d mariadb:10.6

docker exec -it m6 bash

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

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

;

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