Miscellaneous authorization changes: - Unregister user does not remove component privileges - Reuse unused entries from the authID ranges - Add "changeuser" command to update user credentials in place instead of requiring a new sqlci session to be started. Changed privs1/TEST132 to use this change and cut about 5 minutes off the test time.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/079ea00a Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/079ea00a Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/079ea00a Branch: refs/heads/master Commit: 079ea00a1710c9ca8474db06a44309e21c5a0361 Parents: 5071a20 Author: Roberta Marton <[email protected]> Authored: Mon Oct 23 16:13:00 2017 +0000 Committer: Roberta Marton <[email protected]> Committed: Mon Oct 23 16:13:00 2017 +0000 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 2 +- core/sql/common/ComUser.cpp | 6 +- core/sql/common/NAUserId.h | 73 +- core/sql/regress/privs1/EXPECTED132 | 941 ++++++------------- core/sql/regress/privs1/TEST132 | 145 +-- core/sql/sqlci/SqlCmd.cpp | 1 + core/sql/sqlci/SqlciCmd.h | 9 +- core/sql/sqlci/SqlciEnv.cpp | 49 +- core/sql/sqlci/sqlci_lex.ll | 1 + core/sql/sqlci/sqlci_yacc.y | 11 + core/sql/sqlcomp/CmpDDLCatErrorCodes.h | 2 +- core/sql/sqlcomp/CmpSeabaseDDLauth.cpp | 351 ++++--- core/sql/sqlcomp/CmpSeabaseDDLauth.h | 18 +- core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp | 15 +- core/sql/sqlcomp/PrivMgrComponentDefs.h | 284 ++++++ core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp | 240 ++++- core/sql/sqlcomp/PrivMgrComponentPrivileges.h | 2 + core/sql/sqlcomp/PrivMgrMD.cpp | 14 +- core/sql/sqlcomp/PrivMgrRoles.cpp | 2 +- 19 files changed, 1238 insertions(+), 928 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index d70f23d..8fdd3dc 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -5,7 +5,7 @@ 1003 ZZZZZ 99999 BEGINNER MINOR DBADMIN Schema $0~SchemaName does not exist. 1004 ZZZZZ 99999 BEGINNER MINOR DBADMIN Object $0~TableName does not exist or object type is invalid for the current operation. 1005 ZZZZZ 99999 BEGINNER MINOR DBADMIN Constraint $0~ConstraintName does not exist. -1006 ZZZZZ 99999 BEGINNER MINOR DBADMIN --- unused --- +1006 ZZZZZ 99999 BEGINNER MINOR DBADMIN Skipping authorization ID $0~Int0. 1007 ZZZZZ 99999 ADVANCED MAJOR DBADMIN The WITH GRANT OPTION is not supported. 1008 ZZZZZ 99999 BEGINNER MINOR DBADMIN Authorization identifier $0~String0 does not exist. 1009 ZZZZZ 99999 BEGINNER MINOR DBADMIN Column $0~ColumnName does not exist in the specified table. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/common/ComUser.cpp ---------------------------------------------------------------------- diff --git a/core/sql/common/ComUser.cpp b/core/sql/common/ComUser.cpp index b8261f4..796d94b 100644 --- a/core/sql/common/ComUser.cpp +++ b/core/sql/common/ComUser.cpp @@ -408,7 +408,7 @@ Int32 ComUser::getRoleList (char * roleList, const char separator, const bool includeSpecialAuths) { - Int32 numberRoles = sizeof(systemRoles)/sizeof(SystemRolesStruct); + Int32 numberRoles = sizeof(systemRoles)/sizeof(SystemAuthsStruct); Int32 roleListLen = (MAX_AUTHNAME_LEN*numberRoles)+(numberRoles * 4); // 4 = 2 del + 2 sep char generatedRoleList[roleListLen]; char *pRoles = generatedRoleList; @@ -416,13 +416,13 @@ Int32 ComUser::getRoleList (char * roleList, char currentSeparator = ' '; for (Int32 i = 0; i < numberRoles; i++) { - const SystemRolesStruct &roleDefinition = systemRoles[i]; + const SystemAuthsStruct &roleDefinition = systemRoles[i]; if (!includeSpecialAuths && roleDefinition.isSpecialAuth) continue; // str_sprintf does not support the %c format sprintf(roleName, "%c%c%s%c", - currentSeparator, delimiter, roleDefinition.roleName, delimiter); + currentSeparator, delimiter, roleDefinition.authName, delimiter); str_cpy_all(pRoles, roleName, sizeof(roleName)-1); // don't copy null terminator currentSeparator = separator; pRoles = pRoles + strlen(roleName); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/common/NAUserId.h ---------------------------------------------------------------------- diff --git a/core/sql/common/NAUserId.h b/core/sql/common/NAUserId.h index 46de6bc..d332daf 100644 --- a/core/sql/common/NAUserId.h +++ b/core/sql/common/NAUserId.h @@ -37,53 +37,69 @@ */ #include "Platform.h" -#define MAX_USERID_LEN 4 // int 32 +// ---------------------------------------------------------------------------- +// standard defines: #define MAX_DBUSERNAME_LEN 128 #define MAX_USERNAME_LEN 128 #define MAX_AUTHNAME_LEN 128 #define MAX_AUTHID_AS_STRING_LEN 20 +#define NA_UserIdDefault 0 +// ---------------------------------------------------------------------------- +// Authorization range definitions: +// Authorization ID's include users, roles, and tenants (maybe groups later) +#define MIN_USERID 33333 /* reserve 1 to 33333 for system users */ +#define MAX_USERID 799999 -#define MIN_USERID 33333 -#define MAX_USERID 999999 -#define MIN_ROLEID 1000000 +#define MIN_ROLEID 1000000 #define MAX_ROLEID_RANGE1 1490000 -#define MAX_ROLEID 1500000 -#define NA_UserId Int32 -#define NA_AuthID Int32 -#define NA_UserIdDefault 0 +#define MAX_ROLEID 1499999 -// Defines for special roles -// For new system roles, add a define and include it in the -// systemRoles constant -#define PUBLIC_AUTH_NAME "PUBLIC" -#define DB__HIVEROLE "DB__HIVEROLE" -#define DB__HBASEROLE "DB__HBASEROLE" -#define DB__ROOTROLE "DB__ROOTROLE" -#define DB__LIBMGRROLE "DB__LIBMGRROLE" +// ---------------------------------------------------------------------------- +// For roles and other non-user authIDS, use the following structure to create +// new system objects +struct SystemAuthsStruct +{ + const char *authName; + bool isSpecialAuth; + int32_t authID; +}; -// Defines for special users -#define SYSTEM_AUTH_NAME "_SYSTEM" +// ---------------------------------------------------------------------------- +// Definitions for system users: +// For new system tenants, generate the username and the userID; change +// CmpSeabaseDDL::updateSeabaseAuths to register the new (standard) user #define DB__ROOT "DB__ROOT" +#define SUPER_USER_LIT "33333" -#define SUPER_USER_LIT "33333" - +// If a new system defined user is added, subtract one from MIN_SYSTEM_ID and +// be sure to change MIN_USERID to the smaller value #define SYSTEM_USER -2 #define PUBLIC_USER -1 +#define MIN_SYSTEM_ID 33330 #define ROOT_USER_ID 33333 #define SUPER_USER 33333 +// ----------------------------------------------------------------------------- +// Definitions for system roles: +// For new system roles, add a define and include it in the systemRoles constant +// When authorization is enabled, these roles are created, no additional changes +// to the code is required. +#define SYSTEM_AUTH_NAME "_SYSTEM" +#define PUBLIC_AUTH_NAME "PUBLIC" +#define DB__HIVEROLE "DB__HIVEROLE" +#define DB__HBASEROLE "DB__HBASEROLE" +#define DB__ROOTROLE "DB__ROOTROLE" +#define DB__LIBMGRROLE "DB__LIBMGRROLE" + +// Most system roles do not have a predefined range of IDs, so for new roles +// just specify NA_UserIdDefault in the systemRoles struct. Role code creates +// roles for each non special system role in the list. If NA_UserIdDefault is +// specified, the code generates a UniqueID. #define ROOT_ROLE_ID 1000000 #define HIVE_ROLE_ID 1490000 #define HBASE_ROLE_ID 1490001 -struct SystemRolesStruct -{ - const char *roleName; - bool isSpecialAuth; - int32_t roleID; -}; - -static const SystemRolesStruct systemRoles[] +static const SystemAuthsStruct systemRoles[] { { DB__HIVEROLE, false, HIVE_ROLE_ID }, { DB__HBASEROLE, false, HBASE_ROLE_ID }, { DB__ROOTROLE, false, ROOT_ROLE_ID }, @@ -93,5 +109,4 @@ static const SystemRolesStruct systemRoles[] #define NUMBER_SPECIAL_SYSTEM_ROLES 2; - #endif /* NAUSERID_H*/ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/regress/privs1/EXPECTED132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/EXPECTED132 b/core/sql/regress/privs1/EXPECTED132 index 93e580e..0c9f2fe 100644 --- a/core/sql/regress/privs1/EXPECTED132 +++ b/core/sql/regress/privs1/EXPECTED132 @@ -24,7 +24,11 @@ SHOW --- SQL operation complete. >> >>-- succeed: DB__ROOT can create a library ->>sh sqlci -i "TEST132(manage_library)"; +>>obey TEST132(manage_library); +>> +>>set schema t132sch; + +--- SQL operation complete. >>get libraries in schema t132sch; --- SQL operation complete. @@ -46,13 +50,14 @@ T132_L1 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - >> >>-- fail: sql_user1 cannot create a library ->>sh sqlci -i "TEST132(manage_library)" -u sql_user1; +>>changeuser sql_user1; +>>obey TEST132(manage_library); +>> +>>set schema t132sch; + +--- SQL operation complete. >>get libraries in schema t132sch; --- SQL operation complete. @@ -73,16 +78,18 @@ End of MXCI Session --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >> >>-- succeed: grant DB__ROOTROLE to sql_user1 >>grant role DB__ROOTROLE to sql_user1; --- SQL operation complete. ->>sh sqlci -i "TEST132(manage_library)" -u sql_user1; +>>changeuser sql_user1; +>>obey TEST132(manage_library); +>> +>>set schema t132sch; + +--- SQL operation complete. >>get libraries in schema t132sch; --- SQL operation complete. @@ -104,16 +111,18 @@ T132_L1 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser DB__ROOT; >> >>-- fail: just grant the create privilege >>grant component privilege CREATE_LIBRARY on sql_operations to sql_user2; --- SQL operation complete. ->>sh sqlci -i "TEST132(manage_library)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(manage_library); +>> +>>set schema t132sch; + +--- SQL operation complete. >>get libraries in schema t132sch; --- SQL operation complete. @@ -134,10 +143,7 @@ End of MXCI Session --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser DB__ROOT; >> >>-- succeed: now grant the manage_library privilege >>grant component privilege MANAGE_LIBRARY on sql_operations to sql_user2; @@ -146,7 +152,12 @@ End of MXCI Session >>get privileges on component sql_operation for sql_user2; --- SQL operation complete. ->>sh sqlci -i "TEST132(manage_library)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(manage_library); +>> +>>set schema t132sch; + +--- SQL operation complete. >>get libraries in schema t132sch; --- SQL operation complete. @@ -168,10 +179,7 @@ T132_L1 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser DB__ROOT; >> >>-- reset >>revoke role DB__ROOTROLE from sql_user1; @@ -205,24 +213,10 @@ SHOW >>set schema t132sch; --- SQL operation complete. ->>set parserflags 1; - ---- SQL operation complete. ->>set parserflags 131072; - ---- SQL operation complete. ->>cqd DDL_TRANSACTIONS 'ON'; - ---- SQL operation complete. +>>--set parserflags 131072; +>>--cqd DDL_TRANSACTIONS 'ON'; >> ->>get tables; - -Tables in Schema TRAFODION.T132SCH -================================== - -SB_HISTOGRAMS -SB_HISTOGRAM_INTERVALS -SB_PERSISTENT_SAMPLES +>>get tables, match '%T132%'; --- SQL operation complete. >> @@ -239,13 +233,6 @@ SB_PERSISTENT_SAMPLES >>create index t132t2_ndx1 on t132t2(c2) no populate; --- SQL operation complete. ->>create table t132t3 (c1 int not null primary key, c2 int) -+> attribute by sql_user1; - ---- SQL operation complete. ->>create index t132t3_ndx1 on t132t3(c2) no populate; - ---- SQL operation complete. >> >>insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), >>(7,7),(8,8); @@ -253,21 +240,17 @@ SB_PERSISTENT_SAMPLES >>insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), >>(7,7),(8,8); --- 8 row(s) inserted. ->>insert into t132t3 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); - ---- 8 row(s) inserted. >> ->>get tables; +>>get tables, match '%T132%'; Tables in Schema TRAFODION.T132SCH ================================== -SB_HISTOGRAMS -SB_HISTOGRAM_INTERVALS -SB_PERSISTENT_SAMPLES T132T1 T132T2 -T132T3 + +--- SQL operation complete. +>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. >>showddl t132t1; @@ -298,23 +281,12 @@ CREATE TABLE TRAFODION.T132SCH.T132T2 -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T2 TO SQL_USER1 WITH GRANT OPTION; --- SQL operation complete. ->>showddl t132t3; - -CREATE TABLE TRAFODION.T132SCH.T132T3 - ( - C1 INT NO DEFAULT NOT NULL NOT DROPPABLE - , C2 INT DEFAULT NULL - , PRIMARY KEY (C1 ASC) - ) - ATTRIBUTES ALIGNED FORMAT -; - --- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132T3 TO SQL_USER1 WITH GRANT OPTION; - ---- SQL operation complete. >> >>-- DB__ROOT can populate indexes ->>sh sqlci -i "TEST132(populate_index)"; +>>obey TEST132(populate_index); +>>set schema t132sch; + +--- SQL operation complete. >> >>populate index t132t1_ndx1 on t132t1; @@ -322,19 +294,14 @@ CREATE TABLE TRAFODION.T132SCH.T132T3 >>populate index t132t2_ndx1 on t132t2; --- SQL operation complete. ->>populate index t132t3_ndx1 on t132t3; - ---- SQL operation complete. >> ->>exit; - -End of MXCI Session - >>obey TEST132(popindex_check_reset); >>set schema t132sch; --- SQL operation complete. ->>log LOG132; +>>set parserflags 1; + +--- SQL operation complete. >> >>select count(*) from table (index_table t132t1_ndx1); @@ -352,16 +319,8 @@ End of MXCI Session 8 --- 1 row(s) selected. ->>select count(*) from table (index_table t132t3_ndx1); - -(EXPR) --------------------- - - 8 - ---- 1 row(s) selected. >> ->>drop index t132t1_ndx1; +>>cleanup index t132t1_ndx1; --- SQL operation complete. >>create index t132t1_ndx1 on t132t1 (c2) no populate; @@ -373,18 +332,16 @@ End of MXCI Session >>create index t132t2_ndx1 on t132t2 (c2) no populate; --- SQL operation complete. ->>drop index t132t3_ndx1; - ---- SQL operation complete. ->>create index t132t3_ndx1 on t132t3 (c2) no populate; - ---- SQL operation complete. >> >> >>-- object owner can populate ->>-- sql_user1 owns t132t2 and t132t3 but not t132t1 +>>-- sql_user1 owns t132t2 but not t132t1 >>-- popindex fails for t132t1 but works for the rest ->>sh sqlci -i "TEST132(populate_index)" -u sql_user1; +>>changeuser sql_user1; +>>obey TEST132(populate_index); +>>set schema t132sch; + +--- SQL operation complete. >> >>populate index t132t1_ndx1 on t132t1; @@ -396,19 +353,15 @@ End of MXCI Session >>populate index t132t2_ndx1 on t132t2; --- SQL operation complete. ->>populate index t132t3_ndx1 on t132t3; - ---- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >>obey TEST132(popindex_check_reset); >>set schema t132sch; --- SQL operation complete. ->>log LOG132; +>>set parserflags 1; + +--- SQL operation complete. >> >>select count(*) from table (index_table t132t1_ndx1); @@ -426,16 +379,8 @@ End of MXCI Session 8 --- 1 row(s) selected. ->>select count(*) from table (index_table t132t3_ndx1); - -(EXPR) --------------------- - - 8 - ---- 1 row(s) selected. >> ->>drop index t132t1_ndx1; +>>cleanup index t132t1_ndx1; --- SQL operation complete. >>create index t132t1_ndx1 on t132t1 (c2) no populate; @@ -447,19 +392,17 @@ End of MXCI Session >>create index t132t2_ndx1 on t132t2 (c2) no populate; --- SQL operation complete. ->>drop index t132t3_ndx1; - ---- SQL operation complete. ->>create index t132t3_ndx1 on t132t3 (c2) no populate; - ---- SQL operation complete. >> >> >>-- if user belongs to DB__ROOTROLE, has DML privileges, so can populate >>indexes >>grant role DB__ROOTROLE to sql_user2; --- SQL operation complete. ->>sh sqlci -i "TEST132(populate_index)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(populate_index); +>>set schema t132sch; + +--- SQL operation complete. >> >>populate index t132t1_ndx1 on t132t1; @@ -467,19 +410,15 @@ End of MXCI Session >>populate index t132t2_ndx1 on t132t2; --- SQL operation complete. ->>populate index t132t3_ndx1 on t132t3; - ---- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >>obey TEST132(popindex_check_reset); >>set schema t132sch; --- SQL operation complete. ->>log LOG132; +>>set parserflags 1; + +--- SQL operation complete. >> >>select count(*) from table (index_table t132t1_ndx1); @@ -497,16 +436,8 @@ End of MXCI Session 8 --- 1 row(s) selected. ->>select count(*) from table (index_table t132t3_ndx1); - -(EXPR) --------------------- - - 8 - ---- 1 row(s) selected. >> ->>drop index t132t1_ndx1; +>>cleanup index t132t1_ndx1; --- SQL operation complete. >>create index t132t1_ndx1 on t132t1 (c2) no populate; @@ -518,12 +449,6 @@ End of MXCI Session >>create index t132t2_ndx1 on t132t2 (c2) no populate; --- SQL operation complete. ->>drop index t132t3_ndx1; - ---- SQL operation complete. ->>create index t132t3_ndx1 on t132t3 (c2) no populate; - ---- SQL operation complete. >> >>revoke role DB__ROOTROLE from sql_user2; @@ -537,10 +462,11 @@ End of MXCI Session >>grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1; --- SQL operation complete. ->>grant INSERT on t132t3 to sql_user3 by sql_user1; +>>changeuser sql_user3; +>>obey TEST132(populate_index); +>>set schema t132sch; --- SQL operation complete. ->>sh sqlci -i "TEST132(populate_index)" -u sql_user3; >> >>populate index t132t1_ndx1 on t132t1; @@ -550,21 +476,15 @@ End of MXCI Session >>populate index t132t2_ndx1 on t132t2; --- SQL operation complete. ->>populate index t132t3_ndx1 on t132t3; - -*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.T132SCH.T132T3. - ---- SQL operation failed with errors. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >>obey TEST132(popindex_check_reset); >>set schema t132sch; --- SQL operation complete. ->>log LOG132; +>>set parserflags 1; + +--- SQL operation complete. >> >>select count(*) from table (index_table t132t1_ndx1); @@ -582,16 +502,8 @@ End of MXCI Session 8 --- 1 row(s) selected. ->>select count(*) from table (index_table t132t3_ndx1); - -(EXPR) --------------------- - - 0 - ---- 1 row(s) selected. >> ->>drop index t132t1_ndx1; +>>cleanup index t132t1_ndx1; --- SQL operation complete. >>create index t132t1_ndx1 on t132t1 (c2) no populate; @@ -603,12 +515,6 @@ End of MXCI Session >>create index t132t2_ndx1 on t132t2 (c2) no populate; --- SQL operation complete. ->>drop index t132t3_ndx1; - ---- SQL operation complete. ->>create index t132t3_ndx1 on t132t3 (c2) no populate; - ---- SQL operation complete. >> >> >>-- reset @@ -618,17 +524,7 @@ End of MXCI Session >>drop table t132t2 cascade; --- SQL operation complete. ->>drop table t132t3 cascade; - ---- SQL operation complete. ->>get tables; - -Tables in Schema TRAFODION.T132SCH -================================== - -SB_HISTOGRAMS -SB_HISTOGRAM_INTERVALS -SB_PERSISTENT_SAMPLES +>>get tables, match 'T132%'; --- SQL operation complete. >> @@ -772,7 +668,6 @@ CREATE_SCHEMA >>set schema t132sch; --- SQL operation complete. ->>log LOG132; >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -871,7 +766,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:16:36 2016 +-- Definition current Sun Oct 22 16:08:56 2017 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -888,7 +783,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Tue Sep 27 10:16:39 2016 +-- Definition current Sun Oct 22 16:08:59 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -905,7 +800,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Tue Sep 27 10:16:41 2016 +-- Definition current Sun Oct 22 16:09:02 2017 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -919,7 +814,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 Tue Sep 27 10:16:44 2016 +-- Definition current Sun Oct 22 16:09:05 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -932,7 +827,12 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >> >> >>-- sql_user1 owns some of the objects but not all ->>sh sqlci -i "TEST132(show_objects)" -u sql_user1; +>>changeuser sql_user1; +>>obey TEST132(show_objects); +>> +>>set schema t132sch; + +--- SQL operation complete. >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -990,7 +890,7 @@ ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:17:03 2016 +-- Definition current Sun Oct 22 16:09:18 2017 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1020,14 +920,15 @@ ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT --- SQL operation failed with errors. >> ->>exit; - -End of MXCI Session - >> >>-- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege >>-- first illustrate that sql_user2 has no privileges ->>sh sqlci -i "TEST132(show_objects)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(show_objects); +>> +>>set schema t132sch; + +--- SQL operation complete. >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -1079,16 +980,18 @@ End of MXCI Session --- SQL operation failed with errors. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >>grant role DB__ROOTROLE to sql_user2; --- SQL operation complete. >> >>-- now sql_user2 has privileges with the grant ->>sh sqlci -i "TEST132(show_objects)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(show_objects); +>> +>>set schema t132sch; + +--- SQL operation complete. >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -1187,7 +1090,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:17:40 2016 +-- Definition current Sun Oct 22 16:09:50 2017 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1204,7 +1107,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Tue Sep 27 10:17:40 2016 +-- Definition current Sun Oct 22 16:09:50 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1221,7 +1124,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Tue Sep 27 10:17:40 2016 +-- Definition current Sun Oct 22 16:09:50 2017 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1235,7 +1138,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 Tue Sep 27 10:17:40 2016 +-- Definition current Sun Oct 22 16:09:50 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1246,19 +1149,24 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>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. ->>sh sqlci -i "TEST132(show_objects)" -u sql_user3; +>>changeuser sql_user3; +>>obey TEST132(show_objects); +>> +>>set schema t132sch; + +--- SQL operation complete. >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -1316,7 +1224,7 @@ ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Tue Sep 27 10:18:07 2016 +-- Definition current Sun Oct 22 16:10:13 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1341,10 +1249,13 @@ ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT --- SQL operation failed with errors. >> ->>exit; +>>changeuser db__root; +>>set schema t132sch; -End of MXCI Session +--- 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"; @@ -1367,7 +1278,12 @@ CREATE_SCHEMA SHOW --- SQL operation complete. ->>sh sqlci -i "TEST132(show_objects)" -u sql_user1; +>>changeuser sql_user1; +>>obey TEST132(show_objects); +>> +>>set schema t132sch; + +--- SQL operation complete. >>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; --- SQL operation complete. @@ -1422,7 +1338,6 @@ ALTER TABLE TRAFODION.T132SCH.T132_TEAMS ADD CONSTRAINT (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; @@ -1467,7 +1382,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_games; -- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:18:30 2016 +-- Definition current Sun Oct 22 16:10:48 2017 ( HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1484,7 +1399,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_teams; -- Definition of Trafodion table TRAFODION.T132SCH.T132_TEAMS --- Definition current Tue Sep 27 10:18:30 2016 +-- Definition current Sun Oct 22 16:10:48 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1501,7 +1416,7 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE >>invoke t132_giants_games; -- Definition of Trafodion view TRAFODION.T132SCH.T132_GIANTS_GAMES --- Definition current Tue Sep 27 10:18:30 2016 +-- Definition current Sun Oct 22 16:10:48 2017 ( GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1515,7 +1430,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 Tue Sep 27 10:18:30 2016 +-- Definition current Sun Oct 22 16:10:49 2017 ( TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -1526,397 +1441,61 @@ CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE --- SQL operation complete. >> ->>exit; +>> +>>changeuser db__root; +>>set schema t132sch; + +--- SQL operation complete. +>>drop table t132_teams cascade; -End of MXCI Session +--- SQL operation complete. +>>drop table t132_games cascade; ->>sh sqlci -i "TEST132(show_objects)" -u sql_user2; ->>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; +--- SQL operation complete. +>>drop sequence t132_team_number_sequence; --- SQL operation complete. >> ->>showddl t132_games; +>>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; -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 -; +--- SQL operation complete. +>>get tables, match '%T132%'; -CREATE INDEX T132_HOME_GAMES ON TRAFODION.T132SCH.T132_GAMES - ( - HOME_TEAM_NUMBER ASC - ) -; +--- SQL operation complete. +>> +>>create table t132t1 (c1 int, c2 int); -ALTER TABLE TRAFODION.T132SCH.T132_GAMES ADD CONSTRAINT - TRAFODION.T132SCH.VALID_GAME_NUMBER CHECK - (TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER > 0) +--- SQL operation complete. +>>create table t132t2 (c1 int, c2 int) attribute by sql_user1; --- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GAMES TO SQL_USER1 WITH GRANT OPTION; +--- 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 --- 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; - GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3; - ---- SQL operation complete. ->>showddl t132_giants_games; - -CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS - SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER, - TRAFODION.T132SCH.T132_GAMES.GAME_TIME, - TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM - TRAFODION.T132SCH.T132_GAMES WHERE - TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ; - --- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->>showddl t132_home_teams_games; - -CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS - SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM - TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE - T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME - ; - --- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->>showddl sequence t132_team_number_sequence; - -CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE - START WITH 1 /* NEXT AVAILABLE VALUE 1 */ - INCREMENT BY 1 - MAXVALUE 9223372036854775806 - MINVALUE 1 - CACHE 25 - NO CYCLE - LARGEINT -; - --- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->> ->>invoke t132_games; - --- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:18:51 2016 - - ( - 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 Tue Sep 27 10:18:51 2016 - - ( - 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 Tue Sep 27 10:18:51 2016 - - ( - 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 Tue Sep 27 10:18:51 2016 - - ( - TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT - DROPPABLE - ) - ---- SQL operation complete. ->> ->>exit; - -End of MXCI Session - ->>sh sqlci -i "TEST132(show_objects)" -u sql_user3; ->>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; - ---- SQL operation complete. ->> ->>showddl t132_games; - -CREATE TABLE TRAFODION.T132SCH.T132_GAMES - ( - HOME_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , VISITOR_TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT - DROPPABLE - , GAME_LOCATION VARCHAR(50) CHARACTER SET ISO88591 COLLATE - DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE - , PRIMARY KEY (GAME_NUMBER ASC) - ) - 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; - GRANT SELECT ON TRAFODION.T132SCH.T132_TEAMS TO SQL_USER3; - ---- SQL operation complete. ->>showddl t132_giants_games; - -CREATE VIEW TRAFODION.T132SCH.T132_GIANTS_GAMES AS - SELECT TRAFODION.T132SCH.T132_GAMES.GAME_NUMBER, - TRAFODION.T132SCH.T132_GAMES.GAME_TIME, - TRAFODION.T132SCH.T132_GAMES.GAME_LOCATION FROM - TRAFODION.T132SCH.T132_GAMES WHERE - TRAFODION.T132SCH.T132_GAMES.HOME_TEAM_NUMBER = 2 ORDER BY 1, 2, 3 ; - --- GRANT SELECT, DELETE, UPDATE, REFERENCES ON TRAFODION.T132SCH.T132_GIANTS_GAMES TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->>showddl t132_home_teams_games; - -CREATE VIEW TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES AS - SELECT T.TEAM_NUMBER, G.GAME_NUMBER, G.GAME_TIME FROM - TRAFODION.T132SCH.T132_TEAMS T, TRAFODION.T132SCH.T132_GAMES G WHERE - T.TEAM_NUMBER = G.HOME_TEAM_NUMBER ORDER BY 1, G.GAME_NUMBER, G.GAME_TIME - ; - --- GRANT SELECT, REFERENCES ON TRAFODION.T132SCH.T132_HOME_TEAMS_GAMES TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->>showddl sequence t132_team_number_sequence; - -CREATE SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE - START WITH 1 /* NEXT AVAILABLE VALUE 1 */ - INCREMENT BY 1 - MAXVALUE 9223372036854775806 - MINVALUE 1 - CACHE 25 - NO CYCLE - LARGEINT -; - --- GRANT USAGE ON SEQUENCE TRAFODION.T132SCH.T132_TEAM_NUMBER_SEQUENCE TO DB__ROOT WITH GRANT OPTION; - ---- SQL operation complete. ->> ->>invoke t132_games; - --- Definition of Trafodion table TRAFODION.T132SCH.T132_GAMES --- Definition current Tue Sep 27 10:19:11 2016 - - ( - 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 Tue Sep 27 10:19:11 2016 - - ( - 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 Tue Sep 27 10:19:11 2016 - - ( - 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 Tue Sep 27 10:19:11 2016 - - ( - TEAM_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE - , GAME_TIME TIMESTAMP(6) NO DEFAULT NOT NULL NOT - DROPPABLE - ) - ---- SQL operation complete. ->> ->>exit; - -End of MXCI Session - ->> ->>drop table t132_teams cascade; - ---- SQL operation complete. ->>drop table t132_games cascade; - ---- SQL operation complete. ->>drop sequence t132_team_number_sequence; - ---- SQL operation complete. ->> ->>obey TEST132(test_stats); ->>-- ================================================================= ->>-- run tests to make sure users that update statistics have correct ->>-- privileges. To update stats, you must: ->>-- be DB__ROOT ->>-- be table owner ->>-- have SELECT privilege ->>-- have the MANAGE_STATISTICS privilege (DB__ROOTROLE has priv) ->>-- ================================================================= ->> ->>set schema t132sch; - ---- SQL operation complete. ->>get tables; - -Tables in Schema TRAFODION.T132SCH -================================== - -SB_HISTOGRAMS -SB_HISTOGRAM_INTERVALS -SB_PERSISTENT_SAMPLES - ---- SQL operation complete. ->> ->>create table t132t1 (c1 int, c2 int); - ---- SQL operation complete. ->>create table t132t2 (c1 int, c2 int) attribute by sql_user1; - ---- SQL operation complete. ->>insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); - ---- 8 row(s) inserted. ->>insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); - ---- 8 row(s) inserted. ->> ->>get tables; - -Tables in Schema TRAFODION.T132SCH -================================== - -SB_HISTOGRAMS -SB_HISTOGRAM_INTERVALS -SB_PERSISTENT_SAMPLES -T132T1 -T132T2 - ---- SQL operation complete. ->>select count(*) from t132t1; +>>select count(*) from t132t1; (EXPR) -------------------- @@ -1934,7 +1513,10 @@ T132T2 --- 1 row(s) selected. >> >>-- update statistics as DB__ROOT ->>sh sqlci -i "TEST132(update_stats)"; +>>obey TEST132(update_stats); +>>set schema t132sch; + +--- SQL operation complete. >> >>update statistics for table t132t1 on every column; @@ -1943,15 +1525,14 @@ T132T2 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - >> ->>-- run as DB__ROOTROLE >>-- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default >>-- first show that sql_user2 cannot perform operations ->>sh sqlci -i "TEST132(update_stats)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(update_stats); +>>set schema t132sch; + +--- SQL operation complete. >> >>update statistics for table t132t1 on every column; @@ -1968,16 +1549,17 @@ End of MXCI Session --- SQL operation failed with errors. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; +>> +>>-- now show privileges after being granted DB__ROOTROLE role >>grant role DB__ROOTROLE to sql_user2; --- SQL operation complete. ->> ->>-- now show privileges after being granted DB__ROOTROLE role ->>sh sqlci -i "TEST132(update_stats)" -u sql_user2; +>>changeuser sql_user2; +>>obey TEST132(update_stats); +>>set schema t132sch; + +--- SQL operation complete. >> >>update statistics for table t132t1 on every column; @@ -1986,17 +1568,19 @@ End of MXCI Session --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>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 ->>sh sqlci -i "TEST132(show_update_stats)" -u sql_user1; +>>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; @@ -2013,39 +1597,35 @@ End of MXCI Session >>set schema t132sch; --- SQL operation complete. ->>log LOG132; >> >>showstats for table t132t1 on every column; Histogram data for Table TRAFODION.T132SCH.T132T1 -Table ID: 8170765222353678252 +Table ID: 3703791059232936033 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 208504770 8 8 8 SYSKEY - 208504767 8 8 8 C1 - 208504760 8 8 8 C2 +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: 8170765222353678398 +Table ID: 3703791059232936201 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 214964582 8 8 8 SYSKEY - 214964577 8 8 8 C1 - 214964572 8 8 8 C2 +1824908698 8 8 8 SYSKEY +1824908693 8 8 8 C1 +1824908688 8 8 8 C2 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser DB__ROOT; >> >>-- revoke SHOW privilege from public for the next set of tests >>get privileges on component sql_operations for "PUBLIC"; @@ -2074,7 +1654,12 @@ CREATE_SCHEMA >>get privileges on component sql_operations for sql_user3; --- SQL operation complete. ->>sh sqlci -i "TEST132(show_update_stats)" -u sql_user3; +>>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; @@ -2095,7 +1680,6 @@ CREATE_SCHEMA >>set schema t132sch; --- SQL operation complete. ->>log LOG132; >> >>showstats for table t132t1 on every column; @@ -2112,15 +1696,12 @@ CREATE_SCHEMA --- SQL operation failed with errors. >> ->>exit; - -End of MXCI Session - +>>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. ->> ->>-- now show privileges after being granted MANAGE_STATISTICS >>get privileges on component sql_operations for sql_user3; Privilege information on Component SQL_OPERATIONS for SQL_USER3 @@ -2129,7 +1710,12 @@ Privilege information on Component SQL_OPERATIONS for SQL_USER3 MANAGE_STATISTICS --- SQL operation complete. ->>sh sqlci -i "TEST132(show_update_stats)" -u sql_user3; +>>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; @@ -2142,39 +1728,35 @@ MANAGE_STATISTICS >>set schema t132sch; --- SQL operation complete. ->>log LOG132; >> >>showstats for table t132t1 on every column; Histogram data for Table TRAFODION.T132SCH.T132T1 -Table ID: 8170765222353678252 +Table ID: 3703791059232936033 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 208504771 8 8 8 SYSKEY - 208504766 8 8 8 C1 - 208504761 8 8 8 C2 +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: 8170765222353678398 +Table ID: 3703791059232936201 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 214964583 8 8 8 SYSKEY - 214964576 8 8 8 C1 - 214964573 8 8 8 C2 +1824908699 8 8 8 SYSKEY +1824908692 8 8 8 C1 +1824908689 8 8 8 C2 --- SQL operation complete. >> ->>exit; - -End of MXCI Session - +>>changeuser db__root; >>revoke component privilege MANAGE_STATISTICS on sql_operations from >>sql_user3; --- SQL operation complete. @@ -2184,7 +1766,11 @@ End of MXCI Session >> >>-- test showstats >>-- showstats should no longer work ->>sh sqlci -i "TEST132(show_stats)" -u sql_user3; +>>changeuser sql_user3; +>>obey TEST132(show_stats); +>>set schema t132sch; + +--- SQL operation complete. >> >>showstats for table t132t1 on every column; @@ -2200,15 +1786,18 @@ End of MXCI Session *** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2. --- SQL operation failed with errors. ->>exit; - -End of MXCI Session - +>>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 @@ -2223,18 +1812,22 @@ CREATE TABLE TRAFODION.T132SCH.T132T1 GRANT SELECT ON TRAFODION.T132SCH.T132T1 TO SQL_USER4; --- SQL operation complete. ->>sh sqlci -i "TEST132(show_stats)" -u sql_user4; +>>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: 8170765222353678252 +Table ID: 3703791059232936033 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 208504771 8 8 8 SYSKEY - 208504766 8 8 8 C1 - 208504761 8 8 8 C2 +1800623294 8 8 8 SYSKEY +1800623289 8 8 8 C1 +1800623284 8 8 8 C2 --- SQL operation complete. @@ -2245,10 +1838,7 @@ Table ID: 8170765222353678252 *** ERROR[9241] Insufficient privileges to perform the statistics request for table TRAFODION.T132SCH.T132T2. --- SQL operation failed with errors. ->>exit; - -End of MXCI Session - +>>changeuser db__root; >> >>-- testcase for trafodion-2188 fix >>create schema t132sch_private; @@ -2300,22 +1890,23 @@ Privilege information on Component SQL_OPERATIONS for SQL_USER3 MANAGE_STATISTICS --- SQL operation complete. ->>sh sqlci -i "TEST132(update_stats1)" -u sql_user3; +>>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. >> ->>exit; - -End of MXCI Session - ->>revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; +>>changeuser db__root; +>>set schema t132sch_private; --- SQL operation complete. ->>drop table t132t3 cascade; +>>revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; --- SQL operation complete. ->>drop schema t132sch_private cascade; +>>cleanup schema t132sch_private; --- SQL operation complete. >>set schema t132sch; @@ -2346,4 +1937,8 @@ SHOW --- SQL operation complete. >> +>>get tables, match 'T132%'; + +--- SQL operation complete. +>> >>log; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/regress/privs1/TEST132 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs1/TEST132 b/core/sql/regress/privs1/TEST132 index d303510..58d22b9 100755 --- a/core/sql/regress/privs1/TEST132 +++ b/core/sql/regress/privs1/TEST132 @@ -36,8 +36,8 @@ -- <operation>_<type> - runs tests for an operation by a user -- ============================================================================ -cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; obey TEST132(clean_up); +cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; obey TEST132(set_up); log LOG132 clear; obey TEST132(test_libraries); @@ -77,23 +77,31 @@ get libraries; get privileges on component sql_operations for "PUBLIC"; -- succeed: DB__ROOT can create a library -sh sqlci -i "TEST132(manage_library)"; +obey TEST132(manage_library); -- fail: sql_user1 cannot create a library -sh sqlci -i "TEST132(manage_library)" -u sql_user1; +changeuser sql_user1; +obey TEST132(manage_library); +changeuser db__root; -- succeed: grant DB__ROOTROLE to sql_user1 grant role DB__ROOTROLE to sql_user1; -sh sqlci -i "TEST132(manage_library)" -u sql_user1; +changeuser sql_user1; +obey TEST132(manage_library); +changeuser DB__ROOT; -- fail: just grant the create privilege grant component privilege CREATE_LIBRARY on sql_operations to sql_user2; -sh sqlci -i "TEST132(manage_library)" -u sql_user2; +changeuser sql_user2; +obey TEST132(manage_library); +changeuser DB__ROOT; -- succeed: now grant the manage_library privilege grant component privilege MANAGE_LIBRARY on sql_operations to sql_user2; get privileges on component sql_operation for sql_user2; -sh sqlci -i "TEST132(manage_library)" -u sql_user2; +changeuser sql_user2; +obey TEST132(manage_library); +changeuser DB__ROOT; -- reset revoke role DB__ROOTROLE from sql_user1; @@ -104,7 +112,6 @@ get privileges on component sql_operations for "PUBLIC"; ?section manage_library set schema t132sch; -log LOG132; get libraries in schema t132sch; create library t132_l1 file 'etest132.dll'; get libraries in schema t132sch; @@ -121,43 +128,42 @@ get libraries in schema t132sch; -- ================================================================= set schema t132sch; -set parserflags 1; -set parserflags 131072; -cqd DDL_TRANSACTIONS 'ON'; +--set parserflags 131072; +--cqd DDL_TRANSACTIONS 'ON'; -get tables; +get tables, match '%T132%'; create table t132t1 (c1 int not null primary key, c2 int); create index t132t1_ndx1 on t132t1 (c2) no populate; create table t132t2 (c1 int not null primary key, c2 int) attribute by sql_user1; create index t132t2_ndx1 on t132t2(c2) no populate; -create table t132t3 (c1 int not null primary key, c2 int) - attribute by sql_user1; -create index t132t3_ndx1 on t132t3(c2) no populate; insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); -insert into t132t3 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); -get tables; +get tables, match '%T132%'; +cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; showddl t132t1; showddl t132t2; -showddl t132t3; -- DB__ROOT can populate indexes -sh sqlci -i "TEST132(populate_index)"; +obey TEST132(populate_index); obey TEST132(popindex_check_reset); -- object owner can populate --- sql_user1 owns t132t2 and t132t3 but not t132t1 +-- sql_user1 owns t132t2 but not t132t1 -- popindex fails for t132t1 but works for the rest -sh sqlci -i "TEST132(populate_index)" -u sql_user1; +changeuser sql_user1; +obey TEST132(populate_index); +changeuser db__root; obey TEST132(popindex_check_reset); -- if user belongs to DB__ROOTROLE, has DML privileges, so can populate indexes grant role DB__ROOTROLE to sql_user2; -sh sqlci -i "TEST132(populate_index)" -u sql_user2; +changeuser sql_user2; +obey TEST132(populate_index); +changeuser db__root; obey TEST132(popindex_check_reset); revoke role DB__ROOTROLE from sql_user2; @@ -165,38 +171,33 @@ revoke role DB__ROOTROLE from sql_user2; -- only t132t2 has granted both privileges grant SELECT on t132t1 to sql_user3; grant SELECT, INSERT on t132t2 to sql_user3 by sql_user1; -grant INSERT on t132t3 to sql_user3 by sql_user1; -sh sqlci -i "TEST132(populate_index)" -u sql_user3; +changeuser sql_user3; +obey TEST132(populate_index); +changeuser db__root; obey TEST132(popindex_check_reset); -- reset drop table t132t1 cascade; drop table t132t2 cascade; -drop table t132t3 cascade; -get tables; +get tables, match 'T132%'; ?section populate_index set schema t132sch; -log LOG132; populate index t132t1_ndx1 on t132t1;; populate index t132t2_ndx1 on t132t2; -populate index t132t3_ndx1 on t132t3; ?section popindex_check_reset set schema t132sch; -log LOG132; +set parserflags 1; select count(*) from table (index_table t132t1_ndx1); select count(*) from table (index_table t132t2_ndx1); -select count(*) from table (index_table t132t3_ndx1); -drop index t132t1_ndx1; +cleanup index t132t1_ndx1; create index t132t1_ndx1 on t132t1 (c2) no populate; drop index t132t2_ndx1; create index t132t2_ndx1 on t132t2 (c2) no populate; -drop index t132t3_ndx1; -create index t132t3_ndx1 on t132t3 (c2) no populate; ?section test_show -- ================================================================= @@ -275,29 +276,40 @@ get privileges on component sql_operations for "PUBLIC"; obey TEST132(show_objects); -- sql_user1 owns some of the objects but not all -sh sqlci -i "TEST132(show_objects)" -u sql_user1; +changeuser sql_user1; +obey TEST132(show_objects); -- sql_user2 get privileges through DB__ROOTROLE role SHOW privilege -- first illustrate that sql_user2 has no privileges -sh sqlci -i "TEST132(show_objects)" -u sql_user2; +changeuser sql_user2; +obey TEST132(show_objects); +changeuser db__root; grant role DB__ROOTROLE to sql_user2; -- now sql_user2 has privileges with the grant -sh sqlci -i "TEST132(show_objects)" -u sql_user2; +changeuser sql_user2; +obey TEST132(show_objects); +changeuser db__root; revoke role DB__ROOTROLE from sql_user2; -- sql_user3 gets some privileges through SELECT grant +set schema t132sch; grant SELECT on t132_teams to sql_user3; -sh sqlci -i "TEST132(show_objects)" -u sql_user3; +changeuser sql_user3; +obey TEST132(show_objects); +changeuser db__root; +set schema t132sch; +revoke select on t132_teams from sql_user3; -- regrant the show privs - everyone has privs get privileges on component sql_operations for "PUBLIC"; grant component privilege "SHOW" on sql_operations to "PUBLIC"; get privileges on component sql_operations for "PUBLIC"; -sh sqlci -i "TEST132(show_objects)" -u sql_user1; -sh sqlci -i "TEST132(show_objects)" -u sql_user2; -sh sqlci -i "TEST132(show_objects)" -u sql_user3; +changeuser sql_user1; +obey TEST132(show_objects); +changeuser db__root; +set schema t132sch; drop table t132_teams cascade; drop table t132_games cascade; drop sequence t132_team_number_sequence; @@ -305,7 +317,6 @@ drop sequence t132_team_number_sequence; ?section show_objects set schema t132sch; -log LOG132; cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; showddl t132_games; @@ -330,33 +341,38 @@ invoke t132_home_teams_games; -- ================================================================= set schema t132sch; -get tables; +get tables, match '%T132%'; create table t132t1 (c1 int, c2 int); create table t132t2 (c1 int, c2 int) attribute by sql_user1; insert into t132t1 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); insert into t132t2 values (1,1), (2,2), (3,3),(4,4), (5,5),(6,6), (7,7),(8,8); -get tables; +get tables, match '%T132%'; select count(*) from t132t1; select count(*) from t132t2; -- update statistics as DB__ROOT -sh sqlci -i "TEST132(update_stats)"; +obey TEST132(update_stats); --- run as DB__ROOTROLE -- DB__ROOTROLE is granted MANAGE_STATISTICS privilege by default -- first show that sql_user2 cannot perform operations -sh sqlci -i "TEST132(update_stats)" -u sql_user2; -grant role DB__ROOTROLE to sql_user2; +changeuser sql_user2; +obey TEST132(update_stats); +changeuser db__root; -- now show privileges after being granted DB__ROOTROLE role -sh sqlci -i "TEST132(update_stats)" -u sql_user2; +grant role DB__ROOTROLE to sql_user2; +changeuser sql_user2; +obey TEST132(update_stats); +changeuser DB__ROOT; revoke role DB__ROOTROLE from sql_user2; -- run as table owner, sql_user1 owns one table -- update stats only works for t132t2, showstats works on both tables -sh sqlci -i "TEST132(show_update_stats)" -u sql_user1; +changeuser sql_user1; +obey TEST132(show_update_stats); +changeuser DB__ROOT; -- revoke SHOW privilege from public for the next set of tests get privileges on component sql_operations for "PUBLIC"; @@ -366,23 +382,33 @@ get privileges on component sql_operations for "PUBLIC"; -- Run with MANAGE_STATISTICS and no SHOW -- first illustrate that sql_user3 has no privs get privileges on component sql_operations for sql_user3; -sh sqlci -i "TEST132(show_update_stats)" -u sql_user3; -grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3; +changeuser sql_user3; +obey TEST132(show_update_stats); +changeuser db__root; -- now show privileges after being granted MANAGE_STATISTICS +grant component privilege MANAGE_STATISTICS on sql_operations to sql_user3; get privileges on component sql_operations for sql_user3; -sh sqlci -i "TEST132(show_update_stats)" -u sql_user3; +changeuser sql_user3; +obey TEST132(show_update_stats); +changeuser db__root; revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; get privileges on component sql_operations for sql_user3; -- test showstats -- showstats should no longer work -sh sqlci -i "TEST132(show_stats)" -u sql_user3; +changeuser sql_user3; +obey TEST132(show_stats); +changeuser db__root; -- grant select to allow showstats to work +set schema t132sch; grant SELECT on t132t1 to sql_user4; +cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON'; showddl t132t1; -sh sqlci -i "TEST132(show_stats)" -u sql_user4; +changeuser sql_user4; +obey TEST132(show_stats); +changeuser db__root; -- testcase for trafodion-2188 fix create schema t132sch_private; @@ -410,10 +436,12 @@ 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; +changeuser sql_user3; +obey TEST132(update_stats1); +changeuser db__root; +set schema t132sch_private; revoke component privilege MANAGE_STATISTICS on sql_operations from sql_user3; -drop table t132t3 cascade; -drop schema t132sch_private cascade; +cleanup schema t132sch_private; set schema t132sch; -- reset @@ -424,25 +452,24 @@ get privileges on component sql_operations for "PUBLIC"; drop table t132t1; drop table t132t2; +get tables, match 'T132%'; + ?section show_update_stats obey TEST132(update_stats); obey TEST132(show_stats); ?section update_stats set schema t132sch; -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; showstats for table t132t1 on every column; showstats for table t132t2 on every column; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlci/SqlCmd.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/SqlCmd.cpp b/core/sql/sqlci/SqlCmd.cpp index 30a273c..bb79ff4 100644 --- a/core/sql/sqlci/SqlCmd.cpp +++ b/core/sql/sqlci/SqlCmd.cpp @@ -2826,6 +2826,7 @@ short SqlCmd::deallocate(SqlciEnv * sqlci_env, PrepStmt * prep_stmt) if (prep_stmt) delete prep_stmt; + prep_stmt = NULL; return retcode; } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlci/SqlciCmd.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/SqlciCmd.h b/core/sql/sqlci/SqlciCmd.h index 0fb475e..3e37218 100644 --- a/core/sql/sqlci/SqlciCmd.h +++ b/core/sql/sqlci/SqlciCmd.h @@ -61,7 +61,8 @@ public: MODE_TYPE, QUERYID_TYPE, SET_ISO_MAPPING_TYPE, SET_DEFAULT_CHARSET_TYPE, - SET_INFER_CHARSET_TYPE + SET_INFER_CHARSET_TYPE, + USER_TYPE }; private: @@ -285,6 +286,12 @@ public: short process(SqlciEnv * sqlci_env); }; +class ChangeUser : public SqlciCmd { +public: + ChangeUser(char *, Lng32 argLen_); + short process(SqlciEnv * sqlci_env); +}; + class Exit : public SqlciCmd { public: Exit(char *, Lng32 arglen_); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlci/SqlciEnv.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/SqlciEnv.cpp b/core/sql/sqlci/SqlciEnv.cpp index 417cff5..53da9a3 100644 --- a/core/sql/sqlci/SqlciEnv.cpp +++ b/core/sql/sqlci/SqlciEnv.cpp @@ -1222,6 +1222,21 @@ short Env::process(SqlciEnv *sqlci_env) return 0; } +//////////////////////////////////////// +// Processing of the ChangeUser command. +//////////////////////////////////////// + +ChangeUser::ChangeUser(char * argument_, Lng32 argLen_) + : SqlciCmd(SqlciCmd::USER_TYPE, argument_, argLen_) +{} + +short ChangeUser::process (SqlciEnv * sqlci_env) +{ + sqlci_env->setUserNameFromCommandLine(get_argument()); + sqlci_env->setUserIdentityInCLI(); + return 0; +} + void SqlciEnv::getDefaultCatAndSch (ComAnsiNamePart & defaultCat, ComAnsiNamePart & defaultSch) { defaultCatAndSch_ = new ComSchemaName; @@ -1355,15 +1370,30 @@ void SqlciEnv::setUserIdentityInCLI() specialError_ = 0; HandleCLIErrorInit(); - - Lng32 sqlcode = - SQL_EXEC_SetSessionAttr_Internal(SESSION_DATABASE_USER_NAME, - 0, - (char *) userNameFromCommandLine_.data()); + Lng32 sqlcode = 0; + + // get the authID (same as sessionID) + NAString externalName("DB__ROOT"); + NAString databaseName("DB__ROOT"); + Int32 userID(33333); + Int32 sessionID(33333); + SQL_EXEC_SetParserFlagsForExSqlComp_Internal(0x20000); + sqlcode = SQL_EXEC_GetAuthID(userNameFromCommandLine_.data(), userID); + SQL_EXEC_ResetParserFlagsForExSqlComp_Internal(0x20000); HandleCLIError(sqlcode, this); - + sessionID = userID; if (sqlcode >= 0) - printf("\nDatabase user: %s\n\n", userNameFromCommandLine_.data()); + { + printf("\nDatabase user: %s\n", userNameFromCommandLine_.data()); + externalName = userNameFromCommandLine_; + databaseName = userNameFromCommandLine_; + } + + SQL_EXEC_SetParserFlagsForExSqlComp_Internal(0x20000); + sqlcode = SQL_EXEC_SetAuthID(externalName.data(), databaseName.data(), + NULL, 0, userID, sessionID); + SQL_EXEC_ResetParserFlagsForExSqlComp_Internal(0x20000); + HandleCLIError(sqlcode, this); if (sqlcode != 0) SQL_EXEC_ClearDiagnostics(NULL); @@ -1372,11 +1402,6 @@ void SqlciEnv::setUserIdentityInCLI() } else { - // Call CLI to retrieve the current user identity. This is only - // done to see if CLI generates errors or warnings that we should - // display. For example, CLI was not able to establish a default - // user identity, perhaps metadata is corrupt, we should display - // that information. Int32 uid = 0; getDatabaseUserID(uid); } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlci/sqlci_lex.ll ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/sqlci_lex.ll b/core/sql/sqlci/sqlci_lex.ll index afca706..0e22666 100755 --- a/core/sql/sqlci/sqlci_lex.ll +++ b/core/sql/sqlci/sqlci_lex.ll @@ -347,6 +347,7 @@ B [ \t\n]+ [Mm][Vv][Ll][Oo][Gg] return_IDENT_or_TOKEN(MVLOG, 0); [Uu][Nn][Ll][Oo][Aa][Dd] return_IDENT_or_TOKEN(UNLOAD, 0); [Tt][Rr][Uu][Nn][Cc][Aa][Tt][Ee] return_IDENT_or_TOKEN(TRUNCATE, 0); +[Cc][Hh][Aa][Nn][Gg][Ee][Uu][Ss][Ee][Rr] return_IDENT_or_TOKEN(USERtoken, 0); [\*] {SqlciParse_IdentifierExpected = 0; return(ALLtoken);}; [(] {SqlciParse_IdentifierExpected = 0; return(LPAREN);}; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlci/sqlci_yacc.y ---------------------------------------------------------------------- diff --git a/core/sql/sqlci/sqlci_yacc.y b/core/sql/sqlci/sqlci_yacc.y index 95a37b3..6f89f00 100644 --- a/core/sql/sqlci/sqlci_yacc.y +++ b/core/sql/sqlci/sqlci_yacc.y @@ -483,6 +483,7 @@ static char * FCString (const char *idString, int isFC) %token UNLOCK %token UPD_STATS %token UPD_HIST_STATS +%token USERtoken %token USING %token TABLE %token VALUES @@ -676,6 +677,16 @@ sqlci_cmd : MODE SQL { $$ = new Env(0,0); } + | USERtoken IDENTIFIER + { + char userName[strlen($2)+1]; + for (size_t i=0; i < strlen($2); i++) + { + userName[i] = toupper($2[i]); + } + userName[strlen($2)] = 0; + $$ = new ChangeUser(userName, strlen(userName)); + } | REPEAT { // "!" command, a la SQL/MP aRepeat (0,0); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/CmpDDLCatErrorCodes.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpDDLCatErrorCodes.h b/core/sql/sqlcomp/CmpDDLCatErrorCodes.h index 3c9d1f2..2a3dab0 100644 --- a/core/sql/sqlcomp/CmpDDLCatErrorCodes.h +++ b/core/sql/sqlcomp/CmpDDLCatErrorCodes.h @@ -38,7 +38,7 @@ enum CatErrorCode { CAT_FIRST_ERROR = 1000 , CAT_SCHEMA_DOES_NOT_EXIST_ERROR = 1003 , CAT_TABLE_DOES_NOT_EXIST_ERROR = 1004 , CAT_CONSTRAINT_DOES_NOT_EXIST_ERROR = 1005 - // unused = 1006 + , CAT_WARN_USED_AUTHID = 1006 , CAT_WGO_NOT_ALLOWED = 1007 , CAT_AUTHID_DOES_NOT_EXIST_ERROR = 1008 , CAT_COLUMN_DOES_NOT_EXIST_ERROR = 1009 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp b/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp index 8cf1cf5..f15c113 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLauth.cpp @@ -339,27 +339,80 @@ NAString CmpSeabaseDDLauth::getObjectName (const std::vector <int64_t> objectUID // ---------------------------------------------------------------------------- -// method: getUniqueID +// method: getUniqueAuthID // -// This method is not valid for the base class -// -// Input: none +// Return an unused auth ID between the requested ranges +// Input: +// minValue - the lowest value +// maxValue - the highest value // -// Output: populates diag area, throws exception. +// Output: unique ID to use +// exception is generated if unable to generate a unique value // ---------------------------------------------------------------------------- -Int32 CmpSeabaseDDLauth::getUniqueID() +Int32 CmpSeabaseDDLauth::getUniqueAuthID( + const Int32 minValue, + const Int32 maxValue) { - - SEABASEDDL_INTERNAL_ERROR("CmpSeabaseDDLauth::getUniqueID"); + Int32 newUserID = 0; + char buf[300]; + Int32 len = snprintf(buf, 300, + "SELECT [FIRST 1] auth_id FROM (SELECT auth_id, " + "LEAD(auth_id) OVER (ORDER BY auth_id) L FROM %s.%s ) " + "WHERE L - auth_id > 1 and auth_id >= %d ", + MDSchema_.data(),SEABASE_AUTHS, minValue); + assert (len <= 300); + + len = 0; + Int64 metadataValue = 0; + bool nullTerminate = false; + + ExeCliInterface cliInterface(STMTHEAP); + Lng32 cliRC = cliInterface.executeImmediate(buf, (char *)&metadataValue, &len, nullTerminate); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + return 0; + } -UserException excp(NULL,0); + // We have lots of available ID's. Don't expect to run out of ID's for awhile + if (cliRC == 100 || metadataValue > maxValue) + { + SEABASEDDL_INTERNAL_ERROR("CmpSeabaseDDLauth::getUniqueAuthID failed, ran out of available IDs"); + UserException excp (NULL, 0); + throw excp; + } - throw excp; - - return 0; - + newUserID = (Int32)metadataValue; + if (newUserID == 0) + newUserID = ROOT_USER_ID + 1; + else + newUserID++; + + // There is a bug where grants are not being removed from component privileges + // when a user is dropped. So if this authID still shows up as a component + // privilege grantee go ahead a cleanup the inconsistency. + std::string privMDLoc(CmpSeabaseDDL::getSystemCatalogStatic().data()); + privMDLoc += std::string(".\"") + + std::string(SEABASE_PRIVMGR_SCHEMA) + + std::string("\""); + + PrivMgrComponentPrivileges componentPrivs(privMDLoc,CmpCommon::diags()); + if (componentPrivs.isAuthIDGrantedPrivs(newUserID)) + { + if (!componentPrivs.dropAllForGrantee(newUserID)) + { + *CmpCommon::diags() << DgSqlCode(CAT_WARN_USED_AUTHID) + << DgInt0(newUserID); + + Int32 newMinValue = newUserID+1; + newUserID = getUniqueAuthID(newUserID + 1, maxValue); + } + } + + return newUserID; } + // ---------------------------------------------------------------------------- // method: isAuthNameReserved // @@ -448,6 +501,132 @@ bool CmpSeabaseDDLauth::isUserID(Int32 authID) } // ---------------------------------------------------------------------------- +// method: isSystemAuth +// +// Checks the list of authorization IDs to see if the passed in authName is a +// system auth. This replaces checks for reserved names. +// +// isSpecialAuth indicates a system auth but it is not defined in the metadata +// +// Returns: +// true - is a system auth +// false - is not a system auth +// ---------------------------------------------------------------------------- +bool CmpSeabaseDDLauth::isSystemAuth( + const ComIdClass authType, + const NAString &authName, + bool &isSpecialAuth) +{ + bool isSystem = false; + switch (authType) + { + case COM_ROLE_CLASS: + { + int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemAuthsStruct); + for (int32_t i = 0; i < numberRoles; i++) + { + const SystemAuthsStruct &roleDefinition = systemRoles[i]; + if (roleDefinition.authName == authName) + { + isSystem = true; + isSpecialAuth = roleDefinition.isSpecialAuth; + break; + } + } + break; + } + + case COM_USER_CLASS: + { + // Verify name is a standard name + std::string authNameStr(authName.data()); + size_t prefixLength = strlen(RESERVED_AUTH_NAME_PREFIX); + if (authNameStr.size() <= prefixLength || + authNameStr.compare(0,prefixLength,RESERVED_AUTH_NAME_PREFIX) == 0) + isSystem = true; + break; + } + + default: + { + // should never get here - assert? + isSystem = false; + } + } + return isSystem; +} + +// ---------------------------------------------------------------------------- +// protected method: createStandardAuth +// +// Inserts a standard user or role in the Trafodion metadata +// The authType needs to be set up before calling +// +// Input: +// authName +// authID +// ---------------------------------------------------------------------------- +bool CmpSeabaseDDLauth::createStandardAuth( + const std::string authName, + const int32_t authID) +{ + // check to see if authName is a system object + bool isSpecialAuth = false; + bool isSystem = isSystemAuth(getAuthType(), NAString(authName.c_str()), isSpecialAuth); + + // since this is being called by internal code, should not be trying to + // create non system object (isSystemAuth) or object that should not be + // registered in the metadata (isSpecialAuth), return internal error + if (!isSystem || isSpecialAuth) + { + NAString errorMsg ("Invalid system authorization identifier for "); + errorMsg += getAuthType() == COM_ROLE_CLASS ? "role " : "user "; + errorMsg += authName.c_str(); + SEABASEDDL_INTERNAL_ERROR(errorMsg.data()); + return false; + } + + setAuthDbName(authName.c_str()); + setAuthExtName(authName.c_str()); + setAuthValid(true); // assume a valid authorization ID + + Int64 createTime = NA_JulianTimestamp(); + setAuthCreateTime(createTime); + setAuthRedefTime(createTime); // make redef time the same as create time + + // Make sure authorization ID has not already been registered + if (authExists(getAuthDbName(),false)) + return false; + + try + { + Int32 minAuthID = isRole() ? MIN_ROLEID : MIN_USERID; + Int32 maxAuthID = isRole() ? MAX_ROLEID : MAX_USERID; + + Int32 newAuthID = (authID == NA_UserIdDefault) ? getUniqueAuthID(minAuthID, maxAuthID) : authID; + if (isRole()) + assert(isRoleID(newAuthID)); + else if (isUser()) + assert (isUserID(newAuthID)); + + setAuthID(newAuthID); + setAuthCreator(ComUser::getRootUserID()); + + // Add the role to AUTHS table + insertRow(); + } + + catch (...) + { + // At this time, an error should be in the diags area. + // If there is no error, set up an internal error + if (CmpCommon::diags()->getNumber(DgSqlCode::ERROR_) == 0) + SEABASEDDL_INTERNAL_ERROR("Unexpected error in CmpSeabaseDDLuser::createStandardAuth"); + } + return true; +} + +//----------------------------------------------------------------------------- // Methods that perform metadata access // // All methods return a UserException if an unexpected error occurs @@ -783,43 +962,6 @@ CmpSeabaseDDLuser::getUserDetails(const char *pUserName, bool isExternal) } // ---------------------------------------------------------------------------- -// method: getUniqueID -// -// This method returns a unique user ID -// -// Input: none -// -// Output: returns a unique user ID -// ---------------------------------------------------------------------------- -Int32 CmpSeabaseDDLuser::getUniqueID() -{ - Int32 newUserID = 0; - char userIDString[MAX_AUTHID_AS_STRING_LEN]; - - NAString whereClause ("where auth_id >= "); - sprintf(userIDString,"%d",MIN_USERID); - whereClause += userIDString; - whereClause += " and auth_id < "; - sprintf(userIDString, "%d", MAX_USERID); - whereClause += userIDString; - - newUserID = selectMaxAuthID(whereClause); - // DB__ROOT should always be registered as MIN_USERID. Just in case ... - if (newUserID == 0) - newUserID = MIN_USERID + 1; - else - newUserID++; - - // We have 966,667 available ID's. Don't expect to run out of ID's for awhile - // but if/when we do, the algorithm needs to change. Can reuse ID's for users - // that were unregistered. - if (newUserID >= MAX_USERID) - SEABASEDDL_INTERNAL_ERROR("CmpSeabaseDDLrole::getUniqueID failed, ran out of available IDs"); - - return newUserID; -} - -// ---------------------------------------------------------------------------- // Public method: registerUser // // registers a user in the Trafodion metadata @@ -901,7 +1043,8 @@ DBUserAuth::AuthenticationConfiguration foundConfigurationNumber = DBUserAuth::D return; // Get a unique auth ID number - Int32 userID = getUniqueID(); + Int32 userID = getUniqueAuthID(MIN_USERID, MAX_USERID); + assert(isUserID(userID)); setAuthID (userID); // get effective user from the Context @@ -953,7 +1096,7 @@ DBUserAuth::AuthenticationConfiguration foundConfigurationNumber = DBUserAuth::D // At this time, an error should be in the diags area. // If there is no error, set up an internal error if (CmpCommon::diags()->getNumber(DgSqlCode::ERROR_) == 0) - SEABASEDDL_INTERNAL_ERROR("Switch statement in CmpSeabaseDDLuser::registerUser"); + SEABASEDDL_INTERNAL_ERROR("Unexpected error in CmpSeabaseDDLuser::registerUser"); } } @@ -1073,6 +1216,15 @@ void CmpSeabaseDDLuser::unregisterUser(StmtDDLRegisterUser * pNode) } } + // remove any component privileges granted to this user + PrivMgrComponentPrivileges componentPrivileges(privMgrMDLoc.data(),CmpCommon::diags()); + std::string componentUIDString = "1"; + if (!componentPrivileges.dropAllForGrantee(getAuthID())) + { + UserException excp (NULL, 0); + throw excp; + } + // delete the row deleteRow(getAuthDbName()); } @@ -1182,6 +1334,23 @@ void CmpSeabaseDDLuser::alterUser (StmtDDLAlterUser * pNode) } } +// ---------------------------------------------------------------------------- +// method: registerStandardUser +// +// Creates a standard user ie. (DB__ROOT) in the Trafodion metadata +// +// Input: +// authName +// authID +// ---------------------------------------------------------------------------- +void CmpSeabaseDDLuser::registerStandardUser( + const std::string authName, + const int32_t authID) +{ + setAuthType(COM_USER_CLASS); // we are a user + createStandardAuth(authName, authID); +} + // ----------------------------------------------------------------------------- // * * // * Function: validateExternalUsername * @@ -1457,7 +1626,8 @@ void CmpSeabaseDDLrole::createRole(StmtDDLCreateRole * pNode) return; // Get a unique role ID number - Int32 roleID = getUniqueID(); //TODO: add role support + Int32 roleID = getUniqueAuthID(MIN_ROLEID, MAX_ROLEID); //TODO: add role support + assert (isRoleID(roleID)); setAuthID(roleID); std::string creatorUsername; @@ -1537,47 +1707,12 @@ void CmpSeabaseDDLrole::createRole(StmtDDLCreateRole * pNode) bool CmpSeabaseDDLrole::createStandardRole( const std::string roleName, const int32_t roleID) - { - - // Verify name is a standard name - - size_t prefixLength = strlen(RESERVED_AUTH_NAME_PREFIX); - - if (roleName.size() <= prefixLength || - roleName.compare(0,prefixLength,RESERVED_AUTH_NAME_PREFIX) != 0) - { - *CmpCommon::diags() << DgSqlCode(-CAT_ROLE_NOT_EXIST) - << DgString0(roleName.data()); - return false; - } - - setAuthDbName(roleName.c_str()); - setAuthExtName(roleName.c_str()); - setAuthType(COM_ROLE_CLASS); // we are a role - setAuthValid(true); // assume a valid role - - Int64 createTime = NA_JulianTimestamp(); - setAuthCreateTime(createTime); - setAuthRedefTime(createTime); // make redef time the same as create time - - // Make sure role has not already been registered - if (authExists(getAuthDbName(),false)) - return false; - - Int32 newRoleID = (roleID == NA_UserIdDefault) ? getUniqueID() : roleID; - setAuthID(newRoleID); - setAuthCreator(ComUser::getRootUserID()); - -// Add the role to AUTHS table - insertRow(); - - return true; - + setAuthType(COM_ROLE_CLASS); // we are a role + return createStandardAuth(roleName, roleID); } - // ----------------------------------------------------------------------------- // public method: describe // @@ -1938,42 +2073,6 @@ CmpSeabaseDDLauth::AuthStatus authStatus = getAuthDetails(roleName,false); // ---------------------------------------------------------------------------- -// method: getUniqueID -// -// This method returns a unique role ID -// -// Input: none -// -// Output: returns a unique role ID -// ---------------------------------------------------------------------------- -Int32 CmpSeabaseDDLrole::getUniqueID() -{ - Int32 newRoleID = 0; - char roleIDString[MAX_AUTHID_AS_STRING_LEN]; - - NAString whereClause ("where auth_id >= "); - sprintf(roleIDString,"%d",MIN_ROLEID); - whereClause += roleIDString; - whereClause += " and auth_id < "; - sprintf(roleIDString, "%d", MAX_ROLEID_RANGE1); - whereClause += roleIDString; - - newRoleID = selectMaxAuthID(whereClause); - if (newRoleID == 0) - newRoleID = ROOT_ROLE_ID + 1; - else - newRoleID++; - - // We have 490000 available ID's. Don't expect to run out of ID's for awhile - // but if/when we do, the algorithm needs to change. Can reuse ID's for roles - // that were dropped. - if (newRoleID >= MAX_ROLEID_RANGE1) - SEABASEDDL_INTERNAL_ERROR("CmpSeabaseDDLrole::getUniqueID failed, ran out of available IDs"); - - return newRoleID; -} - -// ---------------------------------------------------------------------------- // method: verifyAuthority // // makes sure user has privilege to perform role operation http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/CmpSeabaseDDLauth.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLauth.h b/core/sql/sqlcomp/CmpSeabaseDDLauth.h index b95e8be..a340f3e 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLauth.h +++ b/core/sql/sqlcomp/CmpSeabaseDDLauth.h @@ -92,6 +92,11 @@ class CmpSeabaseDDLauth bool isPublic() const { return authID_ == PUBLIC_USER; } bool isRole() const { return authType_ == COM_ROLE_CLASS; } bool isUser() const { return authType_ == COM_USER_CLASS; } + bool isSystemAuth( + const ComIdClass authType, + const NAString &authName, + bool &specialAuth); + static bool isRoleID(Int32 authID); static bool isUserID(Int32 authID); @@ -100,7 +105,7 @@ class CmpSeabaseDDLauth bool isAuthNameReserved (const NAString &authName); bool isAuthNameValid (const NAString &authName); - virtual Int32 getUniqueID (void); + Int32 getUniqueAuthID (const Int32 minValue, const Int32 maxValue); // mutators void setAuthCreator (const Int32 authCreator) @@ -120,6 +125,10 @@ class CmpSeabaseDDLauth void setAuthValid (bool isValid) {authValid_ = isValid;} + bool createStandardAuth ( + const std::string authName, + const int32_t authID); + // metadata access methods void deleteRow (const NAString &authName); void insertRow (void); @@ -165,7 +174,10 @@ class CmpSeabaseDDLuser : public CmpSeabaseDDLauth void alterUser(StmtDDLAlterUser * pNode); void registerUser(StmtDDLRegisterUser * pNode); void unregisterUser(StmtDDLRegisterUser * pNode); - + void registerStandardUser( + const std::string userName, + const int32_t userID); + CmpSeabaseDDLauth::AuthStatus getUserDetails(const char *pUserName, bool isExternal = false); CmpSeabaseDDLauth::AuthStatus getUserDetails(Int32 userID); @@ -174,7 +186,6 @@ class CmpSeabaseDDLuser : public CmpSeabaseDDLauth protected: - Int32 getUniqueID (void); void verifyAuthority(bool isRemapUser = false); }; @@ -216,7 +227,6 @@ class CmpSeabaseDDLrole : public CmpSeabaseDDLauth protected: - Int32 getUniqueID (void); void verifyAuthority (void); };
