[
https://issues.apache.org/jira/browse/DERBY-5329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13065493#comment-13065493
]
Rick Hillegas commented on DERBY-5329:
--------------------------------------
Hi Kim,
Experiments confirm the following:
Only the DBO can run the following routines:
syscs_util.syscs_set_user_access
syscs_util.syscs_get_user_access
syscs_util.syscs_empty_statement_cache
syscs_util.syscs_disable_log_archive_mode
syscs_util.syscs_reload_security_policy
syscs_util.syscs_set_xplain_schema
syscs_util.syscs_set_xplain_mode
syscs_util.syscs_get_xplain_mode
syscs_util.syscs_get_xplain_schema
Everyone can run the following routines:
syscs_util.syscs_set_runtimestatistics
syscs_util.syscs_update_statistics [ However, to run this procedure, you must
own the table it operates on. ]
The following script demonstrates the above statements. Or mostly does. There
is some problem with the XPLAIN STATISTICS routines which I don't understand
and for which I have logged DERBY-5335:
ij> connect
'jdbc:derby:memory:db;create=true;user=admin;password=adminpassword' as
admin_conn;
ij> create table admin_table( a int );
0 rows inserted/updated/deleted
ij> create index admin_index on admin_table( a );
0 rows inserted/updated/deleted
ij> -- just the dbo
call syscs_util.syscs_set_user_access ('BRUNNER', 'READONLYACCESS');
0 rows inserted/updated/deleted
ij> values syscs_util.syscs_get_user_access ('BRUNNER');
1
--------------------------------------------------------------------------------------------------------------------------------
READONLYACCESS
1 row selected
ij> call syscs_util.syscs_empty_statement_cache();
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_disable_log_archive_mode(0);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_reload_security_policy();
0 rows inserted/updated/deleted
ij> -- this fails right now. don't know why. see derby-5335
--call syscs_util.syscs_set_xplain_schema('ADMIN_STATS');
call syscs_util.syscs_set_xplain_mode(1);
0 rows inserted/updated/deleted
ij> -- these don't run right now either
--values syscs_util.syscs_get_xplain_mode();
--values syscs_util.syscs_get_xplain_schema();
-- everyone
call syscs_util.syscs_set_runtimestatistics(1);
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_update_statistics('ADMIN', 'ADMIN_TABLE', null);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:memory:db;user=alice;password=alicepassword' as
alice_conn;
ij(ALICE_CONN)> create table alice_table( a int );
0 rows inserted/updated/deleted
ij(ALICE_CONN)> create index alice_index on alice_table( a );
0 rows inserted/updated/deleted
ij(ALICE_CONN)> -- just the dbo
call syscs_util.syscs_set_user_access ('BRUNNER', 'READONLYACCESS');
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_SET_USER_ACCESS'.
ij(ALICE_CONN)> values syscs_util.syscs_get_user_access ('BRUNNER');
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_GET_USER_ACCESS'.
ij(ALICE_CONN)> call syscs_util.syscs_empty_statement_cache();
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_EMPTY_STATEMENT_CACHE'.
ij(ALICE_CONN)> call syscs_util.syscs_disable_log_archive_mode(0);
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_DISABLE_LOG_ARCHIVE_MODE'.
ij(ALICE_CONN)> call syscs_util.syscs_reload_security_policy();
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_RELOAD_SECURITY_POLICY'.
ij(ALICE_CONN)> call syscs_util.syscs_set_xplain_schema('ALICE_STATS');
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_SET_XPLAIN_SCHEMA'.
ij(ALICE_CONN)> call syscs_util.syscs_set_xplain_mode(1);
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_SET_XPLAIN_MODE'.
ij(ALICE_CONN)> values syscs_util.syscs_get_xplain_mode();
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_GET_XPLAIN_MODE'.
ij(ALICE_CONN)> values syscs_util.syscs_get_xplain_schema();
ERROR 42504: User 'ALICE' does not have EXECUTE permission on ROUTINE
'SYSCS_UTIL'.'SYSCS_GET_XPLAIN_SCHEMA'.
ij(ALICE_CONN)> -- everyone
call syscs_util.syscs_set_runtimestatistics(1);
0 rows inserted/updated/deleted
ij(ALICE_CONN)> call syscs_util.syscs_update_statistics('ALICE', 'ALICE_TABLE',
null);
0 rows inserted/updated/deleted
ij(ALICE_CONN)> -- fails because alice doesn't own the objects
call syscs_util.syscs_update_statistics('ADMIN', 'ADMIN_TABLE', null);
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'.
> 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
