You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2017/10/26 17:23:40 UTC
[2/3] incubator-trafodion git commit: Miscellaneous authorization
changes:
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 <rm...@edev07.esgyn.local>
Authored: Mon Oct 23 16:13:00 2017 +0000
Committer: Roberta Marton <rm...@edev07.esgyn.local>
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);
};