Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30999

can not query view after creation, and view define display not right

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
    • 10.6, 10.11, 11.4
    • Views
    • 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
      
      

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          Thank you for the report! I repeated on 10.6-10.11 as described, 10.4,10.5 shows view as expected.

          create user if not exists 'dbausr'@'localhost';
           
          create role if not exists adrole;
          GRANT ALL PRIVILEGES ON test.* 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;
           
          --connect (a1,localhost,dbausr,,test)
           
          --connection a1 
           
          create table t1 (a int, b varchar(1));
          create table t2 (a int, b varchar(1));
           
          create 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;
           
          show create view v1;
          select * from v1;
          

          alice Alice Sherepa added a comment - Thank you for the report! I repeated on 10.6-10.11 as described, 10.4,10.5 shows view as expected. create user if not exists 'dbausr' @ 'localhost' ;   create role if not exists adrole; GRANT ALL PRIVILEGES ON test.* 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 ;   --connect (a1,localhost,dbausr,,test) --connection a1   create table t1 (a int , b varchar (1)); create table t2 (a int , b varchar (1)); create 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;   show create view v1; select * from v1;
          JTMosaic J.T. Shyman added a comment -

          This seems to have been fixed somewhere between 11.1.2 and 11.2.5. Can a developer confirm? I have not been able to find a reference to this issue in the release notes. Thanks!

          JTMosaic J.T. Shyman added a comment - This seems to have been fixed somewhere between 11.1.2 and 11.2.5. Can a developer confirm? I have not been able to find a reference to this issue in the release notes. Thanks!

          The test case still fails for me in 11.2.5, am I doing something wrong?

          serg Sergei Golubchik added a comment - The test case still fails for me in 11.2.5, am I doing something wrong?
          JTMosaic J.T. Shyman added a comment -

          Maybe I am. Let me test again and post my test scripts and results.

          JTMosaic J.T. Shyman added a comment - Maybe I am. Let me test again and post my test scripts and results.
          JTMosaic J.T. Shyman added a comment - - edited

          @Sergei Golubchuk. This is on 11.4.3

          You're right. I was using a version of MariaDB which had granted public access to the schema I was using.

          Any thoughts on when this may be fixed? It is holding us back from using roles.

          Set up (as root):

          create user `user1`@`localhost` identified by 'pass1';
          create role role1;
          create schema test_schema;
          grant role1 to `user1`@`localhost`;
          grant all privileges on test_schema.* to role1;
          set default role role1 for `user1`@`localhost`;
          show grants for `user1`@`localhost`;
          

          Test (as user1):

          show grants;
          use test_schema;
           
          create table t1 (a int, b char);
           
          create view v1 as select * from t1;
          show create view v1;
          select * from v1; -- Error. The view was created with sql security definer and a definer of user1, not the default role for user1
           
          create sql security invoker view v2 as select * from t1;
          show create view v2;
          select * from v2; -- Works, but requires a change in behavior during creation
           
          create definer = current_role view v3 as select * from t1;
          show create view v3;
          select * from v3; -- Works, but requires a change in behavior during creation
          

          JTMosaic J.T. Shyman added a comment - - edited @Sergei Golubchuk. This is on 11.4.3 You're right. I was using a version of MariaDB which had granted public access to the schema I was using. Any thoughts on when this may be fixed? It is holding us back from using roles. Set up (as root): create user `user1`@`localhost` identified by 'pass1' ; create role role1; create schema test_schema; grant role1 to `user1`@`localhost`; grant all privileges on test_schema.* to role1; set default role role1 for `user1`@`localhost`; show grants for `user1`@`localhost`; Test (as user1): show grants; use test_schema;   create table t1 (a int , b char );   create view v1 as select * from t1; show create view v1; select * from v1; -- Error. The view was created with sql security definer and a definer of user1, not the default role for user1   create sql security invoker view v2 as select * from t1; show create view v2; select * from v2; -- Works, but requires a change in behavior during creation   create definer = current_role view v3 as select * from t1; show create view v3; select * from v3; -- Works, but requires a change in behavior during creation

          People

            serg Sergei Golubchik
            shawn2016 Shawn Yan
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.