[
https://issues.apache.org/jira/browse/DERBY-3949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12661720#action_12661720
]
Rick Hillegas commented on DERBY-3949:
--------------------------------------
The discussion on DERBY-481 has not found chapter and verse in the SQL standard
calling for this harsh behavior. The current behavior is that a user loses the
ability to insert/update data in a table if a generation clause in that table
depends on a role-based execute permission which is subsequently revoked. This
current behavior seems sensible to me. I am inclined to close this as "not an
issue" unless someone objects. Here is a script which demonstrates the current
behavior:
ij version 10.5
ij> connect
'jdbc:derby:derby10.5;create=true;user=test_dbo;password=test_dbopassword' as
dbo_conn;
ij> create function f_rp_minus
(
a int
)
returns int
language java
deterministic
parameter style java
no sql
external name
'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'
;
0 rows inserted/updated/deleted
ij> create role r_rp_1;
0 rows inserted/updated/deleted
ij> grant r_rp_1 to janet;
0 rows inserted/updated/deleted
ij> grant execute on function f_rp_minus to r_rp_1;
0 rows inserted/updated/deleted
ij> connect
'jdbc:derby:derby10.5;create=true;user=janet;password=janetpassword' as
janet_conn;
WARNING 01J01: Database 'derby10.5' not created, connection made to existing
database instead.
ij(JANET_CONN)> -- should fail
create table t_rp_1( a int, b generated always as ( test_dbo.f_rp_minus( a ) )
);
ERROR 42504: User 'JANET' does not have execute permission on FUNCTION
'TEST_DBO'.'F_RP_MINUS'.
ij(JANET_CONN)> -- should succeed
set role r_rp_1;
0 rows inserted/updated/deleted
ij(JANET_CONN)> create table t_rp_1( a int, b generated always as (
test_dbo.f_rp_minus( a ) ) );
0 rows inserted/updated/deleted
ij(JANET_CONN)> insert into t_rp_1( a ) values ( 1 );
1 row inserted/updated/deleted
ij(JANET_CONN)> select * from t_rp_1 order by a;
A |B
-----------------------
1 |-1
1 row selected
ij(JANET_CONN)> set connection dbo_conn;
ij(DBO_CONN)> revoke r_rp_1 from janet;
0 rows inserted/updated/deleted
ij(DBO_CONN)> set connection janet_conn;
ij(JANET_CONN)> -- should fail
insert into t_rp_1( a ) values ( 1 );
ERROR 42504: User 'JANET' does not have execute permission on FUNCTION
'TEST_DBO'.'F_RP_MINUS'.
ij(JANET_CONN)> -- should also fail
update t_rp_1 set a = 2 where a = 1;
ERROR 42504: User 'JANET' does not have execute permission on FUNCTION
'TEST_DBO'.'F_RP_MINUS'.
ij(JANET_CONN)> select * from t_rp_1 order by a;
A |B
-----------------------
1 |-1
1 row selected
ij(JANET_CONN)> drop table t_rp_1;
0 rows inserted/updated/deleted
ij(JANET_CONN)> set connection dbo_conn;
ij(DBO_CONN)> drop function f_rp_minus;
0 rows inserted/updated/deleted
> If a table owner loses a permission needed to run a function in a generation
> clause of one of her tables, then her table should be dropped.
> -------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3949
> URL: https://issues.apache.org/jira/browse/DERBY-3949
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.0.0
> Reporter: Rick Hillegas
>
> Dag raised a analogy to a similar situation in which views are dropped. This
> discussion took place on DERBY-481, starting at 12/Nov/08 09:49 AM.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
