| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.RolesConferredPrivilegesTest |
| |
| 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.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.SQLWarning; |
| import java.sql.Statement; |
| import java.util.ArrayList; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * This tests that SQL roles actually confer the correct privileges, that is, |
| * when privileges are granted to one or more roles and those roles are granted |
| * to other roles, to one or more users or to PUBLIC, sessions can make use of |
| * them correctly. |
| */ |
| public class RolesConferredPrivilegesTest extends BaseJDBCTestCase |
| { |
| private final static String pwSuffix = "pwSuffix"; |
| |
| /* SQL states */ |
| private final static String NOEXECUTEPERMISSION = "42504"; |
| private final static String NOTABLEPERMISSION = "42500"; |
| private final static String NOCOLUMNPERMISSION = "42502"; |
| private final static String TABLENOTFOUND = "42X05"; |
| private final static String OBJECTNOTFOUND = "42X94"; |
| private final static String FKVIOLATION = "23503"; |
| private final static String CHECKCONSTRAINTVIOLATED = "23513"; |
| private final static String ALREADYCLOSED = "XJ012"; |
| private final static String CONSTRAINTDROPPED = "01500"; |
| private final static String VIEWDROPPED = "01501"; |
| private final static String TRIGGERDROPPED = "01502"; |
| private final static String UNRELIABLE = "42Y39"; |
| |
| private final static String[] users = {"test_dbo", "DonaldDuck", "MickeyMouse"}; |
| |
| /** |
| * Create a new instance of RolesConferredPrivilegesTest. |
| * |
| * @param name Fixture name |
| */ |
| public RolesConferredPrivilegesTest(String name) |
| { |
| super(name); |
| } |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| * |
| * @return A suite containing embedded and client suites. |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = |
| new BaseTestSuite("RolesConferredPrivilegesTest"); |
| |
| suite.addTest(makeSuite()); |
| |
| // suite.addTest( |
| // TestConfiguration.clientServerDecorator(makeSuite())); |
| |
| return suite; |
| } |
| |
| /** |
| * Construct suite of tests |
| * |
| * @return A suite containing the test cases. |
| */ |
| private static Test makeSuite() |
| { |
| /* Tests running with sql authorization set. First decorate |
| * with clean database, then with authentication + |
| * sqlAuthorization. |
| */ |
| Test clean = new CleanDatabaseTestSetup( |
| new BaseTestSuite(RolesConferredPrivilegesTest.class)) { |
| protected void decorateSQL(Statement s) |
| throws SQLException { |
| /* |
| * a1 a2 a3 |
| * / | \ | | |
| * / b +--------> c d |
| * j | \ / |
| * e---+ \ / |
| * \ \ \ / |
| * \ \---------+ \ / |
| * \ \_ f |
| * \ / |
| * \ / |
| * \ / |
| * \ / |
| * \ / |
| * \ / |
| * h |
| */ |
| s.execute("create role a1"); |
| s.execute("create role j"); |
| s.execute("create role b"); |
| s.execute("create role e"); |
| s.execute("create role h"); |
| s.execute("create role a2"); |
| s.execute("create role c"); |
| s.execute("create role f"); |
| s.execute("create role a3"); |
| s.execute("create role d"); |
| |
| s.execute("grant a1 to j"); |
| s.execute("grant a1 TO b"); |
| s.execute("grant b TO e"); |
| s.execute("grant e TO h"); |
| s.execute("grant a1 TO c"); |
| s.execute("grant e TO f"); |
| s.execute("grant a2 TO c"); |
| s.execute("grant c TO f"); |
| s.execute("grant f TO h"); |
| s.execute("grant a3 TO d"); |
| s.execute("grant d TO f"); |
| |
| s.execute("create schema s1"); |
| s.execute |
| ("create function s1.f1( ) returns int " + |
| "language java parameter style java external name " + |
| "'org.apache.derbyTesting.functionTests.tests.lang." + |
| "RolesConferredPrivilegesTest.s1f1' " + |
| "no sql called on null input"); |
| s.execute |
| ("create function s1.f2( ) returns int " + |
| "language java parameter style java external name " + |
| "'org.apache.derbyTesting.functionTests.tests.lang." + |
| "RolesConferredPrivilegesTest.s1f1' " + |
| "no sql called on null input"); |
| s.execute |
| ("create table s1.t1(" + |
| "c1 int unique, c2 int unique, c3 int unique, " + |
| "primary key (c1,c2,c3))"); |
| // We made columns all unique so we can test references |
| // privilege for all columns. |
| s.execute( |
| "create procedure s1.calledNested()" + |
| " language java parameter style java" + |
| " external name " + |
| "'org.apache.derbyTesting.functionTests.tests.lang." + |
| "RolesConferredPrivilegesTest.calledNested' " + |
| " modifies sql data"); |
| s.execute |
| ("create function s1.getCurrentRole() " + |
| "returns varchar(30)" + |
| "language java parameter style java external name " + |
| "'org.apache.derbyTesting.functionTests.tests.lang." + |
| "RolesConferredPrivilegesTest.getCurrentRole' " + |
| " reads sql data"); |
| } |
| }; |
| |
| return |
| TestConfiguration.sqlAuthorizationDecorator( |
| DatabasePropertyTestSetup.singleProperty( |
| DatabasePropertyTestSetup.builtinAuthentication( |
| clean, users, pwSuffix), |
| // Increase default statementCacheSize since we compile a |
| // lot: |
| "derby.language.statementCacheSize", "1000")); |
| } |
| |
| private final static int GRANT = 0; |
| private final static int REVOKE = 1; |
| |
| // Action type for assert methods. |
| private final static int NOPRIV = 0; |
| private final static int VIAUSER = 1; |
| private final static int VIAROLE = 2; |
| |
| // The "guts" of GRANT/REVOKE privilege strings. Prepend GRANT/REVOKE and |
| // append TO/FROM authorizationId [RESTRICT] as the case may be. |
| private final static String g_r = "references on s1.t1 "; |
| private final static String g_r_c1 = "references (c1) on s1.t1 "; |
| private final static String g_r_c2 = "references (c2) on s1.t1 "; |
| private final static String g_r_c3 = "references (c3) on s1.t1 "; |
| private final static String g_u = "update on s1.t1 "; |
| private final static String g_u_c1 = "update (c1) on s1.t1 "; |
| private final static String g_u_c2 = "update (c2) on s1.t1 "; |
| private final static String g_u_c3 = "update (c3) on s1.t1 "; |
| private final static String g_u_c1_c2_c3 = "update (c1,c2,c3) on s1.t1 "; |
| private final static String g_i = "insert on s1.t1 "; |
| private final static String g_s = "select on s1.t1 "; |
| private final static String g_s_c1 = "select (c1) on s1.t1 "; |
| private final static String g_s_c2 = "select (c2) on s1.t1 "; |
| private final static String g_s_c3 = "select (c3) on s1.t1 "; |
| private final static String g_d = "delete on s1.t1 "; |
| private final static String g_t = "trigger on s1.t1 "; |
| private final static String g_e = "execute on function s1.f1 "; |
| private final static String g_e_f2 = "execute on function s1.f2 "; |
| |
| // Collections of privileges |
| private final static String[] g_all = |
| new String[] {g_r, g_r_c1, g_r_c2, g_r_c3, |
| g_u, g_u_c1, g_u_c2, g_u_c3, |
| g_i, |
| g_s, g_s_c1, g_s_c2, g_s_c3, |
| g_d, |
| g_t, |
| g_e}; |
| // column level (for privilege types applicable) |
| private final static String[] g_all_col = |
| new String[] {g_r_c1, g_r_c2, g_r_c3, |
| g_u_c1, g_u_c2, g_u_c3, |
| g_i, |
| g_s_c1, g_s_c2, g_s_c3, |
| g_d, |
| g_t, |
| g_e}; |
| // table level |
| private final static String[] g_all_tab = |
| new String[] {g_r, g_u, g_i, g_s, g_d, g_t, g_e}; |
| |
| private final static String[][]grantRevokes = |
| new String[][] {g_all, g_all_col, g_all_tab}; |
| |
| /** |
| * Basic test that checks that privileges granted to a role are applicable |
| * when the user sets the current role to that role (or to a role that |
| * inherits that role). |
| * |
| * @throws SQLException |
| */ |
| public void atestConferredPrivileges() throws SQLException |
| { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| // try with role granted to both a user and to public |
| String[] grantees = new String[] {"DonaldDuck", "public"}; |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| |
| for (int gNo = 0; gNo < grantees.length; gNo++ ) { |
| /* |
| * Grant a role to a session's user and test that privileges apply |
| * when granted to any applicable role. |
| */ |
| s.executeUpdate("grant h to " + grantees[gNo]); |
| |
| String[] applicableFor_h = |
| new String[] {"h", "a1", "a2", "a3", "b", "e", "c", "f", "d"}; |
| String[] notApplicableFor_h = |
| new String[] {"j"}; |
| setRole(c, "h"); |
| |
| // Test that all privileges apply when granted to the current role |
| // or to an inherited role of the current role, cf graph above. |
| for (int i=0; i < applicableFor_h.length; i++) { |
| assertAllforRole(VIAROLE, c, applicableFor_h[i]); |
| } |
| |
| // Test that no privileges apply when granted to a role NOT |
| // inherited by the current role. |
| for (int i=0; i < notApplicableFor_h.length; i++) { |
| assertAllforRole(NOPRIV, c, notApplicableFor_h[i]); |
| } |
| |
| /* |
| * Test that no privileges apply when we set role to none. |
| */ |
| setRole(c, "none"); |
| |
| for (int i=0; i < applicableFor_h.length; i++) { |
| assertAllforRole(NOPRIV, c, applicableFor_h[i]); |
| } |
| |
| /* |
| * Test that when one link in the graph "cycle" is broken, |
| * privileges still apply. |
| */ |
| s.executeUpdate("grant f to " + grantees[gNo]); |
| setRole(c, "f"); |
| assertAllforRole(VIAROLE, c, "a1"); |
| // one arc gone |
| s.executeUpdate("revoke a1 from c"); |
| assertAllforRole(VIAROLE, c, "a1"); |
| // both arcs gone |
| s.executeUpdate("revoke a1 from b"); |
| assertAllforRole(NOPRIV, c, "a1"); |
| // resurrect other arc |
| s.executeUpdate("grant a1 to b"); |
| assertAllforRole(VIAROLE, c, "a1"); |
| // restore things |
| s.executeUpdate("grant a1 to c"); |
| s.executeUpdate("revoke f from " + grantees[gNo]); |
| |
| /* |
| * Revoke the role from the current session's user and verify that |
| * privileges no longer apply when granted to any heretofore |
| * applicable role. |
| */ |
| setRole(c, "h"); |
| |
| s.executeUpdate("revoke h from " + grantees[gNo]); |
| |
| for (int i=0; i < applicableFor_h.length; i++) { |
| assertAllforRole(NOPRIV, c, applicableFor_h[i]); |
| } |
| |
| /* |
| * Test when role is dropped when still a current role |
| */ |
| s.executeUpdate("grant h to " + grantees[gNo]); |
| setRole(c, "h"); |
| |
| // Test that all privileges apply when granted to the current role |
| // or to an inherited role of the current role, cf graph above. |
| for (int i=0; i < applicableFor_h.length; i++) { |
| assertAllforRole(VIAROLE, c, applicableFor_h[i]); |
| } |
| |
| s.executeUpdate("drop role h"); |
| |
| for (int i=0; i < applicableFor_h.length; i++) { |
| assertAllforRole(NOPRIV, c, applicableFor_h[i]); |
| } |
| |
| // restore the dropped role |
| s.executeUpdate("create role h"); |
| s.executeUpdate("grant e to h"); |
| s.executeUpdate("grant f to h"); |
| } |
| |
| c.close(); |
| s.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * When a view, a trigger or a constraint requires a privilege by way of |
| * the current role (or by way of a role inherited by the current role) at |
| * creation time (SELECT, TRIGGER or REFERENCES privilege respectively), a |
| * dependency is also registered against the current role. Whenever that |
| * role (it need no longer be current) - or indeed one of its inherited |
| * roles - is revoked (from the current user or from a role in the closure |
| * of the original current role) or dropped, the dependent view, trigger or |
| * constraint is potentially invalidated (the single dependency is against |
| * the original current role, not against the potentially n-ary set of |
| * roles in the closure of the current role used to find the required |
| * privileges). Due to DERBY-1632, currently the objects are dropped |
| * instead of being potentially revalidated. |
| * |
| * These tests check that invalidation actually happens and leads to a |
| * dropping of the dependent view (there are also no revalidation |
| * possibilities in play here, so even when DERBY-1632 is fixed these tests |
| * should work). |
| */ |
| public void testViewInvalidation() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| SQLWarning w; |
| /* |
| * 3-dimensional search space: |
| * |
| * Which role we grant the role to (direct to a role or to a role it |
| * inherits) |
| * X |
| * Whether the role is granted directly to the session user or to PUBLIC. |
| * X |
| * Whether we grant the entire underlying table or just the column |
| * needed. |
| */ |
| String[] grantToThisRole = new String[] {"a2", "h"}; |
| String[] roleGrantees = new String[] {"DonaldDuck", "public"}; |
| String[] tabAndColSelectsPerms = new String[] {g_s, g_s_c1}; |
| String createViewString = "create view v as select c1 from s1.t1"; |
| |
| for (int r = 0; r < grantToThisRole.length; r++) { |
| for (int gNo = 0; gNo < roleGrantees.length; gNo++ ) { |
| for (int i = 0; i < tabAndColSelectsPerms.length; i++) { |
| /* |
| * Create a view on the basis of a select privilege via a |
| * role. |
| */ |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| |
| doGrantRevoke(GRANT, "test_dbo", tabAndColSelectsPerms[i], |
| grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createViewString); |
| |
| assertViewExists(true, c, "v"); |
| |
| /* |
| * Setting another role does not affect the view once |
| * defined. |
| */ |
| setRole(c, "none"); |
| assertViewExists(true, c, "v"); |
| |
| /* |
| * Remove privileges from role, and the view should be |
| * gone. |
| */ |
| doGrantRevoke(REVOKE, "test_dbo", tabAndColSelectsPerms[i], |
| grantToThisRole[r], VIEWDROPPED); |
| assertViewExists(false, c, "v"); |
| |
| /* |
| * Revoking the role should also invalidate view |
| */ |
| doGrantRevoke(GRANT, "test_dbo", tabAndColSelectsPerms[i], |
| grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createViewString); |
| assertViewExists(true, c, "v"); |
| |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| w = s.getWarnings(); |
| assertSQLState(VIEWDROPPED, w); |
| assertViewExists(false, c, "v"); |
| |
| /* |
| * Check that user privilege and/or PUBLIC privilege is |
| * preferred over role privilege if available. This is not |
| * standard SQL, but useful behavior IMHO as long as Derby |
| * can't revalidate via another path (DERBY-1632) - lest a |
| * role revoke or drop causes an invalidation when user has |
| * discretionary privilege. Cf. also comment on priority of |
| * user vs public in DERBY-1611. |
| */ |
| String[] directGrantee = roleGrantees; |
| |
| for (int u = 0; u < directGrantee.length; u++) { |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| doGrantRevoke(GRANT, "test_dbo", |
| tabAndColSelectsPerms[i], |
| directGrantee[u]); |
| |
| setRole(c, "h"); |
| |
| // Now we have select privilege two ways, via role and |
| // via user. |
| cStmt.executeUpdate(createViewString); |
| |
| // Now revoke role priv and see that view is still |
| // unaffected. |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertViewExists(true, c, "v"); |
| |
| // Take away user privilege, too. |
| doGrantRevoke(REVOKE, "test_dbo", |
| tabAndColSelectsPerms[i], |
| directGrantee[u], |
| VIEWDROPPED); |
| assertViewExists(false, c, "v"); |
| } |
| |
| // clean up |
| doGrantRevoke(REVOKE, "test_dbo",tabAndColSelectsPerms[i], |
| grantToThisRole[r]); |
| } |
| } |
| } |
| |
| /* |
| * Dropping a role should also invalidate a dependent view. |
| * |
| * (We do this test outside the loop above for simplicity of |
| * reestablish role graph after the drop..) |
| */ |
| |
| // drop the current role |
| doGrantRevoke(GRANT, "test_dbo", g_s, "h"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createViewString); |
| assertViewExists(true, c, "v"); |
| |
| s.executeUpdate("drop role h"); |
| |
| w = s.getWarnings(); |
| assertSQLState(VIEWDROPPED, w); |
| assertViewExists(false, c, "v"); |
| |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| // re-establish role graph |
| s.executeUpdate("create role h"); |
| s.executeUpdate("grant e to h"); |
| s.executeUpdate("grant f to h"); |
| |
| // drop an inherited role needed |
| doGrantRevoke(GRANT, "test_dbo", g_s, "a3"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createViewString); |
| assertViewExists(true, c, "v"); |
| |
| s.executeUpdate("drop role a3"); |
| |
| w = s.getWarnings(); |
| assertSQLState(VIEWDROPPED, w); |
| assertViewExists(false, c, "v"); |
| |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| // re-establish role graph |
| s.executeUpdate("create role a3"); |
| s.executeUpdate("grant a3 to d"); |
| |
| cStmt.close(); |
| c.close(); |
| s.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * @see #testViewInvalidation |
| */ |
| public void testTriggerInvalidation() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| SQLWarning w; |
| |
| /* |
| * 2-dimensional search space: |
| * |
| * Which role we grant the role to (direct to a role or to a role it |
| * inherits) |
| * X |
| * Whether the role is granted directly to the session user or to PUBLIC. |
| */ |
| String[] grantToThisRole = new String[] {"a2", "h"}; |
| String[] roleGrantees = new String[] {"DonaldDuck", "public"}; |
| String createTriggerString = |
| "create trigger t after insert on s1.t1 values 1"; |
| |
| for (int r = 0; r < grantToThisRole.length; r++) { |
| for (int gNo = 0; gNo < roleGrantees.length; gNo++ ) { |
| /* |
| * Create a trigger on the basis of a trigger privilege via a |
| * role. |
| */ |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| |
| doGrantRevoke(GRANT, "test_dbo", g_t, grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createTriggerString); |
| |
| assertTriggerExists(true, c, "t"); |
| cStmt.executeUpdate(createTriggerString); |
| /* |
| * Setting another role does not affect the trigger once |
| * defined. |
| */ |
| setRole(c, "none"); |
| assertTriggerExists(true, c, "t"); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createTriggerString); |
| |
| // Remove privileges from role, and the trigger should be |
| // gone. |
| doGrantRevoke(REVOKE, "test_dbo", g_t, grantToThisRole[r], |
| TRIGGERDROPPED); |
| |
| assertTriggerExists(false, c, "t"); |
| |
| /* |
| * Revoking the role should also invalidate trigger |
| */ |
| doGrantRevoke(GRANT, "test_dbo", g_t, grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createTriggerString); |
| assertTriggerExists(true, c, "t"); |
| cStmt.executeUpdate(createTriggerString); |
| |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| |
| /* |
| * Check that user privilege and/or PUBLIC privilege is |
| * preferred over role privilege if available. This is not |
| * standard SQL, but useful behavior IMHO as long as Derby |
| * can't revalidate via another path (DERBY-1632) - lest a |
| * role revoke or drop causes an invalidation when user has |
| * discretionary privilege. Cf. also comment on priority of |
| * user vs public in DERBY-1611. |
| */ |
| String[] directGrantee = roleGrantees; |
| |
| for (int u = 0; u < directGrantee.length; u++) { |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| doGrantRevoke(GRANT, "test_dbo", g_t, directGrantee[u]); |
| |
| setRole(c, "h"); |
| |
| // Now we have trigger privilege two ways,a via role and |
| // via user. |
| cStmt.executeUpdate(createTriggerString); |
| |
| // Now revoke role priv and see that trigger is still |
| // unaffected. |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertTriggerExists(true, c, "t"); |
| cStmt.executeUpdate(createTriggerString); |
| |
| // take away user privilege, too |
| doGrantRevoke(REVOKE, "test_dbo",g_t,directGrantee[u], |
| TRIGGERDROPPED); |
| assertTriggerExists(false, c, "t"); |
| } |
| |
| // clean up |
| doGrantRevoke(REVOKE, "test_dbo", g_t, grantToThisRole[r]); |
| } |
| } |
| |
| /* |
| * Dropping a role should also invalidate a dependent trigger. |
| * |
| * (We do this test outside the loop above for simplicity of |
| * reestablish role graph after the drop..) |
| */ |
| doGrantRevoke(GRANT, "test_dbo", g_t, "h"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(createTriggerString); |
| assertTriggerExists(true, c, "t"); |
| cStmt.executeUpdate(createTriggerString); |
| |
| s.executeUpdate("drop role h"); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| |
| doGrantRevoke(REVOKE, "test_dbo", g_t, "h"); |
| |
| // re-establish role graph |
| s.executeUpdate("create role h"); |
| s.executeUpdate("grant e to h"); |
| s.executeUpdate("grant f to h"); |
| |
| /* |
| * Dropping an EXECUTE privilege used in a trigger body will not drop |
| * the trigger if the EXECUTE privilege is revoked from a user |
| * directly, since this currently requires the RESTRICT |
| * keyword. However, revoking a role does not carry the RESTRICT |
| * keyword, so any execution privilege conferred through a role is |
| * revoked, too, and any dependent object, for example a trigger in |
| * example below, will be dropped. |
| */ |
| doGrantRevoke(GRANT, "test_dbo", g_t, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e, "h"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| |
| cStmt.executeUpdate |
| ("create trigger t after insert on s1.t1 values s1.f1()"); |
| assertTriggerExists(true, c, "t"); |
| |
| cStmt.executeUpdate |
| ("create trigger t after insert on s1.t1 values s1.f1()"); |
| |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| |
| doGrantRevoke(REVOKE, "test_dbo", g_t, "h"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e, "h"); |
| |
| /* |
| * Check that dependency on role and subsequent invalidation happens |
| * for a mix of column SELECT privileges granted to user, public and |
| * role (due to tricky logic in this implementation, |
| * cf. DDLConstantAction#storeViewTriggerDependenciesOnPrivileges |
| */ |
| |
| // SELECT privileges to {public, role} x |
| // TRIGGER privilege to {user, role} |
| String triggerPrivGrantees[] = new String[] {"h", "DonaldDuck"}; |
| for (int i=0; i < triggerPrivGrantees.length; i++) { |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c1, "public"); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c2, "h"); |
| cStmt.executeUpdate |
| ("create trigger t after insert on s1.t1 " + |
| "select c1,c2 from s1.t1"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c1, "public"); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c2, "h"); |
| } |
| |
| // SELECT privileges to {user, role} x |
| // TRIGGER privilege to {user, role} |
| for (int i=0; i < triggerPrivGrantees.length; i++) { |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c1, "DonaldDuck"); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c2, "h"); |
| cStmt.executeUpdate |
| ("create trigger t after insert on s1.t1 " + |
| "select c1,c2 from s1.t1"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c1, "DonaldDuck"); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c2, "h"); |
| } |
| |
| // SELECT privileges to {user, public, role} x |
| // TRIGGER privilege to {user, role} |
| for (int i=0; i < triggerPrivGrantees.length; i++) { |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c1, "DonaldDuck"); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c2, "public"); |
| doGrantRevoke(GRANT, "test_dbo", g_s_c3, "h"); |
| cStmt.executeUpdate |
| ("create trigger t after insert on s1.t1 " + |
| "select c1,c2,c3 from s1.t1"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(TRIGGERDROPPED, w); |
| assertTriggerExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_t, triggerPrivGrantees[i]); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c1, "DonaldDuck"); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c2, "public"); |
| doGrantRevoke(REVOKE, "test_dbo", g_s_c3, "h"); |
| } |
| |
| cStmt.close(); |
| c.close(); |
| s.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * @see #testViewInvalidation |
| */ |
| public void testConstraintInvalidation() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| SQLWarning w; |
| /* |
| * 3-dimensional search space: |
| * |
| * Which role we grant the role to (direct to a role or to a role it |
| * inherits) |
| * X |
| * Whether the role is granted directly to the session user or to PUBLIC. |
| * X |
| * Whether we grant the entire underlying table or just the column |
| * needed. |
| */ |
| String[] grantToThisRole = new String[] {"a2", "h"}; |
| String[] roleGrantees = new String[] {"DonaldDuck", "public"}; |
| String[][] tabAndColReferencesPerms = |
| new String[][] {{g_r}, {g_r_c1, g_r_c2, g_r_c3}}; |
| |
| String createTableString = |
| "create table t (i int not null, j int, k int)"; |
| String dropTableString = "drop table t"; |
| String addConstraintString = |
| "alter table t add constraint fk " + |
| "foreign key(i,j,k) references s1.t1"; |
| |
| cStmt.executeUpdate(createTableString); |
| |
| for (int r = 0; r < grantToThisRole.length; r++) { |
| for (int gNo = 0; gNo < roleGrantees.length; gNo++ ) { |
| for (int i = 0; i < tabAndColReferencesPerms.length; i++) { |
| /* |
| * Create a foreign key constraint on the basis of a |
| * references privilege via a role. |
| */ |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| |
| doGrantRevoke(GRANT, "test_dbo", |
| tabAndColReferencesPerms[i], |
| grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(addConstraintString); |
| |
| assertFkConstraintExists(true, c, "t"); |
| |
| /* |
| * Setting another role does not affect the constraint once |
| * defined. |
| */ |
| setRole(c, "none"); |
| assertFkConstraintExists(true, c, "t"); |
| |
| // Remove privileges from role, and the constraint should be |
| // gone. |
| doGrantRevoke |
| (REVOKE, "test_dbo", |
| tabAndColReferencesPerms[i], |
| grantToThisRole[r], |
| new String[]{CONSTRAINTDROPPED, null, null}); |
| |
| assertFkConstraintExists(false, c, "t"); |
| |
| /* |
| * Revoking the role should also invalidate constraint |
| */ |
| doGrantRevoke(GRANT, "test_dbo", |
| tabAndColReferencesPerms[i], |
| grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(addConstraintString); |
| assertFkConstraintExists(true, c, "t"); |
| |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertFkConstraintExists(false, c, "t"); |
| |
| /* |
| * Check that user privilege and/or PUBLIC privilege is |
| * preferred over role privilege if available. This is not |
| * standard SQL, but useful behavior IMHO as long as Derby |
| * can't revalidate via another path (DERBY-1632) - lest a |
| * role revoke or drop causes an invalidation when user has |
| * discretionary privilege. Cf. also comment on priority of |
| * user vs public in DERBY-1611. |
| */ |
| String[] directGrantee = roleGrantees; |
| |
| for (int u = 0; u < directGrantee.length; u++) { |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| doGrantRevoke(GRANT, "test_dbo", |
| tabAndColReferencesPerms[i], |
| directGrantee[u]); |
| |
| setRole(c, "h"); |
| |
| // Now we have references privilege two ways, via role |
| // and via user. |
| cStmt.executeUpdate(addConstraintString); |
| |
| // Now revoke role priv and see that constraints is |
| // still unaffected. |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertFkConstraintExists(true, c, "t"); |
| |
| // take away user privilege, too |
| doGrantRevoke |
| (REVOKE, "test_dbo", |
| tabAndColReferencesPerms[i], |
| directGrantee[u], |
| new String[]{CONSTRAINTDROPPED, null, null}); |
| assertFkConstraintExists(false, c, "t"); |
| } |
| |
| // clean up |
| doGrantRevoke |
| (REVOKE, "test_dbo", |
| tabAndColReferencesPerms[i], |
| grantToThisRole[r]); |
| } |
| } |
| } |
| |
| /* |
| * Dropping a role should also invalidate a dependent constraint. |
| * |
| * (We do this test outside the loop above for simplicity of |
| * reestablish role graph after the drop..) |
| */ |
| doGrantRevoke(GRANT, "test_dbo", g_r, "h"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| |
| setRole(c, "h"); |
| cStmt.executeUpdate(addConstraintString); |
| assertFkConstraintExists(true, c, "t"); |
| |
| s.executeUpdate("drop role h"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertFkConstraintExists(false, c, "t"); |
| |
| |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| |
| // re-establish role graph |
| s.executeUpdate("create role h"); |
| s.executeUpdate("grant e to h"); |
| s.executeUpdate("grant f to h"); |
| |
| /* |
| * For FOREIGN KEY constraint, check that dependency on role and |
| * subesquent invalidation happens for a mix of column privileges |
| * granted to user, public and role (due to tricky logic in this |
| * implementation, |
| * cf. DDLConstantAction#storeConstraintDependenciesOnPrivileges |
| */ |
| // {role, role} |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", |
| new String[] {g_r_c1, g_r_c2, g_r_c3}, "h"); |
| cStmt.executeUpdate |
| ("alter table t add constraint fk foreign key(i,j,k) " + |
| "references s1.t1"); |
| |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertFkConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", |
| new String[] {g_r_c1, g_r_c2, g_r_c3}, "h"); |
| |
| // {public, role} |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c1, "public"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c2, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c3, "h"); |
| cStmt.executeUpdate("alter table t add constraint fk " + |
| "foreign key(i,j,k) references s1.t1"); |
| |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertFkConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c1, "public"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c2, "h"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c3, "h"); |
| |
| // {user, role} |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c1, "DonaldDuck"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c2, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c3, "h"); |
| cStmt.executeUpdate("alter table t add constraint fk " + |
| "foreign key(i,j,k) references s1.t1"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertFkConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c1, "DonaldDuck"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c2, "h"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c3, "h"); |
| |
| // {user, public, role} |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c1, "DonaldDuck"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c2, "public"); |
| doGrantRevoke(GRANT, "test_dbo", g_r_c3, "h"); |
| cStmt.executeUpdate("alter table t add constraint fk " + |
| "foreign key(i,j,k) references s1.t1"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertFkConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c1, "DonaldDuck"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c2, "public"); |
| doGrantRevoke(REVOKE, "test_dbo", g_r_c3, "h"); |
| |
| // Try the same as above but with EXECUTE privilege instead of |
| // REFERENCES for a CHECK constraint |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e, "h"); |
| cStmt.executeUpdate("alter table t add constraint ch " + |
| "check(i < s1.f1())"); |
| assertCheckConstraintExists(true, c, "t"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertCheckConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e, "h"); |
| |
| // Try the same as above but with two EXECUTE privileges |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e_f2, "DonaldDuck"); |
| cStmt.executeUpdate("alter table t add constraint ch " + |
| "check(i < (s1.f1() + s1.f2()))"); |
| assertCheckConstraintExists(true, c, "t"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| w = s.getWarnings(); |
| assertSQLState(CONSTRAINTDROPPED, w); |
| assertCheckConstraintExists(false, c, "t"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e, "h"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e_f2, "DonaldDuck"); |
| |
| // Try the same as above but with multiple CHECK constraints to verify |
| // that only those affected by a revoke are impacted. |
| s.executeUpdate("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e, "h"); |
| doGrantRevoke(GRANT, "test_dbo", g_e_f2, "DonaldDuck"); |
| cStmt.executeUpdate |
| ("create table tmp(i int constraint ct1 check(i < s1.f1())," + |
| " j int constraint ct2 check(j < s1.f2()))"); |
| s.executeUpdate("revoke h from DonaldDuck"); |
| |
| // This should only impact ct1 |
| try { |
| cStmt.executeUpdate("insert into tmp values (6, -1)"); |
| } catch (SQLException e) { |
| fail("expected success", e); |
| } |
| |
| try { |
| cStmt.executeUpdate("insert into tmp values (6, 6)"); |
| fail("ct2 should remain"); |
| } catch (SQLException e) { |
| assertSQLState(CHECKCONSTRAINTVIOLATED, e); |
| } |
| |
| cStmt.executeUpdate("alter table tmp drop constraint ct2"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e, "h"); |
| doGrantRevoke(REVOKE, "test_dbo", g_e_f2, "DonaldDuck"); |
| cStmt.executeUpdate("drop table tmp"); |
| |
| cStmt.executeUpdate(dropTableString); |
| |
| cStmt.close(); |
| c.close(); |
| s.close(); |
| dboConn.close(); |
| } |
| |
| /** |
| * DERBY-4191 |
| * There are times when no column is selected from a table in the from |
| * list. At such a time, we should make sure that we make sure there |
| * is atleast some kind of select privilege available on that table for |
| * the query to succeed. eg of such queries |
| * select count(*) from t1 |
| * select count(1) from t1 |
| * select 1 from t1 |
| * select t1.c1 from t1, t2 |
| * |
| * In addition, the subquery inside of a NON-select query should require |
| * select privilege on the tables involved in the subquery eg |
| * update dbo.t set a = ( select max(a1) + 2 from dbo.t1 ) |
| * update dbo.t set a = ( select max(b1) + 2 from dbo.t2 ) |
| * For both the queries above, in addition to update privilege requirement |
| * on dbo.t(a), we need to require select privileges on columns/tables |
| * within the select list. So for first query, the user should have select |
| * privilege on dbo.t1 or dbo.t1(a1). Similarly, for 2nd query, the user |
| * should have select privilege on dbo.t2 or dbo.t2(b1) |
| * @throws SQLException |
| */ |
| public void testMinimumSelectPrivilege() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement stmtDBO = dboConn.createStatement(); |
| |
| Connection cDD = openUserConnection("DonaldDuck"); |
| Statement stmtDD = cDD.createStatement(); |
| |
| Connection cMM = openUserConnection("MickeyMouse"); |
| Statement stmtMM = cMM.createStatement(); |
| |
| stmtDBO.executeUpdate("create role role1"); |
| stmtDBO.executeUpdate("grant role1 to MickeyMouse"); |
| |
| stmtDD.executeUpdate("create table DDtable1(c11 int, c12 int)"); |
| stmtDD.executeUpdate("insert into DDtable1 values(1, 2)"); |
| stmtDD.executeUpdate("create table DDtable2(c21 int, c22 int)"); |
| stmtDD.executeUpdate("insert into DDtable2 values(3, 4)"); |
| |
| stmtMM.executeUpdate("set role role1"); |
| try { |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42502", e); |
| } |
| try { |
| stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " + |
| " (select c21 from DonaldDuck.DDtable2)"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42502", e); |
| } |
| |
| stmtDD.executeUpdate("grant select(c12) on DDtable1 to role1"); |
| stmtDD.executeUpdate("grant update on DDtable1 to role1"); |
| stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1"); |
| try { |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42502", e); |
| } |
| try { |
| stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " + |
| " (select c21 from DonaldDuck.DDtable2)"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42502", e); |
| } |
| |
| stmtDD.executeUpdate("grant select(c11) on DDtable1 to role1"); |
| stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1"); |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1"); |
| try { |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " + |
| "DonaldDuck.DDtable2"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42500", e); |
| } |
| try { |
| stmtMM.executeQuery("update DonaldDuck.DDtable1 set c11 = " + |
| " (select c21 from DonaldDuck.DDtable2)"); |
| fail("select should have failed"); |
| } catch (SQLException e) { |
| assertSQLState("42502", e); |
| } |
| |
| stmtDD.executeUpdate("grant select(c21) on DDtable2 to role1"); |
| stmtMM.executeQuery("select c12 from DonaldDuck.DDtable1"); |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1"); |
| stmtMM.executeQuery("select c11 from DonaldDuck.DDtable1, " + |
| "DonaldDuck.DDtable2"); |
| stmtMM.executeUpdate("update DonaldDuck.DDtable1 set c11 = " + |
| " (select c21 from DonaldDuck.DDtable2)"); |
| } |
| |
| /** |
| * Test that a prepared statement can no longer execute after its required |
| * privileges acquired via the current role are no longer applicable. |
| */ |
| public void testPSInvalidation() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| |
| /* |
| * 3-dimensional search space: |
| * |
| * Which role we grant the role to (direct to a role or to a role it |
| * inherits) |
| * X |
| * Whether the role is granted directly to the session user or to PUBLIC. |
| * X |
| * Whether we grant the entire underlying table or just the column |
| * needed. |
| */ |
| String[] grantToThisRole = new String[] {"a2", "h"}; |
| String[] roleGrantees = new String[] {"DonaldDuck", "public"}; |
| String[][] privilegeStmts = |
| new String[][] {{g_s, "select c1 from s1.t1"}, |
| {g_s_c1, "select c1 from s1.t1"}, |
| {g_e, "values s1.f1()"}, |
| {g_u, "update s1.t1 set c1=0"}, |
| {g_u_c1_c2_c3, "update s1.t1 set c1=0"}, |
| {g_i, "insert into s1.t1 values (5,5,5)"}}; |
| |
| PreparedStatement ps = null; |
| |
| for (int r = 0; r < grantToThisRole.length; r++) { |
| for (int gNo = 0; gNo < roleGrantees.length; gNo++ ) { |
| for (int i = 0; i < privilegeStmts.length; i++) { |
| /* |
| * Create a ps on the basis of a select privilege via a |
| * role. |
| */ |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| |
| doGrantRevoke(GRANT, "test_dbo", privilegeStmts[i][0], |
| grantToThisRole[r]); |
| |
| setRole(c, "h"); |
| ps = c.prepareStatement(privilegeStmts[i][1]); |
| |
| assertPsWorks(true, ps); |
| |
| /* |
| * Setting another role should make the ps fail, since we |
| * no longer have the privilege. |
| */ |
| setRole(c, "none"); |
| assertPsWorks(false, ps); |
| // set it back: |
| setRole(c, "h"); |
| assertPsWorks(true, ps); |
| |
| /* |
| * Remove privileges from role, and the execute should |
| * fail. |
| */ |
| doGrantRevoke(REVOKE, "test_dbo", privilegeStmts[i][0], |
| grantToThisRole[r]); |
| |
| assertPsWorks(false, ps); |
| doGrantRevoke(GRANT, "test_dbo", privilegeStmts[i][0], |
| grantToThisRole[r]); |
| |
| /* |
| * Revoking the role should also make the ps fail, since we |
| * no longer have the privilege. |
| */ |
| setRole(c, "h"); |
| assertPsWorks(true, ps); |
| |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertPsWorks(false, ps); |
| |
| /* |
| * Check that prepared statements are reprepared if there |
| * is another applicable privilege, when the privilege |
| * granted via a role is used first and that role is |
| * revoked. |
| */ |
| String[] directGrantee = roleGrantees; |
| |
| // iterate over granting role h to {user, PUBLIC} |
| for (int u = 0; u < directGrantee.length; u++) { |
| s.executeUpdate("grant h to " + roleGrantees[gNo]); |
| |
| setRole(c, "h"); |
| assertPsWorks(true, ps); |
| |
| doGrantRevoke(GRANT, "test_dbo", |
| privilegeStmts[i][0], |
| directGrantee[u]); |
| |
| // Now we have select privilege two ways, via role and |
| // via user or PUBLIC. |
| // Now revoke role priv and see that ps is still |
| // unaffected. |
| s.executeUpdate("revoke h from " + roleGrantees[gNo]); |
| assertPsWorks(true, ps); |
| |
| // Take away user privilege, too. |
| doGrantRevoke(REVOKE, "test_dbo", |
| privilegeStmts[i][0], |
| directGrantee[u]); |
| assertPsWorks(false, ps); |
| } |
| |
| // clean up |
| doGrantRevoke(REVOKE, "test_dbo",privilegeStmts[i][0], |
| grantToThisRole[r]); |
| } |
| } |
| } |
| |
| /* |
| * Dropping a role should also cause a dependent ps fail. |
| * |
| * (We do this test outside the loop above for simplicity of |
| * reestablish role graph after the drop..) |
| * |
| */ |
| for (int i=0; i < privilegeStmts.length; i++) { |
| doGrantRevoke(GRANT, "test_dbo", privilegeStmts[i][0], "h"); |
| s.executeUpdate("grant h to DonaldDuck"); |
| |
| setRole(c, "h"); |
| ps = c.prepareStatement(privilegeStmts[i][1]); |
| assertPsWorks(true, ps); |
| |
| s.executeUpdate("drop role h"); |
| assertPsWorks(false, ps); |
| |
| doGrantRevoke(REVOKE, "test_dbo", privilegeStmts[i][0], "h"); |
| |
| // re-establish role graph |
| s.executeUpdate("create role h"); |
| s.executeUpdate("grant e to h"); |
| s.executeUpdate("grant f to h"); |
| } |
| |
| cStmt.close(); |
| c.close(); |
| s.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * Test behavior for when there are open result sets on prepared statements |
| * that require privileges obtained via the current role and something |
| * changes in the middle of accessing the result set. We should be able to |
| * finish using the result set. |
| */ |
| public void testOpenRs() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| ResultSet rs = null; |
| String select = "select * from s1.t1"; |
| |
| PreparedStatement ps = dboConn.prepareStatement( |
| "insert into s1.t1 values (?,?,?)"); |
| for (int i=0; i < 5; i++) { |
| ps.setInt(1, i); |
| ps.setInt(2, i); |
| ps.setInt(3, i); |
| ps.execute(); |
| } |
| |
| |
| /* |
| * Select privilege revoked |
| */ |
| // Auto-commit on |
| doGrantRevoke(GRANT, "test_dbo", g_s, "h"); |
| s.execute("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| // Now remove privilege in middle of rs reading |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| |
| // Auto-commit off |
| c.setAutoCommit(false); |
| doGrantRevoke(GRANT, "test_dbo", g_s, "h"); |
| setRole(c, "h"); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| c.commit(); |
| // Now remove privilege in middle of rs reading |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| c.setAutoCommit(true); |
| |
| /* |
| * Role privilege revoked |
| */ |
| // Auto-commit on |
| doGrantRevoke(GRANT, "test_dbo", g_s, "h"); |
| s.execute("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| // Now remove privilege in middle of rs reading |
| s.execute("revoke h from DonaldDuck"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| |
| // Auto-commit off |
| c.setAutoCommit(false); |
| s.execute("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| c.commit(); |
| // Now remove privilege in middle of rs reading |
| s.execute("revoke h from DonaldDuck"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| c.setAutoCommit(true); |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| |
| /* |
| * Current role changed |
| */ |
| // Auto-commit on |
| doGrantRevoke(GRANT, "test_dbo", g_s, "h"); |
| s.execute("grant h to DonaldDuck"); |
| setRole(c, "h"); |
| c.setAutoCommit(true); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| // Now change role in middle of rs reading |
| setRole(c, "none"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| |
| // Auto-commit off |
| c.setAutoCommit(false); |
| setRole(c, "h"); |
| rs = cStmt.executeQuery(select); |
| |
| rs.next(); |
| // Now remove privilege in middle of rs reading |
| c.commit(); |
| setRole(c, "none"); |
| |
| // check that we can read the next row |
| rs.next(); |
| rs.close(); |
| c.setAutoCommit(true); |
| doGrantRevoke(REVOKE, "test_dbo", g_s, "h"); |
| |
| // clean up |
| s.executeUpdate("delete from s1.t1"); |
| c.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * Test that DEFAULT CURRENT_ROLE works as expected |
| * See DERBY-3897. |
| */ |
| public void testDefaultCurrentRole() throws SQLException { |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| s.execute("grant h to DonaldDuck"); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| setRole(c, "h"); |
| |
| // CREATE TABLE |
| cStmt.executeUpdate |
| ("create table t(role varchar(128) default current_role)"); |
| cStmt.executeUpdate("insert into t values default"); |
| ResultSet rs = cStmt.executeQuery("select * from t"); |
| JDBC.assertSingleValueResultSet(rs, "\"H\""); |
| rs.close(); |
| cStmt.executeUpdate("drop table t"); |
| |
| // ALTER TABLE |
| cStmt.executeUpdate("create table t(i int)"); |
| cStmt.executeUpdate("insert into t values 1"); |
| cStmt.executeUpdate |
| ("alter table t " + |
| "add column role varchar(10) default current_role"); |
| rs = cStmt.executeQuery("select * from t"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "\"H\""}}); |
| rs.close(); |
| cStmt.executeUpdate("drop table t"); |
| |
| // do the same from within a stored procedure |
| |
| s.execute("grant execute on procedure s1.calledNested to DonaldDuck"); |
| |
| if (!JDBC.vmSupportsJSR169()) { |
| // JSR169 cannot run with tests with stored procedures |
| // that do database access - for they require a |
| // DriverManager connection to jdbc:default:connection; |
| // DriverManager is not supported with JSR169. |
| cStmt.executeUpdate("call s1.calledNested()"); |
| } |
| |
| setRole(c, "none"); |
| |
| cStmt.close(); |
| s.execute("revoke h from DonaldDuck"); |
| s.execute("revoke execute on procedure s1.calledNested " + |
| "from DonaldDuck restrict"); |
| s.close(); |
| |
| c.close(); |
| dboConn.close(); |
| } |
| |
| |
| /** |
| * Test that CURRENT_ROLE works as expected in some miscellaneous contexts. |
| * See DERBY-3897. |
| */ |
| public void testCurrentRoleInWeirdContexts() throws SQLException { |
| if (JDBC.vmSupportsJSR169()) { |
| // JSR169 cannot run with tests with stored procedures |
| // that do database access - for they require a |
| // DriverManager connection to jdbc:default:connection; |
| // DriverManager is not supported with JSR169. |
| return; |
| } |
| |
| Connection dboConn = getConnection(); |
| Statement s = dboConn.createStatement(); |
| setRole(dboConn, "a1"); |
| s.execute("create table trackCreds(usr varchar(30), role varchar(30))"); |
| s.executeUpdate("create table t(i int)"); |
| s.execute("grant insert on t to DonaldDuck"); |
| s.execute("grant h to DonaldDuck"); |
| |
| // From within a trigger body: |
| s.execute("create trigger tr after insert on t " + |
| "insert into trackCreds values (current_user, current_role)"); |
| |
| Connection c = openUserConnection("DonaldDuck"); |
| Statement cStmt = c.createStatement(); |
| setRole(c, "h"); |
| cStmt.executeUpdate("insert into test_dbo.t values 1"); |
| |
| ResultSet rs = s.executeQuery("select * from trackCreds"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"DONALDDUCK", "\"H\""}}); |
| rs.close(); |
| setRole(c, "none"); |
| cStmt.close(); |
| |
| // From within a CHECK constraint, that we get an error |
| try { |
| s.execute("create table strange(role varchar(30) " + |
| "check (role = current_role))"); |
| fail("current_role inside a check constraint should be denied"); |
| } catch (SQLException e) { |
| assertSQLState(UNRELIABLE, e); |
| } |
| |
| // From within a function, called from a CHECK constraint |
| // executed as a substatement as part of ALTER TABLE. |
| // In this case, the session context stack contains two elements |
| // referenced from the three activations thus: |
| // |
| // top level "alter table" act. -> top level session context |
| // substatement (check constraint act.) -> top level session context |
| // nested connnection in getCurrentRole, |
| // "values current_role" act. -> pushed session context |
| // |
| // Before DERBY-3897 the call to s1.getCurrentRole would yield null |
| // because the pushed session context for getCurrentRole would inherit |
| // a wrong (newly created) session context from the CHECK constraint |
| // substatement's activation. After DERBY-3897, the substatement |
| // correctly inherits the session context of "alter table"s |
| // activation, so the pushed session context for getCurrentRole will |
| // be correct, too. |
| // |
| s.execute("create table strange(i int)"); |
| s.execute("insert into strange values null"); |
| s.execute("alter table strange " + |
| "add constraint s check (s1.getCurrentRole() = '\"A1\"')"); |
| |
| s.execute("revoke h from DonaldDuck"); |
| s.execute("revoke insert on t from DonaldDuck"); |
| setRole(dboConn, "none"); |
| s.execute("drop table t"); |
| s.execute("drop table strange"); |
| s.execute("drop table trackCreds"); |
| s.close(); |
| |
| c.close(); |
| dboConn.close(); |
| } |
| |
| |
| |
| /** |
| * stored function: s1.f1 |
| */ |
| public static int s1f1() { |
| return 0; |
| } |
| |
| |
| private void assertAllforRole(int hasPrivilege, |
| Connection c, |
| String grantee) throws SQLException { |
| for (int i=0; i < grantRevokes.length; i++) { |
| doGrantRevoke(GRANT, "test_dbo", grantRevokes[i], grantee); |
| assertEverything(hasPrivilege, c, null); |
| doGrantRevoke(REVOKE, "test_dbo", grantRevokes[i], grantee); |
| |
| // check that when priv is revoked we no longer have it |
| if (hasPrivilege == VIAROLE) { |
| assertEverything(NOPRIV, c, null); |
| } |
| |
| } |
| } |
| |
| |
| private void assertEverything(int hasPrivilege, |
| String user, |
| String role) throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| assertEverything(hasPrivilege, c, role); |
| c.close(); |
| } |
| |
| |
| private void assertEverything(int hasPrivilege, |
| Connection c, |
| String role) throws SQLException { |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| String[] columns = new String[] {"c1", "c2"}; |
| String schema = "s1"; |
| String table = "t1"; |
| String function = "f1"; |
| |
| assertSelectPrivilege |
| (hasPrivilege, c, schema, table, columns); |
| assertSelectPrivilege |
| (hasPrivilege, c, schema, table, null); |
| assertInsertPrivilege |
| (hasPrivilege, c, schema, table, null); |
| assertUpdatePrivilege |
| (hasPrivilege, c, schema, table, columns); |
| assertUpdatePrivilege |
| (hasPrivilege, c, schema, table, null); |
| assertDeletePrivilege |
| (hasPrivilege, c, schema, table); |
| assertReferencesPrivilege |
| (hasPrivilege, c, schema, table, columns); |
| assertReferencesPrivilege |
| (hasPrivilege, c, schema, table, null); |
| assertTriggerPrivilege |
| (hasPrivilege, c, schema, table); |
| assertExecutePrivilege(hasPrivilege, c, schema, function); |
| } |
| |
| |
| /** |
| * Assert that a user has execute privilege on a given function |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param function the name of the function to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertExecutePrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String function) throws SQLException { |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertExecutePrivilege(hasPrivilege, c, schema, function); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has execute privilege on a given function |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param function the name of the function to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertExecutePrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String function) throws SQLException { |
| Statement stm = c.createStatement(); |
| |
| try { |
| ResultSet rs = |
| stm.executeQuery("values " + schema + "." + function + "()"); |
| |
| rs.next(); |
| rs.close(); |
| stm.close(); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no EXECUTE privilege on function. " + |
| formatArgs(c, schema, function)); |
| } |
| } catch (SQLException e) { |
| if (stm != null) { |
| stm.close(); |
| } |
| |
| if (hasPrivilege == NOPRIV) |
| assertSQLState(NOEXECUTEPERMISSION, e); |
| else { |
| fail("Unexpected lack of execute privilege. " + |
| formatArgs(c, schema, function), e); |
| } |
| } |
| } |
| |
| |
| /** |
| * Assert that a user has trigger privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertTriggerPrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table) throws SQLException { |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertTriggerPrivilege(hasPrivilege, c, schema, table); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has trigger execute privilege on a given table / |
| * column set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertTriggerPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table) throws SQLException { |
| Statement s = c.createStatement(); |
| String triggerName = table + "Trigger"; |
| |
| try { |
| int i = s.executeUpdate |
| ("create trigger " + triggerName + " after insert on " + |
| schema + "." + table + " for each row values 1"); |
| |
| if (hasPrivilege != NOPRIV) { |
| assertEquals(0, i); |
| } |
| |
| s.execute("drop trigger " + triggerName); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no TRIGGER privilege on table. " + |
| formatArgs(c, schema, table)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(NOTABLEPERMISSION, e); |
| } else { |
| fail("Unexpected lack of trigger privilege. " + |
| formatArgs(c, schema, table), e); |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "TRIGGER", schema, table, null); |
| |
| } |
| |
| |
| /** |
| * Assert that a user has references privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @param columns the name of the columns to check, or null |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertReferencesPrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertReferencesPrivilege(hasPrivilege, c, schema, table, columns); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has references privilege on a given table / column |
| * set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertReferencesPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Statement s = c.createStatement(); |
| |
| columns = ((columns == null) |
| ? getAllColumns(schema, table) |
| : columns); |
| |
| for (int i = 0; i < columns.length; i++) { |
| try { |
| s.execute("create table referencestest (c1 int" + |
| " references " + schema + "." + |
| table + "(" + columns[i] + "))" ); |
| |
| s.execute("drop table referencestest"); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("Unexpected references privilege. " + |
| formatArgs(c, schema, table, |
| new String[]{columns[i]})); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(NOCOLUMNPERMISSION, e); |
| } else { |
| fail("Unexpected lack of references privilege. " + |
| formatArgs(c, schema, table, |
| new String[]{columns[i]}), |
| e); |
| } |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "REFERENCES", schema, table, columns); |
| } |
| |
| |
| /** |
| * Assert that a user has update privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @param columns the name of the columns to check, or null |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertUpdatePrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertUpdatePrivilege(hasPrivilege, c, schema, table, columns); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has update privilege on a given table / column set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertUpdatePrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| String[] checkColumns = |
| (columns == null) ? getAllColumns(schema, table) : columns; |
| |
| Statement s = c.createStatement(); |
| int columnCount = 0; |
| boolean checkCount; |
| |
| for (int i = 0; i < checkColumns.length; i++) { |
| checkCount = false; |
| |
| try { |
| // Try to get count of rows to verify update rows. We may not |
| // have select privilege on the column, in which case, we |
| // simply don't verify the count. |
| try { |
| ResultSet countRS = |
| s.executeQuery("select count(" + checkColumns[i] + |
| ") from " + schema + "." + table); |
| |
| if (!countRS.next()) { |
| fail("Could not get count on " + checkColumns[i] + |
| " to verify update"); |
| } |
| |
| columnCount = countRS.getInt(1); |
| checkCount = true; |
| } catch (SQLException e) { |
| assertSQLState(NOCOLUMNPERMISSION, e); |
| } |
| |
| int actualCount = |
| s.executeUpdate("update " + schema + "." + table + |
| " set " + checkColumns[i] + "= 0"); |
| |
| if (hasPrivilege != NOPRIV && checkCount) { |
| // update count should equal select count |
| assertEquals(columnCount, actualCount); |
| } |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no UPDATE privilege on " + |
| formatArgs(c, schema, table, |
| new String[]{checkColumns[i]})); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(NOCOLUMNPERMISSION, e); |
| } else { |
| fail("Unexpected lack of privilege to update. " + |
| formatArgs(c, schema, table, |
| new String[]{checkColumns[i]})); |
| } |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "UPDATE", schema, table, columns); |
| } |
| |
| |
| /** |
| * Assert that a user has insert privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @param columns the name of the columns to check, or null |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertInsertPrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertInsertPrivilege(hasPrivilege, c, schema, table, columns); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has insert privilege on a given table / column set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertInsertPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Statement s = c.createStatement(); |
| |
| try { |
| int i = s.executeUpdate("insert into " + schema + "." + |
| table + " values (0,0,0)"); |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no INSERT privilege on table, " + |
| formatArgs(c, schema, table, columns)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(NOTABLEPERMISSION, e); |
| } else { |
| fail("Unexpected lack of insert privilege. " + |
| formatArgs(c, schema, table, columns), e); |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "INSERT", schema, table, columns); |
| } |
| |
| |
| /** |
| * Assert that a user has select privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @param columns the name of the columns to check, or null |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertSelectPrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table, |
| String[] columns) |
| throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertSelectPrivilege(hasPrivilege, c, schema, table, columns); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has select privilege on a given table / column set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertSelectPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns) throws SQLException { |
| assertSelectPrivilege(hasPrivilege, c, schema, |
| table, columns, NOCOLUMNPERMISSION); |
| assertSelectConstantPrivilege(hasPrivilege, c, schema, |
| table, NOTABLEPERMISSION); |
| assertSelectCountPrivilege(hasPrivilege, c, schema, |
| table, columns, NOTABLEPERMISSION); |
| } |
| |
| /** |
| * Assert that a user has select privilege at the table(s) level or |
| * atleast on one column from each of the tables involved in the |
| * query when running a select query which selects count(*) or |
| * count(constant) from the tables. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns used for error handling if ran into exception |
| * @param sqlState expected state if hasPrivilege == NOPRIV |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertSelectCountPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns, |
| String sqlState) throws SQLException { |
| Statement s = c.createStatement(); |
| |
| try { |
| s.execute("select count(*) from " + schema + "." + table); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no SELECT privilege on table " + |
| formatArgs(c, schema, table, columns)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(sqlState, e); |
| } else { |
| fail("Unexpected lack of select privilege. " + |
| formatArgs(c, schema, table, columns), e); |
| } |
| } |
| |
| try { |
| s.execute("select count('a') from " + schema + "." + table); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no SELECT privilege on table " + |
| formatArgs(c, schema, table, columns)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(sqlState, e); |
| } else { |
| fail("Unexpected lack of select privilege. " + |
| formatArgs(c, schema, table, columns), e); |
| } |
| } |
| |
| s.close(); |
| } |
| |
| /** |
| * Assert that a user has select privilege at the table(s) level or |
| * atleast on one column from each of the tables involved in the |
| * query when running a select query which only selects constants from |
| * the tables. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param sqlState expected state if hasPrivilege == NOPRIV |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertSelectConstantPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String sqlState) throws SQLException { |
| Statement s = c.createStatement(); |
| |
| try { |
| s.execute("select 1 from " + schema + "." + table); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no SELECT privilege on table " + |
| formatArgs(c, schema, table)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(sqlState, e); |
| } else { |
| fail("Unexpected lack of select privilege. " + |
| formatArgs(c, schema, table), e); |
| } |
| } |
| |
| s.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has select privilege on a given table / column set. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check |
| * @param sqlState expected state if hasPrivilege == NOPRIV |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertSelectPrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table, |
| String[] columns, |
| String sqlState) throws SQLException { |
| Statement s = c.createStatement(); |
| |
| try { |
| s.execute("select " + columnListAsString(columns) + |
| " from " + schema + "." + table); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no SELECT privilege on table " + |
| formatArgs(c, schema, table, columns)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(sqlState, e); |
| } else { |
| fail("Unexpected lack of select privilege. " + |
| formatArgs(c, schema, table, columns), e); |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "SELECT", schema, table, columns); |
| } |
| |
| |
| /** |
| * Check that a given view exists (select privilege assumed) or not by |
| * selecting from it. The connection user must supposed to be the owner for |
| * this to work. |
| */ |
| private void assertViewExists(boolean exists, |
| Connection c, |
| String table) throws SQLException { |
| Statement s = c.createStatement(); |
| try { |
| s.execute("select * from " + table); |
| if (!exists) { |
| fail("Table expected not to exist: " + table); |
| } |
| } catch (SQLException e) { |
| if (exists) { |
| fail("Table expected to exist: " + table, e); |
| } |
| assertSQLState(TABLENOTFOUND, e); |
| } |
| s.close(); |
| } |
| |
| |
| /** |
| * Check that a given trigger exists (select privilege assumed) or not. |
| * NOTE: It is destructive, since the test is by dropping the trigger. The |
| * connection user must supposed to be the owner for this to work. |
| */ |
| private void assertTriggerExists(boolean exists, |
| Connection c, |
| String trigger) throws SQLException { |
| Statement s = c.createStatement(); |
| try { |
| s.execute("drop trigger " + trigger); |
| if (!exists) { |
| fail("Trigger expected not to exist: " + trigger); |
| } |
| } catch (SQLException e) { |
| if (exists) { |
| fail("Trigger expected to exist: " + trigger, e); |
| } |
| assertSQLState(OBJECTNOTFOUND, e); |
| } |
| s.close(); |
| } |
| |
| |
| /** |
| * Check that a given foregin key constraint exists by the following |
| * method: We insert a value that is not present in the referenced table so |
| * the foreign key constraint will fail if the constraint is present. The |
| * connection user must be the owner for this to work. |
| */ |
| private void assertFkConstraintExists(boolean exists, |
| Connection c, |
| String table) |
| throws SQLException { |
| assertConstraintExists(exists, c, table, FKVIOLATION); |
| } |
| |
| /** |
| * Check that a given check constraint exists by the following method: We |
| * insert a value that does not satify the check constraint. The connection |
| * user must be the owner for this to work. |
| */ |
| private void assertCheckConstraintExists(boolean exists, |
| Connection c, |
| String table) |
| throws SQLException { |
| assertConstraintExists(exists, c, table, CHECKCONSTRAINTVIOLATED); |
| } |
| |
| |
| private void assertConstraintExists(boolean exists, |
| Connection c, |
| String table, |
| String sqlState) |
| throws SQLException { |
| |
| Statement s = c.createStatement(); |
| try { |
| s.execute("insert into " + table + " values (6,6,6)"); |
| s.execute("delete from " + table); |
| |
| if (exists) { |
| fail("Table expected to have a constraint: " + table); |
| } |
| } catch (SQLException e) { |
| if (!exists) { |
| fail("Table expected not to have a constraint: " + table, e); |
| } |
| assertSQLState(sqlState, e); |
| } |
| s.close(); |
| } |
| |
| |
| /** |
| * Check that a given prepared statement can be executed. |
| */ |
| private void assertPsWorks(boolean works, |
| PreparedStatement ps) throws SQLException { |
| |
| ps.getConnection().setAutoCommit(false); |
| |
| try { |
| boolean b = ps.execute(); |
| ResultSet rs = ps.getResultSet(); |
| if (rs != null) { |
| rs.next(); |
| rs.close(); |
| } |
| ps.getConnection().rollback(); |
| ps.getConnection().setAutoCommit(true); |
| |
| if (!works) { |
| fail("Prepared statement expected to fail."); |
| } |
| } catch (SQLException e) { |
| ps.getConnection().setAutoCommit(true); |
| |
| if (works) { |
| fail("Prepared statement expected to work.", e); |
| } |
| assertSQLState |
| (new String[]{NOCOLUMNPERMISSION, |
| NOEXECUTEPERMISSION, |
| NOTABLEPERMISSION}, |
| e); |
| } |
| } |
| |
| |
| /** |
| * Assert that a user has delete privilege on a given table |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param user the user to check |
| * @param role to use, or null if we do not want to set the role |
| * @param schema the schema to check |
| * @param table the name of the table to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertDeletePrivilege(int hasPrivilege, |
| String user, |
| String role, |
| String schema, |
| String table) |
| throws SQLException { |
| |
| Connection c = openUserConnection(user); |
| |
| if (role != null) { |
| setRole(c, role); |
| } |
| |
| assertDeletePrivilege(hasPrivilege, c, schema, table); |
| c.close(); |
| } |
| |
| |
| /** |
| * Assert that a user has delete privilege on a given table. |
| * |
| * @param hasPrivilege whether or not the user has the privilege |
| * @param c connection to use |
| * @param schema the schema to check |
| * @param table the table to check |
| * @throws SQLException throws all exceptions |
| */ |
| private void assertDeletePrivilege(int hasPrivilege, |
| Connection c, |
| String schema, |
| String table) throws SQLException { |
| |
| Statement s = c.createStatement(); |
| |
| try { |
| s.execute("delete from " + schema + "." + table); |
| |
| if (hasPrivilege == NOPRIV) { |
| fail("expected no DELETE privilege on table " + |
| formatArgs(c, schema, table)); |
| } |
| } catch (SQLException e) { |
| if (hasPrivilege == NOPRIV) { |
| assertSQLState(NOTABLEPERMISSION, e); |
| } else { |
| fail("Unexpected lack of delete privilege. " + |
| formatArgs(c, schema, table), e); |
| } |
| } |
| |
| s.close(); |
| |
| assertPrivilegeMetadata |
| (hasPrivilege, c, "DELETE", schema, table, null); |
| } |
| |
| /** |
| * Assert that a specific privilege exists by checking the |
| * database metadata available to a user. |
| * |
| |
| * @param hasPrivilege Is != NOPRIV if we expect the caller to have the |
| * privilege |
| * @param c user connection |
| * @param type type of privilege, e.g. SELECT, INSERT, DELETE, etc. |
| * @param schema the schema to check |
| * @param table the table to check |
| * @param columns the set of columns to check, or all columns if null |
| * @throws SQLException |
| */ |
| private void assertPrivilegeMetadata(int hasPrivilege, |
| Connection c, |
| String type, |
| String schema, |
| String table, |
| String[] columns) throws SQLException { |
| ResultSet rs; |
| Statement stm = c.createStatement(); |
| rs = stm.executeQuery("values current_user"); |
| rs.next(); |
| String user = rs.getString(1); |
| rs.close(); |
| stm.close(); |
| |
| if (isOwner(schema, user)) { |
| // NOTE: Does not work for table owner, who has no manifest entry |
| // corresponding to the privilege in SYSTABLEPERMS. |
| return; |
| } |
| |
| if (hasPrivilege == VIAROLE) { |
| // No DatabaseMetaData for roles. We could of course check |
| // SYS.SYSROLES and the privilege tables but then we would have to |
| // essentially rebuild the whole role privilege computation |
| // machinery in this test.. ;) |
| return; |
| } |
| |
| DatabaseMetaData dm = c.getMetaData(); |
| |
| rs = dm.getTablePrivileges |
| (null, JDBC.identifierToCNF(schema), JDBC.identifierToCNF(table)); |
| |
| boolean found = false; |
| |
| // check getTablePrivileges |
| if (columns == null) { |
| while (rs.next()) |
| { |
| // Also verify that grantor and is_grantable can be |
| // obtained Derby doesn't currently support the for grant |
| // option, the grantor is always the object owner - in this |
| // test, test_dbo, and is_grantable is always 'NO'. |
| assertEquals(JDBC.identifierToCNF("test_dbo"), |
| rs.getString(4)); |
| assertEquals("NO", rs.getString(7)); |
| |
| if (rs.getString(6).equals(type)) { |
| String privUser = rs.getString(5); |
| |
| if (privUser.equals(user) || |
| privUser.equals( |
| JDBC.identifierToCNF("public"))) { |
| found = true; |
| } |
| } |
| } |
| assertEquals(hasPrivilege == VIAUSER, found); |
| rs.close(); |
| } |
| |
| // check getColumnPrivileges() |
| ResultSet cp = null; |
| if (columns == null) { |
| /* |
| * Derby does not record table level privileges in SYSCOLPERMS, so |
| * the following does not work. If it is ever changed so that |
| * getColumnPrivileges returns proper results for table level |
| * privileges, this(*) can be reenabled. |
| * |
| * (*) See GrantRevokeTest. |
| */ |
| } else { |
| // or, check that all given columns have privilege or not as the |
| // case may be |
| int noFound = 0; |
| |
| for (int i = 0; i < columns.length; i++) { |
| cp = dm.getColumnPrivileges(null, |
| JDBC.identifierToCNF(schema), |
| JDBC.identifierToCNF(table), |
| JDBC.identifierToCNF(columns[i])); |
| |
| while (cp.next()) { |
| // also verify that grantor and is_grantable are valid |
| // Derby doesn't currently support for grant, so |
| // grantor is always the object owner - in this test, |
| // test_dbo, and getColumnPrivileges casts 'NO' for |
| // is_grantable for supported column-related privileges |
| assertEquals(JDBC.identifierToCNF("test_dbo"), |
| cp.getString(5)); |
| assertEquals("NO", cp.getString(8)); |
| |
| if (cp.getString(7).equals(type)) { |
| String privUser = cp.getString(6); |
| |
| if (privUser.equals(user) || |
| privUser.equals( |
| JDBC.identifierToCNF("public"))) { |
| noFound++; |
| } |
| } |
| } |
| } |
| |
| if (hasPrivilege == VIAUSER) { |
| assertEquals(columns.length, noFound); |
| } else { |
| assertEquals(0, noFound); |
| } |
| } |
| |
| if (cp != null) { |
| cp.close(); |
| } |
| } |
| |
| private boolean isOwner(String schema, String user) throws SQLException { |
| Connection c = getConnection(); |
| Statement stm = c.createStatement(); |
| ResultSet rs = stm.executeQuery |
| ("select schemaname, authorizationid from sys.sysschemas " + |
| "where schemaname='" + JDBC.identifierToCNF(schema) + "'"); |
| rs.next(); |
| boolean result = rs.getString(2).equals(JDBC.identifierToCNF(user)); |
| rs.close(); |
| stm.close(); |
| return result; |
| } |
| |
| |
| /** |
| * Get all the columns in a given schema / table |
| * |
| * @return an array of Strings with the column names |
| * @throws SQLException |
| */ |
| private String[] getAllColumns(String schema, String table) |
| throws SQLException |
| { |
| Connection c = getConnection(); |
| DatabaseMetaData dbmd = c.getMetaData(); |
| ArrayList<String> columnList = new ArrayList<String>(); |
| ResultSet rs = |
| dbmd.getColumns( (String) null, schema, table, (String) null); |
| |
| while(rs.next()) |
| { |
| columnList.add(rs.getString(4)); |
| } |
| |
| return columnList.toArray(new String[columnList.size()]); |
| } |
| |
| /** |
| * Return the given String array as a comma separated String |
| * |
| * @param columns an array of columns to format |
| * @return a comma separated String of the column names |
| */ |
| private static String columnListAsString(String[] columns) { |
| if (columns == null) { |
| return "*"; |
| } |
| |
| StringBuffer sb = new StringBuffer(columns[0]); |
| |
| for (int i = 1; i < columns.length; i++ ) { |
| sb.append("," + columns[i]); |
| } |
| |
| return sb.toString(); |
| } |
| |
| /** |
| * Format the table arguments used by the various assert* methods for |
| * printing. |
| */ |
| private static String formatArgs(Connection c, |
| String schema, |
| String table, |
| String[] columns) throws SQLException { |
| |
| return |
| formatArgs(c, schema, table) + |
| "(" + columnListAsString(columns) + ")"; |
| } |
| |
| |
| /** |
| * Format the dbObject arguments used by the various assert* methods for |
| * printing. |
| */ |
| private static String formatArgs(Connection c, |
| String schema, |
| String dbObject) throws SQLException { |
| |
| ResultSet rs; |
| Statement stm = c.createStatement(); |
| rs = stm.executeQuery("values current_user"); |
| rs.next(); |
| String user = rs.getString(1); |
| |
| rs = c.createStatement().executeQuery("values current_role"); |
| rs.next(); |
| String role = rs.getString(1); |
| rs.close(); |
| stm.close(); |
| |
| return |
| "User: " + user + |
| (role == null ? "" : " Role: " + role) + |
| " Object: " + |
| schema + "." + |
| dbObject; |
| } |
| |
| /** |
| * Set the given role for the current session. |
| */ |
| private void setRole(Connection c, String role) throws SQLException { |
| PreparedStatement ps; |
| |
| if (role.toUpperCase().equals("NONE")) { |
| ps = c.prepareStatement("set role none"); |
| } else { |
| ps = c.prepareStatement("set role ?"); |
| ps.setString(1, role); |
| } |
| |
| ps.execute(); |
| ps.close(); |
| } |
| |
| /** |
| * Perform a bulk grant or revoke action for grantee |
| */ |
| private void doGrantRevoke(int action, |
| String grantor, |
| String[] actionStrings, |
| String grantee, |
| String[] warningExpected) |
| throws SQLException { |
| Connection c = openUserConnection(grantor); |
| Statement s = c.createStatement(); |
| |
| for (int i=0; i < actionStrings.length; i++) { |
| s.execute( |
| (action == GRANT ? "grant " : "revoke ") + |
| actionStrings[i] + |
| (action == GRANT ? " to " : " from ") + |
| grantee + |
| (action == REVOKE && actionStrings[i].startsWith |
| ("execute") ? " restrict" : "")); |
| |
| if (warningExpected[i] != null) { |
| assertSQLState(warningExpected[i], s.getWarnings()); |
| } |
| } |
| |
| s.close(); |
| c.close(); |
| } |
| |
| |
| /** |
| * Perform a bulk grant or revoke action for grantee |
| */ |
| private void doGrantRevoke(int action, |
| String grantor, |
| String[] actionStrings, |
| String grantee) |
| throws SQLException { |
| String[] warns = new String[actionStrings.length]; |
| doGrantRevoke(action, grantor, actionStrings, grantee, warns); |
| } |
| |
| /** |
| * Perform a bulk grant or revoke action for grantee |
| */ |
| private void doGrantRevoke(int action, |
| String grantor, |
| String actionString, |
| String grantee, |
| String warningExpected) throws SQLException { |
| doGrantRevoke(action, grantor, new String[] {actionString}, grantee, |
| new String[]{warningExpected}); |
| } |
| |
| /** |
| * Perform a bulk grant or revoke action for grantee |
| */ |
| private void doGrantRevoke(int action, |
| String grantor, |
| String actionString, |
| String grantee) throws SQLException { |
| doGrantRevoke(action, grantor, new String[] {actionString}, grantee); |
| } |
| |
| |
| private String CNFUser2user(String CNFUser) { |
| for (int i = 0; i < users.length; i++) { |
| if (JDBC.identifierToCNF(users[i]).equals(CNFUser)) { |
| return users[i]; |
| } |
| } |
| fail("test error"); |
| return null; |
| } |
| |
| private void assertSQLState(String[] ok_states, SQLException e) { |
| String state = e.getSQLState(); |
| boolean found = false; |
| |
| for (int i = 0; i < ok_states.length; i++) { |
| if (ok_states[i].equals(state)) { |
| found = true; |
| } |
| } |
| |
| if (!found) { |
| StringBuffer b = new StringBuffer(); |
| b.append("Exception "); |
| b.append(state); |
| b.append(" found, one of "); |
| for (int i = 0; i < ok_states.length; i++) { |
| b.append(ok_states[i]); |
| if (i != ok_states.length - 1) { |
| b.append('|'); |
| } |
| } |
| b.append(" expected"); |
| fail(b.toString()); |
| } |
| } |
| |
| public static void calledNested() |
| throws SQLException |
| { |
| Connection c = null; |
| |
| try { |
| c = DriverManager.getConnection("jdbc:default:connection"); |
| Statement cStmt = c.createStatement(); |
| |
| // CREATE TABLE |
| cStmt.executeUpdate |
| ("create table t(role varchar(128) default current_role)"); |
| cStmt.executeUpdate("insert into t values default"); |
| ResultSet rs = cStmt.executeQuery("select * from t"); |
| JDBC.assertSingleValueResultSet(rs, "\"H\""); |
| rs.close(); |
| cStmt.executeUpdate("drop table t"); |
| |
| // ALTER TABLE |
| cStmt.executeUpdate("create table t(i int)"); |
| cStmt.executeUpdate("insert into t values 1"); |
| cStmt.executeUpdate |
| ("alter table t " + |
| "add column role varchar(10) default current_role"); |
| rs = cStmt.executeQuery("select * from t"); |
| JDBC.assertFullResultSet(rs, new String[][]{{"1", "\"H\""}}); |
| rs.close(); |
| cStmt.executeUpdate("drop table t"); |
| cStmt.close(); |
| } finally { |
| if (c != null) { |
| try { |
| c.close(); |
| } catch (Exception e) { |
| } |
| } |
| } |
| } |
| |
| |
| public static String getCurrentRole() |
| throws SQLException |
| { |
| Connection c = null; |
| |
| try { |
| c = DriverManager.getConnection("jdbc:default:connection"); |
| Statement cStmt = c.createStatement(); |
| |
| ResultSet rs = cStmt.executeQuery("values current_role"); |
| rs.next(); |
| String result = rs.getString(1); |
| rs.close(); |
| cStmt.close(); |
| return result; |
| } finally { |
| if (c != null) { |
| try { |
| c.close(); |
| } catch (Exception e) { |
| } |
| } |
| } |
| } |
| } |
| |