/*

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

   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 junit.framework.Test;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;

/**
 * <p>
 * Test permissions on UDTs. See DERBY-651.
 * </p>
 */
public class UDTPermsTest extends GeneratedColumnsHelper
{
    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTANTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    private static  final   String      TEST_DBO = "TEST_DBO";
    private static  final   String      RUTH = "RUTH";
    private static  final   String      ALICE = "ALICE";
    private static  final   String      FRANK = "FRANK";
    private static  final   String[]    LEGAL_USERS = { TEST_DBO, ALICE, RUTH, FRANK  };

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // STATE
    //
    ///////////////////////////////////////////////////////////////////////////////////

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // CONSTRUCTOR
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /**
     * Create a new instance.
     */

    public UDTPermsTest(String name)
    {
        super(name);
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // JUnit BEHAVIOR
    //
    ///////////////////////////////////////////////////////////////////////////////////


    /**
     * Construct top level suite in this JUnit test
     */
    public static Test suite()
    {
        BaseTestSuite suite = (BaseTestSuite)TestConfiguration.embeddedSuite(
            UDTPermsTest.class);

        Test        cleanTest = new CleanDatabaseTestSetup( suite );
        Test        authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication
            ( cleanTest, LEGAL_USERS, "udtPermissions" );
        Test        authorizedTest = TestConfiguration.sqlAuthorizationDecorator( authenticatedTest );

        return authorizedTest;
    }

    ///////////////////////////////////////////////////////////////////////////////////
    //
    // TESTS
    //
    ///////////////////////////////////////////////////////////////////////////////////

    /**
     * <p>
     * Test that you need USAGE privilege on a type in order to select columns
     * of that type and in order to declare objects which mention that type.
     * </p>
     */
    public  void    test_001_basicGrant()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );
        Connection  aliceConnection = openUserConnection( ALICE );

        //
        // Create a type, function, and table. make the function and table
        // public. Verify that they are still not generally usable because the
        // type is not public yet.
        //
        goodStatement
            (
             ruthConnection,
             "create type price_ruth_01_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"
             );
        goodStatement
            (
             ruthConnection,
             "create function makePrice_ruth_01( )\n" +
             "returns price_ruth_01_a\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"
             );
        goodStatement
            (
             ruthConnection,
             "create table t_ruth_01( a price_ruth_01_a )\n"
             );
        goodStatement
            (
             ruthConnection,
             "insert into t_ruth_01( a ) values ( makePrice_ruth_01() )\n"
             );
        goodStatement
            (
             ruthConnection,
             "grant select on t_ruth_01 to public\n"
             );
        goodStatement
            (
             ruthConnection,
             "grant execute on function makePrice_ruth_01 to public\n"
             );

        // should work fine. no USAGE priv required.
        goodStatement
            (
             ruthConnection,
             "select * from ruth.t_ruth_01\n"
             );
        goodStatement
            (
             ruthConnection,
             "values( ruth.makePrice_ruth_01() )\n"
             );
        
        expectExecutionError
            (
             aliceConnection,
             LACK_USAGE_PRIV,
             "create table t_alice_01( a ruth.price_ruth_01_a )\n"
             );
        expectExecutionError
            (
             aliceConnection,
             LACK_USAGE_PRIV,
             "create function makePrice_alice_01_a( )\n" +
             "returns ruth.price_ruth_01_a\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"
             );
        expectExecutionError
            (
             aliceConnection,
             LACK_USAGE_PRIV,
             "create function makePrice_alice_01_b( a ruth.price_ruth_01_a )\n" +
             "returns int\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"
             );

        //
        // The DBO however is almighty.
        //
        assertResults
            (
             dboConnection,
             "select * from ruth.t_ruth_01",
             new String[][]
             {
                 { "Price( USD, 1, XXX )" },
             },
             false
             );
        assertResults
            (
             dboConnection,
             "values( ruth.makePrice_ruth_01() )\n",
             new String[][]
             {
                 { "Price( USD, 1, XXX )" },
             },
             false
             );
        goodStatement
            (
             dboConnection,
             "create table t_dbo_01( a ruth.price_ruth_01_a )\n"
             );
        goodStatement
            (
             dboConnection,
             "insert into t_dbo_01( a ) values ( ruth.makePrice_ruth_01() )\n"
             );
        assertResults
            (
             dboConnection,
             "select * from t_dbo_01\n",
             new String[][]
             {
                 { "Price( USD, 1, XXX )" },
             },
             false
             );
        goodStatement
            (
             dboConnection,
             "drop table t_dbo_01\n"
             );

        //
        // Now grant USAGE on the type. User Alice should now have all the
        // privileges she needs.
        //
        goodStatement
            (
             ruthConnection,
             "grant usage on type price_ruth_01_a to public\n"
             );
        
        assertResults
            (
             aliceConnection,
             "select * from ruth.t_ruth_01",
             new String[][]
             {
                 { "Price( USD, 1, XXX )" },
             },
             false
             );
        goodStatement
            (
             aliceConnection,
             "create table t_alice_01( a ruth.price_ruth_01_a )\n"
             );
        goodStatement
            (
             aliceConnection,
             "insert into t_alice_01( a ) values ( ruth.makePrice_ruth_01() )\n"
             );
        assertResults
            (
             aliceConnection,
             "select * from t_alice_01\n",
             new String[][]
             {
                 { "Price( USD, 1, XXX )" },
             },
             false
             );

    }
    
   /**
     * <p>
     * Test that USAGE privilege can't be revoked if it would make objects
     * unusable by their owners.
     * </p>
     */
    public  void    test_002_basicRevoke()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  ruthConnection = openUserConnection( RUTH );
        Connection  aliceConnection = openUserConnection( ALICE );

        goodStatement
            (
             ruthConnection,
             "create type price_ruth_02_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"
             );

        // only RESTRICTed revocations allowed
        expectCompilationError( ruthConnection, SYNTAX_ERROR, "revoke usage on type price_ruth_02_a from ruth\n" );

        // can't revoke USAGE from owner
        expectCompilationError
            (
             ruthConnection,
             GRANT_REVOKE_NOT_ALLOWED,
             "revoke usage on type price_ruth_02_a from ruth restrict\n"
             );

        String grantUsage = "grant usage on type price_ruth_02_a to alice\n";
        String revokeUsage = "revoke usage on type price_ruth_02_a from alice restrict\n";
        String createStatement;
        String dropStatement;
        String badRevokeSQLState;
        
        // can't revoke USAGE if a routine depends on it
        createStatement =
             "create function makePrice_alice_02( )\n" +
             "returns ruth.price_ruth_02_a\n" +
             "language java\n" +
             "parameter style java\n" +
             "no sql\n" +
             "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"
            ;
        dropStatement = "drop function makePrice_alice_02\n";
        badRevokeSQLState = ROUTINE_DEPENDS_ON_TYPE;
        verifyRevokePrivilege
            (
             ruthConnection,
             aliceConnection,
             grantUsage,
             revokeUsage,
             createStatement,
             dropStatement,
             badRevokeSQLState
             );

        // can't revoke USAGE if a table depends on it
        createStatement = "create table t_alice_02( a ruth.price_ruth_02_a )\n";
        dropStatement = "drop table t_alice_02\n";
        badRevokeSQLState = TABLE_DEPENDS_ON_TYPE;
        verifyRevokePrivilege
            (
             ruthConnection,
             aliceConnection,
             grantUsage,
             revokeUsage,
             createStatement,
             dropStatement,
             badRevokeSQLState
             );

        // can't revoke USAGE if a view depends on it
        createStatement = "create view v_alice_02( a ) as select cast (null as ruth.price_ruth_02_a ) from sys.systables\n";
        dropStatement = "drop view v_alice_02\n";
        badRevokeSQLState = VIEW_DEPENDENCY;
        verifyRevokePrivilege
            (
             ruthConnection,
             aliceConnection,
             grantUsage,
             revokeUsage,
             createStatement,
             dropStatement,
             badRevokeSQLState
             );

        // can't revoke USAGE if a trigger depends on it
        goodStatement( aliceConnection, "create table t_03_a( a int )\n" );
        goodStatement( aliceConnection, "create table t_03_b( a int )\n" );
        createStatement =
            "create trigger trig_03_a after insert on t_03_a\n" +
            "  insert into t_03_b( a ) select a from t_03_a where ( cast( null as ruth.price_ruth_02_a ) ) is not null\n";
        dropStatement = "drop trigger trig_03_a\n";
        badRevokeSQLState = OPERATION_FORBIDDEN;
        verifyRevokePrivilege
            (
             ruthConnection,
             aliceConnection,
             grantUsage,
             revokeUsage,
             createStatement,
             dropStatement,
             badRevokeSQLState
             );
    }
    
  /**
     * <p>
     * Test that you can't drop a schema if it contains a UDT or routine.
     * </p>
     */
    public  void    test_003_dropSchema()
        throws Exception
    {
        Connection  dboConnection = openUserConnection( TEST_DBO );
        Connection  frankConnection = openUserConnection( FRANK );

        goodStatement
            ( frankConnection, "create function f_frank_03( a int ) returns int language java parameter style java no sql external name 'foo.bar.Wibble'\n" );
        expectExecutionError( dboConnection, NON_EMPTY_SCHEMA, "drop schema frank restrict\n" );

        goodStatement
            (frankConnection, "drop function f_frank_03\n" );
        goodStatement
            ( frankConnection, "create type price_frank_03_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" );
        expectExecutionError( dboConnection, NON_EMPTY_SCHEMA, "drop schema frank restrict\n" );

        goodStatement
            ( frankConnection, "drop type price_frank_03_a restrict\n" );
        goodStatement
            ( dboConnection, "drop schema frank restrict\n" );
    }
    
}