Repository: incubator-sentry Updated Branches: refs/heads/master 4d6f63876 -> f41803561
SENTRY-625: Improve test cases in TestPrivilegesAtColumnScope (Reviewed by Xiaomeng) Project: http://git-wip-us.apache.org/repos/asf/incubator-sentry/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-sentry/commit/f4180356 Tree: http://git-wip-us.apache.org/repos/asf/incubator-sentry/tree/f4180356 Diff: http://git-wip-us.apache.org/repos/asf/incubator-sentry/diff/f4180356 Branch: refs/heads/master Commit: f418035616c3afc659caf0cdd243527e30ef9095 Parents: 4d6f638 Author: Colin Ma <[email protected]> Authored: Wed Feb 4 09:11:49 2015 +0800 Committer: Colin Ma <[email protected]> Committed: Wed Feb 4 09:11:49 2015 +0800 ---------------------------------------------------------------------- .../TestDbPrivilegesAtColumnScope.java | 7 +- .../AbstractTestWithStaticConfiguration.java | 47 +- .../e2e/hive/TestPrivilegesAtColumnScope.java | 693 +++---------------- 3 files changed, 110 insertions(+), 637 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/f4180356/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbPrivilegesAtColumnScope.java ---------------------------------------------------------------------- diff --git a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbPrivilegesAtColumnScope.java b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbPrivilegesAtColumnScope.java index a4de2c0..659c61f 100644 --- a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbPrivilegesAtColumnScope.java +++ b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/dbprovider/TestDbPrivilegesAtColumnScope.java @@ -17,7 +17,6 @@ package org.apache.sentry.tests.e2e.dbprovider; -import org.apache.sentry.tests.e2e.hive.AbstractTestWithStaticConfiguration; import org.apache.sentry.tests.e2e.hive.TestPrivilegesAtColumnScope; import org.junit.Before; import org.junit.BeforeClass; @@ -26,14 +25,14 @@ public class TestDbPrivilegesAtColumnScope extends TestPrivilegesAtColumnScope { @Override @Before public void setup() throws Exception { - super.setupAdmin(); + setupAdmin(); super.setup(); } + @BeforeClass public static void setupTestStaticConfiguration() throws Exception { useSentryService = true; - AbstractTestWithStaticConfiguration.setupTestStaticConfiguration(); - + TestPrivilegesAtColumnScope.setupTestStaticConfiguration(); } } http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/f4180356/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/AbstractTestWithStaticConfiguration.java ---------------------------------------------------------------------- diff --git a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/AbstractTestWithStaticConfiguration.java b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/AbstractTestWithStaticConfiguration.java index 689f5a6..d08b4ee 100644 --- a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/AbstractTestWithStaticConfiguration.java +++ b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/AbstractTestWithStaticConfiguration.java @@ -114,6 +114,8 @@ public abstract class AbstractTestWithStaticConfiguration { protected static boolean setMetastoreListener = false; protected static String testServerType = null; protected static boolean enableHiveConcurrency = false; + // indicate if the database need to be clear for every test case in one test class + protected static boolean clearDbAfterPerTest = true; protected static File baseDir; protected static File logDir; @@ -252,8 +254,7 @@ public abstract class AbstractTestWithStaticConfiguration { baseDir, confDir, logDir, policyFile, fileSystem); } - protected void writePolicyFile(PolicyFile policyFile) throws Exception{ - + protected void writePolicyFile(PolicyFile policyFile) throws Exception { policyFile.write(context.getPolicyFile()); if(policyOnHdfs) { dfs.writePolicyFile(context.getPolicyFile()); @@ -262,7 +263,7 @@ public abstract class AbstractTestWithStaticConfiguration { } } - private void grantPermissions(PolicyFile policyFile) throws Exception{ + private void grantPermissions(PolicyFile policyFile) throws Exception { Connection connection = context.createConnection(ADMIN1); Statement statement = context.createStatement(connection); @@ -295,6 +296,7 @@ public abstract class AbstractTestWithStaticConfiguration { } } } + private void addPrivilege(String roleName, String privileges, Statement statement) throws IOException, SQLException{ String serverName = null, dbName = null, tableName = null, uriPath = null, columnName = null; @@ -415,31 +417,31 @@ public abstract class AbstractTestWithStaticConfiguration { @After public void clearDB() throws Exception { - Connection connection; - Statement statement; - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - - String [] dbs = { DB1, DB2, DB3}; - for (String db: dbs) { - statement.execute("DROP DATABASE if exists " + db + " CASCADE"); - } ResultSet resultSet; - statement.execute("USE default"); - resultSet = statement.executeQuery("SHOW tables"); - while(resultSet.next()) { - Statement statement2 = context.createStatement(connection); - statement2.execute("DROP table " + resultSet.getString(1)); - statement2.close(); + Connection connection = context.createConnection(ADMIN1); + Statement statement = context.createStatement(connection); + + if (clearDbAfterPerTest) { + String[] dbs = { DB1, DB2, DB3 }; + for (String db : dbs) { + statement.execute("DROP DATABASE if exists " + db + " CASCADE"); + } + statement.execute("USE default"); + resultSet = statement.executeQuery("SHOW tables"); + while (resultSet.next()) { + Statement statement2 = context.createStatement(connection); + statement2.execute("DROP table " + resultSet.getString(1)); + statement2.close(); + } } if(useSentryService) { resultSet = statement.executeQuery("SHOW roles"); List<String> roles = new ArrayList<String>(); - while ( resultSet.next()) { + while (resultSet.next()) { roles.add(resultSet.getString(1)); } - for(String role:roles) { + for (String role : roles) { statement.execute("DROP Role " + role); } } @@ -448,10 +450,9 @@ public abstract class AbstractTestWithStaticConfiguration { } - protected void setupAdmin() throws Exception { - + protected static void setupAdmin() throws Exception { if(useSentryService) { - Connection connection = context.createConnection(ADMIN1); + Connection connection = context.createConnection(ADMIN1); Statement statement = connection.createStatement(); try { statement.execute("CREATE ROLE admin_role"); http://git-wip-us.apache.org/repos/asf/incubator-sentry/blob/f4180356/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtColumnScope.java ---------------------------------------------------------------------- diff --git a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtColumnScope.java b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtColumnScope.java index 4e43046..1ccab46 100644 --- a/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtColumnScope.java +++ b/sentry-tests/sentry-tests-hive/src/test/java/org/apache/sentry/tests/e2e/hive/TestPrivilegesAtColumnScope.java @@ -17,36 +17,75 @@ package org.apache.sentry.tests.e2e.hive; -import org.apache.sentry.provider.file.PolicyFile; -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 junit.framework.Assert; +import org.apache.sentry.provider.file.PolicyFile; 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. +/* Tests privileges at column scope within a single database. */ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfiguration { - private PolicyFile policyFile; - - private final String SINGLE_TYPE_DATA_FILE_NAME = "kv1.dat"; - private final String MULTI_TYPE_DATA_FILE_NAME = "emp.dat"; + private static PolicyFile policyFile; + private final static String MULTI_TYPE_DATA_FILE_NAME = "emp.dat"; + private static boolean isDBDataPrepared = false; @Before public void setup() throws Exception { - policyFile = PolicyFile.setAdminOnServer1(ADMINGROUP); + if (!isDBDataPrepared) { + prepareDBDataForTest(); + isDBDataPrepared = true; + } + if (useSentryService) { + policyFile = new PolicyFile(); + } else { + policyFile = PolicyFile.setAdminOnServer1(ADMINGROUP); + } + } + + @BeforeClass + public static void setupTestStaticConfiguration() throws Exception { + clearDbAfterPerTest = false; + AbstractTestWithStaticConfiguration.setupTestStaticConfiguration(); + } + + private void prepareDBDataForTest() throws Exception { + policyFile = PolicyFile.setAdminOnServer1(ADMINGROUP).setUserGroupMapping( + StaticUserGroup.getStaticMapping()); + writePolicyFile(policyFile); + // copy data file to test dir + File dataDir = context.getDataDir(); + File dataFile = new File(dataDir, MULTI_TYPE_DATA_FILE_NAME); + FileOutputStream to = new FileOutputStream(dataFile); + Resources.copy(Resources.getResource(MULTI_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("DROP DATABASE IF EXISTS DB_1 CASCADE"); + statement.execute("CREATE DATABASE DB_1"); + statement.execute("USE DB_1"); + statement.execute("CREATE TABLE TAB_1(A STRING, B STRING)"); + statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_1"); + statement.execute("CREATE VIEW VIEW_1(A,B) AS SELECT A,B FROM TAB_1"); + statement.execute("CREATE TABLE TAB_2(A STRING, B STRING)"); + statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() + "' INTO TABLE TAB_2"); + statement.execute("CREATE VIEW VIEW_2(A,B) AS SELECT A,B FROM TAB_2"); + statement.close(); + connection.close(); } /* @@ -59,13 +98,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura */ @Test public void testSelectColumnOnTable() 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(); - policyFile .addRolesToGroup(USERGROUP1, "select_tab1_A", "select_tab2_A") .addRolesToGroup(USERGROUP2, "select_tab1_A", "select_tab1_B", "select_tab2_A", "select_tab2_B") @@ -76,48 +108,16 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura .setUserGroupMapping(StaticUserGroup.getStaticMapping()); writePolicyFile(policyFile); - // setup db objects needed by the test - Connection connection = context.createConnection(ADMIN1); - Statement statement = context.createStatement(connection); - - statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE"); - statement.execute("CREATE DATABASE DB_1"); - statement.execute("USE DB_1"); - statement.execute("CREATE TABLE TAB_1(A STRING, B STRING)"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_1"); - statement.execute("CREATE VIEW VIEW_1(A) AS SELECT A FROM TAB_1"); - statement.execute("CREATE TABLE TAB_2(A STRING, B STRING)"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_2"); - statement.close(); - connection.close(); - // test execution on user1 - connection = context.createConnection(USER1_1); - statement = context.createStatement(connection); + Connection connection = context.createConnection(USER1_1); + Statement statement = context.createStatement(connection); statement.execute("USE DB_1"); // test user can execute query count on column A on tab_1 - 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 == 500); + statement.executeQuery("SELECT COUNT(A) FROM TAB_1"); // test user can execute query column A on tab_1 - resultSet = statement.executeQuery("SELECT A FROM TAB_1"); - countRows = 0; - - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 500); + statement.executeQuery("SELECT A FROM TAB_1"); // negative test: test user can't execute query count of column B on tab_1 try { @@ -150,23 +150,10 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura statement = context.createStatement(connection); statement.execute("USE DB_1"); // test user can execute query count of column A on tab_1 - resultSet = statement.executeQuery("SELECT COUNT(A) FROM TAB_1"); - count = 0; - countRows = 0; + statement.executeQuery("SELECT COUNT(A) FROM TAB_1"); - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } // test user can execute query count of column B on tab_1 - resultSet = statement.executeQuery("SELECT COUNT(B) FROM TAB_1"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } + statement.executeQuery("SELECT COUNT(B) FROM TAB_1"); // test user can't execute query count using * on tab_1 try { @@ -177,23 +164,10 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura } // test user can execute SELECT * on tab_1 - resultSet = statement.executeQuery("SELECT * FROM TAB_1"); - countRows = 0; - - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 500); + statement.executeQuery("SELECT * FROM TAB_1"); statement.close(); connection.close(); - - // test cleanup - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - statement.execute("DROP DATABASE DB_1 CASCADE"); - statement.close(); - connection.close(); } /* @@ -207,13 +181,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura */ @Test public void testSelectColumnOnView() 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(); - policyFile .addRolesToGroup(USERGROUP1, "select_view1_A", "select_view2_A") .addRolesToGroup(USERGROUP2, "select_view1_A", "select_view1_B", "select_view2_A", "select_view2_B") @@ -224,27 +191,9 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura .setUserGroupMapping(StaticUserGroup.getStaticMapping()); writePolicyFile(policyFile); - // setup db objects needed by the test - Connection connection = context.createConnection(ADMIN1); - Statement statement = context.createStatement(connection); - - statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE"); - statement.execute("CREATE DATABASE DB_1"); - statement.execute("USE DB_1"); - statement.execute("CREATE TABLE TAB_1(A STRING, B STRING)"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_1"); - statement.execute("CREATE VIEW VIEW_1(A,B) AS SELECT A,B FROM TAB_1"); - statement.execute("CREATE TABLE TAB_2(A STRING, B STRING)"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_2"); - statement.execute("CREATE VIEW VIEW_2(A,B) AS SELECT A,B FROM TAB_2"); - statement.close(); - connection.close(); - // test execution on user1 - connection = context.createConnection(USER1_1); - statement = context.createStatement(connection); + Connection connection = context.createConnection(USER1_1); + Statement statement = context.createStatement(connection); statement.execute("USE DB_1"); // negative test: test user can't execute query count of column B on tab_1 try { @@ -334,330 +283,62 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura } statement.close(); connection.close(); - - // test cleanup - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - statement.execute("DROP DATABASE DB_1 CASCADE"); - statement.close(); - connection.close(); } /* * Admin creates database DB_1, table TAB_1, TAB_2 in DB_1, VIEW_1 on TAB_1 * loads data into TAB_1, TAB_2. Admin grants SELECT on TAB_1,TAB_2 to - * USER_GROUPS. + * USER_GROUPS. All test cases in this method will do the authorization on the condition of join + * or where clause */ @Test public void testSelectColumnOnTableJoin() throws Exception { - // copy data file to test dir - File dataDir = context.getDataDir(); - File dataFile = new File(dataDir, MULTI_TYPE_DATA_FILE_NAME); - FileOutputStream to = new FileOutputStream(dataFile); - Resources.copy(Resources.getResource(MULTI_TYPE_DATA_FILE_NAME), to); - to.close(); - policyFile .addRolesToGroup(USERGROUP1, "select_tab1_A", "select_tab1_B", "select_tab2_B") - .addRolesToGroup(USERGROUP2, "select_tab1_B", "select_tab2_B") - .addRolesToGroup(USERGROUP3, "select_tab1_B", "select_tab2_A") - .addRolesToGroup(USERGROUP4, "select_tab1_A", "select_tab1_B", "select_tab2_A", "select_tab2_B") .addPermissionsToRole("select_tab1_A", "server=server1->db=DB_1->table=TAB_1->column=A->action=select") .addPermissionsToRole("select_tab1_B", "server=server1->db=DB_1->table=TAB_1->column=B->action=select") - .addPermissionsToRole("select_tab2_A", "server=server1->db=DB_1->table=TAB_2->column=A->action=select") .addPermissionsToRole("select_tab2_B", "server=server1->db=DB_1->table=TAB_2->column=B->action=select") .setUserGroupMapping(StaticUserGroup.getStaticMapping()); writePolicyFile(policyFile); - // setup db objects needed by the test - Connection connection = context.createConnection(ADMIN1); - Statement statement = context.createStatement(connection); - - statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE"); - statement.execute("CREATE DATABASE DB_1"); - statement.execute("USE DB_1"); - statement.execute("CREATE TABLE TAB_1(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_1"); - statement.execute("CREATE VIEW VIEW_1 AS SELECT A, B FROM TAB_1"); - statement.execute("CREATE TABLE TAB_2(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_2"); - statement.execute("CREATE VIEW VIEW_2 AS SELECT A, B FROM TAB_2"); - statement.close(); - connection.close(); - // test execution user1 - connection = context.createConnection(USER1_1); - statement = context.createStatement(connection); + Connection connection = context.createConnection(USER1_1); + Statement statement = context.createStatement(connection); statement.execute("USE DB_1"); - // test user can execute query TAB_1 JOIN TAB_2 - ResultSet resultSet = statement.executeQuery("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - int count = 0; - int countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(*) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(*) FROM TAB_2 T2 JOIN TAB_1 T1 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT T1.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT * FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select count * - try { - statement.execute("SELECT count(*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - //Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT T1.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * on TAB_1 - try { - statement.execute("SELECT count(T1.*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * on TAB_2 - try { - statement.execute("SELECT T2.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT count(T2.*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT * FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT T2.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } + // test user can execute query TAB_1 JOIN TAB_2, do the column authorization on the condition of + // join clause + statement + .executeQuery("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON T1.B = T2.B AND T1.A = '21' "); - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT T2.A FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query VIEW_1 JOIN TAB_2 - try { - statement.executeQuery("SELECT COUNT(*) FROM VIEW_1 V1 JOIN TAB_2 T2 ON (V1.B = T2.B)"); - Assert.fail("Expected SQL exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // test execution on user2 - connection = context.createConnection(USER2_1); - statement = context.createStatement(connection); - statement.execute("USE DB_1"); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(*) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT T2.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * - try { - statement.execute("SELECT * FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * on TAB_1 - try { - statement.execute("SELECT T1.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select column A on TAB_1 - try { - statement.execute("SELECT T1.A FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query VIEW_1 JOIN TAB_2 + // negative test: test user can't execute query if do the column authorization on the condition + // of join clause failed try { statement - .executeQuery("SELECT COUNT(*) FROM VIEW_1 V1 JOIN TAB_2 T2 ON (V1.B = T2.B)"); - Assert.fail("Expected SQL exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // test execution on user3 - connection = context.createConnection(USER3_1); - statement = context.createStatement(connection); - statement.execute("USE DB_1"); - - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(T2.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); + .execute("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON T1.B = T2.B AND T1.A = '21' AND T2.A = '21'"); Assert.fail("Expected SQL Exception"); } catch (SQLException e) { context.verifyAuthzException(e); } - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(T1.B) FROM TAB_2 T2 JOIN TAB_1 T1 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(T2.B) FROM TAB_2 T2 JOIN TAB_1 T1 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(*) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can't execute query TAB_1 JOIN TAB_2 - try { - statement.execute("SELECT T2.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } + // test user can execute query TAB_1 JOIN TAB_2, do the column authorization on the condition of + // where clause + statement + .executeQuery("SELECT T1.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B AND T1.A = '21'"); - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * + // negative test: test user can't execute query if do the column authorization on the condition + // of where clause failed try { - statement.execute("SELECT * FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * on TAB_1 - try { - statement.execute("SELECT T1.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); + statement + .execute("SELECT T1.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B AND T1.A = '21' AND T2.A = '21'"); Assert.fail("Expected SQL Exception"); } catch (SQLException e) { context.verifyAuthzException(e); } - // negative test: test user can execute query TAB_1 JOIN TAB_2 use select * try { - statement.execute("SELECT T1.A FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); + statement + .execute("SELECT T1.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B AND T1.A = '21' AND T2.A = '21'"); Assert.fail("Expected SQL Exception"); } catch (SQLException e) { context.verifyAuthzException(e); @@ -671,145 +352,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura context.verifyAuthzException(e); } - // test execution on user4 - connection = context.createConnection(USER4_1); - statement = context.createStatement(connection); - statement.execute("USE DB_1"); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(T1.B) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT COUNT(*) FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_2 JOIN TAB_1 - resultSet = statement.executeQuery("SELECT COUNT(*) FROM TAB_2 T2 JOIN TAB_1 T1 ON (T1.B = T2.B)"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - // test user can execute query TAB_2 JOIN TAB_1 - resultSet = statement.executeQuery("SELECT T1.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // test user can execute query TAB_2 JOIN TAB_1 - resultSet = statement.executeQuery("SELECT * FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // test user can execute query TAB_2 JOIN TAB_1 - resultSet = statement.executeQuery("SELECT count(*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - count = 0; - countRows = 0; - - while (resultSet.next()) { - count = resultSet.getInt(1); - countRows++; - } - assertTrue("Incorrect row count", countRows == 1); - assertTrue("Incorrect result", count == 12); - - try { - statement.execute("SELECT count(T1.*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // test user can execute query TAB_2 JOIN TAB_1 - resultSet = statement.executeQuery("SELECT T2.* FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - countRows = 0; - - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - try { - statement.execute("SELECT count(T2.*) FROM TAB_1 T1, TAB_2 T2 WHERE T1.B = T2.B"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - // test user can execute query TAB_1 JOIN TAB_2 use select * - resultSet = statement.executeQuery("SELECT * FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // test user can execute query TAB_1 JOIN TAB_2 - resultSet = statement.executeQuery("SELECT T1.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // test user can execute query TAB_1 JOIN TAB_2 use select * - resultSet = statement.executeQuery("SELECT T2.* FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // test user can execute query TAB_1 JOIN TAB_2 use select * - resultSet = statement.executeQuery("SELECT T2.A FROM TAB_1 T1 JOIN TAB_2 T2 ON (T1.B = T2.B)"); - countRows = 0; - while (resultSet.next()) { - countRows++; - } - assertTrue("Incorrect row count", countRows == 12); - - // negative test: test user can't execute query VIEW_1 JOIN TAB_2 - try { - statement.execute("SELECT COUNT(*) FROM VIEW_1 V1 JOIN TAB_2 T2 ON (V1.B = T2.B)"); - Assert.fail("Expected SQL Exception"); - } catch (SQLException e) { - context.verifyAuthzException(e); - } - - statement.close(); - connection.close(); - - // test cleanup - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - statement.execute("DROP DATABASE DB_1 CASCADE"); statement.close(); connection.close(); } @@ -825,13 +367,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura */ @Test public void testSelectColumnOnViewJoin() throws Exception { - File dataDir = context.getDataDir(); - // copy data file to test dir - File dataFile = new File(dataDir, MULTI_TYPE_DATA_FILE_NAME); - FileOutputStream to = new FileOutputStream(dataFile); - Resources.copy(Resources.getResource(MULTI_TYPE_DATA_FILE_NAME), to); - to.close(); - policyFile .addRolesToGroup(USERGROUP1, "select_view1_A", "select_view1_B", "select_view2_B") .addRolesToGroup(USERGROUP2, "select_view1_B", "select_view2_B") @@ -843,32 +378,11 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura .setUserGroupMapping(StaticUserGroup.getStaticMapping()); writePolicyFile(policyFile); - // setup db objects needed by the test - Connection connection = context.createConnection(ADMIN1); - Statement statement = context.createStatement(connection); - - statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE"); - statement.execute("CREATE DATABASE DB_1"); - statement.execute("USE DB_1"); - statement.execute("CREATE TABLE TAB_1(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_1"); - statement.execute("CREATE VIEW VIEW_1 AS SELECT A, B FROM TAB_1"); - statement.execute("CREATE TABLE TAB_2(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_2"); - statement.execute("CREATE VIEW VIEW_2 AS SELECT A, B FROM TAB_2"); - statement.close(); - connection.close(); - // test execution - connection = context.createConnection(USER1_1); - statement = context.createStatement(connection); + Connection connection = context.createConnection(USER1_1); + Statement statement = context.createStatement(connection); statement.execute("USE DB_1"); - // test user can't execute query VIEW_1 JOIN VIEW_2 try { statement.execute("SELECT COUNT(*) FROM VIEW_1 V1 JOIN VIEW_2 V2 ON (V1.B = V2.B)"); @@ -895,13 +409,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura statement.close(); connection.close(); - - // test cleanup - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - statement.execute("DROP DATABASE DB_1 CASCADE"); - statement.close(); - connection.close(); } /* @@ -915,13 +422,6 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura */ @Test public void testSelectColumnOnTableViewJoin() throws Exception { - File dataDir = context.getDataDir(); - // copy data file to test dir - File dataFile = new File(dataDir, MULTI_TYPE_DATA_FILE_NAME); - FileOutputStream to = new FileOutputStream(dataFile); - Resources.copy(Resources.getResource(MULTI_TYPE_DATA_FILE_NAME), to); - to.close(); - policyFile .addRolesToGroup(USERGROUP1, "select_tab1_A", "select_tab1_B", "select_view2_B") .addPermissionsToRole("select_tab1_A", "server=server1->db=DB_1->table=VIEW_1->column=A->action=select") @@ -930,29 +430,9 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura .setUserGroupMapping(StaticUserGroup.getStaticMapping()); writePolicyFile(policyFile); - // setup db objects needed by the test - Connection connection = context.createConnection(ADMIN1); - Statement statement = context.createStatement(connection); - - statement.execute("DROP DATABASE IF EXISTS DB_1 CASCADE"); - statement.execute("CREATE DATABASE DB_1"); - statement.execute("USE DB_1"); - statement.execute("CREATE TABLE TAB_1(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_1"); - statement.execute("CREATE VIEW VIEW_1 AS SELECT A, B FROM TAB_1"); - statement.execute("CREATE TABLE TAB_2(B INT, A STRING) " - + " row format delimited fields terminated by '|' stored as textfile"); - statement.execute("LOAD DATA LOCAL INPATH '" + dataFile.getPath() - + "' INTO TABLE TAB_2"); - statement.execute("CREATE VIEW VIEW_2 AS SELECT A, B FROM TAB_2"); - statement.close(); - connection.close(); - // test execution - connection = context.createConnection(USER1_1); - statement = context.createStatement(connection); + Connection connection = context.createConnection(USER1_1); + Statement statement = context.createStatement(connection); statement.execute("USE DB_1"); // test user can't execute query VIEW_1 JOIN TAB_2 @@ -981,12 +461,5 @@ public class TestPrivilegesAtColumnScope extends AbstractTestWithStaticConfigura statement.close(); connection.close(); - - // test cleanup - connection = context.createConnection(ADMIN1); - statement = context.createStatement(connection); - statement.execute("DROP DATABASE DB_1 CASCADE"); - statement.close(); - connection.close(); } }
