[MDEV-36380] User has unauthorized access to a sequence through a view with security invoker - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5, 10.6, 10.11, 11.4, 11.8
Fix Version/s: 10.5.29, 10.6.22, 10.11.12, 11.4.6, 11.8.2
Component/s: Authentication and Privilege System, Sequences, Views
Labels:
None

Description

Unauthorized access to the sequence
create database db;
use db;
create sequence s;
create sql security invoker view v as select nextval(s);
create user u@localhost;
grant select on db.v to u@localhost;

--connect (con1,localhost,u,,db)
--error ER_TABLEACCESS_DENIED_ERROR
select nextval(s);
--error ER_VIEW_INVALID
select * from v;

--disconnect con1
--connection default
drop database db;
drop user u@localhost;

The expected result would be (as coded in the test case) that SELECT from the view fails with "View 'db.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" or some kind of access denied error; instead, it succeeds;

main ab468e33aff110b44a31ce0350894906ff4bc757
mysqltest: At line 12: query 'select * from v' succeeded - should have failed with error ER_VIEW_INVALID (1356)...

If we use a stored function instead of a sequence in an otherwise identical test case, it works as expected – SELECT from the view fails:

OK with a function instead of a sequence
create database db;
use db;
create function f() returns int return 1;
create sql security invoker view v as select f();
create user u@localhost;
grant select on db.v to u@localhost;

--connect (con1,localhost,u,,db)
--error ER_PROCACCESS_DENIED_ERROR
select f();
--error ER_VIEW_INVALID
select * from v;

--disconnect con1
--connection default
drop database db;
drop user u@localhost;

Attachments

Issue Links

relates to

MDEV-36280 ALTER TABLE require ALTER privilege on sequence from DEFAULT value expression

Stalled

MDEV-36413 User without any privileges to a sequence can read from it and modify it via column default

Closed

Activity

Ascending order - Click to sort in descending order

Michael Widenius added a comment - 2025-04-03 19:12 - edited

The issue here is that when we have a view with just a function, nothing in FROM, the table_list is not used to check for privileges.

For views, access checking for derived tables are done in setup_tables_and_check_access(). However in this function I cannot find any way to access
the sequence tables.

In mysql_make_view(), we have the sequence in lex->query_tables, but we never call check_table_access() on these.

The lex->query_tables are updated in this path:

0x00000000008a3e9e in Query_tables_list::add_to_query_tables (this=0x7fffd0016fb8, table=0x7fffd0020d60) at /my/maria-10.11/sql/sql_lex.h:1823

1823        *(table->prev_global= query_tables_last)= table;

(rr) backtrace

#0  0x00000000008a3e9e in Query_tables_list::add_to_query_tables (this=0x7fffd0016fb8, table=0x7fffd0020d60) at /my/maria-10.11/sql/sql_lex.h:1823

#1  0x00000000009b09fe in st_select_lex::add_table_to_list (this=0x7fffd0023db0, thd=0x7fffd0000d48, table=0x7fffd0019b80, alias=0x0, table_options=16, lock_type=TL_WRITE_ALLOW_WRITE, mdl_type=MDL_SHARED_WRITE, index_hints_arg=0x0, partition_names=0x0, option=0x0) at /my/maria-10.11/sql/sql_parse.cc:8466

#2  0x0000000000975d77 in LEX::create_item_func_nextval (this=0x7fffd0016fb0, thd=0x7fffd0000d48, table_ident=0x7fffd0019b80) at /my/maria-10.11/sql/sql_lex.cc:8280

#3  0x0000000000cc750a in MYSQLparse (thd=0x7fffd0000d48) at /my/maria-10.11/sql/sql_yacc.yy:9863

The question I have is where to add checking of access to sequences.
I assume we could add this in mysql_make_view, together with other access checks.
I don't know why this there is no check of lex->query_tables here.

Here is a bit longer test case that shows more combinations of things:

--source include/have_sequence.inc

create database db;

use db;

create table t1 (a int) select seq as 'a' from seq_1_to_2;

create table t2 (a int) select seq as 'a' from seq_1_to_2;

create sequence s;

create sql security invoker view v as select nextval(s);

create sql security invoker view v2 as select nextval(s) from t2;

create sql security invoker view v3 as select nextval(s) from t1;

create user u@localhost;

grant select on db.v to u@localhost;

grant select on db.v2 to u@localhost;

grant select on db.v3 to u@localhost;

grant select on db.t2 to u@localhost;

--connect (con1,localhost,u,,db)

--error ER_TABLEACCESS_DENIED_ERROR

select nextval(s);

select * from v;   -- Should give error

select * from v2; -- should give error

--error ER_TABLEACCESS_DENIED_ERROR

select * from v3;

--error ER_TABLEACCESS_DENIED_ERROR

select * from t1,v;

--disconnect con1

--connection default

drop database db;

I assume that for fixing this issue, on should check why get an error for 't1' in select * from v3 and why we are not checking 's' in lex->query_tables

Michael Widenius added a comment - 2025-04-03 19:12 - edited The issue here is that when we have a view with just a function, nothing in FROM, the table_list is not used to check for privileges. For views, access checking for derived tables are done in setup_tables_and_check_access(). However in this function I cannot find any way to access the sequence tables. In mysql_make_view(), we have the sequence in lex->query_tables, but we never call check_table_access() on these. The lex->query_tables are updated in this path: 0x00000000008a3e9e in Query_tables_list::add_to_query_tables (this=0x7fffd0016fb8, table=0x7fffd0020d60) at /my/maria-10.11/sql/sql_lex.h:1823 1823 *(table->prev_global= query_tables_last)= table; (rr) backtrace #0 0x00000000008a3e9e in Query_tables_list::add_to_query_tables (this=0x7fffd0016fb8, table=0x7fffd0020d60) at /my/maria-10.11/sql/sql_lex.h:1823 #1 0x00000000009b09fe in st_select_lex::add_table_to_list (this=0x7fffd0023db0, thd=0x7fffd0000d48, table=0x7fffd0019b80, alias=0x0, table_options=16, lock_type=TL_WRITE_ALLOW_WRITE, mdl_type=MDL_SHARED_WRITE, index_hints_arg=0x0, partition_names=0x0, option=0x0) at /my/maria-10.11/sql/sql_parse.cc:8466 #2 0x0000000000975d77 in LEX::create_item_func_nextval (this=0x7fffd0016fb0, thd=0x7fffd0000d48, table_ident=0x7fffd0019b80) at /my/maria-10.11/sql/sql_lex.cc:8280 #3 0x0000000000cc750a in MYSQLparse (thd=0x7fffd0000d48) at /my/maria-10.11/sql/sql_yacc.yy:9863 The question I have is where to add checking of access to sequences. I assume we could add this in mysql_make_view, together with other access checks. I don't know why this there is no check of lex->query_tables here. Here is a bit longer test case that shows more combinations of things: --source include/have_sequence.inc create database db; use db; create table t1 (a int ) select seq as 'a' from seq_1_to_2; create table t2 (a int ) select seq as 'a' from seq_1_to_2; create sequence s; create sql security invoker view v as select nextval(s); create sql security invoker view v2 as select nextval(s) from t2; create sql security invoker view v3 as select nextval(s) from t1; create user u@localhost; grant select on db.v to u@localhost; grant select on db.v2 to u@localhost; grant select on db.v3 to u@localhost; grant select on db.t2 to u@localhost; --connect (con1,localhost,u,,db) --error ER_TABLEACCESS_DENIED_ERROR select nextval(s); select * from v; -- Should give error select * from v2; -- should give error --error ER_TABLEACCESS_DENIED_ERROR select * from v3; --error ER_TABLEACCESS_DENIED_ERROR select * from t1,v; --disconnect con1 --connection default drop database db; I assume that for fixing this issue, on should check why get an error for 't1' in select * from v3 and why we are not checking 's' in lex->query_tables

Oleksandr Byelkin added a comment - 2025-04-11 05:56

VIEW rely on setting correct security context and wanted privileges which will be checked later on fix_fields of the fields (as it done for colum privileges).

The problem is that SEQUENCes do not check column privileges.

Oleksandr Byelkin added a comment - 2025-04-11 05:56 VIEW rely on setting correct security context and wanted privileges which will be checked later on fix_fields of the fields (as it done for colum privileges). The problem is that SEQUENCes do not check column privileges.

Oleksandr Byelkin added a comment - 2025-04-11 11:27

commit eb1d0112bea676c0f4e6dc9e537bbe95946a6280 (HEAD -> bb-10.5-MDEV-36380, origin/bb-10.5-MDEV-36380)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Fri Apr 11 08:28:42 2025 +0200

    MDEV-36380 User has unauthorized access to a sequence through a view with security invoker

    The problem was that view rely on later checking column level privileges

    for underlying tables but sequences do not need checks of its column

    level privileges, only table level (sequence is one unit for SQL level).

    Solution is to check table level privileges for sequences when we

    create view structures (mysql_make_view).

Oleksandr Byelkin added a comment - 2025-04-11 11:27 commit eb1d0112bea676c0f4e6dc9e537bbe95946a6280 (HEAD -> bb-10.5-MDEV-36380, origin/bb-10.5-MDEV-36380) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Fri Apr 11 08:28:42 2025 +0200 MDEV-36380 User has unauthorized access to a sequence through a view with security invoker The problem was that view rely on later checking column level privileges for underlying tables but sequences do not need checks of its column level privileges, only table level (sequence is one unit for SQL level). Solution is to check table level privileges for sequences when we create view structures (mysql_make_view).

Oleksandr Byelkin added a comment - 2025-04-17 08:05

OK to push

Oleksandr Byelkin added a comment - 2025-04-17 08:05 OK to push

MariaDB Server

User has unauthorized access to a sequence through a view with security invoker

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration