Internal error during create or replace view

[Paul Low]

Error returned:

SQL>create or replace view a02view2(a,b) as select col1,c1 from a02tab2, a02tab3 where col2=b1;

*** ERROR[1001] An internal error occurred in module ../sqlcomp/CmpSeabaseDDLview.cpp on line 718. DETAILS(Unable to retrieve privileges for replaced view).

Internal error is returned when using 'create or replace view'.
User creates view, then wants to replace that view with a different one. Internal error is returned.

See log below:

SQL>create schema schema1;

--- SQL operation complete.

SQL>set schema schema1;

--- SQL operation complete.

SQL>create table a02tab1(a int not null primary key, b int ,constraint tab1check check(b>0));

--- SQL operation complete.

SQL>create table a02tab2(col1 int, col2 int )no partition;

--- SQL operation complete.

SQL>create table a02tab3(a1 int,b1 int ,c1 varchar(10))no partition;

--- SQL operation complete.

SQL>create view a02view1 as select * from a02tab2;

--- SQL operation complete.

SQL>create view a02view2 as select * from a02tab2;

--- SQL operation complete.

SQL>create or replace view a02view2(a,b) as select col1,c1 from a02tab2, a02tab3 where col2=b1;

*** ERROR[1001] An internal error occurred in module ../sqlcomp/CmpSeabaseDDLview.cpp on line 718. DETAILS(Unable to retrieve privileges for replaced view).

SQL>drop schema schema1 cascade;

--- SQL operation complete.

[Roberta Marton (roberta-marton)]

Not found errors can be returned, so the error check was change to look for
STATUS_ERROR only.

[Paul Low (paul-low-x)]

Internal error is still visible with this sequence on 0417 build. Will reset status back to in progress.

[Paul Low (paul-low-x)]

The following scenarios completed as expected:
User create private schema, different user create view on table in the private schema (error expected: 1017)
User create private schema, different user create view on table in a shared schema (error expected:4481)
User create private schema, different user create or replace view (error expected:1017)
User create private schema, different user create or replace view on table in a shared schema (error expected:4481)
User create shared schema, different user create view on table in the private schema (error expected:4481)
User create shared schema, different user create view on table in a shared schema (success expected)
User create shared schema, different user create or replace view on table in the private(shared) schema (error expected:1017)
User create shared schema, different user create or replace view on table in a shared schema (success expected, user must be granted DELETE privilege on original view)

The following 3 scenarios fail with internal error:
User create shared schema, DB__ROOT user create or replace view on table in a shared schema (success expected, but ERROR[1001] is returned)
User create shared schema, user granted DB__ROOTROLE create or replace view on table in a shared schema (success expected, but ERROR[1001] is returned)
Shared schema is created and authorization is given to a role, user who is granted the role create or replace view on table in a shared schema (success expected, but ERROR[1001] is returned)

[Roberta Marton (roberta-marton)]

Fixed the problem with returning internal error - the objectUID check was not working correctly.

While debugging this problem, I noticed that once DB__ROOT, or someone granted DB__ROOTROLE becomes the owner after the replace view executes.

Connect as user1:

Create shared schema abc;
Create table abc.t1;
Create view abc.v1 as select from t1;

Connect as db__root

Create or replace view abc.v1 as ….

After the replace view, DB__ROOT owns the view.
For private schemas, the user1 owns the view after replace completes.

So should replace view in a shared schema retain the original ownership or grant it to the person executing the replace command?

Some comments:

Paul - It make sense to think of it as two separate commands (a DROP followed by a CREATE), so I think there should be a new owner. The second owner has the privilege to DROP.

Cliff - I think the person executing the create or replace should be the owner. Otherwise, sometimes the owner will be the second user, other times the first user. That seems harder to manage. In private schemas, owner is consistently the schema owner/original view owner ==> However, DB__ROOT could be a special case, as other commands have DB__ROOT acting on behalf of the owner.

For now, we won't change the behavior.