You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/01/12 23:47:09 UTC
[09/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/privs1/EXPECTED132
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED132 b/core/sql/regress/privs1/EXPECTED132
new file mode 100644
index 0000000..b472586
--- /dev/null
+++ b/core/sql/regress/privs1/EXPECTED132
@@ -0,0 +1,2214 @@
+>>obey TEST132(test_libraries);
+>>-- =================================================================
+>>-- run tests to make sure users that create libraries have correct
+>>-- privileges. To create a library, you must:
+>>-- be DB__ROOT
+>>-- be granted DB__ROOTROLE
+>>-- have the MANAGE_LIBRARY and CREATE/CREATE_LIBARARY privileges
+>>-- =================================================================
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>get libraries;
+
+--- 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.
+>>
+>>-- succeed: DB__ROOT can create a library
+>>sh sqlci -i "TEST132(manage_library)";
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>create library t132_l1 file 'etest132.dll';
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+Libraries in Schema TRAFODION.T132SCH
+=====================================
+
+T132_L1
+
+--- SQL operation complete.
+>>drop library t132_l1;
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- fail: sql_user1 cannot create a library
+>>sh sqlci -i "TEST132(manage_library)" -u sql_user1;
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>create library t132_l1 file 'etest132.dll';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>drop library t132_l1;
+
+*** ERROR[1389] Object TRAFODION.T132SCH.T132_L1 does not exist in Trafodion.
+
+--- SQL operation failed with errors.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- succeed: grant DB__ROOTROLE to sql_user1
+>>grant role DB__ROOTROLE to sql_user1;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(manage_library)" -u sql_user1;
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>create library t132_l1 file 'etest132.dll';
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+Libraries in Schema TRAFODION.T132SCH
+=====================================
+
+T132_L1
+
+--- SQL operation complete.
+>>drop library t132_l1;
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- fail: just grant the create privilege
+>>grant component privilege CREATE_LIBRARY on sql_operations to sql_user2;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(manage_library)" -u sql_user2;
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>create library t132_l1 file 'etest132.dll';
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>drop library t132_l1;
+
+*** ERROR[1389] Object TRAFODION.T132SCH.T132_L1 does not exist in Trafodion.
+
+--- SQL operation failed with errors.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- succeed: now grant the manage_library privilege
+>>grant component privilege MANAGE_LIBRARY on sql_operations to sql_user2;
+
+--- SQL operation complete.
+>>get privileges on component sql_operation for sql_user2;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(manage_library)" -u sql_user2;
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>create library t132_l1 file 'etest132.dll';
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+Libraries in Schema TRAFODION.T132SCH
+=====================================
+
+T132_L1
+
+--- SQL operation complete.
+>>drop library t132_l1;
+
+--- SQL operation complete.
+>>get libraries in schema t132sch;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- reset
+>>revoke role DB__ROOTROLE from sql_user1;
+
+--- SQL operation complete.
+>>revoke component privilege MANAGE_LIBRARY on sql_operations from sql_user2;
+
+--- SQL operation complete.
+>>revoke component privilege CREATE_LIBRARY on sql_operations from sql_user2;
+
+--- 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.
+>>
+>>obey TEST132(test_load_unload_purgedata);
+>>-- =================================================================
+>>-- run tests to make sure users with correct privileges can load
+>>-- and unload data. To load a table, you must:
+>>-- be DB__ROOT
+>>-- be granted DB__ROOTROLE
+>>-- have the MANAGE_LOAD
+>>-- be table owner
+>>-- have SELECT and INSERT (DELETE for TRUNCATE option) privilege
+>>--
+>>-- To unload a table, you must:`
+>>-- be DB__ROOT
+>>-- be granted DB__ROOTROLE
+>>-- have the MANAGE_LOAD
+>>-- be table owner
+>>-- have SELECT privilege
+>>--
+>>-- To purgedata data, you must:
+>>-- be DB__ROOT
+>>-- be table owner
+>>-- have SELECT and DELETE privileges
+>>-- =================================================================
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>
+>>
+>>-- perform a load and unload as DB__ROOT
+>>-- purgedata
+>>-- perform a load and unload after being granted DB__ROOTROLE
+>>-- purgedata
+>>-- perform a load and unload without privileges
+>>-- perform a load and unload as table owner
+>>-- purgedata
+>>-- perform a load and unload with SELECT
+>>-- perform a load and unload with SELECT, INSERT
+>>-- purgedata, grant DELETE and purgedata again
+>>-- perform a load (truncate) and unload with select, insert, delete
+>>-- perform a load and unload with MANAGE_LOAD privilege
+>>
+>>-- reset
+>>
+>>obey TEST132(test_popindex);
+>>-- =================================================================
+>>-- run tests to make sure users that populate indexes have correct
+>>-- privileges. To populate an index, you must:
+>>-- be DB__ROOT
+>>-- be table owner
+>>-- have the SELECT and INSERT privilege
+>>-- =================================================================
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>
+>>create table t132t1 (c1 int not null primary key, c2 int);
+
+--- SQL operation complete.
+>>create index t132t1_ndx1 on t132t1 (c2) no populate;
+
+--- SQL operation complete.
+>>create table t132t2 (c1 int not null primary key, c2 int)
++> attribute by sql_user1;
+
+--- SQL operation complete.
+>>create index t132t2_ndx1 on t132t2(c2) no populate;
+
+--- SQL operation complete.
+>>create table t132t3 (c1 int not null primary key, c2 int)
++> attribute by sql_user1;
+
+--- SQL operation complete.
+>>create index t132t3_ndx1 on t132t3(c2) no populate;
+
+--- SQL operation complete.
+>>
+>>insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
+
+--- 8 row(s) inserted.
+>>insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
+
+--- 8 row(s) inserted.
+>>insert into t132t3 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
+
+--- 8 row(s) inserted.
+>>
+>>get tables;
+
+Tables in Schema TRAFODION.T132SCH
+==================================
+
+T132T1
+T132T2
+T132T3
+
+--- SQL operation complete.
+>>showddl t132t1;
+
+CREATE TABLE TRAFODION.T132SCH.T132T1
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT DEFAULT NULL
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132t2;
+
+CREATE TABLE TRAFODION.T132SCH.T132T2
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT DEFAULT NULL
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T2 TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132t3;
+
+CREATE TABLE TRAFODION.T132SCH.T132T3
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT DEFAULT NULL
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T3 TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>-- DB__ROOT can populate indexes
+>>sh sqlci -i "TEST132(populate_index)";
+>>
+>>populate index t132t1_ndx1 on t132t1;
+
+--- SQL operation complete.
+>>populate index t132t2_ndx1 on t132t2;
+
+--- SQL operation complete.
+>>populate index t132t3_ndx1 on t132t3;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>obey TEST132(popindex_check_reset);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>set parserflags 1;
+
+--- SQL operation complete.
+>>
+>>select count(*) from table (index_table t132t1_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t2_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t3_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>
+>>drop index t132t1_ndx1;
+
+--- SQL operation complete.
+>>create index t132t1_ndx1 on t132t1 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t2_ndx1;
+
+--- SQL operation complete.
+>>create index t132t2_ndx1 on t132t2 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t3_ndx1;
+
+--- SQL operation complete.
+>>create index t132t3_ndx1 on t132t3 (c2) no populate;
+
+--- SQL operation complete.
+>>
+>>
+>>-- object owner can populate
+>>-- sql_user1 owns t132t2 and t132t3 but not t132t1
+>>-- popindex fails for t132t1 but works for the rest
+>>sh sqlci -i "TEST132(populate_index)" -u sql_user1;
+>>
+>>populate index t132t1_ndx1 on t132t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>populate index t132t2_ndx1 on t132t2;
+
+--- SQL operation complete.
+>>populate index t132t3_ndx1 on t132t3;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>obey TEST132(popindex_check_reset);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>set parserflags 1;
+
+--- SQL operation complete.
+>>
+>>select count(*) from table (index_table t132t1_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t2_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t3_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>
+>>drop index t132t1_ndx1;
+
+--- SQL operation complete.
+>>create index t132t1_ndx1 on t132t1 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t2_ndx1;
+
+--- SQL operation complete.
+>>create index t132t2_ndx1 on t132t2 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t3_ndx1;
+
+--- SQL operation complete.
+>>create index t132t3_ndx1 on t132t3 (c2) no populate;
+
+--- SQL operation complete.
+>>
+>>
+>>-- if user belongs to DB__ROOTROLE, has no privilege
+>>grant role DB__ROOTROLE to sql_user2;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(populate_index)" -u sql_user2;
+>>
+>>populate index t132t1_ndx1 on t132t1;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>populate index t132t2_ndx1 on t132t2;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T2.
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>populate index t132t3_ndx1 on t132t3;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T3.
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T132SCH.T132T3.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- if user has SELECT and INSERT privileges
+>>grant SELECT,INSERT on t132t1 to sql_user3;
+
+--- SQL operation complete.
+>>grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(populate_index)" -u sql_user3;
+>>
+>>populate index t132t1_ndx1 on t132t1;
+
+--- SQL operation complete.
+>>populate index t132t2_ndx1 on t132t2;
+
+--- SQL operation complete.
+>>populate index t132t3_ndx1 on t132t3;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T3.
+
+*** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T132SCH.T132T3.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>obey TEST132(popindex_check_reset);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>set parserflags 1;
+
+--- SQL operation complete.
+>>
+>>select count(*) from table (index_table t132t1_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t2_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from table (index_table t132t3_ndx1);
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>
+>>drop index t132t1_ndx1;
+
+--- SQL operation complete.
+>>create index t132t1_ndx1 on t132t1 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t2_ndx1;
+
+--- SQL operation complete.
+>>create index t132t2_ndx1 on t132t2 (c2) no populate;
+
+--- SQL operation complete.
+>>drop index t132t3_ndx1;
+
+--- SQL operation complete.
+>>create index t132t3_ndx1 on t132t3 (c2) no populate;
+
+--- SQL operation complete.
+>>
+>>
+>>-- reset
+>>revoke role DB__ROOTROLE from sql_user2;
+
+--- SQL operation complete.
+>>drop table t132t1 cascade;
+
+--- SQL operation complete.
+>>drop table t132t2 cascade;
+
+--- SQL operation complete.
+>>drop table t132t3 cascade;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>
+>>obey TEST132(test_show);
+>>-- =================================================================
+>>-- run tests to make sure users that perform show commands have correct
+>>-- privileges. To perform show commands, you must:
+>>-- be DB__ROOT
+>>-- be object owner
+>>-- have the SHOW privilege (PUBLIC & DB__ROOTROLE has priv)
+>>-- have SELECT privileges on object
+>>-- =================================================================
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>
+>>create table t132_teams
++> (team_number int not null primary key,
++> team_name char(20) not null,
++> team_contact varchar(50) not null,
++> team_contact_number char (10) not null
++> )
++> ;
+
+--- SQL operation complete.
+>>
+>>alter table t132_teams add constraint valid_team_no check (team_number > 0);
+
+--- SQL operation complete.
+>>
+>>insert into t132_teams values
++> (1, 'White Socks', 'Sam','4082282222'),
++> (2, 'Giants', 'Joe', '5102839483'),
++> (3, 'Cardinals', 'Stella', '9513849384'),
++> (4, 'Indians', 'Matt', '5128383748'),
++> (5, 'Tigers', 'Ronit', '6198273827');
+
+--- 5 row(s) inserted.
+>>
+>>create table t132_games
++> ( home_team_number int not null,
++> visitor_team_number int not null,
++> game_number int not null primary key,
++> game_time timestamp not null,
++> game_location varchar(50) not null)
++> attribute by sql_user1
++> ;
+
+--- SQL operation complete.
+>>create index t132_home_games on t132_games (home_team_number);
+
+--- SQL operation complete.
+>>
+>>alter table t132_games add constraint valid_game_number check (game_number > 0);
+
+--- SQL operation complete.
+>>
+>>insert into t132_games values
++> (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
++> (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
++> (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
++> (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
++> (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
++> (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
++> (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
++> (4, 2, 8, current_timestamp, 'Missouri');
+
+--- 8 row(s) inserted.
+>>
+>>create view t132_giants_games as
++> select game_number, game_time, game_location
++> from t132_games
++> where home_team_number = 2
++> order by 1,2,3;
+
+--- SQL operation complete.
+>>select * from t132_giants_games;
+
+GAME_NUMBER GAME_TIME GAME_LOCATION
+----------- -------------------------- --------------------------------------------------
+
+ 4 2009-04-25 13:30:00.000000 Michigan
+ 6 2009-04-27 17:00:00.000000 New York
+
+--- 2 row(s) selected.
+>>
+>>create view t132_home_teams_games as
++> select t.team_number, g.game_number, g.game_time
++> from "T132_TEAMS" t,
++> "T132_GAMES" g
++> where t.team_number = g.home_team_number
++> order by 1, game_number, game_time;
+
+--- SQL operation complete.
+>>select team_number, game_number from t132_home_teams_games;
+
+TEAM_NUMBER GAME_NUMBER
+----------- -----------
+
+ 1 1
+ 1 2
+ 1 3
+ 1 5
+ 2 4
+ 2 6
+ 3 7
+ 4 8
+
+--- 8 row(s) selected.
+>>
+>>create sequence t132_team_number_sequence;
+
+--- SQL operation complete.
+>>
+>>-- revoke show prvilege from PUBLIC
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+SHOW
+
+--- SQL operation complete.
+>>revoke component privilege "SHOW" on sql_operations from "PUBLIC";
+
+--- SQL operation complete.
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+
+--- SQL operation complete.
+>>
+>>-- DB__ROOT has all privileges
+>>obey TEST132(show_objects);
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS
+ SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER,
+ TRAFODION.T132SCH.T132_GAMES.GAME_TIME,
+ TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM
+ TRAFODION.T132SCH.T132_GAMES WHERE
+ TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ;
+
+-- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_home_teams_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS
+ SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM
+ TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE
+ T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME
+ ;
+
+-- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl sequence t132_team_number_sequence;
+
+CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE
+ START WITH 1 /* NEXT AVAILABLE VALUE 1 */
+ INCREMENT BY 1
+ MAXVALUE 9223372036854775806
+ MINVALUE 1
+ CACHE 25
+ NO CYCLE
+ LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:38:42 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:38:44 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES
+-- Definition current Wed Dec 16 22:38:46 2015
+
+ (
+ GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_home_teams_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES
+-- Definition current Wed Dec 16 22:38:47 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>
+>>-- sql_user1 owns some of the objects but not all
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user1;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl sequence t132_team_number_sequence;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:39:05 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege
+>>-- first illustrate that sql_user2 has no privileges
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user2;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_teams;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl sequence t132_team_number_sequence;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>invoke t132_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_teams;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant role DB__ROOTROLE to sql_user2;
+
+--- SQL operation complete.
+>>
+>>-- now sql_user2 has privileges with the grant
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user2;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS
+ SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER,
+ TRAFODION.T132SCH.T132_GAMES.GAME_TIME,
+ TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM
+ TRAFODION.T132SCH.T132_GAMES WHERE
+ TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ;
+
+-- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_home_teams_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS
+ SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM
+ TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE
+ T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME
+ ;
+
+-- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl sequence t132_team_number_sequence;
+
+CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE
+ START WITH 1 /* NEXT AVAILABLE VALUE 1 */
+ INCREMENT BY 1
+ MAXVALUE 9223372036854775806
+ MINVALUE 1
+ CACHE 25
+ NO CYCLE
+ LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:39:46 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:39:46 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES
+-- Definition current Wed Dec 16 22:39:46 2015
+
+ (
+ GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_home_teams_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES
+-- Definition current Wed Dec 16 22:39:47 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>revoke role DB__ROOTROLE from sql_user2;
+
+--- SQL operation complete.
+>>
+>>-- sql_user3 gets some privileges through SELECT grant
+>>grant SELECT on t132_teams to sql_user3;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user3;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+ GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>showddl sequence t132_team_number_sequence;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>invoke t132_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:40:09 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>invoke t132_home_teams_games;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- regrant the show privs - everyone has privs
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+
+--- SQL operation complete.
+>>grant component privilege "SHOW" on sql_operations to "PUBLIC";
+
+--- 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.
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user1;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+ GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS
+ SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER,
+ TRAFODION.T132SCH.T132_GAMES.GAME_TIME,
+ TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM
+ TRAFODION.T132SCH.T132_GAMES WHERE
+ TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ;
+
+-- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_home_teams_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS
+ SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM
+ TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE
+ T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME
+ ;
+
+-- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl sequence t132_team_number_sequence;
+
+CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE
+ START WITH 1 /* NEXT AVAILABLE VALUE 1 */
+ INCREMENT BY 1
+ MAXVALUE 9223372036854775806
+ MINVALUE 1
+ CACHE 25
+ NO CYCLE
+ LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:40:31 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:40:31 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES
+-- Definition current Wed Dec 16 22:40:31 2015
+
+ (
+ GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_home_teams_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES
+-- Definition current Wed Dec 16 22:40:31 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user2;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+ GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS
+ SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER,
+ TRAFODION.T132SCH.T132_GAMES.GAME_TIME,
+ TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM
+ TRAFODION.T132SCH.T132_GAMES WHERE
+ TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ;
+
+-- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_home_teams_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS
+ SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM
+ TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE
+ T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME
+ ;
+
+-- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl sequence t132_team_number_sequence;
+
+CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE
+ START WITH 1 /* NEXT AVAILABLE VALUE 1 */
+ INCREMENT BY 1
+ MAXVALUE 9223372036854775806
+ MINVALUE 1
+ CACHE 25
+ NO CYCLE
+ LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:40:52 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:40:52 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES
+-- Definition current Wed Dec 16 22:40:52 2015
+
+ (
+ GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_home_teams_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES
+-- Definition current Wed Dec 16 22:40:52 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>sh sqlci -i "TEST132(show_objects)" -u sql_user3;
+>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+
+--- SQL operation complete.
+>>
+>>showddl t132_games;
+
+CREATE TABLE TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (GAME_NUMBER ASC)
+ )
+;
+
+CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES
+ (
+ HOME_TEAM_NUMBER ASC
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK
+ (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_teams;
+
+CREATE TABLE TRAFODION.T132SCH.T132_TEAMS
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , PRIMARY KEY (TEAM_NUMBER ASC)
+ )
+;
+
+ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT
+ TRAFODION.T132SCH.VALID_TEAM_NO CHECK
+ (TRAFODION.T132SCH.T132_TEAMS.TEAM_NUMBER > 0)
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_TEAMS TO DB__ROOT WITH GRANT OPTION;
+ GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3;
+
+--- SQL operation complete.
+>>showddl t132_giants_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS
+ SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER,
+ TRAFODION.T132SCH.T132_GAMES.GAME_TIME,
+ TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM
+ TRAFODION.T132SCH.T132_GAMES WHERE
+ TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ;
+
+-- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl t132_home_teams_games;
+
+CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS
+ SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM
+ TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE
+ T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME
+ ;
+
+-- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>showddl sequence t132_team_number_sequence;
+
+CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE
+ START WITH 1 /* NEXT AVAILABLE VALUE 1 */
+ INCREMENT BY 1
+ MAXVALUE 9223372036854775806
+ MINVALUE 1
+ CACHE 25
+ NO CYCLE
+ LARGEINT
+;
+
+-- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>
+>>invoke t132_games;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES
+-- Definition current Wed Dec 16 22:41:13 2015
+
+ (
+ HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_teams;
+
+-- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS
+-- Definition current Wed Dec 16 22:41:13 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , TEAM_CONTACT_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_giants_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES
+-- Definition current Wed Dec 16 22:41:13 2015
+
+ (
+ GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE
+ DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>invoke t132_home_teams_games;
+
+-- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES
+-- Definition current Wed Dec 16 22:41:13 2015
+
+ (
+ TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT
+ DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>drop table t132_teams cascade;
+
+--- SQL operation complete.
+>>drop table t132_games cascade;
+
+--- SQL operation complete.
+>>drop sequence t132_team_number_sequence;
+
+--- SQL operation complete.
+>>
+>>obey TEST132(test_stats);
+>>-- =================================================================
+>>-- run tests to make sure users that update statistics have correct
+>>-- privileges. To update stats, you must:
+>>-- be DB__ROOT
+>>-- be table owner
+>>-- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv)
+>>-- =================================================================
+>>
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>
+>>create table t132t1 (c1 int, c2 int);
+
+--- SQL operation complete.
+>>create table t132t2 (c1 int, c2 int) attribute by sql_user1;
+
+--- SQL operation complete.
+>>insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
+
+--- 8 row(s) inserted.
+>>insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8);
+
+--- 8 row(s) inserted.
+>>
+>>get tables;
+
+Tables in Schema TRAFODION.T132SCH
+==================================
+
+T132T1
+T132T2
+
+--- SQL operation complete.
+>>select count(*) from t132t1;
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>select count(*) from t132t2;
+
+(EXPR)
+--------------------
+
+ 8
+
+--- 1 row(s) selected.
+>>
+>>-- update statistics as DB__ROOT
+>>sh sqlci -i "TEST132(update_stats)";
+>>
+>>update statistics for table t132t1 on every column;
+
+--- SQL operation complete.
+>>update statistics for table t132t2 on every column;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- run as DB__ROOTROLE
+>>-- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default
+>>-- first show that sql_user2 cannot perform operations
+>>sh sqlci -i "TEST132(update_stats)" -u sql_user2;
+>>
+>>update statistics for table t132t1 on every column;
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>update statistics for table t132t2 on every column;
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant role DB__ROOTROLE to sql_user2;
+
+--- SQL operation complete.
+>>
+>>-- now show privileges after being granted DB__ROOTROLE role
+>>sh sqlci -i "TEST132(update_stats)" -u sql_user2;
+>>
+>>update statistics for table t132t1 on every column;
+
+--- SQL operation complete.
+>>update statistics for table t132t2 on every column;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>revoke role DB__ROOTROLE from sql_user2;
+
+--- SQL operation complete.
+>>
+>>-- run as table owner, sql_user1 owns one table
+>>-- update stats only works for t132t2, showstats works on both tables
+>>sh sqlci -i "TEST132(show_update_stats)" -u sql_user1;
+>>
+>>update statistics for table t132t1 on every column;
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>update statistics for table t132t2 on every column;
+
+--- SQL operation complete.
+>>
+>>obey TEST132(show_stats);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>
+>>showstats for table t132t1 on every column;
+
+Histogram data for Table TRAFODION.T132SCH.T132T1
+Table ID: 5885726695669026634
+
+ Hist ID # Ints Rowcount UEC Colname(s)
+========== ====== =========== =========== ===========================
+ 312208455 8 8 8 SYSKEY
+ 312208448 8 8 8 C1
+ 312208445 8 8 8 C2
+
+
+--- SQL operation complete.
+>>showstats for table t132t2 on every column;
+
+Histogram data for Table TRAFODION.T132SCH.T132T2
+Table ID: 5885726695669026693
+
+ Hist ID # Ints Rowcount UEC Colname(s)
+========== ====== =========== =========== ===========================
+ 315889526 8 8 8 SYSKEY
+ 315889521 8 8 8 C1
+ 315889516 8 8 8 C2
+
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- revoke SHOW privilege from public for the next set of tests
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+SHOW
+
+--- SQL operation complete.
+>>revoke component privilege "SHOW" on sql_operations from "PUBLIC";
+
+--- SQL operation complete.
+>>get privileges on component sql_operations for "PUBLIC";
+
+Privilege information on Component SQL_OPERATIONS for PUBLIC
+============================================================
+
+CREATE_SCHEMA
+
+--- SQL operation complete.
+>>
+>>-- Run with MANAGE_STATISTICS and no SHOW
+>>-- first illustrate that sql_user3 has no privs
+>>get privileges on component sql_operations for sql_user3;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(show_update_stats)" -u sql_user3;
+>>
+>>update statistics for table t132t1 on every column;
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>update statistics for table t132t2 on every column;
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>
+>>obey TEST132(show_stats);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>
+>>showstats for table t132t1 on every column;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>showstats for table t132t2 on every column;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T2.
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3;
+
+--- SQL operation complete.
+>>
+>>-- now show privileges after being granted MANAGE_STATISTICS
+>>get privileges on component sql_operations for sql_user3;
+
+Privilege information on Component SQL_OPERATIONS for SQL_USER3
+===============================================================
+
+MANAGE_STATISTICS
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(show_update_stats)" -u sql_user3;
+>>
+>>update statistics for table t132t1 on every column;
+
+--- SQL operation complete.
+>>update statistics for table t132t2 on every column;
+
+--- SQL operation complete.
+>>
+>>obey TEST132(show_stats);
+>>set schema t132sch;
+
+--- SQL operation complete.
+>>log LOG132;
+>>
+>>showstats for table t132t1 on every column;
+
+Histogram data for Table TRAFODION.T132SCH.T132T1
+Table ID: 5885726695669026634
+
+ Hist ID # Ints Rowcount UEC Colname(s)
+========== ====== =========== =========== ===========================
+ 312208454 8 8 8 SYSKEY
+ 312208449 8 8 8 C1
+ 312208444 8 8 8 C2
+
+
+--- SQL operation complete.
+>>showstats for table t132t2 on every column;
+
+Histogram data for Table TRAFODION.T132SCH.T132T2
+Table ID: 5885726695669026693
+
+ Hist ID # Ints Rowcount UEC Colname(s)
+========== ====== =========== =========== ===========================
+ 315889527 8 8 8 SYSKEY
+ 315889520 8 8 8 C1
+ 315889517 8 8 8 C2
+
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3;
+
+--- SQL operation complete.
+>>get privileges on component sql_operations for sql_user3;
+
+--- SQL operation complete.
+>>
+>>-- test showstats
+>>-- showstats should no longer work
+>>sh sqlci -i "TEST132(show_stats)" -u sql_user3;
+>>
+>>showstats for table t132t1 on every column;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1.
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T1.
+
+--- SQL operation failed with errors.
+>>showstats for table t132t2 on every column;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T2.
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- grant select to allow showstats to work
+>>grant SELECT on t132t1 to sql_user4;
+
+--- SQL operation complete.
+>>showddl t132t1;
+
+CREATE TABLE TRAFODION.T132SCH.T132T1
+ (
+ C1 INT DEFAULT NULL
+ , C2 INT DEFAULT NULL
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T1 TO DB__ROOT WITH GRANT OPTION;
+ GRANT SELECT ON TRAFODION.T132SCH.T132T1 TO SQL_USER4;
+
+--- SQL operation complete.
+>>sh sqlci -i "TEST132(show_stats)" -u sql_user4;
+>>
+>>showstats for table t132t1 on every column;
+
+Histogram data for Table TRAFODION.T132SCH.T132T1
+Table ID: 5885726695669026634
+
+ Hist ID # Ints Rowcount UEC Colname(s)
+========== ====== =========== =========== ===========================
+ 312208454 8 8 8 SYSKEY
+ 312208449 8 8 8 C1
+ 312208444 8 8 8 C2
+
+
+--- SQL operation complete.
+>>showstats for table t132t2 on every column;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T2.
+
+*** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2.
+
+--- SQL operation failed with errors.
+>>exit;
+
+End of MXCI Session
+
+>>
+>>-- reset
+>>revoke SELECT on t132t1 from sql_user4;
+
+--- SQL operation complete.
+>>grant component privilege "SHOW" on sql_operations to "PUBLIC";
+
+--- 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.
+>>
+>>drop table t132t1;
+
+--- SQL operation complete.
+>>drop table t132t2;
+
+--- SQL operation complete.
+>>
+>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/privs1/EXPECTED133
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED133 b/core/sql/regress/privs1/EXPECTED133
new file mode 100644
index 0000000..4bd4913
--- /dev/null
+++ b/core/sql/regress/privs1/EXPECTED133
@@ -0,0 +1,453 @@
+>>obey TEST133(create_db);
+>>create schema t133sch;
+
+--- SQL operation complete.
+>>set schema t133sch;
+
+--- SQL operation complete.
+>>create table teams
++> (team_number int not null primary key,
++> team_name char(20) not null,
++> team_contact varchar(50) not null,
++> team_contact_number char (10) not null
++> )
++> ;
+
+--- SQL operation complete.
+>>
+>>alter table teams add constraint valid_team_no check (team_number > 0);
+
+--- SQL operation complete.
+>>
+>>insert into teams values
++> (1, 'White Socks', 'Sam','4082282222'),
++> (2, 'Giants', 'Joe', '5102839483'),
++> (3, 'Cardinals', 'Stella', '9513849384'),
++> (4, 'Indians', 'Matt', '5128383748'),
++> (5, 'Tigers', 'Ronit', '6198273827');
+
+--- 5 row(s) inserted.
+>>
+>>create table team_statistics
++> (team_number int not null primary key,
++> num_players int not null)
++>;
+
+--- SQL operation complete.
+>>
+>>create table games
++> ( home_team_number int not null,
++> visitor_team_number int not null,
++> game_number int not null primary key,
++> game_time timestamp not null,
++> game_location varchar(50) not null)
++> ;
+
+--- SQL operation complete.
+>>--create index home_games on games (home_team_number);
+>>
+>>alter table games add constraint valid_game_number check (game_number > 0);
+
+--- SQL operation complete.
+>>
+>>create table standings
++> (team_number int not null primary key,
++> wins int default 0,
++> loses int default 0,
++> last_updated timestamp default current_timestamp)
++>;
+
+--- SQL operation complete.
+>>insert into standings (team_number)
++> select team_number from teams;
+
+--- 5 row(s) inserted.
+>>
+>>insert into games values
++> (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
++> (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
++> (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
++> (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
++> (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
++> (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
++> (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
++> (4, 2, 8, current_timestamp, 'Missouri');
+
+--- 8 row(s) inserted.
+>>
+>>create view home_teams_games as
++> select t.team_number, g.game_number, g.game_time
++> from "TEAMS" t,
++> "GAMES" g
++> where t.team_number = g.home_team_number
++> order by 1, game_number, game_time;
+
+--- SQL operation complete.
+>>
+>>create table players
++> (player_number int not null,
++> player_name varchar (50) not null,
++> player_team_number int not null,
++> player_phone_number char (10) not null,
++> player_details varchar(50),
++> primary key (player_number, player_team_number))
++> no partition;
+
+--- SQL operation complete.
+>>
+>>insert into players values
++> (1, 'Tom', 1, '4083948394', null),
++> (2, 'Bob', 1, '4089483948', null),
++> (3, 'Toby',1, '4082938493', 'pitcher'),
++> (3, 'Toby',2, '4082938493', null),
++> (4, 'Julie', 2, '5108394839', 'catcher'),
++> (5, 'Joanne', 2, '5103849384', null),
++> (6, 'Pete', 2, '5102839483', null),
++> (6, 'Pete', 3, '5102839483', 'third base'),
++> (7, 'Jared',4, '9518293849', 'short stop'),
++> (8, 'Zachary', 4, '9518293840', null),
++> (9, 'Lynne', 5, '9518293892', 'pitcher'),
++> (10, 'Omar', 5, '5128394893', null);
+
+--- 12 row(s) inserted.
+>>
+>>alter table players add constraint valid_player_number check(player_number > 0);
+
+--- SQL operation complete.
+>>
+>>create view players_on_team as
++> select player_name, team_name
++> from teams t, players p
++> where p.player_team_number = t.team_number
++> order by t.team_name;
+
+--- SQL operation complete.
+>>select * from players_on_team;
+
+PLAYER_NAME TEAM_NAME
+-------------------------------------------------- --------------------
+
+Pete Cardinals
+Julie Giants
+Joanne Giants
+Toby Giants
+Pete Giants
+Jared Indians
+Zachary Indians
+Omar Tigers
+Lynne Tigers
+Toby White Socks
+Bob White Socks
+Tom White Socks
+
+--- 12 row(s) selected.
+>>
+>>create view games_by_player as
++> select player_name, game_time
++> from teams t, games g, players p
++> where p.player_team_number = t.team_number and
++> t.team_number = g.home_team_number
++> order by player_name, team_number;
+
+--- SQL operation complete.
+>>
+>>alter table players add constraint players_teams
++> foreign key (player_team_number) references teams (team_number);
+
+--- SQL operation complete.
+>>
+>>insert into team_statistics
++> select team_number, count (player_number)
++> from teams t, players p
++> where t.team_number = p.player_team_number
++> group by team_number;
+
+--- 5 row(s) inserted.
+>>
+>>create volatile table home_games as
++> select home_team_number, visitor_team_number, game_number, game_location
++> from games
++> where extract(year from games.game_time) =
++> extract(year from current_timestamp) and
++> extract(month from games.game_time) =
++> extract(month from current_timestamp);
+
+--- 1 row(s) inserted.
+>>
+>>create sequence team_number_sequence;
+
+--- SQL operation complete.
+>>
+>>obey TEST133(tests);
+>>-- =================================================================
+>>-- this set of tests run basic initialize authorization tests
+>>-- =================================================================
+>>
+>>set schema "_PRIVMGR_MD_";
+
+--- SQL operation complete.
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENTS
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+ROLE_USAGE
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>
+>>create table t133_t1 (c1 int not null primary key, c2 int);
+
+*** ERROR[1118] Creating object TRAFODION."_PRIVMGR_MD_".T133_T1 is not allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>create view t133_v1 as select * from t133_t1;
+
+*** ERROR[4082] Object TRAFODION."_PRIVMGR_MD_".T133_T1 does not exist or is inaccessible.
+
+--- SQL operation failed with errors.
+>>create index t133_i1 on t133_t1(c2);
+
+*** ERROR[1118] Creating object TRAFODION."_PRIVMGR_MD_".T133_I1 is not allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>create table t133_t2 like t133_t1;
+
+*** ERROR[1118] Creating object TRAFODION."_PRIVMGR_MD_".T133_T2 is not allowed in a reserved system schema.
+
+--- SQL operation failed with errors.
+>>
+>>obey TEST133(set_up);
+>>set schema "_PRIVMGR_MD_";
+
+--- SQL operation complete.
+>>prepare get_owner_privs from
++>select distinct
++> substring (object_name,1,40) as object_name,
++> object_type as type,
++> substring(authname(grantee_id),1,10) as grantee,
++> privileges_bitmap,
++> grantable_bitmap
++>from object_privileges
++>where grantor_id = -2
++> and object_uid in
++> (select object_uid
++> from "_MD_".objects
++> where object_type in ('VI','BT','LB','UR')
++> and schema_name in ('_PRIVMGR_MD_', 'T133SCH'))
++> order by 1, 2, 3, 4, 5
++>;
+
+--- SQL command prepared.
+>>
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENTS
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+ROLE_USAGE
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>execute get_owner_privs;
+
+OBJECT_NAME TYPE GRANTEE PRIVILEGES_BITMAP GRANTABLE_BITMAP
+---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- -------------------- --------------------
+
+TRAFODION."_PRIVMGR_MD_"."COLUMN_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENTS" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_OPER BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_PRIV BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."OBJECT_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."ROLE_USAGE" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."SCHEMA_PRIVILE BT DB__ROOT 47 47
+TRAFODION.T133SCH.GAMES BT DB__ROOT 47 47
+TRAFODION.T133SCH.GAMES_BY_PLAYER VI DB__ROOT 33 33
+TRAFODION.T133SCH.HOME_TEAMS_GAMES VI DB__ROOT 33 33
+TRAFODION.T133SCH.PLAYERS BT DB__ROOT 47 47
+TRAFODION.T133SCH.PLAYERS_ON_TEAM VI DB__ROOT 33 33
+TRAFODION.T133SCH.STANDINGS BT DB__ROOT 47 47
+TRAFODION.T133SCH.TEAMS BT DB__ROOT 47 47
+TRAFODION.T133SCH.TEAM_STATISTICS BT DB__ROOT 47 47
+
+--- 15 row(s) selected.
+>>
+>>initialize authorization, drop;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>initialize authorization;
+
+--- SQL operation complete.
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENTS
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+ROLE_USAGE
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>select count (*) from components;
+
+(EXPR)
+--------------------
+
+ 1
+
+--- 1 row(s) selected.
+>>select count (*) from component_operations;
+
+(EXPR)
+--------------------
+
+ 57
+
+--- 1 row(s) selected.
+>>select count (*) from component_privileges;
+
+(EXPR)
+--------------------
+
+ 116
+
+--- 1 row(s) selected.
+>>execute get_owner_privs;
+
+OBJECT_NAME TYPE GRANTEE PRIVILEGES_BITMAP GRANTABLE_BITMAP
+---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- -------------------- --------------------
+
+TRAFODION."T133SCH"."GAMES" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."GAMES_BY_PLAYER" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."HOME_TEAMS_GAMES" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."PLAYERS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."PLAYERS_ON_TEAM" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."STANDINGS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."TEAMS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."TEAM_STATISTICS" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COLUMN_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENTS" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_OPER BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_PRIV BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."OBJECT_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."ROLE_USAGE" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."SCHEMA_PRIVILE BT DB__ROOT 47 47
+
+--- 15 row(s) selected.
+>>set parserflags 131072;
+
+--- SQL operation complete.
+>>drop table role_usage;
+
+--- SQL operation complete.
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENTS
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>
+>>initialize authorization;
+
+--- SQL operation complete.
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENTS
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+ROLE_USAGE
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>execute get_owner_privs;
+
+OBJECT_NAME TYPE GRANTEE PRIVILEGES_BITMAP GRANTABLE_BITMAP
+---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------- -------------------- --------------------
+
+TRAFODION."T133SCH"."GAMES" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."GAMES_BY_PLAYER" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."HOME_TEAMS_GAMES" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."PLAYERS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."PLAYERS_ON_TEAM" VI DB__ROOT 1 0
+TRAFODION."T133SCH"."STANDINGS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."TEAMS" BT DB__ROOT 47 47
+TRAFODION."T133SCH"."TEAM_STATISTICS" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COLUMN_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENTS" BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_OPER BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."COMPONENT_PRIV BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."OBJECT_PRIVILE BT DB__ROOT 47 47
+TRAFODION."_PRIVMGR_MD_"."SCHEMA_PRIVILE BT DB__ROOT 47 47
+TRAFODION._PRIVMGR_MD_.ROLE_USAGE BT DB__ROOT 47 47
+
+--- 15 row(s) selected.
+>>
+>>drop table components;
+
+--- SQL operation complete.
+>>get tables;
+
+Tables in Schema TRAFODION._PRIVMGR_MD_
+=======================================
+
+COLUMN_PRIVILEGES
+COMPONENT_OPERATIONS
+COMPONENT_PRIVILEGES
+OBJECT_PRIVILEGES
+ROLE_USAGE
+SCHEMA_PRIVILEGES
+
+--- SQL operation complete.
+>>initialize authorization, drop;
+
+--- SQL operation complete.
+>>get tables;
+
+--- SQL operation complete.
+>>
+>>sh sqlci -i "TEST133(authorized)" -u sql_user4;
+>>initialize authorization;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>initialize authorization, drop;
+
+*** ERROR[1017] You are not authorized to perform this operation.
+
+--- SQL operation failed with errors.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>log;