Derby
  1. Derby
  2. DERBY-3095

CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(?, null) FAILS

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: None
    • Component/s: Documentation, JDBC, SQL
    • Labels:
      None
    • Environment:
      Linux 2.6.17-13mdv #1 SMP Fri Mar 23 15:18:36 EDT 2007 x86_64 AMD Athlon(tm) 64 Processor 3000+ GNU/Linux
    • Urgency:
      Normal
    • Bug behavior facts:
      Security

      Description

      Sorry to bother you again.

      CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(?, 'NOACCESS') FAILS and here's the stack trace :

      ******************************************************************************************
      java.sql.SQLException: Droit d'accès 'NOACCESS' inconnu.
      at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.execute(Unknown Source)
      at com.somecom.createUser(someAPP.java:190)
      at com.somecom.grantKeys(someAPP.java:288)
      at com.somecom.showGrantKeys(someAPP.java:269)
      at com.somecom.MDIMenuClicked(someAPP.java:620)
      at com.somecom.access$000(someAPP.java:15)
      at com.somecom$5.actionPerformed(someAPP.java:564)
      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
      at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
      at javax.swing.AbstractButton.doClick(AbstractButton.java:357)
      at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1216)
      at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1257)
      at java.awt.Component.processMouseEvent(Component.java:6038)
      at javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
      at java.awt.Component.processEvent(Component.java:5803)
      at java.awt.Container.processEvent(Container.java:2058)
      at java.awt.Component.dispatchEventImpl(Component.java:4410)
      at java.awt.Container.dispatchEventImpl(Container.java:2116)
      at java.awt.Component.dispatchEvent(Component.java:4240)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
      at java.awt.Container.dispatchEventImpl(Container.java:2102)
      at java.awt.Window.dispatchEventImpl(Window.java:2429)
      at java.awt.Component.dispatchEvent(Component.java:4240)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
      Caused by: org.apache.derby.client.am.SqlException: Droit d'accès 'NOACCESS' inconnu.
      at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
      at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source)
      at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source)
      at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source)
      at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
      at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source)
      ... 34 more
      *********************************************************************************************

      FULLACCESS : OK
      READONLYACCESS : OK
      NOACCESS : FAILURE

      By the way, the CONNECTION_PERMISSION parameter in the documentation is not up to date.

      Thank you for considering.

      1. Reproduce3095.zip
        1 kB
        EDAH-TALLY
      2. d3095.sql
        2 kB
        John H. Embretsen
      3. 3095_00.diff
        1 kB
        EDAH-TALLY
      4. REGTEST3095_00.zip
        2 kB
        EDAH-TALLY
      5. 3095_01.diff
        2 kB
        EDAH-TALLY
      6. REGTEST3095_01.zip
        2 kB
        EDAH-TALLY

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          Re-opening to close as "won't fix".

          Show
          Dag H. Wanvik added a comment - Re-opening to close as "won't fix".
          Hide
          Dyre Tjeldvoll added a comment -

          Removing Fix-version for unassigned issues

          Show
          Dyre Tjeldvoll added a comment - Removing Fix-version for unassigned issues
          Hide
          Dyre Tjeldvoll added a comment -

          This issue seems to use either 'existing application impact' or 'release note needed' incorrectly, according to the description at

          http://db.apache.org/derby/DerbyBugGuidelines.html#Set+appropriate+special+%22flags%22

          I plan to remove the flags shortly unless there are additional comments.

          Show
          Dyre Tjeldvoll added a comment - This issue seems to use either 'existing application impact' or 'release note needed' incorrectly, according to the description at http://db.apache.org/derby/DerbyBugGuidelines.html#Set+appropriate+special+%22flags%22 I plan to remove the flags shortly unless there are additional comments.
          Hide
          EDAH-TALLY added a comment -

          Sorry,

          Don't know where theis mail comes from. Didn't post it today.

          Show
          EDAH-TALLY added a comment - Sorry, Don't know where theis mail comes from. Didn't post it today.
          Hide
          EDAH-TALLY added a comment -

          OK, I'll try too as soon as I get some time.

          Show
          EDAH-TALLY added a comment - OK, I'll try too as soon as I get some time.
          Hide
          Daniel John Debrunner added a comment -

          Actually I think the point of confusion is based upon this:

          Ehad> from outside Derby, registering a user and wishing to remove him with the same identifier passed to SET_USER_ACCESS,

          I'm saying that these routines do not get passed a SQL identifier representing a user name, but instead they get passed a normal user name.
          This is because there is no way in SQL to pass in a SQL identifier to these routines, they get passed a VARCHAR value that represents a user name.

          Show
          Daniel John Debrunner added a comment - Actually I think the point of confusion is based upon this: Ehad> from outside Derby, registering a user and wishing to remove him with the same identifier passed to SET_USER_ACCESS, I'm saying that these routines do not get passed a SQL identifier representing a user name, but instead they get passed a normal user name. This is because there is no way in SQL to pass in a SQL identifier to these routines, they get passed a VARCHAR value that represents a user name.
          Hide
          EDAH-TALLY added a comment -

          It seems to me that we are looking at the problem from opposite ends.

          Me : from outside Derby, registering a user and wishing to remove him with the same identifier passed to SET_USER_ACCESS,

          You : from inside Derby, getting an identifier from an accass list and trying to match with one passed in by SET_USER_ACCESS.

          Well, in that case, we could misunderstand ourselves for long.

          Whatever method is used to correct this bug, I'll just wait as from now that Derby user management becomes more reliable.

          And I hope it gets fixed as Derby is a great tool.

          Till then, the workaround mentioned above does the job.

          Show
          EDAH-TALLY added a comment - It seems to me that we are looking at the problem from opposite ends. Me : from outside Derby, registering a user and wishing to remove him with the same identifier passed to SET_USER_ACCESS, You : from inside Derby, getting an identifier from an accass list and trying to match with one passed in by SET_USER_ACCESS. Well, in that case, we could misunderstand ourselves for long. Whatever method is used to correct this bug, I'll just wait as from now that Derby user management becomes more reliable. And I hope it gets fixed as Derby is a great tool. Till then, the workaround mentioned above does the job.
          Hide
          Daniel John Debrunner added a comment -

          > <>Note that the delimited identifier "EVE" represents the user EVE which is the same user as the regular identifiers eve, EVE and EvE</>

          > I didn't take this point into consideration, I therefore have to rethink the problem partly. It seems quite strange however and not really logical. It seems like saying "just pull the quotes and you're anyone else" !

          I think you are misunderstanding what I'm writing.

          User name EVE when represented as a SQL identifier can be any of: "EVE", EVE, eve, eVe (etc.). This is a single user with multiple ways to represent its value. It's the defined way that SQL works.

          User name eve when represented as a SQL identifier can only be "eve"
          User name eVe when represented as a SQL identifier can only be "eVe"
          etc.

          > Secondly, I think the identifier passed in by an app should be parsed the same way as individual users retrieved from an accesslist.

          I dont think that's correct. There are different domains where the representation of a user name takes a different form. An application has to understand that and pass in the appropriate form. Like the CURRENT USER example, an application might also take a user name from DatabaseMetaData.getUser() and pass that into a SQL routine, in which case no quotes will exist, the user name will be in its normal form (see the just added wiki page).

          For these routine it's important to note that the format of the user name being passed in does not match the format of the user name when set as a value of the associated Derby properties, the routine needs to correctly handle the conversion between them (which it isn't doing at the moment).

          Show
          Daniel John Debrunner added a comment - > <>Note that the delimited identifier "EVE" represents the user EVE which is the same user as the regular identifiers eve, EVE and EvE</> > I didn't take this point into consideration, I therefore have to rethink the problem partly. It seems quite strange however and not really logical. It seems like saying "just pull the quotes and you're anyone else" ! I think you are misunderstanding what I'm writing. User name EVE when represented as a SQL identifier can be any of: "EVE", EVE, eve, eVe (etc.). This is a single user with multiple ways to represent its value. It's the defined way that SQL works. User name eve when represented as a SQL identifier can only be "eve" User name eVe when represented as a SQL identifier can only be "eVe" etc. > Secondly, I think the identifier passed in by an app should be parsed the same way as individual users retrieved from an accesslist. I dont think that's correct. There are different domains where the representation of a user name takes a different form. An application has to understand that and pass in the appropriate form. Like the CURRENT USER example, an application might also take a user name from DatabaseMetaData.getUser() and pass that into a SQL routine, in which case no quotes will exist, the user name will be in its normal form (see the just added wiki page). For these routine it's important to note that the format of the user name being passed in does not match the format of the user name when set as a value of the associated Derby properties, the routine needs to correctly handle the conversion between them (which it isn't doing at the moment).
          Hide
          EDAH-TALLY added a comment -

          <>Note that the delimited identifier "EVE" represents the user EVE which is the same user as the regular identifiers eve, EVE and EvE</>

          I didn't take this point into consideration, I therefore have to rethink the problem partly. It seems quite strange however and not really logical. It seems like saying "just pull the quotes and you're anyone else" !

          During user deletion, the full user list is retrieved by SYSCS_GET_DATABASE_PROPERTY. It behaves simply :

          "eve" is retrieved as "eve"
          "EVE" as "EVE"
          "EvE" as "EvE"
          eve as eve
          EVE as EVE
          Eve as Eve ...

          <>Is that what your patch does?</>
          It does essentially 2 things :
          1. It prevents a quoted identifier to lose its quotes by a call to parseQId(...) from parseId(StringReader r, boolean normalize, boolean normalizeToUpper)
          2. It applies parsing and transformation to an application supplied identifier [ id in deleteId(String id, String list) ] .

          <>I can't really tell the behaviour you are trying to achieve</>
          I am trying to preserve a quoted identifier as quoted. If it loses its quotes, it is no longer the same user and can be anyone.
          Secondly, I think the identifier passed in by an app should be parsed the same way as individual users retrieved from an accesslist.

          Show
          EDAH-TALLY added a comment - <>Note that the delimited identifier "EVE" represents the user EVE which is the same user as the regular identifiers eve, EVE and EvE</> I didn't take this point into consideration, I therefore have to rethink the problem partly. It seems quite strange however and not really logical. It seems like saying "just pull the quotes and you're anyone else" ! During user deletion, the full user list is retrieved by SYSCS_GET_DATABASE_PROPERTY. It behaves simply : "eve" is retrieved as "eve" "EVE" as "EVE" "EvE" as "EvE" eve as eve EVE as EVE Eve as Eve ... <>Is that what your patch does?</> It does essentially 2 things : 1. It prevents a quoted identifier to lose its quotes by a call to parseQId(...) from parseId(StringReader r, boolean normalize, boolean normalizeToUpper) 2. It applies parsing and transformation to an application supplied identifier [ id in deleteId(String id, String list) ] . <>I can't really tell the behaviour you are trying to achieve</> I am trying to preserve a quoted identifier as quoted. If it loses its quotes, it is no longer the same user and can be anyone. Secondly, I think the identifier passed in by an app should be parsed the same way as individual users retrieved from an accesslist.
          Hide
          Daniel John Debrunner added a comment -

          I added an (incomplete) page to describe how user identifiers (should) work in Derby.

          http://wiki.apache.org/db-derby/UserIdentifiers

          Show
          Daniel John Debrunner added a comment - I added an (incomplete) page to describe how user identifiers (should) work in Derby. http://wiki.apache.org/db-derby/UserIdentifiers
          Hide
          Daniel John Debrunner added a comment -

          I agree there's a bug, I just want to ensure any change still works correctly when CURRENT_USER name is passed in.

          My assertion is that the username passed into these routines (without any modification) is equivalent to value of an username identifier in the SQL language after it has been parsed by the SQL engine. This is also known as "how it is stored in the SQL engine" [JDBC].

          VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('eVe'); – corresponds to the identifier "eVe"
          VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('EVE'); – corresponds to the identifer eve, EVE, "EVE" or EvE etc.

          Note that though how users names are represented in the derby.* properties may or may not match how a SQL engine would store them. For historical reasons in some (all?) cases the values in a derby.* property may be as a delimited identifier, e.g. "eVe".

          Note that the Derby builtin users are not created through a SQL identifier in the SQL language, e.g. in a CREATE USER statement. They are created by setting properties and are thus not "stored in the SQL engine".

          Is that what your patch does? From the comments in this jira entry and the code I can't really tell the behaviour you are trying to achieve.

          Show
          Daniel John Debrunner added a comment - I agree there's a bug, I just want to ensure any change still works correctly when CURRENT_USER name is passed in. My assertion is that the username passed into these routines (without any modification) is equivalent to value of an username identifier in the SQL language after it has been parsed by the SQL engine. This is also known as "how it is stored in the SQL engine" [JDBC] . VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('eVe'); – corresponds to the identifier "eVe" VALUES SYSCS_UTIL.SYSCS_GET_USER_ACCESS('EVE'); – corresponds to the identifer eve, EVE, "EVE" or EvE etc. Note that though how users names are represented in the derby.* properties may or may not match how a SQL engine would store them. For historical reasons in some (all?) cases the values in a derby.* property may be as a delimited identifier, e.g. "eVe". Note that the Derby builtin users are not created through a SQL identifier in the SQL language, e.g. in a CREATE USER statement. They are created by setting properties and are thus not "stored in the SQL engine". Is that what your patch does? From the comments in this jira entry and the code I can't really tell the behaviour you are trying to achieve.
          Hide
          EDAH-TALLY added a comment -

          Well, let's not forget the final aim of this issue.

          We should be able to safely delete a non quote delimited user with at least a lower-case character (eve, Eve, eVe...). It is not possible right now. A work-around is to pass the same identifier all in uppercase (EVE) to CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(CURRENT USER, NULL).

          We should be able to safely delete a quote delimited user ("eve", "eVe"...). It is not possible. And here it's worse as there is no work around.

          Such supposedly deleted users could wretch havoc in the database.

          Whatever concepts are in conflict, we only expect Derby to do the job.

          Show
          EDAH-TALLY added a comment - Well, let's not forget the final aim of this issue. We should be able to safely delete a non quote delimited user with at least a lower-case character (eve, Eve, eVe...). It is not possible right now. A work-around is to pass the same identifier all in uppercase (EVE) to CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(CURRENT USER, NULL). We should be able to safely delete a quote delimited user ("eve", "eVe"...). It is not possible. And here it's worse as there is no work around. Such supposedly deleted users could wretch havoc in the database. Whatever concepts are in conflict, we only expect Derby to do the job.
          Hide
          Daniel John Debrunner added a comment -

          In SQL (ie. the language) then "eve" is represented by eve. E.g. in a GRANT statement then "eve" and eve are two different users.

          Creating users in Derby is through setting properties which is not SQL, and may be inconsistent (see earlier comment).

          The documentation for setting users says the user name is "case-sensitive" and can be a delimited identifier, that seems strange, a horrible mix of two models.
          http://db.apache.org/derby/docs/dev/devguide/cdevcsecure21547.html

          That page implies that the first two will be different users and the first the same as the third, though I don't know what would happen if you set this up.

          derby.user.eve
          derby.user.EVE
          derby.user."eve"

          I thought there was a wiki page that had good examples for this, including how to pass in delimited names in JDBC. The context for that information was using e-mail addresses as user names, e.g. from an LDAP server. The @ sign can only exist in a delimited identifier.

          Show
          Daniel John Debrunner added a comment - In SQL (ie. the language) then "eve" is represented by eve. E.g. in a GRANT statement then "eve" and eve are two different users. Creating users in Derby is through setting properties which is not SQL, and may be inconsistent (see earlier comment). The documentation for setting users says the user name is "case-sensitive" and can be a delimited identifier, that seems strange, a horrible mix of two models. http://db.apache.org/derby/docs/dev/devguide/cdevcsecure21547.html That page implies that the first two will be different users and the first the same as the third, though I don't know what would happen if you set this up. derby.user.eve derby.user.EVE derby.user."eve" I thought there was a wiki page that had good examples for this, including how to pass in delimited names in JDBC. The context for that information was using e-mail addresses as user names, e.g. from an LDAP server. The @ sign can only exist in a delimited identifier.
          Hide
          EDAH-TALLY added a comment -

          <>"eve" is represented by eve</>

          It doesn't seem so.
          If we create a user as "eve" and then try to log in as eve, login fails.

          In the DB,

          "eve" is stored as "eve"
          eve is stored as eve

          eve is retrieved as eve then parsed AND transformed to EVE
          "eve" is retrieved as "eve" and parsed AND transformed to eve. There's no need to parse and transform a quote delimited identifier. If parsed, it should still be used with its quotes.

          Show
          EDAH-TALLY added a comment - <>"eve" is represented by eve</> It doesn't seem so. If we create a user as "eve" and then try to log in as eve, login fails. In the DB, "eve" is stored as "eve" eve is stored as eve eve is retrieved as eve then parsed AND transformed to EVE "eve" is retrieved as "eve" and parsed AND transformed to eve. There's no need to parse and transform a quote delimited identifier. If parsed, it should still be used with its quotes.
          Hide
          Daniel John Debrunner added a comment -

          >> Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ?
          > Yes, when in the SQL language.

          To be precise, regular identifiers are converted to their upper case form, not treated as case insensitive.

          Show
          Daniel John Debrunner added a comment - >> Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ? > Yes, when in the SQL language. To be precise, regular identifiers are converted to their upper case form, not treated as case insensitive.
          Hide
          Daniel John Debrunner added a comment -

          CURRENT USER represents the current user name which is a SQL identifier in its stored form, so that cannot be changed.

          > Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ?
          Yes, when in the SQL language.

          > BTW, EVE and eve can only coexist as "EVE" and "eve", or "EVE" and eve, or EVE and "eve".
          Not sure what this is trying to say. The rules for identifiers are:

          EVE, eVe, eve, "EVE" are all represented as EVE
          "eve" is represented by eve
          "eVe" is represented by eVe

          Those are three separate identifiers.

          Show
          Daniel John Debrunner added a comment - CURRENT USER represents the current user name which is a SQL identifier in its stored form, so that cannot be changed. > Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ? Yes, when in the SQL language. > BTW, EVE and eve can only coexist as "EVE" and "eve", or "EVE" and eve, or EVE and "eve". Not sure what this is trying to say. The rules for identifiers are: EVE, eVe, eve, "EVE" are all represented as EVE "eve" is represented by eve "eVe" is represented by eVe Those are three separate identifiers.
          Hide
          EDAH-TALLY added a comment - - edited

          You're right on this point.

          1. Perhaps we should abandon CURRENT USER as a SQL92Identifier, i.e, EVE and eve whether quoted or not should never refer to the same CURRENT USER.

          appendId(String id, String list) in IdUtil.java doesn't parse the identifier to be appended.
          deleteId(String id, String list) does this parsing with the on disk identifier which was not parsed and hence uppercased when it was appended.

          appenId saves eve.
          deleteId compares whatever input with EVE, and not eve.

          We may consider deleteId with this diff :
          if (!id.equals(enteredList_a[ix]))

          Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ?

          2. One more consideration :
          In parseId(StringReader r, boolean normalize, boolean normalizeToUpper)

          return parseQId(r,normalize);

          should perhaps be written as

          return parseQId(r,false);

          so that a quoted identifier doesn't lose its quotes. This way, what is really on disk can be compared to whatever is supplied from outside, with or without quotes.

          /\ BTW, EVE and eve can only coexist as "EVE" and "eve", or "EVE" and eve, or EVE and "eve". So modifying as per 2 above may be required together with modifying deleteId.

          Show
          EDAH-TALLY added a comment - - edited You're right on this point. 1. Perhaps we should abandon CURRENT USER as a SQL92Identifier, i.e, EVE and eve whether quoted or not should never refer to the same CURRENT USER. appendId(String id, String list) in IdUtil.java doesn't parse the identifier to be appended. deleteId(String id, String list) does this parsing with the on disk identifier which was not parsed and hence uppercased when it was appended. appenId saves eve. deleteId compares whatever input with EVE, and not eve. We may consider deleteId with this diff : if (!id.equals(enteredList_a [ix] )) Is it a fundamental requirement that any of Derby object be case insensitive unless specified by enclosed quotes ? 2. One more consideration : In parseId(StringReader r, boolean normalize, boolean normalizeToUpper) return parseQId(r,normalize); should perhaps be written as return parseQId(r,false); so that a quoted identifier doesn't lose its quotes. This way, what is really on disk can be compared to whatever is supplied from outside, with or without quotes. /\ BTW, EVE and eve can only coexist as "EVE" and "eve", or "EVE" and eve, or EVE and "eve". So modifying as per 2 above may be required together with modifying deleteId.
          Hide
          Daniel John Debrunner added a comment -

          I'm not sure this is a correct change.

          These are SQL routines, thus they take arguments from the SQL domain. This means the identifiers passed into the routines must have already been converted by the SQL engine. E.g. this call need to work as the user expects.

          CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(CURRENT USER, NULL)

          If there are two users EVE and eve and the CURRENT USER is eve, then this routine upper casing eve to EVE would be incorrect.

          This also matches how JDBC DatabaseMetaData methods treat SQL identifiers, e.g. getTables()

          • tableNamePattern - a table name pattern; must match the table name as it is stored in the database

          However, I do think Derby is inconsistent in this area, or potentially inconsistent. Some mechanisms to specify a SQL identifier outside of a SQL statement try to follow the SQL mechanism (using delimited identifiers "eve" for lower-case eve), and some follow the JDBC mechanism (identifier must match name as stored in database). I've never gone through all the SQL routines and other non-SQL ways to specify an identifier to see if there is a consistent pattern or not though.

          Show
          Daniel John Debrunner added a comment - I'm not sure this is a correct change. These are SQL routines, thus they take arguments from the SQL domain. This means the identifiers passed into the routines must have already been converted by the SQL engine. E.g. this call need to work as the user expects. CALL SYSCS_UTIL.SYSCS_SET_USER_ACCESS(CURRENT USER, NULL) If there are two users EVE and eve and the CURRENT USER is eve, then this routine upper casing eve to EVE would be incorrect. This also matches how JDBC DatabaseMetaData methods treat SQL identifiers, e.g. getTables() tableNamePattern - a table name pattern; must match the table name as it is stored in the database However, I do think Derby is inconsistent in this area, or potentially inconsistent. Some mechanisms to specify a SQL identifier outside of a SQL statement try to follow the SQL mechanism (using delimited identifiers "eve" for lower-case eve), and some follow the JDBC mechanism (identifier must match name as stored in database). I've never gone through all the SQL routines and other non-SQL ways to specify an identifier to see if there is a consistent pattern or not though.
          Hide
          EDAH-TALLY added a comment -

          Two attachements are uploaded :

          A diff file, which is slightly different to the above mentioned resolution, so as to be compliant to the SQL92Identifier nature of the USERNAME variable, w/r/t character casing requirements.

          A micro program to implement the resolution in freshly patched binaries. I do not know if it was what you meant by 'adding a regression test'. I prefer not to tweak myself AuthenticationTest.java as I am no Derby guru and can't go too far in Derby internals.

          Show
          EDAH-TALLY added a comment - Two attachements are uploaded : A diff file, which is slightly different to the above mentioned resolution, so as to be compliant to the SQL92Identifier nature of the USERNAME variable, w/r/t character casing requirements. A micro program to implement the resolution in freshly patched binaries. I do not know if it was what you meant by 'adding a regression test'. I prefer not to tweak myself AuthenticationTest.java as I am no Derby guru and can't go too far in Derby internals.
          Hide
          EDAH-TALLY added a comment -

          OK, ASAP

          Show
          EDAH-TALLY added a comment - OK, ASAP
          Hide
          Kathey Marsden added a comment -

          edah, are you interested in making up a patch and adding a regression test for this issue?

          Show
          Kathey Marsden added a comment - edah, are you interested in making up a patch and adding a regression test for this issue?
          Hide
          EDAH-TALLY added a comment - - edited

          I think the resolution is here :

          In file java/engine/org/apache/derby/iapi/util/IdUtil.java - function deleteId

          if (!id.equals(IdUtil.parseId(new StringReader(enteredList_a[ix]), false, false)))

          should be preferred to

          if (!id.equals(IdUtil.parseId(enteredList_a[ix])))

          In the latter case, IdUtil.parseId(enteredList_a[ix]) alxays normalize to uppercase and hence id in lower case will never equals IdUtil.parseId(enteredList_a[ix]).

          Tested and recompiled Derby, and it did the job.

          Please commit, someone with enough rights.

          Show
          EDAH-TALLY added a comment - - edited I think the resolution is here : In file java/engine/org/apache/derby/iapi/util/IdUtil.java - function deleteId if (!id.equals(IdUtil.parseId(new StringReader(enteredList_a [ix] ), false, false))) should be preferred to if (!id.equals(IdUtil.parseId(enteredList_a [ix] ))) In the latter case, IdUtil.parseId(enteredList_a [ix] ) alxays normalize to uppercase and hence id in lower case will never equals IdUtil.parseId(enteredList_a [ix] ). Tested and recompiled Derby, and it did the job. Please commit, someone with enough rights.
          Hide
          John H. Embretsen added a comment -

          Attaching an example sql script, d3095.sql, which confirms EDAH-TALLY's understanding of the actual behavior of the SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure.

          The script does the following:

          • user alice creates a database
          • authentication is turned on
          • alice defines two username/password pairs, one for herself and one for eve
          • alice gives herself and eve full access to the database
          • alice sets the default database access to "noAccess" and disconnects
          • eve connects and creates a table, then disconnects
          • alice tries to revokes eve's access (specifying the username 'eve')
          • eve is still in the list of fullAccess users, and is still able to connect and delete a table
          • alice again tries to revoke eve's access, now specifying the username in uppercase ('EVE')
          • eve is no longer in the list of fullAccess users, and is no longer able to connect

          The Dev guide for trunk, http://db.apache.org/derby/docs/dev/devguide/cdevcsecure24458.html, says:

          "When specifying which users are authorized to access the accounting database, you must list Fred's authorization identifier, FRED (which you can type as FRED, FREd, or fred, since the system automatically converts it to all-uppercase)."

          So it seems to me that there is a product bug in addition to the documentation bugs for SYSCS_UTIL.SYSCS_SET_USER_ACCESS and SYSCS_UTIL.SYSCS_GET_USER_ACCESS.

          Show
          John H. Embretsen added a comment - Attaching an example sql script, d3095.sql, which confirms EDAH-TALLY's understanding of the actual behavior of the SYSCS_UTIL.SYSCS_SET_USER_ACCESS system procedure. The script does the following: user alice creates a database authentication is turned on alice defines two username/password pairs, one for herself and one for eve alice gives herself and eve full access to the database alice sets the default database access to "noAccess" and disconnects eve connects and creates a table, then disconnects alice tries to revokes eve's access (specifying the username 'eve') eve is still in the list of fullAccess users, and is still able to connect and delete a table alice again tries to revoke eve's access, now specifying the username in uppercase ('EVE') eve is no longer in the list of fullAccess users, and is no longer able to connect The Dev guide for trunk, http://db.apache.org/derby/docs/dev/devguide/cdevcsecure24458.html , says: "When specifying which users are authorized to access the accounting database, you must list Fred's authorization identifier, FRED (which you can type as FRED, FREd, or fred, since the system automatically converts it to all-uppercase)." So it seems to me that there is a product bug in addition to the documentation bugs for SYSCS_UTIL.SYSCS_SET_USER_ACCESS and SYSCS_UTIL.SYSCS_GET_USER_ACCESS.
          Hide
          EDAH-TALLY added a comment -

          Please see Reproduce3095.zip which can reproduce the exception.

          My understanding of the problem is as follows :

          Derby creates users as SQL92Identifiers. The user name unless quoted is by default used in upper case when the user is added.
          This should have been the same when the user is removed from the system. It seems that the user name is used as supplied and is not converted to upper case. So deleting a user might fail and it does silently. The user is still in the fullAccess list !!

          It's a security issue that must be addressed.

          A workarround is to use

          cs.setString(1, userName.toUpperCase()); //DERBY-3095 ISSUE
          cs.setString(2, null);

          Show
          EDAH-TALLY added a comment - Please see Reproduce3095.zip which can reproduce the exception. My understanding of the problem is as follows : Derby creates users as SQL92Identifiers. The user name unless quoted is by default used in upper case when the user is added. This should have been the same when the user is removed from the system. It seems that the user name is used as supplied and is not converted to upper case. So deleting a user might fail and it does silently. The user is still in the fullAccess list !! It's a security issue that must be addressed. A workarround is to use cs.setString(1, userName.toUpperCase()); // DERBY-3095 ISSUE cs.setString(2, null);
          Hide
          EDAH-TALLY added a comment - - edited

          OK, but when I run with SQL NULL, the user is still not kicked off in the fullAccess list.

          The test in java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AuthenticationTest.java is passed however with SQL NULL.

          I'm using Derby in clent mode, SSL basic on, derby.connection.requireAuthentication true, derby.connection.sqlAuthorization true, derby.authentication.provider BUILTIN, derby.database.defaultConnectionMode noAccess, derby.database.propertiesOnly true. The DB has got the default owner APP and is encrypted on disk.

          The case goes this way :
          A user is created
          then is removed (no error up to here)
          then the same user is created again.

          This generates the following error :

          ***************************************************************************
          java.sql.SQLSyntaxErrorException: Le ou les utilisateurs '"SUPSEC"' sont répétés dans la liste d'accès 'derby.database.fullAccessUsers' ;
          at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
          at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.execute(Unknown Source)
          at com.somecom.createUser(someapp.java:192)
          at com.somecom.grantKeys(someapp.java:287)
          at com.somecom.showGrantKeys(someapp.java:271)
          at com.somecom.showGrantKeys(someapp.java:277)
          at com.somecom.MDIMenuClicked(someapp.java:622)
          at com.somecom.access$000(someapp.java:15)
          at com.somecom$5.actionPerformed(someapp.java:566)
          at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
          at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
          at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
          at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
          at javax.swing.AbstractButton.doClick(AbstractButton.java:357)
          at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1216)
          at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1257)
          at java.awt.Component.processMouseEvent(Component.java:6038)
          at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
          at java.awt.Component.processEvent(Component.java:5803)
          at java.awt.Container.processEvent(Container.java:2058)
          at java.awt.Component.dispatchEventImpl(Component.java:4410)
          at java.awt.Container.dispatchEventImpl(Container.java:2116)
          at java.awt.Component.dispatchEvent(Component.java:4240)
          at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
          at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
          at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
          at java.awt.Container.dispatchEventImpl(Container.java:2102)
          at java.awt.Window.dispatchEventImpl(Window.java:2429)
          at java.awt.Component.dispatchEvent(Component.java:4240)
          at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
          at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
          at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
          at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
          at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
          at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
          Caused by: org.apache.derby.client.am.SqlException: Le ou les utilisateurs '"SUPSEC"' sont répétés dans la liste d'accès 'derby.database.fullAccessUsers' ;
          at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source)
          at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source)
          at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source)
          at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source)
          ... 35 more
          *************************************************************************

          In java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AuthenticationTest.java,
          a user is created then deleted : no errors. But I'm not sure he is effectively deleted from the fullAccess list.

          About java.sql.SQLSyntaxErrorException, I'm definitely sure there's no syntax error in my code.

          Please leave the issue open for a few days till I have time to write a reproducible case for it.

          Show
          EDAH-TALLY added a comment - - edited OK, but when I run with SQL NULL, the user is still not kicked off in the fullAccess list. The test in java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AuthenticationTest.java is passed however with SQL NULL. I'm using Derby in clent mode, SSL basic on, derby.connection.requireAuthentication true, derby.connection.sqlAuthorization true, derby.authentication.provider BUILTIN, derby.database.defaultConnectionMode noAccess, derby.database.propertiesOnly true. The DB has got the default owner APP and is encrypted on disk. The case goes this way : A user is created then is removed (no error up to here) then the same user is created again. This generates the following error : *************************************************************************** java.sql.SQLSyntaxErrorException: Le ou les utilisateurs '"SUPSEC"' sont répétés dans la liste d'accès 'derby.database.fullAccessUsers' ; at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.PreparedStatement.execute(Unknown Source) at com.somecom.createUser(someapp.java:192) at com.somecom.grantKeys(someapp.java:287) at com.somecom.showGrantKeys(someapp.java:271) at com.somecom.showGrantKeys(someapp.java:277) at com.somecom.MDIMenuClicked(someapp.java:622) at com.somecom.access$000(someapp.java:15) at com.somecom$5.actionPerformed(someapp.java:566) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242) at javax.swing.AbstractButton.doClick(AbstractButton.java:357) at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1216) at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1257) at java.awt.Component.processMouseEvent(Component.java:6038) at javax.swing.JComponent.processMouseEvent(JComponent.java:3265) at java.awt.Component.processEvent(Component.java:5803) at java.awt.Container.processEvent(Container.java:2058) at java.awt.Component.dispatchEventImpl(Component.java:4410) at java.awt.Container.dispatchEventImpl(Container.java:2116) at java.awt.Component.dispatchEvent(Component.java:4240) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916) at java.awt.Container.dispatchEventImpl(Container.java:2102) at java.awt.Window.dispatchEventImpl(Window.java:2429) at java.awt.Component.dispatchEvent(Component.java:4240) at java.awt.EventQueue.dispatchEvent(EventQueue.java:599) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160) at java.awt.EventDispatchThread.run(EventDispatchThread.java:121) Caused by: org.apache.derby.client.am.SqlException: Le ou les utilisateurs '"SUPSEC"' sont répétés dans la liste d'accès 'derby.database.fullAccessUsers' ; at org.apache.derby.client.am.Statement.completeExecute(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source) at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source) at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source) ... 35 more ************************************************************************* In java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/AuthenticationTest.java, a user is created then deleted : no errors. But I'm not sure he is effectively deleted from the fullAccess list. About java.sql.SQLSyntaxErrorException, I'm definitely sure there's no syntax error in my code. Please leave the issue open for a few days till I have time to write a reproducible case for it.
          Hide
          Daniel John Debrunner added a comment -

          Sorry, yes the documentation has the wrong values, but this bug is still invalid because 'NOACCESS' is not supported for SYSCS_SET_USER_ACCESS.
          (see the description for DERBY-2735).

          'NOACCESS' can be returned for SYSCS_GET_USER_ACCESS.

          The asymmetry is there because the underlying functionality does not support setting NOACCESS for a specific user.

          jdbcapi.AuthenticationTest tests SYSCS_SET_USER_ACCESS with FULLACCESS, READONLYACCESS and NULL.

          Show
          Daniel John Debrunner added a comment - Sorry, yes the documentation has the wrong values, but this bug is still invalid because 'NOACCESS' is not supported for SYSCS_SET_USER_ACCESS. (see the description for DERBY-2735 ). 'NOACCESS' can be returned for SYSCS_GET_USER_ACCESS. The asymmetry is there because the underlying functionality does not support setting NOACCESS for a specific user. jdbcapi.AuthenticationTest tests SYSCS_SET_USER_ACCESS with FULLACCESS, READONLYACCESS and NULL.
          Hide
          EDAH-TALLY added a comment -

          Well, 'FULL', 'READONLY' or SQL NULL still do not work.

          Please have a look at

          http://issues.apache.org/jira/browse/DERBY-2735

          (by yourself)

          <quote>
          2) Values for the connection permission changed to match the property settiing explicitly

          NOACCESS
          FULLACCESS
          READONLYACCESS

          all case insensitive.
          </quote>

          I'll try to write a small reproducible case during the coming week-end.

          Show
          EDAH-TALLY added a comment - Well, 'FULL', 'READONLY' or SQL NULL still do not work. Please have a look at http://issues.apache.org/jira/browse/DERBY-2735 (by yourself) <quote> 2) Values for the connection permission changed to match the property settiing explicitly NOACCESS FULLACCESS READONLYACCESS all case insensitive. </quote> I'll try to write a small reproducible case during the coming week-end.
          Hide
          Daniel John Debrunner added a comment -

          The only valid options for the connection_permission are: 'FULL', 'READONLY' or SQL NULL.

          http://db.apache.org/derby/docs/dev/ref/rrefsetuseraccess.html

          Show
          Daniel John Debrunner added a comment - The only valid options for the connection_permission are: 'FULL', 'READONLY' or SQL NULL. http://db.apache.org/derby/docs/dev/ref/rrefsetuseraccess.html

            People

            • Assignee:
              Unassigned
              Reporter:
              EDAH-TALLY
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development