sentry-tests/sentry-tests-hive-v2/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtDatabaseScope.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.hive;

 import org.apache.sentry.core.common.utils.PolicyFile;
 import static org.junit.Assert.assertFalse;
 import static org.junit.Assert.assertTrue;

 import java.io.File;
 import java.io.FileOutputStream;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.HashMap;
 import java.util.Map;

 import org.junit.Assert;

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

 import com.google.common.io.Resources;

 /* Tests privileges at table scope within a single database.
  */

 public class TestPrivilegesAtDatabaseScope extends AbstractTestWithStaticConfiguration {
   private PolicyFile policyFile;

   Map <String, String >testProperties;
   private static final String SINGLE_TYPE_DATA_FILE_NAME = "kv1.dat";

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

   @Override
   @Before
   public void setup() throws Exception {
     policyFile = super.setupPolicy();
     super.setup();
     testProperties = new HashMap<String, String>();
   }

   // SENTRY-285 test
   @Test
   public void testAllOnDb() throws Exception {
     // setup db objects needed by the test
     Connection connection = context.createConnection(ADMIN1);
     Statement statement = context.createStatement(connection);
     statement.execute("create database " + DB1);
     statement.execute("create table " + DB1 + ".tab1(a int)");

     policyFile
             .addRolesToGroup(USERGROUP1, "all_db1")
             .addPermissionsToRole("all_db1", "server=server1->db=" + DB1 + "->action=all")
             .setUserGroupMapping(StaticUserGroup.getStaticMapping());
     writePolicyFile(policyFile);

     connection = context.createConnection(USER1_1);
     statement = context.createStatement(connection);
     statement.execute("use " + DB1);
     statement.execute("select * from tab1");

     policyFile
         .addPermissionsToRole("all_db1", "server=server1->db=" + DB1);
     writePolicyFile(policyFile);
     statement.execute("use " + DB1);
     statement.execute("select * from tab1");
   }


   /* Admin creates database DB_1
    * Admin grants ALL to USER_GROUP of which user1 is a member.
    */
   @Test
   public void testAllPrivilege() throws Exception {

     //copy data file to test dir
     File dataDir = context.getDataDir();
     File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
     FileOutputStream to = new FileOutputStream(dataFile);
     Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
     to.close();

     // setup db objects needed by the test
     Connection connection = context.createConnection(ADMIN1);
     Statement statement = context.createStatement(connection);
     statement.execute("CREATE DATABASE " + DB1);
     statement.execute("CREATE DATABASE " + DB2);
     statement.close();
     connection.close();

     policyFile
             .addRolesToGroup(USERGROUP1, "all_db1", "load_data")
             .addRolesToGroup(USERGROUP2, "all_db2")
             .addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
             .addPermissionsToRole("all_db2", "server=server1->db=" + DB2)
             .addPermissionsToRole("load_data", "server=server1->uri=file://" + dataFile.getPath())
             .setUserGroupMapping(StaticUserGroup.getStaticMapping());
     writePolicyFile(policyFile);

     // test execution
     connection = context.createConnection(USER1_1);
     statement = context.createStatement(connection);
     // test user can create table
     statement.execute("CREATE TABLE " + DB1 + ".TAB_1(A STRING)");
     // test user can execute load
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE " + DB1 + ".TAB_1");
     statement.execute("CREATE TABLE " + DB1 + ".TAB_2(A STRING)");
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE " + DB1 + ".TAB_2");

     // test CTAS can reference UDFs
     statement.execute("USE " + DB1);
     statement.execute("create table table2 as select A, count(A) from TAB_1 GROUP BY A");

     // test user can switch db
     statement.execute("USE " + DB1);
     //test user can create view
     statement.execute("CREATE VIEW VIEW_1(A) AS SELECT A FROM TAB_1");

     // test user can insert
     statement.execute("INSERT INTO TABLE TAB_1 SELECT A FROM TAB_2");
     // test user can query table
     ResultSet resultSet = statement.executeQuery("SELECT COUNT(A) FROM TAB_1");
     int count = 0;
     int countRows = 0;

     while (resultSet.next()) {
       count = resultSet.getInt(1);
       countRows++;
     }
     assertTrue("Incorrect row count", countRows == 1);
     assertTrue("Incorrect result", count == 1000);

     // test user can execute alter table rename
     statement.execute("ALTER TABLE TAB_1 RENAME TO TAB_3");

     // test user can execute create as select
     statement.execute("CREATE TABLE TAB_4 AS SELECT * FROM TAB_2");

     // test user can execute alter table rename cols
     statement.execute("ALTER TABLE TAB_3 ADD COLUMNS (B INT)");

     // test user can drop table
     statement.execute("DROP TABLE TAB_3");

     //negative test case: user can't drop another user's database
     try {
       statement.execute("DROP DATABASE " + DB2 + " CASCADE");
       Assert.fail("Expected SQL exception");
     } catch (SQLException e) {
       context.verifyAuthzException(e);
     }

     //negative test case: user can't switch into another user's database
     try {
       statement.execute("USE " + DB2);
       Assert.fail("Expected SQL exception");
     } catch (SQLException e) {
       context.verifyAuthzException(e);
     }

     //User can drop own database
     statement.execute("DROP DATABASE " + DB1 + " CASCADE");

     statement.close();
     connection.close();
   }

   /* Admin creates database DB_1, creates table TAB_1, loads data into it
    * Admin grants ALL to USER_GROUP of which user1 is a member.
    */
   @Test
   public void testAllPrivilegeOnObjectOwnedByAdmin() throws Exception {

     //copy data file to test dir
     File dataDir = context.getDataDir();
     File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
     File externalTblDir = new File(dataDir, "exttab");
     FileOutputStream to = new FileOutputStream(dataFile);
     Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
     to.close();

     // setup db objects needed by the test
     Connection connection = context.createConnection(ADMIN1);
     Statement statement = context.createStatement(connection);
     statement.execute("CREATE DATABASE " + DB1);
     statement.execute("CREATE DATABASE " + DB2);
     statement.execute("USE " + DB1);
     statement.execute("CREATE TABLE TAB_1(A STRING)");
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_1");
     statement.execute("CREATE TABLE PART_TAB_1(A STRING) partitioned by (B INT) STORED AS TEXTFILE");
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE PART_TAB_1 PARTITION(B=1)");
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE PART_TAB_1 PARTITION(B=2)");
     statement.close();
     connection.close();

     policyFile
             .addRolesToGroup(USERGROUP1, "all_db1", "load_data", "exttab")
             .addRolesToGroup(USERGROUP2, "all_db2")
             .addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
             .addPermissionsToRole("all_db2", "server=server1->db=" + DB2)
             .addPermissionsToRole("exttab", "server=server1->uri=file://" + dataDir.getPath())
             .addPermissionsToRole("load_data", "server=server1->uri=file://" + dataFile.getPath())
             .setUserGroupMapping(StaticUserGroup.getStaticMapping());
     writePolicyFile(policyFile);

     // test execution
     connection = context.createConnection(USER1_1);
     statement = context.createStatement(connection);
     // test user can switch db
     statement.execute("USE " + DB1);
     // test user can execute load
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_1");
     statement.execute("CREATE TABLE TAB_2(A STRING)");
     statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_2");

     //test user can create view
     statement.execute("CREATE VIEW VIEW_1(A) AS SELECT A FROM TAB_1");

     // test user can insert
     statement.execute("INSERT INTO TABLE TAB_1 SELECT A FROM TAB_2");
     // test user can query table
     ResultSet resultSet = statement.executeQuery("SELECT COUNT(A) FROM TAB_1");
     int count = 0;
     int countRows = 0;

     while (resultSet.next()) {
       count = resultSet.getInt(1);
       countRows++;
     }
     assertTrue("Incorrect row count", countRows == 1);
     assertTrue("Incorrect result", count == 1500);

     // test user can execute alter table rename
     statement.execute("ALTER TABLE TAB_1 RENAME TO TAB_3");

     // test user can drop table
     statement.execute("DROP TABLE TAB_3");

     //positive test case: user can create external tables at given location
     assertTrue("Unable to create directory for external table test" , externalTblDir.mkdir());
     statement.execute("CREATE EXTERNAL TABLE EXT_TAB_1(A STRING) STORED AS TEXTFILE LOCATION 'file:"+
                         externalTblDir.getAbsolutePath() + "'");

     //negative test case: user can't execute alter table set location,
     // as the user does not have privileges on that location
     context.assertSentrySemanticException(statement, "ALTER TABLE TAB_2 SET LOCATION 'file:///tab2'", semanticException);

     statement.close();
     connection.close();

     connection = context.createConnection(USER2_1);
     statement = context.createStatement(connection);
     try {
       statement.execute("CREATE EXTERNAL TABLE EXT_TAB_1(A STRING) STORED AS TEXTFILE LOCATION 'file:"+
         externalTblDir.getAbsolutePath() + "'");
       Assert.fail("Expected SQL exception");
     } catch (SQLException e) {
       context.verifyAuthzException(e);
     }

     statement.close();
     connection.close();
   }

   /**
    * Test privileges for 'use <db>'
    * Admin should be able to run use <db> with server level access
    * User with db level access should be able to run use <db>
    * User with table level access should be able to run use <db>
    * User with no access to that db objects, should NOT be able run use <db>
    * @throws Exception
    */
   @Test
   public void testUseDbPrivilege() throws Exception {
     // setup db objects needed by the test
     Connection connection = context.createConnection(ADMIN1);
     Statement statement = context.createStatement(connection);
     statement.execute("CREATE DATABASE " + DB1);
     statement.execute("use " + DB1);
     statement.execute("CREATE TABLE TAB_1(A STRING)");
     statement.execute("CREATE DATABASE " + DB2);
     statement.execute("use " + DB2);
     statement.execute("CREATE TABLE TAB_2(A STRING)");
     context.close();

     policyFile
             .addRolesToGroup(USERGROUP1, "all_db1")
             .addRolesToGroup(USERGROUP2, "select_db2")
             .addRolesToGroup(USERGROUP3, "all_db3")
             .addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
             .addPermissionsToRole("select_db2", "server=server1->db=" + DB2 + "->table=tab_2->action=select")
             .addPermissionsToRole("all_db3", "server=server1->db=DB_3")
             .setUserGroupMapping(StaticUserGroup.getStaticMapping());
     writePolicyFile(policyFile);

     // user1 should be able to connect db_1
     connection = context.createConnection(USER1_1);
     statement = context.createStatement(connection);
     statement.execute("use " + DB1);
     context.close();

     // user2 should not be able to connect db_1
     connection = context.createConnection(USER2_1);
     statement = context.createStatement(connection);
     try {
       statement.execute("use " + DB1);
       assertFalse("user2 shouldn't be able switch to " + DB1, true);
     } catch (SQLException e) {
       context.verifyAuthzException(e);
     }
     statement.execute("use " + DB2);
     context.close();

     // user3 who is not listed in policy file should not be able to connect db_2
     connection = context.createConnection(USER3_1);
     statement = context.createStatement(connection);
     try {
       statement.execute("use " + DB2);
       assertFalse("user3 shouldn't be able switch to " + DB2, true);
     } catch (SQLException e) {
       context.verifyAuthzException(e);
     }
     context.close();
   }

   /**
    * Test access to default DB with out of box authz config
    * All users should be able to switch to default, including the users that don't have any
    * privilege on default db objects via policy file
    * @throws Exception
    */
   @Test
   public void testDefaultDbPrivilege() throws Exception {
     Connection connection = context.createConnection(ADMIN1);
     Statement statement = context.createStatement(connection);
     statement.execute("use default");
     statement.execute("create table tab1(a int)");
     statement.execute("CREATE DATABASE " + DB1);
     statement.execute("use " + DB1);
     statement.execute("CREATE TABLE TAB_1(A STRING)");
     statement.execute("CREATE DATABASE " + DB2);
     statement.execute("use " + DB2);
     statement.execute("CREATE TABLE TAB_2(A STRING)");
     context.close();

     policyFile
             .addRolesToGroup(USERGROUP1, "all_db1")
             .addRolesToGroup(USERGROUP2, "select_db2")
             .addRolesToGroup(USERGROUP3, "all_default")
             .addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
             .addPermissionsToRole("select_db2", "server=server1->db=" + DB2 + "->table=tab_2->action=select")
             .addPermissionsToRole("all_default", "server=server1->db=default")
             .setUserGroupMapping(StaticUserGroup.getStaticMapping());
     writePolicyFile(policyFile);

     connection = context.createConnection(USER1_1);
     statement = context.createStatement(connection);
     statement.execute("use default");
     try {
       statement.execute("select * from tab1");
       assertTrue("Should not be allowed !!", false);
     } catch (Exception e) {
       // Ignore
     }
     context.close();

     connection = context.createConnection(USER2_1);
     statement = context.createStatement(connection);
     statement.execute("use default");
     context.close();

     connection = context.createConnection(USER3_1);
     statement = context.createStatement(connection);
     statement.execute("use default");
     context.close();
   }

 }
	/*
	* 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.hive;

	import org.apache.sentry.core.common.utils.PolicyFile;
	import static org.junit.Assert.assertFalse;
	import static org.junit.Assert.assertTrue;

	import java.io.File;
	import java.io.FileOutputStream;
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.HashMap;
	import java.util.Map;

	import org.junit.Assert;

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

	import com.google.common.io.Resources;

	/* Tests privileges at table scope within a single database.
	*/

	public class TestPrivilegesAtDatabaseScope extends AbstractTestWithStaticConfiguration {
	private PolicyFile policyFile;

	Map <String, String >testProperties;
	private static final String SINGLE_TYPE_DATA_FILE_NAME = "kv1.dat";

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

	@Override
	@Before
	public void setup() throws Exception {
	policyFile = super.setupPolicy();
	super.setup();
	testProperties = new HashMap<String, String>();
	}

	// SENTRY-285 test
	@Test
	public void testAllOnDb() throws Exception {
	// setup db objects needed by the test
	Connection connection = context.createConnection(ADMIN1);
	Statement statement = context.createStatement(connection);
	statement.execute("create database " + DB1);
	statement.execute("create table " + DB1 + ".tab1(a int)");

	policyFile
	.addRolesToGroup(USERGROUP1, "all_db1")
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1 + "->action=all")
	.setUserGroupMapping(StaticUserGroup.getStaticMapping());
	writePolicyFile(policyFile);

	connection = context.createConnection(USER1_1);
	statement = context.createStatement(connection);
	statement.execute("use " + DB1);
	statement.execute("select * from tab1");

	policyFile
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1);
	writePolicyFile(policyFile);
	statement.execute("use " + DB1);
	statement.execute("select * from tab1");
	}


	/* Admin creates database DB_1
	* Admin grants ALL to USER_GROUP of which user1 is a member.
	*/
	@Test
	public void testAllPrivilege() throws Exception {

	//copy data file to test dir
	File dataDir = context.getDataDir();
	File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
	FileOutputStream to = new FileOutputStream(dataFile);
	Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
	to.close();

	// setup db objects needed by the test
	Connection connection = context.createConnection(ADMIN1);
	Statement statement = context.createStatement(connection);
	statement.execute("CREATE DATABASE " + DB1);
	statement.execute("CREATE DATABASE " + DB2);
	statement.close();
	connection.close();

	policyFile
	.addRolesToGroup(USERGROUP1, "all_db1", "load_data")
	.addRolesToGroup(USERGROUP2, "all_db2")
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
	.addPermissionsToRole("all_db2", "server=server1->db=" + DB2)
	.addPermissionsToRole("load_data", "server=server1->uri=file://" + dataFile.getPath())
	.setUserGroupMapping(StaticUserGroup.getStaticMapping());
	writePolicyFile(policyFile);

	// test execution
	connection = context.createConnection(USER1_1);
	statement = context.createStatement(connection);
	// test user can create table
	statement.execute("CREATE TABLE " + DB1 + ".TAB_1(A STRING)");
	// test user can execute load
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE " + DB1 + ".TAB_1");
	statement.execute("CREATE TABLE " + DB1 + ".TAB_2(A STRING)");
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE " + DB1 + ".TAB_2");

	// test CTAS can reference UDFs
	statement.execute("USE " + DB1);
	statement.execute("create table table2 as select A, count(A) from TAB_1 GROUP BY A");

	// test user can switch db
	statement.execute("USE " + DB1);
	//test user can create view
	statement.execute("CREATE VIEW VIEW_1(A) AS SELECT A FROM TAB_1");

	// test user can insert
	statement.execute("INSERT INTO TABLE TAB_1 SELECT A FROM TAB_2");
	// test user can query table
	ResultSet resultSet = statement.executeQuery("SELECT COUNT(A) FROM TAB_1");
	int count = 0;
	int countRows = 0;

	while (resultSet.next()) {
	count = resultSet.getInt(1);
	countRows++;
	}
	assertTrue("Incorrect row count", countRows == 1);
	assertTrue("Incorrect result", count == 1000);

	// test user can execute alter table rename
	statement.execute("ALTER TABLE TAB_1 RENAME TO TAB_3");

	// test user can execute create as select
	statement.execute("CREATE TABLE TAB_4 AS SELECT * FROM TAB_2");

	// test user can execute alter table rename cols
	statement.execute("ALTER TABLE TAB_3 ADD COLUMNS (B INT)");

	// test user can drop table
	statement.execute("DROP TABLE TAB_3");

	//negative test case: user can't drop another user's database
	try {
	statement.execute("DROP DATABASE " + DB2 + " CASCADE");
	Assert.fail("Expected SQL exception");
	} catch (SQLException e) {
	context.verifyAuthzException(e);
	}

	//negative test case: user can't switch into another user's database
	try {
	statement.execute("USE " + DB2);
	Assert.fail("Expected SQL exception");
	} catch (SQLException e) {
	context.verifyAuthzException(e);
	}

	//User can drop own database
	statement.execute("DROP DATABASE " + DB1 + " CASCADE");

	statement.close();
	connection.close();
	}

	/* Admin creates database DB_1, creates table TAB_1, loads data into it
	* Admin grants ALL to USER_GROUP of which user1 is a member.
	*/
	@Test
	public void testAllPrivilegeOnObjectOwnedByAdmin() throws Exception {

	//copy data file to test dir
	File dataDir = context.getDataDir();
	File dataFile = new File(dataDir, SINGLE_TYPE_DATA_FILE_NAME);
	File externalTblDir = new File(dataDir, "exttab");
	FileOutputStream to = new FileOutputStream(dataFile);
	Resources.copy(Resources.getResource(SINGLE_TYPE_DATA_FILE_NAME), to);
	to.close();

	// setup db objects needed by the test
	Connection connection = context.createConnection(ADMIN1);
	Statement statement = context.createStatement(connection);
	statement.execute("CREATE DATABASE " + DB1);
	statement.execute("CREATE DATABASE " + DB2);
	statement.execute("USE " + DB1);
	statement.execute("CREATE TABLE TAB_1(A STRING)");
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_1");
	statement.execute("CREATE TABLE PART_TAB_1(A STRING) partitioned by (B INT) STORED AS TEXTFILE");
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE PART_TAB_1 PARTITION(B=1)");
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE PART_TAB_1 PARTITION(B=2)");
	statement.close();
	connection.close();

	policyFile
	.addRolesToGroup(USERGROUP1, "all_db1", "load_data", "exttab")
	.addRolesToGroup(USERGROUP2, "all_db2")
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
	.addPermissionsToRole("all_db2", "server=server1->db=" + DB2)
	.addPermissionsToRole("exttab", "server=server1->uri=file://" + dataDir.getPath())
	.addPermissionsToRole("load_data", "server=server1->uri=file://" + dataFile.getPath())
	.setUserGroupMapping(StaticUserGroup.getStaticMapping());
	writePolicyFile(policyFile);

	// test execution
	connection = context.createConnection(USER1_1);
	statement = context.createStatement(connection);
	// test user can switch db
	statement.execute("USE " + DB1);
	// test user can execute load
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_1");
	statement.execute("CREATE TABLE TAB_2(A STRING)");
	statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_2");

	//test user can create view
	statement.execute("CREATE VIEW VIEW_1(A) AS SELECT A FROM TAB_1");

	// test user can insert
	statement.execute("INSERT INTO TABLE TAB_1 SELECT A FROM TAB_2");
	// test user can query table
	ResultSet resultSet = statement.executeQuery("SELECT COUNT(A) FROM TAB_1");
	int count = 0;
	int countRows = 0;

	while (resultSet.next()) {
	count = resultSet.getInt(1);
	countRows++;
	}
	assertTrue("Incorrect row count", countRows == 1);
	assertTrue("Incorrect result", count == 1500);

	// test user can execute alter table rename
	statement.execute("ALTER TABLE TAB_1 RENAME TO TAB_3");

	// test user can drop table
	statement.execute("DROP TABLE TAB_3");

	//positive test case: user can create external tables at given location
	assertTrue("Unable to create directory for external table test" , externalTblDir.mkdir());
	statement.execute("CREATE EXTERNAL TABLE EXT_TAB_1(A STRING) STORED AS TEXTFILE LOCATION 'file:"+
	externalTblDir.getAbsolutePath() + "'");

	//negative test case: user can't execute alter table set location,
	// as the user does not have privileges on that location
	context.assertSentrySemanticException(statement, "ALTER TABLE TAB_2 SET LOCATION 'file:///tab2'", semanticException);

	statement.close();
	connection.close();

	connection = context.createConnection(USER2_1);
	statement = context.createStatement(connection);
	try {
	statement.execute("CREATE EXTERNAL TABLE EXT_TAB_1(A STRING) STORED AS TEXTFILE LOCATION 'file:"+
	externalTblDir.getAbsolutePath() + "'");
	Assert.fail("Expected SQL exception");
	} catch (SQLException e) {
	context.verifyAuthzException(e);
	}

	statement.close();
	connection.close();
	}

	/**
	* Test privileges for 'use <db>'
	* Admin should be able to run use <db> with server level access
	* User with db level access should be able to run use <db>
	* User with table level access should be able to run use <db>
	* User with no access to that db objects, should NOT be able run use <db>
	* @throws Exception
	*/
	@Test
	public void testUseDbPrivilege() throws Exception {
	// setup db objects needed by the test
	Connection connection = context.createConnection(ADMIN1);
	Statement statement = context.createStatement(connection);
	statement.execute("CREATE DATABASE " + DB1);
	statement.execute("use " + DB1);
	statement.execute("CREATE TABLE TAB_1(A STRING)");
	statement.execute("CREATE DATABASE " + DB2);
	statement.execute("use " + DB2);
	statement.execute("CREATE TABLE TAB_2(A STRING)");
	context.close();

	policyFile
	.addRolesToGroup(USERGROUP1, "all_db1")
	.addRolesToGroup(USERGROUP2, "select_db2")
	.addRolesToGroup(USERGROUP3, "all_db3")
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
	.addPermissionsToRole("select_db2", "server=server1->db=" + DB2 + "->table=tab_2->action=select")
	.addPermissionsToRole("all_db3", "server=server1->db=DB_3")
	.setUserGroupMapping(StaticUserGroup.getStaticMapping());
	writePolicyFile(policyFile);

	// user1 should be able to connect db_1
	connection = context.createConnection(USER1_1);
	statement = context.createStatement(connection);
	statement.execute("use " + DB1);
	context.close();

	// user2 should not be able to connect db_1
	connection = context.createConnection(USER2_1);
	statement = context.createStatement(connection);
	try {
	statement.execute("use " + DB1);
	assertFalse("user2 shouldn't be able switch to " + DB1, true);
	} catch (SQLException e) {
	context.verifyAuthzException(e);
	}
	statement.execute("use " + DB2);
	context.close();

	// user3 who is not listed in policy file should not be able to connect db_2
	connection = context.createConnection(USER3_1);
	statement = context.createStatement(connection);
	try {
	statement.execute("use " + DB2);
	assertFalse("user3 shouldn't be able switch to " + DB2, true);
	} catch (SQLException e) {
	context.verifyAuthzException(e);
	}
	context.close();
	}

	/**
	* Test access to default DB with out of box authz config
	* All users should be able to switch to default, including the users that don't have any
	* privilege on default db objects via policy file
	* @throws Exception
	*/
	@Test
	public void testDefaultDbPrivilege() throws Exception {
	Connection connection = context.createConnection(ADMIN1);
	Statement statement = context.createStatement(connection);
	statement.execute("use default");
	statement.execute("create table tab1(a int)");
	statement.execute("CREATE DATABASE " + DB1);
	statement.execute("use " + DB1);
	statement.execute("CREATE TABLE TAB_1(A STRING)");
	statement.execute("CREATE DATABASE " + DB2);
	statement.execute("use " + DB2);
	statement.execute("CREATE TABLE TAB_2(A STRING)");
	context.close();

	policyFile
	.addRolesToGroup(USERGROUP1, "all_db1")
	.addRolesToGroup(USERGROUP2, "select_db2")
	.addRolesToGroup(USERGROUP3, "all_default")
	.addPermissionsToRole("all_db1", "server=server1->db=" + DB1)
	.addPermissionsToRole("select_db2", "server=server1->db=" + DB2 + "->table=tab_2->action=select")
	.addPermissionsToRole("all_default", "server=server1->db=default")
	.setUserGroupMapping(StaticUserGroup.getStaticMapping());
	writePolicyFile(policyFile);

	connection = context.createConnection(USER1_1);
	statement = context.createStatement(connection);
	statement.execute("use default");
	try {
	statement.execute("select * from tab1");
	assertTrue("Should not be allowed !!", false);
	} catch (Exception e) {
	// Ignore
	}
	context.close();

	connection = context.createConnection(USER2_1);
	statement = context.createStatement(connection);
	statement.execute("use default");
	context.close();

	connection = context.createConnection(USER3_1);
	statement = context.createStatement(connection);
	statement.execute("use default");
	context.close();
	}

	}