db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5329) Document who is allowed to run which system procedures/functions.
Date Thu, 14 Jul 2011 19:38:01 GMT

    [ 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

        

Mime
View raw message