>>sh rm -f LOG135-SECONDARY;
>>obey TEST135(set_up);
>>-- ============================================================================
>>
>>-- create schema
>>create shared schema t135sch;

--- SQL operation complete.
>>
>>-- Prepare library file
>>sh rm -f ./udrtest135.dll;
>>sh sh $$scriptsdir$$/tools/dll-compile.ksh udrtest135.cpp
+>  2>&1 | tee LOG135-SECONDARY;
>>set pattern $$DLL$$ udrtest135.dll;
>>
>>get tables in schema "_PRIVMGR_MD_";

Tables in Schema TRAFODION._PRIVMGR_MD_
=======================================

COLUMN_PRIVILEGES
COMPONENTS
COMPONENT_OPERATIONS
COMPONENT_PRIVILEGES
OBJECT_PRIVILEGES
ROLE_USAGE
SCHEMA_PRIVILEGES

--- SQL operation complete.
>>
>>-- Prepare metadata queries
>>prepare check_privs from 
+>select object_name, grantee_name, grantor_name 
+>from "_PRIVMGR_MD_".object_privileges
+>where 
+>  object_name in ('TRAFODION.T135SCH.T135_T1', 'TRAFODION.T135SCH.T135_T2', 'TRAFODION.T135SCH.T135_V1', 'TRAFODION.T135SCH.T135_V2', 'TRAFODION.T135SCH.T135_L1', 'TRAFODION.T135SCH.T135_L2', 'TRAFODION.T135SCH.T135_SESSIONIZE', 'TRAFODION.T135SCH.T135_ADD2')
+>order by 1,3,2 for read uncommitted access;

--- SQL command prepared.
>>
>>obey TEST135(tbl_tests);
>>-- ============================================================================
>>set schema t135sch;

--- SQL operation complete.
>>
>>-- Verify that a create table adds privilege manager metadata
>>create table t135_t1 (c1 int not null primary key, c2 int);

--- SQL operation complete.
>>-- returns 1 row
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 1 row(s) selected.
>>
>>-- Verify that a drop table removes privilege manager metadata
>>drop table t135_t1;

--- SQL operation complete.
>>-- returns 0 rows
>>execute check_privs;

--- 0 row(s) selected.
>>
>>-- Verify metadata for tables and indexes
>>create table t135_t1 (c1 int not null primary key, c2 int);

--- SQL operation complete.
>>create index ndx1 on t135_t1(c2);

--- SQL operation complete.
>>-- returns 1 row
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 1 row(s) selected.
>>
>>drop table t135_t1;

--- SQL operation complete.
>>-- returns 0 rows
>>execute check_privs;

--- 0 row(s) selected.
>>
>>-- Verify metadata for tables, indexes, and views
>>create table t135_t1 (c1 int not null primary key, c2 int);

--- SQL operation complete.
>>create index ndx1 on t135_t1(c2);

--- SQL operation complete.
>>create view t135_v1 as select * from t135_t1;

--- SQL operation complete.
>>create view t135_v2 as select * from t135_t1;

--- SQL operation complete.
>>-- returns 3 rows
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_V1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_V2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 3 row(s) selected.
>>
>>-- fails
>>drop table t135_t1;

*** ERROR[1047] Request failed.  Dependent view TRAFODION.T135SCH.T135_V1 exists.

--- SQL operation failed with errors.
>>-- returns 3 rows
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_V1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_V2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 3 row(s) selected.
>>
>>drop table t135_t1 cascade;

--- SQL operation complete.
>>-- returns 0 rows
>>execute check_privs;

--- 0 row(s) selected.
>>
>>-- verify views referencing multiple tables and create table like
>>create table t135_t1 (c1 int not null primary key, c2 int);

--- SQL operation complete.
>>create table t135_t2 like t135_t1;

--- SQL operation complete.
>>create view t135_v1 as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2
+>   where t135_t1.c1 = t135_t2.c1;

--- SQL operation complete.
>>-- return 3 rows
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_V1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 3 row(s) selected.
>>
>>drop view t135_v1;

--- SQL operation complete.
>>-- return 2 rows
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         
TRAFODION.T135SCH.T135_T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 2 row(s) selected.
>>drop table t135_t1;

--- SQL operation complete.
>>-- return 1 rows
>>execute check_privs;

OBJECT_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GRANTEE_NAME                                                                                                                                                                                                                                                      GRANTOR_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION.T135SCH.T135_T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 DB__ROOT                                                                                                                                                                                                                                                          _SYSTEM                                                                                                                                                                                                                                                         

--- 1 row(s) selected.
>>drop table t135_t2;

--- SQL operation complete.
>>-- return 0 rows
>>execute check_privs;

--- 0 row(s) selected.
>>
>>obey TEST135(view_tests);
>>-- ============================================================================
>>set schema t135sch;

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

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

--- SQL operation complete.
>>create table t135_t3 (a int, b int);

--- SQL operation complete.
>>create sequence t135seq;

--- SQL operation complete.
>>
>>-- create a view referencing a single table where view creator has all privs
>>create view t135_v1_t1 as select * from t135_t1;

--- SQL operation complete.
>>-- view should be granted all DML privileges
>>showddl t135_v1_t1;

CREATE VIEW TRAFODION.T135SCH.T135_V1_T1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM
    TRAFODION.T135SCH.T135_T1 ;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_V1_T1 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>drop view t135_v1_t1;

--- SQL operation complete.
>>
>>-- create a non updatable, non insertable view
>>create view t135_v2_t1 
+>as select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;

--- SQL operation complete.
>>-- view should be granted only SELECT and REFERENCES privileges
>>showddl t135_v2_t1;

CREATE VIEW TRAFODION.T135SCH.T135_V2_T1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM
    TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ;

-- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.T135_V2_T1 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>drop view t135_v2_t1;

--- SQL operation complete.
>>
>>-- verify that users granted select privilege can create views
>>-- user cannot create view
>>sh sqlci -i "TEST135(user1_views)" -u sql_user1;
>>create view user1_v1 as select * from t135_t1;

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

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

*** ERROR[4082] Object TRAFODION.T135SCH.USER1_V1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>drop view user1_v1;

*** ERROR[1389] Object TRAFODION.T135SCH.USER1_V1 does not exist in Trafodion.

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

End of MXCI Session

>>
>>-- user can create view but only have select priv
>>grant select on t135_t1 to sql_user1;

--- SQL operation complete.
>>sh sqlci -i "TEST135(user1_views)" -u sql_user1;
>>create view user1_v1 as select * from t135_t1;

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

CREATE VIEW TRAFODION.T135SCH.USER1_V1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM
    TRAFODION.T135SCH.T135_T1 ;

-- GRANT SELECT ON TRAFODION.T135SCH.USER1_V1 TO SQL_USER1;

--- SQL operation complete.
>>drop view user1_v1;

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

End of MXCI Session

>>
>>-- user can create view and have all_dml privs
>>grant all_dml on t135_t1 to sql_user1;

--- SQL operation complete.
>>sh sqlci -i "TEST135(user1_views)" -u sql_user1;
>>create view user1_v1 as select * from t135_t1;

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

CREATE VIEW TRAFODION.T135SCH.USER1_V1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T1.C2 FROM
    TRAFODION.T135SCH.T135_T1 ;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.USER1_V1 TO SQL_USER1;

--- SQL operation complete.
>>drop view user1_v1;

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

End of MXCI Session

>>
>>-- make sure creator privileges are set when multiple referenced
>>-- objects are involved.
>>grant all_dml on t135_t1 to sql_user2;

--- SQL operation complete.
>>-- should fail user2 does not have select privilege on t135_t2
>>sh sqlci -i "TEST135(user2_views)" -u sql_user2;
>>create view user2_v1 as
+>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;

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

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

*** ERROR[4082] Object TRAFODION.T135SCH.USER2_V1 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>drop view user2_v1;

*** ERROR[1389] Object TRAFODION.T135SCH.USER2_V1 does not exist in Trafodion.

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

End of MXCI Session

>>
>>grant select on t135_t2 to sql_user2;

--- SQL operation complete.
>>-- user can create view and have select priv
>>sh sqlci -i "TEST135(user2_views)" -u sql_user2;
>>create view user2_v1 as
+>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;

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

CREATE VIEW TRAFODION.T135SCH.USER2_V1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM
    TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ;

-- GRANT SELECT ON TRAFODION.T135SCH.USER2_V1 TO SQL_USER2;

--- SQL operation complete.
>>drop view user2_v1;

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

End of MXCI Session

>>
>>grant all_DML on t135_t2 to sql_user2;

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

CREATE TABLE TRAFODION.T135SCH.T135_T1
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               INT DEFAULT NULL
  , PRIMARY KEY (C1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1 TO DB__ROOT WITH GRANT OPTION;
  GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T1
  TO SQL_USER1;
GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON
  TRAFODION.T135SCH.T135_T1 TO SQL_USER2;

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

CREATE TABLE TRAFODION.T135SCH.T135_T2
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               INT DEFAULT NULL
  , PRIMARY KEY (C1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T2 TO DB__ROOT WITH GRANT OPTION;
  GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T2
  TO SQL_USER2;

--- SQL operation complete.
>>-- user can create view but just have select and references, it is
>>-- a not updatable and insertable view
>>sh sqlci -i "TEST135(user2_views)" -u sql_user2;
>>create view user2_v1 as
+>select t135_t1.c1, t135_t2.c2 from t135_t1, t135_t2;

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

CREATE VIEW TRAFODION.T135SCH.USER2_V1 AS
  SELECT TRAFODION.T135SCH.T135_T1.C1, TRAFODION.T135SCH.T135_T2.C2 FROM
    TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_T2 ;

-- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.USER2_V1 TO SQL_USER2;

--- SQL operation complete.
>>drop view user2_v1;

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

End of MXCI Session

>>
>>-- test creating a view from several views and tables
>>create view t135_v1_t1 as select c1 from t135_t1;

--- SQL operation complete.
>>create view t135_v2_t1 as select c2 from t135_t1;

--- SQL operation complete.
>>create view t135_v1_t2 as select * from t135_t2;

--- SQL operation complete.
>>
>>-- have user3 create some objects
>>sh sqlci -i "TEST135(user3_objects)" -u sql_user3;
>>create table t135_t3 (c1 int not null primary key, c2 int);

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

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

CREATE TABLE TRAFODION.T135SCH_USER3.T135_T3
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               INT DEFAULT NULL
  , PRIMARY KEY (C1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T3 TO SQL_USER3 WITH GRANT OPTION;

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

CREATE TABLE TRAFODION.T135SCH_USER3.T135_T4
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C3                               INT DEFAULT NULL
  , PRIMARY KEY (C2 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH_USER3.T135_T4 TO SQL_USER3 WITH GRANT OPTION;

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

End of MXCI Session

>>
>>-- user3 create some views
>>-- fails because user3 has no privs
>>sh sqlci -i "TEST135(user3_views)" -u sql_user3;
>>create view t135_v1_user3 as 
+>  select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1, t135_t4.c3
+>  from t135sch.t135_t1, t135sch.t135_v1_t1, t135_t4;

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

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

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

*** ERROR[4082] Object TRAFODION.T135SCH_USER3.T135_V1_USER3 does not exist or is inaccessible.

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

End of MXCI Session

>>grant select on t135_v1_t1 to sql_user3;

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

--- SQL operation complete.
>>-- operations should succeed
>>sh sqlci -i "TEST135(user3_views)" -u sql_user3;
>>create view t135_v1_user3 as 
+>  select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1, t135_t4.c3
+>  from t135sch.t135_t1, t135sch.t135_v1_t1, t135_t4;

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

CREATE VIEW TRAFODION.T135SCH_USER3.T135_V1_USER3 AS
  SELECT TRAFODION.T135SCH.T135_T1.C2, TRAFODION.T135SCH.T135_V1_T1.C1,
    TRAFODION.T135SCH_USER3.T135_T4.C3 FROM TRAFODION.T135SCH.T135_T1,
    TRAFODION.T135SCH.T135_V1_T1, TRAFODION.T135SCH_USER3.T135_T4 ;

-- GRANT SELECT ON TRAFODION.T135SCH_USER3.T135_V1_USER3 TO SQL_USER3;

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

End of MXCI Session

>>
>>-- Since user3 now has a view, try to revoke privileges
>>-- fails because of user3's views
>>revoke select on t135_v1_t1 from sql_user3;

*** ERROR[1025] Request failed.  Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists.

--- SQL operation failed with errors.
>>revoke all on t135_t1 from sql_user3;

*** ERROR[1025] Request failed.  Dependent object TRAFODION.T135SCH_USER3.T135_V1_USER3 exists.

--- SQL operation failed with errors.
>>
>>sh sqlci -i "TEST135(user3_drops)" -u sql_user3;
>>drop table t135_t3 cascade;

--- SQL operation complete.
>>drop table t135_t4 cascade;

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

End of MXCI Session

>>
>>-- create some roles
>>create role t135_role1;

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

--- SQL operation complete.
>>grant role t135_role1, t135_role2 to sql_user4;

--- SQL operation complete.
>>grant select on t135_t1 to t135_role1;

--- SQL operation complete.
>>grant select on t135_v1_t1 to t135_role2;

--- SQL operation complete.
>>
>>-- have sql_user4 create a view based on role privs
>>create schema if not exists t135sch_user4 authorization sql_user4;

--- SQL operation complete.
>>sh sqlci -i "TEST135(user4_views)" -u sql_user4;
>>create view t135_v1_user4 as
+>  select t135sch.t135_t1.c2, t135sch.t135_v1_t1.c1
+>  from t135sch.t135_t1, t135sch.t135_v1_t1;

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

CREATE VIEW TRAFODION.T135SCH_USER4.T135_V1_USER4 AS
  SELECT TRAFODION.T135SCH.T135_T1.C2, TRAFODION.T135SCH.T135_V1_T1.C1 FROM
    TRAFODION.T135SCH.T135_T1, TRAFODION.T135SCH.T135_V1_T1 ;

-- GRANT SELECT ON TRAFODION.T135SCH_USER4.T135_V1_USER4 TO SQL_USER4;

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

End of MXCI Session

>>
>>-- should not be able to revoke user4 from role
>>revoke role t135_role1 from sql_user4;

*** ERROR[1364] Cannot revoke role T135_ROLE1. Object TRAFODION.T135SCH_USER4.T135_V1_USER4 depends on privileges on object TRAFODION.T135SCH.T135_T1.

--- SQL operation failed with errors.
>>revoke role t135_role2 from sql_user4;

*** ERROR[1364] Cannot revoke role T135_ROLE2. Object TRAFODION.T135SCH_USER4.T135_V1_USER4 depends on privileges on object TRAFODION.T135SCH.T135_V1_T1.

--- SQL operation failed with errors.
>>
>>-- after droppping the view, revokes succeed
>>drop view t135sch_user4.t135_v1_user4;

--- SQL operation complete.
>>revoke role t135_role1 from sql_user4;

--- SQL operation complete.
>>revoke role t135_role2 from sql_user4;

--- SQL operation complete.
>>
>>-- test views that reference sequence generators
>>GRANT COMPONENT privilege create_view on sql_operations to sql_user5;

--- SQL operation complete.
>>insert into t135_t3 values (1,1);

--- 1 row(s) inserted.
>>select * from t135_t3;

A            B          
-----------  -----------

          1            1

--- 1 row(s) selected.
>>grant select on t135_t3 to sql_user5;

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

CREATE TABLE TRAFODION.T135SCH.T135_T3
  (
    A                                INT DEFAULT NULL
  , B                                INT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T135SCH.T135_T3 TO DB__ROOT WITH GRANT OPTION;
  GRANT SELECT ON TRAFODION.T135SCH.T135_T3 TO SQL_USER5;

--- SQL operation complete.
>>create schema if not exists t135sch_user5 authorization sql_user5;

--- SQL operation complete.
>>
>>-- unable to create view, lacking USAGE privilege
>>sh sqlci -i "TEST135(user5_views)" -u sql_user5;
>>create view t135_v1_user5 as select seqnum(t135sch.t135seq) as a from t135sch.t135_t3;

*** ERROR[4491] The user does not have USAGE privilege on sequence TRAFODION.T135SCH.T135SEQ.

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

*** ERROR[4082] Object TRAFODION.T135SCH_USER5.T135_V1_USER5 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>select * from t135_v1_user5;

*** ERROR[4082] Object TRAFODION.T135SCH_USER5.T135_V1_USER5 does not exist or is inaccessible.

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

>>set schema t135sch;

--- SQL operation complete.
>>create view t135_v1_user5 as select seqnum(t135seq) as a from t135_t3;

*** ERROR[4491] The user does not have USAGE privilege on sequence TRAFODION.T135SCH.T135SEQ.

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

*** ERROR[4082] Object TRAFODION.T135SCH.T135_V1_USER5 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>select * from t135_v1_user5;

*** ERROR[4082] Object TRAFODION.T135SCH.T135_V1_USER5 does not exist or is inaccessible.

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

>>
>>exit;

End of MXCI Session

>>
>>-- grant privilege and try again
>>grant usage on sequence t135seq to sql_user5;

--- SQL operation complete.
>>showddl sequence t135seq;

CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ
  START WITH 1 /* NEXT AVAILABLE VALUE 1 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

-- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION;
  GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO SQL_USER5;

--- SQL operation complete.
>>sh sqlci -i "TEST135(user5_views)" -u sql_user5;
>>create view t135_v1_user5 as select seqnum(t135sch.t135seq) as a from t135sch.t135_t3;

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

CREATE VIEW TRAFODION.T135SCH_USER5.T135_V1_USER5 AS
  SELECT SEQNUM (TRAFODION.T135SCH.T135SEQ) AS A FROM
    TRAFODION.T135SCH.T135_T3 ;

-- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH_USER5.T135_V1_USER5 TO SQL_USER5 WITH GRANT OPTION;

--- SQL operation complete.
>>select * from t135_v1_user5;

A                   
--------------------

                   1

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

--- SQL operation complete.
>>create view t135_v1_user5 as select seqnum(t135seq) as a from t135_t3;

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

CREATE VIEW TRAFODION.T135SCH.T135_V1_USER5 AS
  SELECT SEQNUM (TRAFODION.T135SCH.T135SEQ) AS A FROM
    TRAFODION.T135SCH.T135_T3 ;

-- GRANT SELECT, REFERENCES ON TRAFODION.T135SCH.T135_V1_USER5 TO SQL_USER5 WITH GRANT OPTION;

--- SQL operation complete.
>>select * from t135_v1_user5;

A                   
--------------------

                   2

--- 1 row(s) selected.
>>
>>exit;

End of MXCI Session

>>
>>-- try revoking privilege
>>-- fails because of sql_user5's view
>>revoke usage on sequence t135seq from sql_user5;

*** ERROR[1025] Request failed.  Dependent object TRAFODION.T135SCH_USER5.T135_V1_USER5 exists.

--- SQL operation failed with errors.
>>
>>-- drop views and try again
>>drop view t135sch_user5.t135_v1_user5;

--- SQL operation complete.
>>revoke usage on sequence t135seq from sql_user5;

*** ERROR[1025] Request failed.  Dependent object TRAFODION.T135SCH.T135_V1_USER5 exists.

--- SQL operation failed with errors.
>>drop view t135sch.t135_v1_user5;

--- SQL operation complete.
>>revoke usage on sequence t135seq from sql_user5;

--- SQL operation complete.
>>showddl sequence t135seq;

CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ
  START WITH 1 /* NEXT AVAILABLE VALUE 26 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

-- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>
>>-- redo but this time grant SELECT, should fail
>>grant select on sequence t135seq to sql_user5;

*** ERROR[1267] SELECT privilege is incompatible with this object type.

--- SQL operation failed with errors.
>>showddl sequence t135seq;

CREATE SEQUENCE TRAFODION.T135SCH.T135SEQ
  START WITH 1 /* NEXT AVAILABLE VALUE 26 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

-- GRANT USAGE ON SEQUENCE TRAFODION.T135SCH.T135SEQ TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>REVOKE COMPONENT privilege create_view on sql_operations from sql_user5;

--- SQL operation complete.
>>
>>drop table t135_t1 cascade;

--- SQL operation complete.
>>drop table t135_t2 cascade;

--- SQL operation complete.
>>drop role t135_role1;

--- SQL operation complete.
>>drop role t135_role2;

--- SQL operation complete.
>>
>>obey TEST135(udr_tests);
>>-- ============================================================================
>>-- To create a function/table_mapping function, or SPJ, you need
>>-- to:
>>--   be DB__ROOT
>>--   be library owner
>>--   have the CREATE_ROUTINE component privilege
>>--   have USAGE privilege on the library
>>set schema t135sch_udrs;

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

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

Roles
=====

DB__HBASEROLE
DB__HIVEROLE
DB__LIBMGRROLE
DB__ROOTROLE
PUBLIC
T135_ROLE1

--- SQL operation complete.
>>
>>grant component privilege MANAGE_LIBRARY on sql_operations to t135_role1;

--- SQL operation complete.
>>grant component privilege CREATE_ROUTINE on sql_operations to "PUBLIC";

--- SQL operation complete.
>>get privileges on component sql_operations for t135_role1;

Privilege information on Component SQL_OPERATIONS for T135_ROLE1
================================================================

MANAGE_LIBRARY

--- SQL operation complete.
>>get privileges on component sql_operations for "PUBLIC";

Privilege information on Component SQL_OPERATIONS for PUBLIC
============================================================

CREATE_ROUTINE
CREATE_SCHEMA
SHOW

--- SQL operation complete.
>>
>>
>>-- create library and udrs as sql_user1
>>-- sql_user1 needs MANAGE_LIBRARY privilege to create libraries
>>-- fails - unsufficient privs
>>sh sqlci -i "TEST135(create_library)" -u sql_user1;
>>values (user);

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>create private schema t135sch_udrs;

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

--- SQL operation complete.
>>create library t135_l1 file 'udrtest135.dll';

*** ERROR[1017] You are not authorized to perform this operation.

--- SQL operation failed with errors.
>>grant USAGE on library t135_l1 to sql_user3;

*** ERROR[1389] Object T135_L1 does not exist in Trafodion.

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

End of MXCI Session

>>
>>-- now it succeeds
>>grant role t135_role1 to sql_user1;

--- SQL operation complete.
>>sh sqlci -i "TEST135(create_library)" -u sql_user1;
>>values (user);

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>create private schema t135sch_udrs;

*** ERROR[1022] Schema TRAFODION.T135SCH_UDRS already exists.

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

--- SQL operation complete.
>>create library t135_l1 file 'udrtest135.dll';

--- SQL operation complete.
>>grant USAGE on library t135_l1 to sql_user3;

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

End of MXCI Session

>>sh sqlci -i "TEST135(create_drop_udrs)" -u sql_user1;
>>values (user);

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>obey TEST135(create_udrs);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>create table_mapping function t135_sessionize(colname char(10), timeintval int)
+>returns (userid char(32), ts largeint, session_id largeint)
+>external name 'SESSIONIZE'
+>library t135_l1;

--- SQL operation complete.
>>
>>create function t135_ADD2(int,int) returns (ADD2 int)
+>language c parameter style sql external name 'add2'
+>library t135_l1
+>deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>obey TEST135(drop_udrs);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>drop function t135_add2;

--- SQL operation complete.
>>drop table_mapping function t135_sessionize;

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

End of MXCI Session

>>
>>-- verify that DB__ROOT can create/drop udrs
>>-- library l1 is owned by sql_user1
>>obey TEST135(create_drop_udrs);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>obey TEST135(create_udrs);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>create table_mapping function t135_sessionize(colname char(10), timeintval int)
+>returns (userid char(32), ts largeint, session_id largeint)
+>external name 'SESSIONIZE'
+>library t135_l1;

--- SQL operation complete.
>>
>>create function t135_ADD2(int,int) returns (ADD2 int)
+>language c parameter style sql external name 'add2'
+>library t135_l1
+>deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>obey TEST135(drop_udrs);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>drop function t135_add2;

--- SQL operation complete.
>>drop table_mapping function t135_sessionize;

--- SQL operation complete.
>>
>>
>>
>>-- verify that user with USAGE privilege on library can create/drop udrs
>>-- first show lack of USAGE privilege
>>sh sqlci -i "TEST135(create_udrs)" -u sql_user2;
>>values (user);

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

SQL_USER2                                                                                                                        

--- 1 row(s) selected.
>>
>>create table_mapping function t135_sessionize(colname char(10), timeintval int)
+>returns (userid char(32), ts largeint, session_id largeint)
+>external name 'SESSIONIZE'
+>library t135_l1;

*** ERROR[4481] The user does not have USAGE privilege on table or view TRAFODION.T135SCH_UDRS.T135_L1.

--- SQL operation failed with errors.
>>
>>create function t135_ADD2(int,int) returns (ADD2 int)
+>language c parameter style sql external name 'add2'
+>library t135_l1
+>deterministic no sql final call allow any parallelism state area size 1024 ;

*** ERROR[4481] The user does not have USAGE privilege on table or view TRAFODION.T135SCH_UDRS.T135_L1.

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

End of MXCI Session

>>
>>-- grant and verify user granted USAGE privilege can create udrs
>>sh sqlci -i "TEST135(create_user3_udrs)" -u sql_user3;
>>values (user);

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

SQL_USER3                                                                                                                        

--- 1 row(s) selected.
>>
>>create table_mapping function t135_sessionize(colname char(10), timeintval int)
+>returns (userid char(32), ts largeint, session_id largeint)
+>external name 'SESSIONIZE'
+>library t135sch_udrs.t135_l1;

--- SQL operation complete.
>>
>>create function t135_ADD2(int,int) returns (ADD2 int)
+>language c parameter style sql external name 'add2'
+>library t135sch_udrs.t135_l1
+>deterministic no sql final call allow any parallelism state area size 1024 ;

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

End of MXCI Session

>>
>>-- try to revoke the USAGE privilege from sql_user3
>>sh sqlci -i "TEST135(revoke_usage)" -u sql_user1;
>>values (user);

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>revoke usage on library t135_l1 from sql_user3;

*** ERROR[1025] Request failed.  Dependent object TRAFODION."T135SCH_USER3"."T135_SESSIONIZE" exists.

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

End of MXCI Session

>>
>>-- now it should work
>>drop function TRAFODION."T135SCH_USER3"."T135_ADD2";

--- SQL operation complete.
>>drop table_mapping function t135sch_user3.t135_sessionize;

--- SQL operation complete.
>>sh sqlci -i "TEST135(revoke_usage)" -u sql_user1;
>>values (user);

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

SQL_USER1                                                                                                                        

--- 1 row(s) selected.
>>
>>revoke usage on library t135_l1 from sql_user3;

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

End of MXCI Session

>>
>>-- reset
>>obey TEST135(drop_library);
>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

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

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

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

DB__ROOT                                                                                                                         

--- 1 row(s) selected.
>>
>>drop library t135_l1;

--- SQL operation complete.
>>drop schema t135sch_udrs;

--- SQL operation complete.
>>
>>revoke component privilege "MANAGE_LIBRARY" on sql_operations from t135_role1;

--- SQL operation complete.
>>revoke component privilege CREATE_ROUTINE on sql_operations from "PUBLIC";

--- SQL operation complete.
>>get privileges on component sql_operations for t135_role1;

--- SQL operation complete.
>>get privileges on component sql_operations for "PUBLIC";

Privilege information on Component SQL_OPERATIONS for PUBLIC
============================================================

CREATE_SCHEMA
SHOW

--- SQL operation complete.
>>
>>revoke role t135_role1 from sql_user1;

--- SQL operation complete.
>>drop role t135_role1;

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

Roles
=====

DB__HBASEROLE
DB__HIVEROLE
DB__LIBMGRROLE
DB__ROOTROLE
PUBLIC

--- SQL operation complete.
>>
>>drop schema t135sch_user3 cascade;

--- SQL operation complete.
>>
>>obey TEST135(negative_tests);
>>-- ============================================================================
>>set schema t135sch;

--- SQL operation complete.
>>
>>log;
