>>obey TEST141(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
+>;

--- SQL command prepared.
>>
>>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
+>;

--- SQL command prepared.
>>
>>-- set up role infrastructure
>>create role t141_role1;

--- SQL operation complete.
>>create role t141_role2;

--- SQL operation complete.
>>create role t141_role3;

--- SQL operation complete.
>>grant role t141_role1 to sql_user1;

--- SQL operation complete.
>>grant role t141_role2 to sql_user2;

--- SQL operation complete.
>>grant role t141_role3 to sql_user3;

--- SQL operation complete.
>>
>>create role t141_role_md;

--- SQL operation complete.
>>grant select on "_PRIVMGR_MD_".object_Privileges to t141_role_md;

--- SQL operation complete.
>>grant select on "_PRIVMGR_MD_".column_Privileges to t141_role_md;

--- SQL operation complete.
>>grant select on "_MD_".objects to t141_role_md;

--- SQL operation complete.
>>grant role t141_role_md to sql_user1, sql_user2, sql_user3;

--- SQL operation complete.
>>
>>-- set up component privilege infrastructure
>>grant component privilege "CREATE" on sql_operations to sql_user2;

--- SQL operation complete.
>>grant component privilege "CREATE" on sql_operations to t141_role2;

--- SQL operation complete.
>>
>>-- ============================================================================
>>--obey TEST141(test_private_user);
>>obey TEST141(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);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>-- setup database with private schemas owned by roles
>>drop schema if exists t141_user1 cascade;

--- SQL operation complete.
>>create schema t141_user1 authorization t141_role1;

--- SQL operation complete.
>>drop schema if exists t141_user2 cascade;

--- SQL operation complete.
>>create schema t141_user2 authorization t141_role2;

--- SQL operation complete.
>>drop schema if exists t141_user3 cascade;

--- SQL operation complete.
>>create schema t141_user3 authorization t141_role3;

--- SQL operation complete.
>>showddl schema t141_user1;

CREATE PRIVATE SCHEMA "TRAFODION"."T141_USER1" AUTHORIZATION "T141_ROLE1";

--- SQL operation complete.
>>showddl schema t141_user2;

CREATE PRIVATE SCHEMA "TRAFODION"."T141_USER2" AUTHORIZATION "T141_ROLE2";

--- SQL operation complete.
>>showddl schema t141_user3;

CREATE PRIVATE SCHEMA "TRAFODION"."T141_USER3" AUTHORIZATION "T141_ROLE3";

--- SQL operation complete.
>>
>>obey TEST141(create_objects);
>>-- ============================================================================
>>set schema t141_user1;

--- SQL operation complete.
>>create table u1t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t3 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t3 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t4 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U1T%';

Tables in Schema TRAFODION.T141_USER1
=====================================

U1T1
U1T2
U1T3
U1T4

=======================
 4 row(s) returned

--- SQL operation complete.
>>
>>set schema t141_user2;

--- SQL operation complete.
>>create table u2t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u2t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u2t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u2t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U2T%';

Tables in Schema TRAFODION.T141_USER2
=====================================

U2T1
U2T2

=======================
 2 row(s) returned

--- SQL operation complete.
>>
>>set schema t141_user3;

--- SQL operation complete.
>>create table u3t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u3t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U3T%';

Tables in Schema TRAFODION.T141_USER3
=====================================

U3T1

=======================
 1 row(s) returned

--- SQL operation complete.
>>
>>
>>-- ============================================================================
>>set schema t141_user1;

--- SQL operation complete.
>>execute get_obj_privs;

OBJECT_NAME                          TYPE  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
-----------------------------------  ----  ----------  ----------  -------------  ---------------

T141_USER1.SB_HISTOGRAMS             BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T2                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T4                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAMS             BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.U2T1                      BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.U2T2                      BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAMS             BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.U3T1                      BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        

--- 16 row(s) selected.
>>execute get_col_privs;

--- 0 row(s) selected.
>>
>>-- 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;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>values (user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER2                                                                                                                        

--- 1 row(s) selected.
>>set schema t141_user1;

--- SQL operation complete.
>>create table u2t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>create table u2t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>
>>get tables, match 'U2T%';

Tables in Schema TRAFODION.T141_USER1
=====================================

U2T1
U2T2

=======================
 2 row(s) returned

--- SQL operation complete.
>>
>>-- create a view on user1's table
>>create view u2v1 as select c1, c2 from u2t1;

--- SQL operation complete.
>>showddl u2v1;

CREATE VIEW TRAFODION.T141_USER1.U2V1 AS
  SELECT TRAFODION.T141_USER1.U2T1.C1, TRAFODION.T141_USER1.U2T1.C2 FROM
    TRAFODION.T141_USER1.U2T1 ;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T141_USER1.U2V1 TO T141_ROLE1 WITH GRANT OPTION;
  GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T141_USER1.U2V1
  TO SQL_USER2 WITH GRANT OPTION;

--- SQL operation complete.
>>
>>-- 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;

--- SQL operation complete.
>>showddl u2v2;

CREATE VIEW TRAFODION.T141_USER1.U2V2 (C1, C2) AS
  SELECT U1.C1, U2.C1 FROM TRAFODION.T141_USER2.U2T1 AS U2,
    TRAFODION.T141_USER1.U2T2 AS U1 ;

-- GRANT SELECT, REFERENCES ON TRAFODION.T141_USER1.U2V2 TO T141_ROLE1 WITH GRANT OPTION;
  GRANT SELECT, REFERENCES ON TRAFODION.T141_USER1.U2V2 TO SQL_USER2 WITH GRANT
  OPTION;

--- SQL operation complete.
>>
>>-- these creates should fail
>>-- user2 has no privs on u1t1
>>create view u1v3 as select * from u1t1;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T1.

--- SQL operation failed with errors.
>>set schema t141_user2;

--- SQL operation complete.
>>
>>-- 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;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T2.

--- SQL operation failed with errors.
>>
>>-- user2 has no privs on u3t1
>>create view u2v1 as select * from t141_user1.u3t1;

*** ERROR[4082] Object TRAFODION.T141_USER1.U3T1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>
>>-- ============================================================================
>>exit;

End of MXCI Session

>>execute get_obj_privs;

OBJECT_NAME                          TYPE  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
-----------------------------------  ----  ----------  ----------  -------------  ---------------

T141_USER1.SB_HISTOGRAMS             BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T2                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U1T4                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U2T1                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U2T1                      BT    T141_ROLE1  SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2T2                      BT    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U2T2                      BT    T141_ROLE1  SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2V1                      VI    -2          T141_ROLE1  SIDU-R-        SIDU-R-        
T141_USER1.U2V1                      VI    T141_ROLE1  SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2V2                      VI    -2          T141_ROLE1  S----R-        S----R-        
T141_USER1.U2V2                      VI    T141_ROLE1  SQL_USER2   S----R-        S----R-        
T141_USER2.SB_HISTOGRAMS             BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.U2T1                      BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER2.U2T2                      BT    -2          T141_ROLE2  SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAMS             BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAM_INTERVALS    BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.SB_PERSISTENT_SAMPLES     BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        
T141_USER3.U3T1                      BT    -2          T141_ROLE3  SIDU-R-        SIDU-R-        

--- 24 row(s) selected.
>>
>>-- ============================================================================
>>obey TEST141(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);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>revoke component privilege "CREATE" on sql_operations from sql_user2;

--- SQL operation complete.
>>
>>-- setup database with shared schemas owned by users
>>drop schema if exists t141_user1 cascade;

--- SQL operation complete.
>>create shared schema t141_user1 authorization sql_user1;

--- SQL operation complete.
>>drop schema if exists t141_user2 cascade;

--- SQL operation complete.
>>create shared schema t141_user2 authorization sql_user2;

--- SQL operation complete.
>>drop schema if exists t141_user3 cascade;

--- SQL operation complete.
>>create shared schema t141_user3 authorization sql_user3;

--- SQL operation complete.
>>showddl schema t141_user1;

CREATE SHARED SCHEMA "TRAFODION"."T141_USER1" AUTHORIZATION "SQL_USER1";

--- SQL operation complete.
>>showddl schema t141_user2;

CREATE SHARED SCHEMA "TRAFODION"."T141_USER2" AUTHORIZATION "SQL_USER2";

--- SQL operation complete.
>>showddl schema t141_user3;

CREATE SHARED SCHEMA "TRAFODION"."T141_USER3" AUTHORIZATION "SQL_USER3";

--- SQL operation complete.
>>
>>-- 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;

--- SQL operation complete.
>>create table u1t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t3 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t3 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u1t4 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u1t4 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U1T%';

Tables in Schema TRAFODION.T141_USER1
=====================================

U1T1
U1T2
U1T3
U1T4

=======================
 4 row(s) returned

--- SQL operation complete.
>>
>>set schema t141_user2;

--- SQL operation complete.
>>create table u2t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u2t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>create table u2t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u2t2 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U2T%';

Tables in Schema TRAFODION.T141_USER2
=====================================

U2T1
U2T2

=======================
 2 row(s) returned

--- SQL operation complete.
>>
>>set schema t141_user3;

--- SQL operation complete.
>>create table u3t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>insert into u3t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--- 5 row(s) inserted.
>>get tables, match 'U3T%';

Tables in Schema TRAFODION.T141_USER3
=====================================

U3T1

=======================
 1 row(s) returned

--- SQL operation complete.
>>
>>
>>-- ============================================================================
>>set schema t141_user1;

--- SQL operation complete.
>>grant select on u1t1 to sql_user3;

--- SQL operation complete.
>>grant select (c1, c2) on u1t2 to sql_user3;

--- SQL operation complete.
>>grant update (c1) on u1t2 to sql_user3;

--- SQL operation complete.
>>grant update, delete, insert on u1t3 to sql_user3;

--- SQL operation complete.
>>grant update on u1t4 to sql_user3;

--- SQL operation complete.
>>grant select(c1, c3) on u1t4 to sql_user3;

--- SQL operation complete.
>>execute get_obj_privs;

OBJECT_NAME                          TYPE  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
-----------------------------------  ----  ----------  ----------  -------------  ---------------

T141_USER1.SB_HISTOGRAMS             BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    DB__ROOT    SQL_USER3   S------        -------        
T141_USER1.U1T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    DB__ROOT    SQL_USER3   -IDU---        -------        
T141_USER1.U1T4                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T4                      BT    DB__ROOT    SQL_USER3   ---U---        -------        
T141_USER2.SB_HISTOGRAMS             BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.U2T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER2.U2T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAMS             BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.U3T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        

--- 19 row(s) selected.
>>execute get_col_privs;

OBJECT_NAME           COLUMN_NUMBER  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
--------------------  -------------  ----------  ----------  -------------  ---------------

T141_USER1.U1T2                   0  DB__ROOT    SQL_USER3   S--U---        -------        
T141_USER1.U1T2                   1  DB__ROOT    SQL_USER3   S------        -------        
T141_USER1.U1T4                   0  DB__ROOT    SQL_USER3   S------        -------        
T141_USER1.U1T4                   2  DB__ROOT    SQL_USER3   S------        -------        

--- 4 row(s) selected.
>>
>>-- 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;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>values (user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER2                                                                                                                        

--- 1 row(s) selected.
>>set schema t141_user1;

--- SQL operation complete.
>>create table u2t1 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>create table u2t2 (c1 int not null primary key, c2 int, c3 int);

--- SQL operation complete.
>>
>>get tables, match 'U2T%';

Tables in Schema TRAFODION.T141_USER1
=====================================

U2T1
U2T2

=======================
 2 row(s) returned

--- SQL operation complete.
>>
>>-- create a view on user1's table
>>create view u2v1 as select c1, c2 from u2t1;

--- SQL operation complete.
>>showddl u2v1;

CREATE VIEW TRAFODION.T141_USER1.U2V1 AS
  SELECT TRAFODION.T141_USER1.U2T1.C1, TRAFODION.T141_USER1.U2T1.C2 FROM
    TRAFODION.T141_USER1.U2T1 ;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T141_USER1.U2V1 TO SQL_USER2 WITH GRANT OPTION;

--- SQL operation complete.
>>
>>-- 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;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER2.U2T1.

--- SQL operation failed with errors.
>>showddl u2v2;

*** ERROR[4082] Object TRAFODION.T141_USER1.U2V2 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

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

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T1.

--- SQL operation failed with errors.
>>set schema t141_user2;

--- SQL operation complete.
>>
>>-- 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;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER2.U2T1.

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T2.

--- SQL operation failed with errors.
>>
>>-- user2 has no privs on u3t1
>>create view u2v1 as select * from t141_user1.u3t1;

*** ERROR[4082] Object TRAFODION.T141_USER1.U3T1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>
>>-- ============================================================================
>>exit;

End of MXCI Session

>>execute get_obj_privs;

OBJECT_NAME                          TYPE  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
-----------------------------------  ----  ----------  ----------  -------------  ---------------

T141_USER1.SB_HISTOGRAMS             BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    DB__ROOT    SQL_USER3   S------        -------        
T141_USER1.U1T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    DB__ROOT    SQL_USER3   -IDU---        -------        
T141_USER1.U1T4                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T4                      BT    DB__ROOT    SQL_USER3   ---U---        -------        
T141_USER1.U2T1                      BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2T2                      BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2V1                      VI    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAMS             BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.U2T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER2.U2T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAMS             BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.U3T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        

--- 22 row(s) selected.
>>
>>-- 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;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>values (user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>set schema t141_user1;

--- SQL operation complete.
>>create view u1v1 as select * from u1t1;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T1.

--- SQL operation failed with errors.
>>insert into u1v1 values (6,6,6);

*** ERROR[4082] Object TRAFODION.T141_USER1.U1V1 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>delete from u1v1 where c1 = 6;

*** ERROR[4082] Object TRAFODION.T141_USER1.U1V1 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>create view u1v2 as select * from u2t1;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U2T1.

--- SQL operation failed with errors.
>>insert into u1v2 values (6,6,6);

*** ERROR[4082] Object TRAFODION.T141_USER1.U1V2 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>delete from u1v2 where c1 = 6;

*** ERROR[4082] Object TRAFODION.T141_USER1.U1V2 does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>create view u1v3(c1, c2) as select u1.c1, u2.c1 from u1t1 u1, u2t1 u2;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T1.

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U2T1.

--- SQL operation failed with errors.
>>
>>-- ============================================================================
>>exit;

End of MXCI Session

>>
>>-- grant privileges to sql_user1 and retry
>>grant select, insert, delete on t141_user1.u1t1 to sql_user1;

--- SQL operation complete.
>>grant select on t141_user1.u2t1 to sql_user1;

--- SQL operation complete.
>>sh sqlci -i "TEST141(user1_objects)" -u sql_user1;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>values (user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>set schema t141_user1;

--- SQL operation complete.
>>create view u1v1 as select * from u1t1;

--- SQL operation complete.
>>insert into u1v1 values (6,6,6);

--- 1 row(s) inserted.
>>delete from u1v1 where c1 = 6;

--- 1 row(s) deleted.
>>create view u1v2 as select * from u2t1;

--- SQL operation complete.
>>insert into u1v2 values (6,6,6);

*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T141_USER1.U1V2.

*** ERROR[8822] The statement was not prepared.

>>delete from u1v2 where c1 = 6;

*** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.T141_USER1.U1V2.

*** ERROR[8822] The statement was not prepared.

>>create view u1v3(c1, c2) as select u1.c1, u2.c1 from u1t1 u1, u2t1 u2;

--- SQL operation complete.
>>
>>-- ============================================================================
>>exit;

End of MXCI Session

>>execute get_obj_privs;

OBJECT_NAME                          TYPE  GRANTOR     GRANTEE     GRANTED_PRIVS  GRANTABLE_PRIVS
-----------------------------------  ----  ----------  ----------  -------------  ---------------

T141_USER1.SB_HISTOGRAMS             BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER1   SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T1                      BT    DB__ROOT    SQL_USER1   SID----        -------        
T141_USER1.U1T1                      BT    DB__ROOT    SQL_USER3   S------        -------        
T141_USER1.U1T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T3                      BT    DB__ROOT    SQL_USER3   -IDU---        -------        
T141_USER1.U1T4                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER1.U1T4                      BT    DB__ROOT    SQL_USER3   ---U---        -------        
T141_USER1.U1V1                      VI    -2          SQL_USER1   SID----        -------        
T141_USER1.U1V2                      VI    -2          SQL_USER1   S------        -------        
T141_USER1.U1V3                      VI    -2          SQL_USER1   S------        -------        
T141_USER1.U2T1                      BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2T1                      BT    SQL_USER2   SQL_USER1   S------        -------        
T141_USER1.U2T2                      BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER1.U2V1                      VI    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAMS             BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER2   SIDU-R-        SIDU-R-        
T141_USER2.U2T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER2.U2T2                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAMS             BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_HISTOGRAM_INTERVALS    BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.SB_PERSISTENT_SAMPLES     BT    -2          SQL_USER3   SIDU-R-        SIDU-R-        
T141_USER3.U3T1                      BT    -2          DB__ROOT    SIDU-R-        SIDU-R-        

--- 27 row(s) selected.
>>
>>-- 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;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>values (user);

(EXPR)
---------------------------------------------------------------------------------------------------------------------------------

SQL_USER3                                                                                                                        

--- 1 row(s) selected.
>>set schema t141_user1;

--- SQL operation complete.
>>
>>--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);

--- SQL operation complete.
>>
>>-- following works based on granted privs
>>set schema t141_user3;

--- SQL operation complete.
>>create view u3v1 as select * from t141_user1.u1t1;

--- SQL operation complete.
>>showddl u3v1;

CREATE VIEW TRAFODION.T141_USER3.U3V1 AS
  SELECT TRAFODION.T141_USER1.U1T1.C1, TRAFODION.T141_USER1.U1T1.C2,
    TRAFODION.T141_USER1.U1T1.C3 FROM TRAFODION.T141_USER1.U1T1 ;

-- GRANT SELECT ON TRAFODION.T141_USER3.U3V1 TO SQL_USER3;

--- SQL operation complete.
>>create view u3v2 as select c1, c2 from t141_user1.u1t2;

--- SQL operation complete.
>>showddl u3v2;

CREATE VIEW TRAFODION.T141_USER3.U3V2 AS
  SELECT TRAFODION.T141_USER1.U1T2.C1, TRAFODION.T141_USER1.U1T2.C2 FROM
    TRAFODION.T141_USER1.U1T2 ;

-- GRANT SELECT ON TRAFODION.T141_USER3.U3V2 TO SQL_USER3;

--- SQL operation complete.
>>create view u3v3 as select c1 from t141_user1.u1t2;

--- SQL operation complete.
>>showddl u3v3;

CREATE VIEW TRAFODION.T141_USER3.U3V3 AS
  SELECT TRAFODION.T141_USER1.U1T2.C1 FROM TRAFODION.T141_USER1.U1T2 ;

-- GRANT SELECT, UPDATE ON TRAFODION.T141_USER3.U3V3 TO SQL_USER3;

--- SQL operation complete.
>>create view u3v4 as select c1, c2 from t141_user1.u1t4;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T4(columns: C2).

--- SQL operation failed with errors.
>>create view u3v4 as select c1, c3 from t141_user1.u1t4;

--- SQL operation complete.
>>
>>-- following fail
>>create view u3v5 as select c1, c3 from t141_user1.u1t3;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T141_USER1.U1T3.

--- SQL operation failed with errors.
>>get tables, match 'U3T%';

Tables in Schema TRAFODION.T141_USER3
=====================================

U3T1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views;

Views in Schema TRAFODION.T141_USER3
====================================

U3V1
U3V2
U3V3
U3V4

=======================
 4 row(s) returned

--- SQL operation complete.
>>
>>-- ============================================================================
>>exit;

End of MXCI Session

>>
>>-- ============================================================================
>>--obey TEST141(test_shared_role);
>>log;
