core/sql/regress/privs1/TEST141

-- ============================================================================
-- Test: TEST141 
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- ============================================================================
-- Functionality: Extended support for views for all levels:
--    column, object, component
--
-- Added in response to JIRA 1100
--
-- Oct 27 - only run some tests to improve performance of privs1 tests suite
--
-- Expected files: EXPECTED141
-- ============================================================================

cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
obey TEST141(clean_up);
log LOG141 clear ;
obey TEST141(set_up);
--obey TEST141(test_private_user);
obey TEST141(test_private_role);
obey TEST141(test_shared_user);
--obey TEST141(test_shared_role);
log;
obey TEST141(clean_up);
exit;

-- ============================================================================
?section clean_up
-- ============================================================================
-- Cleaning up test environment
drop schema if exists t141_user1 cascade;
drop schema if exists t141_user2 cascade;
drop schema if exists t141_user3 cascade;

revoke component privilege "CREATE" on sql_operations from sql_user2;
revoke component privilege "CREATE" on sql_operations from t141_role2;
revoke component privilege "DML_SELECT_METADATA" on sql_operations from "PUBLIC";

revoke role t141_role1 from sql_user1;
revoke role t141_role2 from sql_user2;
revoke role t141_role3 from sql_user3;
drop role t141_role1;
drop role t141_role2;
drop role t141_role3;

--revoke component privilege "CREATE" on sql_operations from user2_role;
--revoke component privilege "SHOW" on sql_operations from "PUBLIC";

revoke select on "_PRIVMGR_MD_".object_Privileges from t141_role_md;
revoke select on "_PRIVMGR_MD_".column_Privileges from t141_role_md;
revoke select on "_MD_".objects from t141_role_md;
revoke role t141_role_md from sql_user1, sql_user2, sql_user3;
drop role t141_role_md;


-- ============================================================================
?section set_up
-- ============================================================================
-- Setup the test environment

-- create function to display bitmaps as a bitmap rather than longs
-- use the same function from TEST140
sh rm -f ./etest141.dll;
sh sh $$scriptsdir$$/tools/dll-compile.ksh etest141.cpp
  2>&1 | tee LOG140-SECONDARY;
set pattern $$DLL$$ etest141.dll;
set pattern $$QUOTE$$ '''';

-- query to read privs from metadata
prepare get_obj_privs from
select distinct
  cast (substring (object_name,11,35) as char(35) character set iso88591) as object_name,
  object_type as type,
  substring(authname(grantor_id),1,10) as grantor,
  substring(authname(grantee_id),1,10) as grantee,
  case when bitextract(privileges_bitmap,63,1) = 1 then 'S'
       else '-' end ||
  case when bitextract(privileges_bitmap,62,1) = 1 then 'I'
        else '-' end ||
  case when bitextract(privileges_bitmap,61,1) = 1 then 'D'
        else '-' end ||
  case when bitextract(privileges_bitmap,60,1) = 1 then 'U'
        else '-' end ||
  case when bitextract(privileges_bitmap,59,1) = 1 then 'G'
        else '-' end ||
  case when bitextract(privileges_bitmap,58,1) = 1 then 'R'
        else '-' end ||
  case when bitextract(privileges_bitmap,57,1) = 1 then 'E'
        else '-' end as granted_privs,
  case when bitextract(grantable_bitmap,63,1) = 1 then 'S'
       else '-' end ||
  case when bitextract(grantable_bitmap,62,1) = 1 then 'I'
        else '-' end ||
  case when bitextract(grantable_bitmap,61,1) = 1 then 'D'
        else '-' end ||
  case when bitextract(grantable_bitmap,60,1) = 1 then 'U'
        else '-' end ||
  case when bitextract(grantable_bitmap,59,1) = 1 then 'G'
        else '-' end ||
  case when bitextract(grantable_bitmap,58,1) = 1 then 'R'
        else '-' end ||
  case when bitextract(grantable_bitmap,57,1) = 1 then 'E'
        else '-' end as grantable_privs
from "_PRIVMGR_MD_".object_privileges
where object_uid in
     (select object_uid
      from "_MD_".objects
      where schema_name like 'T141_USER%')
  order by 1, 2, 3, 4, 5
;

prepare get_col_privs from
select distinct
   cast(substring (object_name,11,20) as char (20) character set iso88591) as object_name,
   column_number,
   substring(authname(grantor_id),1,10) as grantor,
   substring(authname(grantee_id),1,10) as grantee,
  case when bitextract(privileges_bitmap,63,1) = 1 then 'S'
       else '-' end ||
  case when bitextract(privileges_bitmap,62,1) = 1 then 'I'
        else '-' end ||
  case when bitextract(privileges_bitmap,61,1) = 1 then 'D'
        else '-' end ||
  case when bitextract(privileges_bitmap,60,1) = 1 then 'U'
        else '-' end ||
  case when bitextract(privileges_bitmap,59,1) = 1 then 'G'
        else '-' end ||
  case when bitextract(privileges_bitmap,58,1) = 1 then 'R'
        else '-' end ||
  case when bitextract(privileges_bitmap,57,1) = 1 then 'E'
        else '-' end as granted_privs,
  case when bitextract(grantable_bitmap,63,1) = 1 then 'S'
       else '-' end ||
  case when bitextract(grantable_bitmap,62,1) = 1 then 'I'
        else '-' end ||
  case when bitextract(grantable_bitmap,61,1) = 1 then 'D'
        else '-' end ||
  case when bitextract(grantable_bitmap,60,1) = 1 then 'U'
        else '-' end ||
  case when bitextract(grantable_bitmap,59,1) = 1 then 'G'
        else '-' end ||
  case when bitextract(grantable_bitmap,58,1) = 1 then 'R'
        else '-' end ||
  case when bitextract(grantable_bitmap,57,1) = 1 then 'E'
        else '-' end as grantable_privs
from "_PRIVMGR_MD_".column_privileges
where object_uid in
     (select object_uid
      from "_MD_".objects
      where schema_name like 'T141_%')
  order by 1, 2, 3, 4, 5
;

-- set up role infrastructure
create role t141_role1;
create role t141_role2;
create role t141_role3;
grant role t141_role1 to sql_user1;
grant role t141_role2 to sql_user2;
grant role t141_role3 to sql_user3;

create role t141_role_md;
grant select on "_PRIVMGR_MD_".object_Privileges to t141_role_md;
grant select on "_PRIVMGR_MD_".column_Privileges to t141_role_md;
grant select on "_MD_".objects to t141_role_md;
grant role t141_role_md to sql_user1, sql_user2, sql_user3;

-- set up component privilege infrastructure
grant component privilege "CREATE" on sql_operations to sql_user2;
grant component privilege "CREATE" on sql_operations to t141_role2;

-- ============================================================================
?section create_objects
-- ============================================================================
set schema t141_user1;
create table u1t1 (c1 int not null primary key, c2 int, c3 int);
insert into u1t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
create table u1t2 (c1 int not null primary key, c2 int, c3 int);
insert into u1t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
create table u1t3 (c1 int not null primary key, c2 int, c3 int);
insert into u1t3 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
create table u1t4 (c1 int not null primary key, c2 int, c3 int);
insert into u1t4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
get tables, match 'U1T%';

set schema t141_user2;
create table u2t1 (c1 int not null primary key, c2 int, c3 int);
insert into u2t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
create table u2t2 (c1 int not null primary key, c2 int, c3 int);
insert into u2t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
get tables, match 'U2T%';

set schema t141_user3;
create table u3t1 (c1 int not null primary key, c2 int, c3 int);
insert into u3t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
get tables, match 'U3T%';


-- ============================================================================
?section test_private_user
-- ============================================================================
-- verify someone with CREATE permission can create objects in someone elses
-- private schema.  Make sure the schema owner owns the object and the creator
-- has appropriate privileges.

values (user);

-- setup database with private schemas owned by users
drop schema if exists t141_user1 cascade;
create schema t141_user1 authorization sql_user1;
drop schema if exists t141_user2 cascade;
create schema t141_user2 authorization sql_user2;
drop schema if exists t141_user3 cascade;
create schema t141_user3 authorization sql_user3;

obey TEST141(create_objects);
set schema t141_user1;
grant select on u1t1 to sql_user3;
grant select (c1, c2) on u1t2 to sql_user3;
grant update (c1) on u1t2 to sql_user3;
grant update, delete, insert on u1t3 to sql_user3;
grant update on u1t4 to sql_user3;
grant select(c1, c3) on u1t4 to sql_user3;
execute get_obj_privs;
execute get_col_privs;

-- sql_user2 has create privilege on all schemas.
-- have sql_user2 create some tables in sql_user1's schema
-- have sql_user2 create some views
sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
execute get_obj_privs;

-- sql_user1 can create views on all tables 
sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
execute get_obj_privs;

-- sql_user3 does not have the create privilege.
-- have sql_user3 attempt to create a table in sql_user1's schema
sh sqlci -i "TEST141(user3_objects)" -u sql_user3;

-- ============================================================================
?section test_shared_user
-- ============================================================================
-- verify someone with CREATE permission can create objects in someone elses
-- shared schema.  Make sure the current user owns the object and the creator
-- has appropriate privileges.

values (user);
revoke component privilege "CREATE" on sql_operations from sql_user2;

-- setup database with shared schemas owned by users
drop schema if exists t141_user1 cascade;
create shared schema t141_user1 authorization sql_user1;
drop schema if exists t141_user2 cascade;
create shared schema t141_user2 authorization sql_user2;
drop schema if exists t141_user3 cascade;
create shared schema t141_user3 authorization sql_user3;
showddl schema t141_user1;
showddl schema t141_user2;
showddl schema t141_user3;

-- schemas are owned by the authID specified in the authorization clause but
-- tables are owned by the creator.  The objects created in create_objects are
-- owned by DB__ROOT since DB__ROOT is the current user.
obey TEST141(create_objects);
set schema t141_user1;
grant select on u1t1 to sql_user3;
grant select (c1, c2) on u1t2 to sql_user3;
grant update (c1) on u1t2 to sql_user3;
grant update, delete, insert on u1t3 to sql_user3;
grant update on u1t4 to sql_user3;
grant select(c1, c3) on u1t4 to sql_user3;
execute get_obj_privs;
execute get_col_privs;

-- have sql_user2 create some tables in sql_user1's schema
-- have sql_user2 create some views, views that reference user1's objects fail
sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
execute get_obj_privs;

-- In a shared schema sql_user1 does not have privs on sql_user2 objects
-- creates should fail
sh sqlci -i "TEST141(user1_objects)" -u sql_user1;

-- grant privileges to sql_user1 and retry
grant select, insert, delete on t141_user1.u1t1 to sql_user1;
grant select on t141_user1.u2t1 to sql_user1; 
sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
execute get_obj_privs;

-- sql_user3 does not have the create privilege.
-- have sql_user3 attempt to create a table in sql_user1's schema
sh sqlci -i "TEST141(user3_objects)" -u sql_user3;

-- ============================================================================
?section test_private_role
-- ============================================================================
-- verify someone with CREATE permission can create objects in someone elses
-- private schema.  Make sure the schema owner owns the object and the creator
-- has appropriate privileges.

values (user);

-- setup database with private schemas owned by roles
drop schema if exists t141_user1 cascade;
create schema t141_user1 authorization t141_role1;
drop schema if exists t141_user2 cascade;
create schema t141_user2 authorization t141_role2;
drop schema if exists t141_user3 cascade;
create schema t141_user3 authorization t141_role3;
showddl schema t141_user1;
showddl schema t141_user2;
showddl schema t141_user3;

obey TEST141(create_objects);
set schema t141_user1;
execute get_obj_privs;
execute get_col_privs;

-- t141_role2 has create privilege on all schemas.
-- have sql_user2 who belongs to  t141_role2 create some tables in 
-- t141_user1's schema, also have sql_user2 create some views
sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
execute get_obj_privs;

-- ============================================================================
?section test_shared_role
-- ============================================================================
-- verify someone with CREATE permission can create objects in someone elses
-- shared schema.  Make sure the current user owns the object and the creator
-- has appropriate privileges.

values (user);
revoke component privilege "CREATE" on sql_operations from t141_role2;

-- setup database with shared schemas owned by role
drop schema if exists t141_user1 cascade;
create shared schema t141_user1 authorization t141_role1;
drop schema if exists t141_user2 cascade;
create shared schema t141_user2 authorization t141_role2;
drop schema if exists t141_user3 cascade;
create shared schema t141_user3 authorization t141_role3;
showddl schema t141_user1;
showddl schema t141_user2;

-- schemas are owned by the authID specified in the authorization clause but
-- tables are owned by the creator.  The following objects are owned by DB__ROOT
obey TEST141(create_objects);
set schema t141_user1;
execute get_obj_privs;
execute get_col_privs;

-- have sql_user2 create some tables in t141_role2's schema
-- have sql_user2 create some views, views that reference user1's objects fail
sh sqlci -i "TEST141(user2_objects)" -u sql_user2;
execute get_obj_privs;

-- ============================================================================
?section user1_objects
-- ============================================================================
-- executed by sql_user1
log LOG141;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
values (user);

set schema t141_user1;
create view u1v1 as select * from u1t1;
insert into u1v1 values (6,6,6);
delete from u1v1 where c1 = 6;
create view u1v2 as select * from u2t1;
insert into u1v2 values (6,6,6);
delete from u1v2 where c1 = 6;
create view u1v3(c1, c2) as select u1.c1, u2.c1 from u1t1 u1, u2t1 u2;

-- ============================================================================
?section user2_objects
-- ============================================================================
-- executed by sql_user2
-- sql_user2 has the CREATE component privilege for sql_operations.
-- All creates should succeed.  The owner of the table is the schema owner
-- (sql_user1) and sql_user2 should get all DML privileges WGO 
log LOG141;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
values (user);
set schema t141_user1;
create table u2t1 (c1 int not null primary key, c2 int, c3 int);
create table u2t2 (c1 int not null primary key, c2 int, c3 int);

get tables, match 'U2T%';

-- create a view on user1's table
create view u2v1 as select c1, c2 from u2t1;
showddl u2v1;

-- for shared schemas u1t1 is owned by DB__ROOT so create fails.
-- for private schemas this succeeds
create view u2v2(c1, c2)  as select u1.c1, u2.c1 from t141_user2.u2t1 as u2, u2t2 as u1;
showddl u2v2;

-- these creates should fail
-- user2 has no privs on u1t1
create view u1v3 as select * from u1t1;
set schema t141_user2;

-- user2 has no privs on u1t2, for shared schema also u1t1
create view u2v1 as select u1.c1, u2.c1 from u2t1 as u2, t141_user1.u1t2 as u1;

-- user2 has no privs on u3t1
create view u2v1 as select * from t141_user1.u3t1; 


-- ============================================================================
?section user3_objects
-- ============================================================================
-- executed by sql_user2
log LOG141;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
values (user);
set schema t141_user1;

--fails for private schemas - user3 has no create privs in schema user1's schemas
--works for shared schemas - user3 can create objects
create table u3t1 (c1 int not null primary key, c2 int, c3 int);

-- following works based on granted privs
set schema t141_user3;
create view u3v1 as select * from t141_user1.u1t1;
showddl u3v1;
create view u3v2 as select c1, c2 from t141_user1.u1t2;
showddl u3v2;
create view u3v3 as select c1 from t141_user1.u1t2;
showddl u3v3;
create view u3v4 as select c1, c2 from t141_user1.u1t4;
create view u3v4 as select c1, c3 from t141_user1.u1t4;

-- following fail
create view u3v5 as select c1, c3 from t141_user1.u1t3;
get tables, match 'U3T%';
get views;

-- ============================================================================
?section user2_views
-- ============================================================================
-- executed by sql_user2
log LOG141;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
values (user);
set schema t141_user2;

create view u2v1 as select * from t141_user1.u1t1;
create view u2v2 as select * from t141_user1.u1t2;