[DERBY-6439] Privileges not checked before scalar subquery is materialized

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.10.1.1
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 23/Dec/13 17:18
• Updated: 23/Dec/13 17:18

Description

Seen when testing DERBY-6429:

ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true;user=dbo';
ij> call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true');
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:memory:db;shutdown=true';
ERROR 08006: Database 'memory:db' shutdown.
ij> connect 'jdbc:derby:memory:db;user=u1' as u1;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(U1)> create table t1(x int);
0 rows inserted/updated/deleted
ij(U1)> create table t2(x int);
0 rows inserted/updated/deleted
ij(U1)> insert into t2 values 1, 2;
2 rows inserted/updated/deleted
ij(U1)> grant update on t1 to u2;
0 rows inserted/updated/deleted
ij(U1)> connect 'jdbc:derby:memory:db;user=u2' as u2;
WARNING 01J14: SQL authorization is being used without first enabling authentication.
ij(U2)> update u1.t1 set x = (select x from u1.t2);
ERROR 21000: Scalar subquery is only allowed to return a single row.

The UPDATE statement should fail with a privilege error because U2 does not have SELECT permission on U1.T2.X.

Can also be seen without UPDATE:

ij(U2)> values (select x from u1.s);
ERROR 21000: Scalar subquery is only allowed to return a single row.

Issue Links

• relates to: DERBY-6429 Privilege checks for UPDATE statements are wrong.