Derby
  1. Derby
  2. DERBY-4551

Allow database user to execute stored procedures with same permissions as database owner and/or routine definer

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      Curretnly there is no way to hide data and database structure in embedded derby from the end user.

      One way to accomplish the above requirement is as follows:
      1. Create encrypted database so data is protected
      2. Enable authentication and sql authorization in database
      3. Create two users, dbUser and dbOwner
      4. Store application logic as stored procedure in the databse so dbUser does not know what tables are accecced by the application logic, thus hiding table structure
      5. Revoke select permission from dbUser so he cannot describe tables thus protecting table structures
      6. Give only Execute permissions on stored procedures to dbUser

      The above steps will ensure that data and data structure is hidden when application is delivered to end user.

      The problem is, if user does not have select permission, the stored procedures will not execute. So I am requesting the following enhancement to Derby:

      If dbOwner has given Execure permission to stored procecure to a dbUser, then allow stored procedure to execute even if the dbUser has no select permission.

      In otherwords, When dbUser calls stored procedure, database will use dbOwners authorization to execute stored procedure rather than dbUsers.

      This may be implemented by creating new permission called RunAsDbOwner.

      DbOwner can then grant permission to dbUser to execute a stored procedure with RunAsDbOwner.

      If this is implemented, applications can be created which will truely hide the database structure and data from end users. Database will behave as a blackbox with only in/out data exposed in stored procedures.

      1. definers_rights_typos-1.diff
        3 kB
        Kristian Waagan
      2. definers_rights.html
        26 kB
        Dag H. Wanvik
      3. definers_rights.html
        26 kB
        Dag H. Wanvik
      4. definers_rights.html
        25 kB
        Dag H. Wanvik
      5. definers_rights.html
        23 kB
        Dag H. Wanvik
      6. definers_rights.html
        22 kB
        Dag H. Wanvik
      7. definers_rights.html
        22 kB
        Dag H. Wanvik
      8. definers_rights.html
        18 kB
        Dag H. Wanvik
      9. derby-4551-1.diff
        76 kB
        Dag H. Wanvik
      10. derby-4551-1.stat
        3 kB
        Dag H. Wanvik
      11. derby-4551-1.txt
        4 kB
        Dag H. Wanvik
      12. derby-4551-2.diff
        83 kB
        Dag H. Wanvik
      13. derby-4551-2.stat
        3 kB
        Dag H. Wanvik
      14. derby-4551-3.diff
        87 kB
        Dag H. Wanvik
      15. derby-4551-3.stat
        3 kB
        Dag H. Wanvik
      16. derby-4551-3b.diff
        91 kB
        Dag H. Wanvik
      17. derby-4551-3b.stat
        4 kB
        Dag H. Wanvik
      18. derby-4551-4.diff
        92 kB
        Dag H. Wanvik
      19. derby-4551-4.stat
        4 kB
        Dag H. Wanvik
      20. derby-4551-followup-1a.diff
        5 kB
        Dag H. Wanvik
      21. derby-4551-followup-1a.stat
        0.1 kB
        Dag H. Wanvik
      22. derby-4551-followup-1b.diff
        9 kB
        Dag H. Wanvik
      23. derby-4551-followup-1b.stat
        0.2 kB
        Dag H. Wanvik
      24. derby4551-trial.diff
        4 kB
        Dag H. Wanvik
      25. reproTH-derby-4551.7z
        606 kB
        Thomas Hill

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Thomas Hill added a comment -

          Thanks for your reply and for sharing background on this not being part of the SQL standard. I fully understand the hesitation to implement and will use the work-around to pass the role name as a parameter then. Your assumption is right: I moved application level security and access control checking into the data base: for select operations by defining views that only return rows the user is allowed to see; for insert/update/delete operations (which in my set-up can only be performed by executing stored procedures the user is granted permission on) I would need to know inside the procedure which user called it and which role was set for the user before invoking the procedure.

          Show
          Thomas Hill added a comment - Thanks for your reply and for sharing background on this not being part of the SQL standard. I fully understand the hesitation to implement and will use the work-around to pass the role name as a parameter then. Your assumption is right: I moved application level security and access control checking into the data base: for select operations by defining views that only return rows the user is allowed to see; for insert/update/delete operations (which in my set-up can only be performed by executing stored procedures the user is granted permission on) I would need to know inside the procedure which user called it and which role was set for the user before invoking the procedure.
          Hide
          Dag H. Wanvik added a comment -

          You are right that there is no SESSION_ROLE to mirror SESSION_USER currently. Nor does the SQL standard have such a built-in. The only way would be to pass it in as a string parameter as you say. I think we would hesitate to add this since it's not defined by the standard, unless we had a really compelling reason to do so. It seems to me you would only need this is you wanted to implement some hand-crafted access control within the definer's rights method?

          Show
          Dag H. Wanvik added a comment - You are right that there is no SESSION_ROLE to mirror SESSION_USER currently. Nor does the SQL standard have such a built-in. The only way would be to pass it in as a string parameter as you say. I think we would hesitate to add this since it's not defined by the standard, unless we had a really compelling reason to do so. It seems to me you would only need this is you wanted to implement some hand-crafted access control within the definer's rights method?
          Hide
          Thomas Hill added a comment - - edited

          I hope it is okay to add comments to a resolved issue...
          One use case I just came accross is that I would need to know in the SQL code inside the procedure defined with External Security Definer the role that had been set for the session user prior to calling the procedure - so a "SESSION_ROLE" which implements for CURRENT_ROLE what SESSION_USER does for CURRENT_USER. Would this make sense to implement? or is there another way to achieve this? (besides passing the role to the procedure as a string parameter)

          Show
          Thomas Hill added a comment - - edited I hope it is okay to add comments to a resolved issue... One use case I just came accross is that I would need to know in the SQL code inside the procedure defined with External Security Definer the role that had been set for the session user prior to calling the procedure - so a "SESSION_ROLE" which implements for CURRENT_ROLE what SESSION_USER does for CURRENT_USER. Would this make sense to implement? or is there another way to achieve this? (besides passing the role to the procedure as a string parameter)
          Hide
          Thomas Hill added a comment -

          Dag, thanks for your prompt reply. Using an embedded URL connection syntax (and no ssl option) solved the problem. So yes, you helped me once again.

          Show
          Thomas Hill added a comment - Dag, thanks for your prompt reply. Using an embedded URL connection syntax (and no ssl option) solved the problem. So yes, you helped me once again.
          Hide
          Dag H. Wanvik added a comment - - edited

          You are correct that ssl is not necessary when one opens a connection from within the stored procedure (it is already inside the server).
          Since in 2) you are seeing Sockepermission errors, it seems you are using client/server URL connect syntax. You could try to use embedded URL connection syntax, i.e. "jdbc:derby:<dbname-possibly-incl-path>;user=<username>;password=<userpassword>", since there is no need to go out via a socket when you are already executing in the server. Does that help?

          http://db.apache.org/derby/docs/10.6/devguide/tdevdvlp12233.html

          Show
          Dag H. Wanvik added a comment - - edited You are correct that ssl is not necessary when one opens a connection from within the stored procedure (it is already inside the server). Since in 2) you are seeing Sockepermission errors, it seems you are using client/server URL connect syntax. You could try to use embedded URL connection syntax, i.e. "jdbc:derby:<dbname-possibly-incl-path>;user=<username>;password=<userpassword>", since there is no need to go out via a socket when you are already executing in the server. Does that help? http://db.apache.org/derby/docs/10.6/devguide/tdevdvlp12233.html
          Hide
          Thomas Hill added a comment -

          Would need advice on a related question:
          At this stage I have hard coded a connection string opening a new connection as 'dbo' into my stored procedures (i.e. implemented the work around as per Rick's advice) which I intend to remove once this feature becomes available. Recently I have moved my network server to an environment hosted by a java hoster on the internet. This network server uses SSL and authentication. SSL encryption is based on certificates which were self generated using the keytool utility. The network server is started with default/simple security policy and with references to the keystore file on the command line. I am able to connect to the server and issue SQL commands (e.g.selects, inserts) via IJ just fine when starting IJ via a reference to the clientkeystore file and connecting with ssl=basic. However when trying to call my stored procedures I am getting security errors.
          Questions:
          1) on my hard coded connection strings at the start of each procedure do I need to specify ssl=basic or is ssl not relevant in this context as the code is executed within the server? When trying the connect with ssl=basic I am getting 'access denied (java.util.PropertyPermission javax.net.ssl.keyStore write): java.security.AccessControlException' - my hoster tells me properties are set to allow read,write access, i.e. permission java.util.PropertyPermission "*","read,write" is in place;
          a) do I also need the java code System.setProperty("javax.net.ssl.keyStore", vcKeyStoreFileName) and System.setProperty("javax.net.ssl.keyStorePassword", vcKeyStorePassword) prior to the connect?; - tried this as well, but no success.

          2) if I ommit the ssl parameter completely, I am getting a different err: 'access denied (java.net.SocketPermission 127.0.0.1:31540 connect,resolve): java.security.AccessControlException', again the property file setting is permission java.net.SocketPermission "*","accept, connect, listen, resolve"

          Not sure what the right approach would be. Hope my explanation was clear enough and someone can offer some advice.
          Thanks

          Show
          Thomas Hill added a comment - Would need advice on a related question: At this stage I have hard coded a connection string opening a new connection as 'dbo' into my stored procedures (i.e. implemented the work around as per Rick's advice) which I intend to remove once this feature becomes available. Recently I have moved my network server to an environment hosted by a java hoster on the internet. This network server uses SSL and authentication. SSL encryption is based on certificates which were self generated using the keytool utility. The network server is started with default/simple security policy and with references to the keystore file on the command line. I am able to connect to the server and issue SQL commands (e.g.selects, inserts) via IJ just fine when starting IJ via a reference to the clientkeystore file and connecting with ssl=basic. However when trying to call my stored procedures I am getting security errors. Questions: 1) on my hard coded connection strings at the start of each procedure do I need to specify ssl=basic or is ssl not relevant in this context as the code is executed within the server? When trying the connect with ssl=basic I am getting 'access denied (java.util.PropertyPermission javax.net.ssl.keyStore write): java.security.AccessControlException' - my hoster tells me properties are set to allow read,write access, i.e. permission java.util.PropertyPermission "*","read,write" is in place; a) do I also need the java code System.setProperty("javax.net.ssl.keyStore", vcKeyStoreFileName) and System.setProperty("javax.net.ssl.keyStorePassword", vcKeyStorePassword) prior to the connect?; - tried this as well, but no success. 2) if I ommit the ssl parameter completely, I am getting a different err: 'access denied (java.net.SocketPermission 127.0.0.1:31540 connect,resolve): java.security.AccessControlException', again the property file setting is permission java.net.SocketPermission "*","accept, connect, listen, resolve" Not sure what the right approach would be. Hope my explanation was clear enough and someone can offer some advice. Thanks
          Hide
          Kristian Waagan added a comment -

          The current plan for the 10.7 release can be found here: http://wiki.apache.org/db-derby/DerbyTenSevenOneRelease

          Show
          Kristian Waagan added a comment - The current plan for the 10.7 release can be found here: http://wiki.apache.org/db-derby/DerbyTenSevenOneRelease
          Hide
          Thomas Hill added a comment -

          Thanks to you for the implementation! Looking forward to this new feature becoming available - and really hope it will make it into 10.7. and this release not being in a too distant future.

          Show
          Thomas Hill added a comment - Thanks to you for the implementation! Looking forward to this new feature becoming available - and really hope it will make it into 10.7. and this release not being in a too distant future.
          Hide
          Dag H. Wanvik added a comment -

          Good to know.Thanks for your continued help with testing this new feature!

          Show
          Dag H. Wanvik added a comment - Good to know.Thanks for your continued help with testing this new feature!
          Hide
          Thomas Hill added a comment -

          I have now recreated my stored procedures (~ 12 procs) with external security definer and used them to access and change data in base tables to which the user (/ role) I was logged in as has no access. Everything worked as expected. (Note that this test more likely tested the same conditions multiple times opposed to a structured testing which would aim at testing all possible conditions (at least) one time.)

          Show
          Thomas Hill added a comment - I have now recreated my stored procedures (~ 12 procs) with external security definer and used them to access and change data in base tables to which the user (/ role) I was logged in as has no access. Everything worked as expected. (Note that this test more likely tested the same conditions multiple times opposed to a structured testing which would aim at testing all possible conditions (at least) one time.)
          Hide
          Dag H. Wanvik added a comment -

          Thanks a lot for the help, Thomas! The more bugs we can get rid of before the release, the better!

          Show
          Dag H. Wanvik added a comment - Thanks a lot for the help, Thomas! The more bugs we can get rid of before the release, the better!
          Hide
          Thomas Hill added a comment -

          At this stage I can confirm the repro now works fine. I plan to do some further 'testing', i.e. to recreate all my stored procs with external security definer and run my application to see if, in this context, this works as expected as well.

          Show
          Thomas Hill added a comment - At this stage I can confirm the repro now works fine. I plan to do some further 'testing', i.e. to recreate all my stored procs with external security definer and run my application to see if, in this context, this works as expected as well.
          Hide
          Dag H. Wanvik added a comment - - edited

          Resolving this issue since I don't plan more work on it. Docs still remain, though.

          Show
          Dag H. Wanvik added a comment - - edited Resolving this issue since I don't plan more work on it. Docs still remain, though.
          Hide
          Dag H. Wanvik added a comment -

          Thomas, if you want to, you may pick up a testing binary which contains the fix by now. I would appreciate any feedback. Hopefully it should work now. Again, the latest daily jars we make for our regression testing here:http://dbtg.foundry.sun.com/derby/bits/trunk. Again, these are not for production usage and may be unstable. Pick one with s subversion number >999570.

          Show
          Dag H. Wanvik added a comment - Thomas, if you want to, you may pick up a testing binary which contains the fix by now. I would appreciate any feedback. Hopefully it should work now. Again, the latest daily jars we make for our regression testing here: http://dbtg.foundry.sun.com/derby/bits/trunk . Again, these are not for production usage and may be unstable. Pick one with s subversion number >999570.
          Hide
          Dag H. Wanvik added a comment -

          Committed patch derby-4551-followup-1b (plus some small hygiene adjustments) as svn 999570.

          Show
          Dag H. Wanvik added a comment - Committed patch derby-4551-followup-1b (plus some small hygiene adjustments) as svn 999570.
          Hide
          Dag H. Wanvik added a comment -

          Attaching a debugging patch I used to trace the sequence of active SQL session contexts and current users during execution, in case somebody (most likely myself needs to revisit this area in case of future bugs (derby4551-trial.diff).

          Show
          Dag H. Wanvik added a comment - Attaching a debugging patch I used to trace the sequence of active SQL session contexts and current users during execution, in case somebody (most likely myself needs to revisit this area in case of future bugs (derby4551-trial.diff).
          Hide
          Dag H. Wanvik added a comment - - edited

          Regressions ran OK.

          Uploading version followup-1b, which adds tests for the problem seen,
          cf. the new stored procedure with definer's rights:
          RoutinesDefinersRightsTest#updateWage. This procedure exercises
          ResultSet.insertRow, ResultSet.updateRow and ResultSet.deleteRow to
          check that their nested SQL statement run with the proper SQL session
          context. Rerunning regressions: [Update: ran OK].

          Ready for review.

          Show
          Dag H. Wanvik added a comment - - edited Regressions ran OK. Uploading version followup-1b, which adds tests for the problem seen, cf. the new stored procedure with definer's rights: RoutinesDefinersRightsTest#updateWage. This procedure exercises ResultSet.insertRow, ResultSet.updateRow and ResultSet.deleteRow to check that their nested SQL statement run with the proper SQL session context. Rerunning regressions: [Update: ran OK] . Ready for review.
          Hide
          Dag H. Wanvik added a comment - - edited

          Attaching a patch (followup-1a) which fixes the issue seen. It problem doesn't have
          to do with the implementation of definer's rights per se, but showed
          up in this case due to the feature's heavy dependence of the
          correctness of the SQL session context introduced in DERBY-3327.

          The problem here is that the substatement executed as part of
          ResultSet.

          {insertRow, updateRow,deleteRow}

          pushes a new statement
          context. This statement context is consulted when constructing the
          activation for the substatement, to see if the activation shall have a
          parent activation (which is used to get the correct SQL session
          context),
          cf. GenericLanguageConnectionContext#getCurrentSQLSessionContext.

          However, the newly pushed statement context was missing its parent's
          activation, so the substatement instead get the top level session
          context, whose current user is not the DEFINER (in this case instead
          of "DBO" it was "Thomas"), cf
          BaseActivation#setupSQLSessionContextForChildren, hence the
          authorization error.

          The patch makes sure the nested statement context initially gets the
          (new) parent context set.

          The repro now works with this patch. Running regression tests.

          The patch is not for commit, I need to add new regression tests for
          this case.

          Show
          Dag H. Wanvik added a comment - - edited Attaching a patch (followup-1a) which fixes the issue seen. It problem doesn't have to do with the implementation of definer's rights per se , but showed up in this case due to the feature's heavy dependence of the correctness of the SQL session context introduced in DERBY-3327 . The problem here is that the substatement executed as part of ResultSet. {insertRow, updateRow,deleteRow} pushes a new statement context. This statement context is consulted when constructing the activation for the substatement, to see if the activation shall have a parent activation (which is used to get the correct SQL session context), cf. GenericLanguageConnectionContext#getCurrentSQLSessionContext. However, the newly pushed statement context was missing its parent's activation, so the substatement instead get the top level session context, whose current user is not the DEFINER (in this case instead of "DBO" it was "Thomas"), cf BaseActivation#setupSQLSessionContextForChildren, hence the authorization error. The patch makes sure the nested statement context initially gets the (new) parent context set. The repro now works with this patch. Running regression tests. The patch is not for commit, I need to add new regression tests for this case.
          Hide
          Dag H. Wanvik added a comment -

          Thomas, there is definitely a bug here, thanks a lot for uncovering it and providing a good repro

          Show
          Dag H. Wanvik added a comment - Thomas, there is definitely a bug here, thanks a lot for uncovering it and providing a good repro
          Hide
          Dag H. Wanvik added a comment -

          Thomas, just to confirm, I can reproduce using your uploaded repro, thanks!

          Show
          Dag H. Wanvik added a comment - Thomas, just to confirm, I can reproduce using your uploaded repro, thanks!
          Hide
          Thomas Hill added a comment -

          Thanks Kristian for letting me know.

          Show
          Thomas Hill added a comment - Thanks Kristian for letting me know.
          Hide
          Kristian Waagan added a comment -

          Thanks for the repro, Thomas.

          I think Dag is on vacation (as many others in the community), so unless someone else feels the itch it may take a while before this issue is cleared up (sounds like more investigation is needed).

          Show
          Kristian Waagan added a comment - Thanks for the repro, Thomas. I think Dag is on vacation (as many others in the community), so unless someone else feels the itch it may take a while before this issue is cleared up (sounds like more investigation is needed).
          Hide
          Thomas Hill added a comment -

          repro from Thomas - see related comment

          Show
          Thomas Hill added a comment - repro from Thomas - see related comment
          Hide
          Thomas Hill added a comment - - edited

          I have now produced a repro (see file reproTH-derby-4551). Unfortunately all I can say (after quite some hours of testing) is, that my repro sometimes works (= user Thomas@000 can insert a new row into table rte."TBL_Clients" (on which he holds no permissions) via stored procedure rte."SP_addClient" created as DBO with external security definer and sometimes not (then receiving an error that user has no permission to update app."TBL_ApplIDs" from which I am fetching the next available client ID). Note: I have included a call to the stored procedure incrementing the ClientID into the addClient procedure as this is a concept I use for assigning next available values to IDs, knowing that in the repro nothing is done with the result returned. The zipped file also includes the database DB - selecting from rte."TBL_Clients" shows that the row was inserted by Thomas@000. I hope the scripts included in the repro to reproduce the situation are self-explanatory. Thanks again for your help and willingness to look into this.

          Show
          Thomas Hill added a comment - - edited I have now produced a repro (see file reproTH-derby-4551). Unfortunately all I can say (after quite some hours of testing) is, that my repro sometimes works (= user Thomas@000 can insert a new row into table rte."TBL_Clients" (on which he holds no permissions) via stored procedure rte."SP_addClient" created as DBO with external security definer and sometimes not (then receiving an error that user has no permission to update app."TBL_ApplIDs" from which I am fetching the next available client ID). Note: I have included a call to the stored procedure incrementing the ClientID into the addClient procedure as this is a concept I use for assigning next available values to IDs, knowing that in the repro nothing is done with the result returned. The zipped file also includes the database DB - selecting from rte."TBL_Clients" shows that the row was inserted by Thomas@000. I hope the scripts included in the repro to reproduce the situation are self-explanatory. Thanks again for your help and willingness to look into this.
          Hide
          Dag H. Wanvik added a comment -

          Yes, as far as I know. I'll wait with resolving it till we get Thomas' issue cleared up.

          Show
          Dag H. Wanvik added a comment - Yes, as far as I know. I'll wait with resolving it till we get Thomas' issue cleared up.
          Hide
          Kristian Waagan added a comment -

          Besides from the pending feedback from Thomas H. and any potential follow-up work, is the work for this issue complete?

          Show
          Kristian Waagan added a comment - Besides from the pending feedback from Thomas H. and any potential follow-up work, is the work for this issue complete?
          Hide
          Thomas Hill added a comment - - edited

          YES, I did specify EXTERNAL SECURITY DEFINER on the DDL to create the procedures.

          I will extract the relevant parts from my scripts used to create the data base and it's objects and double check / try again.
          If don't come accross anything I overlooked and still can't get it to work, I would make a repro and appreciate if you could have a look as offered then.

          Might take me a couple of days before coming back on this.

          Show
          Thomas Hill added a comment - - edited YES, I did specify EXTERNAL SECURITY DEFINER on the DDL to create the procedures. I will extract the relevant parts from my scripts used to create the data base and it's objects and double check / try again. If don't come accross anything I overlooked and still can't get it to work, I would make a repro and appreciate if you could have a look as offered then. Might take me a couple of days before coming back on this.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for test driving this, Thomas!

          It looks right to me. Did you remember to specify EXTERNAL SECURITY DEFINER in step 5?
          You may have a look at the tests I wrote for this feature, one of which which does essentially the same thing:

          http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutinesDefinersRightsTest.java?view=log

          If you still can't make it work, please try to make a repro, so we can have a look at it.

          Show
          Dag H. Wanvik added a comment - Thanks for test driving this, Thomas! It looks right to me. Did you remember to specify EXTERNAL SECURITY DEFINER in step 5? You may have a look at the tests I wrote for this feature, one of which which does essentially the same thing: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutinesDefinersRightsTest.java?view=log If you still can't make it work, please try to make a repro, so we can have a look at it.
          Hide
          Thomas Hill added a comment -

          I have tested using the daily build from 26th June. Regression testing, i.e. recreating all my database objects under 10.7.0.0 and embedding my current Java procedures in which I use a workaround of running a connect with hardcoded credentials of the data base owner at the beginning of each procedure (=> Connection conn = DriverManager.getConnection("jdbc:derby:DB1;bootPassword=xxxx;user=dbo;password=zzzz;") worked as expected, so everything continued to run fine. Then I have:
          1) recreated the procedures with External Security Definer,
          2) changed the Java code to re-use an existing connection (=> Connection conn = DriverManager.getConnection("jdbc:default:connection")
          3) created a 'normal' login/user without permissions on the (base) table,
          4) created a role 'data_updater',
          5) wrote a Java stored procedure to update the base table and created a corresponding stored procedure when being logged in as database owner,
          6) granted permission to execute the procedure to role 'data_updater',
          7) granted role 'data_updater' to my 'normal' application user
          8) logged in as the 'normal' application user
          9) set role to 'data_updater' and finally
          10) tried to run the procedure
          ==> I am getting a SQLException that the user does not have update permissions on the (first column in the) table.

          Can you please advise if you are spotting anything I have forgotten or done incorrectly as I was expecting step 10 to succeed with the new feature being available in this build?

          Show
          Thomas Hill added a comment - I have tested using the daily build from 26th June. Regression testing, i.e. recreating all my database objects under 10.7.0.0 and embedding my current Java procedures in which I use a workaround of running a connect with hardcoded credentials of the data base owner at the beginning of each procedure (=> Connection conn = DriverManager.getConnection("jdbc:derby:DB1;bootPassword=xxxx;user=dbo;password=zzzz;") worked as expected, so everything continued to run fine. Then I have: 1) recreated the procedures with External Security Definer, 2) changed the Java code to re-use an existing connection (=> Connection conn = DriverManager.getConnection("jdbc:default:connection") 3) created a 'normal' login/user without permissions on the (base) table, 4) created a role 'data_updater', 5) wrote a Java stored procedure to update the base table and created a corresponding stored procedure when being logged in as database owner, 6) granted permission to execute the procedure to role 'data_updater', 7) granted role 'data_updater' to my 'normal' application user 8) logged in as the 'normal' application user 9) set role to 'data_updater' and finally 10) tried to run the procedure ==> I am getting a SQLException that the user does not have update permissions on the (first column in the) table. Can you please advise if you are spotting anything I have forgotten or done incorrectly as I was expecting step 10 to succeed with the new feature being available in this build?
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Thomas. Instead of compiling yourself, you could pick up and test with the latest daily jars we make for our regression testing here:
          http://dbtg.foundry.sun.com/derby/bits/trunk. Again, these are not for production usage and may be unstable. This feature will be part of the 10.7 release, whenever that comes.

          Show
          Dag H. Wanvik added a comment - Thanks, Thomas. Instead of compiling yourself, you could pick up and test with the latest daily jars we make for our regression testing here: http://dbtg.foundry.sun.com/derby/bits/trunk . Again, these are not for production usage and may be unstable. This feature will be part of the 10.7 release, whenever that comes.
          Hide
          Thomas Hill added a comment -

          Dag, many thanks for your work on this! I am amazed how quickly this has been picked up and a solution put in place. I would like to test, but am not sure I will be able to download and compile a Derby version myself (have never done/tried something like that before).

          Show
          Thomas Hill added a comment - Dag, many thanks for your work on this! I am amazed how quickly this has been picked up and a solution put in place. I would like to test, but am not sure I will be able to download and compile a Derby version myself (have never done/tried something like that before).
          Hide
          Dag H. Wanvik added a comment -

          Committed a follow-up patch to address some Javadoc issues, svn 952284.

          Show
          Dag H. Wanvik added a comment - Committed a follow-up patch to address some Javadoc issues, svn 952284.
          Hide
          Dag H. Wanvik added a comment -

          Tushar & Thomas, if you want to test the new functionality you can download Derby trunk with subversion and compile it yourself. Note that you should not use those bits in production, since it's a snapshot of the development branch which has not been through QA.

          Show
          Dag H. Wanvik added a comment - Tushar & Thomas, if you want to test the new functionality you can download Derby trunk with subversion and compile it yourself. Note that you should not use those bits in production, since it's a snapshot of the development branch which has not been through QA.
          Hide
          Dag H. Wanvik added a comment -

          Committed derby-4551-4 as svn 952227.

          Show
          Dag H. Wanvik added a comment - Committed derby-4551-4 as svn 952227.
          Hide
          Dag H. Wanvik added a comment - - edited

          Uploading derby-4551-4, which

          • fixes Knut's nit
          • adds a test to check that multiple security clauses are caught, and updated sqlgrammar to include
            a string for the new error message parameter, which was missing.

          Regressions ran ok.

          Show
          Dag H. Wanvik added a comment - - edited Uploading derby-4551-4, which fixes Knut's nit adds a test to check that multiple security clauses are caught, and updated sqlgrammar to include a string for the new error message parameter, which was missing. Regressions ran ok.
          Hide
          Dag H. Wanvik added a comment - - edited

          Uploading version 1.2 of the specification for this feature:

          • fixed typos found by Krisitian, thanks!
          • added an item for doc additon to REVOKE statement.
          • added an item for doc addition to the user built-in functions.
          Show
          Dag H. Wanvik added a comment - - edited Uploading version 1.2 of the specification for this feature: fixed typos found by Krisitian, thanks! added an item for doc additon to REVOKE statement. added an item for doc addition to the user built-in functions.
          Hide
          Dag H. Wanvik added a comment -

          Thanks a lot, guys!

          @Kristian: 3 first items are all correctly observed. Item 4: I think we need to update the documentation on SESSION_USER,
          but I am not sure we need a release note, since the change in semantics is only observable with the new feature. Old usage (with invoker's rights only) will still not change behavior for SESSION_USER. I'll incorporate your typos, thx!

          @Knut: I'll fix the nit before I commit

          Show
          Dag H. Wanvik added a comment - Thanks a lot, guys! @Kristian: 3 first items are all correctly observed. Item 4: I think we need to update the documentation on SESSION_USER, but I am not sure we need a release note, since the change in semantics is only observable with the new feature. Old usage (with invoker's rights only) will still not change behavior for SESSION_USER. I'll incorporate your typos, thx! @Knut: I'll fix the nit before I commit
          Hide
          Knut Anders Hatlen added a comment -

          The changes in the patch look reasonable to me. One tiny nit: the added Boolean variable definersRights in sqlgrammar.jj appears to be unused.

          Show
          Knut Anders Hatlen added a comment - The changes in the patch look reasonable to me. One tiny nit: the added Boolean variable definersRights in sqlgrammar.jj appears to be unused.
          Hide
          Kristian Waagan added a comment -

          Hi Dag,

          I read through the specification. It was very clear, nicely written
          A few observations and comments (correct me if I got this wrong):
          o Derby will deviate from the standard for the default value of EXTERNAL SECURITY, this is done to avoid breaking existing applications.
          o There is an issue with REVOKE EXECUTE ... RESTRICT, which is proposed handled [for now] by relaxing the semantics of RESTRICT.
          o As far as I understand, no changes to the descriptors for existing routines will be performed on hard upgrade. They happen to already have the value meaning INVOKER.
          o Should there be a release note describing the changes made to SESSION_USER?
          The reference manual says: "USER, CURRENT_USER, and SESSION_USER are synonyms."

          I also attached 'definers_rights_typos-1.diff', which you may want to incorporate if you update the specification.

          Show
          Kristian Waagan added a comment - Hi Dag, I read through the specification. It was very clear, nicely written A few observations and comments (correct me if I got this wrong): o Derby will deviate from the standard for the default value of EXTERNAL SECURITY, this is done to avoid breaking existing applications. o There is an issue with REVOKE EXECUTE ... RESTRICT, which is proposed handled [for now] by relaxing the semantics of RESTRICT. o As far as I understand, no changes to the descriptors for existing routines will be performed on hard upgrade. They happen to already have the value meaning INVOKER. o Should there be a release note describing the changes made to SESSION_USER? The reference manual says: "USER, CURRENT_USER, and SESSION_USER are synonyms." I also attached 'definers_rights_typos-1.diff', which you may want to incorporate if you update the specification.
          Hide
          Dag H. Wanvik added a comment -

          I have not had any comment on the new patch for about a week, so I will commit this patch shortly.

          Show
          Dag H. Wanvik added a comment - I have not had any comment on the new patch for about a week, so I will commit this patch shortly.
          Hide
          Dag H. Wanvik added a comment -

          Revision 3 had some missing updates to the masters for dblook_test (DerbyNet, DerbyNetClient), so I upload a version that passed regressions: derby-4551-3b.

          Show
          Dag H. Wanvik added a comment - Revision 3 had some missing updates to the masters for dblook_test (DerbyNet, DerbyNetClient), so I upload a version that passed regressions: derby-4551-3b.
          Hide
          Dag H. Wanvik added a comment -

          Uploading rev 3 of the patch, which adds dblook support & tests, otherwise identical to rev 2.

          Show
          Dag H. Wanvik added a comment - Uploading rev 3 of the patch, which adds dblook support & tests, otherwise identical to rev 2.
          Hide
          Dag H. Wanvik added a comment -

          Uploading version 2 of this patch. The only difference relative version 1 is that more tests have been added, including:

          • test of 2 levels of routines:
          • invoker session, which calls
          • definer A's rights routine, employing a role to next call
          • definer B's rights routine
          • sanity checks that current user, role are reset correctly at return time
          • test of explicit invoker's rights syntax and behavior.

          If you have ideas of more tests for this feature, feel free to suggest them!
          Rerunning regressions.

          Show
          Dag H. Wanvik added a comment - Uploading version 2 of this patch. The only difference relative version 1 is that more tests have been added, including: test of 2 levels of routines: invoker session, which calls definer A's rights routine, employing a role to next call definer B's rights routine sanity checks that current user, role are reset correctly at return time test of explicit invoker's rights syntax and behavior. If you have ideas of more tests for this feature, feel free to suggest them! Rerunning regressions.
          Hide
          Dag H. Wanvik added a comment - - edited

          Thanks for looking at the patch, Rick!

          When a routine exits, any role set inside the routine will be popped back to whatever role (or none) was set when the call was invoked. This is true whether definer's or invoker's rights are used, as per the standard.

          In the implementation this is realized by popping the SQLSessionContext which was pushed at call time. The "old" SQLSessionContext (the one which was active when the call was invoked) is untouched during the invocation, and will still hold the "old" values of current user, current role and current default schema at return time.

          The new thing with the patch is that the current user is also held by the stack of SQLSessionContexts, making it possible to pop back from the defining user to the invoking user in a similar way at return time.

          Show
          Dag H. Wanvik added a comment - - edited Thanks for looking at the patch, Rick! When a routine exits, any role set inside the routine will be popped back to whatever role (or none) was set when the call was invoked. This is true whether definer's or invoker's rights are used, as per the standard. In the implementation this is realized by popping the SQLSessionContext which was pushed at call time. The "old" SQLSessionContext (the one which was active when the call was invoked) is untouched during the invocation, and will still hold the "old" values of current user, current role and current default schema at return time. The new thing with the patch is that the current user is also held by the stack of SQLSessionContexts, making it possible to pop back from the defining user to the invoking user in a similar way at return time.
          Hide
          Rick Hillegas added a comment -

          Thanks for the patch and description, Dag. I glanced at the changes and they look like they are covering a lot of cases. I was wondering about what happens in the following situation:

          o Alice invokes procedure Brad.P which executes with definer's rights.

          o Procedure Brad.P sets role to HR, does some work, then returns.

          o What is the role of Alice's session after the return?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the patch and description, Dag. I glanced at the changes and they look like they are covering a lot of cases. I was wondering about what happens in the following situation: o Alice invokes procedure Brad.P which executes with definer's rights. o Procedure Brad.P sets role to HR, does some work, then returns. o What is the role of Alice's session after the return? Thanks, -Rick
          Hide
          Dag H. Wanvik added a comment -

          Rick > By "real caller" you mean the user associated with the original stack frame, that is, the user whose credentials logged in the session?

          Yes, I have changed the wording now.

          Show
          Dag H. Wanvik added a comment - Rick > By "real caller" you mean the user associated with the original stack frame, that is, the user whose credentials logged in the session? Yes, I have changed the wording now.
          Hide
          Dag H. Wanvik added a comment -

          Uploading a first patch for this feature, which is intended to implement the specification as it currently stands.
          Please see detailed patch notes in derby-4551-1.txt. Regressions ran ok.

          Tests for the new feature are still only rudimentary.

          Show
          Dag H. Wanvik added a comment - Uploading a first patch for this feature, which is intended to implement the specification as it currently stands. Please see detailed patch notes in derby-4551-1.txt. Regressions ran ok. Tests for the new feature are still only rudimentary.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for your comments, Rick. Uploading a new version which addresses those + one more item, see rev. notes at top.

          Show
          Dag H. Wanvik added a comment - Thanks for your comments, Rick. Uploading a new version which addresses those + one more item, see rev. notes at top.
          Hide
          Dag H. Wanvik added a comment -

          Uploading version 1.0 of spec. Please review.

          Show
          Dag H. Wanvik added a comment - Uploading version 1.0 of spec. Please review.
          Hide
          Rick Hillegas added a comment -

          Thanks for the functional spec, Dag. Some small comments follow:

          Behavior

          o Some typos:

          "to Derby will retain" -> "so Derby will retain"

          "notwithstanding and lack" -> "notwithstanding a lack"

          o I did not understand the red paragraph. It talked about 2 different routines and it was hard for me to tell which routine was intended at various points in the paragraph.

          o By "real caller" you mean the user associated with the original stack frame, that is, the user whose credentials logged in the session?

          Security Considerations

          o Probably this feature widens the scope of SQL injection attacks since the procedure could construct statements based on its arguments.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the functional spec, Dag. Some small comments follow: Behavior o Some typos: "to Derby will retain" -> "so Derby will retain" "notwithstanding and lack" -> "notwithstanding a lack" o I did not understand the red paragraph. It talked about 2 different routines and it was hard for me to tell which routine was intended at various points in the paragraph. o By "real caller" you mean the user associated with the original stack frame, that is, the user whose credentials logged in the session? Security Considerations o Probably this feature widens the scope of SQL injection attacks since the procedure could construct statements based on its arguments. Thanks, -Rick
          Hide
          Dag H. Wanvik added a comment -

          Uploading new version of the spec draft, added a section on security and impl suggestion for RoutineAliasInfo.

          Show
          Dag H. Wanvik added a comment - Uploading new version of the spec draft, added a section on security and impl suggestion for RoutineAliasInfo.
          Hide
          Dag H. Wanvik added a comment -

          Updated spec with note on semantics of SESSION_USER as opposed to CURRENT_USER inside a routine running with definer's rights.

          Show
          Dag H. Wanvik added a comment - Updated spec with note on semantics of SESSION_USER as opposed to CURRENT_USER inside a routine running with definer's rights.
          Hide
          Dag H. Wanvik added a comment -

          Updated the spec with a new example which also shows the feature used with a table function.

          Show
          Dag H. Wanvik added a comment - Updated the spec with a new example which also shows the feature used with a table function.
          Hide
          Dag H. Wanvik added a comment -

          Uploading a draft functional specification for this feature.

          Show
          Dag H. Wanvik added a comment - Uploading a draft functional specification for this feature.
          Hide
          Dag H. Wanvik added a comment -

          Updated title, set component to SQL, reset Urgency field since that is reserved by dev team at triage time.

          Show
          Dag H. Wanvik added a comment - Updated title, set component to SQL, reset Urgency field since that is reserved by dev team at triage time.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Thomas. You can find the SQL standard drafts (very close to the real thing!) here:
          http://www.wiscorp.com/SQLStandards.html

          Show
          Dag H. Wanvik added a comment - Thanks, Thomas. You can find the SQL standard drafts (very close to the real thing!) here: http://www.wiscorp.com/SQLStandards.html
          Hide
          Thomas Hill added a comment -

          Although my requirement is not to hide data structure from the end user, being able to specify the security context in which SQL routines (stored procedures and functions) are executed would improve SQL standard compliance and allow a security layer to be implemented by which access to data (primarily inserts, updates, deletes / but also selects if needed) would only be possible via SQL routines. When designing database applications, one rarely wants users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures (which have all been created by / are owned by the database owner), and it is through the stored procedures application users can access and update data. The procedures perform validations of business rules to protect the integrity of the database. This concept would also work when accessing data with SQL Query tools like IJ.

          Raising questions about routine permission granting on the Derby mailing list, I was advised by Dag Wanvik that "Derby routines execute with the invoker's current privileges. SQL has a provision for defining routines to run with the definer's privileges as well, but this is not yet implemented in Derby. Feel free to file an improvement request!". As I found this existing request which I consider asking largerly for the same, I decided to add my comments here.

          Having looked at other data bases, to execute routines with the invoker's privileges seems to be the default commonly found (at least in MS SQL 2005 and PostgreSQL).
          Note: For SQL Server I found the SQL text from Erland Sommarskog published at http://www.sommarskog.se/grantperm.html very helpful. After having read this, it was sufficient to look at the create function statement syntax for PostgreSQL to understand how ownership chaining is implemented there. I would have liked to read the SQL standard on this, but am not sure where this can be found.
          However these databases (MS SQL, PostgreSQL) in contrast to Derby also allow executing routines with the definer's privilege.

          Two additional topics come to mind which I think need to be mentioned:
          1) As logging the user who manipulated the data is also a frequent requirement, there would also need to be a function made available which would unlike CURRENT_USER not return the name of the user under whos privileges the SQL is executed (i.e. the database owner), but the session user who is connected to the database / has called the routine (e.g. FredMeyer).
          2) Not sure if there is a dependency on schema privileges. I noted when testing on PostgreSQL that usage privileges need to be granted on the schema(s) - the grant statement in Derby has not option allowing to grant permissions on schemas.

          I will look into the workaround suggested by Rick above and might use it hoping for this request to receive attention and support from the community to get implemented soon. (Unfortunately I am not a programmer myself, so am afraid can't contribute more than raising the request and potentially writing documentation on it.

          Regards
          Thomas

          Show
          Thomas Hill added a comment - Although my requirement is not to hide data structure from the end user, being able to specify the security context in which SQL routines (stored procedures and functions) are executed would improve SQL standard compliance and allow a security layer to be implemented by which access to data (primarily inserts, updates, deletes / but also selects if needed) would only be possible via SQL routines. When designing database applications, one rarely wants users to have full permissions to access the tables in the database. Many applications are designed to perform all database access through stored procedures (which have all been created by / are owned by the database owner), and it is through the stored procedures application users can access and update data. The procedures perform validations of business rules to protect the integrity of the database. This concept would also work when accessing data with SQL Query tools like IJ. Raising questions about routine permission granting on the Derby mailing list, I was advised by Dag Wanvik that "Derby routines execute with the invoker's current privileges. SQL has a provision for defining routines to run with the definer's privileges as well, but this is not yet implemented in Derby. Feel free to file an improvement request!". As I found this existing request which I consider asking largerly for the same, I decided to add my comments here. Having looked at other data bases, to execute routines with the invoker's privileges seems to be the default commonly found (at least in MS SQL 2005 and PostgreSQL). Note: For SQL Server I found the SQL text from Erland Sommarskog published at http://www.sommarskog.se/grantperm.html very helpful. After having read this, it was sufficient to look at the create function statement syntax for PostgreSQL to understand how ownership chaining is implemented there. I would have liked to read the SQL standard on this, but am not sure where this can be found. However these databases (MS SQL, PostgreSQL) in contrast to Derby also allow executing routines with the definer's privilege. Two additional topics come to mind which I think need to be mentioned: 1) As logging the user who manipulated the data is also a frequent requirement, there would also need to be a function made available which would unlike CURRENT_USER not return the name of the user under whos privileges the SQL is executed (i.e. the database owner), but the session user who is connected to the database / has called the routine (e.g. FredMeyer). 2) Not sure if there is a dependency on schema privileges. I noted when testing on PostgreSQL that usage privileges need to be granted on the schema(s) - the grant statement in Derby has not option allowing to grant permissions on schemas. I will look into the workaround suggested by Rick above and might use it hoping for this request to receive attention and support from the community to get implemented soon. (Unfortunately I am not a programmer myself, so am afraid can't contribute more than raising the request and potentially writing documentation on it. Regards Thomas
          Hide
          Rick Hillegas added a comment -

          One workaround would be for the database procedure to create a connection using the database owner's credentials and do its work in that connection. The database procedure could be stored in a jar file in the database. The hard-coded credentials would be protected by the database-wide encryption. Hope this helps.

          Show
          Rick Hillegas added a comment - One workaround would be for the database procedure to create a connection using the database owner's credentials and do its work in that connection. The database procedure could be stored in a jar file in the database. The hard-coded credentials would be protected by the database-wide encryption. Hope this helps.

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Tushar Kale
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development