Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.16
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
- relates to
-
-
- Closed
-