Hello Sergei, Please review a patch that fixes the problem described in MDEV-7574. The idea is that a SELECT from a view over a CONNECT table now checks FILE privileges of the view definer (unless CREATE VIEW states SQL SECURITY INVOKER). It looks like a very good idea and gives more possible security options. The administrator can create a VIEW and give access to it to some user, without giving FILE privilege to this user. I'm not really sure about the patch, but it seems to do the trick :) From what I understood, FILE_ACL is written (among the other privileges) into thd->security_ctx.privilege in TABLE_LIST::prepare_security(). In case of a DEFINER view, thd->security_ctx.privilege is filled exactly with the definer privileges, and to the invoker privileges otherwise. So inside ha_connect::check_privileges() the fact that there is FILE_ACL in thd->security_ctx.privilege means that TABLE_LIST::prepare_security() was previously called and FILE_ACL is set to DEFINER or INVOKER, according to the view definition. This is exactly what we need. I'm not sure about the opposite: if there is no FILE_ACL in thd->security_ctx.privilege, what does it mean? Does it mean that there is no FILE_ACL for the effective user? Or can it also mean that TABLE_LIST::prepare_security() was not called? Thanks. On 02/12/2015 04:04 AM, Olivier Bertrand wrote:
Hi Alexander,
Can you take care of this issue? I know you wrote the ha_connect::check_privileges function and I personnally have not the faintest idea about how this must be checked.
Thanks, Olivier
-------- Message transféré -------- Sujet : [JIRA] (MDEV-7574) Security definer views don't work with CONNECT ODBC tables Date : Wed, 11 Feb 2015 23:21:00 +0200 (EET) De : Elena Stepanova (JIRA) <jira@mariadb.atlassian.net> Pour : bertrandop@gmail.com
[https://mariadb.atlassian.net/browse/MDEV-7574?page=com.atlassian.jira.plugi... ]
Elena Stepanova reassigned MDEV-7574: -------------------------------------
Assignee: Olivier Bertrand Fix Version/s: 10.0
Security definer views don't work with CONNECT ODBC tables ----------------------------------------------------------
Key: MDEV-7574 URL:https://mariadb.atlassian.net/browse/MDEV-7574 Project: MariaDB Server Issue Type: Bug Components: Storage Engine - Connect Affects Versions: 10.0.16 Reporter: Geoff Montee Assignee: Olivier Bertrand Labels: connect-engine Fix For: 10.0
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: {code} [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 {code} Now connect with the new user, and try to use the view: {code} [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 {code} It didn't work, so give the user privileges on the underlying ODBC table: {code} [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 {code} Now try using the view again: {code} [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) {code}
-- This message was sent by Atlassian JIRA (v6.4-OD-14-082#64012)