>>obey TEST143(set_up);
>>-- ============================================================================
>>-- Setup the test environment
>>
>>-- create function to display bitmaps as a bitmap rather than longs
>>-- use the function from privs2/TEST140
>>sh rm -f ./etest140.dll;
>>sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
+>  2>&1 | tee LOG140-SECONDARY;
>>set pattern $$DLL$$ etest140.dll;
>>set pattern $$QUOTE$$ '''';
>>
>>create schema t143_udr;

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

--- SQL operation complete.
>>create library t143_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;

--- SQL operation complete.
>>create function translateBitmap(bitmap largeint) returns (bitmap_string char (20))
+>language c parameter style sql external name 'translateBitmap'
+>library t143_l1
+>deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>grant execute on function t143_udr.translateBitmap to "PUBLIC";

--- SQL operation complete.
>>
>>-- query to read privs from metadata
>>prepare get_obj_privs from
+>select distinct
+>   substring (object_name,11,15) as object_name,
+>   object_type as type,
+>   substring(authname(grantor_id),1,10) as grantor,
+>   substring(authname(grantee_id),1,10) as grantee,
+>   t143_udr.translateBitmap(privileges_bitmap) as granted_privs,
+>   t143_udr.translateBitmap(grantable_bitmap) as grantable_privs
+>from "_PRIVMGR_MD_".object_privileges
+>where object_uid in
+>     (select object_uid
+>      from "_MD_".objects
+>      where object_name like 'U%' and schema_name like 'T143_USER%')
+>  order by 1, 2, 3
+>;

--- SQL command prepared.
>>
>>prepare get_col_privs from
+>select distinct
+>   substring (object_name,11,15) as object_name,
+>   column_number,
+>   substring(authname(grantor_id),1,10) as grantor,
+>   substring(authname(grantee_id),1,10) as grantee,
+>   t143_udr.translateBitmap(privileges_bitmap) as granted_privs,
+>   t143_udr.translateBitmap(grantable_bitmap) as grantable_privs
+>from "_PRIVMGR_MD_".column_privileges
+>where object_uid in
+>     (select object_uid
+>      from "_MD_".objects
+>      where object_name like 'U%' and schema_name like 'T143_USER%')
+>  order by 1, 2, 3, 4
+>;

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

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

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

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

--- SQL operation complete.
>>
>>-- ============================================================================
>>obey TEST143(test_view_object_priv_propagation);
>>-- ============================================================================
>>values (user);

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>-- remove schemas 
>>drop schema if exists t143_user1 cascade;

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

--- SQL operation complete.
>>
>>-- setup database with private schemas owned by users
>>create schema t143_user1 authorization sql_user1;

--- SQL operation complete.
>>create schema t143_user2 authorization sql_user2;

--- SQL operation complete.
>>
>>-- create some tables owned by user1
>>set schema t143_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.
>>
>>-- grant privileges to sql_user2 on tables
>>grant select, delete on u1t1 to sql_user2;

--- SQL operation complete.
>>grant select, delete on u1t2 to sql_user2 with grant option;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   S-D----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   S-D----               S-D----             

--- 4 row(s) selected.
>>
>>-- user2 creates some views
>>sh sqlci -i "TEST143(user2_views)" -u sql_user2;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>cqd traf_store_object_desc 'ON';

--- SQL operation complete.
>>cqd traf_read_object_desc 'ON';

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

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

SQL_USER2                                                                                                                        

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

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

--- SQL operation complete.
>>create view u2v2 as select * from t143_user1.u1t2;

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

End of MXCI Session

>>
>>execute get_obj_privs;

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   S-D----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   S-D----               S-D----             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   S-D----               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   S-D----               S-D----             

--- 6 row(s) selected.
>>
>>-- user1 grants insert privilege on tables
>>--    u2v1 should have insert priv
>>--    u2v2 should have insert priv WGO
>>grant insert on u1t1 to sql_user2;

--- SQL operation complete.
>>grant insert on u1t2 to sql_user2 with grant option;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   SID----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   SID----               SID----             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   SID----               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   SID----               SID----             

--- 6 row(s) selected.
>>
>>-- remove with grant option for some privileges
>>--    u2v2 should have insert, delete without WGO
>>--    u2v2 should have select WGO
>>revoke grant option for insert, delete on u1t2 from sql_user2;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   SID----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   SID----               S------             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   SID----               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   SID----               S------             

--- 6 row(s) selected.
>>
>>-- Add back the with grant option for delete
>>--    u2v2 should have select, delete with WGO
>>grant delete on u1t2 to sql_user2 with grant option;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   SID----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   SID----               S-D----             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   SID----               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   SID----               S-D----             

--- 6 row(s) selected.
>>
>>-- Remove insert, and delete entirely
>>--    u2v2 has select WGO
>>revoke insert, delete on u1t1 from sql_user2;

--- SQL operation complete.
>>revoke insert, delete on u1t2 from sql_user2;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   SQL_USER2   S------               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   SQL_USER2   S------               S------             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   S------               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   S------               S------             

--- 6 row(s) selected.
>>
>>-- ============================================================================
>>obey TEST143(test_view_column_priv_propagation);
>>-- ============================================================================
>>
>>-- regression test 129 tests that views can be created based on column privs
>>--  and that revoke works (or fails) according to specifications.
>>-- this section tests that privileges are propagated to dependent views when 
>>--  privs are changed on referenced objects
>>values (user);

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>-- remove schemas
>>drop schema if exists t143_user1 cascade;

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

--- SQL operation complete.
>>
>>-- setup database with private schemas owned by users
>>create schema t143_user1 authorization sql_user1;

--- SQL operation complete.
>>create schema t143_user3 authorization sql_user3;

--- SQL operation complete.
>>
>>-- create some tables owned by user1
>>set schema t143_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, c4 int);

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

--- 5 row(s) inserted.
>>
>>-- grant column level privileges to sql_user3
>>grant select (c1,c2) on t143_user1.u1t1 to sql_user3;

--- SQL operation complete.
>>grant select (c1,c3) on t143_user1.u1t2 to sql_user3 with grant option;

--- SQL operation complete.
>>grant select (c2,c3,c1,c4) on t143_user1.u1t3 to sql_user3;

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T2                                                           0  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           1  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           2  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           3  SQL_USER1   SQL_USER3   S------               NONE                

--- 8 row(s) selected.
>>
>>-- user3 can create all views
>>sh sqlci -i "TEST143(user3_views)" -u sql_user3;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>cqd traf_store_object_desc 'ON';

--- SQL operation complete.
>>cqd traf_read_object_desc 'ON';

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

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

SQL_USER3                                                                                                                        

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

--- SQL operation complete.
>>
>>-- following views can be created based on granted privs
>>create view u3v1 as select c1, c2 from t143_user1.u1t1;

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

CREATE VIEW TRAFODION.T143_USER3.U3V1 AS
  SELECT TRAFODION.T143_USER1.U1T1.C1, TRAFODION.T143_USER1.U1T1.C2 FROM
    TRAFODION.T143_USER1.U1T1 ;

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

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

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

CREATE VIEW TRAFODION.T143_USER3.U3V2 AS
  SELECT TRAFODION.T143_USER1.U1T2.C1, TRAFODION.T143_USER1.U1T2.C3 FROM
    TRAFODION.T143_USER1.U1T2 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V2 TO SQL_USER3 WITH GRANT OPTION;

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

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

CREATE VIEW TRAFODION.T143_USER3.U3V3 AS
  SELECT TRAFODION.T143_USER1.U1T2.C1 FROM TRAFODION.T143_USER1.U1T2 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V3 TO SQL_USER3 WITH GRANT OPTION;

--- SQL operation complete.
>>create view u3v4 as select c2, c1, c4 from t143_user1.u1t3;

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

CREATE VIEW TRAFODION.T143_USER3.U3V4 AS
  SELECT TRAFODION.T143_USER1.U1T3.C2, TRAFODION.T143_USER1.U1T3.C1,
    TRAFODION.T143_USER1.U1T3.C4 FROM TRAFODION.T143_USER1.U1T3 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V4 TO SQL_USER3;

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

Tables in Schema TRAFODION.T143_USER3
=====================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES

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

Views in Schema TRAFODION.T143_USER3
====================================

U3V1
U3V2
U3V3
U3V4

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

End of MXCI Session

>>
>>-- Add column privs to tables that are propagated to views
>>--   u3v1 can now insert without WGO
>>--   u3v2 still cannot insert
>>--   u3v3 can insert without WGO
>>--   u3v4 can update without WGO
>>grant insert (c1, c2) on t143_user1.u1t1 to sql_user3;

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

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

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    SQL_USER1   SQL_USER3   ---U---               NONE                
T143_USER3.U3V1                                               VI    -2          SQL_USER3   SI-----               NONE                
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   SI-----               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S--U---               NONE                

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER3   SI-----               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER3   SI-----               NONE                
T143_USER1.U1T2                                                           0  SQL_USER1   SQL_USER3   SI-----               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           1  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           2  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           3  SQL_USER1   SQL_USER3   S------               NONE                

--- 8 row(s) selected.
>>
>>-- Revoke privileges
>>--   u3v1 can no longer insert even though one column has insert priv
>>--   u3v3 can no longer insert
>>--   u3v4 can not longer update
>>revoke insert (c1) on t143_user1.u1t1 from sql_user3;

--- SQL operation complete.
>>revoke insert (c1) on t143_user1.u1t2 from sql_user3;

--- SQL operation complete.
>>revoke update  on t143_user1.u1t3 from sql_user3;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S------               NONE                
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S------               NONE                

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER3   SI-----               NONE                
T143_USER1.U1T2                                                           0  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           1  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           2  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           3  SQL_USER1   SQL_USER3   S------               NONE                

--- 8 row(s) selected.
>>
>>-- Test WGO grants 
>>--  grant WGO
>>grant references (c1, c2, c3) on t143_user1.u1t1 to sql_user3 with grant option;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S----R-               -----R-             
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S------               NONE                

--- 7 row(s) selected.
>>-- revoke WGO
>>revoke grant option for references (c1) on t143_user1.u1t1 from sql_user3;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S----R-               NONE                
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S------               NONE                

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER3   S----R-               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER3   SI---R-               -----R-             
T143_USER1.U1T1                                                           2  SQL_USER1   SQL_USER3   -----R-               -----R-             
T143_USER1.U1T2                                                           0  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   SQL_USER3   S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           1  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           2  SQL_USER1   SQL_USER3   S------               NONE                
T143_USER1.U1T3                                                           3  SQL_USER1   SQL_USER3   S------               NONE                

--- 9 row(s) selected.
>>
>>-- ============================================================================
>>obey TEST143(test_view_role_priv_propagation);
>>-- ============================================================================
>>values (user);

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>-- remove schemas
>>drop schema if exists t143_user1 cascade;

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

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

--- SQL operation complete.
>>
>>-- setup database with private schemas owned by users
>>create schema t143_user1 authorization sql_user1;

--- SQL operation complete.
>>create schema t143_user2 authorization sql_user2;

--- SQL operation complete.
>>create schema t143_user3 authorization sql_user3;

--- SQL operation complete.
>>
>>-- create some tables owned by user1
>>set schema t143_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, c4 int);

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

--- 5 row(s) inserted.
>>
>>-- grant privileges to roles on tables
>>grant select (c1,c2,c3) on t143_user1.u1t1 to user2_role;

--- SQL operation complete.
>>grant select (c1,c2) on t143_user1.u1t1 to user3_role;

--- SQL operation complete.
>>grant select (c1,c2,c3) on t143_user1.u1t2 to user2_role with grant option;

--- SQL operation complete.
>>grant select (c1,c3) on t143_user1.u1t2 to user3_role with grant option;

--- SQL operation complete.
>>grant select (c2,c3,c1,c4) on t143_user1.u1t3 to user3_role;

--- SQL operation complete.
>>
>>-- create views
>>grant role user2_role to sql_user2;

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

--- SQL operation complete.
>>sh sqlci -i "TEST143(user2_views)" -u sql_user2;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>cqd traf_store_object_desc 'ON';

--- SQL operation complete.
>>cqd traf_read_object_desc 'ON';

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

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

SQL_USER2                                                                                                                        

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

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

--- SQL operation complete.
>>create view u2v2 as select * from t143_user1.u1t2;

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

End of MXCI Session

>>sh sqlci -i "TEST143(user3_views)" -u sql_user3;
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>cqd traf_store_object_desc 'ON';

--- SQL operation complete.
>>cqd traf_read_object_desc 'ON';

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

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

SQL_USER3                                                                                                                        

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

--- SQL operation complete.
>>
>>-- following views can be created based on granted privs
>>create view u3v1 as select c1, c2 from t143_user1.u1t1;

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

CREATE VIEW TRAFODION.T143_USER3.U3V1 AS
  SELECT TRAFODION.T143_USER1.U1T1.C1, TRAFODION.T143_USER1.U1T1.C2 FROM
    TRAFODION.T143_USER1.U1T1 ;

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

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

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

CREATE VIEW TRAFODION.T143_USER3.U3V2 AS
  SELECT TRAFODION.T143_USER1.U1T2.C1, TRAFODION.T143_USER1.U1T2.C3 FROM
    TRAFODION.T143_USER1.U1T2 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V2 TO SQL_USER3 WITH GRANT OPTION;

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

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

CREATE VIEW TRAFODION.T143_USER3.U3V3 AS
  SELECT TRAFODION.T143_USER1.U1T2.C1 FROM TRAFODION.T143_USER1.U1T2 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V3 TO SQL_USER3 WITH GRANT OPTION;

--- SQL operation complete.
>>create view u3v4 as select c2, c1, c4 from t143_user1.u1t3;

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

CREATE VIEW TRAFODION.T143_USER3.U3V4 AS
  SELECT TRAFODION.T143_USER1.U1T3.C2, TRAFODION.T143_USER1.U1T3.C1,
    TRAFODION.T143_USER1.U1T3.C4 FROM TRAFODION.T143_USER1.U1T3 ;

-- GRANT SELECT ON TRAFODION.T143_USER3.U3V4 TO SQL_USER3;

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

Tables in Schema TRAFODION.T143_USER3
=====================================

SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES

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

Views in Schema TRAFODION.T143_USER3
====================================

U3V1
U3V2
U3V3
U3V4

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

End of MXCI Session

>>
>>-- user1 grants insert privilege on tables to roles
>>--    u2v1 should have insert priv without WGO
>>--    u2v2 should have insert priv WGO
>>--    u3v1 should have insert priv without WGO
>>--    u3v2 should not be granted insert, only one col
>>--    u3v3 should have insert priv without WGO
>>--    u3v4 shoud have update without WGO, select with WGO
>>grant insert on u1t1 to user2_role;

--- SQL operation complete.
>>grant insert on u1t2 to user2_role with grant option;

--- SQL operation complete.
>>grant insert (c1, c2) on t143_user1.u1t1 to user3_role;

--- SQL operation complete.
>>grant insert (c1) on t143_user1.u1t2 to user3_role;

--- SQL operation complete.
>>grant update on t143_user1.u1t3 to user3_role;

--- SQL operation complete.
>>grant select (c2, c3, c1,c4) on t143_user1.u1t3 to user3_role with grant option;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   USER2_ROLE  -I-----               NONE                
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   USER2_ROLE  -I-----               -I-----             
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T3                                               BT    SQL_USER1   USER3_ROLE  ---U---               NONE                
T143_USER2.U2V1                                               VI    -2          SQL_USER2   SI-----               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   SI-----               SI-----             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   SI-----               NONE                
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   SI-----               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S--U---               S------             

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T1                                                           0  SQL_USER1   USER3_ROLE  SI-----               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   USER3_ROLE  SI-----               NONE                
T143_USER1.U1T1                                                           2  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T2                                                           0  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           0  SQL_USER1   USER3_ROLE  SI-----               S------             
T143_USER1.U1T2                                                           1  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           1  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           2  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           3  SQL_USER1   USER3_ROLE  S------               S------             

--- 14 row(s) selected.
>>
>>-- Reset privs
>>revoke insert on u1t1 from user2_role;

--- SQL operation complete.
>>revoke grant option for insert on u1t2 from user2_role;

--- SQL operation complete.
>>revoke insert (c1, c2) on t143_user1.u1t1 from user3_role;

--- SQL operation complete.
>>revoke insert (c1) on t143_user1.u1t2 from user3_role;

--- SQL operation complete.
>>revoke update on t143_user1.u1t3 from user3_role;

--- SQL operation complete.
>>revoke grant option for select (c2, c3) on t143_user1.u1t3 from user3_role;

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

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T2                                               BT    SQL_USER1   USER2_ROLE  -I-----               NONE                
T143_USER1.U1T3                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER2.U2V1                                               VI    -2          SQL_USER2   S------               NONE                
T143_USER2.U2V2                                               VI    -2          SQL_USER2   SI-----               S------             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S------               NONE                
T143_USER3.U3V2                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V3                                               VI    -2          SQL_USER3   S------               S------             
T143_USER3.U3V4                                               VI    -2          SQL_USER3   S------               NONE                

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T1                                                           0  SQL_USER1   USER3_ROLE  S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   USER3_ROLE  S------               NONE                
T143_USER1.U1T1                                                           2  SQL_USER1   USER2_ROLE  S------               NONE                
T143_USER1.U1T2                                                           0  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           0  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T2                                                           1  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1T2                                                           2  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           0  SQL_USER1   USER3_ROLE  S------               S------             
T143_USER1.U1T3                                                           1  SQL_USER1   USER3_ROLE  S------               NONE                
T143_USER1.U1T3                                                           2  SQL_USER1   USER3_ROLE  S------               NONE                
T143_USER1.U1T3                                                           3  SQL_USER1   USER3_ROLE  S------               S------             

--- 14 row(s) selected.
>>
>>-- ============================================================================
>>obey TEST143(test_view_misc_priv_propagation);
>>-- ============================================================================
>>--
>>-- Verifies that multiple views with different owners don't cause issues
>>-- 
>>--  Views:
>>--
>>-- u1v1 -> u1t1
>>-- u5v1 -> u1t1
>>-- u5v2 -> u1t1
>>-- u2v1 -> u1v1 -> u1t1
>>-- u3v1 -> u2v1 -> u1v1 ->u1t1
>>-- u3v2 -> u3v1 -> u2v1 -> u1v1 -> u1t1
>>
>>values (user);

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>-- remove schemas
>>drop schema if exists t143_user1 cascade;

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

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

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

--- SQL operation complete.
>>
>>-- setup database with private schemas owned by users
>>create schema t143_user1 authorization sql_user1;

--- SQL operation complete.
>>create schema t143_user2 authorization user2_role;

--- SQL operation complete.
>>create schema t143_user3 authorization sql_user3;

--- SQL operation complete.
>>create schema t143_user5 authorization sql_user5;

--- SQL operation complete.
>>
>>-- create objects owned by sql_user1
>>set schema t143_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.
>>grant select (c1) on u1t1 to sql_user5;

--- SQL operation complete.
>>grant select (c2, c3) on u1t1 to sql_user5 with grant option;

--- SQL operation complete.
>>
>>create view u1v1 (u1v1_c1, u1v1_c2) as select c2, c3 from u1t1;

--- SQL operation complete.
>>grant select on u1v1 to user2_role with grant option;

--- SQL operation complete.
>>grant select on u1v1 to sql_user5;

--- SQL operation complete.
>>
>>-- create objects for role user2_role;
>>set schema t143_user2;

--- SQL operation complete.
>>create view u2v1 (u2v1_c1, u2v1_c2) as
+>  select u1v1_c2, u1v1_c1 from t143_user1.u1v1;

--- SQL operation complete.
>>grant select (u2v1_c1, u2v1_c2) on u2v1 to sql_user3;

--- SQL operation complete.
>>grant select on u2v1 to sql_user6 with grant option;

--- SQL operation complete.
>>grant select (u2v1_c2) on u2v1 to sql_user6;

--- SQL operation complete.
>>
>>-- create objects for user sql_user3
>>set schema t143_user3;

--- SQL operation complete.
>>create view u3v1 (u3v1_c1, u3v1_c2) as
+>  select u2v1_c2, u2v1_c1 
+>  from t143_user2.u2v1;

--- SQL operation complete.
>>
>>-- create objects for user sq1_user5
>>set schema t143_user5;

--- SQL operation complete.
>>create view u5v1(u5v1_c1, u5v1_c2, u5v1_c3) as 
+>   select * from t143_user1.u1t1;

--- SQL operation complete.
>>create view u5v2 (u5v1_c1) as
+>  select u5v1_c2 from t143_user5.u5v1, t143_user1.u1v1;

--- SQL operation complete.
>>
>>-- u1v1 user1 grants: select user2_role WGO, select user5
>>-- u2v1 user2-role grants: select user6 WGO
>>execute get_obj_privs;

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1V1                                               VI    -2          SQL_USER1   S-DU-R-               S-DU-R-             
T143_USER1.U1V1                                               VI    SQL_USER1   SQL_USER5   S------               NONE                
T143_USER1.U1V1                                               VI    SQL_USER1   USER2_ROLE  S------               S------             
T143_USER2.U2V1                                               VI    -2          USER2_ROLE  S------               S------             
T143_USER2.U2V1                                               VI    USER2_ROLE  SQL_USER6   S------               S------             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S------               NONE                
T143_USER5.U5V1                                               VI    -2          SQL_USER5   S------               NONE                
T143_USER5.U5V2                                               VI    -2          SQL_USER5   S----R-               S----R-             

--- 9 row(s) selected.
>>
>>-- u1t1 user1 grants: c1-select, c2/c3-select WGO user5
>>-- u2v1 user2_role grants: c1/c2-select user3, select-c2 user6
>>execute get_col_privs;

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER5   S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER5   S------               S------             
T143_USER1.U1T1                                                           2  SQL_USER1   SQL_USER5   S------               S------             
T143_USER2.U2V1                                                           0  USER2_ROLE  SQL_USER3   S------               NONE                
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER3   S------               NONE                
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER6   S------               NONE                

--- 6 row(s) selected.
>>
>>-- Run some different tests:
>>
>>-- fails with no priv
>>grant select (u3v1_c1, u3v1_c2) on t143_user3.u3v1 to sql_user4;

*** ERROR[1012] No privileges were granted.  You lack grant option on the specified privileges.

--- SQL operation failed with errors.
>>
>>-- user2_role grants to user3 WGO
>>grant select (u2v1_c1, u2v1_c2) on t143_user2.u2v1 to sql_user3 with grant option;

--- SQL operation complete.
>>
>>-- now user3's grant works, user4 can create views on u3v1
>>grant select (u3v1_c1, u3v1_c2) on t143_user3.u3v1 to sql_user4;

--- SQL operation complete.
>>
>>-- grant insert on user1's table, doesn't affect any views
>>grant insert on t143_user1.u1t1 to user2_role with grant option;

--- SQL operation complete.
>>
>>-- u1t1 user1 grants: insert user2_role WGO
>>execute get_obj_privs;

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   USER2_ROLE  -I-----               -I-----             
T143_USER1.U1V1                                               VI    -2          SQL_USER1   S-DU-R-               S-DU-R-             
T143_USER1.U1V1                                               VI    SQL_USER1   USER2_ROLE  S------               S------             
T143_USER1.U1V1                                               VI    SQL_USER1   SQL_USER5   S------               NONE                
T143_USER2.U2V1                                               VI    -2          USER2_ROLE  S------               S------             
T143_USER2.U2V1                                               VI    USER2_ROLE  SQL_USER6   S------               S------             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S------               S------             
T143_USER5.U5V1                                               VI    -2          SQL_USER5   S------               NONE                
T143_USER5.U5V2                                               VI    -2          SQL_USER5   S----R-               S----R-             

--- 10 row(s) selected.
>>-- u2v1 user2_role grants: + c1/c2 WGO user3 
>>-- u3v1 user3 grants: select-c1/c2 user4
>>execute get_col_privs;

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER5   S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER5   S------               S------             
T143_USER1.U1T1                                                           2  SQL_USER1   SQL_USER5   S------               S------             
T143_USER2.U2V1                                                           0  USER2_ROLE  SQL_USER3   S------               S------             
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER3   S------               S------             
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER6   S------               NONE                
T143_USER3.U3V1                                                           0  SQL_USER3   SQL_USER4   S------               NONE                
T143_USER3.U3V1                                                           1  SQL_USER3   SQL_USER4   S------               NONE                

--- 8 row(s) selected.
>>
>>-- grant insert on user1's view
>>-- fails because u1v1 is not an insertable view
>>-- unfortunately, the returned error is not the best
>>grant insert on t143_user1.u1v1 to user2_role;

*** ERROR[1012] No privileges were granted.  You lack grant option on the specified privileges.

--- SQL operation failed with errors.
>>
>>-- grant update on user1's view, this works
>>grant update on t143_user1.u1v1 to user2_role;

--- SQL operation complete.
>>
>>-- grant privileges by user5, these fail
>>grant select on t143_user5.u5v1 to sql_user4;

*** ERROR[1012] No privileges were granted.  You lack grant option on the specified privileges.

--- SQL operation failed with errors.
>>grant select (u5v1_c1, u5v1_c2, u5v1_c3) on t143_user5.u5v1 to sql_user4;

*** ERROR[1012] No privileges were granted.  You lack grant option on the specified privileges.

--- SQL operation failed with errors.
>>
>>-- this fails, sql_user5 has WGO for the underlying column (u5v1_c2) but the
>>-- columns cannot be split
>>grant select (u5v1_c2) on t143_user5.u5v1 to sql_user4;

*** ERROR[1012] No privileges were granted.  You lack grant option on the specified privileges.

--- SQL operation failed with errors.
>>
>>-- this succeeds
>>grant select on t143_user5.u5v2 to sql_user4;

--- SQL operation complete.
>>
>>-- u1v1 user1 grants: + update user2_role
>>-- u5v2 user5 grants: c1-select user4
>>execute get_obj_privs;

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

T143_USER1.U1T1                                               BT    -2          SQL_USER1   SIDU-R-               SIDU-R-             
T143_USER1.U1T1                                               BT    SQL_USER1   USER2_ROLE  -I-----               -I-----             
T143_USER1.U1V1                                               VI    -2          SQL_USER1   S-DU-R-               S-DU-R-             
T143_USER1.U1V1                                               VI    SQL_USER1   SQL_USER5   S------               NONE                
T143_USER1.U1V1                                               VI    SQL_USER1   USER2_ROLE  S--U---               S------             
T143_USER2.U2V1                                               VI    -2          USER2_ROLE  S--U---               S------             
T143_USER2.U2V1                                               VI    USER2_ROLE  SQL_USER6   S------               S------             
T143_USER3.U3V1                                               VI    -2          SQL_USER3   S------               S------             
T143_USER5.U5V1                                               VI    -2          SQL_USER5   S------               NONE                
T143_USER5.U5V2                                               VI    -2          SQL_USER5   S----R-               S----R-             
T143_USER5.U5V2                                               VI    SQL_USER5   SQL_USER4   S------               NONE                

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

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

T143_USER1.U1T1                                                           0  SQL_USER1   SQL_USER5   S------               NONE                
T143_USER1.U1T1                                                           1  SQL_USER1   SQL_USER5   S------               S------             
T143_USER1.U1T1                                                           2  SQL_USER1   SQL_USER5   S------               S------             
T143_USER2.U2V1                                                           0  USER2_ROLE  SQL_USER3   S------               S------             
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER3   S------               S------             
T143_USER2.U2V1                                                           1  USER2_ROLE  SQL_USER6   S------               NONE                
T143_USER3.U3V1                                                           0  SQL_USER3   SQL_USER4   S------               NONE                
T143_USER3.U3V1                                                           1  SQL_USER3   SQL_USER4   S------               NONE                

--- 8 row(s) selected.
>>
>>-- ============================================================================
>>log;
