/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.RolesTest

   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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import javax.sql.ConnectionPoolDataSource;
import javax.sql.PooledConnection;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.J2EEDataSource;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * This JUnit tests the SQL roles feature. This feature relies on
 * sqlAuthorization being set. Upgrade tests are not handled here.
 *
 * The tests are run in the cross product:
 *
 *    {client/server, embedded} x
 *    {no sqlAuthorization/sqlAuthorization} x
 *    {data base owner, other user }
 *
 */
public class RolesTest extends BaseJDBCTestCase
{
    /* internal state */
    private final int _authLevel;
    private final String _user;
    private final String _userPassword;
    private Connection _conn;
    private Statement _stm;

    /* test execution security context: one of two below */
    private final static int NO_SQLAUTHORIZATION=0;
    private final static int SQLAUTHORIZATION=1;

    private final static String pwSuffix = "pwSuffix";

    /* SQL states */
    private final static String sqlAuthorizationRequired = "42Z60";
    private final static String syntaxError              = "42X01";
    private final static String roleDboOnly              = "4251A";
    private final static String invalidRole              = "0P000";
    private final static String tooLongId                = "42622";
    private final static String revokeWarn               = "01007";
    private final static String notIdle                  = "25001";
    private final static String invalidRoleName          = "4293A";
    private final static String userException            = "38000";
    private final static String objectAlreadyExists      = "X0Y68";
    private final static String invalidPUBLIC            = "4251B";
    private final static String loginFailed              = "08004";
    private final static String roleGrantCircularity     = "4251C";
    private final static String idParseError             = "XCXA0";

    private int MAX_IDENTIFIER_LENGTH = 128;
    /**
     * Users used by all suites when when authLevel == SQLAUTHORIZATION.
     * The TestConfiguration.sqlAuthorizationDecorator decorator presumes
     * TEST_DBO as dbo, so add it to set of valid users. It uses a fresh db
     * 'dbsqlauth', not 'wombat'.
     */
    private final static String[] users =
        {"TEST_DBO", "DonaldDuck", "\"additional\"\"user\""};

    private final static int
        dboIndex            = 0; // used for connections
    private final static int
        nonDboIndex         = 1; // used for connections
    private final static int
        additionaluserIndex = 2; // *not* used for connections

    private boolean isDbo()
    {
        return users[0].equals(this._user);
    }

    /**
     * Create a new instance of RolesTest.
     *
     * @param name Fixture name
     * @param authLevel authentication level with which test is run
     * @param user Database user
     * @param userPassword Database user's password
     */

    public RolesTest(String name, int authLevel,
                     String user, String userPassword)
    {
        super(name);
        this._authLevel = authLevel;
        this._user = user;
        this._userPassword = userPassword;
    }


    /**
     * Construct top level suite in this JUnit test
     *
     * @return A suite containing embedded and client suites.
     */
    public static Test suite()
    {
        BaseTestSuite suite = new BaseTestSuite("RolesTest");

        /* Negative syntax tests */
        suite.addTest(negativeSyntaxSuite("suite: negative syntax, embedded"));

        suite.addTest(
            TestConfiguration.clientServerDecorator(
                negativeSyntaxSuite("suite: negative syntax, client")));

        /* Positive syntax tests */
        suite.addTest(positiveSyntaxSuite("suite: positive syntax, embedded"));

        suite.addTest(
            TestConfiguration.clientServerDecorator(
                positiveSyntaxSuite("suite: positive syntax, client")));

        /* Semantic tests */
        suite.addTest(
            semanticSuite("suite: semantic, embedded"));

        suite.addTest(
            TestConfiguration.clientServerDecorator(
                semanticSuite("suite: semantic, client")));

        return suite;
    }

    /**
     *
     * Construct suite of tests for negative syntax
     *
     * @param framework Derby framework indication
     * @return A suite containing the test cases for negative syntax
     * incarnated for the two security levels no sqlAuthorization, and
     * sqlAuthorization, The latter has an instance for dbo, and one
     * for an ordinary user, so there are in all three incarnations of
     * tests.
     */
    private static Test negativeSyntaxSuite(String framework)
    {
        BaseTestSuite suite = new BaseTestSuite("roles:"+framework);

        /* Tests running without sql authorization set.
         */
        BaseTestSuite noauthSuite = new BaseTestSuite(
            "suite: security level=noSqlAuthorization");
        noauthSuite.addTest(new RolesTest("testNegativeSyntax",
                                          NO_SQLAUTHORIZATION,
                                          null,
                                          null));
        suite.addTest(noauthSuite);

        // Tests running with sql authorization set.
        suite.addTest(wrapInAuthorization("testNegativeSyntax"));

        return suite;
    }


    /**
     *
     * Construct suite of tests for positive syntax (edge cases)
     *
     * @param framework Derby framework indication
     * @return A suite containing the test cases for  syntax
     * edge cases. Incarnated only for sqlAuthorization, dbo.
     */
    private static Test positiveSyntaxSuite(String framework)
    {
        String dbo = users[dboIndex];
        String dbopw = dbo.concat(pwSuffix);

        Test t = (TestConfiguration.changeUserDecorator
                  (new RolesTest("testPositiveSyntax",
                                 SQLAUTHORIZATION,
                                 dbo, dbopw),
                   dbo, dbopw));

        return TestConfiguration.sqlAuthorizationDecorator(
            DatabasePropertyTestSetup.builtinAuthentication(
                t, users, pwSuffix));
    }


    /**
     * Test negative syntax for roles.
     *
     * @throws SQLException
     */
    public void testNegativeSyntax() throws SQLException
    {
        println("testNegativeSyntax: auth=" + this._authLevel +
                " user="+getTestConfiguration().getUserName());

        _conn = getConnection();
        _stm  = _conn.createStatement();

        doStmt("create role none", // none is reserved word
               syntaxError, syntaxError, syntaxError);
        doStmt("create role current_role", // current_role is reserved word
               syntaxError, syntaxError, syntaxError);

        char[] longname = new char[MAX_IDENTIFIER_LENGTH + 1];
        java.util.Arrays.fill(longname, 'a');
        String nameWithMoreThanMaxChars = new String(longname);

        doStmt("create role " + nameWithMoreThanMaxChars,
               tooLongId, tooLongId, tooLongId);
        // Check SYS-prefix ban
        doStmt("create role sysrole",
               sqlAuthorizationRequired, invalidRoleName, invalidRoleName);
        doStmt("create role \"SYSROLE\"",
               sqlAuthorizationRequired, invalidRoleName, invalidRoleName);
        doStmt("create role public",
               syntaxError, syntaxError, syntaxError);
        doStmt("create role \"PUBLIC\"",
               sqlAuthorizationRequired, invalidPUBLIC, roleDboOnly);
        doStmt("grant \"PUBLIC\" to " + users[1],
               sqlAuthorizationRequired, invalidPUBLIC, invalidPUBLIC);
        doStmt("revoke \"PUBLIC\" from " + users[1],
               sqlAuthorizationRequired, invalidPUBLIC, invalidPUBLIC);
        _stm.close();
    }


    /**
     * Test syntax edge cases (positive)
     *
     * @throws SQLException
     */
    public void testPositiveSyntax() throws SQLException
    {
        println("testPositiveSyntax");
        String n_a     = null; // auth level not used for this test
        int    n_a_cnt = -1;   //

        // sanity check:
        if (!isDbo()) { throw new SQLException("test error"); }


        _conn = getConnection();
        _stm  = _conn.createStatement();

        // "trigger" is not reserved word, but required tweaking of grammar
        // so we add a regression test for it.
        doStmt("create role trigger", n_a, null, n_a);

        // "role" is not a reserved word, either:
        doStmt("create role role", n_a, null, n_a);

        // Check that role name can be longer than present user name
        // (max 30, cf.  Limits.DB2_MAX_USERID_LENGTH).
        String nameWithMoreThan30Chars = ("r123456789" +
                                          "0123456789" +
                                          "01234567890"); // 31 long
        doStmt("create role " + nameWithMoreThan30Chars,
               n_a, null, n_a);

        assertSysRolesRowCount(n_a_cnt, 3, n_a_cnt);

        doStmt("grant trigger to foo", n_a, null, n_a);
        doStmt("grant role to foo", n_a, null, n_a);
        doStmt("revoke trigger from foo", n_a, null, n_a);
        doStmt("revoke role from foo", n_a, null, n_a);

        doStmt("set role " + nameWithMoreThan30Chars, n_a, null, n_a);

        doStmt("create table mytab(i int)", n_a, null, n_a);
        doStmt("grant select on mytab to " + nameWithMoreThan30Chars,
               n_a, null, n_a);
        doStmt("revoke select on mytab from " + nameWithMoreThan30Chars,
               n_a, null, n_a);
        doStmt("drop table mytab", n_a, null, n_a);

        doStmt("drop role trigger", n_a, null, n_a);
        doStmt("drop role role", n_a, null, n_a);
        doStmt("drop role " + nameWithMoreThan30Chars, n_a, null, n_a);

        assertSysRolesRowCount(n_a_cnt, 0, n_a_cnt);

        _stm.close();
    }

    /**
     *
     * Construct suite of semantic tests
     *
     * @param framework Derby framework indication
     *
     * @return A suite containing the semantic test cases incarnated only
     * for security level sqlAuthorization.
     *
     * It has one instance for dbo, and one for an ordinary user, so there
     * are in all three incarnations of tests.
     */
    private static Test semanticSuite(String framework)
    {
        /*
         * Tests running without sql authorization set.  The purpose
         * of this is just to make sure the proper errors are given.
         */
        BaseTestSuite noauthSuite = new BaseTestSuite(
            "suite: security level=noSqlAuthorization");
        noauthSuite.addTest(new RolesTest("testSemantics",
                                          NO_SQLAUTHORIZATION,
                                          null,
                                          null));

        /* Tests running with sql authorization set.
         * First decorate with users, then with authentication +
         * sqlAuthorization.
         */
        BaseTestSuite suite = new BaseTestSuite("roles:"+framework);

        suite.addTest(noauthSuite);
        suite.addTest(wrapInAuthorization("testSemantics"));

        return suite;
    }

    /**
     * Wrap in decorators to run with data base owner and one other
     * valid user in sqlAuthorization mode.
     *
     * @param testName test to wrap
     */
    private static Test wrapInAuthorization(String testName)
    {
        // add decorator for different users authenticated
        BaseTestSuite usersSuite =
            new BaseTestSuite("suite: security level=sqlAuthorization");

        // First decorate with users (except "additionaluser"), then
        // with authorization decorator
        for (int userNo = 0; userNo <= users.length - 2; userNo++) {
            usersSuite.addTest
                (TestConfiguration.changeUserDecorator
                 (new RolesTest(testName,
                                SQLAUTHORIZATION,
                                users[userNo],
                                users[userNo].concat(pwSuffix)),
                  users[userNo],
                  users[userNo].concat(pwSuffix)));
        }

        return TestConfiguration.sqlAuthorizationDecorator(
            DatabasePropertyTestSetup.builtinAuthentication(
                usersSuite, users, pwSuffix));
    }

    /**
     * Semantic tests for roles.
     * Side effect from the dbo run are needed for the nonDbo run
     * which follows (since only dbo can create and grant roles).
     *
     * @throws SQLException
     */
    public void testSemantics() throws SQLException
    {
        println("testSemantics: auth=" + this._authLevel +
                " user="+getTestConfiguration().getUserName());

        _conn = getConnection();
        _stm  = _conn.createStatement();


        /*
         * CREATE ROLE
         */
        doStmt("create role foo",
               sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("create role bar",
               sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("create role notGranted",
               sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("create role role", // role is not reserved word
               sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("create role admin",
                sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("create role \"NONE\"", // quoted role id should work
                sqlAuthorizationRequired, null , roleDboOnly);

        // Verify that we can't create a role which already exists
        doStmt("create role foo",
               sqlAuthorizationRequired, objectAlreadyExists, roleDboOnly);

        // Verify that we can't create a role which has the same auth
        // id as a known user (DERBY-3673).
        //
        // a) built-in user:
        doStmt("create role " + users[dboIndex], sqlAuthorizationRequired,
               objectAlreadyExists, roleDboOnly);

        // specified with mixed case : DonaldDuck
        doStmt("create role " + users[nonDboIndex],
                sqlAuthorizationRequired, objectAlreadyExists, roleDboOnly);

        // delimited identifier with embedded text quote inside
        doStmt("create role " + users[additionaluserIndex],
                sqlAuthorizationRequired, objectAlreadyExists, roleDboOnly);


        // b) A grant to this auth id exists (see setup), even though
        // it is not a built-in user, so the presumption is, it is a
        // user defined externally:
        doStmt("create role whoever", sqlAuthorizationRequired,
               objectAlreadyExists, roleDboOnly);

        // c) A schema exists which has an authid we did not see
        // through properties; user has been removed, but his schema
        // lingers..
        doStmt("create role schemaowner", sqlAuthorizationRequired,
               objectAlreadyExists, roleDboOnly);

        testLoginWithUsernameWhichIsARole();

        /*
         * GRANT <role>
         */
        doStmt("grant foo to authid", // authid: user or role
               sqlAuthorizationRequired, null , roleDboOnly);

        // this grant also grant role bar to the non-dbo user
        // so we can set it when running as non-dbo.
        doStmt("grant foo, role, bar to authid1, authid2, " +
               users[nonDboIndex],
               sqlAuthorizationRequired, null , roleDboOnly);

        doStmt("grant admin to authid",
               sqlAuthorizationRequired, null , invalidRole);
        doStmt("grant admin, foo to authid",
               sqlAuthorizationRequired, null , invalidRole);
        doStmt("grant admin, foo to public",
               sqlAuthorizationRequired, null , invalidRole);

        // These grants will no be explicitly revoked, count on drop
        // role to void them!
        doStmt("grant admin to a,b,c",
               sqlAuthorizationRequired, null , invalidRole);
        doStmt("grant foo,bar to admin",
               sqlAuthorizationRequired, null , roleDboOnly);

        assertSysRolesRowCount(0, 24,
                               // nonDbo run: foo, bar, notGranted still in
                               // place, used for testing SET ROLE for non-dbo
                               // user. foo granted to public, bar granted to
                               // nonDbo, notGranted not granted at all, so 5!
                               5);

        checkGrantCircularity();

        /*
         * SET ROLE
         */
        doStmt("set role foo",
               sqlAuthorizationRequired, null , null /* through public */);
        doStmt("set role 'FOO'",
               sqlAuthorizationRequired, null, null);

        doStmt("set role none",
               sqlAuthorizationRequired, null , null);

        doDynamicSetRole(_conn);

        doStmt("set role 'NONE'",
               sqlAuthorizationRequired, idParseError, idParseError);
        doStmt("set role 'none'",
               sqlAuthorizationRequired, idParseError, idParseError);
        doStmt("set role '\"NONE\"'",
               sqlAuthorizationRequired, null, invalidRole);
        doStmt("set role ' '",
               sqlAuthorizationRequired, idParseError, idParseError);


        doStmt("set role bar",
               sqlAuthorizationRequired, null , null /* direct grant */);
        doStmt("set role notGranted",
               sqlAuthorizationRequired, null , invalidRole /* not granted */);
        doStmt("set role role",
               sqlAuthorizationRequired, null , invalidRole /* not exists */);

        /* Test that we cannot set role while in non-idle state */
        _conn.setAutoCommit(false);
        doStmt("select * from SYS.SYSROLES", null, null, null);
        doStmt("set role role",
               sqlAuthorizationRequired, notIdle , notIdle);
        _conn.commit();
        _conn.setAutoCommit(true);



        /*
         * CURRENT_ROLE
         */
        ResultSet rs = doQuery("values current_role",
                               sqlAuthorizationRequired, null , null);
        assertRoleInRs(rs, "\"ROLE\"", "\"BAR\"");

        if (rs != null) {
            rs.close();
        }

        /*
         * REVOKE role
         */
        doStmt("revoke foo from authid", // authid: user or role
               sqlAuthorizationRequired, null , roleDboOnly);
        doStmt("revoke foo, role, bar from authid1, authid2",
               sqlAuthorizationRequired, null , roleDboOnly);
        // revoke everything from nonDbo also, except bar
        doStmt("revoke foo, role from " + users[nonDboIndex],
               sqlAuthorizationRequired, null , roleDboOnly);


        doStmt("revoke admin from authid",
               sqlAuthorizationRequired, null , invalidRole);
        doStmtWithWarnings("revoke admin from authid",
                           new String[]{sqlAuthorizationRequired, null},
                           new String[]{null, revokeWarn},
                           new String[]{invalidRole, null},
                           false);
        doStmt("revoke admin, foo from authid",
               sqlAuthorizationRequired, null , invalidRole);
        // leave foo granted to public
        doStmt("revoke admin from public",
               sqlAuthorizationRequired, null , invalidRole);



        /*
         * DEFAULT CURRENT_ROLE
         */
        doStmt("create table foo(str varchar(128) default current_role)",
               sqlAuthorizationRequired, null , null );

        /*
         * GRANT TABLE PERMISSION to role
         * Should get auto-dropped when role is dropped
         */
        doStmt("grant select, insert on foo to admin",
               sqlAuthorizationRequired, null , null );
        /*
         * GRANT COLUMN PERMISSION to role
         * Should get auto-dropped when role is dropped
         */
        doStmt("grant select (str), update (str) on foo to admin",
               sqlAuthorizationRequired, null , null );
        /*
         * GRANT ROUTINE PERMISSION to role
         * Should get auto-dropped when role is dropped
         */
        doStmt("create function f1() returns int" +
               "  language java parameter style java" +
               "  external name 'org.apache.derbyTesting." +
               "functionTests.tests.lang.RolesTest.f1'" +
               "  no sql called on null input",
               null, null, null);
        doStmt("grant execute on function f1 to admin",
               sqlAuthorizationRequired, null , null );

        assertSysTablePermsRowCount(0,
                                    // role admin not dropped yet:
                                    // + grant to whoever int setup
                                    2,
                                    // role admin has been dropped, so
                                    // this run's grant to admin is de
                                    // facto to a user named admin
                                    1);

        assertSysColPermsRowCount(0, 2, 2);

        assertSysRoutinePermsRowCount(11, // 11 pre-existing grants to PUBLIC
                                      12,
                                      12);

        /*
         * DROP ROLE
         */

        // Dbo run: don't drop foo and bar, so they survive to next run,
        // a non-dbo can set them, otherwise drop all roles and
        // premissions.
        doStmt("drop role role",
               sqlAuthorizationRequired, null , roleDboOnly);

        doStmt("drop role admin",
               sqlAuthorizationRequired, null , roleDboOnly);
        assertSysTablePermsRowCount(0,
                                    // grant to whoever in setup:
                                    1,
                                    // nonDbo run: role admin has
                                    // been dropped, so this run's
                                    // grant to admin is de facto to a
                                    // user named admin
                                    1);
        assertSysColPermsRowCount(0, 0,
                                  // nonDbo run: role admin has
                                  // been dropped, so this run's grant
                                  // to admin is de facto to a user
                                  // named admin:
                                  2);
        assertSysRoutinePermsRowCount(11, 11,
                                      //  nonDbo run: role admin
                                      // has been dropped, so this
                                      // run's grant to admin is de
                                      // facto to a user named admin:
                                      12);

        doStmt("drop role \"NONE\"",
               sqlAuthorizationRequired, null , roleDboOnly);


        /*
         * REVOKE permissions for nonDbo run
         */
        doStmt("revoke select, insert on foo from admin",
               sqlAuthorizationRequired, null , null );
        doStmt("revoke select (str), update (str) on foo from admin",
               sqlAuthorizationRequired, null , null );
        doStmt("revoke execute on function f1 from admin restrict",
               sqlAuthorizationRequired, null , null );

        // assert (almost) blank slate
        assertSysTablePermsRowCount(0,
                                    // grant to whoever in setup:
                                    1,
                                    0);
        assertSysColPermsRowCount(0,0,0);
        assertSysRoutinePermsRowCount(11,11,11);

        // roles foo and bar survive to nonDbo run and beyond:
        assertSysRolesRowCount(0, 5, 5);

        _stm.close();

        if (!JDBC.vmSupportsJSR169()) {
            testCurrentRoleIsReset();
        }
    }


    /**
     * Create a user that has the same name as a role and try to
     * log in with it; should be denied. This will catch cases
     * where we can't check up front when roles are created,
     * e.g. external authentication, or users are added after the
     * role is created (DERBY-3681).
     * @exception Exception
     */
    private void testLoginWithUsernameWhichIsARole() throws SQLException {
        if (_authLevel == SQLAUTHORIZATION && isDbo()) {
            _stm.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY" +
                         "('derby.user.soonarole', 'whatever')");

            // should work, not defined as a role yet
            openDefaultConnection("soonarole","whatever").close();

            // remove the user so we can create a role
            _stm.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY" +
                         "('derby.user.soonarole', NULL)");
            _stm.execute("create role soonarole");

            // reintroduce the colliding user name now that we have a role
            _stm.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY" +
                         "('derby.user.soonarole', 'whatever')");

            try {
                // should fail now
                openDefaultConnection("soonarole","whatever").close();
                fail("Exception expected connecting with " +
                     "user name equal to a role");
            } catch (SQLException e) {
                assertSQLState(loginFailed, e);
            }

            _stm.execute("drop role soonarole");
            _stm.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY" +
                         "('derby.user.soonarole', NULL)");
        }
    }


    /**
     * Verifies that the current role is reset when creating a new logical
     * connection.
     * <p>
     * The test is run in a non-statement pooling configuration first,
     * and then with statement pooling enabled if the environment supports it.
     * <p>
     * The test pattern is borrowed from the test case in J2EEDataSourceTest.
     *
     * @see org.apache.derbyTesting.functionTests.tests.jdbcapi.J2EEDataSourceTest#testSchemaIsReset
     *
     * @throws SQLException if something goes wrong
     */
    private void testCurrentRoleIsReset()
            throws SQLException {

        if (_authLevel == SQLAUTHORIZATION && isDbo() /* once is enough */) {
            final String user = "DonaldDuck";
            final String passwd = user.concat(pwSuffix);
            ConnectionPoolDataSource cpDs =
                J2EEDataSource.getConnectionPoolDataSource();
            // Test without statement pooling first.
            doTestCurrentRoleIsReset(cpDs.getPooledConnection(user, passwd),
                                     user);

            // Try to enable statement pooling.
            // This is currently only implemented in the client driver.
            if (usingDerbyNetClient()) {
                J2EEDataSource.setBeanProperty(
                    cpDs, "maxStatements",7);
                doTestCurrentRoleIsReset(cpDs.getPooledConnection(user, passwd),
                                         user);
            }
        }
    }

    /**
     * Executes a test sequence to make sure the current role is reset between
     * logical connections.
     *
     * @param pc pooled connection to get logical connections from
     * @param user name of  for the connection
     * @throws SQLException if something goes wrong...
     */
    private void doTestCurrentRoleIsReset(PooledConnection pc, String user)
            throws SQLException {

        Connection con = pc.getConnection();
        Statement stmt = con.createStatement();
        String n_a     = null; // auth level not used for this test

        JDBC.assertCurrentSchema(con, user.toUpperCase());

        // Change the role.
        stmt.execute("set role bar");
        ResultSet rs = stmt.executeQuery("values current_role");
        assertRoleInRs(rs, "\"BAR\"", n_a);
        rs.close();
        stmt.close();

        // Close the logical connection and get a new one and make sure the
        // current role has been reset.
        con.close();
        con = pc.getConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery("values current_role");
        assertRoleInRs(rs, null, n_a);
        rs.close();
        stmt.close();
        con.close();
    }


    private void checkGrantCircularity() {
        if (isDbo()) {
            // Test circularity in role grant relation given this a
            // priori graph:
            //
            //          s8
            //           |   s1<---s3
            //           | / |
            //           V/  |
            //          s4   |
            //         / |\  |
            //        /  V \ V
            //       s5  s6  s2
            //           |
            //           s7

            String NA = null; // we only run this as dbo

            for(int i=1; i <= 8; i++) {
                doStmt("create role s" + i, NA, null, NA);
            }

            // This establishes the role grant graph shown above. None
            // of these grants should fail.
            doStmt("grant s1 to s2", NA, null, NA);
            doStmt("grant s3 to s1", NA, null, NA);
            doStmt("grant s1 to s4", NA, null, NA);
            doStmt("grant s4 to s2", NA, null, NA);
            doStmt("grant s4 to s6", NA, null, NA);
            doStmt("grant s4 to s5", NA, null, NA);
            doStmt("grant s6 to s7", NA, null, NA);
            doStmt("grant s8 to s4", NA, null, NA);

            // These statements all represent illegal grants in that
            // they would cause a circularity, so we expect all to
            // throw.
            doStmt("grant s1 to s1", NA, roleGrantCircularity, NA);
            doStmt("grant s2 to s3", NA, roleGrantCircularity, NA);
            doStmt("grant s2 to s8", NA, roleGrantCircularity, NA);
            doStmt("grant s7 to s1", NA, roleGrantCircularity, NA);
            doStmt("grant s7 to s4", NA, roleGrantCircularity, NA);
            doStmt("grant s7 to s6", NA, roleGrantCircularity, NA);
            doStmt("grant s7 to s3", NA, roleGrantCircularity, NA);
            doStmt("grant s2 to s1", NA, roleGrantCircularity, NA);
            doStmt("grant s2 to s8", NA, roleGrantCircularity, NA);
            doStmt("grant s2 to s4", NA, roleGrantCircularity, NA);
            doStmt("grant s6 to s1", NA, roleGrantCircularity, NA);
            doStmt("grant s6 to s8", NA, roleGrantCircularity, NA);
            doStmt("grant s6 to s3", NA, roleGrantCircularity, NA);
            doStmt("grant s6 to s4", NA, roleGrantCircularity, NA);
            doStmt("grant s5 to s1", NA, roleGrantCircularity, NA);
            doStmt("grant s5 to s3", NA, roleGrantCircularity, NA);
            doStmt("grant s5 to s4", NA, roleGrantCircularity, NA);
            doStmt("grant s5 to s8", NA, roleGrantCircularity, NA);

            for(int i=1; i <= 8; i++) {
                doStmt("drop role s" + i, NA, null, NA);
            }
        }
    }


    protected void setUp() throws Exception
    {
        super.setUp();

        _stm = createStatement();

        if (_authLevel == SQLAUTHORIZATION && isDbo()) {

            // We need to clean away roles when we run the dbo run the
            // second time around (client/server). The reason is that
            // the dbo run has a side-effect: it leaves roles for the
            // non-dbo run to play with, and that run can't remove the
            // roles (since non-dbo can't drop roles).
            try {
                _stm.executeUpdate("drop role foo");
                _stm.executeUpdate("drop role bar");
                _stm.executeUpdate("drop role notGranted");
            } catch (SQLException se) {
            }
        }

        try {
            _stm.executeUpdate("drop function f1");
            _stm.executeUpdate("drop table foo");
        } catch (SQLException se) {
        }

        if (_authLevel == SQLAUTHORIZATION && isDbo()) {
            // create a table grant to an (uknown) user WHOEVER.
            // This is used to test that create role detects the
            // presence of existing user ids before allowing a
            // role creation with that id.
            _stm.executeUpdate("create table t1(i int)");
            _stm.executeUpdate("grant select on t1 to whoever");

            // create a schema for (uknown) user SCHEMAOWNER.
            // This is used to test that create role detects the
            // presence of existing user ids before allowing a
            // role creation with that id.
            _stm.executeUpdate(
                "create schema lingerSchema authorization schemaowner");
        }

        _stm.close();
    }


    protected void tearDown() throws Exception
    {
        if (_authLevel == SQLAUTHORIZATION &&  isDbo()) {
            _stm = createStatement();

            try {
                _stm.executeUpdate("revoke select on t1 from whoever");
                _stm.executeUpdate("drop table t1");
                _stm.executeUpdate("drop schema lingerSchema restrict");
            } catch (SQLException se) {
                System.err.println("Test error + " + se);
            }
        }

        if (_stm != null) {
            _stm.close();
            _stm = null;
        }

        if (_conn != null) {
            _conn = null;
        }

        super.tearDown();
    }


    private void doStmt(String stmt,
                        String noAuthState,
                        String authDboState,
                        String authNotDboState)
    {
        doStmt(stmt, noAuthState, authDboState, authNotDboState, false);
    }


    private ResultSet doQuery(String stmt,
                              String noAuthState,
                              String authDboState,
                              String authNotDboState)
    {
        return doStmt(stmt, noAuthState, authDboState, authNotDboState, true);
    }


    private ResultSet doStmt(String stmt,
                             String noAuthState,
                             String authDboState,
                             String authNotDboState,
                             boolean query)
    {
        return doStmtWithWarnings(stmt,
                                  new String[]{noAuthState,null},
                                  new String[]{authDboState, null},
                                  new String[]{authNotDboState, null},
                                  query);
    }

    // Minion to analyze outcome. If state string is empty, we expect success
    // for that combination of authentication level and user (dbo or not).
    // State arrays: element 0: expected error, element 1: expected warning
    private ResultSet doStmtWithWarnings(String stmt,
                                         String[] noAuthState,
                                         String[] authDboState,
                                         String[] authNotDboState,
                                         boolean query)
    {
        ResultSet result = null;

        try {
            if (query) {
                result = _stm.executeQuery(stmt);
            } else {
                _stm.execute(stmt);
            }

            if (_authLevel == NO_SQLAUTHORIZATION) {
                if (noAuthState[0] != null) {
                    fail("exception " + noAuthState[0] + " expected: (" + stmt);
                }
                if (noAuthState[1] != null) {
                    SQLWarning w = _stm.getWarnings();
                    assertNotNull("Expected warning but found none", w);
                    assertSQLState(noAuthState[1], w);
                }

            } else { // SQLAUTHORIZATION
                if (isDbo()) {
                    if (authDboState[0] != null) {
                        fail("exception " + authDboState[0] + " expected: (" +
                             stmt);
                    }
                    if (authDboState[1] != null) {
                        SQLWarning w = _stm.getWarnings();
                        assertNotNull("Expected warning but found none", w);
                        assertSQLState(authDboState[1], w);
                    }
                } else {
                    if (authNotDboState[0] != null) {
                        fail("exception " + authNotDboState[0] +
                             " expected: (" + stmt);
                    }
                    if (authNotDboState[1] != null) {
                        SQLWarning w = _stm.getWarnings();
                        assertNotNull("Expected warning but found none", w);
                        assertSQLState(authNotDboState[1], w);
                    }
                }
            }
        } catch (SQLException e) {
            if (_authLevel == NO_SQLAUTHORIZATION) {
                if (noAuthState[0] == null) {
                    fail("stmt " + stmt + " failed with exception " +
                         e.getSQLState(), e);
                } else {
                    assertSQLState("Stmt " + stmt, noAuthState[0], e);
                }

            } else { // SQLAUTHORIZATION
                if (isDbo()) {
                    if (authDboState[0] == null) {
                        fail("stmt " + stmt + " failed with exception " +
                             e.getSQLState(), e);
                    } else {
                        assertSQLState("Stmt " + stmt, authDboState[0], e);
                    }
                } else {
                    if (authNotDboState[0] == null) {
                        fail("stmt " + stmt + " failed with exception " +
                             e.getSQLState(), e);
                    } else {
                        assertSQLState("Stmt " + stmt, authNotDboState[0], e);
                    }
                }
            }
        }

        return result;
    }


    private void doDynamicSetRole(Connection conn)
    {
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement("set role ?");

            if (_authLevel == NO_SQLAUTHORIZATION) {
                fail("set role ? should have failed; no sqlAuthorization");
            }
        } catch (SQLException e) {
             if (_authLevel == NO_SQLAUTHORIZATION) {
                 assertSQLState(sqlAuthorizationRequired, e);
                 return;
             } else {
                 fail("prepare of set role ? failed:" + e, e);
             }
        }

        try {
            pstmt.setString(1, "BAR");
            int rowcnt = pstmt.executeUpdate();
            assertEquals("rowcount from set role ? not 0", rowcnt, 0);
        } catch (SQLException e) {
            fail("execute of set role ? failed: [foo]" + e, e);
        }

        // Test that trimming works:
        try {
            pstmt.setString(1, " BAR ");
            int rowcnt = pstmt.executeUpdate();
            assertEquals("rowcount from set role ? not 0", rowcnt, 0);
        } catch (SQLException e) {
            fail("execute of set role ? failed: [foo]" + e, e);
        }

        try {
            int rowcnt = conn.createStatement().executeUpdate(
                "set role ' BAR '");
            assertEquals("rowcount from set role ? not 0", rowcnt, 0);
        } catch (SQLException e) {
            fail("execute of set role ? failed: [foo]" + e, e);
        }


        try {
            pstmt.setString(1, "");
            int rowcnt = pstmt.executeUpdate();
            fail("Expected syntax error on identifier");
        } catch (SQLException e) {
            assertSQLState(idParseError, e);
        }

        try {
            pstmt.setString(1, " ");
            int rowcnt = pstmt.executeUpdate();
            fail("Expected syntax error on identifier");
        } catch (SQLException e) {
            assertSQLState(idParseError, e);
        }

        try {
            pstmt.setString(1, null);
            int rowcnt = pstmt.executeUpdate();
            fail("Expected syntax error on identifier");
        } catch (SQLException e) {
            assertSQLState(idParseError, e);
        }


        if (isDbo()) {
            // not granted to non-dbo, so don't try..
            String n_a     = null; // auth level not used for this test

            try {
                pstmt.setString(1, "NONE");
                int rowcnt = pstmt.executeUpdate();
                fail("NONE should not be allowed as a dynamic parameter");
            } catch (SQLException e) {
                assertSQLState(idParseError, e);
            }

            try {
                pstmt.setString(1, "none");
                int rowcnt = pstmt.executeUpdate();
                fail("NONE should not be allowed as a dynamic parameter");
            } catch (SQLException e) {
                assertSQLState(idParseError, e);
            }

            try {
                pstmt.setString(1, "\"NONE\"");
                int rowcnt = pstmt.executeUpdate();
                assertEquals("rowcount from set role ? not 0", rowcnt, 0);
                ResultSet rs = doQuery("values current_role", n_a, null , n_a );
                assertRoleInRs(rs, "\"NONE\"", n_a);
                rs.close();
            } catch (SQLException e) {
                fail("execute of set role ? failed: [\"NONE\"] " + e, e);
            }
        }

        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
            }
        }
    }


    /* Test that current role is handled correctly when inside a
     * stored procedure.  The SQL standard requires we have an
     * "authorization stack", see section 4.34.1.1 and
     * 4.27.3. Initially tested here, but now moved to
     * lang/SQLSessionContextTest.
     */



    private void assertSystableRowCount(String table,
                                        int rcNoAuth,
                                        int rcDbo,
                                        int rcMereMortal)
        throws SQLException
    {
        ResultSet rs = _stm.executeQuery(
                "SELECT COUNT(*) FROM " + table);
        rs.next();
        assertEquals(table +
                     " (_authLevel == NO_SQLAUTHORIZATION) = " + (_authLevel == NO_SQLAUTHORIZATION) +
                     ", isDbo() = " + isDbo() +
                     ", row count:",
                     _authLevel == NO_SQLAUTHORIZATION ? rcNoAuth :
                     (isDbo() ? rcDbo : rcMereMortal),
                     rs.getInt(1));
        rs.close();
    }


    private void assertSysRolesRowCount(int rcNoAuth,
                                        int rcDbo,
                                        int rcMereMortal)
        throws SQLException
    {

        if (TestConfiguration.getCurrent().isVerbose()) {
            dumpSysRoles();
        }

        assertSystableRowCount("SYS.SYSROLES",
                               rcNoAuth, rcDbo, rcMereMortal);
    }


    private void assertSysTablePermsRowCount(int rcNoAuth,
                                             int rcDbo,
                                             int rcMereMortal)
        throws SQLException
    {

        if (TestConfiguration.getCurrent().isVerbose()) {
            dumpSysTablePerms();
        }

        assertSystableRowCount("SYS.SYSTABLEPERMS",
                               rcNoAuth, rcDbo, rcMereMortal);
    }


    private void assertSysColPermsRowCount(int rcNoAuth,
                                           int rcDbo,
                                           int rcMereMortal)
        throws SQLException
    {

        if (TestConfiguration.getCurrent().isVerbose()) {
            dumpSysColPerms();
        }

        assertSystableRowCount("SYS.SYSCOLPERMS",
                               rcNoAuth, rcDbo, rcMereMortal);
    }


    private void assertSysRoutinePermsRowCount(int rcNoAuth,
                                               int rcDbo,
                                               int rcMereMortal)
        throws SQLException
    {

        if (TestConfiguration.getCurrent().isVerbose()) {
            dumpSysRoutinePerms();
        }

        assertSystableRowCount("SYS.SYSROUTINEPERMS",
                               rcNoAuth, rcDbo, rcMereMortal);
    }


    private void dumpSysRoles() throws SQLException
    {

        ResultSet rs = _stm.executeQuery
            ("SELECT * FROM SYS.SYSROLES ORDER BY ROLEID");

        println("SYS.SYSROLES:");

        while (rs.next()) {
            println("uuid=" + rs.getString(1) +
                    " r=" + rs.getString(2) + " -ee:" + rs.getString(3) +
                    " -or:" + rs.getString(4) + " a:" + rs.getString(5) +
                    " d:" + rs.getString(6));
        }

        rs.close();
    }


    private void dumpSysTablePerms() throws SQLException
    {
        ResultSet rs = _stm.executeQuery
            ("SELECT * FROM SYS.SYSTABLEPERMS");

        println("SYS.SYSTABLEPERMS:");

        while (rs.next()) {
            println("id: " + rs.getString(1) +
                    " -ee:" + rs.getString(2) +
                    " -or:" + rs.getString(3) +
                    " tableid:" + rs.getString(4) +
                    " S:" + rs.getString(5) +
                    " D:" + rs.getString(6) +
                    " I:" + rs.getString(7) +
                    " U:" + rs.getString(8) +
                    " R:" + rs.getString(9) +
                    " T:" + rs.getString(10));
        }

        rs.close();
    }


    private void dumpSysColPerms() throws SQLException
    {

        ResultSet rs = _stm.executeQuery
            ("SELECT * FROM SYS.SYSCOLPERMS");

        println("SYS.SYSCOLPERMS:");

        while (rs.next()) {
            println("id: " + rs.getString(1) +
                    " -ee:" + rs.getString(2) +
                    " -or:" + rs.getString(3) +
                    " tableid:" + rs.getString(4) +
                    " type:" + rs.getString(5) +
                    " col#:" + rs.getString(6));
        }

        rs.close();
    }


    private void dumpSysRoutinePerms() throws SQLException
    {

        ResultSet rs = _stm.executeQuery
            ("SELECT * FROM SYS.SYSROUTINEPERMS");

        println("SYS.SYSROUTINEPERMS:");

        while (rs.next()) {
            println("id: " + rs.getString(1) +
                    " -ee:" + rs.getString(2) +
                    " -or:" + rs.getString(3) +
                    " alias:" + rs.getString(4) +
                    " grantopt:" + rs.getString(5));
        }

        rs.close();
    }


    private void assertRoleInRs(ResultSet rs,
                                String dboRole,
                                String notDboRole)
        throws SQLException
    {

        if (_authLevel == NO_SQLAUTHORIZATION) {
            assertNull(rs);
        } else {
            if (isDbo()) {
                JDBC.assertSingleValueResultSet(rs, dboRole);
            } else {
                JDBC.assertSingleValueResultSet(rs, notDboRole);
            }
        }
    }

    /**
     * Utility function used to test auto-drop of grant routine
     * permission to a role
     * @return 1
     */
    public static int f1()
    {
        return 1;
    }
}