[TRAFODION-1101] LP Bug: 1437474 - Drop schema cascade fails then succeeds

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.1 (pre-incubation)
• Component/s: sql-security
• External issue URL: https://bugs.launchpad.net/bugs/1437474
• Assignee: Cliff Gray
• Reporter: Paul Low
• Created: 22/Jul/15 18:19
• Updated: 31/Aug/15 04:19
• Resolved: 31/Aug/15 04:04

Description

The first drop schema cascade command returns error after waiting for about 30 seconds, but the second drop schema cascade command returns success immediately.
It seems that the library and function must be part of the sequence below in order to reproduce the error, because when they were left out, no error returned.

SQL>drop schema schema1 cascade;

• • • ERROR[1069] Schema TRAFODION.SCHEMA1 could not be dropped.

See log of sequence below:

SQL>create schema schema1;

— SQL operation complete.

SQL>grant COMPONENT privilege "CREATE" on sql_operations to username1;

— SQL operation complete.

SQL>set schema schema1;

— SQL operation complete.

SQL>create library sec_udflib1 file '/home/trafodion/secudf.dll';

— SQL operation complete.

SQL>create function myudf (INVAL int)
returns (OUTVAL int)
language c
parameter style sql
external name 'myudf'
library sec_udflib1
deterministic
state area size 1024
allow any parallelism
no sql;
>>>>>>+>
— SQL operation complete.

SQL>grant execute on function myudf to username1;

— SQL operation complete.

SQL>connect username1/password1;
Connected to Trafodion

SQL>set schema schema1;

— SQL operation complete.

SQL>create table mytable (a int, b int);

— SQL operation complete.

SQL>insert into mytable values (1,1);

— 1 row(s) inserted.

SQL>create view myview as select myudf(a) from mytable;

— SQL operation complete.

SQL>create view myview2 as select * from mytable;

— SQL operation complete.

SQL>select * from myview;

• • • ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCHEMA1.MYVIEW.

SQL>select * from myview2;

• • • ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.SCHEMA1.MYVIEW2.

SQL>select * from mytable;

A B
----------- -----------
1 1

— 1 row(s) selected.

SQL>connect db__rootuser/password1;
Connected to Trafodion

SQL>drop schema schema1 cascade;

• • • ERROR[1069] Schema TRAFODION.SCHEMA1 could not be dropped.

SQL>drop schema schema1 cascade;

— SQL operation complete.