SECURITY: Missing access control check for GRANT SELECT ON VIEW on objects referenced in a WITH clause

Detail name	Value
Changelog Number	16055
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.9.0, Exasol 7.1.18, Exasol 7.0.22
Resolution Date	2023-02-02

Vulnerability

Classification: Low

A user should only be allowed to GRANT SELECT ON <view> TO <user/role> if the owner of <view> is also allowed to grant select on all objects referenced in the view text. Otherwise, a malicious user that is allowed to select from a table, but not allowed to grant select on a table, can create a view that selects from the table and grant select on this view instead.

Due to a bug, all objects that are only referenced in a WITH clause inside a view are not checked in this way. This allows a user to grant select on a view even if the owner of the view is not allowed to grant select on objects that are only referenced inside the WITH clause.

Example

Preparation (run as DBA)

/*
u1 gets select access to hidden_table and shouldn't be able to give anybody else access.
*/
create user u1 identified by "u1";
create user u2 identified by "u2";

create schema hidden_schema;
create table hidden_schema.hidden_table(a int);

create schema public_schema;
alter schema public_schema change owner u1;

grant create view to u1;
grant select on hidden_schema.hidden_table to u1;
grant create session to u1; -- to allow u1 to create views GRANT CREATE ANY VIEW should also work here

commit;

Problem (run as u1)

create or replace view public_schema.v_direct as (
select * from hidden_schema.hidden_table );

create or replace view public_schema.v_cte as (
with cte as (select * from hidden_schema.hidden_table) -- hide the access in a with clause
select * from cte);

create or replace view public_schema.v_select as (
select * from (select * from hidden_schema.hidden_table));

grant select on public_schema.v_direct to u2; -- Error as expected: insufficient privileges: SELECT on table HIDDEN_TABLE must be grantable for U1
grant select on public_schema.v_cte to u2; -- This should result in the same error, but works.
grant select on public_schema.v_select to u2; -- Error as expected: insufficient privileges: SELECT on table HIDDEN_TABLE must be grantable for U1

Fix

The GRANT statement now correctly checks all objects referenced in a view, including those referenced in a WITH clause. This only affects GRANT statements executed after upgrading to an Exasol version containing this fix. Privileges which were granted in a previous version remain unchanged. These preexisting privileges must be reviewed manually because they may have been granted intentionally, even if that should not have been allowed.

Changed behavior

Some GRANT queries which previously worked will now return an error.