/*

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

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

/**
 * Test that routines declared with EXTERNAL SECURITY DEFINER/INVOKER behaves
 * correctly. In addition to tests found here, see also the test in
 * org.apache.derbyTesting.functionTests.tests.upgradeTests.
 * Changes10_7#testExternalSecuritySpecification, which checks that the feature
 * will not work if SQL authorization is not enabled.
 *
 */
public class RoutinesDefinersRightsTest extends BaseJDBCTestCase
{
    private final static String pwSuffix = "pwSuffix";

    /* SQL states */
    private final static String NOEXECUTEPERMISSION  = "42504";
    private final static String NOCOLUMNPERMISSION = "42502";
    private final static String MULTIPLE_ELEMENTS = "42613";

    // job categories
    private final static int BOSS = 0;
    private final static int MIDDLEMANAGER = 1;
    private final static int FOOTSOLDIER = 2;


    private final static String[] users = {"test_dbo", "PHB", "Dilbert"};
    private final static double[] wages = {1000.0, 500.0, 100.0};
    private final static int[] categories = {BOSS, MIDDLEMANAGER, FOOTSOLDIER};
    /**
     * Create a new instance of RoutinesDefinersRightsTest.
     *
     * @param name Fixture name
     */
    public RoutinesDefinersRightsTest(String name)
    {
        super(name);
    }


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

        if (!JDBC.vmSupportsJSR169()) {
            // JSR169 cannot run with tests with stored procedures
            // that do database access - for they require a
            // DriverManager connection to jdbc:default:connection;
            // DriverManager is not supported with JSR169.
            suite.addTest(makeSuite());

            suite.addTest(
                TestConfiguration.clientServerDecorator(makeSuite()));
        }


        return suite;
    }

    /**
     * Construct suite of tests
     *
     * @return A suite containing the test cases.
     */
    private static Test makeSuite()
    {
        /* Tests running with sql authorization set.  First decorate
         * with clean database, then with authentication +
         * sqlAuthorization.
         */
        Test clean = new CleanDatabaseTestSetup(
            new BaseTestSuite(RoutinesDefinersRightsTest.class)) {
                protected void decorateSQL(Statement s)
                        throws SQLException {

                    s.execute("create role middleManager");
                    s.execute("create table s1.wages(employeeId int," +
                              "                   category int," +
                              "                   salary double," +
                              "                   name VARCHAR(20))");
                    PreparedStatement ps = s.getConnection().prepareStatement(
                        "insert into s1.wages values (?,?,?,?)");

                    for (int i=BOSS; i <= FOOTSOLDIER; i++) {
                        ps.setInt(1, 1000 + i);
                        ps.setInt(2, i);
                        ps.setDouble(3, wages[i]);
                        ps.setString(4, users[i]);
                        ps.execute();
                    }

                    ps.close();

                    s.execute
                        ("create function s1.lookupWageFootSoldier(int) " +
                         "returns double " +
                         "language java parameter style java external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.lookupWageFootSoldier' " +
                         "EXTERNAL SECURITY DEFINER " +
                         "reads sql data called on null input");

                    s.execute
                        ("create procedure s1.updateWage() " +
                         "language java parameter style java " +
                         "modifies sql data " +
                         "external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.updateWage' " +
                         "EXTERNAL SECURITY DEFINER ");

                    s.execute
                        ("grant execute on function s1.lookupWageFootSoldier " +
                         "   to phb");

                    s.execute
                        ("grant execute on procedure s1.updateWage " +
                         "   to phb");

                    s.execute
                        ("create function s1.lookupWageFootSoldierI(int) " +
                         "returns double " +
                         "language java parameter style java external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.lookupWageFootSoldier' " +
                         "EXTERNAL SECURITY INVOKER " +
                         "reads sql data called on null input");

                    s.execute
                        ("grant execute on function " +
                         "    s1.lookupWageFootSoldierI to phb");

                    s.execute
                        ("create procedure s1.spTestBuiltins() " +
                         "language java parameter style java external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.spTestBuiltins' " +
                         "EXTERNAL SECURITY DEFINER " +
                         "reads sql data");

                    s.execute
                        ("grant execute on procedure s1.spTestBuiltins " +
                         "   to phb");

                    // Table function
                    s.execute
                        ("create function s1.selectFootSoldiers() " +
                         "returns table (" +
                         "    employeeId int," +
                         "    category int," +
                         "    salary double," +
                         "    name varchar(20))" +
                         "language java parameter style " +
                         "    derby_jdbc_result_set " +
                         "external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.selectFootSoldiers' " +
                         "EXTERNAL SECURITY DEFINER " +
                         "reads sql data");

                    // PHB needs to set his role to be able to use
                    // s1.selectFootSoldiers. Just to vary a bit..
                    s.execute
                        ("grant execute on function s1.selectFootSoldiers " +
                         "    to middleManager");
                    s.execute
                        ("grant middleManager to phb");

                    s.execute
                        ("create function s1.selectFootSoldiersI() " +
                         "returns table (" +
                         "    employeeId int," +
                         "    category int," +
                         "    salary double," +
                         "    name varchar(20))" +
                         "language java parameter style " +
                         "    derby_jdbc_result_set " +
                         "external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.selectFootSoldiers' " +
                         "EXTERNAL SECURITY INVOKER " +
                         "reads sql data");
                    s.execute
                        ("grant execute on function s1.selectFootSoldiersI " +
                         "    to middleManager");

                    s.execute
                        ("create schema phb authorization phb");
                    s.execute
                        ("create function phb.lookupDilbertWage() " +
                         "returns double " +
                         "language java parameter style java external name " +
                         "'org.apache.derbyTesting.functionTests.tests.lang." +
                         "RoutinesDefinersRightsTest.lookupDilbertWage' " +
                         "EXTERNAL SECURITY DEFINER " +
                         "reads sql data called on null input");
                    s.execute
                        ("grant execute on function phb.lookupDilbertWage " +
                         "    to dilbert");

                    // Check confliciting clauses
                    assertStatementError(
                        new String[]{MULTIPLE_ELEMENTS},
                        s,
                        "create function phb.lookupDilbertWage() " +
                        "returns double " +
                        "language java parameter style java external name " +
                        "'org.apache.derbyTesting.functionTests.tests.lang." +
                        "RoutinesDefinersRightsTest.lookupDilbertWage' " +
                        "EXTERNAL SECURITY DEFINER " +
                        "EXTERNAL SECURITY INVOKER " +
                        "reads sql data called on null input");
                }
            };

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


    public void testDefinersRights() throws SQLException
    {
        Connection c = null;
        ResultSet rs = null;
        Statement stm = null;

        // Shut down database, just so we are sure the routine info is
        // persisted into the on-disk dictionary correctly before we try to use
        // it.
        TestConfiguration.getCurrent().shutdownDatabase();

        // Try as vainly as dilbert to execute lookupWageFootSoldier
        c = openUserConnection("Dilbert");
        stm = c.createStatement();
        assertStatementError(
            new String[]{NOEXECUTEPERMISSION},
            stm,
            "values s1.lookupWageFootSoldier(1002)");
        stm.close();
        c.close();

        // Try as PHB
        c = openUserConnection("PHB");
        stm = c.createStatement();
        rs = stm.executeQuery("values s1.lookupWageFootSoldier(1002)");
        JDBC.assertSingleValueResultSet(rs, "100.0");

        // Try as PHB to update, delete and insert on a result set
        stm.executeUpdate("call s1.updateWage()");

        stm.close();
        c.close();

        // dbo can execute lookupWageFootSoldier, since he has select
        // privileges on the wages table.
        Connection dboConn = getConnection();
        Statement dbos = dboConn.createStatement();

        rs = dbos.executeQuery("values s1.lookupWageFootSoldier(1002)");
        JDBC.assertSingleValueResultSet(rs, "100.0");

        // We see that the method will not return wages for anything but foot
        // soldiers, even for the dbo:
        rs = dbos.executeQuery("values s1.lookupWageFootSoldier(1001)");
        JDBC.assertSingleValueResultSet(rs, "-1.0");

        dbos.close();
        dboConn.close();

        // ----------------------------------------
        // Now try with a table function

        c = openUserConnection("PHB");

        stm = c.createStatement();
        stm.executeUpdate("set role middlemanager");
        rs = stm.executeQuery(
            "select * from table (s1.selectFootSoldiers()) as t");
        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"1002", "2", "100.0", "Dilbert"}});

        // Check that user and role is unchanged after call
        rs = stm.executeQuery(
            "values (current_user, session_user, current_role)");
        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"PHB", "PHB", "\"MIDDLEMANAGER\""}});

        stm.close();
        c.close();

        // ------------------------------------
        // Try with two levels of routines with different definers
        c = openUserConnection("Dilbert");
        stm = c.createStatement();
        rs = stm.executeQuery("values phb.lookupDilbertWage()");
        JDBC.assertSingleValueResultSet(rs, "100.0");

        // sanity when we return
        rs = stm.executeQuery(
            "values (current_user, session_user, current_role)");
        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"DILBERT", "DILBERT", null}});

        stm.close();
        c.close();

    }

    public void testBuiltins() throws SQLException{
        Connection c = openUserConnection("PHB");
        Statement stm = c.createStatement();
        stm.executeUpdate("set role middlemanager");
        stm.executeUpdate("call s1.spTestBuiltins()");
        stm.close();
        c.close();
    }


    public void testInvokersRights() throws SQLException{
        Connection c = null;
        ResultSet rs = null;
        Statement stm = null;

        // Try as vainly as dilbert to execute lookupWageFootSoldier
        c = openUserConnection("Dilbert");
        stm = c.createStatement();
        assertStatementError(
            new String[]{NOEXECUTEPERMISSION},
            stm,
            "values s1.lookupWageFootSoldierI(1002)");
        stm.close();
        c.close();

        // Try as PHB, vainly too, since no definer's rights now.
        c = openUserConnection("PHB");
        stm = c.createStatement();
        assertStatementError(
            new String[]{NOCOLUMNPERMISSION},
            stm,
            "values s1.lookupWageFootSoldierI(1002)");
        stm.close();
        c.close();

        // dbo can execute lookupWageFootSoldier, since she has select
        // privileges on the wages table.
        Connection dboConn = getConnection();
        Statement dbos = dboConn.createStatement();

        rs = dbos.executeQuery("values s1.lookupWageFootSoldierI(1002)");
        JDBC.assertSingleValueResultSet(rs, "100.0");

        dbos.close();
        dboConn.close();

        // ----------------------------------------
        // Now try with a table function

        c = openUserConnection("PHB");

        stm = c.createStatement();
        stm.executeUpdate("set role middlemanager");
        assertStatementError(
            new String[]{NOCOLUMNPERMISSION},
            stm,
            "select * from table (s1.selectFootSoldiersI()) as t");

        // Check that user and role is unchanged after failed call
        rs = stm.executeQuery(
            "values (current_user, session_user, current_role)");
        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"PHB", "PHB", "\"MIDDLEMANAGER\""}});

        stm.close();
        c.close();

    }

    public static double lookupWageFootSoldier(int employee)
            throws SQLException
    {
        Connection c = null;

        c = DriverManager.getConnection("jdbc:default:connection");
        Statement cStmt = c.createStatement();

        // Can only look up wages of foot soldiers
        ResultSet rs = cStmt.executeQuery(
            "select salary from s1.wages where employeeId=" +
            employee + " and category = " + FOOTSOLDIER);

        if (rs.next()) {
            double result = rs.getDouble(1);
            cStmt.close();
            rs.close();
            c.close();
            return result;
        } else {
            cStmt.close();
            rs.close();
            c.close();
            return -1.0;
        }
    }


    /**
     * Test that PHB can actually update using {@code ResultSet.insertRow},
     * {@code ResultSet.updateRow} and {@code ResultSet.deleteRow}.
     * <p/>
     * Aside: This test is somewhat artificial here, since the middle manager
     * would not be allowed to do this, presumably; just added here to test
     * this functionality (which was initially broken by the first patch for
     * DERBY-4551).
     * <p/>
     * The problem was that the nested statement contexts used for SQL
     * substatements generated for these ResultSet operations were not
     * correctly set up, so the effective user id would not be the DEFINER
     * (DBO), and authorization would fail. Cf DERBY-4551 and DERBY-3327
     * for more detail.
     */
    public static void updateWage()
            throws SQLException
    {
        Connection c = null;

        c = DriverManager.getConnection("jdbc:default:connection");
        Statement cStmt = c.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);

        // Try nested statements by inserting, updating and deleting a bogus
        // row
        ResultSet rs = cStmt.executeQuery(
            "select * from s1.wages");
        assertTrue(rs.isBeforeFirst());
        rs.moveToInsertRow();
        rs.updateInt("EMPLOYEEID", 666);
        rs.updateInt("CATEGORY", 667);
        rs.updateDouble("SALARY", 666.0);
        rs.updateString("NAME", "N.N.");
        rs.insertRow();
        rs.close();

        rs = cStmt.executeQuery(
            "select * from s1.wages where name = 'N.N.'");
        rs.next();
        rs.updateDouble("SALARY", 666.1);
        rs.updateRow();
        rs.close();

        rs = cStmt.executeQuery(
            "select * from s1.wages where name = 'N.N.'");
        rs.next();
        rs.deleteRow();
        rs.close();

        cStmt.close();
        c.close();
    }


    public static ResultSet selectFootSoldiers()
            throws SQLException
    {
        Connection c = null;

        c = DriverManager.getConnection("jdbc:default:connection");
        Statement cStmt = c.createStatement();

        // Can only look up wages of foot soldiers
        ResultSet rs = cStmt.executeQuery(
            "select * from s1.wages where category = " +
            FOOTSOLDIER);

        return rs;
    }


    public static double lookupDilbertWage()
            throws SQLException
    {
        Connection c = null;

        c = DriverManager.getConnection("jdbc:default:connection");
        Statement stm = c.createStatement();

        // We are executing as middle manager so we can look up Dilberts wage
        // for him.
        c.commit(); // Need to be no transaction active when we set role
        stm.executeUpdate("set role middlemanager");

        // Check that definer is PHB even though we created function as dbo.
        ResultSet rs = stm.executeQuery(
            "values (current_user, session_user, current_role)");

        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"PHB", "DILBERT", "\"MIDDLEMANAGER\""}});

        rs = stm.executeQuery(
            "select salary from table (s1.selectFootSoldiers()) as t " +
            "    where name='Dilbert'");
        rs.next();
        double result = rs.getDouble(1);

        rs.close();
        stm.close();
        c.close();

        return result;
    }


    public static void spTestBuiltins()
            throws SQLException
    {
        Connection c = null;

        c = DriverManager.getConnection("jdbc:default:connection");
        Statement cStmt = c.createStatement();

        ResultSet rs = cStmt.executeQuery(
            "values (user, current_user, current schema, " +
            "        session_user, current_role)");
        JDBC.assertFullResultSet(
            rs,
            new String[][]{{"TEST_DBO", "TEST_DBO", "TEST_DBO", "PHB", null}});

        cStmt.close();
        c.close();
    }
}