sentry-tests/sentry-tests-hive-v2/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbComplexView.java - sentry - Git at Google

 /*
  * 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.
  */

 package org.apache.sentry.tests.e2e.dbprovider;

 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;

 import java.util.ArrayList;
 import java.util.List;

 import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.assertFalse;

 import org.apache.sentry.core.common.utils.PolicyFile;
 import org.apache.sentry.tests.e2e.hive.AbstractTestWithStaticConfiguration;

 import org.junit.Before;
 import org.junit.BeforeClass;
 import org.junit.Ignore;
 import org.junit.Test;

 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;

 public class TestDbComplexView extends AbstractTestWithStaticConfiguration {

     private static final Logger LOGGER = LoggerFactory
             .getLogger(TestDbComplexView.class);

     private static final String TEST_VIEW_DB = "test_complex_view_database";
     private static final String TEST_VIEW_TB = "test_complex_view_table";
     private static final String TEST_VIEW_TB2 = "test_complex_view_table_2";
     private static final String TEST_VIEW = "test_complex_view";
     private static final String TEST_VIEW_ROLE = "test_complex_view_role";

     /**
      * Run query and validate one column with given column name
      * @param user
      * @param sql
      * @param db
      * @param colName
      * @param colVal
      * @return
      * @throws Exception
      */
     private static boolean execValidate(String user, String sql, String db,
                                         String colName, String colVal) throws Exception {
         boolean status = false;
         Connection conn = null;
         Statement stmt = null;
         try {
             conn = context.createConnection(user);
             stmt = context.createStatement(conn);
             LOGGER.info("Running [USE " + db + ";" + sql + "] to validate column " +  colName + " = " + colVal);
             stmt.execute("USE " + db);
             ResultSet rset = stmt.executeQuery(sql);
             while (rset.next()) {
                 String val = rset.getString(colName);
                 if (val.equalsIgnoreCase(colVal)) {
                     LOGGER.info("found [" + colName + "] = " + colVal);
                     status = true;
                     break;
                 } else {
                     LOGGER.warn("[" + colName + "] = " + val + " not equal to " + colVal);
                 }
             }
             rset.close();
         } catch (SQLException ex) {
             LOGGER.error("SQLException: ", ex);
         } catch (Exception ex) {
             LOGGER.error("Exception: ", ex);
         } finally {
             try {
                 if (stmt != null) {
                   stmt.close();
                 }
                 if (conn != null) {
                   conn.close();
                 }
             } catch (Exception ex) {
                 LOGGER.error("failed to close connection and statement: " + ex);
             }
         }
         return status;
     }

     @BeforeClass
     public static void setupTestStaticConfiguration() throws Exception {
         useSentryService = true;
         AbstractTestWithStaticConfiguration.setupTestStaticConfiguration();
     }

     @Override
     @Before
     public void setup() throws Exception {
         super.setupAdmin();
         super.setup();
         PolicyFile.setAdminOnServer1(ADMINGROUP);

         // prepare test db and base table
         List<String> sqls = new ArrayList<String>();
         sqls.add("USE DEFAULT");
         sqls.add("DROP DATABASE IF EXISTS " + TEST_VIEW_DB + " CASCADE");
         sqls.add("CREATE DATABASE IF NOT EXISTS " + TEST_VIEW_DB);
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("CREATE TABLE " + TEST_VIEW_TB + " (userid VARCHAR(64), link STRING, source STRING) "
             + "PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC");
         sqls.add("INSERT INTO TABLE " + TEST_VIEW_TB + " PARTITION (datestamp = '2014-09-23') VALUES "
             + "('tlee', " + "'mail.com', 'sports.com'), ('jdoe', 'mail.com', null)");
         sqls.add("SELECT userid FROM " + TEST_VIEW_TB);
         sqls.add("CREATE TABLE " + TEST_VIEW_TB2 + " (userid VARCHAR(64), name VARCHAR(64), age INT, "
             + "gpa DECIMAL(3, 2)) CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC");
         sqls.add("INSERT INTO TABLE " + TEST_VIEW_TB2 + " VALUES ('rgates', 'Robert Gates', 35, 1.28), "
             + "('tlee', 'Tod Lee', 32, 2.32)");
         sqls.add("SELECT * FROM " + TEST_VIEW_TB2);
         execBatch(ADMIN1, sqls);
     }

     private void createTestRole(String user, String roleName) throws Exception {
         Connection conn = context.createConnection(user);
         Statement stmt = conn.createStatement();
         try {
             exec(stmt, "DROP ROLE " + roleName);
         } catch (Exception ex) {
             LOGGER.info("test role doesn't exist, but it's ok");
         } finally {
             exec(stmt, "CREATE ROLE " + roleName);
         }
         if (stmt != null) {
             stmt.close();
         }
         if (conn != null) {
             conn.close();
         }
     }

     private void grantAndValidatePrivilege(String testView, String testRole, String testGroup,
                                            String user, boolean revoke) throws Exception {
         createTestRole(ADMIN1, testRole);
         List<String> sqls = new ArrayList<String>();

         // grant privilege
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("GRANT SELECT ON TABLE " + testView + " TO ROLE " + testRole);
         sqls.add("GRANT ROLE " + testRole + " TO GROUP " + testGroup);
         execBatch(ADMIN1, sqls);

         // show grant should pass and could list view
         assertTrue("can not find select privilege from " + testRole,
                 execValidate(ADMIN1, "SHOW GRANT ROLE " + testRole + " ON TABLE " + testView,
                         TEST_VIEW_DB, "privilege", "select"));
         assertTrue("can not find " + testView,
             execValidate(user, "SHOW TABLES", TEST_VIEW_DB, "tab_name", testView));

         // select from view should pass
         sqls.clear();
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("SELECT * FROM " + testView);
         execBatch(user, sqls);

         if (revoke) {
             // revoke privilege
             sqls.clear();
             sqls.add("USE " + TEST_VIEW_DB);
             sqls.add("REVOKE SELECT ON TABLE " + testView + " FROM ROLE " + testRole);
             execBatch(ADMIN1, sqls);

             // shouldn't be able to show grant
             assertFalse("should not find select from " + testRole,
                 execValidate(ADMIN1, "SHOW GRANT ROLE " + testRole + " ON TABLE " + testView,
                     TEST_VIEW_DB, "privilege", "select"));

             // select from view should fail
             sqls.clear();
             sqls.add("USE " + TEST_VIEW_DB);
             sqls.add("SELECT * FROM " + testView);
             try {
                 execBatch(user, sqls);
             } catch (SQLException ex) {
                 LOGGER.info("Expected SQLException here", ex);
             }
         }
     }

     private void grantAndValidatePrivilege(String testView, String testRole,
                                            String testGroup, String user) throws Exception {
         grantAndValidatePrivilege(testView, testRole, testGroup, user, true);
     }
     /**
      * Create view1 and view2 from view1
      * Grant and validate select privileges to both views
      * @throws Exception
      */
     @Test
     public void testDbViewFromView() throws Exception {
         List<String> sqls = new ArrayList<String>();
         // create a simple view
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("CREATE VIEW " + TEST_VIEW +
                 "(userid,link) AS SELECT userid,link from " + TEST_VIEW_TB);

         // create another view from the previous view
         String testView2 = "view1_from_" + TEST_VIEW;
         //String testRole2 = testView2 + "_test_role";
         sqls.add(String.format("CREATE VIEW %s AS SELECT userid,link from %s",
             testView2, TEST_VIEW));

         String testView3 = "view2_from_" + TEST_VIEW;
         sqls.add(String.format("CREATE VIEW %s(userid,link) AS SELECT userid,link from %s",
             testView3, TEST_VIEW));

         execBatch(ADMIN1, sqls);

         // validate privileges
         grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
         //grantAndValidatePrivilege(testView2, testRole2, USERGROUP2, USER2_1);

         // Disabled because of SENTRY-745, also need to backport HIVE-10875
         //grantAndValidatePrivilege(testView3, testRole3, USERGROUP3, USER3_1);
     }

     /**
      * Create a view by join two tables
      * Grant and verify select privilege
      * @throws Exception
      */
     @Test
     public void TestDbViewWithJoin() throws Exception {
         List<String> sqls = new ArrayList<String>();
         // create a joint view
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add(String.format("create view %s as select name,age,gpa from %s join %s on "
                 + "(%s.userid=%s.userid) where name='Tod Lee'", TEST_VIEW, TEST_VIEW_TB2,
             TEST_VIEW_TB, TEST_VIEW_TB2, TEST_VIEW_TB));
         execBatch(ADMIN1, sqls);

         // validate privileges
         grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
     }

     /**
      * Create a view with nested query
      * Grant and verify select privilege
      * @throws Exception
      * SENTRY-716: Hive plugin does not correctly enforce
      * privileges for new in case of nested queries
      * Once backport HIVE-10875 to Sentry repo, will enable this test.
      */
     @Ignore ("After SENTRY-716 is fixed, turn on this test")
     @Test
     public void TestDbViewWithNestedQuery() throws Exception {
         List<String> sqls = new ArrayList<String>();
         // create a joint view
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("CREATE VIEW " + TEST_VIEW + " AS SELECT * FROM " + TEST_VIEW_TB);
         execBatch(ADMIN1, sqls);
         grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1, false);

         sqls.clear();
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("SELECT * FROM (SELECT * FROM " + TEST_VIEW + ") v2");
         execBatch(USER1_1, sqls);
     }

     /**
      * Create a view with union two tables
      * Grant and verify select privilege
      * @throws Exception
      * SENTRY-747: Create a view by union tables, grant select
      * then select from view encounter errors
      * Once backport HIVE-10875 to Sentry repo, will enable this test.
      */
     @Ignore ("After SENTRY-747 is fixed, turn on this test")
     @Test
     public void TestDbViewWithUnion() throws Exception {
         List<String> sqls = new ArrayList<String>();
         String testTable = "test_user_info";
         sqls.add("USE " + TEST_VIEW_DB);
         sqls.add("DROP TABLE IF EXISTS " + testTable);
         sqls.add("CREATE TABLE " + testTable + " (userid VARCHAR(64), name STRING, address STRING, tel STRING) ");
         sqls.add("INSERT INTO TABLE " + testTable + " VALUES "
                 + "('tlee', " + "'Tod Lee', '1234 23nd Ave SFO, CA', '123-456-7890')");
         sqls.add("SELECT * FROM " + testTable);
         sqls.add(String.format("CREATE VIEW " + TEST_VIEW + " AS "
                         + "SELECT u.userid, u.name, u.address, res.uid "
                         + "FROM ("
                         + "SELECT t1.userid AS uid "
                         + "FROM %s t1 "
                         + "UNION ALL "
                         + "SELECT t2.userid AS uid "
                         + "FROM %s t2 "
                         + ") res JOIN %s u ON (u.userid = res.uid)",
                 TEST_VIEW_TB, TEST_VIEW_TB2, testTable));
         execBatch(ADMIN1, sqls);
         grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
     }
 }
	/*
	* 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.
	*/

	package org.apache.sentry.tests.e2e.dbprovider;

	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;

	import java.util.ArrayList;
	import java.util.List;

	import static org.junit.Assert.assertTrue;
	import static org.junit.Assert.assertFalse;

	import org.apache.sentry.core.common.utils.PolicyFile;
	import org.apache.sentry.tests.e2e.hive.AbstractTestWithStaticConfiguration;

	import org.junit.Before;
	import org.junit.BeforeClass;
	import org.junit.Ignore;
	import org.junit.Test;

	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;

	public class TestDbComplexView extends AbstractTestWithStaticConfiguration {

	private static final Logger LOGGER = LoggerFactory
	.getLogger(TestDbComplexView.class);

	private static final String TEST_VIEW_DB = "test_complex_view_database";
	private static final String TEST_VIEW_TB = "test_complex_view_table";
	private static final String TEST_VIEW_TB2 = "test_complex_view_table_2";
	private static final String TEST_VIEW = "test_complex_view";
	private static final String TEST_VIEW_ROLE = "test_complex_view_role";

	/**
	* Run query and validate one column with given column name
	* @param user
	* @param sql
	* @param db
	* @param colName
	* @param colVal
	* @return
	* @throws Exception
	*/
	private static boolean execValidate(String user, String sql, String db,
	String colName, String colVal) throws Exception {
	boolean status = false;
	Connection conn = null;
	Statement stmt = null;
	try {
	conn = context.createConnection(user);
	stmt = context.createStatement(conn);
	LOGGER.info("Running [USE " + db + ";" + sql + "] to validate column " + colName + " = " + colVal);
	stmt.execute("USE " + db);
	ResultSet rset = stmt.executeQuery(sql);
	while (rset.next()) {
	String val = rset.getString(colName);
	if (val.equalsIgnoreCase(colVal)) {
	LOGGER.info("found [" + colName + "] = " + colVal);
	status = true;
	break;
	} else {
	LOGGER.warn("[" + colName + "] = " + val + " not equal to " + colVal);
	}
	}
	rset.close();
	} catch (SQLException ex) {
	LOGGER.error("SQLException: ", ex);
	} catch (Exception ex) {
	LOGGER.error("Exception: ", ex);
	} finally {
	try {
	if (stmt != null) {
	stmt.close();
	}
	if (conn != null) {
	conn.close();
	}
	} catch (Exception ex) {
	LOGGER.error("failed to close connection and statement: " + ex);
	}
	}
	return status;
	}

	@BeforeClass
	public static void setupTestStaticConfiguration() throws Exception {
	useSentryService = true;
	AbstractTestWithStaticConfiguration.setupTestStaticConfiguration();
	}

	@Override
	@Before
	public void setup() throws Exception {
	super.setupAdmin();
	super.setup();
	PolicyFile.setAdminOnServer1(ADMINGROUP);

	// prepare test db and base table
	List<String> sqls = new ArrayList<String>();
	sqls.add("USE DEFAULT");
	sqls.add("DROP DATABASE IF EXISTS " + TEST_VIEW_DB + " CASCADE");
	sqls.add("CREATE DATABASE IF NOT EXISTS " + TEST_VIEW_DB);
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("CREATE TABLE " + TEST_VIEW_TB + " (userid VARCHAR(64), link STRING, source STRING) "
	+ "PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC");
	sqls.add("INSERT INTO TABLE " + TEST_VIEW_TB + " PARTITION (datestamp = '2014-09-23') VALUES "
	+ "('tlee', " + "'mail.com', 'sports.com'), ('jdoe', 'mail.com', null)");
	sqls.add("SELECT userid FROM " + TEST_VIEW_TB);
	sqls.add("CREATE TABLE " + TEST_VIEW_TB2 + " (userid VARCHAR(64), name VARCHAR(64), age INT, "
	+ "gpa DECIMAL(3, 2)) CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC");
	sqls.add("INSERT INTO TABLE " + TEST_VIEW_TB2 + " VALUES ('rgates', 'Robert Gates', 35, 1.28), "
	+ "('tlee', 'Tod Lee', 32, 2.32)");
	sqls.add("SELECT * FROM " + TEST_VIEW_TB2);
	execBatch(ADMIN1, sqls);
	}

	private void createTestRole(String user, String roleName) throws Exception {
	Connection conn = context.createConnection(user);
	Statement stmt = conn.createStatement();
	try {
	exec(stmt, "DROP ROLE " + roleName);
	} catch (Exception ex) {
	LOGGER.info("test role doesn't exist, but it's ok");
	} finally {
	exec(stmt, "CREATE ROLE " + roleName);
	}
	if (stmt != null) {
	stmt.close();
	}
	if (conn != null) {
	conn.close();
	}
	}

	private void grantAndValidatePrivilege(String testView, String testRole, String testGroup,
	String user, boolean revoke) throws Exception {
	createTestRole(ADMIN1, testRole);
	List<String> sqls = new ArrayList<String>();

	// grant privilege
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("GRANT SELECT ON TABLE " + testView + " TO ROLE " + testRole);
	sqls.add("GRANT ROLE " + testRole + " TO GROUP " + testGroup);
	execBatch(ADMIN1, sqls);

	// show grant should pass and could list view
	assertTrue("can not find select privilege from " + testRole,
	execValidate(ADMIN1, "SHOW GRANT ROLE " + testRole + " ON TABLE " + testView,
	TEST_VIEW_DB, "privilege", "select"));
	assertTrue("can not find " + testView,
	execValidate(user, "SHOW TABLES", TEST_VIEW_DB, "tab_name", testView));

	// select from view should pass
	sqls.clear();
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("SELECT * FROM " + testView);
	execBatch(user, sqls);

	if (revoke) {
	// revoke privilege
	sqls.clear();
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("REVOKE SELECT ON TABLE " + testView + " FROM ROLE " + testRole);
	execBatch(ADMIN1, sqls);

	// shouldn't be able to show grant
	assertFalse("should not find select from " + testRole,
	execValidate(ADMIN1, "SHOW GRANT ROLE " + testRole + " ON TABLE " + testView,
	TEST_VIEW_DB, "privilege", "select"));

	// select from view should fail
	sqls.clear();
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("SELECT * FROM " + testView);
	try {
	execBatch(user, sqls);
	} catch (SQLException ex) {
	LOGGER.info("Expected SQLException here", ex);
	}
	}
	}

	private void grantAndValidatePrivilege(String testView, String testRole,
	String testGroup, String user) throws Exception {
	grantAndValidatePrivilege(testView, testRole, testGroup, user, true);
	}
	/**
	* Create view1 and view2 from view1
	* Grant and validate select privileges to both views
	* @throws Exception
	*/
	@Test
	public void testDbViewFromView() throws Exception {
	List<String> sqls = new ArrayList<String>();
	// create a simple view
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("CREATE VIEW " + TEST_VIEW +
	"(userid,link) AS SELECT userid,link from " + TEST_VIEW_TB);

	// create another view from the previous view
	String testView2 = "view1_from_" + TEST_VIEW;
	//String testRole2 = testView2 + "_test_role";
	sqls.add(String.format("CREATE VIEW %s AS SELECT userid,link from %s",
	testView2, TEST_VIEW));

	String testView3 = "view2_from_" + TEST_VIEW;
	sqls.add(String.format("CREATE VIEW %s(userid,link) AS SELECT userid,link from %s",
	testView3, TEST_VIEW));

	execBatch(ADMIN1, sqls);

	// validate privileges
	grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
	//grantAndValidatePrivilege(testView2, testRole2, USERGROUP2, USER2_1);

	// Disabled because of SENTRY-745, also need to backport HIVE-10875
	//grantAndValidatePrivilege(testView3, testRole3, USERGROUP3, USER3_1);
	}

	/**
	* Create a view by join two tables
	* Grant and verify select privilege
	* @throws Exception
	*/
	@Test
	public void TestDbViewWithJoin() throws Exception {
	List<String> sqls = new ArrayList<String>();
	// create a joint view
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add(String.format("create view %s as select name,age,gpa from %s join %s on "
	+ "(%s.userid=%s.userid) where name='Tod Lee'", TEST_VIEW, TEST_VIEW_TB2,
	TEST_VIEW_TB, TEST_VIEW_TB2, TEST_VIEW_TB));
	execBatch(ADMIN1, sqls);

	// validate privileges
	grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
	}

	/**
	* Create a view with nested query
	* Grant and verify select privilege
	* @throws Exception
	* SENTRY-716: Hive plugin does not correctly enforce
	* privileges for new in case of nested queries
	* Once backport HIVE-10875 to Sentry repo, will enable this test.
	*/
	@Ignore ("After SENTRY-716 is fixed, turn on this test")
	@Test
	public void TestDbViewWithNestedQuery() throws Exception {
	List<String> sqls = new ArrayList<String>();
	// create a joint view
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("CREATE VIEW " + TEST_VIEW + " AS SELECT * FROM " + TEST_VIEW_TB);
	execBatch(ADMIN1, sqls);
	grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1, false);

	sqls.clear();
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("SELECT * FROM (SELECT * FROM " + TEST_VIEW + ") v2");
	execBatch(USER1_1, sqls);
	}

	/**
	* Create a view with union two tables
	* Grant and verify select privilege
	* @throws Exception
	* SENTRY-747: Create a view by union tables, grant select
	* then select from view encounter errors
	* Once backport HIVE-10875 to Sentry repo, will enable this test.
	*/
	@Ignore ("After SENTRY-747 is fixed, turn on this test")
	@Test
	public void TestDbViewWithUnion() throws Exception {
	List<String> sqls = new ArrayList<String>();
	String testTable = "test_user_info";
	sqls.add("USE " + TEST_VIEW_DB);
	sqls.add("DROP TABLE IF EXISTS " + testTable);
	sqls.add("CREATE TABLE " + testTable + " (userid VARCHAR(64), name STRING, address STRING, tel STRING) ");
	sqls.add("INSERT INTO TABLE " + testTable + " VALUES "
	+ "('tlee', " + "'Tod Lee', '1234 23nd Ave SFO, CA', '123-456-7890')");
	sqls.add("SELECT * FROM " + testTable);
	sqls.add(String.format("CREATE VIEW " + TEST_VIEW + " AS "
	+ "SELECT u.userid, u.name, u.address, res.uid "
	+ "FROM ("
	+ "SELECT t1.userid AS uid "
	+ "FROM %s t1 "
	+ "UNION ALL "
	+ "SELECT t2.userid AS uid "
	+ "FROM %s t2 "
	+ ") res JOIN %s u ON (u.userid = res.uid)",
	TEST_VIEW_TB, TEST_VIEW_TB2, testTable));
	execBatch(ADMIN1, sqls);
	grantAndValidatePrivilege(TEST_VIEW, TEST_VIEW_ROLE, USERGROUP1, USER1_1);
	}
	}