Return-Path: X-Original-To: apmail-trafodion-commits-archive@www.apache.org Delivered-To: apmail-trafodion-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id A71C418B82 for ; Tue, 12 Jan 2016 22:47:09 +0000 (UTC) Received: (qmail 59754 invoked by uid 500); 12 Jan 2016 22:47:09 -0000 Delivered-To: apmail-trafodion-commits-archive@trafodion.apache.org Received: (qmail 59721 invoked by uid 500); 12 Jan 2016 22:47:09 -0000 Mailing-List: contact commits-help@trafodion.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: commits@trafodion.apache.org Delivered-To: mailing list commits@trafodion.apache.org Received: (qmail 59712 invoked by uid 99); 12 Jan 2016 22:47:09 -0000 Received: from Unknown (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Jan 2016 22:47:09 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 07F10C0B62 for ; Tue, 12 Jan 2016 22:47:09 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.779 X-Spam-Level: * X-Spam-Status: No, score=1.779 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-0.001] autolearn=disabled Received: from mx1-us-east.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id VLZ6Olp1JZCu for ; Tue, 12 Jan 2016 22:47:03 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-us-east.apache.org (ASF Mail Server at mx1-us-east.apache.org) with SMTP id A5BEF43ED4 for ; Tue, 12 Jan 2016 22:47:02 +0000 (UTC) Received: (qmail 59447 invoked by uid 99); 12 Jan 2016 22:47:02 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Jan 2016 22:47:02 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 0E89EE3814; Tue, 12 Jan 2016 22:47:02 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: rmarton@apache.org To: commits@trafodion.incubator.apache.org Date: Tue, 12 Jan 2016 22:47:09 -0000 Message-Id: In-Reply-To: <824efa2853a94e4cbc5bf13124e72fbe@git.apache.org> References: <824efa2853a94e4cbc5bf13124e72fbe@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [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;