| >>obey TEST142(create_db); |
| >>create schema t142user1 authorization sql_user1; |
| |
| --- SQL operation complete. |
| >>create schema t142user2 authorization sql_user2; |
| |
| --- SQL operation complete. |
| >>create schema t142user3 authorization sql_user3; |
| |
| --- SQL operation complete. |
| >>create schema t142user4 authorization sql_user4; |
| |
| --- SQL operation complete. |
| >>create schema t142user5 authorization sql_user5; |
| |
| --- SQL operation complete. |
| >>create schema t142user6 authorization sql_user6; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user4; |
| |
| --- SQL operation complete. |
| >>create table referencedTable |
| +>( c1 int not null primary key, |
| +> c2 int not null, |
| +> c3 char(10) not null, |
| +> c4 char(10) not null, |
| +> c5 largeint not null ); |
| |
| --- SQL operation complete. |
| >>alter table referencedTable add constraint u1 unique (c3, c2); |
| |
| --- SQL operation complete. |
| >>alter table referencedTable add constraint u2 unique (c4); |
| |
| --- SQL operation complete. |
| >>alter table referencedTable add constraint u3 unique(c5); |
| |
| --- SQL operation complete. |
| >>showddl referencedTable; |
| |
| CREATE TABLE TRAFODION.T142USER4.REFERENCEDTABLE |
| ( |
| C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C4 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , C5 LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (C1 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX U1 ON TRAFODION.T142USER4.REFERENCEDTABLE |
| ( |
| C3 ASC |
| , C2 ASC |
| ) |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX U2 ON TRAFODION.T142USER4.REFERENCEDTABLE |
| ( |
| C4 ASC |
| ) |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX U3 ON TRAFODION.T142USER4.REFERENCEDTABLE |
| ( |
| C5 ASC |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT |
| TRAFODION.T142USER4.U1 UNIQUE |
| ( |
| C3 |
| , C2 |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT |
| TRAFODION.T142USER4.U2 UNIQUE |
| ( |
| C4 |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T142USER4.REFERENCEDTABLE ADD CONSTRAINT |
| TRAFODION.T142USER4.U3 UNIQUE |
| ( |
| C5 |
| ) |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER4.REFERENCEDTABLE TO SQL_USER4 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>-- compile cpp program for function |
| >>sh rm -f ./etest140.dll; |
| >>sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp |
| +> 2>&1 | tee LOG140-SECONDARY; |
| >>set pattern $$DLL$$ etest140.dll; |
| >>set pattern $$QUOTE$$ ''''; |
| >> |
| >>-- create the library and udf |
| >>create library t142_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ; |
| |
| --- SQL operation complete. |
| >>create function t142_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20)) |
| +>language c parameter style sql external name 'translateBitmap' |
| +>library t142_l1 |
| +>deterministic no sql final call allow any parallelism state area size 1024 ; |
| |
| --- SQL operation complete. |
| >>grant execute on function t142_translatePrivsBitmap to "PUBLIC"; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user1; |
| |
| --- SQL operation complete. |
| >>create table user1 |
| +>(u1_c1 int not null, |
| +> u1_c2 int not null primary key, |
| +> u1_c3 char(10) not null, |
| +> u1_c4 char(10)); |
| |
| --- SQL operation complete. |
| >>showddl user1; |
| |
| CREATE TABLE TRAFODION.T142USER1.USER1 |
| ( |
| U1_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U1_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U1_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U1_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , PRIMARY KEY (U1_C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER1.USER1 TO SQL_USER1 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user2; |
| |
| --- SQL operation complete. |
| >>create table user2 |
| +>(u2_c1 int not null, |
| +> u2_c2 int not null primary key, |
| +> u2_c3 char(10) not null, |
| +> u2_c4 largeint); |
| |
| --- SQL operation complete. |
| >>showddl user2; |
| |
| CREATE TABLE TRAFODION.T142USER2.USER2 |
| ( |
| U2_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U2_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U2_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U2_C4 LARGEINT DEFAULT NULL |
| , PRIMARY KEY (U2_C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER2.USER2 TO SQL_USER2 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user3; |
| |
| --- SQL operation complete. |
| >>create table user3 |
| +>(u3_c1 int not null, |
| +> u3_c2 int not null primary key, |
| +> u3_c3 char(10) not null, |
| +> u3_c4 char(10)); |
| |
| --- SQL operation complete. |
| >>showddl user3; |
| |
| CREATE TABLE TRAFODION.T142USER3.USER3 |
| ( |
| U3_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U3_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U3_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U3_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , PRIMARY KEY (U3_C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER3.USER3 TO SQL_USER3 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user5; |
| |
| --- SQL operation complete. |
| >>create table user5 |
| +>(u5_c1 int not null, |
| +> u5_c2 int not null primary key, |
| +> u5_c3 char(10) not null, |
| +> u5_c4 char(10)); |
| |
| --- SQL operation complete. |
| >>showddl user5; |
| |
| CREATE TABLE TRAFODION.T142USER5.USER5 |
| ( |
| U5_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U5_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U5_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U5_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , PRIMARY KEY (U5_C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER5.USER5 TO SQL_USER5 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>set schema t142user6; |
| |
| --- SQL operation complete. |
| >>create table user6 |
| +>(u6_c1 int not null, |
| +> u6_c2 int not null primary key, |
| +> u6_c3 char(10) not null, |
| +> u6_c4 char(10)); |
| |
| --- SQL operation complete. |
| >>showddl user6; |
| |
| CREATE TABLE TRAFODION.T142USER6.USER6 |
| ( |
| U6_C1 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U6_C2 INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U6_C3 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE |
| , U6_C4 CHAR(10) CHARACTER SET ISO88591 COLLATE |
| DEFAULT DEFAULT NULL |
| , PRIMARY KEY (U6_C2 ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T142USER6.USER6 TO SQL_USER6 WITH GRANT OPTION; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST142(set_up); |
| >>set schema "_PRIVMGR_MD_"; |
| |
| --- SQL operation complete. |
| >>prepare get_privs from |
| +>select distinct |
| +> trim(substring (o.object_name,1,20)) as object_name, |
| +> grantor_id, grantee_id, 'all', |
| +> t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs, |
| +> t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs |
| +>from object_privileges p, "_MD_".objects o |
| +>where p.object_uid in |
| +> (select object_uid |
| +> from "_MD_".objects |
| +> where schema_name like 'T142USER%' |
| +> and object_name not like 'SB_%') |
| +> and p.object_uid = o.object_uid |
| +>union |
| +> (select distinct |
| +> trim(substring (o.object_name,1,20)) as object_name, |
| +> grantor_id, grantee_id, cast (column_number as char(3)), |
| +> t142user4.t142_translatePrivsBitmap(privileges_bitmap) as granted_privs, |
| +> t142user4.t142_translatePrivsBitmap(grantable_bitmap) as grantable_privs |
| +> from column_privileges p, "_MD_".objects o |
| +> where p.object_uid in |
| +> (select object_uid |
| +> from "_MD_".objects |
| +> where schema_name like 'T142USER%' |
| +> and object_name not like 'SB_%') |
| +> and p.object_uid = o.object_uid) |
| +>order by 1, 2, 3, 4 |
| +>; |
| |
| --- SQL command prepared. |
| >> |
| >>obey TEST142(test_grants); |
| >>-- ================================================================= |
| >>-- this set of tests run basic grant tests for constraints |
| >>-- schema t142user4 contains the referenced table |
| >>-- ================================================================= |
| >>set schema t142user4; |
| |
| --- SQL operation complete. |
| >> |
| >>-- all should fail, no one has permissions |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS |
| -------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- -------------------- |
| |
| REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R- |
| T142_L1 -2 33337 all ---UG-- ---UG-- |
| T142_TRANSLATEPRIVSB -2 33337 all ------E ------E |
| T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE |
| USER1 -2 33334 all SIDU-R- SIDU-R- |
| USER2 -2 33335 all SIDU-R- SIDU-R- |
| USER3 -2 33336 all SIDU-R- SIDU-R- |
| USER5 -2 33338 all SIDU-R- SIDU-R- |
| USER6 -2 33339 all SIDU-R- SIDU-R- |
| |
| --- 9 row(s) selected. |
| >>sh sqlci -i "TEST142(user1_cmds)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user1; |
| |
| --- SQL operation complete. |
| >>alter table user1 add constraint u1_fk1 foreign key (u1_c4, u1_c1) |
| +> references t142user4.referencedTable (c3, c2); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >>alter table user1 add constraint u1_fk2 foreign key (u1_c2) |
| +> references t142user4.referencedTable(c1); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user2; |
| |
| --- SQL operation complete. |
| >>alter table user2 add constraint u2_fk1 foreign key (u2_c3) |
| +> references t142user4.referencedTable (c4); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >>alter table user2 add constraint u2_fk2 foreign key (u2_c4) |
| +> references t142user4.referencedTable(c5); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user3_cmds)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user3; |
| |
| --- SQL operation complete. |
| >>alter table user3 add constraint u3_fk1 foreign key (u3_c1) |
| +> references t142user4.referencedTable; |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user5_cmds)" -u sql_user5; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER5 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user5; |
| |
| --- SQL operation complete. |
| >>alter table user5 add constraint u5_fk1 foreign key (u5_c1) |
| +> references t142user4.referencedTable; |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user6_cmds)" -u sql_user6; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER6 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user6; |
| |
| --- SQL operation complete. |
| >>alter table user6 add constraint u6_fk1 foreign key (u6_c3) |
| +> references t142user4.referencedTable(c4); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >>-- this case always fails |
| >>alter table user6 add constraint u6_fk2 foreign key (u6_c1) |
| +> references t142user4.referencedTable(c2); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user1, user3, & user5 can create constraints |
| >>grant references on referencedTable to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant references(c2, c3) on referencedTable to sql_user1; |
| |
| --- SQL operation complete. |
| >>grant all_dml on referencedTable to sql_user3 with grant option; |
| |
| --- SQL operation complete. |
| >>grant references on referencedTable to sql_user5 by sql_user3; |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS |
| -------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- -------------------- |
| |
| REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R- |
| REFERENCEDTABLE 33336 33338 all -----R- NONE |
| REFERENCEDTABLE 33337 33334 1 -----R- NONE |
| REFERENCEDTABLE 33337 33334 2 -----R- NONE |
| REFERENCEDTABLE 33337 33334 all -----R- NONE |
| REFERENCEDTABLE 33337 33336 all SIDU-R- SIDU-R- |
| T142_L1 -2 33337 all ---UG-- ---UG-- |
| T142_TRANSLATEPRIVSB -2 33337 all ------E ------E |
| T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE |
| USER1 -2 33334 all SIDU-R- SIDU-R- |
| USER2 -2 33335 all SIDU-R- SIDU-R- |
| USER3 -2 33336 all SIDU-R- SIDU-R- |
| USER5 -2 33338 all SIDU-R- SIDU-R- |
| USER6 -2 33339 all SIDU-R- SIDU-R- |
| |
| --- 14 row(s) selected. |
| >> |
| >>sh sqlci -i "TEST142(user1_cmds)" -u sql_user1; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER1 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user1; |
| |
| --- SQL operation complete. |
| >>alter table user1 add constraint u1_fk1 foreign key (u1_c4, u1_c1) |
| +> references t142user4.referencedTable (c3, c2); |
| |
| --- SQL operation complete. |
| >>alter table user1 add constraint u1_fk2 foreign key (u1_c2) |
| +> references t142user4.referencedTable(c1); |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user3_cmds)" -u sql_user3; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER3 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user3; |
| |
| --- SQL operation complete. |
| >>alter table user3 add constraint u3_fk1 foreign key (u3_c1) |
| +> references t142user4.referencedTable; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>sh sqlci -i "TEST142(user5_cmds)" -u sql_user5; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER5 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user5; |
| |
| --- SQL operation complete. |
| >>alter table user5 add constraint u5_fk1 foreign key (u5_c1) |
| +> references t142user4.referencedTable; |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- first time, user2 can only create one, second time it works |
| >>grant references (c5) on referencedTable to sql_user2; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user2; |
| |
| --- SQL operation complete. |
| >>alter table user2 add constraint u2_fk1 foreign key (u2_c3) |
| +> references t142user4.referencedTable (c4); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >>alter table user2 add constraint u2_fk2 foreign key (u2_c4) |
| +> references t142user4.referencedTable(c5); |
| |
| --- SQL operation complete. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >>grant references (c4) on referencedTable to sql_user2 with grant option; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST142(user2_cmds)" -u sql_user2; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER2 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user2; |
| |
| --- SQL operation complete. |
| >>alter table user2 add constraint u2_fk1 foreign key (u2_c3) |
| +> references t142user4.referencedTable (c4); |
| |
| --- SQL operation complete. |
| >>alter table user2 add constraint u2_fk2 foreign key (u2_c4) |
| +> references t142user4.referencedTable(c5); |
| |
| *** ERROR[1043] Constraint TRAFODION.T142USER2.U2_FK2 already exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>-- user6 can create first but not second constraint |
| >>grant references (c4) on referencedTable to sql_user6 by sql_user2; |
| |
| --- SQL operation complete. |
| >>sh sqlci -i "TEST142(user6_cmds)" -u sql_user6; |
| >>values (user); |
| |
| (EXPR) |
| --------------------------------------------------------------------------------------------------------------------------------- |
| |
| SQL_USER6 |
| |
| --- 1 row(s) selected. |
| >>set schema t142user6; |
| |
| --- SQL operation complete. |
| >>alter table user6 add constraint u6_fk1 foreign key (u6_c3) |
| +> references t142user4.referencedTable(c4); |
| |
| --- SQL operation complete. |
| >>-- this case always fails |
| >>alter table user6 add constraint u6_fk2 foreign key (u6_c1) |
| +> references t142user4.referencedTable(c2); |
| |
| *** ERROR[4481] The user does not have REFERENCES privilege on table or view REFERENCEDTABLE. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>exit; |
| |
| End of MXCI Session |
| |
| >> |
| >>obey TEST142(test_revokes); |
| >>-- ============================================================================ |
| >>-- verify that revoking privileges handle REFERENCES privilege correctly |
| >>-- ============================================================================ |
| >>set schema t142user4; |
| |
| --- SQL operation complete. |
| >>execute get_privs; |
| |
| OBJECT_NAME GRANTOR_ID GRANTEE_ID (EXPR) GRANTED_PRIVS GRANTABLE_PRIVS |
| -------------------------------------------------------------------------------- -------------------- -------------------- ------ -------------------- -------------------- |
| |
| REFERENCEDTABLE -2 33337 all SIDU-R- SIDU-R- |
| REFERENCEDTABLE 33335 33339 3 -----R- NONE |
| REFERENCEDTABLE 33336 33338 all -----R- NONE |
| REFERENCEDTABLE 33337 33334 1 -----R- NONE |
| REFERENCEDTABLE 33337 33334 2 -----R- NONE |
| REFERENCEDTABLE 33337 33334 all -----R- NONE |
| REFERENCEDTABLE 33337 33335 3 -----R- -----R- |
| REFERENCEDTABLE 33337 33335 4 -----R- NONE |
| REFERENCEDTABLE 33337 33336 all SIDU-R- SIDU-R- |
| T142_L1 -2 33337 all ---UG-- ---UG-- |
| T142_TRANSLATEPRIVSB -2 33337 all ------E ------E |
| T142_TRANSLATEPRIVSB 33337 -1 all ------E NONE |
| USER1 -2 33334 all SIDU-R- SIDU-R- |
| USER2 -2 33335 all SIDU-R- SIDU-R- |
| USER3 -2 33336 all SIDU-R- SIDU-R- |
| USER5 -2 33338 all SIDU-R- SIDU-R- |
| USER6 -2 33339 all SIDU-R- SIDU-R- |
| |
| --- 17 row(s) selected. |
| >> |
| >>-- unable to revoke because of u1_fk2 |
| >>revoke references on referencedTable from sql_user1; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK2" exists. |
| |
| --- SQL operation failed with errors. |
| >>-- revoke succeeds because user2 has references at the object level |
| >>revoke references (c2, c3) on referencedTable from sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- remove u1_fk2 and retry |
| >>alter table t142user1.user1 drop constraint u1_fk2; |
| |
| --- SQL operation complete. |
| >>grant references (c2, c3) on referencedTable to sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>-- now able to revoke references privilege |
| >>revoke references on referencedTable from sql_user1; |
| |
| --- SQL operation complete. |
| >>-- but not able to remove column privileges |
| >>revoke references (c2) on referencedTable from sql_user1; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| --- SQL operation failed with errors. |
| >>revoke references (c3) on referencedTable from sql_user1; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| --- SQL operation failed with errors. |
| >>revoke references (c2, c3) on referencedTable from sql_user1; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- and vice versa |
| >>grant references on referencedTable to sql_user1; |
| |
| --- SQL operation complete. |
| >>-- can revoke |
| >>revoke references (c2) on referencedTable from sql_user1; |
| |
| --- SQL operation complete. |
| >>revoke references (c3) on referencedTable from sql_user1; |
| |
| --- SQL operation complete. |
| >>-- cannot revoke |
| >>revoke references on referencedTable from sql_user1; |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| *** ERROR[1025] Request failed. Dependent object TRAFODION."T142USER1"."U1_FK1" exists. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- drop constraint and revoke succeeds |
| >>alter table t142user1.user1 drop constraint u1_fk1; |
| |
| --- SQL operation complete. |
| >>revoke references on referencedTable from sql_user1; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
