Derby
  1. Derby
  2. DERBY-5329

Document who is allowed to run which system procedures/functions.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      Normal

      Description

      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.

      1. DERBY-5329.diff
        32 kB
        Kim Haase
      2. DERBY-5329.stat
        2 kB
        Kim Haase
      3. DERBY-5329.zip
        85 kB
        Kim Haase
      4. DERBY-5329-2.diff
        36 kB
        Kim Haase
      5. DERBY-5329-2.stat
        1 kB
        Kim Haase
      6. DERBY-5329-2.zip
        75 kB
        Kim Haase
      7. rrefaltertablecompress.html
        7 kB
        Kim Haase
      8. rrefstorejarinstall.html
        5 kB
        Kim Haase
      9. testPriv.log
        2 kB
        Dag H. Wanvik

        Activity

        Hide
        Kim Haase added a comment -

        Couple of sanity checks:

        I'm working from grantRevokeSpec_v5.html attached to DERBY-464, which seems to be the final version.

        Within that spec, I'm working from the table in the section "System Schemata, Functions, Procedures."

        The first row of that table mentions the SYSIBM procedures, which we don't document at all, so I'll ignore that.

        Please let me know if I'm off track anywhere. Thanks!

        Show
        Kim Haase added a comment - Couple of sanity checks: I'm working from grantRevokeSpec_v5.html attached to DERBY-464 , which seems to be the final version. Within that spec, I'm working from the table in the section "System Schemata, Functions, Procedures." The first row of that table mentions the SYSIBM procedures, which we don't document at all, so I'll ignore that. Please let me know if I'm off track anywhere. Thanks!
        Hide
        Rick Hillegas added a comment -

        Hi Kim,

        That sounds like the right spec to me. I agree that there's no need to expose the SYSIBM procedures as part of this issue. Thanks.

        Show
        Rick Hillegas added a comment - Hi Kim, That sounds like the right spec to me. I agree that there's no need to expose the SYSIBM procedures as part of this issue. Thanks.
        Hide
        Kim Haase added a comment -

        Thanks, Rick. I'm attaching rrefstorejarinstall.html to see if this language seems appropriate for the default case before I change it everywhere. I could add the sentence "No other privileges are needed" if that seems useful (except in the case of the 4 procedures where additional privileges are needed, of course).

        I'm also attaching rrefaltertablecompress.html because this is the table entry in the spec that puzzles me. The "Who has execute privilege by default" column has "Everyone" in it, but the "Other privileges needed" column says "Needs to be schema owner, pending DERBY-1062". DERBY-1062 has been fixed, but it's not clear to me how that fix affects these procedures.

        Do you have any suggestions for changes?

        Show
        Kim Haase added a comment - Thanks, Rick. I'm attaching rrefstorejarinstall.html to see if this language seems appropriate for the default case before I change it everywhere. I could add the sentence "No other privileges are needed" if that seems useful (except in the case of the 4 procedures where additional privileges are needed, of course). I'm also attaching rrefaltertablecompress.html because this is the table entry in the spec that puzzles me. The "Who has execute privilege by default" column has "Everyone" in it, but the "Other privileges needed" column says "Needs to be schema owner, pending DERBY-1062 ". DERBY-1062 has been fixed, but it's not clear to me how that fix affects these procedures. Do you have any suggestions for changes?
        Hide
        Rick Hillegas added a comment -

        Thanks for the sample edits, Kim. The rrefstorejarinstall edits look great to me.

        Concerning rrefaltertablecompress: I agree that DERBY-1062 doesn't affect what we should say here. I think it's worth pointing out that you need to be the schema owner in order to compress a table. The following script output 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'.

        I don't have any additional suggestions. The pattern you have established looks good to me. Thanks.

        Show
        Rick Hillegas added a comment - Thanks for the sample edits, Kim. The rrefstorejarinstall edits look great to me. Concerning rrefaltertablecompress: I agree that DERBY-1062 doesn't affect what we should say here. I think it's worth pointing out that you need to be the schema owner in order to compress a table. The following script output 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'. I don't have any additional suggestions. The pattern you have established looks good to me. Thanks.
        Hide
        Kim Haase added a comment -

        Thanks, Rick. So it's not the case that everyone has execute privileges on the table compression procedures – only the schema owner does?

        Show
        Kim Haase added a comment - Thanks, Rick. So it's not the case that everyone has execute privileges on the table compression procedures – only the schema owner does?
        Hide
        Rick Hillegas added a comment -

        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'.

        Show
        Rick Hillegas added a comment - 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'.
        Hide
        Kim Haase added a comment -

        Oh, I think I get it now ... any user can execute the procedure, but it won't actually work on a given table unless the user is either the database owner or the schema owner?

        Show
        Kim Haase added a comment - Oh, I think I get it now ... any user can execute the procedure, but it won't actually work on a given table unless the user is either the database owner or the schema owner?
        Hide
        Rick Hillegas added a comment -

        Exactly. Thanks.

        Show
        Rick Hillegas added a comment - Exactly. Thanks.
        Hide
        Kim Haase added a comment -

        Thanks for all the help!

        The spec was written in 2006, and some system routines have been added since then. What are the execute privileges for the following? I've made a few guesses.

        SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure
        SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function

        SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function (everyone? as for statistics ones)
        SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure (everyone? as for statistics ones)
        SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function (everyone? as for statistics ones)
        SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure (everyone? as for statistics ones)

        SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure (DBO?)
        SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure
        SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure
        SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure (everyone? as for other statistics ones)

        Thanks!

        Show
        Kim Haase added a comment - Thanks for all the help! The spec was written in 2006, and some system routines have been added since then. What are the execute privileges for the following? I've made a few guesses. SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure SYSCS_UTIL.SYSCS_GET_USER_ACCESS system function SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE system function (everyone? as for statistics ones) SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE system procedure (everyone? as for statistics ones) SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA system function (everyone? as for statistics ones) SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA system procedure (everyone? as for statistics ones) SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE system procedure (DBO?) SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE system procedure SYSCS_UTIL.SYSCS_RELOAD_SECURITY_POLICY system procedure SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure (everyone? as for other statistics ones) Thanks!
        Hide
        Rick Hillegas added a comment -

        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'.

        Show
        Rick Hillegas added a comment - 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'.
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5329.diff, DERBY-5329.stat, and DERBY-5329.zip, adding an "Execute privileges" section to each of the system routine topics, based on the information in the spec for DERBY-464 and Rick's updates.

        Also corrected a VARCHAR value in src/ref/rrefstorejarreplace.dita that I'd missed for DERBY-5238, and fixed an alphabetization mistake in the map file (see toc.html).

        The following files have changes:

        M src/ref/rrefbackupdbproc.dita
        M src/ref/rref_syscs_set_xplain_schema.dita
        M src/ref/rreffreezedbproc.dita
        M src/ref/rrefsyscschecktablefunc.dita
        M src/ref/rrefemptystmtcache.dita
        M src/ref/rrefgetuseraccess.dita
        M src/ref/rrefbackupdbenablelogproc.dita
        M src/ref/rrefsqlj38831.dita
        M src/ref/rrefcheckpointdbproc.dita
        M src/ref/rrefbackupdbenablelognowaitproc.dita
        M src/ref/rrefstorejarremove.dita
        M src/ref/rrefsetdbpropproc.dita
        M src/ref/rrefimportproc.dita
        M src/ref/rrefsetstatstimingproc.dita
        M src/ref/rrefimportdataproc.dita
        M src/ref/rrefdisablelogproc.dita
        M src/ref/rref_syscs_get_xplain_schema.dita
        M src/ref/rrefexportproctablelobs.dita
        M src/ref/rrefstorejarinstall.dita
        M src/ref/rrefupdatestatsproc.dita
        M src/ref/rrefgetdbpropfunc.dita
        M src/ref/rrefimportdataproclobs.dita
        M src/ref/rrefproceduresinplacecompress.dita
        M src/ref/rrefsyscsruntimestatsfunc.dita
        M src/ref/rref_syscs_get_xplain_mode.dita
        M src/ref/rrefstorejarreplace.dita
        M src/ref/rrefaltertablecompress.dita
        M src/ref/rrefimporttableproclobs.dita
        M src/ref/rrefexportselectionproclobs.dita
        M src/ref/rrefexportproc.dita
        M src/ref/rrefbackupdbnowaitproc.dita
        M src/ref/rrefreloadpolicyproc.dita
        M src/ref/rrefexportselectionproc.dita
        M src/ref/rref_syscs_set_xplain_mode.dita
        M src/ref/rrefsetuseraccess.dita
        M src/ref/rrefunfreezedbproc.dita
        M src/ref/refderby.ditamap

        Thanks for any comments.

        Show
        Kim Haase added a comment - Attaching DERBY-5329 .diff, DERBY-5329 .stat, and DERBY-5329 .zip, adding an "Execute privileges" section to each of the system routine topics, based on the information in the spec for DERBY-464 and Rick's updates. Also corrected a VARCHAR value in src/ref/rrefstorejarreplace.dita that I'd missed for DERBY-5238 , and fixed an alphabetization mistake in the map file (see toc.html). The following files have changes: M src/ref/rrefbackupdbproc.dita M src/ref/rref_syscs_set_xplain_schema.dita M src/ref/rreffreezedbproc.dita M src/ref/rrefsyscschecktablefunc.dita M src/ref/rrefemptystmtcache.dita M src/ref/rrefgetuseraccess.dita M src/ref/rrefbackupdbenablelogproc.dita M src/ref/rrefsqlj38831.dita M src/ref/rrefcheckpointdbproc.dita M src/ref/rrefbackupdbenablelognowaitproc.dita M src/ref/rrefstorejarremove.dita M src/ref/rrefsetdbpropproc.dita M src/ref/rrefimportproc.dita M src/ref/rrefsetstatstimingproc.dita M src/ref/rrefimportdataproc.dita M src/ref/rrefdisablelogproc.dita M src/ref/rref_syscs_get_xplain_schema.dita M src/ref/rrefexportproctablelobs.dita M src/ref/rrefstorejarinstall.dita M src/ref/rrefupdatestatsproc.dita M src/ref/rrefgetdbpropfunc.dita M src/ref/rrefimportdataproclobs.dita M src/ref/rrefproceduresinplacecompress.dita M src/ref/rrefsyscsruntimestatsfunc.dita M src/ref/rref_syscs_get_xplain_mode.dita M src/ref/rrefstorejarreplace.dita M src/ref/rrefaltertablecompress.dita M src/ref/rrefimporttableproclobs.dita M src/ref/rrefexportselectionproclobs.dita M src/ref/rrefexportproc.dita M src/ref/rrefbackupdbnowaitproc.dita M src/ref/rrefreloadpolicyproc.dita M src/ref/rrefexportselectionproc.dita M src/ref/rref_syscs_set_xplain_mode.dita M src/ref/rrefsetuseraccess.dita M src/ref/rrefunfreezedbproc.dita M src/ref/refderby.ditamap Thanks for any comments.
        Hide
        Rick Hillegas added a comment -

        Thanks for the patch, Kim. Looks great. +1

        Show
        Rick Hillegas added a comment - Thanks for the patch, Kim. Looks great. +1
        Hide
        Kim Haase added a comment -

        Thanks so much for the quick review, Rick!

        Committed patch DERBY-5329.diff to documentation trunk at revision 1147279.
        Merged to 10.8 doc branch at revision 1147295.

        Show
        Kim Haase added a comment - Thanks so much for the quick review, Rick! Committed patch DERBY-5329 .diff to documentation trunk at revision 1147279. Merged to 10.8 doc branch at revision 1147295.
        Hide
        Dag H. Wanvik added a comment -

        A small comment: Sorry for my late comment on this. The sentence in section "Execute privileges":

        "By default, only the database owner has execute privileges on this procedure." is not strictly accurate: Derby does not by default run with authorization enabled. Although this can perhaps be understood correctly by most users, it could possibly confuse others, so we might want to change it to "If SQL authorization is enabled (link), by default only the database owner has execute privileges on this procedure". What do you think?

        Show
        Dag H. Wanvik added a comment - A small comment: Sorry for my late comment on this. The sentence in section "Execute privileges": "By default, only the database owner has execute privileges on this procedure." is not strictly accurate: Derby does not by default run with authorization enabled. Although this can perhaps be understood correctly by most users, it could possibly confuse others, so we might want to change it to "If SQL authorization is enabled ( link ), by default only the database owner has execute privileges on this procedure". What do you think?
        Hide
        Kim Haase added a comment -

        Thanks, Dag, that's a very good point and worth reopening the issue for.

        I guess that if SQL authorization is not enabled, anyone can perform the operation?

        Show
        Kim Haase added a comment - Thanks, Dag, that's a very good point and worth reopening the issue for. I guess that if SQL authorization is not enabled, anyone can perform the operation?
        Hide
        Kim Haase added a comment -

        In the section currently linked to (the "Database owner" section in the "create=true attribute" topic), we say,

        "If authentication and SQL authorization are both enabled (see "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide), ..."

        Would that language be appropriate to add here?

        Show
        Kim Haase added a comment - In the section currently linked to (the "Database owner" section in the "create=true attribute" topic), we say, "If authentication and SQL authorization are both enabled (see "Enabling user authentication" and "Setting the SQL standard authorization mode" in the Derby Developer's Guide), ..." Would that language be appropriate to add here?
        Hide
        Dag H. Wanvik added a comment -

        The answer to the first question is "yes". On number two, technically, SQL authorization can be enabled without authentication in Derby, but it is rather pointless since all users can pretend to be anyone, including the DBO. I.e. authentication can be meaningfully used without SQL authorization, but the opposite is not true. I guess our present wording is a bit verbose (and technically wrong: for execution privileges one does need to at least pretend to be the dbo - by connecing as dbo), it makes more sense. So I suggest we continue to use what we currently have: "If authentication and SQL authorization are both enabled, ...".

        Show
        Dag H. Wanvik added a comment - The answer to the first question is "yes". On number two, technically, SQL authorization can be enabled without authentication in Derby, but it is rather pointless since all users can pretend to be anyone, including the DBO. I.e. authentication can be meaningfully used without SQL authorization, but the opposite is not true. I guess our present wording is a bit verbose (and technically wrong: for execution privileges one does need to at least pretend to be the dbo - by connecing as dbo), it makes more sense. So I suggest we continue to use what we currently have: "If authentication and SQL authorization are both enabled, ...".
        Hide
        Kim Haase added a comment -

        Thanks, Dag, I'm working on this.

        Does the "If authentication and SQL authorization are both enabled" condition apply to http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html and/or http://db.apache.org/derby/docs/dev/ref/rrefupdatestatsproc.html?

        Show
        Kim Haase added a comment - Thanks, Dag, I'm working on this. Does the "If authentication and SQL authorization are both enabled" condition apply to http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html and/or http://db.apache.org/derby/docs/dev/ref/rrefupdatestatsproc.html?
        Hide
        Dag H. Wanvik added a comment -

        > By default, all users have execute privileges on this procedure. However, in order for the procedure to run successfully on a given table, the user must be the owner of either the database or the schema in which the table resides.

        The restriction is subject to the same condition, yes. That is, if <a&a condition>, then the user must be the owner of either the database or the schema in which the table resides.

        But the first sentence complicates the exposition, I guess, since by default there are no privileges...

        Here is my attempt:

        If authentication and SQL authorization are both enabled, all users have execute privilege on the procedure, but for the operation to succeed the current user must also own the database or schema of the table being compressed.

        Show
        Dag H. Wanvik added a comment - > By default, all users have execute privileges on this procedure. However, in order for the procedure to run successfully on a given table, the user must be the owner of either the database or the schema in which the table resides. The restriction is subject to the same condition, yes. That is, if <a&a condition>, then the user must be the owner of either the database or the schema in which the table resides. But the first sentence complicates the exposition, I guess, since by default there are no privileges... Here is my attempt: If authentication and SQL authorization are both enabled, all users have execute privilege on the procedure, but for the operation to succeed the current user must also own the database or schema of the table being compressed.
        Hide
        Dag H. Wanvik added a comment -

        As for SYSCS_UPDATE_STATISTICS, similar change is required. But here I wonder: only the table owner is mention, doesn't the database owner also have the privilege to do update the statistics (not mentioned there as opposed to in place compress)? If so, that seems a bug to me either in the docs or the code...

        Show
        Dag H. Wanvik added a comment - As for SYSCS_UPDATE_STATISTICS, similar change is required. But here I wonder: only the table owner is mention, doesn't the database owner also have the privilege to do update the statistics (not mentioned there as opposed to in place compress)? If so, that seems a bug to me either in the docs or the code...
        Hide
        Kim Haase added a comment -

        Thanks, Dag, for those clarifications. I'm not sure about SYSCS_UPDATE_STATISTICS – that is what Rick reported in a previous comment based on his experiments, but I would think the DBO would have that privilege too. You are the authority on a&a, so I will trust your judgment on this.

        Show
        Kim Haase added a comment - Thanks, Dag, for those clarifications. I'm not sure about SYSCS_UPDATE_STATISTICS – that is what Rick reported in a previous comment based on his experiments, but I would think the DBO would have that privilege too. You are the authority on a&a, so I will trust your judgment on this.
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim. I'll run an experiment to verify that SYSCS_UPDATE_STATISTICS work as we expect, stay tuned.

        Show
        Dag H. Wanvik added a comment - Thanks, Kim. I'll run an experiment to verify that SYSCS_UPDATE_STATISTICS work as we expect, stay tuned.
        Hide
        Dag H. Wanvik added a comment -

        The dbo can in deed execute SYSCS_UTIL.SYSCS_UPDATE_STATISTICS on a table owned by a mere mortal, cf. attachment testPriv.log.

        Show
        Dag H. Wanvik added a comment - The dbo can in deed execute SYSCS_UTIL.SYSCS_UPDATE_STATISTICS on a table owned by a mere mortal, cf. attachment testPriv.log.
        Hide
        Kim Haase added a comment -

        Thanks, Dag! So it looks as if UPDATE_STATISTICS is similar to the compress routines – the user must be the owner of the database or the schema?

        Show
        Kim Haase added a comment - Thanks, Dag! So it looks as if UPDATE_STATISTICS is similar to the compress routines – the user must be the owner of the database or the schema?
        Hide
        Dag H. Wanvik added a comment -

        Affirmative!

        Show
        Dag H. Wanvik added a comment - Affirmative!
        Hide
        Kim Haase added a comment -

        Thanks for all your help, Dag. Attaching a second patch, DERBY-5329-2.diff, along with DERBY-5329-2.stat and DERBY-5329-2.zip, with changes to the following files:

        M src/ref/rrefbackupdbproc.dita
        M src/ref/rref_syscs_set_xplain_schema.dita
        M src/ref/rreffreezedbproc.dita
        M src/ref/rrefsyscschecktablefunc.dita
        M src/ref/rrefemptystmtcache.dita
        M src/ref/rrefgetuseraccess.dita
        M src/ref/rrefbackupdbenablelogproc.dita
        M src/ref/rrefcheckpointdbproc.dita
        M src/ref/rrefbackupdbenablelognowaitproc.dita
        M src/ref/rrefstorejarremove.dita
        M src/ref/rrefsetdbpropproc.dita
        M src/ref/rrefimportproc.dita
        M src/ref/rrefimportdataproc.dita
        M src/ref/rrefdisablelogproc.dita
        M src/ref/rref_syscs_get_xplain_schema.dita
        M src/ref/rrefexportproctablelobs.dita
        M src/ref/rrefstorejarinstall.dita
        M src/ref/rrefupdatestatsproc.dita
        M src/ref/rrefgetdbpropfunc.dita
        M src/ref/rrefimportdataproclobs.dita
        M src/ref/rrefproceduresinplacecompress.dita
        M src/ref/rref_syscs_get_xplain_mode.dita
        M src/ref/rrefstorejarreplace.dita
        M src/ref/rrefaltertablecompress.dita
        M src/ref/rrefimporttableproclobs.dita
        M src/ref/rrefexportselectionproclobs.dita
        M src/ref/rrefexportproc.dita
        M src/ref/rrefbackupdbnowaitproc.dita
        M src/ref/rrefreloadpolicyproc.dita
        M src/ref/rrefexportselectionproc.dita
        M src/ref/rref_syscs_set_xplain_mode.dita
        M src/ref/rrefsetuseraccess.dita
        M src/ref/rrefunfreezedbproc.dita

        Three of the statistics-related topics were in the previous patch but are not included in this patch because they did not change.

        Show
        Kim Haase added a comment - Thanks for all your help, Dag. Attaching a second patch, DERBY-5329 -2.diff, along with DERBY-5329 -2.stat and DERBY-5329 -2.zip, with changes to the following files: M src/ref/rrefbackupdbproc.dita M src/ref/rref_syscs_set_xplain_schema.dita M src/ref/rreffreezedbproc.dita M src/ref/rrefsyscschecktablefunc.dita M src/ref/rrefemptystmtcache.dita M src/ref/rrefgetuseraccess.dita M src/ref/rrefbackupdbenablelogproc.dita M src/ref/rrefcheckpointdbproc.dita M src/ref/rrefbackupdbenablelognowaitproc.dita M src/ref/rrefstorejarremove.dita M src/ref/rrefsetdbpropproc.dita M src/ref/rrefimportproc.dita M src/ref/rrefimportdataproc.dita M src/ref/rrefdisablelogproc.dita M src/ref/rref_syscs_get_xplain_schema.dita M src/ref/rrefexportproctablelobs.dita M src/ref/rrefstorejarinstall.dita M src/ref/rrefupdatestatsproc.dita M src/ref/rrefgetdbpropfunc.dita M src/ref/rrefimportdataproclobs.dita M src/ref/rrefproceduresinplacecompress.dita M src/ref/rref_syscs_get_xplain_mode.dita M src/ref/rrefstorejarreplace.dita M src/ref/rrefaltertablecompress.dita M src/ref/rrefimporttableproclobs.dita M src/ref/rrefexportselectionproclobs.dita M src/ref/rrefexportproc.dita M src/ref/rrefbackupdbnowaitproc.dita M src/ref/rrefreloadpolicyproc.dita M src/ref/rrefexportselectionproc.dita M src/ref/rref_syscs_set_xplain_mode.dita M src/ref/rrefsetuseraccess.dita M src/ref/rrefunfreezedbproc.dita Three of the statistics-related topics were in the previous patch but are not included in this patch because they did not change.
        Hide
        Dag H. Wanvik added a comment -

        +1, looks good to me.

        Show
        Dag H. Wanvik added a comment - +1, looks good to me.
        Hide
        Kim Haase added a comment -

        Thanks, Dag! I think we're all set now.

        Committed patch DERBY-5329-2.diff to documentation trunk at revision 1150806.
        Merged to 10.8 doc branch at revision 1150831.

        Show
        Kim Haase added a comment - Thanks, Dag! I think we're all set now. Committed patch DERBY-5329 -2.diff to documentation trunk at revision 1150806. Merged to 10.8 doc branch at revision 1150831.

          People

          • Assignee:
            Kim Haase
            Reporter:
            Rick Hillegas
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development