Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 15 Jan 2014 17:56:19 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12684332.1386875874691.7579.1389808579678@arcas>
In-Reply-To: <JIRA.12684332.1386875874691@arcas>
References: <JIRA.12684332.1386875874691@arcas>
Subject: [jira] [Resolved] (DERBY-6432) INSERT/UPDATE incorrectly require
 user to have privilege to execute CHECK constraints on the target table.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-6432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas resolved DERBY-6432.
----------------------------------

       Resolution: Fixed
    Fix Version/s: 10.11.0.0

Fixed by work on DERBY-6434 and DERBY-6429.

> INSERT/UPDATE incorrectly require user to have privilege to execute CHECK constraints on the target table.
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6432
>                 URL: https://issues.apache.org/jira/browse/DERBY-6432
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>             Fix For: 10.11.0.0
>
>
> According to the discussion on DERBY-6429, CHECK constraints run under the aegis of the constraint owner and their privileges do not need to be checked when running INSERT/UPDATE statements. However, Derby requires the INSERTer/UPDATEr to have EXECUTE privilege on functions invoked by CHECK constraints and USAGE privilege on types mentioned by CHECK constraints.
> Hopefully, this bug will be fixed by the work on DERBY-6429.
> The following script shows this behavior:
> connect 'jdbc:derby:memory:db;user=test_dbo;create=true';
> call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' );
> call syscs_util.syscs_create_user( 'RUTH', 'ruthpassword' );
> -- bounce database to turn on authentication and authorization
> connect 'jdbc:derby:memory:db;shutdown=true';
> connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;
> -- schema
> create function absoluteValue( inputValue int ) returns int
> language java parameter style java deterministic no sql
> external name 'java.lang.Math.abs';
> create type hashmap external name 'java.util.HashMap' language java;
> create function makeHashMap() returns hashmap
> language java parameter style java no sql
> external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.makeHashMap';
> create table t1_check_function
> (
>     a int check ( absoluteValue( a ) > 100 )
> );
> create table t1_check_type
> (
>     a hashmap check( (a is not null) or (a is not null) )
> );
> -- data
> insert into t1_check_function( a ) values -101;
> insert into t1_check_type( a ) values ( makeHashMap() );
> -- privileges
> grant insert on t1_check_function to ruth;
> grant update on t1_check_function to ruth;
> grant insert on t1_check_type to ruth;
> grant update on t1_check_type to ruth;
> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth;
> -- incorrectly fails because ruth does not have EXECUTE privilege on absoluteValue()
> insert into test_dbo.t1_check_function values ( -102 );
> update test_dbo.t1_check_function set a = -103;
> -- incorrectly fails because ruth does not have USAGE privilege on hashmap
> insert into test_dbo.t1_check_type values ( null );
> update test_dbo.t1_check_type set a = null;


--
This message was sent by Atlassian JIRA
(v6.1.5#6160)