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) <j...@mariadb.atlassian.net>
Pour : bertran...@gmail.com
[https://mariadb.atlassian.net/browse/MDEV-7574?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
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)
=== modified file 'storage/connect/ha_connect.cc'
--- storage/connect/ha_connect.cc 2015-01-20 00:21:56 +0000
+++ storage/connect/ha_connect.cc 2015-02-17 13:46:34 +0000
@@ -3865,6 +3865,8 @@ bool ha_connect::check_privileges(THD *t
case TAB_MAC:
case TAB_WMI:
case TAB_OEM:
+ if (table && (table->grant.privilege & FILE_ACL))
+ return false;
return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0);
// This is temporary until a solution is found
=== modified file 'storage/connect/mysql-test/connect/r/grant.result'
--- storage/connect/mysql-test/connect/r/grant.result 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/r/grant.result 2015-02-17 13:58:33 +0000
@@ -43,10 +43,25 @@ ERROR 28000: Access denied for user 'use
CREATE VIEW v1 AS SELECT * FROM t1;
ERROR 28000: Access denied for user 'user'@'localhost' (using password: NO)
# Testing a VIEW created with FILE privileges but accessed with no FILE
+# Using SQL SECURITY DEFINER
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+SELECT user();
+user()
+user@localhost
+SELECT fname, ftype FROM v1 ORDER BY fname;
+fname ftype
+t1 .frm
+v1 .frm
+DROP VIEW v1;
+# Testing a VIEW created with FILE privileges but accessed with no FILE
+# Using SQL SECURITY INVOKER
+SELECT user();
+user()
+root@localhost
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
=== modified file 'storage/connect/mysql-test/connect/r/mysql_grant.result'
--- storage/connect/mysql-test/connect/r/mysql_grant.result 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/r/mysql_grant.result 2015-02-17 13:40:33 +0000
@@ -40,7 +40,7 @@ ERROR 28000: Access denied for user 'use
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
=== modified file 'storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result'
--- storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result 2013-11-06 17:22:09 +0000
+++ storage/connect/mysql-test/connect/r/odbc_sqlite3_grant.result 2015-02-17 13:42:09 +0000
@@ -52,7 +52,7 @@ ERROR 28000: Access denied for user 'use
SELECT user();
user()
root@localhost
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
SELECT user();
user()
user@localhost
=== modified file 'storage/connect/mysql-test/connect/t/grant.test'
--- storage/connect/mysql-test/connect/t/grant.test 2013-05-21 14:29:10 +0000
+++ storage/connect/mysql-test/connect/t/grant.test 2015-02-17 13:58:13 +0000
@@ -47,9 +47,21 @@ ALTER TABLE t1 READONLY=1;
CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
+--echo # Using SQL SECURITY DEFINER
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM t1;
+--connection user
+SELECT user();
+SELECT fname, ftype FROM v1 ORDER BY fname;
+--connection default
+DROP VIEW v1;
+
+--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
+--echo # Using SQL SECURITY INVOKER
+--connection default
+SELECT user();
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
=== modified file 'storage/connect/mysql-test/connect/t/mysql_grant.test'
--- storage/connect/mysql-test/connect/t/mysql_grant.test 2013-10-27 09:37:12 +0000
+++ storage/connect/mysql-test/connect/t/mysql_grant.test 2015-02-17 13:40:21 +0000
@@ -54,7 +54,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
=== modified file 'storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test'
--- storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test 2013-05-24 11:21:06 +0000
+++ storage/connect/mysql-test/connect/t/odbc_sqlite3_grant.test 2015-02-17 13:41:53 +0000
@@ -58,7 +58,7 @@ CREATE VIEW v1 AS SELECT * FROM t1;
--echo # Testing a VIEW created with FILE privileges but accessed with no FILE
--connection default
SELECT user();
-CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM t1;
--connection user
SELECT user();
--error ER_ACCESS_DENIED_ERROR
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
