| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsPermsTest |
| |
| 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 generated columns. See DERBY-481. |
| * </p> |
| */ |
| public class GeneratedColumnsPermsTest extends GeneratedColumnsHelper |
| { |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTANTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| private static final String TEST_DBO = "TEST_DBO"; |
| private static final String JANET = "JANET"; |
| private static final String[] LEGAL_USERS = { TEST_DBO, JANET }; |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // STATE |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Create a new instance. |
| */ |
| |
| public GeneratedColumnsPermsTest(String name) |
| { |
| super(name); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = (BaseTestSuite)TestConfiguration.embeddedSuite( |
| GeneratedColumnsPermsTest.class); |
| |
| Test cleanTest = new CleanDatabaseTestSetup( suite ); |
| Test authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication |
| ( cleanTest, LEGAL_USERS, "generatedColumnsPermissions" ); |
| Test authorizedTest = TestConfiguration.sqlAuthorizationDecorator( authenticatedTest ); |
| |
| return authorizedTest; |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * Test that you need select/insert/update/delete privileges on a generated column and not just on |
| * the columns it references. |
| * </p> |
| */ |
| public void test_001_basicPermissions() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection janetConnection = openUserConnection( JANET ); |
| |
| // |
| // Verify correct behavior when have granted only SELECT and UPDATE on the referenced column. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "create table t_bp_1( a int, b int generated always as ( -a ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "insert into t_bp_1( a ) values ( 1 )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant select ( a ) on t_bp_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant update ( a ) on t_bp_1 to public" |
| ); |
| |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "insert into test_dbo.t_bp_1( a ) values ( 100 )" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_bp_1 set a = a+ 1" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "delete from test_dbo.t_bp_1 where a = 2" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_COLUMN_PRIV, |
| "select * from test_dbo.t_bp_1 order by a" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select a from test_dbo.t_bp_1 order by a", |
| new String[][] |
| { |
| { "2", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify correct behavior when we also grant SELECT on the generated column. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "grant select ( b ) on t_bp_1 to public" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "insert into test_dbo.t_bp_1( a ) values ( 100 )" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_bp_1 set a = a+ 1" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "delete from test_dbo.t_bp_1 where a = 2" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_bp_1 order by a", |
| new String[][] |
| { |
| { "3", "-3", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify correct behavior when we also grant UPDATE on the generated column. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "grant update ( b ) on t_bp_1 to public" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "insert into test_dbo.t_bp_1( a ) values ( 100 )" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_bp_1 set a = a+ 1" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "delete from test_dbo.t_bp_1 where a = 2" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_bp_1 order by a", |
| new String[][] |
| { |
| { "4", "-4", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify correct behavior when we also grant INSERT on the table. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "grant insert on t_bp_1 to public" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_bp_1( a ) values ( 100 )" |
| ); |
| expectExecutionError |
| ( |
| janetConnection, |
| LACK_TABLE_PRIV, |
| "delete from test_dbo.t_bp_1 where a = 2" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_bp_1 order by a", |
| new String[][] |
| { |
| { "4", "-4", }, |
| { "100", "-100", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify correct behavior when we also grant DELETE on the table. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "grant delete on t_bp_1 to public" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "delete from test_dbo.t_bp_1 where a = 4" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_bp_1 order by a", |
| new String[][] |
| { |
| { "100", "-100", }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test that you DON'T need execute privilege to run functions mentioned in |
| * generation clauses. That privilege is needed by the user who declared |
| * the generation clause. |
| * </p> |
| */ |
| public void test_002_functionPermissions() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection janetConnection = openUserConnection( JANET ); |
| |
| // |
| // Verify correct behavior when EXECUTE privilege is not granted. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "create function f_fp_minus\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create table t_fp_1( a int, b int generated always as ( test_dbo.f_fp_minus( a ) ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant update on t_fp_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant insert on t_fp_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant select on t_fp_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "insert into test_dbo.t_fp_1( a ) values ( 100 )" |
| ); |
| |
| goodStatement |
| ( |
| janetConnection, |
| "create function f_fp_minus\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'java.lang.Math.abs'\n" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_fp_1 set a = a + 1" |
| ); |
| |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_fp_1( a ) values ( 200 )" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fp_1 order by a", |
| new String[][] |
| { |
| { "101", "-101", }, |
| { "200", "-200", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify correct behavior when EXECUTE privilege is granted. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "grant execute on function f_fp_minus to public" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_fp_1 set a = a + 1" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_fp_1( a ) values ( 200 )" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fp_1 order by a", |
| new String[][] |
| { |
| { "102", "-102", }, |
| { "200", "-200", }, |
| { "201", "-201", }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test ddl that can only be issued when authorization is turned on. |
| * </p> |
| */ |
| public void test_003_ddl() |
| throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // |
| // Verify that current_role is not allowed in generation clauses. |
| // |
| expectCompilationError |
| ( |
| UNSTABLE_RESULTS, |
| "create table t_ddl_1( a int, b varchar( 128 ) generated always as ( current_role ) )" |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test that unqualified function references in generation clauses resolve |
| * to the current schema in effect when the generated column was added. |
| * See DERBY-3945. |
| * </p> |
| */ |
| public void test_004_functionSchema() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection janetConnection = openUserConnection( JANET ); |
| |
| // |
| // Schema. |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "create function f_fsch_1\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create table t_fsch_1( a int, b generated always as ( f_fsch_1( a ) ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create table t_fsch_2( a int )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "alter table t_fsch_2 add column b generated always as ( f_fsch_1( a ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant execute on function f_fsch_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant select on t_fsch_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant insert on t_fsch_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant update on t_fsch_1 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant select on t_fsch_2 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant insert on t_fsch_2 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant update on t_fsch_2 to public" |
| ); |
| |
| goodStatement |
| ( |
| janetConnection, |
| "create function f_fsch_1\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'java.lang.Math.abs'\n" |
| ); |
| |
| // |
| // Populate |
| // |
| goodStatement |
| ( |
| dboConnection, |
| "insert into test_dbo.t_fsch_1( a ) values ( 1 )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "insert into test_dbo.t_fsch_2( a ) values ( 2 )" |
| ); |
| |
| // |
| // Verify insert by other user |
| // |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_fsch_1( a ) values ( 1 )" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fsch_1 order by a", |
| new String[][] |
| { |
| { "1", "-1", }, |
| { "1", "-1", }, |
| }, |
| false |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_fsch_2( a ) values ( 2 )" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fsch_2 order by a", |
| new String[][] |
| { |
| { "2", "-2", }, |
| { "2", "-2", }, |
| }, |
| false |
| ); |
| |
| // |
| // Verify update by other user |
| // |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_fsch_1 set a = 100 + a" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fsch_1 order by a", |
| new String[][] |
| { |
| { "101", "-101", }, |
| { "101", "-101", }, |
| }, |
| false |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "update test_dbo.t_fsch_2 set a = 100 + a" |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.t_fsch_2 order by a", |
| new String[][] |
| { |
| { "102", "-102", }, |
| { "102", "-102", }, |
| }, |
| false |
| ); |
| |
| } |
| |
| /** |
| * <p> |
| * Test that unqualified function references in check constraints resolve |
| * to the current schema in effect when the constraint was declared. |
| * See DERBY-3944, which is related to the bug above, DERBY-3945. |
| * </p> |
| */ |
| public void test_005_functionSchema() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection janetConnection = openUserConnection( JANET ); |
| |
| goodStatement |
| ( |
| dboConnection, |
| "create function f_3944\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create table t_3944( a int, constraint t_3944_check check ( f_3944( a ) < 0 ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant insert on t_3944 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant execute on function f_3944 to public" |
| ); |
| |
| expectExecutionError |
| ( |
| janetConnection, |
| CONSTRAINT_VIOLATION, |
| "insert into test_dbo.t_3944( a ) values ( -100 )" |
| ); |
| goodStatement |
| ( |
| janetConnection, |
| "insert into test_dbo.t_3944( a ) values ( 200 )" |
| ); |
| assertResults |
| ( |
| dboConnection, |
| "select * from t_3944 order by a", |
| new String[][] |
| { |
| { "200", }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test that unqualified function references in views resolve |
| * to the view's schema. |
| * See DERBY-3953, which is related to the bugs above, DERBY-3944 and DERBY-3945. |
| * </p> |
| */ |
| public void test_006_functionSchema() |
| throws Exception |
| { |
| Connection dboConnection = openUserConnection( TEST_DBO ); |
| Connection janetConnection = openUserConnection( JANET ); |
| |
| goodStatement |
| ( |
| dboConnection, |
| "create function f_3953\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "create view v_3953( a, b ) as values ( f_3953( 1 ), f_3953( 2 ) )" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant select on v_3953 to public" |
| ); |
| goodStatement |
| ( |
| dboConnection, |
| "grant execute on function f_3953 to public" |
| ); |
| |
| goodStatement |
| ( |
| janetConnection, |
| "create function f_3953\n" + |
| "(\n" + |
| " a int\n" + |
| ")\n" + |
| "returns int\n" + |
| "language java\n" + |
| "deterministic\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'java.lang.Math.abs'\n" |
| ); |
| |
| assertResults |
| ( |
| janetConnection, |
| "values ( f_3953( 1 ), f_3953( 2 ) )", |
| new String[][] |
| { |
| { "1", "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| janetConnection, |
| "select * from test_dbo.v_3953", |
| new String[][] |
| { |
| { "-1", "-2" }, |
| }, |
| false |
| ); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // MINIONS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| } |
