trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [1/2] incubator-trafodion git commit: TRAFODION [1895] - Unable to grant and revoke privileges on native hive tables
Date Thu, 24 Mar 2016 15:58:13 GMT
Repository: incubator-trafodion
Updated Branches:
  refs/heads/master 2857b3e97 -> 1abcec1cb


TRAFODION [1895] - Unable to grant and revoke privileges on native hive tables

There are actually two issues:
1 - when an external table is created, the wrong role is assigned ownership
    which makes it impossible to grant privileges.
2 - Query invalidation is not removing external tables from caches after revoke
    completes; therefore, the user/role still has permissions until a restart

Also, added code to initialize authorization to set hive schema ownerships and
dependent objects to DB__HIVEROLE.  This fixes the issue of ownership. For
existing installations that has authorization enabled, run initialize
authorization again. This acts as an upgrade and will fix the owner issue.

Removed Row.java, BaseRow.java and InsertRow.java from T2 driver code - these
are obsolete and their removal fixes a licensing issue.  T4 driver code is
not touched since it still uses these files.


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/6950f11d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/6950f11d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/6950f11d

Branch: refs/heads/master
Commit: 6950f11d251a5a26f2c218902cbc90fde1fdaf68
Parents: 17dbda7
Author: Roberta Marton <roberta.marton@apache.org>
Authored: Fri Mar 18 17:39:51 2016 +0000
Committer: Roberta Marton <roberta.marton@apache.org>
Committed: Fri Mar 18 17:39:51 2016 +0000

----------------------------------------------------------------------
 .../java/org/trafodion/jdbc/t2/BaseRow.java     |  67 -----
 .../java/org/trafodion/jdbc/t2/InsertRow.java   |  89 ------
 .../main/java/org/trafodion/jdbc/t2/Row.java    | 275 -------------------
 core/sql/optimizer/NATable.cpp                  |   4 +-
 core/sql/regress/core/EXPECTED131               |  89 +++++-
 core/sql/regress/core/TEST131                   |  40 +++
 core/sql/regress/privs1/EXPECTED133             | Bin 22984 -> 26383 bytes
 core/sql/regress/privs1/TEST133                 |  97 +++----
 core/sql/sqlcomp/CmpSeabaseDDL.h                |   1 +
 core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp        |  10 +
 core/sql/sqlcomp/CmpSeabaseDDLschema.cpp        | 101 ++++++-
 core/sql/sqlcomp/CmpSeabaseDDLtable.cpp         |   2 +-
 12 files changed, 282 insertions(+), 493 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/BaseRow.java
----------------------------------------------------------------------
diff --git a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/BaseRow.java b/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/BaseRow.java
deleted file mode 100644
index 2eed427..0000000
--- a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/BaseRow.java
+++ /dev/null
@@ -1,67 +0,0 @@
-// @@@ 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 @@@
-
-// Source File Name:   BaseRow.java
-
-/*
- * Copyright (c) 2003, Oracle and/or its affiliates. All rights reserved.
- * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
- *
- * This code is free software; you can redistribute it and/or modify it
- * under the terms of the GNU General Public License version 2 only, as
- * published by the Free Software Foundation.  Oracle designates this
- * particular file as subject to the "Classpath" exception as provided
- * by Oracle in the LICENSE file that accompanied this code.
- * This code is distributed in the hope that it will be useful, but WITHOUT
- * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
- * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
- * version 2 for more details (a copy is included in the LICENSE file that
- * accompanied this code).
- *
- * You should have received a copy of the GNU General Public License version
- * 2 along with this work; if not, write to the Free Software Foundation,
- * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
- *
- * Please contact Oracle, 500 Oracle Parkway, Redwood Shores, CA 94065 USA
- * or visit www.oracle.com if you need additional information or have any
- * questions.
- */
-package org.trafodion.jdbc.t2;
-
-import java.io.Serializable;
-import java.sql.SQLException;
-
-abstract class BaseRow
-    implements Serializable, Cloneable {
-
-    protected Object origVals[];
-
-    BaseRow() {
-    }
-
-    protected abstract Object getColumnObject(int i) throws SQLException;
-
-    protected Object[] getOrigRow() {
-        return origVals;
-    }
-
-    protected abstract void setColumnObject(int i, Object obj) throws SQLException;
-}

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/InsertRow.java
----------------------------------------------------------------------
diff --git a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/InsertRow.java b/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/InsertRow.java
deleted file mode 100644
index 54bb150..0000000
--- a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/InsertRow.java
+++ /dev/null
@@ -1,89 +0,0 @@
-// @@@ 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 @@@
-
-package org.trafodion.jdbc.t2;
-
-import java.io.Serializable;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.sql.PreparedStatement;
-import java.util.BitSet;
-
-
-class InsertRow extends BaseRow
-    implements Serializable, Cloneable {
-
-    private BitSet colsInserted;
-    private int cols;
-
-    InsertRow(int i) {
-        origVals = new Object[i];
-        colsInserted = new BitSet(i);
-        cols = i;
-    }
-
-    protected Object getColumnObject(int i) throws SQLException {
-        if(!colsInserted.get(i - 1))
-            throw new SQLException("No value has been inserted");
-        else
-            return origVals[i - 1];
-    }
-
-    protected void initInsertRow() {
-        for(int i = 0; i < cols; i++)
-            colsInserted.clear(i);
-
-    }
-
-	/*
-    protected boolean isCompleteRow(RowSetMetaData rowsetmetadata) throws SQLException {
-        for(int i = 0; i < cols; i++)
-            if(!colsInserted.get(i) && rowsetmetadata.isNullable(i + 1) == 0)
-                return false;
-
-        return true;
-    }
-	*/
-
-    protected void markColInserted(int i) {
-        colsInserted.set(i);
-    }
-
-    protected void setColumnObject(int i, Object obj) {
-        origVals[i - 1] = obj;
-        markColInserted(i - 1);
-    }
-
-	protected void insertRow(PreparedStatement insertStmt, BitSet paramCols) throws SQLException
-	{
-		int i;
-		int j;
-
-		for (i = 0, j= 1; i < cols ; i++)
-		{
-			if (paramCols.get(i))
-				insertStmt.setObject(j++, origVals[i]);
-		}
-		insertStmt.execute();
-		initInsertRow();
-	}
-}
-

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/Row.java
----------------------------------------------------------------------
diff --git a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/Row.java b/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/Row.java
deleted file mode 100644
index b8104aa..0000000
--- a/core/conn/jdbc_type2/src/main/java/org/trafodion/jdbc/t2/Row.java
+++ /dev/null
@@ -1,275 +0,0 @@
-// @@@ 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 @@@
-
-/*
-* Filename    : Row.java
-* Description :
-*
-*/
-
-package org.trafodion.jdbc.t2;
-
-import java.io.Serializable;
-import java.sql.*;
-import java.util.BitSet;
-import java.util.Locale;
-
-// Referenced classes of package sun.jdbc.rowset:
-//            BaseRow
-
-class Row extends BaseRow
-    implements Serializable, Cloneable {
-
-    private Object currentVals[];
-    private BitSet colsChanged;
-    private boolean deleted;
-    private boolean updated;
-    private boolean inserted;
-    private int numCols;
-
-    Row(int i) {
-        origVals = new Object[i];
-        currentVals = new Object[i];
-        colsChanged = new BitSet(i);
-        numCols = i;
-    }
-
-    Row(int i, Object aobj[]) {
-        origVals = new Object[i];
-        for(int j = 0; j < i; j++)
-            origVals[j] = aobj[j];
-
-        currentVals = new Object[i];
-        colsChanged = new BitSet(i);
-        numCols = i;
-    }
-
-    protected void clearDeleted() {
-        deleted = false;
-    }
-
-    protected void clearInserted() {
-        inserted = false;
-    }
-
-    protected void clearUpdated() {
-        updated = false;
-        for(int i = 0; i < numCols; i++) {
-            currentVals[i] = null;
-            colsChanged.clear(i);
-        }
-
-    }
-
-    protected boolean getColUpdated(int i) {
-        return colsChanged.get(i);
-    }
-
-    protected Object getColumnObject(int i) throws SQLException {
-        if(getColUpdated(i - 1))
-            return currentVals[i - 1];
-        else
-            return origVals[i - 1];
-    }
-
-    protected boolean getDeleted() {
-        return deleted;
-    }
-
-    protected boolean getInserted() {
-        return inserted;
-    }
-
-    protected boolean getUpdated() {
-        return updated;
-    }
-
-    protected void initColumnObject(int i, Object obj) {
-        origVals[i - 1] = obj;
-    }
-
-    protected void moveCurrentToOrig() {
-        for(int i = 0; i < numCols; i++)
-            if(getColUpdated(i)) {
-                origVals[i] = currentVals[i];
-                currentVals[i] = null;
-                colsChanged.clear(i);
-            }
-	}
-
-    private void setColUpdated(int i) {
-        colsChanged.set(i);
-    }
-
-    protected void setColumnObject(int i, Object obj) {
-        currentVals[i - 1] = obj;
-        setColUpdated(i - 1);
-    }
-
-	protected void setLobObject(int i, Object obj) {
-        currentVals[i - 1] = obj;
-		origVals[i-1] = obj;
-    }
-
-
-    protected void setDeleted() {
-        deleted = true;
-    }
-
-    protected void setInserted() {
-        inserted = true;
-    }
-
-    protected void setUpdated() {
-        updated = true;
-    }
-	
-	protected void deleteRow(Locale locale, PreparedStatement deleteStmt, BitSet paramCols)
throws SQLException
-	{
-		int i;
-		int j;
-		int count;
-
-		for (i = 0, j = 1; i < numCols ; i++)
-		{
-			if (paramCols.get(i))
-				deleteStmt.setObject(j++, origVals[i]);
-		}
-		count =	deleteStmt.executeUpdate();
-		if (count == 0)
-			throw SQLMXMessages.createSQLException(locale, "row_modified", null);
-	}
-
-	protected void updateRow(Locale locale, PreparedStatement updateStmt, BitSet paramCols,
BitSet keyCols) throws SQLException
-	{
-		int i;
-		int j;
-		int count;
-		Object obj;
-        int numPKey=0;        
-        int loc=0;           
-        int pKeyCounter=1;    
-
-        for (i = 0; i < numCols; i++ )  
-        {                                
-            if(keyCols.get(i))           
-                numPKey++;               
-        }                               
-        
-        loc = numCols - numPKey;         
-
-		for (i = 0, j = 1; i < numCols ; i++)
-		{
-			if (keyCols.get(i))
-			{
-				if (getColUpdated(i))
-					throw SQLMXMessages.createSQLException(locale, "primary_key_not_updateable", null);
-                updateStmt.setObject((loc+pKeyCounter),getColumnObject(i+1)); 
-                pKeyCounter++;  
-			}
-			else
-			{
-				{
-					obj = getColumnObject((i+1));
-					if (obj instanceof SQLMXLob)
-					{
-						if (obj == origVals[i])	// New and old Lob objects are same
-						{
-							updateStmt.setObject(j++, new DataWrapper((int) ((SQLMXLob)obj).dataLocator_));
-							continue;
-						}
-					}
-					updateStmt.setObject(j++, obj);
-				}
-			}
-		}
-		
-
-        /* 
-		for (i = 0 ;  i < numCols ; i++)
-		{
-			if (paramCols.get(i))
-			{
-				obj = origVals[i];
-				if (obj instanceof SQLMXLob)
-				{
-					updateStmt.setObject(j++, new DataWrapper(((SQLMXLob)obj).dataLocator_));
-					continue;
-				}
-				updateStmt.setObject(j++, origVals[i]);
-			}
-        } */
-		count = updateStmt.executeUpdate();
-		if (count == 0)
-			throw SQLMXMessages.createSQLException(locale, "row_modified", null);
-		moveCurrentToOrig();
-		setUpdated();
-	}
-	
-	protected void refreshRow(Locale locale, PreparedStatement selectStmt, BitSet selectCols,
BitSet keyCols) throws SQLException
-	{
-		int i;
-		int j;
-		ResultSet rs;
-		ResultSetMetaData rsmd;
-		int columnCount;
-		
-		clearUpdated();	
-	
-		for (i = 0, j = 1; i < numCols ; i++)
-		{
-			if (keyCols.get(i))
-				selectStmt.setObject(j++, origVals[i]);
-		}
-		rs = selectStmt.executeQuery();
-		if (rs != null)
-		{
-			try {
-				rsmd = rs.getMetaData();
-
-				columnCount = rsmd.getColumnCount();
-				for (i = 0, j = 1 ; i < numCols ; i++)
-				{
-					if (selectCols.get(i))
-						origVals[i] = rs.getObject(j++);
-				}
-			} finally {
-				rs.close();
-			}
-		}
-	}
-
-	protected void closeLobObjects() 
-	{
-		int i;
-		SQLMXLob lob;
-
-		for (i = 0; i < numCols ; i++)
-		{
-			if (currentVals[i] instanceof SQLMXLob)
-			{
-				lob = (SQLMXLob)currentVals[i];
-				lob.close();
-			}
-				
-		}
-	}
-}

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/optimizer/NATable.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NATable.cpp b/core/sql/optimizer/NATable.cpp
index 46f6f0d..aa1a8c9 100644
--- a/core/sql/optimizer/NATable.cpp
+++ b/core/sql/optimizer/NATable.cpp
@@ -8716,8 +8716,8 @@ NATableDB::free_entries_with_QI_key(Int32 numKeys, SQL_QIKEY* qiKeyArray)
   {
     NATable * currTable = cachedTableList_[currIndx];
 
-    // Only need to remove seabase tables
-    if (!currTable->isSeabaseTable())
+    // Only need to remove seabase tables and external Hive/hbase tables
+    if (!currTable->isSeabaseTable() && !currTable->hasExternalTable())
     {
       currIndx++;
       continue;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/regress/core/EXPECTED131
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/EXPECTED131 b/core/sql/regress/core/EXPECTED131
index c5a3fb5..57a464d 100644
--- a/core/sql/regress/core/EXPECTED131
+++ b/core/sql/regress/core/EXPECTED131
@@ -51,6 +51,18 @@
 
 --- SQL operation complete.
 >>
+>>-- setup for test_native
+>>grant role DB__HIVEROLE to SQL_USER4;
+
+--- SQL operation complete.
+>>showddl role DB__HIVEROLE;
+
+CREATE ROLE "DB__HIVEROLE";
+
+--- SQL operation complete.
+>>create external table item for hive.hive.item;
+
+--- SQL operation complete.
 >>
 >>log;
 >>
@@ -308,9 +320,9 @@ SQL_USER7
 >>
 >>-- expect error 8031 since the query is not running.
 >>obey cancel_cmd;
->>control query cancel qid MXID11000006223212317056413926129000000000206U3334000_52_S1;
+>>control query cancel qid MXID11000007219212324915937583329000000000206U3335600_52_S1;
 
-*** ERROR[8031] Server declined cancel request for query ID MXID11000006223212317056413926129000000000206U3334000_52_S1.
The query is not in OPEN or FETCH or EXECUTE state.
+*** ERROR[8031] Server declined cancel request for query ID MXID11000007219212324915937583329000000000206U3335600_52_S1.
The query is not in OPEN or FETCH or EXECUTE state.
 
 --- SQL operation failed with errors.
 >>
@@ -450,6 +462,65 @@ End of MXCI Session
 
 End of MXCI Session
 
+>>sh sqlci -i"TEST131(test_native)" -u sql_user3;
+>>values(user);
+
+(EXPR)
+---------------------------------------------------------------------------------------------------------------------------------
+
+SQL_USER3                                                                               
                                        
+
+--- 1 row(s) selected.
+>>
+>>-- user3 has no privs
+>>select count(*) from hive.hive.item;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.HIVE.ITEM.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- grant privs to user3, now user3 can execute dml
+>>sh sqlci -i"TEST131(grant_hive_privs)" -u"SQL_User4"  ;
+>>grant select on hive.hive.item to sql_user3;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>select count(*) from hive.hive.item;
+
+(EXPR)              
+--------------------
+
+               18000
+
+--- 1 row(s) selected.
+>>
+>>-- revoke privs from user3, user3 can no longer perform dml
+>>-- verify that query invalidate works as designed
+>>sh sqlci -i"TEST131(revoke_hive_privs)" -u"SQL_User4"  ;
+>>revoke select on hive.hive.item from sql_user3;
+
+--- SQL operation complete.
+>>
+>>exit;
+
+End of MXCI Session
+
+>>select count(*) from hive.hive.item;
+
+*** ERROR[4481] The user does not have SELECT privilege on table or view HIVE.HIVE.ITEM.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>exit;
+
+End of MXCI Session
+
 >>
 >>obey TEST131(clnup);
 >>
@@ -487,5 +558,19 @@ End of MXCI Session
 
 --- SQL operation failed with errors.
 >>
+>>-- cleanup for test_native
+>>drop external table item for hive.hive.item;
+
+--- SQL operation complete.
+>>revoke role DB__HIVEROLE from SQL_USER4;
+
+--- SQL operation complete.
+>>showddl role DB__HIVEROLE;
+
+CREATE ROLE "DB__HIVEROLE";
+  -- GRANT ROLE "DB__HIVEROLE" TO "DB__ROOT" WITH ADMIN OPTION;
+
+--- SQL operation complete.
+>>
 >>
 >>log;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/regress/core/TEST131
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST131 b/core/sql/regress/core/TEST131
index 75eb331..bffa4be 100755
--- a/core/sql/regress/core/TEST131
+++ b/core/sql/regress/core/TEST131
@@ -33,8 +33,10 @@ log LOG131 ;
 -- run the test in sqlci sessions which start after authorization
 -- is enabled.
 log;
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
 sh sqlci -i"TEST131(test_session1)";
 log LOG131 ;
+sh sqlci -i"TEST131(test_native)" -u sql_user3;
 
 obey TEST131(clnup);
 
@@ -74,6 +76,11 @@ revoke component privilege QUERY_CANCEL on sql_operations from SQL_USER7;
 revoke role role131  from sql_user7;
 revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
 drop role role131;
+ 
+-- cleanup for test_native
+drop external table item for hive.hive.item;
+revoke role DB__HIVEROLE from SQL_USER4;
+showddl role DB__HIVEROLE;
 
 ?section setup
 
@@ -111,6 +118,10 @@ grant all on t131a to SQL_USER7;
 grant all on t131b to SQL_USER7;
 grant all on t131c to SQL_USER7;
 
+-- setup for test_native
+grant role DB__HIVEROLE to SQL_USER4;
+showddl role DB__HIVEROLE;
+create external table item for hive.hive.item;
 
 ?section dml
 
@@ -361,5 +372,34 @@ revoke role role131  from sql_user7;
 revoke component privilege QUERY_CANCEL on SQL_OPERATIONS from role131;
 drop role role131;
 
+?section test_native
+-- user3
+log LOG131;
+values(user);
+ 
+-- user3 has no privs
+select count(*) from hive.hive.item;
+
+-- grant privs to user3, now user3 can execute dml
+sh sqlci -i"TEST131(grant_hive_privs)" -u"SQL_User4"  ;
+select count(*) from hive.hive.item;
+
+-- revoke privs from user3, user3 can no longer perform dml
+-- verify that query invalidate works as designed
+sh sqlci -i"TEST131(revoke_hive_privs)" -u"SQL_User4"  ;
+select count(*) from hive.hive.item;
+
+?section grant_hive_privs
+-- user4 who has been granted DB__HIVEROLE
+values (user);
+log LOG131;
+grant select on hive.hive.item to sql_user3;
+
+?section revoke_hive_privs
+-- user4, has been granted DB__HIVEROLE
+values (user);
+log LOG131;
+revoke select on hive.hive.item from sql_user3;
+
 ?section end_of_test
 -- end of test.

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/regress/privs1/EXPECTED133
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED133 b/core/sql/regress/privs1/EXPECTED133
index 6edbc65..c20d1a0 100644
Binary files a/core/sql/regress/privs1/EXPECTED133 and b/core/sql/regress/privs1/EXPECTED133
differ

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/regress/privs1/TEST133
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/TEST133 b/core/sql/regress/privs1/TEST133
index d630bda..0b1899f 100755
--- a/core/sql/regress/privs1/TEST133
+++ b/core/sql/regress/privs1/TEST133
@@ -35,9 +35,9 @@
 --   authorized - runs authorization tests
 -- ============================================================================
 
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
 obey TEST133(clean_up);
 log LOG133 clear;
-obey TEST133(create_db);
 obey TEST133(tests);
 sh sqlci -i "TEST133(authorized)" -u sql_user4;
 log;
@@ -46,11 +46,14 @@ initialize authorization;
 exit;
 
 ?section clean_up
-set schema "_PRIVMGR_MD_";
 drop sequence t133sch.team_number_sequence;
 -- drop database
 drop schema t133sch cascade;
 
+set parserflags 131072;
+drop schema "_HV_HIVE_" cascade;
+reset parserflags 131072;
+
 ?section create_db
 create schema t133sch;
 set schema t133sch;
@@ -64,13 +67,6 @@ create table teams
 
 alter table teams add constraint valid_team_no check (team_number > 0);
 
-insert into teams values
-   (1, 'White Socks', 'Sam','4082282222'),
-   (2, 'Giants', 'Joe', '5102839483'),
-   (3, 'Cardinals', 'Stella', '9513849384'),
-   (4, 'Indians', 'Matt', '5128383748'),
-   (5, 'Tigers', 'Ronit', '6198273827');
-
 create table team_statistics
   (team_number int not null primary key,
    num_players int not null)
@@ -83,7 +79,6 @@ create table games
      game_time timestamp not null,
      game_location varchar(50) not null)
   ;
---create index home_games on games (home_team_number);
 
 alter table games add constraint valid_game_number check (game_number > 0);
 
@@ -93,18 +88,6 @@ create table standings
      loses int default 0,
      last_updated timestamp default current_timestamp)
 ;
-insert into standings (team_number)
-  select team_number from teams;
-
-insert into games values
-   (1, 2, 1, timestamp '2009-04-23 19:30:00', 'California'),
-   (1, 3, 2, timestamp '2009-04-24 19:30:00', 'California'),
-   (1, 4, 3, timestamp '2009-04-25 10:00:00', 'Oklahoma'),
-   (2, 3, 4, timestamp '2009-04-25 13:30:00', 'Michigan'),
-   (1, 5, 5, timestamp '2009-04-25 15:00:00', 'Oklahoma'),
-   (2, 5, 6, timestamp '2009-04-27 17:00:00', 'New York'),
-   (3, 4, 7, timestamp '2009-04-28 17:00:00', 'Florida'),
-   (4, 2, 8, current_timestamp, 'Missouri');
 
 create view home_teams_games as
   select t.team_number, g.game_number, g.game_time
@@ -122,20 +105,6 @@ create table players
    primary key (player_number, player_team_number))
   no partition;
 
-insert into players values
-   (1, 'Tom', 1, '4083948394', null),
-   (2, 'Bob', 1, '4089483948', null),
-   (3, 'Toby',1, '4082938493', 'pitcher'),
-   (3, 'Toby',2, '4082938493', null),
-   (4, 'Julie', 2, '5108394839', 'catcher'),
-   (5, 'Joanne', 2, '5103849384', null),
-   (6, 'Pete', 2, '5102839483', null),
-   (6, 'Pete', 3, '5102839483', 'third base'),
-   (7, 'Jared',4, '9518293849', 'short stop'),
-   (8, 'Zachary', 4, '9518293840', null),
-   (9, 'Lynne', 5, '9518293892', 'pitcher'),
-   (10, 'Omar', 5, '5128394893', null);
-
 alter table players add constraint valid_player_number check(player_number > 0);
 
 create view players_on_team as
@@ -143,7 +112,6 @@ create view players_on_team as
   from teams t, players p
   where p.player_team_number = t.team_number
   order by t.team_name;
-select * from players_on_team;
 
 create view games_by_player as
   select player_name, game_time
@@ -155,12 +123,6 @@ create view games_by_player as
 alter table players add constraint players_teams
    foreign key (player_team_number) references teams (team_number);
 
-insert into team_statistics
-  select team_number, count (player_number)
-   from teams t, players p
-   where t.team_number = p.player_team_number
-   group by team_number;
-
 create volatile table home_games as
   select home_team_number, visitor_team_number, game_number,  game_location
   from games
@@ -171,22 +133,34 @@ create volatile table home_games as
 
 create sequence team_number_sequence;
 
+-- create function to display bitmaps as a bitmap rather than longs
+sh rm -f ./etest141.dll;
+sh sh $$scriptsdir$$/tools/dll-compile.ksh etest141.cpp
+  2>&1 | tee LOG133-SECONDARY;
+set pattern $$DLL$$ etest141.dll;
+set pattern $$QUOTE$$ '''';
+
+create library t133_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$ ;
+create function translateBitmap(bitmap largeint) returns (bitmap_string char (20))
+language c parameter style sql external name 'translateBitmap'
+library t133_l1
+deterministic no sql final call allow any parallelism state area size 1024 ;
+
 ?section set_up
 set schema "_PRIVMGR_MD_";
 prepare get_owner_privs from
 select distinct
    substring (object_name,1,40) as object_name,
    object_type as type,
+   substring(authname(grantor_id),1,10) as grantor,
    substring(authname(grantee_id),1,10) as grantee,
-   privileges_bitmap,
-   grantable_bitmap
-from object_privileges 
-where grantor_id = -2
-    and object_uid in 
+   t133sch.translateBitmap(privileges_bitmap) as granted_privs,
+   t133sch.translateBitmap(grantable_bitmap) as grantable_privs
+from "_PRIVMGR_MD_".object_privileges
+where object_uid in
      (select object_uid
       from "_MD_".objects
-      where object_type in ('VI','BT','LB','UR')
-        and schema_name in ('_PRIVMGR_MD_', 'T133SCH'))
+      where schema_name in ('_PRIVMGR_MD_', 'T133SCH', '_HV_HIVE_'))
   order by 1, 2, 3, 4, 5
 ;
 
@@ -195,26 +169,40 @@ where grantor_id = -2
 -- this set of tests run basic initialize authorization tests
 -- =================================================================
 
+obey TEST133(create_db);
 set schema "_PRIVMGR_MD_";
 get tables;
 
+-- verify can't create an object in "_PRIVMGR_MD_" schema
 create table t133_t1 (c1 int not null primary key, c2 int);
-create view t133_v1 as select * from t133_t1;
-create index t133_i1 on t133_t1(c2);
-create table t133_t2 like t133_t1;
 
 obey TEST133(set_up);
-get tables;
 execute get_owner_privs;
 
 initialize authorization, drop;
 get tables;
+
+-- owner s/b db__root
+create external table customer for hive.hive.customer;
+create external table item for hive.hive.item;
+create external table promotion for hive.hive.promotion;
+select schema_owner, object_owner, substring (object_name,1,20) as object_name 
+from "_MD_".objects where schema_name = '_HV_HIVE_';
+
 initialize authorization;
 get tables;
 select count (*) from components;
 select count (*) from component_operations;
 select count (*) from component_privileges;
+
+-- external Hive schema now owned by DB__HIVEROLE
+select schema_owner, object_owner, substring (object_name,1,20) as object_name 
+from "_MD_".objects where schema_name = '_HV_HIVE_';
+
+-- External hive table show owner as DB__HIVEROLE
 execute get_owner_privs;
+
+-- drop role_usage and make sure initialize authorization recreates
 set parserflags 131072;
 drop table role_usage;
 get tables;
@@ -223,6 +211,7 @@ initialize authorization;
 get tables;
 execute get_owner_privs;
 
+-- drop components and make sure initialize authorization recreates
 drop table components;
 get tables;
 initialize authorization, drop;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/sqlcomp/CmpSeabaseDDL.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDL.h b/core/sql/sqlcomp/CmpSeabaseDDL.h
index 243c95e..96a0882 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDL.h
+++ b/core/sql/sqlcomp/CmpSeabaseDDL.h
@@ -1136,6 +1136,7 @@ class CmpSeabaseDDL
   short createLibmgrProcs(ExeCliInterface * cliInterface);
   short grantLibmgrPrivs(ExeCliInterface *cliInterface);
 
+  short adjustHiveExternalSchemas(ExeCliInterface *cliInterface);
 
   void createSeabaseSequence(StmtDDLCreateSequence  * createSequenceNode,
 			     NAString &currCatName, NAString &currSchName);

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
index 781ee1f..1a4acbe 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
+++ b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp
@@ -7667,6 +7667,15 @@ short CmpSeabaseDDL::initSeabaseAuthorization(
      for (short i = 0; i < GetCliGlobals()->currContext()->getNumArkcmps(); i++)
        GetCliGlobals()->currContext()->getArkcmp(i)->endConnection();
 
+     // Adjust hive external table ownership - if someone creates external 
+     // tables before initializing authorization, the external schemas are 
+     // owned by DB__ROOT -> change to DB__HIVEROLE.  
+     // Also if you have initialized authorization and created external tables 
+     // before the fix for JIRA 1895, rerunning initialize authorization will 
+     // fix the metadata inconsistencies
+     if (adjustHiveExternalSchemas(cliInterface) != 0)
+       return -1;
+
      // If someone initializes trafodion with library management but does not 
      // initialize authorization, then the role DB__LIBMGRROLE has not been 
      // granted to LIBMGR procedures.  Do this now
@@ -7676,6 +7685,7 @@ short CmpSeabaseDDL::initSeabaseAuthorization(
                                     COM_LIBRARY_OBJECT, TRUE, FALSE);
      if (cliRC == 1) // library exists
        cliRC = grantLibmgrPrivs(cliInterface);
+
   }
   else
   {

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp
index 67fd24f..a96792b 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp
+++ b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp
@@ -1132,6 +1132,99 @@ short CmpSeabaseDDL::createHistogramTables(
 
 // *****************************************************************************
 // *                                                                           *
+// * Function: adjustHiveExternalSchemas                                       *
+// *                                                                           *
+// *    Changes the ownership and privilege grants to DB__HIVEROLE             *
+// *                                                                           *
+// *****************************************************************************
+// *                                                                           *
+// *  Parameters:                                                              *
+// *                                                                           *
+// *  <cliInterface>                  ExeCliInterface *               In       *
+// *    is a reference to an Executor CLI interface handle.                    *
+// *****************************************************************************
+// *                                                                           *
+// * Returns: Int32                                                            *
+// *                                                                           *
+// *            0: Adjustment was successful                                   *
+// *           -1: Adjustment failed                                           *
+// *                                                                           *
+// *****************************************************************************
+short CmpSeabaseDDL::adjustHiveExternalSchemas(ExeCliInterface *cliInterface)
+{
+  char buf[sizeof(SEABASE_MD_SCHEMA) + 
+           sizeof(SEABASE_OBJECTS) + 
+           strlen(getSystemCatalog()) + 300];
+
+  // get all the objects in special hive schemas
+  sprintf(buf, "SELECT catalog_name, schema_name, object_name, object_uid, object_type, object_owner
"
+               " from %s.\"%s\".%s WHERE schema_name like '_HV_%c_'",
+               getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, '%');
+
+   Queue * objectsQueue = NULL;
+   Int32 cliRC = cliInterface->fetchAllRows(objectsQueue, buf, 0, FALSE, FALSE, TRUE);
+   if (cliRC < 0)
+   {
+      cliInterface->retrieveSQLDiagnostics(CmpCommon::diags());
+      return -1;
+   }
+
+   // adjust owner and privilege information for external hive objects
+   objectsQueue->position();
+   for (size_t i = 0; i < objectsQueue->numEntries(); i++)
+   {
+     OutputInfo * vi = (OutputInfo*)objectsQueue->getNext();
+     NAString catName = vi->get(0);
+     NAString schName = vi->get(1);
+     NAString objName = vi->get(2);
+     Int64 objUID     = *(Int64*)vi->get(3);
+     NAString objectTypeLit = vi->get(4);
+     Int32 objOwner   = *(Int32*)vi->get(5);
+     ComObjectType objType = PrivMgr::ObjectLitToEnum(objectTypeLit.data());
+
+     // If object owner is already the HIVE_ROLE_ID, then we are done.
+     if (objOwner == HIVE_ROLE_ID)
+       continue;
+     else
+     {
+       // only need to adjust privileges on securable items
+       if (PrivMgr::isSecurableObject(objType))
+       {
+         ComObjectName tblName(catName, schName, objName, COM_TABLE_NAME, 
+                               ComAnsiNamePart::INTERNAL_FORMAT, STMTHEAP);
+
+         NAString extTblName = tblName.getExternalName(TRUE);
+
+         // remove existing privs on object
+         if (!deletePrivMgrInfo(extTblName, objUID, objType))
+           return -1;
+
+         // add owner privs
+         if (!insertPrivMgrInfo(objUID, extTblName, objType, 
+                                HIVE_ROLE_ID, HIVE_ROLE_ID, ComUser::getCurrentUser()))
+           return -1;
+       }
+
+       // update schema_owner and objectOwner for object
+       sprintf(buf,"UPDATE %s.\"%s\".%s SET object_owner = %d "
+                   ", schema_owner = %d WHERE object_uid = %ld ",
+                   getSystemCatalog(),SEABASE_MD_SCHEMA,SEABASE_OBJECTS, 
+                   HIVE_ROLE_ID, HIVE_ROLE_ID, objUID);
+       cliRC = cliInterface->executeImmediate(buf);
+       if (cliRC < 0)
+       {
+         cliInterface->retrieveSQLDiagnostics(CmpCommon::diags());
+         return -1;
+       }
+     }
+  }
+
+  return 0;
+}
+//********************* End of adjustHiveExternalTables ************************
+
+// *****************************************************************************
+// *                                                                           *
 // * Function: dropOneTable                                                    *
 // *                                                                           *
 // *    Drops a table and all its dependent objects.                           *
@@ -1278,17 +1371,19 @@ NAString privMgrMDLoc;
 PrivMgrCommands privInterface(std::string(privMgrMDLoc.data()),CmpCommon::diags());
    
 std::vector<UIDAndOwner> objectRows;
-std::string whereClause(" WHERE catalogName = ");
+std::string whereClause(" WHERE catalog_name = '");
    
    whereClause += catalogName;
-   whereClause += " AND schema_name = ";
+   whereClause += "' AND schema_name = '";
    whereClause += schemaName;
+   whereClause += "'";
    
 std::string orderByClause(" ORDER BY OBJECT_OWNER");
 std::string metadataLocation(systemCatalogName);  
       
-   metadataLocation += ".";
+   metadataLocation += ".\"";
    metadataLocation += SEABASE_MD_SCHEMA;
+   metadataLocation += "\"";
       
 PrivMgrObjects objects(metadataLocation,CmpCommon::diags());
    

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6950f11d/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
index ce3349b..078cc99 100644
--- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
+++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp
@@ -485,7 +485,7 @@ short CmpSeabaseDDL::createSeabaseTableExternal(
 
   if (isAuthorizationEnabled())
     {
-      if (srcTableName.isExternalHive())
+      if (tgtTableName.isExternalHive())
         {
           tableInfo->objOwnerID = HIVE_ROLE_ID;
           tableInfo->schemaOwnerID = HIVE_ROLE_ID;



Mime
View raw message