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

Security definer views don't work with CONNECT ODBC tables

    XMLWordPrintable

    Details

      Description

      One possible way to get around the requirement for having the FILE privilege to access ODBC tables with CONNECT would be to have them called indirectly via a security definer view. However, it does not currently work.

      Create a security definer view to access the ODBC table, then create a new user:

      [gmontee@localhost ~]$ mysql -u root tmp
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 16
      Server version: 10.0.15-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [tmp]> SHOW CREATE TABLE datetime_table;
      +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table          | Create Table                                                                                                                                                                                                                                                |
      +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | datetime_table | CREATE TABLE `datetime_table` (
        `id` int(10) NOT NULL,
        `modifiedon` datetime DEFAULT NULL
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1' `TABLE_TYPE`='ODBC' `TABNAME`='dbo.datetime_table' |
      +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [tmp]> DROP USER 'connecttest'@'localhost';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> CREATE OR REPLACE
          -> DEFINER = CURRENT_USER
          -> SQL SECURITY DEFINER
          -> VIEW datetime_view
          -> AS SELECT * FROM datetime_table;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> CREATE USER 'connecttest'@'localhost';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> GRANT SELECT ON datetime_view TO 'connecttest'@'localhost';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> \q
      Bye

      Now connect with the new user, and try to use the view:

      [gmontee@localhost ~]$ mysql -u connecttest tmp
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 17
      Server version: 10.0.15-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [tmp]> SELECT * FROM datetime_view;
      ERROR 1045 (28000): Access denied for user 'connecttest'@'localhost' (using password: NO)
      MariaDB [tmp]> \q
      Bye

      It didn't work, so give the user privileges on the underlying ODBC table:

      [gmontee@localhost ~]$ mysql -u root tmp
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 18
      Server version: 10.0.15-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [tmp]> GRANT FILE ON *.* TO 'connecttest'@'localhost';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> GRANT SELECT ON datetime_table TO 'connecttest'@'localhost';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [tmp]> \q
      Bye

      Now try using the view again:

      [gmontee@localhost ~]$ mysql -u connecttest tmp
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 19
      Server version: 10.0.15-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [tmp]> SELECT * FROM datetime_view;
      +----+---------------------+
      | id | modifiedon          |
      +----+---------------------+
      |  1 | 2014-01-01 00:00:00 |
      |  2 | 2016-01-01 00:00:00 |
      +----+---------------------+
      2 rows in set (0.24 sec)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: