| >>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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>-- succeed: DB__ROOT can create a library |
| >>obey TEST132(manage_library); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>drop library t132_l1; |
| |
| --- SQL operation complete. |
| >>get libraries in schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- fail: sql_user1 cannot create a library |
| >>changeuser sql_user1; |
| >>obey TEST132(manage_library); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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. |
| >> |
| >>changeuser db__root; |
| >> |
| >>-- succeed: grant DB__ROOTROLE to sql_user1 |
| >>grant role DB__ROOTROLE to sql_user1; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user1; |
| >>obey TEST132(manage_library); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>drop library t132_l1; |
| |
| --- SQL operation complete. |
| >>get libraries in schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser DB__ROOT; |
| >> |
| >>-- fail: just grant the create privilege |
| >>grant component privilege CREATE_LIBRARY on sql_operations to sql_user2; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user2; |
| >>obey TEST132(manage_library); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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. |
| >> |
| >>changeuser DB__ROOT; |
| >> |
| >>-- 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. |
| >>changeuser sql_user2; |
| >>obey TEST132(manage_library); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>drop library t132_l1; |
| |
| --- SQL operation complete. |
| >>get libraries in schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser DB__ROOT; |
| >> |
| >>-- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>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. |
| >>--set parserflags 131072; |
| >>--cqd DDL_TRANSACTIONS 'ON'; |
| >> |
| >>get tables, match '%T132%'; |
| |
| --- 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. |
| >> |
| >>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, match '%T132%'; |
| |
| Tables in Schema TRAFODION.T132SCH |
| ================================== |
| |
| T132T1 |
| T132T2 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- 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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- 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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T2 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- DB__ROOT can populate indexes |
| >>obey TEST132(populate_index); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>populate index t132t1_ndx1 on t132t1; |
| |
| --- SQL operation complete. |
| >>populate index t132t2_ndx1 on t132t2; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST132(popindex_check_reset); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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. |
| >> |
| >>cleanup 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. |
| >> |
| >> |
| >>-- object owner can populate |
| >>-- sql_user1 owns t132t2 but not t132t1 |
| >>-- popindex fails for t132t1 but works for the rest |
| >>changeuser sql_user1; |
| >>obey TEST132(populate_index); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>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. |
| >> |
| >>changeuser db__root; |
| >>obey TEST132(popindex_check_reset); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >> |
| >>select count(*) from table (index_table t132t1_ndx1); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table (index_table t132t2_ndx1); |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >> |
| >>cleanup 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. |
| >> |
| >> |
| >>-- if user belongs to DB__ROOTROLE, has DML privileges, so can populate indexes |
| >>grant role DB__ROOTROLE to sql_user2; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user2; |
| >>obey TEST132(populate_index); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>populate index t132t1_ndx1 on t132t1; |
| |
| --- SQL operation complete. |
| >>populate index t132t2_ndx1 on t132t2; |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser db__root; |
| >>obey TEST132(popindex_check_reset); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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. |
| >> |
| >>cleanup 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. |
| >> |
| >>revoke role DB__ROOTROLE from sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- sql_user3 requires both SELECT and INSERT privileges |
| >>-- only t132t2 has granted both privileges |
| >>grant SELECT on t132t1 to sql_user3; |
| |
| --- SQL operation complete. |
| >>grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user3; |
| >>obey TEST132(populate_index); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>populate index t132t1_ndx1 on 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. |
| >> |
| >>changeuser db__root; |
| >>obey TEST132(popindex_check_reset); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >> |
| >>select count(*) from table (index_table t132t1_ndx1); |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from table (index_table t132t2_ndx1); |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >> |
| >>cleanup 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. |
| >> |
| >> |
| >>-- reset |
| >>drop table t132t1 cascade; |
| |
| --- SQL operation complete. |
| >>drop table t132t2 cascade; |
| |
| --- SQL operation complete. |
| >>get tables, match 'T132%'; |
| |
| --- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- 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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>-- DB__ROOT has all privileges |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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 Sun Oct 22 16:08:56 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_teams; |
| |
| -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS |
| -- Definition current Sun Oct 22 16:08:59 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_giants_games; |
| |
| -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES |
| -- Definition current Sun Oct 22 16:09:02 2017 |
| |
| ( |
| 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 Sun Oct 22 16:09:05 2017 |
| |
| ( |
| 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 |
| >>changeuser sql_user1; |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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 Sun Oct 22 16:09:18 2017 |
| |
| ( |
| 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) |
| |
| --- 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. |
| >> |
| >> |
| >>-- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege |
| >>-- first illustrate that sql_user2 has no privileges |
| >>changeuser sql_user2; |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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. |
| >> |
| >>changeuser db__root; |
| >>grant role DB__ROOTROLE to sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- now sql_user2 has privileges with the grant |
| >>changeuser sql_user2; |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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 Sun Oct 22 16:09:50 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_teams; |
| |
| -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS |
| -- Definition current Sun Oct 22 16:09:50 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_giants_games; |
| |
| -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES |
| -- Definition current Sun Oct 22 16:09:50 2017 |
| |
| ( |
| 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 Sun Oct 22 16:09:50 2017 |
| |
| ( |
| 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. |
| >> |
| >>changeuser db__root; |
| >>revoke role DB__ROOTROLE from sql_user2; |
| |
| --- SQL operation complete. |
| >> |
| >>-- sql_user3 gets some privileges through SELECT grant |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>grant SELECT on t132_teams to sql_user3; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user3; |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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 Sun Oct 22 16:10:13 2017 |
| |
| ( |
| 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) |
| |
| --- 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. |
| >> |
| >>changeuser db__root; |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>revoke select on t132_teams from sql_user3; |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>changeuser sql_user1; |
| >>obey TEST132(show_objects); |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| 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 Sun Oct 22 16:10:48 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_teams; |
| |
| -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS |
| -- Definition current Sun Oct 22 16:10:48 2017 |
| |
| ( |
| 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) |
| |
| --- SQL operation complete. |
| >>invoke t132_giants_games; |
| |
| -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES |
| -- Definition current Sun Oct 22 16:10:48 2017 |
| |
| ( |
| 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 Sun Oct 22 16:10:49 2017 |
| |
| ( |
| 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. |
| >> |
| >> |
| >>changeuser db__root; |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>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 SELECT privilege |
| >>-- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv) |
| >>-- ================================================================= |
| >> |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>get tables, match '%T132%'; |
| |
| --- 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, match '%T132%'; |
| |
| Tables in Schema TRAFODION.T132SCH |
| ================================== |
| |
| T132T1 |
| T132T2 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- 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 |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>update statistics for table t132t1 on every column; |
| |
| --- SQL operation complete. |
| >>update statistics for table t132t2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>-- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default |
| >>-- first show that sql_user2 cannot perform operations |
| >>changeuser sql_user2; |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>update statistics for table t132t1 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >>update statistics for table t132t2 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >> |
| >>changeuser db__root; |
| >> |
| >>-- now show privileges after being granted DB__ROOTROLE role |
| >>grant role DB__ROOTROLE to sql_user2; |
| |
| --- SQL operation complete. |
| >>changeuser sql_user2; |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>update statistics for table t132t1 on every column; |
| |
| --- SQL operation complete. |
| >>update statistics for table t132t2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser DB__ROOT; |
| >>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 |
| >>changeuser sql_user1; |
| >>obey TEST132(show_update_stats); |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>update statistics for table t132t1 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >>update statistics for table t132t2 on every column; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST132(show_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table t132t1 on every column; |
| |
| Histogram data for Table TRAFODION.T132SCH.T132T1 |
| Table ID: 3703791059232936033 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 1800623295 8 8 8 SYSKEY |
| 1800623288 8 8 8 C1 |
| 1800623285 8 8 8 C2 |
| |
| |
| --- SQL operation complete. |
| >>showstats for table t132t2 on every column; |
| |
| Histogram data for Table TRAFODION.T132SCH.T132T2 |
| Table ID: 3703791059232936201 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 1824908698 8 8 8 SYSKEY |
| 1824908693 8 8 8 C1 |
| 1824908688 8 8 8 C2 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser DB__ROOT; |
| >> |
| >>-- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- 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 |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- 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. |
| >>changeuser sql_user3; |
| >>obey TEST132(show_update_stats); |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>update statistics for table t132t1 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >>update statistics for table t132t2 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >> |
| >>obey TEST132(show_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table t132t1 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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 or MANAGE_STATISTICS 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. |
| >> |
| >>changeuser db__root; |
| >> |
| >>-- now show privileges after being granted MANAGE_STATISTICS |
| >>grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user3; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER3 |
| =============================================================== |
| |
| MANAGE_STATISTICS |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>changeuser sql_user3; |
| >>obey TEST132(show_update_stats); |
| >>obey TEST132(update_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>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. |
| >> |
| >>showstats for table t132t1 on every column; |
| |
| Histogram data for Table TRAFODION.T132SCH.T132T1 |
| Table ID: 3703791059232936033 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 1800623294 8 8 8 SYSKEY |
| 1800623289 8 8 8 C1 |
| 1800623284 8 8 8 C2 |
| |
| |
| --- SQL operation complete. |
| >>showstats for table t132t2 on every column; |
| |
| Histogram data for Table TRAFODION.T132SCH.T132T2 |
| Table ID: 3703791059232936201 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 1824908699 8 8 8 SYSKEY |
| 1824908692 8 8 8 C1 |
| 1824908689 8 8 8 C2 |
| |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser db__root; |
| >>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 |
| >>changeuser sql_user3; |
| >>obey TEST132(show_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table t132t1 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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 or MANAGE_STATISTICS 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. |
| >>changeuser db__root; |
| >> |
| >>-- grant select to allow showstats to work |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >>grant SELECT on t132t1 to sql_user4; |
| |
| --- SQL operation complete. |
| >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; |
| |
| --- SQL operation complete. |
| >>showddl t132t1; |
| |
| CREATE TABLE TRAFODION.T132SCH.T132T1 |
| ( |
| C1 INT DEFAULT NULL |
| , C2 INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- 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. |
| >>changeuser sql_user4; |
| >>obey TEST132(show_stats); |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>showstats for table t132t1 on every column; |
| |
| Histogram data for Table TRAFODION.T132SCH.T132T1 |
| Table ID: 3703791059232936033 |
| |
| Hist ID # Ints Rowcount UEC Colname(s) |
| ========== ====== =========== =========== =========================== |
| 1800623294 8 8 8 SYSKEY |
| 1800623289 8 8 8 C1 |
| 1800623284 8 8 8 C2 |
| |
| |
| --- SQL operation complete. |
| >>showstats for table t132t2 on every column; |
| |
| *** ERROR[4481] The user does not have SELECT or MANAGE_STATISTICS 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. |
| >>changeuser db__root; |
| >> |
| >>-- testcase for trafodion-2188 fix |
| >>create schema t132sch_private; |
| |
| --- SQL operation complete. |
| >>set schema t132sch_private; |
| |
| --- SQL operation complete. |
| >>CREATE TABLE t132t3 |
| +>( c1 char(12) not null, c2 char(12) not null, c3 char(12) not null, |
| +> PRIMARY KEY (C1 ASC)) |
| +>SALT USING 4 PARTITIONS |
| +>ON (C1); |
| |
| --- SQL operation complete. |
| >> |
| >>upsert using load into t132t3 |
| +>select |
| +> x1 || x2 || x3 || x4 || x5, |
| +> x2 || x4 || x1, |
| +> x5 || x3 |
| +>-- the from clause below creates 100,000 rows, the cross product of |
| +>-- 5 copies of { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' } |
| +> from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) T(x1) |
| +>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x2 |
| +>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x3 |
| +>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x4 |
| +>transpose 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' as x5; |
| |
| --- 100000 row(s) inserted. |
| >> |
| >>select count(*) from t132t3; |
| |
| (EXPR) |
| -------------------- |
| |
| 100000 |
| |
| --- 1 row(s) selected. |
| >> |
| >>grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3; |
| |
| --- SQL operation complete. |
| >>get privileges on component sql_operations for sql_user3; |
| |
| Privilege information on Component SQL_OPERATIONS for SQL_USER3 |
| =============================================================== |
| |
| MANAGE_STATISTICS |
| |
| ======================= |
| 1 row(s) returned |
| |
| --- SQL operation complete. |
| >>changeuser sql_user3; |
| >>obey TEST132(update_stats1); |
| >>set schema t132sch_private; |
| |
| --- SQL operation complete. |
| >>update statistics for table t132t3 create sample random 10 percent; |
| |
| --- SQL operation complete. |
| >> |
| >>changeuser db__root; |
| >>set schema t132sch_private; |
| |
| --- SQL operation complete. |
| >>revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; |
| |
| --- SQL operation complete. |
| >>cleanup schema t132sch_private; |
| |
| --- SQL operation complete. |
| >>set schema t132sch; |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t132t1; |
| |
| --- SQL operation complete. |
| >>drop table t132t2; |
| |
| --- SQL operation complete. |
| >> |
| >>get tables, match 'T132%'; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
