0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.grantRevoke
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.lang;
0023:
0024: import org.apache.derby.tools.ij;
0025: import org.apache.derbyTesting.functionTests.util.TestUtil;
0026: import org.apache.derby.tools.JDBCDisplayUtil;
0027: import org.apache.derby.iapi.services.io.FormatableBitSet;
0028:
0029: import java.sql.*;
0030:
0031: import java.util.ArrayList;
0032: import java.util.HashMap;
0033:
0034: public class grantRevoke {
0035: private static final User[] users = {
0036: new User("DAN", "MakeItFaster"),
0037: new User("KREG", "visualWhat?"),
0038: new User("JEFF", "HomeRun61"),
0039: new User("AMES", "AnyVolunteer?"),
0040: new User("JERRY", "SacreBleu"),
0041: new User("HOWARDR", "IamBetterAtTennis"),
0042: new User("FRANCOIS", "paceesalute"),
0043: new User("JAMIE", "MrNamePlates") };
0044: private static final User owner = new User("OWNER", "BigCheese");
0045: private static final User publicUser = new User("PUBLIC", null);
0046: private DatabaseMetaData dbmd;
0047: private static boolean routineCalled = false;
0048: private int errorCount = 0;
0049:
0050: public static void main(String[] args) {
0051: grantRevoke tester = new grantRevoke();
0052: tester.doIt(args);
0053: }
0054:
0055: private void doIt(String[] args) {
0056: try {
0057: // use the ij utility to read the property file and
0058: // make the initial connection.
0059: ij.getPropertyArg(args);
0060:
0061: // ij.password set in the _app.properties file gets overwritten by test harness!!!
0062: System.setProperty("ij.password", "BigCheese");
0063: owner.setConnection(ij.startJBMS());
0064: dbmd = owner.getConnection().getMetaData();
0065:
0066: runGrantTests();
0067: // We can't test much of REVOKE unless GRANT works
0068: if (errorCount == 0)
0069: runRevokeTests();
0070:
0071: System.out.println("Error cases.");
0072: testErrors(stdErrorCases);
0073: } catch (SQLException sqle) {
0074: unexpectedException(null, sqle);
0075: } catch (Throwable t) {
0076: errorCount++;
0077: t.printStackTrace(System.out);
0078: }
0079: if (errorCount == 0)
0080: System.out.println("PASSED.");
0081: else
0082: System.out.println("FAILED. " + errorCount
0083: + ((errorCount > 1) ? " errors" : " error"));
0084: System.exit(0);
0085: } // end of doIt
0086:
0087: private void runGrantTests() throws SQLException {
0088: setup(grantTestSetupSQL);
0089:
0090: // Test simple grant
0091: testOneStatement("Grant", "grant select on s1.t1 to "
0092: + users[0].name, new PrivCheck[] {
0093: new SelectPrivCheck(true, false, users[0], "S1", "T1",
0094: null),
0095: new SelectPrivCheck(false, false, users[1], "S1", "T1",
0096: null) }, "simple grant");
0097: // all privileges, default schema, multiple users
0098: owner.stmt.executeUpdate("set schema s2");
0099: testOneStatement("Grant", "grant all privileges on t1 to "
0100: + users[1].name + "," + users[2].name,
0101: new PrivCheck[] {
0102: new SelectPrivCheck(true, false, users[1],
0103: "S2", "T1", null),
0104: new DeletePrivCheck(true, false, users[1],
0105: "S2", "T1"),
0106: new InsertPrivCheck(true, false, users[1],
0107: "S2", "T1"),
0108: new UpdatePrivCheck(true, false, users[1],
0109: "S2", "T1", null),
0110: new ReferencesPrivCheck(true, false, users[1],
0111: "S2", "T1", null),
0112: new TriggerPrivCheck(true, false, users[1],
0113: "S2", "T1"),
0114:
0115: new SelectPrivCheck(true, false, users[2],
0116: "S2", "T1", null),
0117: new DeletePrivCheck(true, false, users[2],
0118: "S2", "T1"),
0119: new InsertPrivCheck(true, false, users[2],
0120: "S2", "T1"),
0121: new UpdatePrivCheck(true, false, users[2],
0122: "S2", "T1", null),
0123: new ReferencesPrivCheck(true, false, users[2],
0124: "S2", "T1", null),
0125: new TriggerPrivCheck(true, false, users[2],
0126: "S2", "T1"),
0127:
0128: new SelectPrivCheck(false, false, users[0],
0129: "S2", "T1", null),
0130: new DeletePrivCheck(false, false, users[0],
0131: "S2", "T1"),
0132: new InsertPrivCheck(false, false, users[0],
0133: "S2", "T1"),
0134: new UpdatePrivCheck(false, false, users[0],
0135: "S2", "T1", null),
0136: new ReferencesPrivCheck(false, false, users[0],
0137: "S2", "T1", null),
0138: new TriggerPrivCheck(false, false, users[0],
0139: "S2", "T1"),
0140:
0141: new SelectPrivCheck(false, false, users[1],
0142: "S1", "T1", null),
0143: new SelectPrivCheck(false, false, users[1],
0144: "S2", "T2", null), },
0145: "all privileges, multiple users (2)");
0146: // Column privileges
0147: testOneStatement(
0148: "Grant",
0149: "grant select(c1),update(c3,c2),references(c3,c1,c2) on s1.t1 to "
0150: + users[3].name,
0151: new PrivCheck[] {
0152: new SelectPrivCheck(true, false, users[3],
0153: "S1", "T1", new String[] { "C1" }),
0154: new SelectPrivCheck(false, false, users[3],
0155: "S1", "T1", new String[] { "C2" }),
0156: new SelectPrivCheck(false, false, users[3],
0157: "S1", "T1", new String[] { "C3" }),
0158: new SelectPrivCheck(false, false, users[3],
0159: "S1", "T1", null),
0160: new UpdatePrivCheck(true, false, users[3],
0161: "S1", "T1", new String[] { "C2", "C3" }),
0162: new UpdatePrivCheck(false, false, users[3],
0163: "S1", "T1", new String[] { "C1" }),
0164: new ReferencesPrivCheck(true, false, users[3],
0165: "S1", "T1", new String[] { "C1", "C2",
0166: "C3" }),
0167: new ReferencesPrivCheck(false, false, users[3],
0168: "S1", "T1", null) },
0169: "Column privileges");
0170: // Execute on function when there is a procedure with the same name
0171: testOneStatement("Grant", "grant execute on function s1.f1 to "
0172: + users[0].name, new PrivCheck[] {
0173: new ExecutePrivCheck(true, false, users[0], "S1", "F1",
0174: true),
0175: new ExecutePrivCheck(false, false, users[0], "S1",
0176: "F1", false),
0177: new ExecutePrivCheck(false, false, users[1], "S1",
0178: "F1", true), },
0179: "execute on function with like named procedure");
0180: // Execute on procedure
0181: testOneStatement("Grant",
0182: "grant execute on procedure s1.p1 to " + users[0].name,
0183: new PrivCheck[] {
0184: new ExecutePrivCheck(true, false, users[0],
0185: "S1", "P1", false),
0186: new ExecutePrivCheck(false, false, users[1],
0187: "S1", "P1", false), },
0188: "execute on procedure");
0189:
0190: // PUBLIC
0191: testOneStatement(
0192: "Grant",
0193: "grant select, references(c1) on table s2.t2 to public",
0194: new PrivCheck[] {
0195: new SelectPrivCheck(true, true, publicUser,
0196: "S2", "T2", null),
0197: new SelectPrivCheck(false, true, users[1],
0198: "S2", "T2", null),
0199: new SelectPrivCheck(false, false, publicUser,
0200: "S2", "NOPERMS", null),
0201: new UpdatePrivCheck(false, false, publicUser,
0202: "S2", "T2", null),
0203: new ReferencesPrivCheck(true, true, publicUser,
0204: "S2", "T2", new String[] { "C1" }),
0205: new ReferencesPrivCheck(false, false,
0206: publicUser, "S2", "T2", null) },
0207: "PUBLIC table privileges");
0208: testOneStatement("Grant",
0209: "grant execute on procedure s1.p1 to Public",
0210: new PrivCheck[] {
0211: new ExecutePrivCheck(true, true, publicUser,
0212: "S1", "P1", false),
0213: // user0 should still have his own execute privilege
0214: new ExecutePrivCheck(true, true, users[0],
0215: "S1", "P1", false),
0216: // user1 should not have an individual execute privilege
0217: new ExecutePrivCheck(false, true, users[1],
0218: "S1", "P1", false) },
0219: "PUBLIC routine privileges");
0220:
0221: testGrantRollbackAndCommit();
0222:
0223: System.out.println("Test metadata supports methods.");
0224: if (dbmd.supportsCatalogsInPrivilegeDefinitions())
0225: reportFailure("DatabaseMetaData.supportsCatalogsInPrivilegeDefinitions returned true.");
0226: if (!dbmd.supportsSchemasInPrivilegeDefinitions())
0227: reportFailure("DatabaseMetaData.supportsSchemasInPrivilegeDefinitions returned false.");
0228: } // end of runGrantTests
0229:
0230: private void testOneStatement(String stmtName, String sql,
0231: PrivCheck[] checks, String testLabel) {
0232: testOneStatement(stmtName, sql, checks, true, testLabel);
0233: }
0234:
0235: private void testOneStatement(String stmtName, String sql,
0236: PrivCheck[] checks, boolean runStatements, String testLabel) {
0237: System.out.println(stmtName + " test: " + testLabel);
0238: try {
0239: owner.stmt.executeUpdate(sql);
0240: runChecks(checks, runStatements, false, testLabel);
0241: } catch (SQLException sqle) {
0242: unexpectedException(testLabel, sqle);
0243: }
0244: } // end of testOneStatement
0245:
0246: private void setup(String[] setupSQL) throws SQLException {
0247: boolean autoCommit = owner.getConnection().getAutoCommit();
0248: owner.getConnection().setAutoCommit(false);
0249: for (int i = 0; i < setupSQL.length; i++)
0250: owner.stmt.executeUpdate(setupSQL[i]);
0251: owner.getConnection().commit();
0252: owner.getConnection().setAutoCommit(autoCommit);
0253: } // end of setup
0254:
0255: private void testGrantRollbackAndCommit() {
0256: System.out.println("Test grant rollback and commit");
0257: PrivCheck[] preExistingPrivChecks = new PrivCheck[] {
0258: new SelectPrivCheck(true, true, publicUser, "S2", "T2",
0259: null),
0260: new UpdatePrivCheck(false, false, publicUser, "S2",
0261: "T2", null)
0262:
0263: };
0264: PrivCheck[] tableChecks1 = new PrivCheck[] {
0265: new SelectPrivCheck(true, false, users[0], "S2", "T3",
0266: new String[] { "C2" }),
0267: new DeletePrivCheck(true, false, users[0], "S2", "T3") };
0268: PrivCheck[] tableChecks2 = new PrivCheck[] { new TriggerPrivCheck(
0269: true, true, publicUser, "S2", "T2") };
0270: PrivCheck[] routineChecks = new PrivCheck[] { new ExecutePrivCheck(
0271: true, false, users[0], "S2", "F1", true) };
0272: PrivCheck[] noChecks = new PrivCheck[0];
0273:
0274: try {
0275: runChecks(preExistingPrivChecks, false,
0276: "transaction test pre-existing table privileges");
0277: owner.getConnection().setAutoCommit(false);
0278: for (int i = 0; i < 2; i++) {
0279: // test rollback on i == 0, commit on i == 1
0280: // Add a new row in the SYSTABLEPERMS table
0281: testOneStatement("Grant",
0282: "grant select(c2), delete on s2.t3 to "
0283: + users[0].name, tableChecks1, false,
0284: "table privileges in transaction");
0285: // Update an existing row in the SYSTABLEPERMS table
0286: testOneStatement("Grant",
0287: "grant trigger on s2.t2 to public",
0288: tableChecks2, false,
0289: "table privileges in transaction");
0290: testOneStatement("Grant",
0291: "grant execute on function s2.f1 to "
0292: + users[0].name, routineChecks, false,
0293: "routine privileges in transaction");
0294: if (i == 0)
0295: owner.getConnection().rollback();
0296: else
0297: owner.getConnection().commit();
0298: runChecks(tableChecks1, i == 0,
0299: ((i == 0) ? "rolled back" : "committed")
0300: + " table privileges");
0301: runChecks(tableChecks2, i == 0,
0302: ((i == 0) ? "rolled back" : "committed")
0303: + " table privileges");
0304: runChecks(routineChecks, i == 0,
0305: ((i == 0) ? "rolled back" : "committed")
0306: + " routine privileges");
0307: runChecks(preExistingPrivChecks, false,
0308: "transaction test pre-existing table privileges");
0309: }
0310: } catch (SQLException sqle) {
0311: unexpectedException("rollback and commit test", sqle);
0312: }
0313: } // end of testGrantRollbackAndCommit
0314:
0315: private static final String[] grantTestSetupSQL = {
0316: "create schema s1",
0317: "create schema s2",
0318: "create table s1.t1(c1 int, c2 int, c3 int)",
0319: "create table s2.t1(c1 int, c2 int, c3 int)",
0320: "create table s2.t2(c1 int, c2 int, c3 int)",
0321: "create table s2.t3(c1 int, c2 int, c3 int)",
0322: "create table s2.noPerms(c1 int, c2 int, c3 int)",
0323: "create function s1.f1() returns int"
0324: + " language java parameter style java"
0325: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1'"
0326: + " no sql called on null input",
0327: "create function s2.f1() returns int"
0328: +
0329: // RESOLVE Derby does not implement SPECIFIC names
0330: // " specific s2.s2sp1" +
0331: " language java parameter style java"
0332: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1a'"
0333: + " no sql called on null input",
0334: /* RESOLVE Derby doesn't seem to support function overloading. It doesn't allow us to create two
0335: * functions with the same name but different signatures. (Though the StaticMethodCallNode.bindExpression
0336: * method does have code to handle overloaded methods). So we cannot throughly test
0337: * grant/revoke on overloaded procedures.
0338: */
0339:
0340: // "create function s2.f1( p1 char(8)) returns int" +
0341: // " language java parameter style java" +
0342: // " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1b'" +
0343: // " no sql called on null input",
0344: // "create function s2.f1( char(8), char(8)) returns int" +
0345: // " language java parameter style java" +
0346: // " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1c'" +
0347: // " no sql called on null input",
0348: // "create function s2.f1( int) returns int" +
0349: // " language java parameter style java" +
0350: // " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1d'" +
0351: // " no sql called on null input",
0352: "create function s2.f2( p1 char(8), p2 integer) returns int"
0353: + " language java parameter style java"
0354: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F2'"
0355: + " no sql called on null input",
0356:
0357: /* functions and procedures are supposed to have separate name spaces. Make sure that this does
0358: * not confuse grant/revoke.
0359: */
0360: "create procedure s1.f1( )"
0361: + " language java parameter style java"
0362: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1P'"
0363: + " no sql called on null input",
0364: "create procedure s1.p1( )"
0365: + " language java parameter style java"
0366: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'"
0367: + " no sql called on null input" };
0368:
0369: public static int s1F1() {
0370: routineCalled = true;
0371: return 1;
0372: }
0373:
0374: public static int s2F1a() {
0375: routineCalled = true;
0376: return 1;
0377: }
0378:
0379: public static int s2F1b(String s) {
0380: routineCalled = true;
0381: return 1;
0382: }
0383:
0384: public static int s2F1c(String s1, String s2) {
0385: routineCalled = true;
0386: return 1;
0387: }
0388:
0389: public static int s2F1d(int i) {
0390: routineCalled = true;
0391: return 1;
0392: }
0393:
0394: public static int s2F2() {
0395: routineCalled = true;
0396: return 1;
0397: }
0398:
0399: public static void s1F1P() {
0400: routineCalled = true;
0401: }
0402:
0403: public static void s1P1() {
0404: routineCalled = true;
0405: }
0406:
0407: private void runRevokeTests() throws SQLException {
0408: setup(revokeTestSetupSQL);
0409: owner.getConnection().setAutoCommit(true);
0410:
0411: // Revoke when there are no permissions
0412: PrivCheck[] privCheck1 = {
0413: new SelectPrivCheck(false, false, users[0], "R1", "T1",
0414: null),
0415: new SelectPrivCheck(false, false, users[0], "R1", "T1",
0416: new String[] { "C2" }),
0417: new UpdatePrivCheck(false, false, users[1], "R1", "T1",
0418: new String[] { "C1", "C3" }),
0419: new ExecutePrivCheck(false, false, users[0], "R1",
0420: "P1", false) };
0421: owner.stmt.executeUpdate("set schema r1");
0422: runChecks(privCheck1, false, "Initial revoke test conditions");
0423: testOneStatement("Revoke", "revoke all Privileges on t1 from "
0424: + users[0].name, privCheck1, "all with no permissions");
0425: testOneStatement("Revoke",
0426: "revoke execute on procedure r1.p1 from "
0427: + users[0].name + " restrict", privCheck1,
0428: "execute with no permissions");
0429: testOneStatement("Revoke",
0430: "revoke select(c2), update(c1,c3) on table t1 from "
0431: + users[1].name, privCheck1,
0432: "column with no permissions");
0433:
0434: // Revoke single table permissions, single user
0435: owner.stmt.executeUpdate("grant all privileges on r2.t1 to "
0436: + users[0].name);
0437: owner.stmt.executeUpdate("grant update(c3) on r2.t1 to "
0438: + users[0].name);
0439: testOneStatement("Revoke", "revoke update on r2.t1 from "
0440: + users[0].name,
0441: new PrivCheck[] {
0442: new SelectPrivCheck(true, false, users[0],
0443: "R2", "T1", null),
0444: new UpdatePrivCheck(false, false, users[0],
0445: "R2", "T1", null),
0446: new UpdatePrivCheck(false, false, users[0],
0447: "R2", "T1", new String[] { "C3" }),
0448: new InsertPrivCheck(true, false, users[0],
0449: "R2", "T1"),
0450: new DeletePrivCheck(true, false, users[0],
0451: "R2", "T1"),
0452: new ReferencesPrivCheck(true, false, users[0],
0453: "R2", "T1", null),
0454: new TriggerPrivCheck(true, false, users[0],
0455: "R2", "T1") },
0456: "single table privilege, one user");
0457: testOneStatement("Revoke",
0458: "revoke all privileges on r2.t1 from " + users[0].name,
0459: new PrivCheck[] {
0460: new SelectPrivCheck(false, false, users[0],
0461: "R2", "T1", null),
0462: new UpdatePrivCheck(false, false, users[0],
0463: "R2", "T1", null),
0464: new UpdatePrivCheck(false, false, users[0],
0465: "R2", "T1", new String[] { "C3" }),
0466: new InsertPrivCheck(false, false, users[0],
0467: "R2", "T1"),
0468: new DeletePrivCheck(false, false, users[0],
0469: "R2", "T1"),
0470: new ReferencesPrivCheck(false, false, users[0],
0471: "R2", "T1", null),
0472: new TriggerPrivCheck(false, false, users[0],
0473: "R2", "T1") },
0474: "single table privilege, one user");
0475:
0476: // Revoke multiple table & column permissions, multiple users some of which do not have the permission
0477: // Leave one user some permissions on the table, another no permissions
0478: owner.stmt.executeUpdate("grant select on t1 to "
0479: + users[0].name + "," + users[1].name + ","
0480: + users[2].name);
0481: owner.stmt.executeUpdate("grant update(c1,c2,c3) on t1 to "
0482: + users[0].name);
0483: owner.stmt.executeUpdate("grant update(c3) on t1 to "
0484: + users[1].name);
0485: owner.stmt.executeUpdate("grant trigger on t1 to "
0486: + users[0].name);
0487: runChecks(
0488: new PrivCheck[] {
0489: new SelectPrivCheck(true, false, users[0],
0490: "R1", "T1", null),
0491: new SelectPrivCheck(true, false, users[1],
0492: "R1", "T1", null),
0493: new SelectPrivCheck(true, false, users[2],
0494: "R1", "T1", null),
0495: new UpdatePrivCheck(true, false, users[0],
0496: "R1", "T1", new String[] { "C1", "C2",
0497: "C3" }),
0498: new UpdatePrivCheck(true, false, users[1],
0499: "R1", "T1", new String[] { "C3" }),
0500: new TriggerPrivCheck(true, false, users[0],
0501: "R1", "T1"),
0502: new TriggerPrivCheck(false, false, users[1],
0503: "R1", "T1") }, false, "setup (1)");
0504: testOneStatement(
0505: "Revoke",
0506: "revoke select, update(c2,c3) on t1 from "
0507: + users[0].name + "," + users[1].name + ","
0508: + users[2].name,
0509: new PrivCheck[] {
0510: new SelectPrivCheck(false, false, users[0],
0511: "R1", "T1", null),
0512: new SelectPrivCheck(false, false, users[1],
0513: "R1", "T1", null),
0514: new SelectPrivCheck(false, false, users[2],
0515: "R1", "T1", null),
0516: new UpdatePrivCheck(true, false, users[0],
0517: "R1", "T1", new String[] { "C1" }),
0518: new UpdatePrivCheck(false, false, users[0],
0519: "R1", "T1", new String[] { "C2", "C3" }),
0520: new UpdatePrivCheck(false, false, users[1],
0521: "R1", "T1", new String[] { "C1", "C2",
0522: "C3" }),
0523: new TriggerPrivCheck(true, false, users[0],
0524: "R1", "T1"),
0525: new TriggerPrivCheck(false, false, users[1],
0526: "R1", "T1") },
0527: "multiple table permissions, multiple users");
0528: testOneStatement("Revoke", "revoke update on r1.t1 from "
0529: + users[0].name, new PrivCheck[] {
0530: new UpdatePrivCheck(false, false, users[0], "R1", "T1",
0531: new String[] { "C1" }),
0532: new UpdatePrivCheck(false, false, users[0], "R1", "T1",
0533: null) },
0534: "table privilege implies column privileges");
0535: // Revoke all
0536: testOneStatement("Revoke",
0537: "revoke all privileges on r1.t1 from " + users[0].name,
0538: new PrivCheck[] {
0539: new UpdatePrivCheck(false, false, users[0],
0540: "R1", "T1", new String[] { "C1", "C2",
0541: "C3" }),
0542: new TriggerPrivCheck(false, false, users[0],
0543: "R1", "T1") }, "all privileges");
0544:
0545: // Revoke function permission
0546: owner.stmt.executeUpdate("grant execute on function f1 to "
0547: + users[0].name + "," + users[1].name);
0548: owner.stmt.executeUpdate("grant execute on procedure f1 to "
0549: + users[0].name);
0550: runChecks(new PrivCheck[] {
0551: new ExecutePrivCheck(true, false, users[0], "R1", "F1",
0552: true),
0553: new ExecutePrivCheck(true, false, users[1], "R1", "F1",
0554: true),
0555: new ExecutePrivCheck(true, false, users[0], "R1", "F1",
0556: false) }, false, "setup for revoke execute");
0557: testOneStatement("Revoke",
0558: "revoke execute on function f1 from " + users[0].name
0559: + " restrict", new PrivCheck[] {
0560: new ExecutePrivCheck(false, false, users[0],
0561: "R1", "F1", true),
0562: new ExecutePrivCheck(true, false, users[1],
0563: "R1", "F1", true),
0564: new ExecutePrivCheck(true, false, users[0],
0565: "R1", "F1", false) },
0566: "function execute permission");
0567:
0568: // Revoke procedure permission
0569: testOneStatement("Revoke",
0570: "revoke execute on procedure f1 from " + users[0].name
0571: + " restrict", new PrivCheck[] {
0572: new ExecutePrivCheck(false, false, users[0],
0573: "R1", "F1", true),
0574: new ExecutePrivCheck(true, false, users[1],
0575: "R1", "F1", true),
0576: new ExecutePrivCheck(false, false, users[0],
0577: "R1", "F1", false) },
0578: "function execute permission");
0579:
0580: // Revoke privileges from user when there is PUBLIC permission
0581: owner.stmt
0582: .executeUpdate("grant select, delete on r2.t1 to public");
0583: owner.stmt.executeUpdate("grant select, delete on r2.t1 to "
0584: + users[1].name + "," + users[2].name);
0585: owner.stmt
0586: .executeUpdate("grant update(c1,c3) on r2.t1 to public");
0587: owner.stmt.executeUpdate("grant update(c1,c3) on r2.t1 to "
0588: + users[1].name + "," + users[2].name);
0589: runChecks(
0590: new PrivCheck[] {
0591: new SelectPrivCheck(true, true, users[1], "R2",
0592: "T1", null),
0593: new SelectPrivCheck(true, true, users[2], "R2",
0594: "T1", null),
0595: new SelectPrivCheck(true, true, publicUser,
0596: "R2", "T1", null),
0597: new DeletePrivCheck(true, true, users[1], "R2",
0598: "T1"),
0599: new DeletePrivCheck(true, true, users[2], "R2",
0600: "T1"),
0601: new DeletePrivCheck(true, true, publicUser,
0602: "R2", "T1"),
0603: new UpdatePrivCheck(true, true, users[1], "R2",
0604: "T1", new String[] { "C1", "C3" }),
0605: new UpdatePrivCheck(true, true, users[2], "R2",
0606: "T1", new String[] { "C1", "C3" }),
0607: new UpdatePrivCheck(true, true, publicUser,
0608: "R2", "T1", new String[] { "C1", "C3" }) },
0609: false,
0610: "setup for revoke individual permissions leaving public permissions");
0611: testOneStatement(
0612: "Revoke",
0613: "revoke select, update(c1,c3), delete on table r2.t1 from "
0614: + users[1].name,
0615: new PrivCheck[] {
0616: new SelectPrivCheck(false, true, users[1],
0617: "R2", "T1", null),
0618: new SelectPrivCheck(true, true, users[2], "R2",
0619: "T1", null),
0620: new SelectPrivCheck(true, true, publicUser,
0621: "R2", "T1", null),
0622: new DeletePrivCheck(false, true, users[1],
0623: "R2", "T1"),
0624: new DeletePrivCheck(true, true, users[2], "R2",
0625: "T1"),
0626: new DeletePrivCheck(true, true, publicUser,
0627: "R2", "T1"),
0628: new UpdatePrivCheck(false, true, users[1],
0629: "R2", "T1", new String[] { "C1", "C2",
0630: "C3" }),
0631: new UpdatePrivCheck(true, true, users[2], "R2",
0632: "T1", new String[] { "C1", "C3" }),
0633: new UpdatePrivCheck(true, true, publicUser,
0634: "R2", "T1", new String[] { "C1", "C3" }) },
0635: "individual permissions leaving public permissions");
0636: testOneStatement(
0637: "Revoke",
0638: "revoke select, update(c1,c3), delete on table r2.t1 from public",
0639: new PrivCheck[] {
0640: new SelectPrivCheck(false, false, users[1],
0641: "R2", "T1", null),
0642: new SelectPrivCheck(true, false, users[2],
0643: "R2", "T1", null),
0644: new SelectPrivCheck(false, false, publicUser,
0645: "R2", "T1", null),
0646: new DeletePrivCheck(false, true, users[1],
0647: "R2", "T1"),
0648: new DeletePrivCheck(true, true, users[2], "R2",
0649: "T1"),
0650: new DeletePrivCheck(false, true, publicUser,
0651: "R2", "T1"),
0652: new UpdatePrivCheck(false, false, users[1],
0653: "R2", "T1", new String[] { "C1", "C2",
0654: "C3" }),
0655: new UpdatePrivCheck(true, false, users[2],
0656: "R2", "T1", new String[] { "C1", "C3" }),
0657: new UpdatePrivCheck(false, false, publicUser,
0658: "R2", "T1", new String[] { "C1", "C3" }) },
0659: "public permissions");
0660:
0661: owner.stmt
0662: .executeUpdate("grant execute on function r2.f1 to public");
0663: owner.stmt.executeUpdate("grant execute on function r2.f1 to "
0664: + users[2].name + "," + users[0].name);
0665: runChecks(new PrivCheck[] {
0666: new ExecutePrivCheck(true, true, users[0], "R2", "F1",
0667: true),
0668: new ExecutePrivCheck(true, true, users[2], "R2", "F1",
0669: true),
0670: new ExecutePrivCheck(true, true, publicUser, "R2",
0671: "F1", true) }, false,
0672: "setup for revoke execute leaving public permission");
0673: testOneStatement("Revoke",
0674: "revoke execute on function r2.f1 from "
0675: + users[0].name + " restrict", new PrivCheck[] {
0676: new ExecutePrivCheck(false, true, users[0],
0677: "R2", "F1", true),
0678: new ExecutePrivCheck(true, true, users[2],
0679: "R2", "F1", true),
0680: new ExecutePrivCheck(true, true, publicUser,
0681: "R2", "F1", true) },
0682: "execute leaving public permission");
0683: testOneStatement(
0684: "Revoke",
0685: "revoke execute on function r2.f1 from Public restrict",
0686: new PrivCheck[] {
0687: new ExecutePrivCheck(false, false, users[0],
0688: "R2", "F1", true),
0689: new ExecutePrivCheck(true, false, users[2],
0690: "R2", "F1", true),
0691: new ExecutePrivCheck(false, false, publicUser,
0692: "R2", "F1", true) },
0693: "execute leaving public permission");
0694:
0695: testRevokeRollback();
0696:
0697: testAbandonedView();
0698: testAbandonedTrigger();
0699: testAbandonedConstraint();
0700: } // end of runRevokeTests
0701:
0702: private void testErrors(String[][] errorCases) throws SQLException {
0703: System.out.println("Testing error cases ...");
0704: for (int i = 0; i < errorCases.length; i++) {
0705: try {
0706: System.out.println("testErrors: " + errorCases[i][0]);
0707: owner.stmt.executeUpdate(errorCases[i][0]);
0708: reportFailure("No error generated by \""
0709: + errorCases[i][0] + "\"");
0710: } catch (SQLException sqle) {
0711: if (!errorCases[i][1].equals(sqle.getSQLState()))
0712: reportFailure("Incorrect SQLState for error case "
0713: + i + ". Expected " + errorCases[i][1]
0714: + ", got " + sqle.getSQLState() + ": "
0715: + sqle.getMessage());
0716: else if (!errorCases[i][2].equals(sqle.getMessage()))
0717: reportFailure(new String[] {
0718: "Incorrect message for error case " + i
0719: + ".",
0720: " Expected " + errorCases[i][2],
0721: " Got " + sqle.getMessage() });
0722: }
0723: }
0724: } // end of testErrors
0725:
0726: private static final String[][] stdErrorCases = {
0727: { "grant xx on s1.t1 to " + users[0].name, "42X01",
0728: "Syntax error: Encountered \"xx\" at line 1, column 7." }, // invalid action
0729: { "grant between on s1.t1 to " + users[0].name, "42X01",
0730: "Syntax error: Encountered \"between\" at line 1, column 7." }, // invalid reserved word action
0731: { "grant select on schema t1 to " + users[0].name, "42X01",
0732: "Syntax error: Encountered \"schema\" at line 1, column 17." },
0733: { "grant select on decimal t1 to " + users[0].name,
0734: "42X01",
0735: "Syntax error: Encountered \"decimal\" at line 1, column 17." },
0736: { "grant select(nosuchCol) on s1.t1 to " + users[0].name,
0737: "42X14",
0738: "'NOSUCHCOL' is not a column in table or VTI 'S1.T1'." },
0739:
0740: { "grant select on nosuch.t1 to " + users[0].name, "42Y07",
0741: "Schema 'NOSUCH' does not exist" },
0742: { "grant select on s1.nosuch to " + users[0].name, "42X05",
0743: "Table/View 'S1.NOSUCH' does not exist." },
0744: {
0745: "grant execute on function nosuch.f0 to "
0746: + users[0].name, "42Y07",
0747: "Schema 'NOSUCH' does not exist" },
0748: {
0749: "grant execute on function s1.nosuch to "
0750: + users[0].name, "42Y03",
0751: "'S1.NOSUCH' is not recognized as a function or procedure." },
0752: { "grant execute on function s1.p1 to " + users[0].name,
0753: "42Y03",
0754: "'S1.P1' is not recognized as a function or procedure." },
0755: // 10
0756: {
0757: "grant execute on procedure nosuch.f0 to "
0758: + users[0].name, "42Y07",
0759: "Schema 'NOSUCH' does not exist" },
0760: {
0761: "grant execute on procedure s1.nosuch to "
0762: + users[0].name, "42Y03",
0763: "'S1.NOSUCH' is not recognized as a function or procedure." },
0764: { "grant execute on procedure s1.f2 to " + users[0].name,
0765: "42Y03",
0766: "'S1.F2' is not recognized as a function or procedure." },
0767: { "grant execute on table s1.t1 to " + users[0].name,
0768: "42X01",
0769: "Syntax error: Encountered \"table\" at line 1, column 18." },
0770: { "grant select on function s1.f1 to " + users[0].name,
0771: "42X01",
0772: "Syntax error: Encountered \"function\" at line 1, column 17." },
0773:
0774: { "grant select on procedure s1.p1 to " + users[0].name,
0775: "42X01",
0776: "Syntax error: Encountered \"procedure\" at line 1, column 17." },
0777: {
0778: "grant execute on function s1.f1 to "
0779: + users[0].name + " restrict", "42X01",
0780: "Syntax error: Encountered \"restrict\" at line 1, column 40." }, // "restrict" invalid in grant
0781: { "revoke execute on function s1.f1 from " + users[0].name,
0782: "42X01",
0783: "Syntax error: Encountered \"<EOF>\" at line 1, column 41." }, // Missing "restrict"
0784: {
0785: "revoke select on s1.t1 from " + users[0].name
0786: + " restrict", "42X01",
0787: "Syntax error: Encountered \"restrict\" at line 1, column 33." }, // "restrict" invalid in table revoke
0788: { "grant delete(c1) on s1.t1 to " + users[0].name, "42X01",
0789: "Syntax error: Encountered \"(\" at line 1, column 13." }, // Column list invalid with delete
0790: // 20
0791: { "grant trigger(c1) on s1.t1 to " + users[0].name,
0792: "42X01",
0793: "Syntax error: Encountered \"(\" at line 1, column 14." } // Column list invalid with trigger
0794: }; // end of String[][] errorCases
0795:
0796: private void testRevokeRollback() throws SQLException {
0797: owner.getConnection().setAutoCommit(false);
0798: owner.stmt
0799: .executeUpdate("grant select(c1,c2), update(c1), insert, delete on r2.t3 to "
0800: + users[0].name);
0801: owner.stmt
0802: .executeUpdate("grant select, references on r2.t3 to "
0803: + users[1].name);
0804: owner.stmt.executeUpdate("grant select on r2.t3 to "
0805: + users[2].name);
0806: owner.stmt.executeUpdate("grant execute on procedure r1.p1 to "
0807: + users[0].name);
0808: owner.getConnection().commit();
0809: runChecks(new PrivCheck[] {
0810: new SelectPrivCheck(true, false, users[0], "R2", "T3",
0811: new String[] { "C1", "C2" }),
0812: new UpdatePrivCheck(true, false, users[0], "R2", "T3",
0813: new String[] { "C1" }),
0814: new InsertPrivCheck(true, false, users[0], "R2", "T3"),
0815: new DeletePrivCheck(true, false, users[0], "R2", "T3"),
0816: new SelectPrivCheck(true, false, users[1], "R2", "T3",
0817: null),
0818: new ReferencesPrivCheck(true, false, users[1], "R2",
0819: "T3", null),
0820: new SelectPrivCheck(true, false, users[2], "R2", "T3",
0821: null),
0822: new ExecutePrivCheck(true, false, users[0], "R1", "P1",
0823: false) }, false, "setup for rollback test");
0824: for (int i = 0; i < 2; i++) {
0825: boolean doRollback = (i == 0);
0826: testOneStatement("Revoke",
0827: "revoke select(c2), update(c1), delete on r2.t3 from "
0828: + users[0].name, new PrivCheck[] {
0829: new SelectPrivCheck(true, false, users[0],
0830: "R2", "T3", new String[] { "C1" }),
0831: new SelectPrivCheck(false, false, users[0],
0832: "R2", "T3", new String[] { "C2",
0833: "C3" }),
0834: new UpdatePrivCheck(false, false, users[0],
0835: "R2", "T3", new String[] { "C1",
0836: "C2", "C3" }),
0837: new InsertPrivCheck(true, false, users[0],
0838: "R2", "T3"),
0839: new DeletePrivCheck(false, false, users[0],
0840: "R2", "T3") }, false,
0841: "table privileges (uncommitted)");
0842: testOneStatement("Revoke",
0843: "revoke references on r2.t3 from " + users[1].name,
0844: new PrivCheck[] {
0845: new SelectPrivCheck(true, false, users[1],
0846: "R2", "T3", null),
0847: new ReferencesPrivCheck(false, false,
0848: users[1], "R2", "T3", null) },
0849: false, "table privileges (uncommitted)");
0850: testOneStatement("Revoke", "revoke select on r2.t3 from "
0851: + users[2].name,
0852: new PrivCheck[] { new SelectPrivCheck(false, false,
0853: users[2], "R2", "T3", null) }, false,
0854: "table privileges (uncommitted)");
0855: testOneStatement("Revoke",
0856: "revoke execute on procedure r1.p1 from "
0857: + users[0].name + " restrict",
0858: new PrivCheck[] { new ExecutePrivCheck(false,
0859: false, users[0], "R1", "P1", false) },
0860: false, "execute privilege (uncommitted)");
0861: if (doRollback)
0862: owner.getConnection().rollback();
0863: else
0864: owner.getConnection().commit();
0865: runChecks(new PrivCheck[] {
0866: new SelectPrivCheck(doRollback, false, users[0],
0867: "R2", "T3", new String[] { "C2" }),
0868: new UpdatePrivCheck(doRollback, false, users[0],
0869: "R2", "T3", new String[] { "C1" }),
0870: new DeletePrivCheck(doRollback, false, users[0],
0871: "R2", "T3"),
0872: new ReferencesPrivCheck(doRollback, false,
0873: users[1], "R2", "T3", null),
0874: new SelectPrivCheck(doRollback, false, users[2],
0875: "R2", "T3", null),
0876: new ExecutePrivCheck(doRollback, false, users[0],
0877: "R1", "P1", false) }, false,
0878: doRollback ? "rollback of revokes"
0879: : "commit of revokes");
0880: }
0881: owner.getConnection().setAutoCommit(true);
0882: } // end of testRevokeRollback
0883:
0884: private void testAbandonedView() throws SQLException {
0885: // RESOLVE
0886: }
0887:
0888: private void testAbandonedTrigger() throws SQLException {
0889: // RESOLVE
0890: }
0891:
0892: private void testAbandonedConstraint() throws SQLException {
0893: // RESOLVE
0894: }
0895:
0896: private static final String[] revokeTestSetupSQL = {
0897: "create schema r1",
0898: "create schema r2",
0899: "create table r1.t1(c1 int, c2 int, c3 int)",
0900: "create table r2.t1(c1 int, c2 int, c3 int)",
0901: "create table r2.t2(c1 int, c2 int, c3 int)",
0902: "create table r2.t3(c1 int, c2 int, c3 int)",
0903: "create function r1.f1() returns int"
0904: + " language java parameter style java"
0905: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1F1'"
0906: + " no sql called on null input",
0907:
0908: /* functions and procedures are supposed to have separate name spaces. Make sure that this does
0909: * not confuse grant/revoke.
0910: */
0911: "create procedure r1.f1()"
0912: + " language java parameter style java"
0913: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'"
0914: + " no sql called on null input",
0915: "create function r2.f1() returns int"
0916: + " language java parameter style java"
0917: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F1a'"
0918: + " no sql called on null input",
0919: "create function r2.f2( p1 char(8), p2 integer) returns int"
0920: + " language java parameter style java"
0921: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s2F2'"
0922: + " no sql called on null input",
0923: "create procedure r1.p1( )"
0924: + " language java parameter style java"
0925: + " external name 'org.apache.derbyTesting.functionTests.tests.lang.grantRevoke.s1P1'"
0926: + " no sql called on null input" };
0927:
0928: private void runChecks(PrivCheck[] checks,
0929: boolean invertExpecation, String testLabel) {
0930: runChecks(checks, true, invertExpecation, testLabel);
0931: }
0932:
0933: private void runChecks(PrivCheck[] checks, boolean runStatements,
0934: boolean invertExpecation, String testLabel) {
0935: try {
0936: for (int i = 0; i < checks.length; i++) {
0937: if (invertExpecation)
0938: checks[i].invertExpectation();
0939: checks[i].checkPriv(runStatements, testLabel);
0940: if (invertExpecation)
0941: checks[i].invertExpectation();
0942: }
0943: } catch (SQLException sqle) {
0944: unexpectedException(testLabel, sqle);
0945: }
0946: } // end of runChecks
0947:
0948: private void reportFailure(String msg) {
0949: errorCount++;
0950: System.out.println(msg);
0951: }
0952:
0953: private void reportFailure(String[] msg) {
0954: errorCount++;
0955: for (int i = 0; i < msg.length; i++)
0956: System.out.println(msg[i]);
0957: }
0958:
0959: private void unexpectedException(String testLabel, SQLException sqle) {
0960: reportFailure((testLabel == null) ? "Unexpected exception"
0961: : ("Unexpected exception in " + testLabel + " test"));
0962: while (sqle != null) {
0963: System.out.println(sqle.getSQLState() + ": "
0964: + sqle.getMessage());
0965: SQLException next = sqle.getNextException();
0966: if (next == null) {
0967: sqle.printStackTrace(System.out);
0968: break;
0969: }
0970: sqle = next;
0971: }
0972: }
0973:
0974: private abstract class PrivCheck {
0975: boolean expectPriv;
0976: boolean privIsPublic;
0977: User user;
0978: String schema;
0979:
0980: PrivCheck(boolean expectPriv, boolean privIsPublic, User user,
0981: String schema) {
0982: this .expectPriv = expectPriv;
0983: this .privIsPublic = privIsPublic;
0984: this .user = user;
0985: this .schema = schema;
0986: }
0987:
0988: void invertExpectation() {
0989: expectPriv = !expectPriv;
0990: }
0991:
0992: void checkPriv(boolean runStatements, String testLabel)
0993: throws SQLException {
0994: checkSQL(testLabel);
0995: checkMetaData(testLabel);
0996: if (runStatements && !user.isPublic()) {
0997: checkUser(user, testLabel);
0998: }
0999: }
1000:
1001: /**
1002: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1003: *
1004: * @param testLabel A label to use in diagnostic messages.
1005: *
1006: * @exception SQLException Indicates a problem with the test program. Should not happen.
1007: */
1008: abstract void checkUser(User user, String testLabel)
1009: throws SQLException;
1010:
1011: /**
1012: * Use the database metadata to check that the privilege is (not) in the the system permission catalogs.
1013: *
1014: * @param testLabel A label to use in diagnostic messages.
1015: *
1016: * @exception SQLException Indicates a problem with the test program. Should not happen.
1017: */
1018: abstract void checkMetaData(String testLabel)
1019: throws SQLException;
1020:
1021: /**
1022: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1023: *
1024: * @param testLabel A label to use in diagnostic messages.
1025: *
1026: * @exception SQLException Indicates a problem with the test program. Should not happen.
1027: */
1028: abstract void checkSQL(String testLabel) throws SQLException;
1029:
1030: protected void checkSQLException(SQLException sqle,
1031: boolean expected, String expectedSQLState,
1032: String testLabel, String[] fixedSegs,
1033: String[][] variables, boolean[] ignoreCase) {
1034: if (!expected)
1035: unexpectedException(testLabel, sqle);
1036: else if (!sqle.getSQLState().startsWith(expectedSQLState))
1037: unexpectedException(testLabel, sqle);
1038: else {
1039: if (msgTxtOK(sqle.getMessage(), 0, 0, fixedSegs,
1040: variables, ignoreCase))
1041: return;
1042: StringBuffer expectedMsg = new StringBuffer();
1043: for (int segIdx = 0; segIdx < fixedSegs.length; segIdx++) {
1044: expectedMsg.append(fixedSegs[segIdx]);
1045: if (segIdx < variables.length) {
1046: if (variables[segIdx].length == 1)
1047: expectedMsg.append(variables[segIdx][0]);
1048: else
1049: expectedMsg.append("{?}");
1050: }
1051: }
1052: reportFailure("Incorrect error message. Expected \""
1053: + expectedMsg.toString() + "\" got \""
1054: + sqle.getMessage() + "\"");
1055: }
1056: } // end of checkSQLException
1057:
1058: /* See if actualMsg.substring( offset) looks like
1059: * fixedSegs[segIdx] + variables[segIdx] + fixedSegs[segIdx + 1] ...
1060: */
1061: private boolean msgTxtOK(String actualMsg, int offset,
1062: int segIdx, String[] fixedSegs, String[][] variables,
1063: boolean[] ignoreCase) {
1064: for (; segIdx < fixedSegs.length; segIdx++) {
1065: if (!actualMsg.startsWith(fixedSegs[segIdx], offset))
1066: return false;
1067: offset += fixedSegs[segIdx].length();
1068: if (segIdx < variables.length) {
1069: if (variables[segIdx].length == 1) {
1070: if (!actualMsg.regionMatches(
1071: ignoreCase[segIdx], offset,
1072: variables[segIdx][0], 0,
1073: variables[segIdx][0].length()))
1074: return false;
1075: offset += variables[segIdx][0].length();
1076: } else {
1077: // There is a choice. See if any of them works.
1078: int i;
1079: for (i = 0; i < variables[segIdx].length; i++) {
1080: if (actualMsg.regionMatches(
1081: ignoreCase[segIdx], offset,
1082: variables[segIdx][i], 0,
1083: variables[segIdx][i].length())
1084: && msgTxtOK(actualMsg, offset
1085: + variables[segIdx][i]
1086: .length(),
1087: segIdx + 1, fixedSegs,
1088: variables, ignoreCase)) {
1089: offset += variables[segIdx][i].length();
1090: break;
1091: }
1092: }
1093: if (i >= variables[segIdx].length)
1094: return false;
1095: }
1096: }
1097: }
1098: return true;
1099: } // end of msgTxtOK
1100:
1101: } // end of class PrivCheck
1102:
1103: private static final String[] columnPrivErrMsgFixedSegs = {
1104: "User '", "' does not have ", " permission on column '",
1105: "' of table '", "'.'", "'." };
1106:
1107: private static final String[] tablePrivErrMsgFixedSegs = {
1108: "User '", "' does not have ", " permission on table '",
1109: "'.'", "'." };
1110:
1111: private static final String[] executePrivErrMsgFixedSegs = {
1112: "User '", "' does not have execute permission on ", " '",
1113: "'.'", "'." };
1114:
1115: private abstract class TablePrivCheck extends PrivCheck {
1116: String table;
1117: String[] columns;
1118: private String[] allColumns;
1119:
1120: TablePrivCheck(boolean expectPriv, boolean privIsPublic,
1121: User user, String schema, String table, String[] columns) {
1122: super (expectPriv, privIsPublic, user, schema);
1123: this .table = table;
1124: this .columns = columns;
1125: }
1126:
1127: /**
1128: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1129: *
1130: * @param testLabel A label to use in diagnostic messages.
1131: * @param tablePermsColName the name of the column to check in SYS.SYSTABLEPERMS
1132: * @param colPermsType the value to look for in the SYS.SYSCOLPERMS.TYPE column
1133: *
1134: * @exception SQLException Indicates a problem with the test program. Should not happen.
1135: */
1136: void checkSQL(String testLabel, String tablePermsColName,
1137: String colPermsType) throws SQLException {
1138: // Can't do this testing in client frameworks as FormatableBitSet is not exposed there
1139: if (TestUtil.isNetFramework())
1140: return;
1141:
1142: if (columns == null) {
1143: ResultSet rs = owner.stmt
1144: .executeQuery("select p."
1145: + tablePermsColName
1146: + " from SYS.SYSTABLEPERMS p, SYS.SYSTABLES t, SYS.SYSSCHEMAS s"
1147: + " where p.GRANTEE = '" + user.name
1148: + "' and p.TABLEID = t.TABLEID and "
1149: + " t.TABLENAME = '" + table
1150: + "' and t.SCHEMAID = s.SCHEMAID and "
1151: + " s.SCHEMANAME = '" + schema + "'");
1152: if (rs.next()) {
1153: String hasPerm = rs.getString(1);
1154: if ("N".equals(hasPerm)) {
1155: if (expectPriv)
1156: reportFailure(getPrivName()
1157: + " permission not in SYSTABLEPERMS for "
1158: + user + " on table " + schema
1159: + "." + table);
1160: } else if ("y".equals(hasPerm)) {
1161: if (!expectPriv)
1162: reportFailure(getPrivName()
1163: + " permission was in SYSTABLEPERMS for "
1164: + user + " on table " + schema
1165: + "." + table);
1166: } else if ("Y".equals(hasPerm)) {
1167: reportFailure(getPrivName()
1168: + " WITH GRANT OPTION in SYSTABLEPERMS for "
1169: + user + " on table " + schema + "."
1170: + table);
1171: }
1172: if (rs.next())
1173: reportFailure("Multiple SYS.SYSTABLEPERMS rows for user "
1174: + user
1175: + " on table "
1176: + schema
1177: + "."
1178: + table);
1179: } else {
1180: if (expectPriv)
1181: reportFailure("No SYSTABLEPERMS rows for "
1182: + user + " on table " + schema + "."
1183: + table);
1184: }
1185: rs.close();
1186: } else {
1187: // Column permissions
1188: ResultSet rs = owner.stmt
1189: .executeQuery("select p.type,p.columns from SYS.SYSCOLPERMS p, SYS.SYSTABLES t, SYS.SYSSCHEMAS s"
1190: + " where p.GRANTEE = '"
1191: + user.name
1192: + "' and (p.type = '"
1193: + colPermsType.toLowerCase()
1194: + "' or p.type = '"
1195: + colPermsType.toUpperCase()
1196: + "') and p.TABLEID = t.TABLEID and "
1197: + " t.TABLENAME = '"
1198: + table
1199: + "' and t.SCHEMAID = s.SCHEMAID and "
1200: + " s.SCHEMANAME = '" + schema + "'");
1201: if (rs.next()) {
1202: String type = rs.getString(1);
1203: FormatableBitSet colBitSet = (FormatableBitSet) rs
1204: .getObject(2);
1205: if (type == null || colBitSet == null)
1206: reportFailure("Null type or columns value in SYSCOLPERMS row for "
1207: + user
1208: + " on table "
1209: + schema
1210: + "."
1211: + table);
1212: else {
1213: FormatableBitSet expectedColBitSet = getColBitSet();
1214: colBitSet.and(expectedColBitSet);
1215: if (expectPriv) {
1216: if (!colBitSet.equals(expectedColBitSet))
1217: reportFailure("Expected "
1218: + getPrivName()
1219: + " permissions not all in SYSCOLPERMS for "
1220: + user + " on table " + schema
1221: + "." + table);
1222: } else {
1223: if (colBitSet.anySetBit() >= 0)
1224: reportFailure("Unexpected "
1225: + getPrivName()
1226: + " permissions in SYSCOLPERMS for "
1227: + user + " on table " + schema
1228: + "." + table);
1229: }
1230: }
1231: if (rs.next())
1232: reportFailure("Multiple " + getPrivName()
1233: + " rows in SYSCOLPERMS for " + user
1234: + " on table " + schema + "." + table);
1235: } else {
1236: if (expectPriv)
1237: reportFailure("No " + getPrivName()
1238: + " permissions in SYSCOLPERMS for "
1239: + user + " on table " + schema + "."
1240: + table);
1241: }
1242: rs.close();
1243: }
1244: } // end of checkSQL
1245:
1246: String getUserCurrentSchema(User user) throws SQLException {
1247: String schemaString = null;
1248:
1249: Statement s = user.getConnection().createStatement();
1250: ResultSet rs = s.executeQuery("values current schema");
1251: while (rs.next())
1252: schemaString = rs.getString(1);
1253: return schemaString;
1254: }
1255:
1256: void setUserCurrentSchema(User user, String schema)
1257: throws SQLException {
1258: Statement s = user.getConnection().createStatement();
1259: try {
1260: s.executeUpdate("set schema " + schema);
1261: } catch (SQLException sqle) {
1262: // If schema not present, create it and try again
1263: if (sqle.getSQLState() == "42Y07") {
1264: s.executeUpdate("create schema " + schema);
1265: s.executeUpdate("set schema " + schema);
1266: }
1267: }
1268: }
1269:
1270: private HashMap columnHash;
1271:
1272: FormatableBitSet getColBitSet() throws SQLException {
1273: if (columns == null)
1274: return null;
1275:
1276: if (columnHash == null) {
1277: columnHash = new HashMap();
1278: ResultSet rs = dbmd.getColumns((String) null, schema,
1279: table, (String) null);
1280: while (rs.next()) {
1281: columnHash.put(rs.getString("COLUMN_NAME"),
1282: new Integer(
1283: rs.getInt("ORDINAL_POSITION") - 1));
1284: }
1285: rs.close();
1286: }
1287: FormatableBitSet colBitSet = new FormatableBitSet(
1288: columnHash.size());
1289: for (int i = 0; i < columns.length; i++) {
1290: Integer colIdx = (Integer) columnHash.get(columns[i]
1291: .toUpperCase());
1292: if (colIdx == null)
1293: throw new SQLException(
1294: "Internal test error: table " + schema
1295: + "." + table + " does not have a "
1296: + columns[i].toUpperCase()
1297: + " column.");
1298: colBitSet.set(colIdx.intValue());
1299: }
1300: return colBitSet;
1301: } // end of getColBitSet
1302:
1303: /**
1304: * Use the database metadata to check that the privilege is (not) in the the system permission catalogs.
1305: *
1306: * @param testLabel A label to use in diagnostic messages.
1307: *
1308: * @exception SQLException Indicates a problem with the test program. Should not happen.
1309: */
1310: void checkMetaData(String testLabel) throws SQLException {
1311: // Can't do this testing in client frameworks as FormatableBitSet is not exposed there
1312: if (TestUtil.isNetFramework())
1313: return;
1314:
1315: if (columns == null) {
1316: ResultSet rs = dbmd.getTablePrivileges((String) null,
1317: schema, table);
1318: boolean found = false;
1319: while (rs.next()) {
1320: String go = rs.getString(4); // grantor
1321: String ge = rs.getString(5); // grantee
1322: String p = rs.getString(6); // privilege
1323: String ig = rs.getString(7); // is grantable
1324: if (!dbmd.getUserName().equals(go))
1325: reportFailure("DatabaseMetaData.getTablePrivileges returned incorrect grantor");
1326: if (ge == null)
1327: reportFailure("DatabaseMetaData.getTablePrivileges returned null user");
1328: if (p == null)
1329: reportFailure("DatabaseMetaData.getTablePrivileges returned null privilege");
1330: if (ig == null)
1331: reportFailure("DatabaseMetaData.getTablePrivileges returned null is_grantable");
1332: if (ig.equals("YES"))
1333: reportFailure("grantable "
1334: + p
1335: + " privilege reported by DatabaseMetaData.getTablePrivileges");
1336: else if (!ig.equals("NO"))
1337: reportFailure("DatabaseMetaData.getTablePrivileges returned invalid is_grantable");
1338: if (user.name.equals(ge) && getPrivName().equals(p))
1339: found = true;
1340: }
1341: rs.close();
1342: if (expectPriv && !found)
1343: reportFailure("DatabaseMetaData.getTablePrivileges did not return expected "
1344: + getPrivName() + " permision");
1345: else if (found && !expectPriv)
1346: reportFailure("DatabaseMetaData.getTablePrivileges returned an unexpected "
1347: + getPrivName() + " permision");
1348: } else {
1349: FormatableBitSet expectedColBitSet = getColBitSet();
1350: FormatableBitSet found = new FormatableBitSet(
1351: expectedColBitSet.getLength());
1352: ResultSet rs = dbmd.getColumnPrivileges((String) null,
1353: schema, table, "%");
1354: while (rs.next()) {
1355: String colName = rs.getString("COLUMN_NAME");
1356: String go = rs.getString("GRANTOR");
1357: String ge = rs.getString("GRANTEE");
1358: String p = rs.getString("PRIVILEGE");
1359: String ig = rs.getString("IS_GRANTABLE");
1360: if (!dbmd.getUserName().equals(go))
1361: reportFailure("DatabaseMetaData.getColumnPrivileges returned incorrect grantor");
1362: if (ge == null)
1363: reportFailure("DatabaseMetaData.getColumnPrivileges returned null user");
1364: if (p == null)
1365: reportFailure("DatabaseMetaData.getColumnPrivileges returned null privilege");
1366: if (ig == null)
1367: reportFailure("DatabaseMetaData.getColumnPrivileges returned null is_grantable");
1368: if (ig.equals("YES"))
1369: reportFailure("grantable "
1370: + p
1371: + " privilege reported by DatabaseMetaData.getColumnPrivileges");
1372: else if (!ig.equals("NO"))
1373: reportFailure("DatabaseMetaData.getColumnPrivileges returned invalid is_grantable");
1374: Integer cI = (Integer) columnHash.get(colName);
1375: if (cI == null)
1376: reportFailure("DatabaseMetaData.getColumnPrivileges returned invalid column name: "
1377: + colName);
1378: else if (user.name.equals(ge)
1379: && getPrivName().equals(p)) {
1380: int cIdx = cI.intValue();
1381: if (found.isSet(cIdx))
1382: reportFailure("DatabaseMetaData.getColumnPrivileges returned duplicate rows");
1383: else
1384: found.set(cIdx);
1385: }
1386: }
1387: rs.close();
1388: if (expectPriv) {
1389: for (int i = expectedColBitSet.anySetBit(); i >= 0; i = expectedColBitSet
1390: .anySetBit(i)) {
1391: if (!found.isSet(i)) {
1392: reportFailure("DatabaseMetaData.getColumnPrivileges missed "
1393: + getPrivName()
1394: + " permission on column "
1395: + (i + 1));
1396: break;
1397: }
1398: }
1399: } else {
1400: for (int i = expectedColBitSet.anySetBit(); i >= 0; i = expectedColBitSet
1401: .anySetBit(i)) {
1402: if (found.isSet(i)) {
1403: reportFailure("DatabaseMetaData.getColumnPrivileges returned unexpected "
1404: + getPrivName()
1405: + " permission on column "
1406: + (i + 1));
1407: break;
1408: }
1409: }
1410: }
1411: }
1412: } // end of checkMetaData
1413:
1414: abstract String getPrivName();
1415:
1416: protected String[] getAllColumns() throws SQLException {
1417: if (allColumns == null) {
1418: ArrayList columnList = new ArrayList();
1419: ResultSet rs = dbmd.getColumns((String) null, schema,
1420: table, (String) null);
1421: String separator = "";
1422: while (rs.next()) {
1423: columnList.add(rs.getString(4));
1424: }
1425: allColumns = (String[]) columnList
1426: .toArray(new String[0]);
1427: }
1428: return allColumns;
1429: } // end of getAllColumns
1430:
1431: protected void appendWhereClause(StringBuffer sb,
1432: String[] columns) throws SQLException {
1433: if (columns == null)
1434: columns = getAllColumns();
1435: sb.append(" where (");
1436: for (int i = 0; i < columns.length; i++) {
1437: if (i > 0)
1438: sb.append(" or (");
1439: sb.append(columns[i]);
1440: sb.append(" is null)");
1441: }
1442: } // end of appendWhereClause
1443:
1444: /* Check that the error message looks right. It should be
1445: * User '{user}' does not have {action} permission on table '{schema}'.'{table}'.
1446: */
1447: protected void checkTablePermissionMsg(SQLException sqle,
1448: User user, String action, String testLabel) {
1449: checkSQLException(sqle, !expectPriv, "28506", testLabel,
1450: tablePrivErrMsgFixedSegs, new String[][] {
1451: new String[] { user.name },
1452: new String[] { action },
1453: new String[] { schema },
1454: new String[] { table } }, new boolean[] {
1455: true, true, false, false });
1456: } // end of checkTablePermissionMsg
1457:
1458: protected void checkColumnPermissionMsg(SQLException sqle,
1459: User user, String action, String testLabel)
1460: throws SQLException {
1461: checkSQLException(sqle, !expectPriv, "28508", testLabel,
1462: columnPrivErrMsgFixedSegs, new String[][] {
1463: new String[] { user.name },
1464: new String[] { action },
1465: (columns == null) ? getAllColumns()
1466: : columns, new String[] { schema },
1467: new String[] { table } }, new boolean[] {
1468: true, true, false, false, false });
1469: } // end of checkColumnPermissionMsg
1470: } // end of class TablePrivCheck
1471:
1472: static void appendAColumnValue(StringBuffer sb, int type) {
1473: switch (type) {
1474: case Types.BIGINT:
1475: case Types.DECIMAL:
1476: case Types.DOUBLE:
1477: case Types.FLOAT:
1478: case Types.INTEGER:
1479: case Types.NUMERIC:
1480: case Types.REAL:
1481: case Types.SMALLINT:
1482: case Types.TINYINT:
1483: sb.append("0");
1484: break;
1485:
1486: case Types.CHAR:
1487: case Types.VARCHAR:
1488: sb.append("' '");
1489: break;
1490:
1491: case Types.DATE:
1492: sb.append("CURRENT_DATE");
1493: break;
1494:
1495: case Types.TIME:
1496: sb.append("CURRENT_TIME");
1497: break;
1498:
1499: case Types.TIMESTAMP:
1500: sb.append("CURRENT_TIMESTAMP");
1501: break;
1502:
1503: default:
1504: sb.append("null");
1505: break;
1506: }
1507: } // end of appendAColumnValue
1508:
1509: private class SelectPrivCheck extends TablePrivCheck {
1510: SelectPrivCheck(boolean expectPriv, boolean privIsPublic,
1511: User user, String schema, String table, String[] columns) {
1512: super (expectPriv, privIsPublic, user, schema, table,
1513: columns);
1514: }
1515:
1516: String getPrivName() {
1517: return "SELECT";
1518: }
1519:
1520: /**
1521: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1522: *
1523: * @param testLabel A label to use in diagnostic messages.
1524: *
1525: * @exception SQLException Indicates a problem with the test program. Should not happen.
1526: */
1527: void checkSQL(String testLabel) throws SQLException {
1528: checkSQL(testLabel, "SELECTPRIV", "s");
1529: }
1530:
1531: /**
1532: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1533: *
1534: * @param testLabel A label to use in diagnostic messages.
1535: *
1536: * @exception SQLException Indicates a problem with the test program. Should not happen.
1537: */
1538: void checkUser(User user, String testLabel) throws SQLException {
1539: StringBuffer sb = new StringBuffer();
1540: sb.append("select ");
1541: if (columns == null)
1542: sb.append("*");
1543: else {
1544: for (int i = 0; i < columns.length; i++) {
1545: if (i != 0)
1546: sb.append(",");
1547: sb.append(columns[i]);
1548: }
1549: }
1550: sb.append(" from ");
1551: if (schema != null) {
1552: sb.append(schema);
1553: sb.append(".");
1554: }
1555: sb.append(table);
1556:
1557: checkUser(user, sb, testLabel);
1558:
1559: // Test using the columns in a where clause.
1560: sb.setLength(0);
1561: sb.append("select count(*) from \"");
1562: sb.append(schema);
1563: sb.append("\".\"");
1564: sb.append(table);
1565: sb.append("\"");
1566: appendWhereClause(sb, columns);
1567: checkUser(user, sb, testLabel);
1568: } // end of checkUser
1569:
1570: private void checkUser(User user, StringBuffer sb,
1571: String testLabel) throws SQLException {
1572: System.out.println("SelectPrivCheck: " + sb.toString());
1573: PreparedStatement ps = user.getConnection()
1574: .prepareStatement(sb.toString());
1575: try {
1576: ResultSet rs = ps.executeQuery();
1577: rs.next();
1578: rs.close();
1579: if (!(privIsPublic || expectPriv))
1580: reportFailure("A select was performed without permission. ("
1581: + testLabel + ")");
1582: } catch (SQLException sqle) {
1583: checkColumnPermissionMsg(sqle, user, "select",
1584: testLabel);
1585: }
1586: ps.close();
1587: }
1588: } // end of class SelectPrivCheck
1589:
1590: private class DeletePrivCheck extends TablePrivCheck {
1591: DeletePrivCheck(boolean expectPriv, boolean privIsPublic,
1592: User user, String schema, String table) {
1593: super (expectPriv, privIsPublic, user, schema, table,
1594: (String[]) null);
1595: }
1596:
1597: String getPrivName() {
1598: return "DELETE";
1599: }
1600:
1601: /**
1602: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1603: *
1604: * @param testLabel A label to use in diagnostic messages.
1605: *
1606: * @exception SQLException Indicates a problem with the test program. Should not happen.
1607: */
1608: void checkSQL(String testLabel) throws SQLException {
1609: checkSQL(testLabel, "DELETEPRIV", "d");
1610: }
1611:
1612: /**
1613: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1614: *
1615: * @param testLabel A label to use in diagnostic messages.
1616: *
1617: * @exception SQLException Indicates a problem with the test program. Should not happen.
1618: */
1619: void checkUser(User user, String testLabel) throws SQLException {
1620: StringBuffer sb = new StringBuffer();
1621: sb.append("delete from \"");
1622: sb.append(schema);
1623: sb.append("\".\"");
1624: sb.append(table);
1625: sb.append("\"");
1626: boolean savedAutoCommit = user.getConnection()
1627: .getAutoCommit();
1628: user.getConnection().setAutoCommit(false);
1629: System.out.println("DeletePrivCheck: " + sb.toString());
1630: PreparedStatement ps = user.getConnection()
1631: .prepareStatement(sb.toString());
1632: try {
1633: ps.executeUpdate();
1634: if (!(privIsPublic || expectPriv))
1635: reportFailure("A delete was performed without permission. ("
1636: + testLabel + ")");
1637: } catch (SQLException sqle) {
1638: checkTablePermissionMsg(sqle, user, "delete", testLabel);
1639: } finally {
1640: try {
1641: user.getConnection().rollback();
1642: } finally {
1643: user.getConnection().setAutoCommit(savedAutoCommit);
1644: }
1645: }
1646: } // end of checkUser
1647:
1648: } // end of class DeletePrivCheck
1649:
1650: private class InsertPrivCheck extends TablePrivCheck {
1651: InsertPrivCheck(boolean expectPriv, boolean privIsPublic,
1652: User user, String schema, String table) {
1653: super (expectPriv, privIsPublic, user, schema, table,
1654: (String[]) null);
1655: }
1656:
1657: String getPrivName() {
1658: return "INSERT";
1659: }
1660:
1661: /**
1662: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1663: *
1664: * @param testLabel A label to use in diagnostic messages.
1665: *
1666: * @exception SQLException Indicates a problem with the test program. Should not happen.
1667: */
1668: void checkSQL(String testLabel) throws SQLException {
1669: checkSQL(testLabel, "INSERTPRIV", "i");
1670: }
1671:
1672: /**
1673: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1674: *
1675: * @param testLabel A label to use in diagnostic messages.
1676: *
1677: * @exception SQLException Indicates a problem with the test program. Should not happen.
1678: */
1679: void checkUser(User user, String testLabel) throws SQLException {
1680: StringBuffer sb = new StringBuffer();
1681: sb.append("insert into \"");
1682: sb.append(schema);
1683: sb.append("\".\"");
1684: sb.append(table);
1685: sb.append("\" values(");
1686: ResultSet rs = dbmd.getColumns((String) null, schema,
1687: table, (String) null);
1688: boolean first = true;
1689: while (rs.next()) {
1690: if (first)
1691: first = false;
1692: else
1693: sb.append(",");
1694: appendAColumnValue(sb, rs.getInt(5));
1695: }
1696: sb.append(")");
1697: boolean savedAutoCommit = user.getConnection()
1698: .getAutoCommit();
1699: user.getConnection().setAutoCommit(false);
1700: System.out.println("InsertPrivCheck: " + sb.toString());
1701: PreparedStatement ps = user.getConnection()
1702: .prepareStatement(sb.toString());
1703: try {
1704: ps.executeUpdate();
1705: if (!(privIsPublic || expectPriv))
1706: reportFailure("An insert was performed without permission. ("
1707: + testLabel + ")");
1708: } catch (SQLException sqle) {
1709: checkTablePermissionMsg(sqle, user, "insert", testLabel);
1710: } finally {
1711: try {
1712: user.getConnection().rollback();
1713: } finally {
1714: user.getConnection().setAutoCommit(savedAutoCommit);
1715: }
1716: }
1717: } // end of checkUser
1718:
1719: } // end of class InsertPrivCheck
1720:
1721: private class UpdatePrivCheck extends TablePrivCheck {
1722: UpdatePrivCheck(boolean expectPriv, boolean privIsPublic,
1723: User user, String schema, String table, String[] columns) {
1724: super (expectPriv, privIsPublic, user, schema, table,
1725: columns);
1726: }
1727:
1728: String getPrivName() {
1729: return "UPDATE";
1730: }
1731:
1732: /**
1733: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1734: *
1735: * @param testLabel A label to use in diagnostic messages.
1736: *
1737: * @exception SQLException Indicates a problem with the test program. Should not happen.
1738: */
1739: void checkSQL(String testLabel) throws SQLException {
1740: checkSQL(testLabel, "UPDATEPRIV", "u");
1741: }
1742:
1743: /**
1744: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1745: *
1746: * @param testLabel A label to use in diagnostic messages.
1747: *
1748: * @exception SQLException Indicates a problem with the test program. Should not happen.
1749: */
1750: void checkUser(User user, String testLabel) throws SQLException {
1751: String[] checkColumns = (columns == null) ? getAllColumns()
1752: : columns;
1753: StringBuffer sb = new StringBuffer();
1754: boolean savedAutoCommit = user.getConnection()
1755: .getAutoCommit();
1756: user.getConnection().setAutoCommit(false);
1757: try {
1758: for (int colIdx = 0; colIdx < checkColumns.length; colIdx++) {
1759: sb.setLength(0);
1760: sb.append("update ");
1761: sb.append(schema);
1762: sb.append(".");
1763: sb.append(table);
1764: sb.append(" set ");
1765: sb.append(checkColumns[colIdx]);
1766: sb.append("=");
1767: ResultSet rs = dbmd.getColumns(null, schema, table,
1768: checkColumns[colIdx]);
1769: if (!rs.next()) {
1770: rs.close();
1771: reportFailure("Could not get column metadata for "
1772: + schema
1773: + "."
1774: + table
1775: + "."
1776: + checkColumns[colIdx]);
1777: continue;
1778: }
1779: appendAColumnValue(sb, rs.getInt(5));
1780: rs.close();
1781: System.out.println("UpdatePrivCheck: "
1782: + sb.toString());
1783: PreparedStatement ps = user.getConnection()
1784: .prepareStatement(sb.toString());
1785: try {
1786: ps.executeUpdate();
1787: if (!(privIsPublic || expectPriv))
1788: reportFailure("An update of "
1789: + schema
1790: + "."
1791: + table
1792: + "."
1793: + checkColumns[colIdx]
1794: + " was performed without permission. ("
1795: + testLabel + ")");
1796: } catch (SQLException sqle) {
1797: checkColumnPermissionMsg(sqle, user, "update",
1798: testLabel);
1799: }
1800: }
1801: } finally {
1802: try {
1803: user.getConnection().rollback();
1804: } finally {
1805: user.getConnection().setAutoCommit(savedAutoCommit);
1806: }
1807: }
1808: } // end of checkUser
1809:
1810: } // end of class UpdatePrivCheck
1811:
1812: private class ReferencesPrivCheck extends TablePrivCheck {
1813: HashMap colNameHash;
1814:
1815: ReferencesPrivCheck(boolean expectPriv, boolean privIsPublic,
1816: User user, String schema, String table, String[] columns) {
1817: super (expectPriv, privIsPublic, user, schema, table,
1818: columns);
1819: if (columns != null) {
1820: colNameHash = new HashMap((5 * columns.length) / 4);
1821: for (int i = 0; i < columns.length; i++)
1822: colNameHash.put(columns[i], columns[i]);
1823: }
1824: }
1825:
1826: String getPrivName() {
1827: return "REFERENCES";
1828: }
1829:
1830: /**
1831: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1832: *
1833: * @param testLabel A label to use in diagnostic messages.
1834: *
1835: * @exception SQLException Indicates a problem with the test program. Should not happen.
1836: */
1837: void checkSQL(String testLabel) throws SQLException {
1838: checkSQL(testLabel, "REFERENCESPRIV", "r");
1839: }
1840:
1841: /**
1842: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1843: *
1844: * @param testLabel A label to use in diagnostic messages.
1845: *
1846: * @exception SQLException Indicates a problem with the test program. Should not happen.
1847: */
1848: void checkUser(User user, String testLabel) throws SQLException {
1849: // RESOLVE
1850: } // end of checkUser
1851: } // end of class ReferencesPrivCheck
1852:
1853: private class TriggerPrivCheck extends TablePrivCheck {
1854: TriggerPrivCheck(boolean expectPriv, boolean privIsPublic,
1855: User user, String schema, String table) {
1856: super (expectPriv, privIsPublic, user, schema, table,
1857: (String[]) null);
1858: }
1859:
1860: String getPrivName() {
1861: return "TRIGGER";
1862: }
1863:
1864: /**
1865: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1866: *
1867: * @param testLabel A label to use in diagnostic messages.
1868: *
1869: * @exception SQLException Indicates a problem with the test program. Should not happen.
1870: */
1871: void checkSQL(String testLabel) throws SQLException {
1872: checkSQL(testLabel, "TRIGGERPRIV", "t");
1873: }
1874:
1875: /**
1876: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
1877: *
1878: * @param testLabel A label to use in diagnostic messages.
1879: *
1880: * @exception SQLException Indicates a problem with the test program. Should not happen.
1881: */
1882: void checkUser(User user, String testLabel) throws SQLException {
1883: StringBuffer sb = new StringBuffer();
1884: sb.append("create trigger ");
1885: sb.append("\"");
1886: sb.append(table + "Trig");
1887: sb.append("\"");
1888: sb.append(" after insert on ");
1889:
1890: sb.append("\"");
1891: sb.append(schema);
1892: sb.append("\".\"");
1893: sb.append(table);
1894: sb.append("\"");
1895: sb.append(" for each row mode db2sql values 1");
1896:
1897: boolean savedAutoCommit = user.getConnection()
1898: .getAutoCommit();
1899: String currentSchema = getUserCurrentSchema(user);
1900: // DDLs can only be issued in their own schema
1901: setUserCurrentSchema(user, user.toString());
1902: user.getConnection().setAutoCommit(false);
1903: System.out.println("TriggerPrivCheck: " + sb.toString());
1904: PreparedStatement ps = user.getConnection()
1905: .prepareStatement(sb.toString());
1906: try {
1907: ps.executeUpdate();
1908: if (!(privIsPublic || expectPriv))
1909: reportFailure("An execute was performed without permission. ("
1910: + testLabel + ")");
1911: } catch (SQLException sqle) {
1912: checkTablePermissionMsg(sqle, user, "trigger",
1913: testLabel);
1914: } finally {
1915: try {
1916: user.getConnection().rollback();
1917: } finally {
1918: user.getConnection().setAutoCommit(savedAutoCommit);
1919: setUserCurrentSchema(user, currentSchema);
1920: }
1921: }
1922: } // end of checkUser
1923: } // end of class TriggerPrivCheck
1924:
1925: private class ExecutePrivCheck extends PrivCheck {
1926: String routine;
1927: boolean isFunction;
1928:
1929: ExecutePrivCheck(boolean expectPriv, boolean privIsPublic,
1930: User user, String schema, String routine,
1931: boolean isFunction) {
1932: super (expectPriv, privIsPublic, user, schema);
1933: this .routine = routine;
1934: this .isFunction = isFunction;
1935: }
1936:
1937: /**
1938: * Use SQL to check that the privilege is (not) in the the system permission catalogs.
1939: *
1940: * @param testLabel A label to use in diagnostic messages.
1941: *
1942: * @exception SQLException Indicates a problem with the test program. Should not happen.
1943: */
1944: void checkSQL(String testLabel) throws SQLException {
1945: ResultSet rs = owner.stmt
1946: .executeQuery("select p.GRANTOPTION from SYS.SYSROUTINEPERMS p, SYS.SYSALIASES a, SYS.SYSSCHEMAS s"
1947: + " where p.GRANTEE = '"
1948: + user.name
1949: + "' and p.ALIASID = a.ALIASID and"
1950: + " a.ALIAS = '"
1951: + routine
1952: + "' and a.ALIASTYPE = '"
1953: + (isFunction ? "F" : "P")
1954: + "' and a.SCHEMAID = s.SCHEMAID and"
1955: + " s.SCHEMANAME = '" + schema + "'");
1956: if (rs.next()) {
1957: if (!expectPriv)
1958: reportFailure("Execute permission in SYSROUTINEPERMS for "
1959: + user
1960: + " on "
1961: + (isFunction ? "function" : "procedure")
1962: + " " + schema + "." + routine);
1963: else {
1964: if (!"N".equals(rs.getString(1)))
1965: reportFailure("WITH GRANT OPTION specified in SYSROUTINEPERMS for "
1966: + user
1967: + " on "
1968: + (isFunction ? "function"
1969: : "procedure")
1970: + " "
1971: + schema
1972: + "." + routine);
1973: }
1974: if (rs.next())
1975: reportFailure("Multiple rows in SYSROUTINEPERMS for "
1976: + user
1977: + " on "
1978: + (isFunction ? "function" : "procedure")
1979: + " " + schema + "." + routine);
1980: } else {
1981: if (expectPriv)
1982: reportFailure("No execute permission in SYSROUTINEPERMS for "
1983: + user
1984: + " on "
1985: + (isFunction ? "function" : "procedure")
1986: + " " + schema + "." + routine);
1987: }
1988: rs.close();
1989: } // end of checkSQL
1990:
1991: /**
1992: * Use the database metadata to check that the privilege is (not) in the the system permission catalogs.
1993: *
1994: * @param testLabel A label to use in diagnostic messages.
1995: *
1996: * @exception SQLException Indicates a problem with the test program. Should not happen.
1997: */
1998: void checkMetaData(String testLabel) throws SQLException {
1999: ; // There is no database metadata method for finding function/procedure privileges
2000: } // end of checkMetaData
2001:
2002: /**
2003: * Run the appropriate SQL statement to see if Derby really grants the privilege or not
2004: *
2005: * @param testLabel A label to use in diagnostic messages.
2006: *
2007: * @exception SQLException Indicates a problem with the test program. Should not happen.
2008: */
2009: void checkUser(User user, String testLabel) throws SQLException {
2010: StringBuffer sb = new StringBuffer();
2011: if (isFunction)
2012: sb.append("values \"");
2013: else
2014: sb.append("call \"");
2015: sb.append(schema);
2016: sb.append("\".\"");
2017: sb.append(routine);
2018: sb.append("\"");
2019: sb.append("()");
2020:
2021: boolean savedAutoCommit = user.getConnection()
2022: .getAutoCommit();
2023: user.getConnection().setAutoCommit(false);
2024: System.out.println("ExecutePrivCheck: " + sb.toString());
2025: PreparedStatement ps = user.getConnection()
2026: .prepareStatement(sb.toString());
2027: try {
2028: if (isFunction) {
2029: ResultSet rs = ps.executeQuery();
2030: rs.close();
2031: } else
2032: ps.executeUpdate();
2033: if (!(privIsPublic || expectPriv))
2034: reportFailure("An execute was performed without permission. ("
2035: + testLabel + ")");
2036: } catch (SQLException sqle) {
2037: checkExecutePermissionMsg(sqle, user, testLabel);
2038: } finally {
2039: try {
2040: user.getConnection().rollback();
2041: } finally {
2042: user.getConnection().setAutoCommit(savedAutoCommit);
2043: }
2044: }
2045: } // end of checkUser
2046:
2047: /* Check that the error message looks right. It should be
2048: * User '{user}' does not have execute permission on FUNCTION/PROCEDURE '{schema}'.'{table}'.
2049: */
2050: protected void checkExecutePermissionMsg(SQLException sqle,
2051: User user, String testLabel) {
2052: checkSQLException(sqle, !expectPriv, "2850A", testLabel,
2053: executePrivErrMsgFixedSegs, new String[][] {
2054: new String[] { user.name },
2055: new String[] { (isFunction) ? "FUNCTION"
2056: : "PROCEDURE" },
2057: new String[] { schema },
2058: new String[] { routine } }, new boolean[] {
2059: true, true, false, false });
2060: } // end of checkExecutePermissionMsg
2061: } // end of class ExecutePrivCheck
2062: }
2063:
2064: class User {
2065: public final String name;
2066: public final String password;
2067: private final boolean isPublic;
2068: private Connection conn;
2069: public Statement stmt;
2070:
2071: User(String name, String password) {
2072: this .name = name;
2073: this .password = password;
2074: isPublic = "public".equalsIgnoreCase(name);
2075: }
2076:
2077: boolean isPublic() {
2078: return isPublic;
2079: }
2080:
2081: void setConnection(Connection conn) throws SQLException {
2082: this .conn = conn;
2083: stmt = conn.createStatement();
2084: }
2085:
2086: Connection getConnection() throws SQLException {
2087: if (conn == null) {
2088: if (!isPublic) {
2089: String connAttrs = "user=" + name + ";password="
2090: + password;
2091: conn = TestUtil.getConnection("wombat", connAttrs);
2092: stmt = conn.createStatement();
2093: }
2094: }
2095: return conn;
2096: }
2097:
2098: public String toString() {
2099: return name;
2100: }
2101: } // end of class User
|