| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.UDAPermsTestz |
| |
| 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 user-defined aggregates. See DERBY-672. |
| * </p> |
| */ |
| public class UDAPermsTest 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 TONY = "TONY"; |
| private static final String[] LEGAL_USERS = { TEST_DBO, ALICE, RUTH, FRANK, TONY }; |
| |
| private static final String MISSING_ROUTINE = "42Y03"; |
| private static final String IMPLICIT_CAST_ERROR = "42Y22"; |
| private static final String PARSE_ERROR = "42X01"; |
| private static final String BAD_DISTINCT = "42XAS"; |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // STATE |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * Create a new instance. |
| */ |
| |
| public UDAPermsTest(String name) |
| { |
| super(name); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = (BaseTestSuite)TestConfiguration.embeddedSuite( |
| UDAPermsTest.class); |
| |
| Test cleanTest = new CleanDatabaseTestSetup( suite ); |
| Test authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication |
| ( cleanTest, LEGAL_USERS, "udaPermissions" ); |
| Test authorizedTest = TestConfiguration.sqlAuthorizationDecorator( authenticatedTest ); |
| |
| return authorizedTest; |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * Test that you need USAGE privilege on an aggregate in order to invoke it. |
| * 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 an aggregate and table. |
| // |
| goodStatement |
| ( |
| ruthConnection, |
| "create derby aggregate mode_01 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table mode_inputs_01( a int, b int )\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into mode_inputs_01( a, b ) values ( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "grant select on mode_inputs_01 to public\n" |
| ); |
| |
| expectExecutionError |
| ( |
| aliceConnection, |
| LACK_USAGE_PRIV, |
| "select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a\n" |
| ); |
| expectExecutionError |
| ( |
| aliceConnection, |
| LACK_USAGE_PRIV, |
| "create view v_alice_01( a, modeOfA ) as select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a\n" |
| ); |
| |
| // |
| // The DBO however is almighty. |
| // |
| assertResults |
| ( |
| dboConnection, |
| "select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create view v_dbo_01( a, modeOfA ) as select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a\n" |
| ); |
| assertResults |
| ( |
| dboConnection, |
| "select * from v_dbo_01", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| |
| // |
| // Now grant USAGE on the user-defined aggregate. User Alice should now have all the |
| // privileges she needs. |
| // |
| goodStatement |
| ( |
| ruthConnection, |
| "grant usage on derby aggregate mode_01 to public\n" |
| ); |
| |
| assertResults |
| ( |
| aliceConnection, |
| "select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| |
| goodStatement |
| ( |
| aliceConnection, |
| "create view v_alice_01( a, modeOfA ) as select a, ruth.mode_01( b ) from ruth.mode_inputs_01 group by a\n" |
| ); |
| assertResults |
| ( |
| aliceConnection, |
| "select * from v_alice_01", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| 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 ); |
| |
| // |
| // Create an aggregate and table. |
| // |
| goodStatement |
| ( |
| ruthConnection, |
| "create derby aggregate mode_02 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table mode_inputs_02( a int, b int )\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "grant select on mode_inputs_02 to public\n" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into mode_inputs_02( a, b ) values ( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )\n" |
| ); |
| |
| // only RESTRICTed revocations allowed |
| expectCompilationError( ruthConnection, SYNTAX_ERROR, "revoke usage on derby aggregate mode_02 from ruth\n" ); |
| |
| // can't revoke USAGE from owner |
| expectCompilationError |
| ( |
| ruthConnection, |
| GRANT_REVOKE_NOT_ALLOWED, |
| "revoke usage on derby aggregate mode_02 from ruth restrict\n" |
| ); |
| |
| String grantUsage = "grant usage on derby aggregate mode_02 to alice\n"; |
| String revokeUsage = "revoke usage on derby aggregate mode_02 from alice restrict\n"; |
| String createStatement; |
| String dropStatement; |
| String badRevokeSQLState; |
| |
| // can't revoke USAGE if a view depends on it |
| createStatement = |
| "create view v_alice_02( a, modeOfA ) as select a, ruth.mode_02( b ) from ruth.mode_inputs_02 group by a" |
| ; |
| 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_source_02( a int )\n" ); |
| goodStatement( aliceConnection, "create table t_target_02( a int )\n" ); |
| createStatement = |
| "create trigger t_insert_trigger_02\n" + |
| "after insert on t_source_02\n" + |
| "for each row\n" + |
| "insert into t_target_02( a ) select ruth.mode_02( b ) from ruth.mode_inputs_02\n"; |
| dropStatement = "drop trigger t_insert_trigger_02\n"; |
| badRevokeSQLState = OPERATION_FORBIDDEN; |
| verifyRevokePrivilege |
| ( |
| ruthConnection, |
| aliceConnection, |
| grantUsage, |
| revokeUsage, |
| createStatement, |
| dropStatement, |
| badRevokeSQLState |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test that you need USAGE privilege on user-defined types in order to use them in |
| * user-defined aggregates. |
| * </p> |
| */ |
| public void test_003_typePrivs() |
| throws Exception |
| { |
| Connection ruthConnection = openUserConnection( RUTH ); |
| Connection aliceConnection = openUserConnection( ALICE ); |
| |
| // can't revoke USAGE on a type if an aggregate's input/return depends on it |
| goodStatement |
| ( |
| ruthConnection, |
| "create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| |
| String grantUsage = "grant usage on type Price to public"; |
| String revokeUsage = "revoke usage on type Price from public restrict"; |
| String createStatement = |
| "create derby aggregate priceMode for ruth.Price\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'\n"; |
| String dropStatement = "drop derby aggregate priceMode restrict"; |
| String badRevokeSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| |
| verifyRevokePrivilege |
| ( |
| ruthConnection, |
| aliceConnection, |
| grantUsage, |
| revokeUsage, |
| createStatement, |
| dropStatement, |
| badRevokeSQLState |
| ); |
| |
| // can't revoke USAGE on a type if an aggregate's input depends on it |
| goodStatement |
| ( |
| ruthConnection, |
| "create type Price_input external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create type Price_return external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "grant usage on type Price_return to public" |
| ); |
| |
| grantUsage = "grant usage on type Price_input to public"; |
| revokeUsage = "revoke usage on type Price_input from public restrict"; |
| createStatement = |
| "create derby aggregate priceMode for ruth.Price_input returns ruth.Price_return\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'\n"; |
| dropStatement = "drop derby aggregate priceMode restrict"; |
| badRevokeSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| |
| verifyRevokePrivilege |
| ( |
| ruthConnection, |
| aliceConnection, |
| grantUsage, |
| revokeUsage, |
| createStatement, |
| dropStatement, |
| badRevokeSQLState |
| ); |
| |
| // can't revoke USAGE on a type if an aggregate's return value depends on it |
| goodStatement |
| ( |
| ruthConnection, |
| "create type Price_input_2 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create type Price_return_2 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "grant usage on type Price_input_2 to public" |
| ); |
| |
| grantUsage = "grant usage on type Price_return_2 to public"; |
| revokeUsage = "revoke usage on type Price_return_2 from public restrict"; |
| createStatement = |
| "create derby aggregate priceMode for ruth.Price_input_2 returns ruth.Price_return_2\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'\n"; |
| dropStatement = "drop derby aggregate priceMode restrict"; |
| badRevokeSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| |
| verifyRevokePrivilege |
| ( |
| ruthConnection, |
| aliceConnection, |
| grantUsage, |
| revokeUsage, |
| createStatement, |
| dropStatement, |
| badRevokeSQLState |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test that we fixed an NPE in resolving function names when the |
| * schema hasn't been created yet. |
| * </p> |
| */ |
| public void test_004_emptySchema() |
| throws Exception |
| { |
| Connection tonyConnection = openUserConnection( TONY ); |
| |
| expectCompilationError( tonyConnection, MISSING_ROUTINE, "values toString( 100 )" ); |
| } |
| |
| /** |
| * <p> |
| * Test that anyone can run the modern, builtin system aggregates |
| * which implement org.apache.derby.agg.Aggregator. |
| * </p> |
| */ |
| public void test_005_builtinAggregators() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection ruthConnection = openUserConnection( RUTH ); |
| |
| createSchema_005( ruthConnection ); |
| |
| vetStatsBuiltins_005( dboConnection ); |
| vetStatsBuiltins_005( ruthConnection ); |
| |
| dropSchema_005( ruthConnection ); |
| } |
| private void vetStatsBuiltins_005( Connection conn ) |
| throws Exception |
| { |
| vetBuiltinAgg_005 |
| ( |
| conn, |
| "var_pop", |
| new String[][] { { "8.079999999999991" } }, |
| new String[][] { { "8.0" } } |
| ); |
| vetBuiltinAgg_005 |
| ( |
| conn, |
| "var_samp", |
| new String[][] { { "10.099999999999994" } }, |
| new String[][] { { "10.0" } } |
| ); |
| vetBuiltinAgg_005 |
| ( |
| conn, |
| "stddev_pop", |
| new String[][] { { "2.8425340807103776" } }, |
| new String[][] { { "2.8284271247461903" } } |
| ); |
| vetBuiltinAgg_005 |
| ( |
| conn, |
| "stddev_samp", |
| new String[][] { { "3.1780497164141397" } }, |
| new String[][] { { "3.1622776601683795" } } |
| ); |
| } |
| private void vetBuiltinAgg_005 |
| ( |
| Connection conn, |
| String aggName, |
| String[][] expectedInexactResults, |
| String[][] expectedExactResults |
| ) |
| throws Exception |
| { |
| vetBuiltinAgg_005( conn, aggName, "doubles", expectedInexactResults ); |
| vetBuiltinAgg_005( conn, aggName, "floats", expectedInexactResults ); |
| vetBuiltinAgg_005( conn, aggName, "bigints", expectedExactResults ); |
| vetBuiltinAgg_005( conn, aggName, "ints", expectedExactResults ); |
| vetBuiltinAgg_005( conn, aggName, "smallints", expectedExactResults ); |
| vetBuiltinNegative_005( conn, aggName ); |
| } |
| private void vetBuiltinAgg_005 |
| ( |
| Connection conn, |
| String aggName, |
| String tableName, |
| String[][] expectedResults |
| ) |
| throws Exception |
| { |
| assertResults |
| ( |
| conn, |
| "select " + aggName + "( a ) from ruth." + tableName, |
| expectedResults, |
| false |
| ); |
| } |
| private void vetBuiltinNegative_005 |
| ( |
| Connection conn, |
| String aggName |
| ) |
| throws Exception |
| { |
| // varchar can not be implicitly cast to double |
| expectCompilationError( conn, IMPLICIT_CAST_ERROR, |
| "select " + aggName + "( a ) from ruth.varchars" ); |
| |
| // cannot schema-qualify a builtin aggregate name |
| expectCompilationError( conn, MISSING_ROUTINE, |
| "select sys." + aggName + "( a ) from ruth.doubles" ); |
| |
| // cannot use ALL or DISTINCT with a builtin aggregate |
| expectCompilationError( conn, PARSE_ERROR, |
| "select " + aggName + "( all a ) from ruth.doubles" ); |
| expectCompilationError( conn, BAD_DISTINCT, |
| "select " + aggName + "( distinct a ) from ruth.doubles" ); |
| } |
| private void createSchema_005( Connection ruthConnection ) |
| throws Exception |
| { |
| goodStatement |
| ( |
| ruthConnection, |
| "create table doubles( a double )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into doubles values ( 1.2 ), ( 3.4 ), (5.6), (7.8), (9.0)" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table floats( a double )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into floats values ( 1.2 ), ( 3.4 ), (5.6), (7.8), (9.0)" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table bigints( a bigint )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into bigints values ( 1 ), ( 3 ), (5), (7), (9)" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table ints( a bigint )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into ints values ( 1 ), ( 3 ), (5), (7), (9)" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table smallints( a bigint )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into smallints values ( 1 ), ( 3 ), (5), (7), (9)" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "create table varchars( a varchar( 10 ) )" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "insert into varchars values ( '1' ), ( '3' ), ( '5' ), ( '7' ), ( '9' )" |
| ); |
| } |
| private void dropSchema_005( Connection ruthConnection ) |
| throws Exception |
| { |
| // drop schema |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table varchars" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table smallints" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table ints" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table bigints" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table floats" |
| ); |
| goodStatement |
| ( |
| ruthConnection, |
| "drop table doubles" |
| ); |
| } |
| } |
