Owner privileges are not checked during view creation with CTE's

Detail name	Value
Changelog Number	10637
Type	Bug
Status	Resolved
Affected Versions
Fix Versions	Exasol 8.9.0, Exasol 7.1.18
Resolution Date	2023-02-02

Description

The permissions needed to create and execute views are described in our documentation. In order to create a view, the owner (not the person creating it) must have SELECT privileges on all underlying objects. In some cases, these permissions are not checked, and it is possible to create a view that is not able to be selected afterward. The following conditions must be met:

• You must be able to create a view 
• The view text contains CTE's or WITH clauses that select some objects.
• The owner of the view must not have permissions on at least one object referred in a CTE (WITH clause)

Although the permissions are not checked during creation, the permissions will be checked on subsequent SELECTs, so there is no potential data exposure.

 The following example reproduces this case:

-- Setup
CREATE SCHEMA A;
CREATE SCHEMA B;

CREATE ROLE A_OWNER;
CREATE ROLE B_OWNER;

ALTER SCHEMA A CHANGE OWNER A_OWNER;
ALTER SCHEMA B CHANGE OWNER B_OWNER;

CREATE TABLE B.T1 (C1 INT);
CREATE TABLE B.T2 (C1 INT);

-- Below query receives error message due to insufficient privileges, as expected.
CREATE OR REPLACE VIEW A.V1 AS 
SELECT * FROM B.T1;

-- Below query does not receive an error message. Running a SELECT on the view afterwards will return an error that there are insufficient privileges.
CREATE OR REPLACE VIEW A.V1 AS 
WITH CTE AS (SELECT * FROM B.T1)
SELECT * FROM CTE;

Workaround

You can re-write your query to put the CTE into a subselect instead. For example:

CREATE OR REPLACE VIEW A.V1 AS
SELECT * FROM (SELECT * FROM B.T1);

Fix

Views matching the above criteria will return an error that there are insufficient privileges, as expected according to the documentation.