Table 'dual' in view prevents grant select

Detail name	Value
Changelog Number	8617
Type	Bug
Status	Open
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0

Background

To prevent data leakage, granting SELECT on a view in Exasol requires the owner of the view to also own GRANT privileges on all underlying objects.

Problem

All system tables are owned by SYS. This means that to grant select on any view referencing system tables one of the following must be true:

• The view is owned by SYS
• The view owner has the SELECT ANY DICTIONARY WITH ADMIN OPTION privilege
• The grantor has grant any object privilege

This also includes 'fake' tables like DUAL:

Example:

create schema test;
create user user1 identified by "user1";
grant impersonation on sys to user1;
create user user2 identified by "user2";

alter schema test change owner user1;
create or replace view v1 as select 1 as a from dual;

impersonate user1;
grant select on test.v1 to user2; -- insufficient privileges: SELECT on table DUAL must be grantable for USER1
impersonate sys;

grant select any dictionary to user1 with admin option;
impersonate user1;
grant select on test.v1 to user2; -- Now it works...
impersonate sys;

Unfortunately, selecting without a table is internally rewritten to select from dual:

create or replace view test.v1 as ( select 1 );
grant select on test.v1 to user2;
-- will also fail.

Workarounds

1 - Use VALUES constructor

Some variants:

create or replace view v1(a) as ( values 1 );
create or replace view v1 as ( values 1 as t(a) );
create or replace view v1 as select 1 as a from ( values  1 );

2 - Create your own DUAL table

create table test.dual as select * from sys.dual;
create or replace view v1 as select 1 as a from test.dual;