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:39 UTC
[1/3] incubator-trafodion git commit: Miscellaneous authorization
changes:
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 6b07d620e -> 9e5f36cd3
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
index 70d37d1..cc1e4a5 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
+++ b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
@@ -7167,15 +7167,12 @@ short CmpSeabaseDDL::updateSeabaseAuths(
Int64 initTime = NA_JulianTimestamp();
- str_sprintf(buf, "insert into %s.\"%s\".%s values (%d, 'DB__ROOT', 'TRAFODION', 'U', %d, 'Y', %ld,%ld, 0) ",
- sysCat, SEABASE_MD_SCHEMA, SEABASE_AUTHS,
- SUPER_USER, SUPER_USER, initTime, initTime);
- cliRC = cliInterface->executeImmediate(buf);
- if (cliRC < 0)
- {
- cliInterface->retrieveSQLDiagnostics(CmpCommon::diags());
- return -1;
- }
+ NAString mdLocation;
+ CONCAT_CATSCH(mdLocation, getSystemCatalog(), SEABASE_MD_SCHEMA);
+ CmpSeabaseDDLuser authOperation(sysCat, mdLocation.data());
+ authOperation.registerStandardUser(DB__ROOT, ROOT_USER_ID);
+ if (CmpCommon::diags()->getNumber(DgSqlCode::ERROR_))
+ return -1;
return 0;
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/PrivMgrComponentDefs.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrComponentDefs.h b/core/sql/sqlcomp/PrivMgrComponentDefs.h
new file mode 100644
index 0000000..8986dd9
--- /dev/null
+++ b/core/sql/sqlcomp/PrivMgrComponentDefs.h
@@ -0,0 +1,284 @@
+//*****************************************************************************
+// @@@ START COPYRIGHT @@@
+//
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+//
+//// @@@ END COPYRIGHT @@@
+//*****************************************************************************
+
+#ifndef PRIVMGR_COMPONENTS_DEFS_H
+#define PRIVMGR_COMPONENTS_DEFS_H
+
+// *****************************************************************************
+// *
+// * Component definition section
+// *
+// * Several system components are created and managed by the database.
+// * They are managed by two main structures:
+// * ComponentListStruct - the list of components
+// * ComponentOpStruct - the list of operations for each component
+// *
+// * To add a new component (assume xxx is component name):
+// * Assign a UID (in enum ComponentOp add xxx_COMPONENT_UID)
+// * Generate a component name (add new define called xxx_NAME)
+// * Define component operations (add enum xxxOperation)
+// * Define operation attributes (add ComponentOpStruct xxxOpStruct)
+// * Add component to list (add component to componentList)
+// *
+// * To add a new operation to an existing component, see comments associated
+// * with the component.
+// *
+// *****************************************************************************
+
+// The ComponentOpStruct describes a component
+// operationID - a number from xxxOperation representing the operation
+// operationCode - unique 2 charater value that represents the operation
+// operationName - unique name for the operation
+// isRootRoleOp - grant DB__ROOTROLE this operation
+// isAdminOp - grant DB__ADMIN/DB__ADMINROLE this operation
+// isDMLOp - this is a DML operation
+// isPublicOp - grant PUBLIC this operation
+struct ComponentOpStruct
+{
+ int32_t operationID;
+ const char * operationCode;
+ const char * operationName;
+ const bool isRootRoleOp;
+ const bool isAdminOp;
+ const bool isDMLOp;
+ const bool isPublicOp;
+};
+
+// The ComponentListStruct describes the relationship between a component UID,
+// its name, the number of operations for the component, and a pointer to the
+// list of operations.
+// componentUID - the UID for the component
+// componentName - the component name
+// numOps - the number of operations in the component
+// componentOps - pointer the ComponentOpStruct describing the operations
+struct ComponentListStruct
+{
+ int64_t componentUID;
+ const char * componentName;
+ int32_t numOps;
+ const ComponentOpStruct * componentOps;
+};
+
+// UID's for system component
+// USER_COMPONENT_START_UID begins user defined components
+enum ComponentOp{ INVALID_COMPONENT_UID = 0,
+ SQL_OPERATIONS_COMPONENT_UID = 1,
+ DBMGR_COMPONENT_UID = 2,
+ WMS_COMPONENT_UID = 3,
+ USER_COMPONENT_START_UID = 1000};
+
+// List of components
+#define SQL_OPERATIONS_NAME "SQL_OPERATIONS"
+#define DBMGR_NAME "DBMGR"
+#define WMS_NAME "WMS"
+
+// Defines component operations for SQL_OPERATIONS:
+// to add a new operation, add an entry to this list (in alphebetic order)
+// and add a corresponding entry to the sqlOpList.
+enum class SQLOperation {
+ ALTER = 2,
+ ALTER_LIBRARY,
+ ALTER_ROUTINE,
+ ALTER_ROUTINE_ACTION,
+ ALTER_SCHEMA,
+ ALTER_SEQUENCE,
+ ALTER_SYNONYM,
+ ALTER_TABLE,
+ ALTER_TRIGGER,
+ ALTER_VIEW,
+ CREATE,
+ CREATE_CATALOG,
+ CREATE_INDEX,
+ CREATE_LIBRARY,
+ CREATE_PROCEDURE,
+ CREATE_ROUTINE,
+ CREATE_ROUTINE_ACTION,
+ CREATE_SCHEMA,
+ CREATE_SEQUENCE,
+ CREATE_SYNONYM,
+ CREATE_TABLE,
+ CREATE_TRIGGER,
+ CREATE_VIEW,
+ DML_DELETE,
+ DML_EXECUTE,
+ DML_INSERT,
+ DML_REFERENCES,
+ DML_SELECT,
+ DML_SELECT_METADATA,
+ DML_UPDATE,
+ DML_USAGE,
+ DROP,
+ DROP_CATALOG,
+ DROP_INDEX,
+ DROP_LIBRARY,
+ DROP_PROCEDURE,
+ DROP_ROUTINE,
+ DROP_ROUTINE_ACTION,
+ DROP_SCHEMA,
+ DROP_SEQUENCE,
+ DROP_SYNONYM,
+ DROP_TABLE,
+ DROP_TRIGGER,
+ DROP_VIEW,
+ MANAGE,
+ MANAGE_COMPONENTS,
+ MANAGE_LIBRARY,
+ MANAGE_LOAD,
+ MANAGE_PRIVILEGES,
+ MANAGE_ROLES,
+ MANAGE_STATISTICS,
+ MANAGE_TENANTS,
+ MANAGE_USERS,
+ QUERY_ACTIVATE,
+ QUERY_CANCEL,
+ QUERY_SUSPEND,
+ REGISTER_HIVE_OBJECT,
+ REMAP_USER,
+ SHOW,
+ UNREGISTER_HIVE_OBJECT,
+ USE_ALTERNATE_SCHEMA,
+ FIRST_OPERATION = ALTER,
+ LAST_OPERATION = USE_ALTERNATE_SCHEMA,
+ NUMBER_OF_OPERATIONS = LAST_OPERATION - FIRST_OPERATION + 1,
+ UNKNOWN,
+ FIRST_DML_PRIV = DML_DELETE,
+ LAST_DML_PRIV = DML_USAGE
+};
+
+// Assign initial privileges for SQL_OPERATIONS (based on ComponentOpStruct):
+// recommend that DB__ROOTROLE granted all non DML privileges
+// recommend that DB__ADMIN and DB__ADMINROLE granted all non DML privileges
+// recommend that PUBLIC granted only a small subset of privileges
+static const ComponentOpStruct sqlOpList[] =
+{
+ {(int32_t)SQLOperation::ALTER, "A0","ALTER",true,true,false,false},
+ {(int32_t)SQLOperation::ALTER_LIBRARY, "AL","ALTER_LIBRARY",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_ROUTINE, "AR","ALTER_ROUTINE",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_ROUTINE_ACTION,"AA","ALTER_ROUTINE_ACTION",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_SCHEMA, "AH","ALTER_SCHEMA",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_SEQUENCE, "AQ","ALTER_SEQUENCE",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_SYNONYM, "AY","ALTER_SYNONYM",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_TABLE, "AT","ALTER_TABLE",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_TRIGGER, "AG","ALTER_TRIGGER",true,false,false,false},
+ {(int32_t)SQLOperation::ALTER_VIEW, "AV","ALTER_VIEW",true,false,false,false},
+
+ {(int32_t)SQLOperation::CREATE, "C0","CREATE",true,true,false,false },
+ {(int32_t)SQLOperation::CREATE_CATALOG, "CC","CREATE_CATALOG",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_INDEX, "CI","CREATE_INDEX",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_LIBRARY, "CL","CREATE_LIBRARY",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_PROCEDURE, "CP","CREATE_PROCEDURE",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_ROUTINE, "CR","CREATE_ROUTINE",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_ROUTINE_ACTION,"CA","CREATE_ROUTINE_ACTION",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_SCHEMA, "CH","CREATE_SCHEMA",true,false,false,true},
+ {(int32_t)SQLOperation::CREATE_SEQUENCE, "CQ","CREATE_SEQUENCE",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_SYNONYM, "CY","CREATE_SYNONYM",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_TABLE, "CT","CREATE_TABLE",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_TRIGGER, "CG","CREATE_TRIGGER",true,false,false,false},
+ {(int32_t)SQLOperation::CREATE_VIEW, "CV","CREATE_VIEW",true,false,false,false},
+
+ {(int32_t)SQLOperation::DML_DELETE, "PD","DML_DELETE",false,false,true,false},
+ {(int32_t)SQLOperation::DML_EXECUTE, "PE","DML_EXECUTE",false,false,true,false},
+ {(int32_t)SQLOperation::DML_INSERT, "PI","DML_INSERT",false,false,true,false},
+ {(int32_t)SQLOperation::DML_REFERENCES, "PR","DML_REFERENCES",false,false,true,false},
+ {(int32_t)SQLOperation::DML_SELECT, "PS","DML_SELECT",false,false,true,false},
+ {(int32_t)SQLOperation::DML_SELECT_METADATA,"PM","DML_SELECT_METADATA",true,true,true,false},
+ {(int32_t)SQLOperation::DML_UPDATE, "PU","DML_UPDATE",false,false,true,false},
+ {(int32_t)SQLOperation::DML_USAGE, "PG","DML_USAGE",false,false,true,false},
+
+ {(int32_t)SQLOperation::DROP, "D0","DROP",true,true,false,false },
+ {(int32_t)SQLOperation::DROP_CATALOG, "DC","DROP_CATALOG",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_INDEX, "DI","DROP_INDEX",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_LIBRARY, "DL","DROP_LIBRARY",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_PROCEDURE, "DP","DROP_PROCEDURE",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_ROUTINE, "DR","DROP_ROUTINE",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_ROUTINE_ACTION,"DA","DROP_ROUTINE_ACTION",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_SCHEMA, "DH","DROP_SCHEMA",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_SEQUENCE, "DQ","DROP_SEQUENCE",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_SYNONYM, "DY","DROP_SYNONYM",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_TABLE, "DT","DROP_TABLE",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_TRIGGER, "DG","DROP_TRIGGER",true,false,false,false},
+ {(int32_t)SQLOperation::DROP_VIEW, "DV","DROP_VIEW",true,false,false,false},
+
+ {(int32_t)SQLOperation::MANAGE, "M0","MANAGE",true,true,false,false},
+ {(int32_t)SQLOperation::MANAGE_COMPONENTS, "MC","MANAGE_COMPONENTS",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_LIBRARY, "ML","MANAGE_LIBRARY",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_LOAD, "MT","MANAGE_LOAD",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_PRIVILEGES, "MP","MANAGE_PRIVILEGES",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_ROLES, "MR","MANAGE_ROLES",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_STATISTICS, "MS","MANAGE_STATISTICS",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_TENANTS, "MX","MANAGE_TENANTS",true,false,false,false},
+ {(int32_t)SQLOperation::MANAGE_USERS, "MU","MANAGE_USERS",true,false,false,false},
+
+ {(int32_t)SQLOperation::QUERY_ACTIVATE, "QA","QUERY_ACTIVATE",true,true,false,false},
+ {(int32_t)SQLOperation::QUERY_CANCEL, "QC","QUERY_CANCEL",true,true,false,false},
+ {(int32_t)SQLOperation::QUERY_SUSPEND, "QS","QUERY_SUSPEND",true,true,false,false},
+ {(int32_t)SQLOperation::REGISTER_HIVE_OBJECT, "RH","REGISTER_HIVE_OBJECT",true,true,false,false},
+
+ {(int32_t)SQLOperation::REMAP_USER, "RU","REMAP_USER",true,true,false,false},
+ {(int32_t)SQLOperation::SHOW, "SW","SHOW",true,true,false,false},
+ {(int32_t)SQLOperation::UNREGISTER_HIVE_OBJECT, "UH","UNREGISTER_HIVE_OBJECT",true,true,false,false},
+ {(int32_t)SQLOperation::USE_ALTERNATE_SCHEMA, "UA","USE_ALTERNATE_SCHEMA",true,true,false,false}
+};
+
+// Defines the component operations for DBMGR:
+// add an entry to this list for new DBMGR operations(in alphabetic order)
+// and to the corresponding dbmgrOpList
+enum class DBMGROperation {
+ MANAGE_ALERTS = 2,
+ MANAGE_SESSIONS,
+ SHOW_ACTIVE_QUERIES,
+ SHOW_EVENT_LOGS,
+ SHOW_REPOS_QUERIES
+};
+
+// Assign initial privileges for DBMGROperation (based on ComponentOpStruct):
+static const ComponentOpStruct dbmgrOpList[] =
+{
+ {(int32_t)DBMGROperation::MANAGE_ALERTS, "MA","MANAGE_ALERTS",true,true,false,false},
+ {(int32_t)DBMGROperation::MANAGE_SESSIONS, "MS","MANAGE_SESSIONS",true,true,false,false},
+ {(int32_t)DBMGROperation::SHOW_ACTIVE_QUERIES, "AQ","SHOW_ACTIVE_QUERIES",true,true,false,false},
+ {(int32_t)DBMGROperation::SHOW_EVENT_LOGS, "EL","SHOW_EVENT_LOGS",true,true,false,false},
+ {(int32_t)DBMGROperation::SHOW_REPOS_QUERIES, "RQ","SHOW_REPOS_QUERIES",true,true,false,false}
+};
+
+// Defines the component operations for WMS:
+// add an entry to this list for new WMS operations (in alphabetic order)
+// and to the corresponding wmsOpList
+enum class WMSOperation {
+ MANAGE_WMS = 2
+};
+
+// Assign initial privileges for DBMGROperation (based on ComponentOpStruct):
+static const ComponentOpStruct wmsOpList[] =
+{
+ {(int32_t)WMSOperation::MANAGE_WMS, "MW","MANAGE_WMS",true,true,false,false},
+};
+
+
+// List of components
+static const ComponentListStruct componentList[]
+{ { (int64_t)SQL_OPERATIONS_COMPONENT_UID, SQL_OPERATIONS_NAME, sizeof(sqlOpList)/sizeof(ComponentOpStruct), (ComponentOpStruct *)&sqlOpList },
+ { (int64_t)DBMGR_COMPONENT_UID, DBMGR_NAME, sizeof(dbmgrOpList)/sizeof(ComponentOpStruct), (ComponentOpStruct *)&dbmgrOpList },
+ { (int64_t)WMS_COMPONENT_UID, WMS_NAME, sizeof(wmsOpList)/sizeof(ComponentOpStruct), (ComponentOpStruct *)&wmsOpList } };
+
+#endif
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp b/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
index be6de56..ecee04e 100644
--- a/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
+++ b/core/sql/sqlcomp/PrivMgrComponentPrivileges.cpp
@@ -74,10 +74,12 @@ public:
// -------------------------------------------------------------------
MyRow(std::string tableName)
: PrivMgrMDRow(tableName, COMPONENT_PRIVILEGES_ENUM),
- componentUID_(0)
+ componentUID_(0),
+ visited_(false)
{ };
MyRow(const MyRow &other)
- : PrivMgrMDRow(other)
+ : PrivMgrMDRow(other),
+ visited_(false)
{
componentUID_ = other.componentUID_;
operationCode_ = other.operationCode_;
@@ -88,6 +90,15 @@ public:
grantDepth_ = other.grantDepth_;
};
virtual ~MyRow() {};
+
+ bool operator==(const MyRow & other) const
+ {
+ return ( ( componentUID_ == other.componentUID_ ) &&
+ ( operationCode_ == other.operationCode_ ) &&
+ ( granteeID_ == other.granteeID_ ) &&
+ ( grantorID_ == other.grantorID_ ) );
+ }
+
inline void clear() {componentUID_ = 0;};
void describeGrant(
@@ -107,6 +118,7 @@ public:
int32_t grantorID_;
std::string grantorName_;
int32_t grantDepth_;
+ bool visited_;
private:
MyRow();
@@ -141,6 +153,11 @@ public:
const std::string & operationCode,
int32_t & grantee);
+ void getRowsForGrantee(
+ const MyRow &baseRow,
+ std::vector<MyRow> &masterRowList,
+ std::set<size_t> &rowsToDelete);
+
virtual PrivStatus insert(const PrivMgrMDRow & row);
PrivStatus selectAllWhere(
@@ -442,6 +459,120 @@ std::string whereClause("WHERE ");
+// *****************************************************************************
+// * *
+// * Function: PrivMgrComponentPrivileges::dropAllForGrantee *
+// * *
+// * This function drops all component privileges that have been granted *
+// * to the user specified as "granteeID". If the grantee had the WGO then *
+// * the branch of privileges started by granteeID are removed. *
+// * *
+// * This code assumes that all roles have been revoked from the granteeID *
+// * prior to being called. *
+// *****************************************************************************
+// * *
+// * Parameters: *
+// * *
+// * <granteeID> const int32_t In *
+// * *
+// *****************************************************************************
+// * *
+// * Returns: bool *
+// * *
+// * true: grantees were dropped *
+// * false: unexpected error occurred. Error is put into the diags area. *
+// * *
+// *****************************************************************************
+bool PrivMgrComponentPrivileges::dropAllForGrantee(
+ const int32_t granteeID)
+{
+ // Get the list of all privileges from component_privileges table
+ // Skip rows granted by the system (-2)
+ std::string whereClause (" WHERE GRANTOR_ID > 0");
+ std::string orderByClause= " ORDER BY COMPONENT_UID, GRANTOR_ID, GRANTEE_ID, OPERATION_CODE, GRANT_DEPTH";
+
+ MyTable &myTable = static_cast<MyTable &>(myTable_);
+ std::vector<MyRow> masterRowList;
+
+ PrivStatus privStatus = myTable.selectAllWhere(whereClause,orderByClause,masterRowList);
+ if (privStatus == STATUS_ERROR)
+ return false;
+
+ // Create a list of indexes into the masterRowList where the granteeID is
+ // the target of one or more privileges
+ std::vector<size_t> granteeRowList;
+ for (size_t i = 0; i < masterRowList.size(); i++)
+ {
+ if (masterRowList[i].granteeID_ == granteeID)
+ granteeRowList.push_back(i);
+ }
+
+ // if the granteeID has not been granted any privileges, we are done
+ if (granteeRowList.size() == 0)
+ return true;
+
+ // Add the rows from granteeRowList to rowsToDelete list
+ // If any privileges were granted WGO, also remove the branch.
+ std::set<size_t> rowsToDelete;
+ for (size_t i = 0; i < granteeRowList.size(); i++)
+ {
+ size_t baseIdx = granteeRowList[i];
+ MyRow baseRow = masterRowList[baseIdx];
+
+ // If grantDepth < 0, then WGO was specified, remove branch
+ if (baseRow.grantDepth_ < 0)
+ myTable.getRowsForGrantee(baseRow, masterRowList, rowsToDelete);
+ masterRowList[baseIdx].visited_ = true;
+ rowsToDelete.insert(baseIdx);
+ }
+
+ // delete all the rows in affected list into statements of 10 rows
+ if (rowsToDelete.size() > 0)
+ {
+ whereClause = "WHERE ";
+ bool isFirst = true;
+ size_t count = 0;
+ for (std::set<size_t>::iterator it = rowsToDelete.begin(); it!= rowsToDelete.end(); ++it)
+ {
+ if (count > 20)
+ {
+ privStatus == myTable.deleteWhere(whereClause);
+ if (privStatus == STATUS_ERROR)
+ return false;
+ whereClause = "WHERE ";
+ isFirst = true;
+ count = 0;
+ }
+ if (isFirst)
+ isFirst = false;
+ else
+ whereClause += " OR ";
+ size_t masterIdx = *it;
+ MyRow row = masterRowList[masterIdx];
+
+ const std::string componentUIDString = to_string((long long int)row.componentUID_);
+ whereClause += "(component_uid = ";
+ whereClause += componentUIDString.c_str();
+ whereClause += " AND grantor_name = '";
+ whereClause += row.grantorName_;
+ whereClause += "' AND grantee_name = '";
+ whereClause += row.granteeName_;
+ whereClause += "' AND operation_code = '";
+ whereClause += row.operationCode_;
+ whereClause += "')";
+ count++;
+ }
+ privStatus == myTable.deleteWhere(whereClause);
+ if (privStatus == STATUS_ERROR)
+ return false;
+ }
+
+ return true;
+}
+
+
+
+
// *****************************************************************************
// * *
@@ -2168,6 +2299,111 @@ PrivStatus privStatus = selectWhereUnique(whereClause,row);
+// *****************************************************************************
+// * *
+// * Function: MyTable::getRowsForGrantee *
+// * *
+// * Finds the list of rows (branch) that need to be removed if the *
+// * grantee no longer has WGO. *
+// * *
+// *****************************************************************************
+// * *
+// * Parameters: *
+// * *
+// * <baseRow> const MyRow & In *
+// * contains the starting point for the branch *
+// * *
+// * <masterRowList> std::vector<MyRow> & In/Out *
+// * contains the master list of privileges *
+// * this list is updated to set the "visited_" flag for performance *
+// * *
+// * <rowsToDelete> std::set<size_t> & Out *
+// * returns the list of privileges to be removed *
+// * *
+// *****************************************************************************
+// * *
+// * Returns: No errors are generated *
+// * *
+// *****************************************************************************
+void MyTable::getRowsForGrantee(
+ const MyRow &baseRow,
+ std::vector<MyRow> &masterRowList,
+ std::set<size_t> &rowsToDelete)
+{
+ for (size_t i = 0; i < masterRowList.size(); i++)
+ {
+ // master list is ordered by component ID, grantorID, granteeID and operationCode
+ // If done checking rows for the grantorID_ from the baseRow, just return
+ if ((masterRowList[i].componentUID_ == baseRow.componentUID_) &&
+ (masterRowList[i].grantorID_ > baseRow.granteeID_))
+ break;
+
+ // If we have already processed the row or it is a row we are not
+ // interested in - continue
+ if (masterRowList[i].visited_ || (masterRowList[i].grantorID_ < baseRow.granteeID_))
+ continue;
+
+ // If this is a row we are interested in, add to rowsToDelete
+ if ((masterRowList[i].componentUID_ == baseRow.componentUID_) &&
+ (masterRowList[i].grantorID_ == baseRow.granteeID_) &&
+ (masterRowList[i].operationCode_ == baseRow.operationCode_))
+ {
+ // no more leaves, done with the branch
+ if (masterRowList[i].grantDepth_ == 0)
+ {
+ masterRowList[i].visited_ = true;
+ rowsToDelete.insert(i);
+ continue;
+ }
+
+ // Privilege was granted WITH GRANT OPTION, see if there is anything
+ // left on the branch to remove. If there are more leaves, check to
+ // see if grantee gets the priv from other grantors (WGO). If so, then
+ // no need to remove rest of branch
+ std::vector<size_t> grantList;
+ for (size_t g = 0; g < masterRowList.size(); g++)
+ {
+ // see if this is a row we are interested in
+ if ((masterRowList[g].visited_ == false) &&
+ (masterRowList[g].componentUID_ == baseRow.componentUID_) &&
+ (masterRowList[g].granteeID_ == baseRow.granteeID_) &&
+ (masterRowList[g].operationCode_ == baseRow.operationCode_))
+ {
+ // If this is the base row, skip
+ if (masterRowList[g] == baseRow)
+ continue;
+
+ // we are interested, save it
+ grantList.push_back(g);
+ }
+ }
+
+ // See if privilege has been granted by another grantor
+ if (grantList.size() > 0)
+ {
+ for (size_t j = 0; j < grantList.size(); j++)
+ {
+ size_t grantNdx = grantList[j];
+ if (masterRowList[grantNdx].grantDepth_ < 0)
+ {
+ // this authID has been granted WGO privilege from another user
+ // no need to remove branch
+ masterRowList[i].visited_ = true;
+ break;
+ }
+ }
+ }
+
+ // Check the next branch of privileges
+ getRowsForGrantee(masterRowList[i], masterRowList, rowsToDelete);
+
+ // found a leaf to remove
+ masterRowList[i].visited_;
+ rowsToDelete.insert(i);
+ }
+ }
+}
+
// *****************************************************************************
// * *
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/PrivMgrComponentPrivileges.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrComponentPrivileges.h b/core/sql/sqlcomp/PrivMgrComponentPrivileges.h
index 566b051..2081a0f 100644
--- a/core/sql/sqlcomp/PrivMgrComponentPrivileges.h
+++ b/core/sql/sqlcomp/PrivMgrComponentPrivileges.h
@@ -74,6 +74,8 @@ public:
const std::string & componentUID,
const std::string & operationCode);
+ bool dropAllForGrantee(const int32_t granteeID);
+
bool findByNames(
const std::string & componentName,
const std::string & operationName);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/PrivMgrMD.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrMD.cpp b/core/sql/sqlcomp/PrivMgrMD.cpp
index e50ff93..91047e7 100644
--- a/core/sql/sqlcomp/PrivMgrMD.cpp
+++ b/core/sql/sqlcomp/PrivMgrMD.cpp
@@ -559,16 +559,16 @@ PrivStatus PrivMgrMDAdmin::dropMetadata (
CmpSeabaseDDLrole role;
std::vector<std::string> rolesCreated;
- int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemRolesStruct);
+ int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemAuthsStruct);
for (int32_t i = 0; i < numberRoles; i++)
{
- const SystemRolesStruct &roleDefinition = systemRoles[i];
+ const SystemAuthsStruct &roleDefinition = systemRoles[i];
// special Auth includes roles that are not registered in the metadata
if (roleDefinition.isSpecialAuth)
continue;
- role.dropStandardRole(roleDefinition.roleName);
+ role.dropStandardRole(roleDefinition.authName);
}
int32_t actualSize = 0;
@@ -1630,18 +1630,18 @@ PrivStatus PrivMgrMDAdmin::updatePrivMgrMetadata(
// operation, than all system roles are created.
CmpSeabaseDDLrole role;
std::vector<std::string> rolesCreated;
- int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemRolesStruct);
+ int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemAuthsStruct);
for (int32_t i = 0; i < numberRoles; i++)
{
- const SystemRolesStruct &roleDefinition = systemRoles[i];
+ const SystemAuthsStruct &roleDefinition = systemRoles[i];
// special Auth includes roles that are not registered in the metadata
if (roleDefinition.isSpecialAuth)
continue;
// returns true is role was created, false if it already existed
- if (role.createStandardRole(roleDefinition.roleName, roleDefinition.roleID))
- rolesCreated.push_back(roleDefinition.roleName);
+ if (role.createStandardRole(roleDefinition.authName, roleDefinition.authID))
+ rolesCreated.push_back(roleDefinition.authName);
}
// Report the number roles created
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/079ea00a/core/sql/sqlcomp/PrivMgrRoles.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/PrivMgrRoles.cpp b/core/sql/sqlcomp/PrivMgrRoles.cpp
index 04f0d87..6d610d5 100644
--- a/core/sql/sqlcomp/PrivMgrRoles.cpp
+++ b/core/sql/sqlcomp/PrivMgrRoles.cpp
@@ -1371,7 +1371,7 @@ PrivStatus PrivMgrRoles::populateCreatorGrants(
MyTable &myTable = static_cast<MyTable &>(myTable_);
- int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemRolesStruct) -
+ int32_t numberRoles = sizeof(systemRoles)/sizeof(SystemAuthsStruct) -
NUMBER_SPECIAL_SYSTEM_ROLES;
// Calculate the number of roles that have already been created
[2/3] incubator-trafodion git commit: Miscellaneous authorization
changes:
Posted by rm...@apache.org.
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);
};
[3/3] incubator-trafodion git commit: Merge pr 1274 miscellaneous
authorization changes
Posted by rm...@apache.org.
Merge pr 1274 miscellaneous authorization changes
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/9e5f36cd
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/9e5f36cd
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/9e5f36cd
Branch: refs/heads/master
Commit: 9e5f36cd36c0b77ca2b9a3f46afbd56761fa7929
Parents: 6b07d62 079ea00
Author: Roberta Marton <rm...@edev07.esgyn.local>
Authored: Thu Oct 26 17:23:17 2017 +0000
Committer: Roberta Marton <rm...@edev07.esgyn.local>
Committed: Thu Oct 26 17:23:17 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(-)
----------------------------------------------------------------------