@Test
  public void test() throws Exception {

    String db1Name = "testdb1";
    String db2Name = "testdb2";
    String table1Name = "testtable1";
    String table2Name = "testtable2";
    String table3Name = "testtable3";
    String table4Name = "testtable4";
    CLIServiceClient serviceClient = miniHS2.getServiceClient();
    SessionHandle sessHandle = serviceClient.openSession("hive_test_user", "");
    serviceClient.executeStatement(sessHandle, "DROP DATABASE IF EXISTS " + db1Name + " CASCADE", confOverlay);
    serviceClient.executeStatement(sessHandle, "CREATE DATABASE " + db1Name, confOverlay);
    serviceClient.executeStatement(sessHandle, "DROP TABLE IF EXISTS " + db1Name + "." + table1Name, confOverlay);
    serviceClient.executeStatement(sessHandle,
        "CREATE TABLE " + db1Name + "." + table1Name + "(key string, value double)", confOverlay);
    serviceClient.executeStatement(sessHandle, "DROP TABLE IF EXISTS " + db1Name + "." + table2Name, confOverlay);
    serviceClient.executeStatement(sessHandle,
        "CREATE TABLE " + db1Name + "." + table2Name + "(key string, value double)", confOverlay);
    serviceClient.executeStatement(sessHandle, "DROP VIEW IF EXISTS " + db1Name + "." + table3Name, confOverlay);
    serviceClient.executeStatement(sessHandle,
        "CREATE VIEW " + db1Name + "." + table3Name + " AS SELECT * FROM " + db1Name + "." + table1Name, confOverlay);
    serviceClient.executeStatement(sessHandle, "DROP TABLE IF EXISTS " + db1Name + "." + table4Name, confOverlay);
    serviceClient.executeStatement(sessHandle,
        "CREATE TABLE " + db1Name + "." + table4Name + "(key string, value double) PARTITIONED BY (p string)",
        confOverlay);

    serviceClient.executeStatement(sessHandle, "DROP DATABASE IF EXISTS " + db2Name + " CASCADE", confOverlay);
    serviceClient.executeStatement(sessHandle, "CREATE DATABASE " + db2Name, confOverlay);
    serviceClient.executeStatement(sessHandle, "DROP TABLE IF EXISTS " + db2Name + "." + table1Name, confOverlay);
    serviceClient.executeStatement(sessHandle,
        "CREATE TABLE " + db2Name + "." + table1Name + "(key string, value double)", confOverlay);

    // Just to trigger auto creation of needed metastore tables
    serviceClient.executeStatement(sessHandle, "SHOW GRANT USER hive_test_user ON ALL", confOverlay);
    serviceClient.closeSession(sessHandle);

    List<String> baseArgs = new ArrayList<String>();
    baseArgs.add("-d");
    baseArgs.add(BeeLine.BEELINE_DEFAULT_JDBC_DRIVER);
    baseArgs.add("-u");
    baseArgs.add(miniHS2.getBaseJdbcURL());
    baseArgs.add("-n");
    baseArgs.add("hive_test_user");

    List<String> args = new ArrayList<String>(baseArgs);
    args.add("-f");
    args.add("../../standalone-metastore/metastore-server/src/main/sql/hive/hive-schema-" + hiveSchemaVer + ".hive.sql");
    BeeLine beeLine = new BeeLine();
    int result = beeLine.begin(args.toArray(new String[] {}), null);
    beeLine.close();
    Assert.assertEquals(result, 0);

    boolean containsDb1 = false;
    boolean containsDb2 = false;
    boolean containsDb1Table1 = false;
    boolean containsDb1Table2 = false;
    boolean containsDb1Table3 = false;
    boolean containsDb1Table4 = false;
    boolean containsDb2Table1 = false;
    boolean containsDb1Table1SelectPriv = false;
    boolean containsDb1Table1UpdatePriv = false;
    boolean containsDb1Table2SelectPriv = false;
    boolean containsDb1Table3SelectPriv = false;
    boolean containsDb1Table4SelectPriv = false;
    boolean containsDb2Table1SelectPriv = false;
    boolean containsDb1Table1Key = false;
    boolean containsDb1Table1Value = false;
    boolean containsDb1Table2Key = false;
    boolean containsDb1Table2Value = false;
    boolean containsDb1Table3Key = false;
    boolean containsDb1Table3Value = false;
    boolean containsDb1Table4Key = false;
    boolean containsDb1Table4Value = false;
    boolean containsDb1Table4P = false;
    boolean containsDb2Table1Key = false;

    // We shall have enough time to synchronize privileges during loading
    // information schema

    // User1 privileges:
    // testdb1:            S
    //   testtable1.*:     SU
    //   testtable2.*:     S
    //   testtable3.*:     S
    //   testtable4.*:
    // testdb2:            S
    //   testtable1.*:     S
    sessHandle = serviceClient.openSession("user1", "");
    OperationHandle opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.SCHEMATA",
        confOverlay);
    RowSet rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 2);
    Iterator<Object[]> iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name)) {
        containsDb1 = true;
      } else if (cols[1].equals(db2Name)) {
        containsDb2 = true;
      }
    }
    Assert.assertTrue(containsDb1 && containsDb2);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLES", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 4);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table1Name)) {
        containsDb1Table1 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name)) {
        containsDb1Table2 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name)) {
        containsDb1Table3 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table4Name)) {
        containsDb1Table4 = true;
      } else if (cols[1].equals(db2Name) && cols[2].equals(table1Name)) {
        containsDb2Table1 = true;
      }
    }
    Assert.assertTrue(
        containsDb1Table1 && containsDb1Table2 && containsDb1Table3 && !containsDb1Table4 && containsDb2Table1);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.VIEWS", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 1);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table3Name)) {
        containsDb1Table3 = true;
      } else {
        containsDb1Table3 = false;
      }
    }
    Assert.assertTrue(containsDb1Table3);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 5);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("SELECT")) {
        containsDb1Table1SelectPriv = true;
      }
      if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("UPDATE")) {
        containsDb1Table1UpdatePriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("SELECT")) {
        containsDb1Table2SelectPriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("SELECT")) {
        containsDb1Table3SelectPriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table4Name) && cols[5].equals("SELECT")) {
        containsDb1Table4SelectPriv = true;
      } else if (cols[3].equals(db2Name) && cols[4].equals(table1Name) && cols[5].equals("SELECT")) {
        containsDb2Table1SelectPriv = true;
      }
    }
    Assert.assertTrue(containsDb1Table1SelectPriv && containsDb1Table1UpdatePriv && containsDb1Table2SelectPriv
        && containsDb1Table3SelectPriv && !containsDb1Table4SelectPriv && containsDb2Table1SelectPriv);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMNS", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 7);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table1Name) && cols[3].equals("key")) {
        containsDb1Table1Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table1Name) && cols[3].equals("value")) {
        containsDb1Table1Value = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name) && cols[3].equals("key")) {
        containsDb1Table2Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name) && cols[3].equals("value")) {
        containsDb1Table2Value = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name) && cols[3].equals("key")) {
        containsDb1Table3Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name) && cols[3].equals("value")) {
        containsDb1Table3Value = true;
      } else if (cols[1].equals(db2Name) && cols[2].equals(table1Name) && cols[3].equals("key")) {
        containsDb2Table1Key = true;
      }
    }
    Assert.assertTrue(containsDb1Table1Key && containsDb1Table1Value && containsDb1Table2Key && containsDb1Table2Value
        && containsDb1Table3Key && containsDb1Table3Value && containsDb2Table1Key);

    containsDb1Table1Key = false;
    containsDb1Table1Value = false;
    containsDb1Table2Key = false;
    containsDb1Table2Value = false;
    containsDb1Table3Key = false;
    containsDb1Table3Value = false;
    containsDb2Table1Key = false;
    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 7);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("key")) {
        containsDb1Table1Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("value")) {
        containsDb1Table1Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("key")) {
        containsDb1Table2Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("value")) {
        containsDb1Table2Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("key")) {
        containsDb1Table3Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("value")) {
        containsDb1Table3Value = true;
      } else if (cols[3].equals(db2Name) && cols[4].equals(table1Name) && cols[5].equals("key")) {
        containsDb2Table1Key = true;
      }
    }
    Assert.assertTrue(containsDb1Table1Key && containsDb1Table1Value && containsDb1Table2Key && containsDb1Table2Value
        && containsDb1Table3Key && containsDb1Table3Value && containsDb2Table1Key);
    serviceClient.closeSession(sessHandle);

    // User2 privileges:
    // testdb1: S
    // testtable1.*: S
    // testtable2.*: S
    // testtable3.*: S
    // testtable4.*: S
    // testdb2:
    // testtable1.*:
    sessHandle = serviceClient.openSession("user2", "");
    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.SCHEMATA", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 1);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name)) {
        containsDb1 = true;
      }
    }
    Assert.assertTrue(containsDb1);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLES", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 4);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table1Name)) {
        containsDb1Table1 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name)) {
        containsDb1Table2 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name)) {
        containsDb1Table3 = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table4Name)) {
        containsDb1Table4 = true;
      }
    }
    Assert.assertTrue(containsDb1Table1 && containsDb1Table2 && containsDb1Table3 && containsDb1Table4);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.VIEWS", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 1);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table3Name)) {
        containsDb1Table3 = true;
      } else {
        containsDb1Table3 = false;
      }
    }
    Assert.assertTrue(containsDb1Table3);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 4);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("SELECT")) {
        containsDb1Table1SelectPriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("SELECT")) {
        containsDb1Table2SelectPriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("SELECT")) {
        containsDb1Table3SelectPriv = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table4Name) && cols[5].equals("SELECT")) {
        containsDb1Table4SelectPriv = true;
      }
    }
    Assert.assertTrue(containsDb1Table1SelectPriv && containsDb1Table2SelectPriv && containsDb1Table3SelectPriv
        && containsDb1Table4SelectPriv);

    // db1.testtable3.p should also be in COLUMNS, will fix in separate ticket
    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMNS", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 8);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[1].equals(db1Name) && cols[2].equals(table1Name) && cols[3].equals("key")) {
        containsDb1Table1Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table1Name) && cols[3].equals("value")) {
        containsDb1Table1Value = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name) && cols[3].equals("key")) {
        containsDb1Table2Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table2Name) && cols[3].equals("value")) {
        containsDb1Table2Value = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name) && cols[3].equals("key")) {
        containsDb1Table3Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table3Name) && cols[3].equals("value")) {
        containsDb1Table3Value = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table4Name) && cols[3].equals("key")) {
        containsDb1Table4Key = true;
      } else if (cols[1].equals(db1Name) && cols[2].equals(table4Name) && cols[3].equals("value")) {
        containsDb1Table4Value = true;
      }
    }
    Assert.assertTrue(containsDb1Table1Key && containsDb1Table1Value && containsDb1Table2Key && containsDb1Table2Value
        && containsDb1Table3Key && containsDb1Table3Value && containsDb1Table4Key && containsDb1Table4Value);

    containsDb1Table1Key = false;
    containsDb1Table1Value = false;
    containsDb1Table2Key = false;
    containsDb1Table2Value = false;
    containsDb1Table3Key = false;
    containsDb1Table3Value = false;
    containsDb1Table4Key = false;
    containsDb1Table4Value = false;
    containsDb1Table4P = false;
    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 9);
    iter = rowSet.iterator();
    while (iter.hasNext()) {
      Object[] cols = iter.next();
      if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("key")) {
        containsDb1Table1Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table1Name) && cols[5].equals("value")) {
        containsDb1Table1Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("key")) {
        containsDb1Table2Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table2Name) && cols[5].equals("value")) {
        containsDb1Table2Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("key")) {
        containsDb1Table3Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table3Name) && cols[5].equals("value")) {
        containsDb1Table3Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table4Name) && cols[5].equals("key")) {
        containsDb1Table4Key = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table4Name) && cols[5].equals("value")) {
        containsDb1Table4Value = true;
      } else if (cols[3].equals(db1Name) && cols[4].equals(table4Name) && cols[5].equals("p")) {
        containsDb1Table4P = true;
      }
    }
    Assert.assertTrue(containsDb1Table1Key && containsDb1Table1Value && containsDb1Table2Key && containsDb1Table2Value
        && containsDb1Table3Key && containsDb1Table3Value && containsDb1Table4Key && containsDb1Table4Value
        && containsDb1Table4P);
    serviceClient.closeSession(sessHandle);

    // Revert hive.server2.restrict_information_schema to false
    miniHS2.getHiveConf().set(ConfVars.HIVE_AUTHORIZATION_ENABLED.varname, "false");

    sessHandle = serviceClient.openSession("user1", "");

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.SCHEMATA", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 5);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLES", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertTrue(rowSet.numRows() > 50);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertTrue(rowSet.numRows() > 200);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMNS", confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertTrue(rowSet.numRows() > 350);

    opHandle = serviceClient.executeStatement(sessHandle, "select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES",
        confOverlay);
    rowSet = serviceClient.fetchResults(opHandle);
    Assert.assertEquals(rowSet.numRows(), 12);
  }
Test restricted information schema with privilege synchronization
itests/hive-unit/src/test/java/org/apache/hive/service/server/InformationSchemaWithPrivilegeTestBase.java