You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2008/11/14 21:53:44 UTC
[jira] Created: (DERBY-3949) 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.
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.
[jira] Commented: (DERBY-3949) 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.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Closed: (DERBY-3949) 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.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3949?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-3949.
--------------------------------
Resolution: Invalid
Closing this as "not an issue".
> 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.