| >>obey TEST139(set_up); |
| >>set schema "_PRIVMGR_MD_"; |
| |
| --- SQL operation complete. |
| >>prepare get_owner_privs from |
| +>select distinct |
| +> substring (object_name,1,10) 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_', 'T139SCH')) |
| +> order by 1, 2 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare object_privs from |
| +>select distinct |
| +> object_type as type, |
| +> substring (grantor_name,1,10) as grantor, |
| +> substring (grantee_name,1,10) as grantee, |
| +> privileges_bitmap as privs, |
| +> grantable_bitmap as wgo |
| +>from "_PRIVMGR_MD_".object_privileges |
| +>where object_name like ?tblname |
| +>order by 1, 2, 3; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare all_privs from |
| +>select distinct |
| +> object_type as type, |
| +> substring (object_name,1,20) as objname, |
| +> substring (grantor_name,1,10) as grantor, |
| +> substring (grantee_name,1,10) as grantee, |
| +> privileges_bitmap as privs, |
| +> grantable_bitmap as wgo |
| +>from "_PRIVMGR_MD_".object_privileges |
| +>where object_uid in |
| +> (select object_uid from "_MD_".objects |
| +> where schema_name in ('T139SCH')) |
| +>ORDER BY 1, 2,3,4; |
| |
| --- SQL command prepared. |
| >> |
| >>obey TEST139(create_db); |
| >>create shared schema t139sch; |
| |
| --- SQL operation complete. |
| >>set schema t139sch; |
| |
| --- 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. |
| >> |
| >>obey TEST139(tests); |
| >>-- ================================================================= |
| >>-- this set of tests run basic grant and revoke tests |
| >>-- ================================================================= |
| >>set schema t139sch; |
| |
| --- SQL operation complete. |
| >> |
| >>set param ?tblname '%GAMES'; |
| >> |
| >>-- test SELECT, UPDATE, DELETE, INSERT on games |
| >>grant select on games to sql_user1; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT ON TRAFODION.T139SCH.GAMES TO SQL_USER1; |
| |
| --- SQL operation complete. |
| >>-- make sure user1 can select but not other operations |
| >>sh sqlci -i "TEST139(user1_dml)" -u sql_user1; |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>update games set game_location = 'Ohio' where game_location = 'New York'; |
| |
| *** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>update games set game_location = 'New York' where game_location = 'Ohio'; |
| |
| *** ERROR[4481] The user does not have UPDATE privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>insert into games values (4, 5, 9, current_timestamp, 'Ohio'); |
| |
| *** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>delete from games where game_number = 9; |
| |
| *** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>grant update on games to sql_user1; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, UPDATE ON TRAFODION.T139SCH.GAMES TO SQL_USER1; |
| |
| --- SQL operation complete. |
| >>-- make sure user 1 can select and update but not other operations |
| >>sh sqlci -i "TEST139(user1_dml)" -u sql_user1; |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>update games set game_location = 'Ohio' where game_location = 'New York'; |
| |
| --- 1 row(s) updated. |
| >>update games set game_location = 'New York' where game_location = 'Ohio'; |
| |
| --- 1 row(s) updated. |
| >>insert into games values (4, 5, 9, current_timestamp, 'Ohio'); |
| |
| *** ERROR[4481] The user does not have INSERT privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>delete from games where game_number = 9; |
| |
| *** ERROR[4481] The user does not have DELETE privilege on table or view TRAFODION.T139SCH.GAMES. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>grant delete, insert, references on games to sql_user1; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES |
| TO SQL_USER1; |
| |
| --- SQL operation complete. |
| >>-- make sure user1 can do all dml |
| >>sh sqlci -i "TEST139(user1_dml)" -u sql_user1; |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>update games set game_location = 'Ohio' where game_location = 'New York'; |
| |
| --- 1 row(s) updated. |
| >>update games set game_location = 'New York' where game_location = 'Ohio'; |
| |
| --- 1 row(s) updated. |
| >>insert into games values (4, 5, 9, current_timestamp, 'Ohio'); |
| |
| --- 1 row(s) inserted. |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 9 |
| |
| --- 1 row(s) selected. |
| >>delete from games where game_number = 9; |
| |
| --- 1 row(s) deleted. |
| >>select count(*) from games; |
| |
| (EXPR) |
| -------------------- |
| |
| 8 |
| |
| --- 1 row(s) selected. |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>execute object_privs; |
| |
| TYPE GRANTOR GRANTEE PRIVS WGO |
| ---- ---------------------------------------- ---------------------------------------- -------------------- -------------------- |
| |
| BT DB__ROOT SQL_USER1 47 0 |
| BT _SYSTEM DB__ROOT 47 47 |
| VI _SYSTEM DB__ROOT 33 33 |
| |
| --- 3 row(s) selected. |
| >>-- this query does not always return the correct rows |
| >>-- execute all_privs; |
| >> |
| >>-- test WITH GRANT OPTION |
| >>--grant select, insert, delete on teams to sql_user2 with grant option; |
| >>--showddl teams; |
| >>-- make sure user2 grant of select, insert, delete to user2 succeeds |
| >>-- make sure user2 grant of update, references fails |
| >>-- user2 will grant select to user3 |
| >>--sh sqlci -i "TEST139(user2_dml)" -u sql_user2; |
| >>-- make sure user3 can select |
| >>-- user3 will grant select to user4 |
| >>--sh sqlci -i "TEST139(user3_dml)" -u sql_user3; |
| >>--grant update on teams to sql_user2 with grant option; |
| >>--showddl teams; |
| >>-- make sure user2 can grant update privilege |
| >>--sh sqlci -i "TEST139(user2_dml)" -u sql_user2; |
| >>-- make sure user3 can update |
| >>--sh sqlci -i "TEST139(user3_dml)" -u sql_user3; |
| >>-- make sure user4 can select, but not other privs |
| >>--sh sqlci -i "TEST139(user4_dml)" -u sql_user4; |
| >> |
| >>-- test revoke SELECT, UPDATE, DELETE, INSERT, REFERENCES |
| >> |
| >>revoke update on games from sql_user1; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, REFERENCES ON TRAFODION.T139SCH.GAMES TO |
| SQL_USER1; |
| |
| --- SQL operation complete. |
| >>revoke all_dml on games from sql_user2; |
| |
| *** WARNING[1018] Grant of role or privilege ALL_DML from DB__ROOT to SQL_USER2 not found, revoke request ignored. |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT, INSERT, DELETE, REFERENCES ON TRAFODION.T139SCH.GAMES TO |
| SQL_USER1; |
| |
| --- SQL operation complete. |
| >>revoke all_dml on teams from sql_user3; |
| |
| *** WARNING[1018] Grant of role or privilege ALL_DML from DB__ROOT to SQL_USER3 not found, revoke request ignored. |
| |
| --- SQL operation complete. |
| >>showddl teams; |
| |
| CREATE TABLE TRAFODION.T139SCH.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.T139SCH.TEAMS ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_TEAM_NO CHECK (TRAFODION.T139SCH.TEAMS.TEAM_NUMBER > |
| 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.TEAMS TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- test for JIRA 2177 |
| >>create role role1; |
| |
| --- SQL operation complete. |
| >>grant role role1 to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant insert(team_contact,team_contact_number), select on teams to role1; |
| |
| --- SQL operation complete. |
| >>showddl teams; |
| |
| CREATE TABLE TRAFODION.T139SCH.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.T139SCH.TEAMS ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_TEAM_NO CHECK (TRAFODION.T139SCH.TEAMS.TEAM_NUMBER > |
| 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.TEAMS TO DB__ROOT WITH GRANT OPTION; |
| GRANT SELECT ON TRAFODION.T139SCH.TEAMS TO ROLE1; |
| GRANT INSERT(TEAM_CONTACT, |
| TEAM_CONTACT_NUMBER) ON TRAFODION.T139SCH.TEAMS TO ROLE1; |
| |
| --- SQL operation complete. |
| >>revoke insert(team_contact,team_contact_number), select on teams from role1; |
| |
| --- SQL operation complete. |
| >>showddl teams; |
| |
| CREATE TABLE TRAFODION.T139SCH.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.T139SCH.TEAMS ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_TEAM_NO CHECK (TRAFODION.T139SCH.TEAMS.TEAM_NUMBER > |
| 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.TEAMS TO DB__ROOT WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- test for JIRA 2196/2197 |
| >>grant select,insert ,delete, update ,references(game_time) on games to sql_user1 with grant option; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT REFERENCES ON TRAFODION.T139SCH.GAMES TO SQL_USER1; |
| GRANT SELECT, |
| INSERT, DELETE, UPDATE ON TRAFODION.T139SCH.GAMES TO SQL_USER1 WITH GRANT |
| OPTION; |
| GRANT REFERENCES(GAME_TIME) ON TRAFODION.T139SCH.GAMES TO SQL_USER1 |
| WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST139(user1_grants)" -u sql_user1; |
| >>grant select(home_team_number,game_time), |
| +> insert(home_team_number), |
| +> update(visitor_team_number,game_time), |
| +> references(game_time) |
| +>on games to role1; |
| |
| --- SQL operation complete. |
| >>showddl games; |
| |
| CREATE TABLE TRAFODION.T139SCH.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 |
| ; |
| |
| ALTER TABLE TRAFODION.T139SCH.GAMES ADD CONSTRAINT |
| TRAFODION.T139SCH.VALID_GAME_NUMBER CHECK |
| (TRAFODION.T139SCH.GAMES.GAME_NUMBER > 0) |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T139SCH.GAMES TO DB__ROOT WITH GRANT OPTION; |
| GRANT REFERENCES ON TRAFODION.T139SCH.GAMES TO SQL_USER1; |
| GRANT SELECT, |
| INSERT, DELETE, UPDATE ON TRAFODION.T139SCH.GAMES TO SQL_USER1 WITH GRANT |
| OPTION; |
| GRANT REFERENCES(GAME_TIME) ON TRAFODION.T139SCH.GAMES TO SQL_USER1 |
| WITH GRANT OPTION; |
| GRANT SELECT(HOME_TEAM_NUMBER, GAME_TIME), |
| INSERT(HOME_TEAM_NUMBER), UPDATE(VISITOR_TEAM_NUMBER, GAME_TIME), |
| REFERENCES(GAME_TIME) ON TRAFODION.T139SCH.GAMES TO ROLE1 GRANTED BY |
| SQL_USER1; |
| |
| --- SQL operation complete. |
| >>revoke select(home_team_number,game_time), |
| +> insert(home_team_number), |
| +> update(visitor_team_number,game_time), |
| +> references(game_time) |
| +>on games from role1; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>revoke select,insert ,delete, update ,references(game_time) on games |
| +> from sql_user1; |
| |
| --- SQL operation complete. |
| >>revoke role role1 from sql_user1; |
| |
| --- SQL operation complete. |
| >>drop role role1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- sh sqlci -i "TEST139(authorized)" -u sql_user4; |
| >>log; |
