[
https://issues.apache.org/jira/browse/DERBY-5329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13065373#comment-13065373
]
Rick Hillegas commented on DERBY-5329:
--------------------------------------
Hi Kim. I can see this is a little tricky. Everyone has execute privilege on
the table compression procedures themselves. But to actually get something
done, you also have to be allowed to touch the table in question. So there are
two checks which are performed. To compress a table you must be one of the
following:
o The DBO (the DBO can compress all tables in the database)
o The schema owner.
Here's a run of a longer script which shows this:
ij> connect
'jdbc:derby:memory:db;create=true;user=admin;password=adminpassword' as
admin_conn;
ij> create table t( a int );
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_compress_table( 'ADMIN', 'T', 1 );
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:memory:db;user=alice;password=alicepassword' as
alice_conn;
ij(ALICE_CONN)> create table s( a int );
0 rows inserted/updated/deleted
ij(ALICE_CONN)> call syscs_util.syscs_compress_table( 'ALICE', 'S', 1 );
0 rows inserted/updated/deleted
ij(ALICE_CONN)> call syscs_util.syscs_compress_table( 'ADMIN', 'T', 1 );
ERROR 38000: The exception 'java.sql.SQLException: User 'ALICE' can not perform
the operation in schema 'ADMIN'.' was thrown while evaluating an expression.
ERROR 42507: User 'ALICE' can not perform the operation in schema 'ADMIN'.
ij(ALICE_CONN)> set connection admin_conn;
ij(ADMIN_CONN)> call syscs_util.syscs_compress_table( 'ALICE', 'S', 1 );
0 rows inserted/updated/deleted
ij(ADMIN_CONN)> set connection alice_conn;
ij(ALICE_CONN)> grant all privileges on s to public;
0 rows inserted/updated/deleted
ij(ALICE_CONN)> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword'
as ruth_conn;
ij(RUTH_CONN)> call syscs_util.syscs_compress_table( 'ALICE', 'S', 1 );
ERROR 38000: The exception 'java.sql.SQLException: User 'RUTH' can not perform
the operation in schema 'ALICE'.' was thrown while evaluating an expression.
ERROR 42507: User 'RUTH' can not perform the operation in schema 'ALICE'.
> Document who is allowed to run which system procedures/functions.
> -----------------------------------------------------------------
>
> Key: DERBY-5329
> URL: https://issues.apache.org/jira/browse/DERBY-5329
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.9.0.0
> Reporter: Rick Hillegas
> Assignee: Kim Haase
> Attachments: rrefaltertablecompress.html, rrefstorejarinstall.html
>
>
> The 5th functional spec attached to DERBY-464 contains a table describing
> which system procedures/functions can only be run by the DBO and which can be
> run by everyone. I can't find this information in our user guides. It would
> be good to copy this information into the Reference Guide topics for each of
> these procedures/functions.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
