Repository: incubator-trafodion Updated Branches: refs/heads/master 1b51c43f5 -> c581a16ae
Various JIRA fixes related to security TRAFODION-2177: Revoke combined column privileges from role failed TRAFODION-2188: Insufficient privileges on sample table TRAFODION-2197: column privilege -- mxosrvr crashed 2177: Privilege list for object generated twice causing restrict check to fail 2188: Updated to allow user without create privilege to create sample table 2197: List index was incorrectly specified In addition, relaxed rules on who can run update statistics to include anyone that has SELECT privileges on the target table. Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/58e00611 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/58e00611 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/58e00611 Branch: refs/heads/master Commit: 58e006111d3a457cf61efad743f327d9d4822d8a Parents: 5d06605 Author: Roberta Marton <roberta.mar...@apache.org> Authored: Thu Sep 1 17:28:20 2016 +0000 Committer: Roberta Marton <roberta.mar...@apache.org> Committed: Thu Sep 1 17:28:20 2016 +0000 ---------------------------------------------------------------------- core/sql/regress/privs1/EXPECTED132 | 177 +++++++++++++++++++------- core/sql/regress/privs1/TEST132 | 39 ++++++ core/sql/regress/privs2/EXPECTED139 | 155 ++++++++++++++++++++++ core/sql/regress/privs2/TEST139 | 34 +++++ core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp | 74 ++++++----- core/sql/sqlcomp/PrivMgrPrivileges.cpp | 8 +- core/sql/ustat/hs_globals.cpp | 57 +++++---- 7 files changed, 433 insertions(+), 111 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/regress/privs1/EXPECTED132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED132 b/core/sql/regress/privs1/EXPECTED132 index d84aee8..6df38e5 100644 --- a/core/sql/regress/privs1/EXPECTED132 +++ b/core/sql/regress/privs1/EXPECTED132 @@ -866,7 +866,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:15:40 2016 +-- Definition current Thu Sep 1 16:41:51 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -883,7 +883,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:15:42 2016 +-- Definition current Thu Sep 1 16:41:54 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -900,7 +900,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Thu Feb 25 11:15:43 2016 +-- Definition current Thu Sep 1 16:41:57 2016 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -914,7 +914,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_home_teams_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES --- Definition current Thu Feb 25 11:15:45 2016 +-- Definition current Thu Sep 1 16:41:59 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -984,7 +984,7 @@ ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:16:00 2016 +-- Definition current Thu Sep 1 16:42:20 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1179,7 +1179,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:16:33 2016 +-- Definition current Thu Sep 1 16:42:56 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1196,7 +1196,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:16:33 2016 +-- Definition current Thu Sep 1 16:42:56 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1213,7 +1213,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Thu Feb 25 11:16:33 2016 +-- Definition current Thu Sep 1 16:42:56 2016 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1227,7 +1227,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_home_teams_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES --- Definition current Thu Feb 25 11:16:33 2016 +-- Definition current Thu Sep 1 16:42:56 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1307,7 +1307,7 @@ ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:16:51 2016 +-- Definition current Thu Sep 1 16:43:23 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1456,7 +1456,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:17:10 2016 +-- Definition current Thu Sep 1 16:43:46 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1473,7 +1473,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:17:10 2016 +-- Definition current Thu Sep 1 16:43:46 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1490,7 +1490,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Thu Feb 25 11:17:10 2016 +-- Definition current Thu Sep 1 16:43:46 2016 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1504,7 +1504,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_home_teams_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES --- Definition current Thu Feb 25 11:17:10 2016 +-- Definition current Thu Sep 1 16:43:46 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1617,7 +1617,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:17:27 2016 +-- Definition current Thu Sep 1 16:44:07 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1634,7 +1634,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:17:27 2016 +-- Definition current Thu Sep 1 16:44:07 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1651,7 +1651,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Thu Feb 25 11:17:27 2016 +-- Definition current Thu Sep 1 16:44:07 2016 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1665,7 +1665,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_home_teams_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES --- Definition current Thu Feb 25 11:17:27 2016 +-- Definition current Thu Sep 1 16:44:07 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1778,7 +1778,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Thu Feb 25 11:17:44 2016 +-- Definition current Thu Sep 1 16:44:28 2016 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1795,7 +1795,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Thu Feb 25 11:17:44 2016 +-- Definition current Thu Sep 1 16:44:29 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1812,7 +1812,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Thu Feb 25 11:17:44 2016 +-- Definition current Thu Sep 1 16:44:29 2016 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1826,7 +1826,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_home_teams_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES --- Definition current Thu Feb 25 11:17:44 2016 +-- Definition current Thu Sep 1 16:44:29 2016 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1858,6 +1858,7 @@ End of MXCI Session >>-- privileges. To update stats, you must: >>-- be DB__ROOT >>-- be table owner +>>-- have SELECT privilege >>-- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv) >>-- ================================================================= >> @@ -1939,11 +1940,15 @@ End of MXCI Session >> >>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. @@ -1980,6 +1985,8 @@ End of MXCI Session >> >>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. @@ -1996,26 +2003,26 @@ End of MXCI Session >>showstats for table t132t1 on every column; Histogram data for Table TRAFODION.T132SCH.T132T1 -Table ID: 9082725574613497403 +Table ID: 5832268892549246290 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== -1275314956 8 8 8 SYSKEY -1275314953 8 8 8 C1 -1275314946 8 8 8 C2 +1715115286 8 8 8 SYSKEY +1715115283 8 8 8 C1 +1715115276 8 8 8 C2 --- SQL operation complete. >>showstats for table t132t2 on every column; Histogram data for Table TRAFODION.T132SCH.T132T2 -Table ID: 9082725574613497462 +Table ID: 5832268892549246382 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== -1278381713 8 8 8 SYSKEY -1278381708 8 8 8 C1 -1278381703 8 8 8 C2 +1721386187 8 8 8 SYSKEY +1721386182 8 8 8 C1 +1721386177 8 8 8 C2 --- SQL operation complete. @@ -2056,11 +2063,15 @@ CREATE_SCHEMA >> >>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. @@ -2073,14 +2084,14 @@ CREATE_SCHEMA >> >>showstats for table t132t1 on every column; -*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1. +*** 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 privilege on table or view TRAFODION.T132SCH.T132T2. +*** 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. @@ -2121,26 +2132,26 @@ MANAGE_STATISTICS >>showstats for table t132t1 on every column; Histogram data for Table TRAFODION.T132SCH.T132T1 -Table ID: 9082725574613497403 +Table ID: 5832268892549246290 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== -1275314957 8 8 8 SYSKEY -1275314952 8 8 8 C1 -1275314947 8 8 8 C2 +1715115287 8 8 8 SYSKEY +1715115282 8 8 8 C1 +1715115277 8 8 8 C2 --- SQL operation complete. >>showstats for table t132t2 on every column; Histogram data for Table TRAFODION.T132SCH.T132T2 -Table ID: 9082725574613497462 +Table ID: 5832268892549246382 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== -1278381712 8 8 8 SYSKEY -1278381709 8 8 8 C1 -1278381702 8 8 8 C2 +1721386186 8 8 8 SYSKEY +1721386183 8 8 8 C1 +1721386176 8 8 8 C2 --- SQL operation complete. @@ -2162,14 +2173,14 @@ End of MXCI Session >> >>showstats for table t132t1 on every column; -*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T1. +*** 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 privilege on table or view TRAFODION.T132SCH.T132T2. +*** 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. @@ -2201,19 +2212,19 @@ CREATE TABLE TRAFODION.T132SCH.T132T1 >>showstats for table t132t1 on every column; Histogram data for Table TRAFODION.T132SCH.T132T1 -Table ID: 9082725574613497403 +Table ID: 5832268892549246290 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== -1275314957 8 8 8 SYSKEY -1275314952 8 8 8 C1 -1275314947 8 8 8 C2 +1715115287 8 8 8 SYSKEY +1715115282 8 8 8 C1 +1715115277 8 8 8 C2 --- SQL operation complete. >>showstats for table t132t2 on every column; -*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T2. +*** ERROR[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. @@ -2223,6 +2234,78 @@ Table ID: 9082725574613497403 End of MXCI Session >> +>>-- 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 + +--- SQL operation complete. +>>sh sqlci -i "TEST132(update_stats1)" -u sql_user3; +>>update statistics for table t132t3 create sample random 10 percent; + +--- SQL operation complete. +>> +>>exit; + +End of MXCI Session + +>>revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; + +--- SQL operation complete. +>>drop table t132t3 cascade; + +--- SQL operation complete. +>>drop schema t132sch_private cascade; + +--- SQL operation complete. +>>set schema t132sch; + +--- SQL operation complete. +>> >>-- reset >>revoke SELECT on t132t1 from sql_user4; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/regress/privs1/TEST132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST132 b/core/sql/regress/privs1/TEST132 index 30e846f..d303510 100755 --- a/core/sql/regress/privs1/TEST132 +++ b/core/sql/regress/privs1/TEST132 @@ -52,6 +52,7 @@ exit; set schema t132sch; drop sequence t132_team_number_sequence; cleanup schema t132sch; +drop schema t132sch_private cascade; ?section set_up create shared schema t132sch; @@ -324,6 +325,7 @@ invoke t132_home_teams_games; -- privileges. To update stats, you must: -- be DB__ROOT -- be table owner +-- have SELECT privilege -- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv) -- ================================================================= @@ -382,6 +384,38 @@ grant SELECT on t132t1 to sql_user4; showddl t132t1; sh sqlci -i "TEST132(show_stats)" -u sql_user4; +-- testcase for trafodion-2188 fix +create schema t132sch_private; +set schema t132sch_private; +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); + +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; +; +select count(*) from t132t3; + +grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3; +get privileges on component sql_operations for sql_user3; +sh sqlci -i "TEST132(update_stats1)" -u sql_user3; +revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; +drop table t132t3 cascade; +drop schema t132sch_private cascade; +set schema t132sch; + -- reset revoke SELECT on t132t1 from sql_user4; grant component privilege "SHOW" on sql_operations to "PUBLIC"; @@ -401,6 +435,11 @@ log LOG132; update statistics for table t132t1 on every column; update statistics for table t132t2 on every column; +?section update_stats1 +set schema t132sch_private; +log LOG132; +update statistics for table t132t3 create sample random 10 percent; + ?section show_stats set schema t132sch; log LOG132; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/regress/privs2/EXPECTED139 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED139 b/core/sql/regress/privs2/EXPECTED139 index 934a2dd..b60d239 100644 --- a/core/sql/regress/privs2/EXPECTED139 +++ b/core/sql/regress/privs2/EXPECTED139 @@ -580,6 +580,161 @@ ALTER TABLE TRAFODION.T139SCH.TEAMS ADD CONSTRAINT --- 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) + ) +; + +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) + ) +; + +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) + ) +; + +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) + ) +; + +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; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/regress/privs2/TEST139 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST139 b/core/sql/regress/privs2/TEST139 index 6a2f354..2b864ef 100755 --- a/core/sql/regress/privs2/TEST139 +++ b/core/sql/regress/privs2/TEST139 @@ -48,6 +48,8 @@ exit; ?section clean_up -- drop database drop schema t139sch cascade; +revoke role1 from sq1_user1; +drop role role1; ?section create_db create shared schema t139sch; @@ -266,6 +268,22 @@ showddl games; revoke all_dml on teams from sql_user3; showddl teams; +-- test for JIRA 2177 +create role role1; +grant role role1 to sql_user1; +grant insert(team_contact,team_contact_number), select on teams to role1; +showddl teams; +revoke insert(team_contact,team_contact_number), select on teams from role1; +showddl teams; + +-- test for JIRA 2196/2197 +grant select,insert ,delete, update ,references(game_time) on games to sql_user1 with grant option; +showddl games; +sh sqlci -i "TEST139(user1_grants)" -u sql_user1; +revoke select,insert ,delete, update ,references(game_time) on games + from sql_user1; +revoke role role1 from sql_user1; +drop role role1; ?section user1_dml set schema t139sch; @@ -323,3 +341,19 @@ delete from teams where team_number = 6; grant select on teams to sql_user4; grant update on teams to sql_user4; +?section user1_grants +set schema t139sch; +cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; +log LOG139; +grant select(home_team_number,game_time), + insert(home_team_number), + update(visitor_team_number,game_time), + references(game_time) +on games to role1; +showddl games; +revoke select(home_team_number,game_time), + insert(home_team_number), + update(visitor_team_number,game_time), + references(game_time) +on games from role1; + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp index 6203ab6..e4020e8 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp @@ -9668,17 +9668,17 @@ int32_t CmpSeabaseDDL::verifyDDLCreateOperationAuthorized( { -int32_t currentUser = ComUser::getCurrentUser(); -NAString privMgrMDLoc; + int32_t currentUser = ComUser::getCurrentUser(); + NAString privMgrMDLoc; CONCAT_CATSCH(privMgrMDLoc,getSystemCatalog(),SEABASE_PRIVMGR_SCHEMA); -PrivMgrComponentPrivileges componentPrivileges(std::string(privMgrMDLoc.data()), - CmpCommon::diags()); + PrivMgrComponentPrivileges componentPrivileges(std::string(privMgrMDLoc.data()), + CmpCommon::diags()); -// CREATE SCHEMA is a special case. There is no existing schema with an -// an owner or class. A new schema may be created if the user is DB__ROOT, -// authorization is not enabled, or the user has the CREATE_SCHEMA privilege. + // CREATE SCHEMA is a special case. There is no existing schema with an + // an owner or class. A new schema may be created if the user is DB__ROOT, + // authorization is not enabled, or the user has the CREATE_SCHEMA privilege. if (operation == SQLOperation::CREATE_SCHEMA) { @@ -9699,9 +9699,9 @@ PrivMgrComponentPrivileges componentPrivileges(std::string(privMgrMDLoc.data()), return CAT_NOT_AUTHORIZED; } -// -// Not CREATE SCHEMA, but verify the operation is a create operation. -// + // + // Not CREATE SCHEMA, but verify the operation is a create operation. + // if (!PrivMgr::isSQLCreateOperation(operation)) { SEABASEDDL_INTERNAL_ERROR("Unknown create operation"); @@ -9709,12 +9709,12 @@ PrivMgrComponentPrivileges componentPrivileges(std::string(privMgrMDLoc.data()), return CAT_INTERNAL_EXCEPTION_ERROR; } -// User is asking to create an object in an existing schema. Determine if this -// schema exists, and if it exists, the owner of the schema. The schema class -// and owner will determine if this user can create an object in the schema and -// who will own the object. + // User is asking to create an object in an existing schema. Determine if this + // schema exists, and if it exists, the owner of the schema. The schema class + // and owner will determine if this user can create an object in the schema and + // who will own the object. -ComObjectType objectType; + ComObjectType objectType; if (getObjectTypeandOwner(cliInterface,catalogName.data(),schemaName.data(), SEABASE_SCHEMA_OBJECTNAME,objectType,schemaOwner) == -1) @@ -9743,29 +9743,33 @@ ComObjectType objectType; objectOwner = schemaOwner; -// Root user is authorized for all create operations in private schemas. For -// installations with no authentication, all users are mapped to root database -// user, so all users have full DDL create authority. + // Root user is authorized for all create operations in private schemas. For + // installations with no authentication, all users are mapped to root database + // user, so all users have full DDL create authority. if (currentUser == ComUser::getRootUserID()) return 0; -// If authorization is not enabled, then authentication should not be enabled -// either, and the previous check should have already returned. But just in -// case, verify authorization is enabled before proceeding. Eventually this -// state should be recorded somewhere, e.g. CLI globals. + // If authorization is not enabled, then authentication should not be enabled + // either, and the previous check should have already returned. But just in + // case, verify authorization is enabled before proceeding. Eventually this + // state should be recorded somewhere, e.g. CLI globals. if (!isAuthorizationEnabled()) return 0; - -// To create an object in a private schema, one of three conditions must be true: -// -// 1) The user is the owner of the schema. -// 2) The schema is owned by a role, and the user has been granted the role. -// 3) The user has been granted the requisite system-level SQL_OPERATIONS -// component create privilege. -// -// NOTE: In the future, schema-level create authority will be supported. + + // If this is an internal operation, allow the operation. + if (Get_SqlParser_Flags(INTERNAL_QUERY_FROM_EXEUTIL)) + return 0; + + // To create an object in a private schema, one of three conditions must be true: + // + // 1) The user is the owner of the schema. + // 2) The schema is owned by a role, and the user has been granted the role. + // 3) The user has been granted the requisite system-level SQL_OPERATIONS + // component create privilege. + // + // NOTE: In the future, schema-level create authority will be supported. if (currentUser == schemaOwner) return 0; @@ -9780,14 +9784,14 @@ ComObjectType objectType; return 0; } -// Current user is not the schema owner. See if they have been granted the -// requisite create privilege. + // Current user is not the schema owner. See if they have been granted the + // requisite create privilege. if (componentPrivileges.hasSQLPriv(currentUser,operation,true)) return 0; -// TODO: When schema-level privileges are implemented, see if user has the -// requisite create privilege for this specific schema. + // TODO: When schema-level privileges are implemented, see if user has the + // requisite create privilege for this specific schema. objectOwner = schemaOwner = NA_UserIdDefault; return CAT_NOT_AUTHORIZED; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/sqlcomp/PrivMgrPrivileges.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/PrivMgrPrivileges.cpp b/core/sql/sqlcomp/PrivMgrPrivileges.cpp index 82b3d6b..7b96792 100644 --- a/core/sql/sqlcomp/PrivMgrPrivileges.cpp +++ b/core/sql/sqlcomp/PrivMgrPrivileges.cpp @@ -2654,6 +2654,9 @@ PrivStatus PrivMgrPrivileges::gatherViewPrivileges( // **************************************************************************** PrivStatus PrivMgrPrivileges::generateColumnRowList() { + // If columnRowList_ already allocated, just return + if (columnRowList_.size() > 0) + return STATUS_GOOD; PrivStatus privStatus = getColumnRowList(objectUID_, columnRowList_); if (privStatus == STATUS_ERROR) return privStatus; @@ -2718,6 +2721,9 @@ PrivStatus PrivMgrPrivileges::getColumnRowList( // **************************************************************************** PrivStatus PrivMgrPrivileges::generateObjectRowList() { + // If objectRowList_ already allocated, just return + if (objectRowList_.size() > 0) + return STATUS_GOOD; PrivStatus privStatus = getObjectRowList(objectUID_, objectRowList_); if (privStatus == STATUS_ERROR) return privStatus; @@ -5958,7 +5964,7 @@ std::vector<ColPrivSpec> &colPrivsArray = // See if the grantor has been granted WGO at column-level for priv. for (size_t j = 0; j < colRowList.size(); j++) { - ColumnPrivsMDRow &columnRow = static_cast<ColumnPrivsMDRow &> (*colRowList[i]); + ColumnPrivsMDRow &columnRow = static_cast<ColumnPrivsMDRow &> (*colRowList[j]); if (columnRow.columnOrdinal_ == colPrivSpec.columnOrdinal) { if (columnRow.grantableBitmap_.test(colPrivSpec.privType)) http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/58e00611/core/sql/ustat/hs_globals.cpp ---------------------------------------------------------------------- diff --git a/core/sql/ustat/hs_globals.cpp b/core/sql/ustat/hs_globals.cpp index 3f48b42..a7af0f8 100644 --- a/core/sql/ustat/hs_globals.cpp +++ b/core/sql/ustat/hs_globals.cpp @@ -3475,7 +3475,6 @@ NABoolean HSGlobalsClass::isAuthorized(NABoolean isShowStats) true)); } - // for UPDATE STATISTICS, no more checking is performed // For SHOW STATS command, check for additional privileges if (!authorized && isShowStats) { @@ -3489,35 +3488,37 @@ NABoolean HSGlobalsClass::isAuthorized(NABoolean isShowStats) authorized = (componentPrivileges.hasSQLPriv(ComUser::getCurrentUser(), SQLOperation::SHOW, true)); - if (!authorized) - { - if (LM->LogNeeded()) - { - sprintf(LM->msg, "Authorization: check for SELECT object privilege"); - LM->Log(LM->msg); - } - - // check for SELECT privilege - PrivMgrUserPrivs *privs = objDef->getNATable()->getPrivInfo(); - if (privs == NULL) - { - *CmpCommon::diags() << DgSqlCode(-1034); - authorized = FALSE; - } + } + + // Allow operation if requester has SELECT priv + if (!authorized) + { + if (LM->LogNeeded()) + { + sprintf(LM->msg, "Authorization: check for SELECT object privilege"); + LM->Log(LM->msg); + } - // Requester must have at least select privilege - if ( privs->hasSelectPriv() ) - authorized = TRUE; - else - { - *CmpCommon::diags() - << DgSqlCode( -4481 ) - << DgString0( "SELECT" ) - << DgString1( objDef->getNATable()->getTableName().getQualifiedNameAsAnsiString() ); - authorized = FALSE; - } + // check for SELECT privilege + PrivMgrUserPrivs *privs = objDef->getNATable()->getPrivInfo(); + if (privs == NULL) + { + *CmpCommon::diags() << DgSqlCode(-1034); + authorized = FALSE; } - } + + // Requester must have at least select privilege + if ( privs->hasSelectPriv() ) + authorized = TRUE; + else + { + *CmpCommon::diags() + << DgSqlCode( -4481 ) + << DgString0( "SELECT or MANAGE_STATISTICS" ) + << DgString1( objDef->getNATable()->getTableName().getQualifiedNameAsAnsiString() ); + authorized = FALSE; + } + } LM->LogTimeDiff("Exiting: HSGlobalsClass::isAuthorized"); return authorized;
