Derby
  1. Derby
  2. DERBY-866

Derby User Management Enhancements

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: 10.9.1.0
    • Component/s: Services
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Patch Available
    • Bug behavior facts:
      Security

      Description

      Proposal to enhance Derby's Built-In DDL User Management. (See proposal spec attached to the JIRA).

      Abstract:

      This feature aims at improving the way BUILT-IN users are managed in Derby by providing a more intuitive and familiar DDL interface. Currently (in 10.1.2.1), Built-In users can be defined at the system and/or database level. Users created at the system level can be defined via JVM or/and Derby system properties in the derby.properties file. Built-in users created at the database level are defined via a call to a Derby system procedure (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY) which sets a database property.

      Defining a user at the system level is very convenient and practical during the development phase (EOD) of an application - However, the user's password is not encrypted and consequently appears in clear in the derby.properties file. Hence, for an application going into production, whether it is embedded or not, it is preferable to create users at the database level where the password is encrypted.

      There is no real ANSI SQL standard for managing users in SQL but by providing a more intuitive and known interface, it will ease Built-In User management at the database level as well as Derby's adoption.

      1. UserManagement.html
        16 kB
        Rick Hillegas
      2. UserManagement.html
        22 kB
        Rick Hillegas
      3. UserManagement.html
        25 kB
        Rick Hillegas
      4. UserManagement.html
        31 kB
        Rick Hillegas
      5. UserManagement.html
        33 kB
        Rick Hillegas
      6. UserManagement.html
        34 kB
        Rick Hillegas
      7. UserManagement.html
        36 kB
        Rick Hillegas
      8. releaseNote.html
        4 kB
        Rick Hillegas
      9. releaseNote.html
        4 kB
        Rick Hillegas
      10. dummyCredentials.properties
        0.1 kB
        Rick Hillegas
      11. DummyAuthenticator.java
        3 kB
        Rick Hillegas
      12. derby-866-24-aa-dboMustTurnOnSecurity.diff
        5 kB
        Rick Hillegas
      13. derby-866-23-aa-improveErrorMessages.diff
        2 kB
        Rick Hillegas
      14. derby-866-22-aa-dboFirst.diff
        50 kB
        Rick Hillegas
      15. derby-866-21-ab-emptyCredentials.diff
        10 kB
        Rick Hillegas
      16. derby-866-21-aa-emptyCredentials.diff
        8 kB
        Rick Hillegas
      17. derby-866-20-ab-npeAndUserProbing.diff
        5 kB
        Rick Hillegas
      18. derby-866-20-aa-npeAndUserProbing.diff
        6 kB
        Rick Hillegas
      19. derby-866-19-aa-replicationTest.diff
        20 kB
        Rick Hillegas
      20. derby-866-18-aa-encryptedCredentialsDB.diff
        31 kB
        Rick Hillegas
      21. derby-866-17-aa-grantRevokeNative.diff
        6 kB
        Rick Hillegas
      22. derby-866-16-aa-credDBViaSubprotocol.diff
        15 kB
        Rick Hillegas
      23. derby-866-15-ae-dbInJarFileOrOnClasspath.diff
        34 kB
        Rick Hillegas
      24. derby-866-14-ac-badNativeSpec.diff
        13 kB
        Rick Hillegas
      25. derby-866-13-ab-systemWideOperationTests.diff
        14 kB
        Rick Hillegas
      26. derby-866-12-ac-passwordExpiration.diff
        23 kB
        Rick Hillegas
      27. derby-866-11-aa-upgradeTest.diff
        5 kB
        Rick Hillegas
      28. derby-866-10-ac-propChanging.diff
        30 kB
        Rick Hillegas
      29. derby-866-09-ae-nativeAuthenticationServiceWithTests.diff
        67 kB
        Rick Hillegas
      30. derby-866-09-ad-nativeAuthenticationService.diff
        40 kB
        Rick Hillegas
      31. derby-866-08-ad-passwordHasher.diff
        45 kB
        Rick Hillegas
      32. derby-866-08-ab-passwordHasher.diff
        40 kB
        Rick Hillegas
      33. derby-866-08-aa-passwordHasher.diff
        40 kB
        Rick Hillegas
      34. derby-866-07-aa-removeSQLPassword.diff
        45 kB
        Rick Hillegas
      35. derby-866-06-aa-upgradeFrom10.1.diff
        0.7 kB
        Rick Hillegas
      36. derby-866-05-aa-grantRevoke.diff
        2 kB
        Rick Hillegas
      37. derby-866-04-aa-fixRolesTest.diff
        2 kB
        Rick Hillegas
      38. derby-866-03-ab-resetModifyPassword.diff
        21 kB
        Rick Hillegas
      39. derby-866-03-aa-resetModifyPassword.diff
        20 kB
        Rick Hillegas
      40. derby-866-02-ag-createDropUser.diff
        68 kB
        Rick Hillegas
      41. derby-866-01-ab-sysusers.diff
        50 kB
        Rick Hillegas
      42. derby-866-01-aa-sysusers.diff
        48 kB
        Rick Hillegas
      43. Derby_User_Enhancement.html
        7 kB
        Francois Orsini
      44. Derby_User_Enhancement_v1.1.html
        8 kB
        Francois Orsini

        Issue Links

          Activity

          Hide
          Francois Orsini added a comment -

          Attached proposal specifications - please post comments in the JIRA itself.

          Show
          Francois Orsini added a comment - Attached proposal specifications - please post comments in the JIRA itself.
          Hide
          Francois Orsini added a comment -

          Hi Satheesh,

          Thanks for the initial feedback. Please find some answers enclosed below.

          On 1/24/06, Satheesh Bandaram wrote:
          >
          > Hi Francois,
          >
          > Some questions on the proposal.
          >
          > 1. I am not sure if database is the right place to do
          > authentication. For example, your proposal still calls for sysadmin to know
          > user passwords (at least initially). Who has the authorization to run ALTER
          > USER? Database keeping user passwords may not be a good idea for many
          > reasons. How to ensure no one is snooping on the protocol to read clear text
          > password that is embedded as part of SQL statement? Can a sysadmin turn on
          > tracing in network server or in the engine to see what password users are
          > picking for themselfs?
          >
          >
          This proposal does not discuss how a sysadmin (dba) type of user is defined,
          as obviously we don't have System privileges support as well as roles (yet)

          • specs for these are incoming .

          Here it how it would work in the meantime:

          • For a database to be created and assuming 'sqlStandard' authorization mode
            is enabled and we have support for certain system privileges such as the
            CREATE DATABASE one, a grantee, would be allowed to create a database and
            assign a 'dba' (or 'admin') role to a new user in the just created database.
            Again, this is assuming there is support for Roles and System privileges but
            features can be done in phases as you know. Derby supports authentication at
            the System and Database level - Derby databases can defined their own
            security restrictions/settings independently of the system level - as you
            know, derby databases can be moved around independently of the derby system
            realm - there is no 'master' system tables (i.e. mo master DB) in Derby and
            it was done so that databases could be detached, accessed without having to
            be defined in some sort of master database containing metadata to access
            user databases - this is a very powerful and flexible model but it has a
            cost...

          An application should be able to have a database which itself is either
          being shipped to a user or distributed with user credentials and
          authorization (privileges) metadata being defined as part of the database.
          In order to run ALTER USER, the individual would need to have the CREATE
          USER privilege (we don't have that right now). Derby has a Built-In
          authentication provider (scheme) which allows Derby to run its own user
          authentication realm - You can't always assume LDAP, many applications don't
          use or have access to LDAP and are not always connected, yet they still want
          to be able to protect access to a DB.

          As far as snooping the clear text password on the network, we do have the
          same issue with the current scheme of calling
          SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY across a networked JDBC connectoin -
          in fact, there is a similar issue with LDAP authentication, some people use
          SSL in order to solve this but in our case, DRDA also has mechanisms for
          resetting a user password over a network and in a secure way - hence we
          could either piggyback on existing and similar DRDA mechanisms or have the
          admin use an SSL-enabled (or IPSEC) derby connection for such admin
          commands...

          > 1. Why not sure LDAP or some other standard authentication models?
          > It may be good to strengthen Derby authentication, but not necessarily by
          > making Derby manage passwords.
          >
          >
          Derby Built-In authentication is important - again, not everyone is using
          LDAP, especially with small departmental level type of databases as well as
          embedded / disconnected ones. Derby already manages user passwords at the
          database level when a user is defined in a database. Derby stores user
          password in a secure fashion (they cannot be decrypted easily and if at all

          • they are single-hashed) - the transport of passwords across a network is
            defined and handled in the DRDA specifications which themselves are part of
            a standard - encrypting a password in a DB is not related to how a password
            is transported across the network - they are both important to address and
            we have the means.

          > 1. You said current user management would work as is in addition to
          > the new proposal. How do you propose to keep these two in sync? Could a user
          > be present in both ways and what happens if their passwords are different?
          >
          >
          No - this phase I proposal is to enhance the DDL support for managing
          Built-In Derby users in a database. We would still be using database
          properties to store the actual user/password combination as presently. You
          cannot have 2 users of the same name in a derby database - the only way to
          achieve something close would be to define a user at the system level and
          the same at the database level, and this last one would take precedence -
          also, in derby it is possible to set a a database property to only consider
          database properties and not the system ones, meaning that even if some users
          would be defined at the system level, they would not be granted access to
          the database which has authentication turned ON.

          > 1. Why not make SYSUSERS a system table now, instead of a VTI?
          > Making it a system catalog has benifits like dictionary management.
          >
          >
          Agreed - just a question of phasing something in different stages - Also, we
          would not have to do any upgrade changes with this first phase since it
          would still be going after database properties underneath. I agree that the
          upgrade issue would still have to be done if sysusers is added - at the same
          time, it is very likely that it will be required if additional user
          semantics are added (i.e. profiles, pwd expiration, roles (hence UID
          required instead of username) - so Yes, this is a valid point

          Thanks for all the comments.

          --francois

          Show
          Francois Orsini added a comment - Hi Satheesh, Thanks for the initial feedback. Please find some answers enclosed below. On 1/24/06, Satheesh Bandaram wrote: > > Hi Francois, > > Some questions on the proposal. > > 1. I am not sure if database is the right place to do > authentication. For example, your proposal still calls for sysadmin to know > user passwords (at least initially). Who has the authorization to run ALTER > USER? Database keeping user passwords may not be a good idea for many > reasons. How to ensure no one is snooping on the protocol to read clear text > password that is embedded as part of SQL statement? Can a sysadmin turn on > tracing in network server or in the engine to see what password users are > picking for themselfs? > > This proposal does not discuss how a sysadmin (dba) type of user is defined, as obviously we don't have System privileges support as well as roles (yet) specs for these are incoming . Here it how it would work in the meantime: For a database to be created and assuming 'sqlStandard' authorization mode is enabled and we have support for certain system privileges such as the CREATE DATABASE one, a grantee, would be allowed to create a database and assign a 'dba' (or 'admin') role to a new user in the just created database. Again, this is assuming there is support for Roles and System privileges but features can be done in phases as you know. Derby supports authentication at the System and Database level - Derby databases can defined their own security restrictions/settings independently of the system level - as you know, derby databases can be moved around independently of the derby system realm - there is no 'master' system tables (i.e. mo master DB) in Derby and it was done so that databases could be detached, accessed without having to be defined in some sort of master database containing metadata to access user databases - this is a very powerful and flexible model but it has a cost... An application should be able to have a database which itself is either being shipped to a user or distributed with user credentials and authorization (privileges) metadata being defined as part of the database. In order to run ALTER USER, the individual would need to have the CREATE USER privilege (we don't have that right now). Derby has a Built-In authentication provider (scheme) which allows Derby to run its own user authentication realm - You can't always assume LDAP, many applications don't use or have access to LDAP and are not always connected, yet they still want to be able to protect access to a DB. As far as snooping the clear text password on the network, we do have the same issue with the current scheme of calling SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY across a networked JDBC connectoin - in fact, there is a similar issue with LDAP authentication, some people use SSL in order to solve this but in our case, DRDA also has mechanisms for resetting a user password over a network and in a secure way - hence we could either piggyback on existing and similar DRDA mechanisms or have the admin use an SSL-enabled (or IPSEC) derby connection for such admin commands... > 1. Why not sure LDAP or some other standard authentication models? > It may be good to strengthen Derby authentication, but not necessarily by > making Derby manage passwords. > > Derby Built-In authentication is important - again, not everyone is using LDAP, especially with small departmental level type of databases as well as embedded / disconnected ones. Derby already manages user passwords at the database level when a user is defined in a database. Derby stores user password in a secure fashion (they cannot be decrypted easily and if at all they are single-hashed) - the transport of passwords across a network is defined and handled in the DRDA specifications which themselves are part of a standard - encrypting a password in a DB is not related to how a password is transported across the network - they are both important to address and we have the means. > 1. You said current user management would work as is in addition to > the new proposal. How do you propose to keep these two in sync? Could a user > be present in both ways and what happens if their passwords are different? > > No - this phase I proposal is to enhance the DDL support for managing Built-In Derby users in a database. We would still be using database properties to store the actual user/password combination as presently. You cannot have 2 users of the same name in a derby database - the only way to achieve something close would be to define a user at the system level and the same at the database level, and this last one would take precedence - also, in derby it is possible to set a a database property to only consider database properties and not the system ones, meaning that even if some users would be defined at the system level, they would not be granted access to the database which has authentication turned ON. > 1. Why not make SYSUSERS a system table now, instead of a VTI? > Making it a system catalog has benifits like dictionary management. > > Agreed - just a question of phasing something in different stages - Also, we would not have to do any upgrade changes with this first phase since it would still be going after database properties underneath. I agree that the upgrade issue would still have to be done if sysusers is added - at the same time, it is very likely that it will be required if additional user semantics are added (i.e. profiles, pwd expiration, roles (hence UID required instead of username) - so Yes, this is a valid point Thanks for all the comments. --francois
          Hide
          Daniel John Debrunner added a comment -

          Questions on the spec:

          • What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database?
          • "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else?
          • What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures.
          • I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database.
          Show
          Daniel John Debrunner added a comment - Questions on the spec: What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database? "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else? What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures. I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database.
          Hide
          Daniel John Debrunner added a comment -

          What's the purpose of returning the password column in the table/vti SYSUSERS?

          Show
          Daniel John Debrunner added a comment - What's the purpose of returning the password column in the table/vti SYSUSERS?
          Hide
          Francois Orsini added a comment -

          > Daniel John Debrunner commented on DERBY-866:
          > ---------------------------------------------
          >
          > Questions on the spec:
          >
          > - What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database?
          >

          I mean that at least 2 known database systems out there are using such DDL interface. The CREATE USER syntax works well in respect with creating a new user object (such as CREATE TABLE for instance) but it not defined in the ANSI SQL Specs (at least I have not found any reference myself...if someone does please let me know). From a SQL standpoint, I find this DDL syntax in-line with other other DDL syntax - That's all.

          > - "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else?
          >

          I have not found any ANSI SQL defining a standard syntax for SQL USER DDL statements, so that's what I mean by saying there is no "real"ANSI SQL standard - it is all based on preference and what users would be the most familiar with to use as an interface, knowing that 2 other known RDBMS out are using a similar one.

          > - What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures.
          >

          I thought I mentioned it in the proposal but I omitted to add the 'Alternatives' section which would be good to have indeed - I thought it would be more intuitive to use a DDL SQL-like rather than calling a Derby procedure (again for the reasons that this syntax is similar to some used by other RDBMS).

          We know tat the current interface (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY) does NOT scale in the long run - It is based on using a database property to define a user in the database - It is preventing additional semantics (such as password expiration, default Role, etc) to be added to the user (since it is based on a property Name-Value pair definition right now...Sure we could use procedures to manage users but it won't be a structured english-like syntac (ala SQL)...

          > - I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database.
          >

          I actually thought about the user mapping but did not mention it - a derby user should be generic enough that once authenticated in the system, it is a user, not a built-in user, neither an LDAP user, etc - The CREATE USER DDL command could be expanded with an EXTERNAL attribute to map/refer to a user outside of the Built-In realm and by overloading the IDENTIFIED BY attribute to specify the LDAP user DN (as used when authenticating with LDAP). Roles are independent of Users, and their relationships should be defined in separate system catalogs, a separate user<>role<>role relationship catalog can be defined to store the metadata about user roles or roles assigned to other roles...User mapping is good as there is so little control we can have over users defined in LDAP (in terms of settings additional user semantics in the Derby's realm), hence, we might need to compensate by linking an "external" user defined in Derby (i.e. SYSUSERS) with some user DN (Distinguished Name) defined in an LDAP repository.

          Syntax such as:

          CREATE USER user_id IDENTIFIED BY 'LDAP User DN' EXTERNAL

          for instance: CREATE USER user_id IDENTIFIED BY 'uid=mary,ou=People,o=FlyTours.com' EXTERNAL

          The other alternative that you suggest by having a SYS_BUILTIN_USERS is conceivable but I was hoping to use SYSUSERS, as a system catalog not just for Built-In users - for instance if we need to add new user semantics such as user profile, role, etc, then we can map an LDAP authenticated user with a derby defined one with additional derby user properties (and via the EXTERNAL attribute). SYSUSERS can be used for the built-in provider when it is used or/and by other providers to map externally authenticated users whom would need to have additional user properties set in Derby...

          --francois

          Show
          Francois Orsini added a comment - > Daniel John Debrunner commented on DERBY-866 : > --------------------------------------------- > > Questions on the spec: > > - What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database? > I mean that at least 2 known database systems out there are using such DDL interface. The CREATE USER syntax works well in respect with creating a new user object (such as CREATE TABLE for instance) but it not defined in the ANSI SQL Specs (at least I have not found any reference myself...if someone does please let me know). From a SQL standpoint, I find this DDL syntax in-line with other other DDL syntax - That's all. > - "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else? > I have not found any ANSI SQL defining a standard syntax for SQL USER DDL statements, so that's what I mean by saying there is no "real"ANSI SQL standard - it is all based on preference and what users would be the most familiar with to use as an interface, knowing that 2 other known RDBMS out are using a similar one. > - What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures. > I thought I mentioned it in the proposal but I omitted to add the 'Alternatives' section which would be good to have indeed - I thought it would be more intuitive to use a DDL SQL-like rather than calling a Derby procedure (again for the reasons that this syntax is similar to some used by other RDBMS). We know tat the current interface (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY) does NOT scale in the long run - It is based on using a database property to define a user in the database - It is preventing additional semantics (such as password expiration, default Role, etc) to be added to the user (since it is based on a property Name-Value pair definition right now...Sure we could use procedures to manage users but it won't be a structured english-like syntac (ala SQL)... > - I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database. > I actually thought about the user mapping but did not mention it - a derby user should be generic enough that once authenticated in the system, it is a user, not a built-in user, neither an LDAP user, etc - The CREATE USER DDL command could be expanded with an EXTERNAL attribute to map/refer to a user outside of the Built-In realm and by overloading the IDENTIFIED BY attribute to specify the LDAP user DN (as used when authenticating with LDAP). Roles are independent of Users, and their relationships should be defined in separate system catalogs, a separate user< >role< >role relationship catalog can be defined to store the metadata about user roles or roles assigned to other roles...User mapping is good as there is so little control we can have over users defined in LDAP (in terms of settings additional user semantics in the Derby's realm), hence, we might need to compensate by linking an "external" user defined in Derby (i.e. SYSUSERS) with some user DN (Distinguished Name) defined in an LDAP repository. Syntax such as: CREATE USER user_id IDENTIFIED BY 'LDAP User DN' EXTERNAL for instance: CREATE USER user_id IDENTIFIED BY 'uid=mary,ou=People,o=FlyTours.com' EXTERNAL The other alternative that you suggest by having a SYS_BUILTIN_USERS is conceivable but I was hoping to use SYSUSERS, as a system catalog not just for Built-In users - for instance if we need to add new user semantics such as user profile, role, etc, then we can map an LDAP authenticated user with a derby defined one with additional derby user properties (and via the EXTERNAL attribute). SYSUSERS can be used for the built-in provider when it is used or/and by other providers to map externally authenticated users whom would need to have additional user properties set in Derby... --francois
          Hide
          Francois Orsini added a comment -

          >
          > Daniel John Debrunner commented on DERBY-866:
          > ---------------------------------------------
          >
          > What's the purpose of returning the password column in the table/vti SYSUSERS?
          >
          Password won't be clearly readable, only the base64 representation of a already hashed password - no risk really, just thought it could have some usage and if we use EXTERNAL to specify that this user maps to one defined in some LDAP repository (as an example), then because of EXTERNAL the password would not be encrypted and instead display the User DN ('uid=mary,ou=People,o=FlyTours.com') that maps into LDAP...So in that case it would have a meaning...See my previous comment on the EXTERNAL attribute definition. We could also decide not to display anything for built-in stored passwords (eventhough they cannot be decrypted)...

          Show
          Francois Orsini added a comment - > > Daniel John Debrunner commented on DERBY-866 : > --------------------------------------------- > > What's the purpose of returning the password column in the table/vti SYSUSERS? > Password won't be clearly readable, only the base64 representation of a already hashed password - no risk really, just thought it could have some usage and if we use EXTERNAL to specify that this user maps to one defined in some LDAP repository (as an example), then because of EXTERNAL the password would not be encrypted and instead display the User DN ('uid=mary,ou=People,o=FlyTours.com') that maps into LDAP...So in that case it would have a meaning...See my previous comment on the EXTERNAL attribute definition. We could also decide not to display anything for built-in stored passwords (eventhough they cannot be decrypted)...
          Hide
          Daniel John Debrunner added a comment -

          I see the argument for DDL, maybe the create user could be like

          CREATE USER <username> IDENTIFIED BY PASSWORD <password>

          basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g.

          CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>

          Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc.

          Show
          Daniel John Debrunner added a comment - I see the argument for DDL, maybe the create user could be like CREATE USER <username> IDENTIFIED BY PASSWORD <password> basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g. CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff> Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc.
          Hide
          Francois Orsini added a comment -

          > Daniel John Debrunner commented on DERBY-866:
          > ---------------------------------------------
          >
          > I see the argument for DDL, maybe the create user could be like
          >
          > CREATE USER <username> IDENTIFIED BY PASSWORD <password>
          >
          > basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g.
          >
          > CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>
          >
          > Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc.
          >

          Yes it could - I somewhat tried to avoid this as we have a pluggable authentication scheme so additional custom-created auth providers would not have the proper grammar defined (unless they are part of Derby where the grammar can always be extended to support a new one) - But a new auth provider can just be implemented outside of Derby, hence we would also need to map to the custom auth providers out there - but if we have a column with the authentication provider name name, then we can expect to have the proper user login info as part of the 'IDENTIFIED BY' clause without any extra specific auth type keyword...In this case, the password column can just be renamed to something else (i.e. AUTH_INFO), in order to support not just a password value but other type of info (it would be a VARCHAR after all) for the the other providers.

          or based on what you're suggesting we could still have:

          // Built-In
          CREATE USER <username> IDENTIFIED BY PASSWORD <password>

          // LDAP (mapping)
          CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>

          // PAM (mapping)
          CREATE USER <username> IDENTIFIED BY PAM <pam stuff>

          // CUSTOME Auth Provider (mapping) - External keyword used for that purpose here
          CREATE USER <username> IDENTIFIED BY EXTERNAL <custom stuff>

          Show
          Francois Orsini added a comment - > Daniel John Debrunner commented on DERBY-866 : > --------------------------------------------- > > I see the argument for DDL, maybe the create user could be like > > CREATE USER <username> IDENTIFIED BY PASSWORD <password> > > basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g. > > CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff> > > Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc. > Yes it could - I somewhat tried to avoid this as we have a pluggable authentication scheme so additional custom-created auth providers would not have the proper grammar defined (unless they are part of Derby where the grammar can always be extended to support a new one) - But a new auth provider can just be implemented outside of Derby, hence we would also need to map to the custom auth providers out there - but if we have a column with the authentication provider name name, then we can expect to have the proper user login info as part of the 'IDENTIFIED BY' clause without any extra specific auth type keyword...In this case, the password column can just be renamed to something else (i.e. AUTH_INFO), in order to support not just a password value but other type of info (it would be a VARCHAR after all) for the the other providers. or based on what you're suggesting we could still have: // Built-In CREATE USER <username> IDENTIFIED BY PASSWORD <password> // LDAP (mapping) CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff> // PAM (mapping) CREATE USER <username> IDENTIFIED BY PAM <pam stuff> // CUSTOME Auth Provider (mapping) - External keyword used for that purpose here CREATE USER <username> IDENTIFIED BY EXTERNAL <custom stuff>
          Hide
          Francois Orsini added a comment -

          Posted new 1.1 spec - Derby_User_Enhancement_v1.1.html - please enclose comments to the JIRA itself.

          Show
          Francois Orsini added a comment - Posted new 1.1 spec - Derby_User_Enhancement_v1.1.html - please enclose comments to the JIRA itself.
          Hide
          Daniel John Debrunner added a comment -

          Thanks for the updated spec, some comments:

          • are you expecting this to be ready for the 10.2 release?
          • I'm still confused by the phrase "no real ANSI SQL standard for managing users in SQL", the "real" implies there is some sort of ANSI standard in this area, but provisional, or not widely adopted or you imagined it.
          • what the defined behaviour for 'CREATE USER dan', ie. no IDENTIFIED BY clause, since it's optional?
          • Without a special type token between IDENTIFIED BY and the auto_info I think you require that the authentication provider is defined for the database, otherwise I don't see how the code decides to store a hash of the password or the mapping info.
            I was assuming it would be

          CREATE USER dan IDENTIFIED BY PASSWORD 'ek992ffdwfe'

          or

          CREATE USER dan IDENTIFIED BY LDAP 'djd@apache.org'

          • Can user_id and auto_info be parameter markers?
          • How the mapping works is not defined.
          • SYSUSERS example has PASSWORD in it
          • Development phasing is a strange section. Really there is only a single phase (as described) for the functionality described by this spec. Phases 2 and 3 are future ideas, so thye are not related to the development of this spec. I would have put them in a separate section like "Future Direction".
          • There is a hole in the spec, if this is implemented in 10.2 and DERBY-464 (DML grant revoke) is implemented then the addition of CREATE USER will (could?) allow anyone to create users in the database., which in turn allows anyone to create schemas of any name. You refer to this in your NOTE in the Syntax section, where you say CREATE USER permission will be required, but as far as I know no-one is working on that. Ie. as you have written, what happens when the database is in SQL authorization mode, but system priviledges are not available, as I believe will be the case for 10.2?
          Show
          Daniel John Debrunner added a comment - Thanks for the updated spec, some comments: are you expecting this to be ready for the 10.2 release? I'm still confused by the phrase "no real ANSI SQL standard for managing users in SQL", the "real" implies there is some sort of ANSI standard in this area, but provisional, or not widely adopted or you imagined it. what the defined behaviour for 'CREATE USER dan', ie. no IDENTIFIED BY clause, since it's optional? Without a special type token between IDENTIFIED BY and the auto_info I think you require that the authentication provider is defined for the database, otherwise I don't see how the code decides to store a hash of the password or the mapping info. I was assuming it would be CREATE USER dan IDENTIFIED BY PASSWORD 'ek992ffdwfe' or CREATE USER dan IDENTIFIED BY LDAP 'djd@apache.org' Can user_id and auto_info be parameter markers? How the mapping works is not defined. SYSUSERS example has PASSWORD in it Development phasing is a strange section. Really there is only a single phase (as described) for the functionality described by this spec. Phases 2 and 3 are future ideas, so thye are not related to the development of this spec. I would have put them in a separate section like "Future Direction". There is a hole in the spec, if this is implemented in 10.2 and DERBY-464 (DML grant revoke) is implemented then the addition of CREATE USER will (could?) allow anyone to create users in the database., which in turn allows anyone to create schemas of any name. You refer to this in your NOTE in the Syntax section, where you say CREATE USER permission will be required, but as far as I know no-one is working on that. Ie. as you have written, what happens when the database is in SQL authorization mode, but system priviledges are not available, as I believe will be the case for 10.2?
          Show
          Kathey Marsden added a comment - removing from 10.2. see: http://www.nabble.com/10.2-High-Value-Fix-Candidates-and-Fix-Version-Adjustments-tf2007999.html
          Hide
          David Gradwell added a comment -

          An SQL based CREATE USER capability is really important for my application.

          I fully support the syntax proposed by Francis Orsini and hope that this can be implemented soon.

          As an aside, if anyone reading this is a member of the ANSI or ISO SQL Standards committees then please get in touch. I would be willing to draft a change proposal to the SQL Standard for this area. Then the various implementations could provide a common capability.

          David Gradwell

          Show
          David Gradwell added a comment - An SQL based CREATE USER capability is really important for my application. I fully support the syntax proposed by Francis Orsini and hope that this can be implemented soon. As an aside, if anyone reading this is a member of the ANSI or ISO SQL Standards committees then please get in touch. I would be willing to draft a change proposal to the SQL Standard for this area. Then the various implementations could provide a common capability. David Gradwell
          Hide
          Rick Hillegas added a comment -

          Derby has changed a bit since Francois posted his original spec on this issue. I would like to revive the discussion of this feature. I agree that Derby needs a simple scheme for managing user credentials. This seems to me to be a pre-requisite to making Derby secure-by-default.

          I agree with the previous discussion on this issue that the current BUILTIN mechanism is too limited to support production-quality user management. In particular, the BUILTIN mechanism does not support password expiration limits. The BUILTIN mechanism is a development/debugging aid, not a production-quality tool.

          Since the SQL Standard does not address user management, I am reluctant to introduce new DDL to support this feature.

          Here are the outlines of a scheme which I think would be fairly easy to implement in the 10.9 timeframe:

          1) Introduce a new value for derby.authentication.provider. This would tell Derby to use this new authentication scheme.

          2) Introduce a new database property which defines how long passwords are usable: derby.authentication.password.expiration. This can be set to the number of milliseconds which a password remains valid after being updated. If set to a value <= 0, then newly updated passwords don't expire.

          3) Introduce a new system table which only the DBO can view. The DBO can grant SELECT on this table to other users/roles:

          create table sys.sysusers
          (
          userName varchar( 128 ),
          hashingScheme varchar( 32672 ),
          password varchar( 32672 ),
          lastModified timestamp
          );

          The second column of this table (hashingScheme) contains something like the decoding instructions which are prepended to current BUILTIN passwords when they are persisted.

          A user can not log in after derby.authentication.password.expiration milliseconds have elapsed since lastModified. Trying to log in with an expired password will raise an exception.

          4) Introduce the following new system procedures which only the DBO can run. The DBO can grant EXECUTE privilege on these procedures to other users/roles:

          create procedure syscs_util.createUser
          (
          in userName varchar( 128 ),
          in password varchar( 32672 )
          ) ...

          create procedure syscs_util.modifyUser
          (
          in userName varchar( 128 ),
          in password varchar( 32672 )
          )
          ...

          create procedure syscs_util.dropUser
          (
          in userName varchar( 128 )
          ) ...

          5) Introduce the following new system procedure which any user can run to change their own password:

          create procedure syscs_util.modifyPassword
          (
          in password varchar( 32672 )
          )
          ...

          What are your thoughts?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Derby has changed a bit since Francois posted his original spec on this issue. I would like to revive the discussion of this feature. I agree that Derby needs a simple scheme for managing user credentials. This seems to me to be a pre-requisite to making Derby secure-by-default. I agree with the previous discussion on this issue that the current BUILTIN mechanism is too limited to support production-quality user management. In particular, the BUILTIN mechanism does not support password expiration limits. The BUILTIN mechanism is a development/debugging aid, not a production-quality tool. Since the SQL Standard does not address user management, I am reluctant to introduce new DDL to support this feature. Here are the outlines of a scheme which I think would be fairly easy to implement in the 10.9 timeframe: 1) Introduce a new value for derby.authentication.provider. This would tell Derby to use this new authentication scheme. 2) Introduce a new database property which defines how long passwords are usable: derby.authentication.password.expiration. This can be set to the number of milliseconds which a password remains valid after being updated. If set to a value <= 0, then newly updated passwords don't expire. 3) Introduce a new system table which only the DBO can view. The DBO can grant SELECT on this table to other users/roles: create table sys.sysusers ( userName varchar( 128 ), hashingScheme varchar( 32672 ), password varchar( 32672 ), lastModified timestamp ); The second column of this table (hashingScheme) contains something like the decoding instructions which are prepended to current BUILTIN passwords when they are persisted. A user can not log in after derby.authentication.password.expiration milliseconds have elapsed since lastModified. Trying to log in with an expired password will raise an exception. 4) Introduce the following new system procedures which only the DBO can run. The DBO can grant EXECUTE privilege on these procedures to other users/roles: create procedure syscs_util.createUser ( in userName varchar( 128 ), in password varchar( 32672 ) ) ... create procedure syscs_util.modifyUser ( in userName varchar( 128 ), in password varchar( 32672 ) ) ... create procedure syscs_util.dropUser ( in userName varchar( 128 ) ) ... 5) Introduce the following new system procedure which any user can run to change their own password: create procedure syscs_util.modifyPassword ( in password varchar( 32672 ) ) ... What are your thoughts? Thanks, -Rick
          Hide
          Dag H. Wanvik added a comment -

          Thanks for pushing this forward, Rick! Some questions and comments:

          • q1: the password in the system table is the result of the hash, right? (not the password itself)
          • q2: salting?
          • q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants
          • q4: boot-strap: how is the dbo user created? Autmatically when sing the provided connection password when creating the database and seeing the property for the new scheme set in derby.authentication.provider?
          • q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly)
            If so, the authentication could be overridden by specifying another value for derby.authentication.provider.
          • q6: do you see the scheme requiring SQL authorization enabled? If not, we'd need encryption to avoid attacks: arbitrary user could backup entire database and stage dictionary attack against the USER table...Btw, I forget, is backup restricted with authorization on? Docs doesn't say...
          • comment: creating a new user/role should cross-check each other to avoid collisions in uids.
          • comment: the new feature only solves the issue of database level authentication, not system level, obviously (limitation)
          Show
          Dag H. Wanvik added a comment - Thanks for pushing this forward, Rick! Some questions and comments: q1: the password in the system table is the result of the hash, right? (not the password itself) q2: salting? q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants q4: boot-strap: how is the dbo user created? Autmatically when sing the provided connection password when creating the database and seeing the property for the new scheme set in derby.authentication.provider? q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly) If so, the authentication could be overridden by specifying another value for derby.authentication.provider. q6: do you see the scheme requiring SQL authorization enabled? If not, we'd need encryption to avoid attacks: arbitrary user could backup entire database and stage dictionary attack against the USER table...Btw, I forget, is backup restricted with authorization on? Docs doesn't say... comment: creating a new user/role should cross-check each other to avoid collisions in uids. comment: the new feature only solves the issue of database level authentication, not system level, obviously (limitation)
          Hide
          Dag H. Wanvik added a comment -

          To answer one of my question: backup is restricted:

          ij> connect 'jdbc:derby:wombat;user=donald;password=duck';
          ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/tmp/backupdir2') ;
          ERROR 42504: User 'DONALD' does not have EXECUTE permission on ROUTINE 'SYSCS_UTIL'.'SYSCS_BACKUP_DATABASE'.

          The docs have been fixed on trunk, I see

          Show
          Dag H. Wanvik added a comment - To answer one of my question: backup is restricted: ij> connect 'jdbc:derby:wombat;user=donald;password=duck'; ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/tmp/backupdir2') ; ERROR 42504: User 'DONALD' does not have EXECUTE permission on ROUTINE 'SYSCS_UTIL'.'SYSCS_BACKUP_DATABASE'. The docs have been fixed on trunk, I see
          Hide
          Rick Hillegas added a comment -

          Thanks for the analysis and questions, Dag. Some responses follow:

          • q1: the password in the system table is the result of the hash, right? (not the password itself)
          • q2: salting?

          Right. The password in the system table should be salted and hashed.

          • q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants

          This sounds like a good idea but I'm not sure how to secure the encryption key so that the database can be booted by someone other than the dbo. Any ideas?

          • q4: boot-strap: how is the dbo user created? Autmatically when sing the provided connection password when creating the database and seeing the property for the new scheme set in derby.authentication.provider?

          Right.

          • q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly) If so, the authentication could be overridden by specifying another value for derby.authentication.provider.

          This is a good point. It is goofy that someone can masquerade as the DBO by disabling authentication on the boot command line and then simply asserting "user=dbo" at connection time--even when sql authorization is enabled. This is a security hole for all of our authentication schemes. I'm afraid it's too late to close this hole for the other schemes, but we could close it for this new scheme. That would be independent of turning on derby.database.propertiesOnly as part of the master security property. Thanks.

          • q6: do you see the scheme requiring SQL authorization enabled? If not, we'd need encryption to avoid attacks: arbitrary user could backup entire database and stage dictionary attack against the USER table...Btw, I forget, is backup restricted with authorization on? Docs doesn't say...

          I think this is a good idea although it violates the orthogonality of the Derby properties. I don't understand why anyone would want to run with authentication but not authorization. The ability to do that is an unfortunate accident of our backward compatibility requirements combined with the order in which we implemented the two security mechanisms. Thanks.

          • comment: creating a new user/role should cross-check each other to avoid collisions in uids.

          Good idea.

          • comment: the new feature only solves the issue of database level authentication, not system level, obviously (limitation)

          Yes. This is another, closely related discussion we need to have. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for the analysis and questions, Dag. Some responses follow: q1: the password in the system table is the result of the hash, right? (not the password itself) q2: salting? Right. The password in the system table should be salted and hashed. q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants This sounds like a good idea but I'm not sure how to secure the encryption key so that the database can be booted by someone other than the dbo. Any ideas? q4: boot-strap: how is the dbo user created? Autmatically when sing the provided connection password when creating the database and seeing the property for the new scheme set in derby.authentication.provider? Right. q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly) If so, the authentication could be overridden by specifying another value for derby.authentication.provider. This is a good point. It is goofy that someone can masquerade as the DBO by disabling authentication on the boot command line and then simply asserting "user=dbo" at connection time--even when sql authorization is enabled. This is a security hole for all of our authentication schemes. I'm afraid it's too late to close this hole for the other schemes, but we could close it for this new scheme. That would be independent of turning on derby.database.propertiesOnly as part of the master security property. Thanks. q6: do you see the scheme requiring SQL authorization enabled? If not, we'd need encryption to avoid attacks: arbitrary user could backup entire database and stage dictionary attack against the USER table...Btw, I forget, is backup restricted with authorization on? Docs doesn't say... I think this is a good idea although it violates the orthogonality of the Derby properties. I don't understand why anyone would want to run with authentication but not authorization. The ability to do that is an unfortunate accident of our backward compatibility requirements combined with the order in which we implemented the two security mechanisms. Thanks. comment: creating a new user/role should cross-check each other to avoid collisions in uids. Good idea. comment: the new feature only solves the issue of database level authentication, not system level, obviously (limitation) Yes. This is another, closely related discussion we need to have. Thanks.
          Hide
          Francois Orsini added a comment -
          • q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly) If so, the authentication could be overridden by specifying another value for derby.authentication.provider.

          This is a good point. It is goofy that someone can masquerade as the DBO by disabling authentication on the boot command line and then simply asserting "user=dbo" at connection time--even when sql authorization is enabled. This is a security hole for all of our authentication schemes. I'm afraid it's too late to close this hole for the other schemes, but we could close it for this new scheme. That would be independent of turning on derby.database.propertiesOnly as part of the master security property. Thanks.

                • derby.database.propertiesOnly property cannot be overridden by a system property, unless things have changed so no one should be able to override some authentication provider set at the database level as long as derby.database.propertiesOnly is set to true in that database - unless of course, one is able to change it to false at the file level.
          • q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants

          This sounds like a good idea but I'm not sure how to secure the encryption key so that the database can be booted by someone other than the dbo. Any ideas?

                • Most traditional RDBMS do not encrypt their sys.sysusers equivalent as you would protect access to the DB files typically and passwords are (salted)+hashed - if you want to give the files away or they get accessed by other means, then all bets are off, meaning that no matter what and with expensive hardware, it could be possible to decrypt most ciphered streams these days. Of course if the salt is known, then without encryption, someone could just go and replace an existing password in sys.sysusers, assuming they have access to it - Database encryption seems like the way to protect sys.sysusers (to a certain limit as mentioned above) unless one wants to introduce yet another database access (Sesame) secret key that a user would have to enter to get access (without encrypting the whole DB, but rather sys.sysusers). There is probably other ways, that's the quickest I had in mind.

          At the end, if you want to hand out a some Derby DB files (non client-server topology) and you want to restrict access, Authentication and Authorization should be enabled as well as Database encryption turned ON.

          Show
          Francois Orsini added a comment - q5: database only properties is not default, but must be enabled. will the new mechanism work without it? (cf. derby.database.propertiesOnly) If so, the authentication could be overridden by specifying another value for derby.authentication.provider. This is a good point. It is goofy that someone can masquerade as the DBO by disabling authentication on the boot command line and then simply asserting "user=dbo" at connection time--even when sql authorization is enabled. This is a security hole for all of our authentication schemes. I'm afraid it's too late to close this hole for the other schemes, but we could close it for this new scheme. That would be independent of turning on derby.database.propertiesOnly as part of the master security property. Thanks. derby.database.propertiesOnly property cannot be overridden by a system property, unless things have changed so no one should be able to override some authentication provider set at the database level as long as derby.database.propertiesOnly is set to true in that database - unless of course, one is able to change it to false at the file level. q3: should the system table be encrypted to avoid attacks? currently we encrypt entire database which may not be what one wants This sounds like a good idea but I'm not sure how to secure the encryption key so that the database can be booted by someone other than the dbo. Any ideas? Most traditional RDBMS do not encrypt their sys.sysusers equivalent as you would protect access to the DB files typically and passwords are (salted)+hashed - if you want to give the files away or they get accessed by other means, then all bets are off, meaning that no matter what and with expensive hardware, it could be possible to decrypt most ciphered streams these days. Of course if the salt is known, then without encryption, someone could just go and replace an existing password in sys.sysusers, assuming they have access to it - Database encryption seems like the way to protect sys.sysusers (to a certain limit as mentioned above) unless one wants to introduce yet another database access (Sesame) secret key that a user would have to enter to get access (without encrypting the whole DB, but rather sys.sysusers). There is probably other ways, that's the quickest I had in mind. At the end, if you want to hand out a some Derby DB files (non client-server topology) and you want to restrict access, Authentication and Authorization should be enabled as well as Database encryption turned ON.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for helping us think this through, Francois! As far as derby.database.propertiesOnly is concerned, my worry was not that it could be switched off after being enabled, but rather if the new scheme could be made to work without it being set to true (insecure). Rick suggests that the new scheme be independent of this property, I am not sure how we could make that work with the present property based way of determining what authentication scheme to use.... Rick, any ideas? In the end we may not want to force this (present idea being that for deployment, derby.database.propertiesOnly should be set to true, but that it can be convenient during development to override it). This is not secure by default, of course, so we we may want to disallow the new scheme unless derby.database.propertiesOnly is set...

          Show
          Dag H. Wanvik added a comment - Thanks for helping us think this through, Francois! As far as derby.database.propertiesOnly is concerned, my worry was not that it could be switched off after being enabled, but rather if the new scheme could be made to work without it being set to true (insecure). Rick suggests that the new scheme be independent of this property, I am not sure how we could make that work with the present property based way of determining what authentication scheme to use.... Rick, any ideas? In the end we may not want to force this (present idea being that for deployment, derby.database.propertiesOnly should be set to true, but that it can be convenient during development to override it). This is not secure by default, of course, so we we may want to disallow the new scheme unless derby.database.propertiesOnly is set...
          Hide
          Rick Hillegas added a comment -

          Here is my next tranche of thoughts about building a more secure out-of-the-box authentication mechanism for Derby. I address two topics:

          1) Setting a reasonable goal.

          2) Handling system-wide users.

          ------------- Setting a Reasonable Goal -------------------

          Francois raised the point that if someone can read or write your database directories, then you may have already lost the security battle. If someone can read your database directories, then they can copy the database to a workbench area and crack it at their leisure. If someone can write to the database directories, then they can alter your database; they might even escape detection via an exploit like the one described by DERBY-5510.

          If the attacker can connect to a Derby engine which reads directories holding your database, then the attacker can copy the database to a workbench area by exploiting the vulnerability described by DERBY-2470. By using the RESTORE functionality, the attacker does not even need valid credentials to do this.

          Encryption may help protect you from these attacks. However, as Francois points out, once the attacker has a physical copy of your database, cracking it may only be a matter of time. Derby encryption may be particularly vulnerable because system conglomerates keep their original names and begin with known sequences of bytes. It is possible that encrypted file systems may give you a bit more protection.

          For these reasons, I do not expect this JIRA to address the following non-goals:

          A) We are not trying to protect Derby databases on mobile devices like laptops and phones.

          B) We are not trying to protect Derby databases on removable media like USB sticks.

          However, I still think that we can boost the security of databases which live in physically secure locations such as servers inside protected machine rooms. This includes databases embedded inside user-written server applications and databases running inside Derby's own server framework.

          ------------- Handling System-wide Users -------------------

          This JIRA focuses on the problem of database-specific users. However, we need to give some thought to the problem of system-wide users. These users appear in the following situations:

          i) The credentials of Derby network administrators. Here there is no distinguished database to which we can tie the credentials.

          ii) The credentials needed to create a database. Since the database doesn't exist yet, we can't tie credentials to it.

          I am tempted to add the following case too:

          iii) The credentials needed to restore a database from a backup. We might want to address this problem by authenticating the user in a read-only connection to the backup BEFORE restoring from it. On the other hand, previous discussions related to DERBY-2109 and DERBY-2470 have compared this use-case to database creation. If we want to treat this case like database creation, then we may want to treat the credentials like case (ii) above.

          Here are some options for managing system-wide credentials:

          I) Via system-wide properties, stated either on the JVM boot command or in a properties file. Maybe we could reuse the existing BUILTIN user/password properties for this purpose. I do not recommend this solution because it seems vulnerable to being spied on.

          II) Via some encrypted file managed by Derby. This would require building system-wide machinery for changing credentials and for disaster recovery. One variation of this approach might be a master database which just holds system-wide credentials.

          III) Via some application-supplied authenticator. This pushes the problem into the application and seems to defeat the intent of this JIRA.

          Maybe you can think of some other approaches. As you can tell, I am not thrilled by any of the solutions above.

          Perhaps we can finesse these problems and not create a concept of system-wide users:

          i') We already have a solution to : The server administration credentials are invented at server boot time. In a properly configured server (forget NetServlet), the server can only be booted by someone who has passed other, enterprise-supplied authentication hurdles.

          ii') Do not allow arbitrary users to write their own Derby connection URLs. The trick here is for the embedding application to not provide a means by which users can specify arbitrary connection URLs. That is, educate users to treat URL injection like SQL injection.

          iii') We could tackle this as a case similar to (ii'). Or we could first authenticate in a read-only connection to the backup, as noted above.

          I would appreciate your additional thoughts about these topics. Thanks.

          Show
          Rick Hillegas added a comment - Here is my next tranche of thoughts about building a more secure out-of-the-box authentication mechanism for Derby. I address two topics: 1) Setting a reasonable goal. 2) Handling system-wide users. ------------- Setting a Reasonable Goal ------------------- Francois raised the point that if someone can read or write your database directories, then you may have already lost the security battle. If someone can read your database directories, then they can copy the database to a workbench area and crack it at their leisure. If someone can write to the database directories, then they can alter your database; they might even escape detection via an exploit like the one described by DERBY-5510 . If the attacker can connect to a Derby engine which reads directories holding your database, then the attacker can copy the database to a workbench area by exploiting the vulnerability described by DERBY-2470 . By using the RESTORE functionality, the attacker does not even need valid credentials to do this. Encryption may help protect you from these attacks. However, as Francois points out, once the attacker has a physical copy of your database, cracking it may only be a matter of time. Derby encryption may be particularly vulnerable because system conglomerates keep their original names and begin with known sequences of bytes. It is possible that encrypted file systems may give you a bit more protection. For these reasons, I do not expect this JIRA to address the following non-goals: A) We are not trying to protect Derby databases on mobile devices like laptops and phones. B) We are not trying to protect Derby databases on removable media like USB sticks. However, I still think that we can boost the security of databases which live in physically secure locations such as servers inside protected machine rooms. This includes databases embedded inside user-written server applications and databases running inside Derby's own server framework. ------------- Handling System-wide Users ------------------- This JIRA focuses on the problem of database-specific users. However, we need to give some thought to the problem of system-wide users. These users appear in the following situations: i) The credentials of Derby network administrators. Here there is no distinguished database to which we can tie the credentials. ii) The credentials needed to create a database. Since the database doesn't exist yet, we can't tie credentials to it. I am tempted to add the following case too: iii) The credentials needed to restore a database from a backup. We might want to address this problem by authenticating the user in a read-only connection to the backup BEFORE restoring from it. On the other hand, previous discussions related to DERBY-2109 and DERBY-2470 have compared this use-case to database creation. If we want to treat this case like database creation, then we may want to treat the credentials like case (ii) above. Here are some options for managing system-wide credentials: I) Via system-wide properties, stated either on the JVM boot command or in a properties file. Maybe we could reuse the existing BUILTIN user/password properties for this purpose. I do not recommend this solution because it seems vulnerable to being spied on. II) Via some encrypted file managed by Derby. This would require building system-wide machinery for changing credentials and for disaster recovery. One variation of this approach might be a master database which just holds system-wide credentials. III) Via some application-supplied authenticator. This pushes the problem into the application and seems to defeat the intent of this JIRA. Maybe you can think of some other approaches. As you can tell, I am not thrilled by any of the solutions above. Perhaps we can finesse these problems and not create a concept of system-wide users: i') We already have a solution to : The server administration credentials are invented at server boot time. In a properly configured server (forget NetServlet), the server can only be booted by someone who has passed other, enterprise-supplied authentication hurdles. ii') Do not allow arbitrary users to write their own Derby connection URLs. The trick here is for the embedding application to not provide a means by which users can specify arbitrary connection URLs. That is, educate users to treat URL injection like SQL injection. iii') We could tackle this as a case similar to (ii'). Or we could first authenticate in a read-only connection to the backup, as noted above. I would appreciate your additional thoughts about these topics. Thanks.
          Hide
          Bryan Pendleton added a comment -

          I'm having a bit of trouble following the overall threat model. My initial reaction is that the
          primary problem you're worried about here is the overall security of the Network Server, as
          distinct from the security of the individual database(s) it is providing access to; is that correct?

          It seems like the secondary problem has to do with properly securing a backup of a database,
          since the backup contains information that is as sensitive as the database itself. But that
          seems quite separate from the topic of Network Server security, no?

          I guess I feel like you're striking out into new ground, and away from the original goals of DERBY-866,
          and possibly in multiple directions at once.

          Very valuable thoughts, and thanks for sharing them! I'm just having a bit of trouble following it all.

          Show
          Bryan Pendleton added a comment - I'm having a bit of trouble following the overall threat model. My initial reaction is that the primary problem you're worried about here is the overall security of the Network Server, as distinct from the security of the individual database(s) it is providing access to; is that correct? It seems like the secondary problem has to do with properly securing a backup of a database, since the backup contains information that is as sensitive as the database itself. But that seems quite separate from the topic of Network Server security, no? I guess I feel like you're striking out into new ground, and away from the original goals of DERBY-866 , and possibly in multiple directions at once. Very valuable thoughts, and thanks for sharing them! I'm just having a bit of trouble following it all.
          Hide
          Rick Hillegas added a comment -

          Thanks for your comments, Bryan. I apologize for my scattershot series of questions and proposals.

          I seem to have come to focus on the following problem: Provide an out-of-the-box authentication scheme for Derby which is better than the current BUILTIN scheme. The new scheme would be used by multi-user applications.

          It doesn't matter whether the applications use Derby's Network Server framework or whether they simply embed Derby inside their own session manager. The primary use of this scheme would be to restrict access to databases, that is, to enforce a notion of database-specific user identity.

          The problem I was wrestling with in my last tranche of comments was this: Derby's other authentication schemes are all JVM-wide schemes, not database-specific schemes. This lets them handle a couple authentication problems which arise when you don't have a database. I am trying to figure out how we want to handle these problems when application(s) want to use the new scheme across all databases managed by a JVM.

          It's true, I am striking out in many different directions all at once. I am trying to make sure that the new scheme can handle all use-cases which the other schemes do today. This involves making sure that it will fit into candidate solutions for problems which we haven't addressed yet, like the database restoration problem described by DERBY-2470.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for your comments, Bryan. I apologize for my scattershot series of questions and proposals. I seem to have come to focus on the following problem: Provide an out-of-the-box authentication scheme for Derby which is better than the current BUILTIN scheme. The new scheme would be used by multi-user applications. It doesn't matter whether the applications use Derby's Network Server framework or whether they simply embed Derby inside their own session manager. The primary use of this scheme would be to restrict access to databases, that is, to enforce a notion of database-specific user identity. The problem I was wrestling with in my last tranche of comments was this: Derby's other authentication schemes are all JVM-wide schemes, not database-specific schemes. This lets them handle a couple authentication problems which arise when you don't have a database. I am trying to figure out how we want to handle these problems when application(s) want to use the new scheme across all databases managed by a JVM. It's true, I am striking out in many different directions all at once. I am trying to make sure that the new scheme can handle all use-cases which the other schemes do today. This involves making sure that it will fit into candidate solutions for problems which we haven't addressed yet, like the database restoration problem described by DERBY-2470 . Thanks, -Rick
          Hide
          Dag H. Wanvik added a comment -

          Re: options for managing system-wide credentials:

          > I) Via system-wide properties
          > II) Via some encrypted file managed by Derby
          > III) Via some application-supplied authenticator

          I guess LDAP would fill this need too. Another option is integration with the host OS process id. Downside: system specific.
          For both, the upside is we don't need to build a Derby specific system level authentication mechanism.

          Show
          Dag H. Wanvik added a comment - Re: options for managing system-wide credentials: > I) Via system-wide properties > II) Via some encrypted file managed by Derby > III) Via some application-supplied authenticator I guess LDAP would fill this need too. Another option is integration with the host OS process id. Downside: system specific. For both, the upside is we don't need to build a Derby specific system level authentication mechanism.
          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. Yes, LDAP should work. I lump it in category (III). Thanks.

          Show
          Rick Hillegas added a comment - Thanks, Dag. Yes, LDAP should work. I lump it in category (III). Thanks.
          Hide
          Rick Hillegas added a comment -

          Attaching a first version of a functional spec for NATIVE user management. This is an attempt to capture the discussion so far. Having a concrete proposal in front of us may help move the discussion forward. Your feedback is appreciated.

          The following topics need more discussion:

          1) This version of the spec does not address the issue of system-wide credentials. I will post a follow-on comment soon which tries to move that discussion forward.

          2) This version of the spec phrases password procedure arguments as CLOBs in order to address the Java vulnerability described here: http://securesoftware.blogspot.com/2009/01/java-security-why-not-to-use-string.html An alternative approach would be to introduce a new Derby datatype corresponding to char[]. Other suggestions are welcome.

          Thanks in advance for reading this spec.

          Show
          Rick Hillegas added a comment - Attaching a first version of a functional spec for NATIVE user management. This is an attempt to capture the discussion so far. Having a concrete proposal in front of us may help move the discussion forward. Your feedback is appreciated. The following topics need more discussion: 1) This version of the spec does not address the issue of system-wide credentials. I will post a follow-on comment soon which tries to move that discussion forward. 2) This version of the spec phrases password procedure arguments as CLOBs in order to address the Java vulnerability described here: http://securesoftware.blogspot.com/2009/01/java-security-why-not-to-use-string.html An alternative approach would be to introduce a new Derby datatype corresponding to char[]. Other suggestions are welcome. Thanks in advance for reading this spec.
          Hide
          Rick Hillegas added a comment -

          Here is a proposal for how NATIVE user management can handle system-wide users. That is, this proposal handles authenticating orderly engine shutdown, database creation/restoration, and other situations where there is no database in focus.

          1) By default, NATIVE authentication successfully authenticates all user/password pairs in system-wide situations. That is, there are no authentication checks preventing orderly engine shutdown and database creation/restoration. In this default mode, Derby is vulnerable to denial-of-service and resource-hogging exploits by anyone who can craft their own connection URLs.

          2) We can introduce a new Derby property: derby.authentication.native.credentialsDB. This property can be pointed at a distinguished Derby database. When this property is set, the NATIVE credentials in the distinguished database are used for all authentication. That is, credentials in the distinguished database work for system-wide authentication as well as for ordinary connections to Derby databases. A typical use-case would be a networked Derby application which uses a single database; in this use-case, that database would be set to be the distinguished credentials database.

          3) Note that what has been proposed so far merely puts an authentication hurdle in front of denial-of-service and resource-hogging exploits. Any legal user could still shutdown the engine and create unlimited numbers of databases. More protection against these attacks would be provided by completing the system privileges work described by DERBY-2109.

          A variation on this proposal would be:

          2') The distinguished credentials database is only used for system-wide situations. When connecting to an existing Derby database, the NATIVE credentials in that database are used. I think this is a more difficult proposal because it could result in a single identity having to maintain two sets of credentials: a system-wide set and a database-specific set. Note, however, that there is no distinction between (2) and (2') for the typical use-case cited above, in which a single database serves both as the distinguished credentials database as well as the application data store.

          For extra credit, we might want to make it easy to support the following scenario:

          A) An application could supply a custom authenticator which relies on NATIVE authentication when authenticating connections to existing databases but which validates system-wide users via LDAP or local OS accounts. I am not sure it is worth spending extra effort to make this scenario easy to support. That is because this usage suffers from the same problem as (2'): a single identity would have to maintain multiple sets of credentials.

          ---------------------------------------------------------

          More on this problem of multiple credentials for a single identity:

          It is clear that Derby authentication was designed to make it possible for an identity to have multiple sets of credentials. This feature can be exploited currently by both BUILTIN and application-supplied authentication schemes. However, it does not seem to me that the feature is completely baked. In particular, at database creation time there is no way to tease apart which set of credentials you intend. This forces you to keep both sets in sync. That is because database creation and restoration entail 2 separate credentials checks: an initial check using system-wide credentials followed by a connection attempt using database-specific credentials.

          If you want to further investigate this double authentication problem, try out the attached DummyAuthenticator and its dummyCredentials.properties file. Boot ij with the following command line and try to create a database:

          java \
          -Dderby.connection.requireAuthentication=true \
          -Dderby.authentication.provider=DummyAuthenticator \
          -Dderby.database.sqlAuthorization=true \
          org.apache.derby.tools.ij

          Show
          Rick Hillegas added a comment - Here is a proposal for how NATIVE user management can handle system-wide users. That is, this proposal handles authenticating orderly engine shutdown, database creation/restoration, and other situations where there is no database in focus. 1) By default, NATIVE authentication successfully authenticates all user/password pairs in system-wide situations. That is, there are no authentication checks preventing orderly engine shutdown and database creation/restoration. In this default mode, Derby is vulnerable to denial-of-service and resource-hogging exploits by anyone who can craft their own connection URLs. 2) We can introduce a new Derby property: derby.authentication.native.credentialsDB. This property can be pointed at a distinguished Derby database. When this property is set, the NATIVE credentials in the distinguished database are used for all authentication. That is, credentials in the distinguished database work for system-wide authentication as well as for ordinary connections to Derby databases. A typical use-case would be a networked Derby application which uses a single database; in this use-case, that database would be set to be the distinguished credentials database. 3) Note that what has been proposed so far merely puts an authentication hurdle in front of denial-of-service and resource-hogging exploits. Any legal user could still shutdown the engine and create unlimited numbers of databases. More protection against these attacks would be provided by completing the system privileges work described by DERBY-2109 . A variation on this proposal would be: 2') The distinguished credentials database is only used for system-wide situations. When connecting to an existing Derby database, the NATIVE credentials in that database are used. I think this is a more difficult proposal because it could result in a single identity having to maintain two sets of credentials: a system-wide set and a database-specific set. Note, however, that there is no distinction between (2) and (2') for the typical use-case cited above, in which a single database serves both as the distinguished credentials database as well as the application data store. For extra credit, we might want to make it easy to support the following scenario: A) An application could supply a custom authenticator which relies on NATIVE authentication when authenticating connections to existing databases but which validates system-wide users via LDAP or local OS accounts. I am not sure it is worth spending extra effort to make this scenario easy to support. That is because this usage suffers from the same problem as (2'): a single identity would have to maintain multiple sets of credentials. --------------------------------------------------------- More on this problem of multiple credentials for a single identity: It is clear that Derby authentication was designed to make it possible for an identity to have multiple sets of credentials. This feature can be exploited currently by both BUILTIN and application-supplied authentication schemes. However, it does not seem to me that the feature is completely baked. In particular, at database creation time there is no way to tease apart which set of credentials you intend. This forces you to keep both sets in sync. That is because database creation and restoration entail 2 separate credentials checks: an initial check using system-wide credentials followed by a connection attempt using database-specific credentials. If you want to further investigate this double authentication problem, try out the attached DummyAuthenticator and its dummyCredentials.properties file. Boot ij with the following command line and try to create a database: java \ -Dderby.connection.requireAuthentication=true \ -Dderby.authentication.provider=DummyAuthenticator \ -Dderby.database.sqlAuthorization=true \ org.apache.derby.tools.ij
          Hide
          Rick Hillegas added a comment -

          In the next rev of the spec, I will rephrase the password procedure args as VARCHAR( 32672 ). It should be possible to wrap a char[] password in a CharArrayReader and use CallableStatement.setCharacterStream() to pass the Reader to Derby. CharArrayReader promises that it does NOT copy the char[] used to construct it. When the application zeroes out its char[] password, the contents of the CharArrayReader will be cleared too.

          Show
          Rick Hillegas added a comment - In the next rev of the spec, I will rephrase the password procedure args as VARCHAR( 32672 ). It should be possible to wrap a char[] password in a CharArrayReader and use CallableStatement.setCharacterStream() to pass the Reader to Derby. CharArrayReader promises that it does NOT copy the char[] used to construct it. When the application zeroes out its char[] password, the contents of the CharArrayReader will be cleared too.
          Hide
          Dag H. Wanvik added a comment -

          > 2) This version of the spec phrases password procedure arguments as
          > CLOBs in order to address the Java vulnerability described here:
          > http://securesoftware.blogspot.com/2009/01/java-security-why-not-to-use-string.html
          > An alternative approach would be to introduce a new Derby datatype
          > corresponding to char[]. Other suggestions are welcome.

          Btw, this article mentions not String objects are not necessarily
          immutable (although the interened strings are):

          http://directwebremoting.org/blog/joe/2005/05/26/java_lang_string_is_not_immutable.html

          I checked that with our default server policy, this attack doesn't
          work:

          java.security.AccessControlException: access denied (java.lang.RuntimePermission accessDeclaredMembers)
          ***
          lowercase

          Good to know.

          Show
          Dag H. Wanvik added a comment - > 2) This version of the spec phrases password procedure arguments as > CLOBs in order to address the Java vulnerability described here: > http://securesoftware.blogspot.com/2009/01/java-security-why-not-to-use-string.html > An alternative approach would be to introduce a new Derby datatype > corresponding to char[]. Other suggestions are welcome. Btw, this article mentions not String objects are not necessarily immutable (although the interened strings are): http://directwebremoting.org/blog/joe/2005/05/26/java_lang_string_is_not_immutable.html I checked that with our default server policy, this attack doesn't work: java.security.AccessControlException: access denied (java.lang.RuntimePermission accessDeclaredMembers) *** lowercase Good to know.
          Hide
          Rick Hillegas added a comment -

          Thanks for that pointer and for running that experiment, Dag!

          Show
          Rick Hillegas added a comment - Thanks for that pointer and for running that experiment, Dag!
          Hide
          Dag H. Wanvik added a comment -

          I have read through the attached spec document UserManagement.html, thanks Rick! Looks like we're aiming for a much better user experience with this one. Some comments.

          1) Is locking out the users unconditionally after the password expiration nice? You do provide a method (modifyPassword) to let users update their password. How about letting the connect issue a warning is the password is getting stale according to some criterion? This would enable user apps to take action before it's too late and the DBO needs to be called into action, which could be impractical. Also there is a security downside to that, in that the DBO would have access to the users' password. Presently, its kind of moot, since the DBO can access all schemas anyway, but we might change that in the future..

          2) How do you envisage the initial DBO credentials be specified? For the controlling property to be allowed to be set, you state

          "derby.authentication.provider - ... This value can only be set if there are credentials for the DBO in SYSUSERS."

          So I wonder about the bootstrap here: DBO credentials could be taken from supplied credentials (i.e. they would be required) in the URL with the "create=true". You state:

          "If you set this value (i.e. derby.authentication.provider -dhw) using a system property before you create databases, all new databases will automatically be set to use NATIVE authentication"

          If so, it seems it would be too late the call createUser to create the DBO manually? The use of the URL would also force a string with the DBO password to be constructed in that case, right?

          3) Like you, I also have reservations about the Clob datatype interface, but it does avoid the complexity of a new Derby type..not sure what's the right thing to do, maybe you could make an example with a Derby type so we can see how its looks..

          4) nit: examples have password[0] instead of password[i] in the loop to erase the password array characters

          5) In the examples, it may be useful to state that if users are created from a network client, ssl should be enabled. Not important here, but as a note to self when we write the docs based on this in the future..

          Show
          Dag H. Wanvik added a comment - I have read through the attached spec document UserManagement.html, thanks Rick! Looks like we're aiming for a much better user experience with this one. Some comments. 1) Is locking out the users unconditionally after the password expiration nice? You do provide a method (modifyPassword) to let users update their password. How about letting the connect issue a warning is the password is getting stale according to some criterion? This would enable user apps to take action before it's too late and the DBO needs to be called into action, which could be impractical. Also there is a security downside to that, in that the DBO would have access to the users' password. Presently, its kind of moot, since the DBO can access all schemas anyway, but we might change that in the future.. 2) How do you envisage the initial DBO credentials be specified? For the controlling property to be allowed to be set, you state "derby.authentication.provider - ... This value can only be set if there are credentials for the DBO in SYSUSERS." So I wonder about the bootstrap here: DBO credentials could be taken from supplied credentials (i.e. they would be required) in the URL with the "create=true". You state: "If you set this value (i.e. derby.authentication.provider -dhw) using a system property before you create databases, all new databases will automatically be set to use NATIVE authentication" If so, it seems it would be too late the call createUser to create the DBO manually? The use of the URL would also force a string with the DBO password to be constructed in that case, right? 3) Like you, I also have reservations about the Clob datatype interface, but it does avoid the complexity of a new Derby type..not sure what's the right thing to do, maybe you could make an example with a Derby type so we can see how its looks.. 4) nit: examples have password [0] instead of password [i] in the loop to erase the password array characters 5) In the examples, it may be useful to state that if users are created from a network client, ssl should be enabled. Not important here, but as a note to self when we write the docs based on this in the future..
          Hide
          Dag H. Wanvik added a comment -

          Re my item 3): I just read about your suggestion for the CharArrayReader to wrap passwords, sounds like a good idea!

          Show
          Dag H. Wanvik added a comment - Re my item 3): I just read about your suggestion for the CharArrayReader to wrap passwords, sounds like a good idea!
          Hide
          Dag H. Wanvik added a comment -

          I like the idea of using a dedicated database for all the credentials (system level, db level) and avoid the problem of maintaining two sets.
          As for the denial of service attack you mention not being covered in the proposed solution, we could require DBO credentials for the system level operations? We do that already for encryption, upgrade and system shutdown if sqlAuthorization is enabled (DERBY-2264).

          Show
          Dag H. Wanvik added a comment - I like the idea of using a dedicated database for all the credentials (system level, db level) and avoid the problem of maintaining two sets. As for the denial of service attack you mention not being covered in the proposed solution, we could require DBO credentials for the system level operations? We do that already for encryption, upgrade and system shutdown if sqlAuthorization is enabled ( DERBY-2264 ).
          Hide
          Rick Hillegas added a comment -

          Thanks for reading the spec, Dag. Some responses follow. I will incorporate your suggestions into the next rev of the spec:

          1) I can see the value of warning users that their password's lifetime is running out. How about:

          a) Raise a warning if the password will expire within a week.

          b) Raise an exception if the password has expired.

          2) I will try to describe the bootstrapping mechanism better in the next rev of the spec. The idea is that the bootstrap credentials are supplied in the connection arguments when the database is created. If the database is being newly created, then we just trust the bootstrap credentials.

          The credentials could be supplied by the URL or in the Properties object which accompanies the connection attempt or in the DataSource. Derby will internally call createUser() to persist credentials for the DBO when the database is created.

          It is true that the current JDBC connection methods suffer from the String vulnerability described by the link above. I expect that the JDBC expert group will address this vulnerability in JDBC 4.2. In Java 8 I expect that users will be able to state their passwords in a safer way.

          3) Agreed.

          4) Thanks, I'll clean that up.

          5) Thanks, I'll add a note about this to the documentation section. SSL is essential for safeguarding credentials when using the Derby network client.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for reading the spec, Dag. Some responses follow. I will incorporate your suggestions into the next rev of the spec: 1) I can see the value of warning users that their password's lifetime is running out. How about: a) Raise a warning if the password will expire within a week. b) Raise an exception if the password has expired. 2) I will try to describe the bootstrapping mechanism better in the next rev of the spec. The idea is that the bootstrap credentials are supplied in the connection arguments when the database is created. If the database is being newly created, then we just trust the bootstrap credentials. The credentials could be supplied by the URL or in the Properties object which accompanies the connection attempt or in the DataSource. Derby will internally call createUser() to persist credentials for the DBO when the database is created. It is true that the current JDBC connection methods suffer from the String vulnerability described by the link above. I expect that the JDBC expert group will address this vulnerability in JDBC 4.2. In Java 8 I expect that users will be able to state their passwords in a safer way. 3) Agreed. 4) Thanks, I'll clean that up. 5) Thanks, I'll add a note about this to the documentation section. SSL is essential for safeguarding credentials when using the Derby network client. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Thanks for the additional comments, Dag. I think you are recommending that the default behavior should be to refuse to authenticate system-wide operations (like orderly engine shutdown) if a credentials DB has not been specified. I think that sounds safer. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for the additional comments, Dag. I think you are recommending that the default behavior should be to refuse to authenticate system-wide operations (like orderly engine shutdown) if a credentials DB has not been specified. I think that sounds safer. Thanks.
          Hide
          Dag H. Wanvik added a comment -

          Hmm, that was not what I was thinking, but is an interesting idea. My point is this: even is a user is authenticated, he would not be allowed to perform system level operations unless the user is actually the DBO. We could possibly be able to delegate (grant) this ability somehow.

          Show
          Dag H. Wanvik added a comment - Hmm, that was not what I was thinking, but is an interesting idea. My point is this: even is a user is authenticated, he would not be allowed to perform system level operations unless the user is actually the DBO. We could possibly be able to delegate (grant) this ability somehow.
          Hide
          Dag H. Wanvik added a comment -

          Another issue, given that we have one credentials database, would there be an a priori DBO or sys admin identity there? Probably not, since the DBO gets to be the user that creates the database. But if so, we have no DBO role at the systems level, and anybody authenticated would be allowed to perform system level operations. All users are created equal Which may be what you said all along If so, forget my previous comment.

          Show
          Dag H. Wanvik added a comment - Another issue, given that we have one credentials database, would there be an a priori DBO or sys admin identity there? Probably not, since the DBO gets to be the user that creates the database. But if so, we have no DBO role at the systems level, and anybody authenticated would be allowed to perform system level operations. All users are created equal Which may be what you said all along If so, forget my previous comment.
          Hide
          Rick Hillegas added a comment -

          Thanks for the additional comments, Dag. For some applications, it's probably ok to make all DBOs system administrators. Not sure that's going to be ideal for all applications but it might take us a long way.

          Your comments suggest this side-effect of having a single credentials database: We could implement system privileges via GRANT/REVOKE in that database rather than via the Java security mechanisms proposed by DERBY-2109. GRANT/REVOKE is much easier to understand and administer than Java security's policy files. Not sure how to make this work with our other authentication schemes (LDAP/BUILTIN/custom), but it might be possible.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the additional comments, Dag. For some applications, it's probably ok to make all DBOs system administrators. Not sure that's going to be ideal for all applications but it might take us a long way. Your comments suggest this side-effect of having a single credentials database: We could implement system privileges via GRANT/REVOKE in that database rather than via the Java security mechanisms proposed by DERBY-2109 . GRANT/REVOKE is much easier to understand and administer than Java security's policy files. Not sure how to make this work with our other authentication schemes (LDAP/BUILTIN/custom), but it might be possible. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Attaching a second rev of UserManagement.html.

          Show
          Rick Hillegas added a comment - Attaching a second rev of UserManagement.html.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for the updated spec! Using the varchar instead of the CLOB seems more reasonable, and one can stream the password to that type, too.
          Yes, I think we could consider using the credentials database for implementing system privileges. If the user wanted system privileges but still wants to use LDAP or custom authentication, the credentials database could still be used for authorization purposes for the system privileges. The GRANT/REVOKE could be independent on how we authenticate.

          Show
          Dag H. Wanvik added a comment - Thanks for the updated spec! Using the varchar instead of the CLOB seems more reasonable, and one can stream the password to that type, too. Yes, I think we could consider using the credentials database for implementing system privileges. If the user wanted system privileges but still wants to use LDAP or custom authentication, the credentials database could still be used for authorization purposes for the system privileges. The GRANT/REVOKE could be independent on how we authenticate.
          Hide
          Rick Hillegas added a comment -

          Attaching a third rev of UserManagement.html. This version fills in the Documentation section.

          Show
          Rick Hillegas added a comment - Attaching a third rev of UserManagement.html. This version fills in the Documentation section.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-01-aa-sysusers.diff. This first patch adds the SYSUSERS table. I will add upgrade tests in a follow-on patch. I am running regression tests now.

          This patch makes the following changes:

          1) Creates the SYSUSERS table and a unique index on its USERNAME column. This happens in newly created 10.9 databases and in databases which have been hard-upgraded to 10.9.

          2) When SQL authorization is enabled, lets only the DBO view SYSUSERS and prevents even the DBO from viewing the PASSWORD column.

          This patch touches a lot of files for these reasons:

          i) Adding a new catalog involves touching several data dictionary files.

          ii) Many of our tests have canonized the results of selects from SYSTABLES. We should consider adjusting these tests so that they will not break the next time we add a catalog. This might be a good task for a newcomer or GSoC student.

          iii) A couple other files had to be touched because SYSUSERS is a special table with restricted access.

          Touches the following files:

          ------------

          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          A java/engine/org/apache/derby/impl/sql/catalog/SYSUSERSRowFactory.java
          M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
          A java/engine/org/apache/derby/iapi/sql/dictionary/UserDescriptor.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java

          Adds the SYSUSERS table to the DataDictionary.

          ------------

          M java/engine/org/apache/derby/iapi/types/SQLChar.java
          M java/engine/org/apache/derby/iapi/types/SQLVarchar.java

          Special logic to allow us to wrap a SQLVarchar around the char[] used to hold a password. This is not intended as general support for representing Strings as char[].

          ------------

          M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New check to prevent unauthorized viewing of SYSUSERS.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/DBOAccessTest.java

          New test case to verify that Derby prevents unauthorized viewing of SYSUSERS.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ViewsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
          M java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out

          Additional tests which had to be changed because a new catalog appears when you select from SYSTABLES.

          Show
          Rick Hillegas added a comment - Attaching derby-866-01-aa-sysusers.diff. This first patch adds the SYSUSERS table. I will add upgrade tests in a follow-on patch. I am running regression tests now. This patch makes the following changes: 1) Creates the SYSUSERS table and a unique index on its USERNAME column. This happens in newly created 10.9 databases and in databases which have been hard-upgraded to 10.9. 2) When SQL authorization is enabled, lets only the DBO view SYSUSERS and prevents even the DBO from viewing the PASSWORD column. This patch touches a lot of files for these reasons: i) Adding a new catalog involves touching several data dictionary files. ii) Many of our tests have canonized the results of selects from SYSTABLES. We should consider adjusting these tests so that they will not break the next time we add a catalog. This might be a good task for a newcomer or GSoC student. iii) A couple other files had to be touched because SYSUSERS is a special table with restricted access. Touches the following files: ------------ M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java A java/engine/org/apache/derby/impl/sql/catalog/SYSUSERSRowFactory.java M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java A java/engine/org/apache/derby/iapi/sql/dictionary/UserDescriptor.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java Adds the SYSUSERS table to the DataDictionary. ------------ M java/engine/org/apache/derby/iapi/types/SQLChar.java M java/engine/org/apache/derby/iapi/types/SQLVarchar.java Special logic to allow us to wrap a SQLVarchar around the char[] used to hold a password. This is not intended as general support for representing Strings as char[]. ------------ M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New check to prevent unauthorized viewing of SYSUSERS. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/DBOAccessTest.java New test case to verify that Derby prevents unauthorized viewing of SYSUSERS. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ViewsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out M java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out Additional tests which had to be changed because a new catalog appears when you select from SYSTABLES.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-866-01-aa-sysusers.diff.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-866-01-aa-sysusers.diff.
          Hide
          Mike Matrigali added a comment -

          In the proposed system is there a separate database that has to be accessed for every connection to a user database?

          Show
          Mike Matrigali added a comment - In the proposed system is there a separate database that has to be accessed for every connection to a user database?
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          The credentials database might be a separate database. Or it might be the ordinary database used by the application. I imagine these use cases:

          1) Simple - The application has one database. In this case, that database also serves as the credentials database.

          2) Complex - The application has many databases or the JVM is running many Derby applications. In this case, there would be a separate credentials database. Every new connection to a user database would involve accessing the credentials database in order to authenticate the user.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Mike, The credentials database might be a separate database. Or it might be the ordinary database used by the application. I imagine these use cases: 1) Simple - The application has one database. In this case, that database also serves as the credentials database. 2) Complex - The application has many databases or the JVM is running many Derby applications. In this case, there would be a separate credentials database. Every new connection to a user database would involve accessing the credentials database in order to authenticate the user. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-01-ab-sysusers.diff. This second rev of the patch adds some additional tests, including an upgrade test.

          Adds the following tests:

          1) Verifies that you can't use views to subvert the authorization checks on SYSUSERS.

          2) Verifies that SYSUSERS is created by hard-upgrade but not by soft-upgrade.

          I ran the upgrade tests against the following list of old releases. I did not see any errors other than the ones which appear without this patch:

          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.2.2.1
          10.3.1.4
          10.3.2.1
          10.3.3.0
          10.4.1.3
          10.4.2.0
          10.4.2.1
          10.5.1.1
          10.5.2.0
          10.5.3.0
          10.6.1.0
          10.6.2.1
          10.7.1.1
          10.8.1.2
          10.8.2.2

          Touches an additional file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-01-ab-sysusers.diff. This second rev of the patch adds some additional tests, including an upgrade test. Adds the following tests: 1) Verifies that you can't use views to subvert the authorization checks on SYSUSERS. 2) Verifies that SYSUSERS is created by hard-upgrade but not by soft-upgrade. I ran the upgrade tests against the following list of old releases. I did not see any errors other than the ones which appear without this patch: 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.2.2.1 10.3.1.4 10.3.2.1 10.3.3.0 10.4.1.3 10.4.2.0 10.4.2.1 10.5.1.1 10.5.2.0 10.5.3.0 10.6.1.0 10.6.2.1 10.7.1.1 10.8.1.2 10.8.2.2 Touches an additional file: M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java
          Hide
          Rick Hillegas added a comment -

          Committed derby-866-01-ab-sysusers.diff at subversion revision 1212562.

          Show
          Rick Hillegas added a comment - Committed derby-866-01-ab-sysusers.diff at subversion revision 1212562.
          Hide
          Mike Matrigali added a comment -

          Will the new system catalogs be present in an embedded database that does not have any interest in this user management implementation? If at all possible it would be nice if this implementation was modular and did not affect code, code paths, and disk footprint if user wants no user managment, or uses their own user management. Would it be possible to use the existing pluggable interfaces to implement this alternate implementation, and if not maybe that interface needs to be enhanced?

          Show
          Mike Matrigali added a comment - Will the new system catalogs be present in an embedded database that does not have any interest in this user management implementation? If at all possible it would be nice if this implementation was modular and did not affect code, code paths, and disk footprint if user wants no user managment, or uses their own user management. Would it be possible to use the existing pluggable interfaces to implement this alternate implementation, and if not maybe that interface needs to be enhanced?
          Hide
          Mike Matrigali added a comment -

          In the past I worked on a system that had a 2 database system where one database had info about the other databases. This caused some
          support nightmares, especially when a single database problem could prevent access to all databases.

          With derby it is nice that all databases are a unit unto themselves and that is what the current architecture is built around. A number of issues
          jump to mind if you start assume multiple access on one connection:
          o now backup of a database becomes more complicated as it can be out of sync with the master db
          o are databases now not portable across systems, or only if combined with master db?
          o how does this affect replication?
          o can the master db be encrypted, and if so how do you get multiple keys from single connection?
          o does collation of the 2 dbs have an effect, now do all dbs in the system need same collation?

          Having a separate db to me seems like even more administration for derby.

          Also for derby a database is a pretty heavy weight object in the system. A good Derby application is one that is embedded and uses a single
          database. This is the best case usage of Derby. Of course multiple databases are allowed and work but often lead to unexpected problems.
          Adding a 2nd db in a normal case may double the memory requirements of derby given the 1 cache per database model currently implemented,
          n background threads per active db, ...

          Show
          Mike Matrigali added a comment - In the past I worked on a system that had a 2 database system where one database had info about the other databases. This caused some support nightmares, especially when a single database problem could prevent access to all databases. With derby it is nice that all databases are a unit unto themselves and that is what the current architecture is built around. A number of issues jump to mind if you start assume multiple access on one connection: o now backup of a database becomes more complicated as it can be out of sync with the master db o are databases now not portable across systems, or only if combined with master db? o how does this affect replication? o can the master db be encrypted, and if so how do you get multiple keys from single connection? o does collation of the 2 dbs have an effect, now do all dbs in the system need same collation? Having a separate db to me seems like even more administration for derby. Also for derby a database is a pretty heavy weight object in the system. A good Derby application is one that is embedded and uses a single database. This is the best case usage of Derby. Of course multiple databases are allowed and work but often lead to unexpected problems. Adding a 2nd db in a normal case may double the memory requirements of derby given the 1 cache per database model currently implemented, n background threads per active db, ...
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          Thanks for thinking about this proposal. I would like to begin addressing your comments.

          NATIVE authentication introduces two new conglomerates, the SYSUSERS heap and a primary key index on its USERNAME column. On my system, taking default page sizes, those empty conglomerates take up 16K space on disk. I believe that is noise compared to the 1.8M consumed by the entire empty Derby database. I don't see much value in optimizing out that noise.

          However, some day we may want to seriously slim down the size of our empty database. If we want to do that, then I could see value in building a mechanism which creates certain catalogs only if they are actually needed. The following catalogs would be targets for this mechanism. All of these catalogs start out empty and will never have any tuples unless the application uses the indicated features:

          SQL Authorization catalogs:

          sys.syscolperms
          sys.sysperms
          sys.sysroles
          sys.systableperms

          SQL Routines:

          sys.sysfiles

          Sequences:

          sys.syssequences

          NATIVE authentication:

          sys.sysusers

          Views:

          sys.sysviews

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Mike, Thanks for thinking about this proposal. I would like to begin addressing your comments. NATIVE authentication introduces two new conglomerates, the SYSUSERS heap and a primary key index on its USERNAME column. On my system, taking default page sizes, those empty conglomerates take up 16K space on disk. I believe that is noise compared to the 1.8M consumed by the entire empty Derby database. I don't see much value in optimizing out that noise. However, some day we may want to seriously slim down the size of our empty database. If we want to do that, then I could see value in building a mechanism which creates certain catalogs only if they are actually needed. The following catalogs would be targets for this mechanism. All of these catalogs start out empty and will never have any tuples unless the application uses the indicated features: SQL Authorization catalogs: sys.syscolperms sys.sysperms sys.sysroles sys.systableperms SQL Routines: sys.sysfiles Sequences: sys.syssequences NATIVE authentication: sys.sysusers Views: sys.sysviews Thanks, -Rick
          Hide
          Kathey Marsden added a comment -

          I have really only skimmed the spec but totally agree that a separate credentialsDB seems too heavy weight for Derby and also seems like it would cause trouble in shared environments like Application Servers. It seems like it is geared mostly for network server environments, is that correct?

          I also agree that this should be a plug-able feature and not impact standard embedded environments in any way.

          Show
          Kathey Marsden added a comment - I have really only skimmed the spec but totally agree that a separate credentialsDB seems too heavy weight for Derby and also seems like it would cause trouble in shared environments like Application Servers. It seems like it is geared mostly for network server environments, is that correct? I also agree that this should be a plug-able feature and not impact standard embedded environments in any way.
          Hide
          Rick Hillegas added a comment -

          Hi again Mike,

          I would like to address some of your other concerns.

          Let me start out by noting that no-one is forcing an application to use NATIVE authentication. The application has to opt into this scheme. If the application doesn't need authentication, then this feature will not affect it at all. If the application uses LDAP or a custom authenticator, then this feature has no effect either.

          Like you, I have worked on systems which had a master database in addition to the application-specific databases. I share your anxiety about the complexity of those systems. Because of those anxieties, I resisted this approach for a long time. However, I have settled on this approach because it is nonetheless more appealing than the other alternatives which I listed above in my 2011-11-22 comment. Option (I) is impossible to implement securely. Option (III) is not a solution at all, it just punts the problem back to the user. So I have settled on option (II).

          This proposal tries not to tightly couple the databases. What is being discussed here is not a master database in the sense of the old systems which we used to work on. This is just a credentials database. In most systems which opt into NATIVE authentication, I suspect that there will only be one database. That is, for them the application database will do double duty as the credentials database too.

          Of course, once we introduce the possibility of a separate credentials database, then developers may think up other uses for it and try to use it to perform some of the other functions of a master database. We will have to be vigilant about that feature-creep.

          Let me address some of your other concerns.

          o You observe that "a single database problem could prevent access to all databases". True enough. But this is just a special case of the fact that the authenticator, whatever it is, is a bottleneck and single-point-of-failure. LDAP could go down and so could a user-supplied custom authenticator.

          o You observe that "now backup of a database becomes more complicated as it can be out of sync with the master db". Again, I do not see a new danger being introduced here. The linkage to the credentials database is no stronger than the linkage to LDAP or to a user-supplied custom authenticator.

          o You ask, "are databases now not portable across systems, or only if combined with master db?" Again, the same response. No new danger is introduced other than the danger already experienced with LDAP and user-supplied custom authenticators. If you move your database to another system, then you must make sure that the authentication service is available there too. I would even go so far as to argue that NATIVE authentication is more portable than LDAP for the usual case of an application with a single database which serves as the credentials database. The credentials are in the database. Nothing could be more portable than that.

          o You ask, "how does this affect replication?" I do not see any replication issues for a system which has only one database and which uses it as the credentials database. In systems which manage multiple databases in the same JVM, I do not think that replication failover has really been thought through. In those systems the slave database will be on a separate machine and will somehow have to be copied back to the system hosting the common JVM. After the slave database becomes usable by the common JVM, it will have access to the credentials database managed by that JVM. However, thank you for raising this topic. This is an area we will want to test.

          o You ask, "can the master db be encrypted, and if so how do you get multiple keys from single connection?" Yes, it can be encrypted. As with any encrypted database, it will have to be booted by a super-user before anyone else can use it. Once the encrypted database has been booted, no-one else needs to supply the encryption key. For the common case of a system with one database which also serves as the credentials database, no additional hurdles are introduced by NATIVE authentication. For systems which manage multiple databases, the credentials database will have to be booted with an encryption key before anyone can do any work. But there is nothing special about this situation either. LDAP has to be booted and so does a user-supplied custom authenticator.

          o You ask, "does collation of the 2 dbs have an effect, now do all dbs in the system need same collation?" I don't see why. In Derby, user names are stored in SYSSCHEMAS.AUTHORIZATIONID. The string columns of Derby's catalogs are not governed by the collation used for user tables. Even if you enable a collation for user tables, Derby's catalogs continue to obey the comparison/sorting semantics of databases which don't use collations.

          o You observe, "Having a separate db to me seems like even more administration for derby." I think that this is again a general comment about any authentication scheme. Yes, someone has to administer LDAP and the user-supplied custom authenticator.

          o You observe, "Also for derby a database is a pretty heavy weight object in the system. A good Derby application is one that is embedded and uses a single database. This is the best case usage of Derby. Of course multiple databases are allowed and work but often lead to unexpected problems." Agreed. I expect that the usual use case will be an application with a single database which also serves as the credentials database.

          o You continue, "Adding a 2nd db in a normal case may double the memory requirements of derby given the 1 cache per database model currently implemented, n background threads per active db, ..." Here we are talking about a system which has a separate credentials database. That would be a system which is already managing multiple Derby databases.

          Hope that this helps allay your concerns.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi again Mike, I would like to address some of your other concerns. Let me start out by noting that no-one is forcing an application to use NATIVE authentication. The application has to opt into this scheme. If the application doesn't need authentication, then this feature will not affect it at all. If the application uses LDAP or a custom authenticator, then this feature has no effect either. Like you, I have worked on systems which had a master database in addition to the application-specific databases. I share your anxiety about the complexity of those systems. Because of those anxieties, I resisted this approach for a long time. However, I have settled on this approach because it is nonetheless more appealing than the other alternatives which I listed above in my 2011-11-22 comment. Option (I) is impossible to implement securely. Option (III) is not a solution at all, it just punts the problem back to the user. So I have settled on option (II). This proposal tries not to tightly couple the databases. What is being discussed here is not a master database in the sense of the old systems which we used to work on. This is just a credentials database. In most systems which opt into NATIVE authentication, I suspect that there will only be one database. That is, for them the application database will do double duty as the credentials database too. Of course, once we introduce the possibility of a separate credentials database, then developers may think up other uses for it and try to use it to perform some of the other functions of a master database. We will have to be vigilant about that feature-creep. Let me address some of your other concerns. o You observe that "a single database problem could prevent access to all databases". True enough. But this is just a special case of the fact that the authenticator, whatever it is, is a bottleneck and single-point-of-failure. LDAP could go down and so could a user-supplied custom authenticator. o You observe that "now backup of a database becomes more complicated as it can be out of sync with the master db". Again, I do not see a new danger being introduced here. The linkage to the credentials database is no stronger than the linkage to LDAP or to a user-supplied custom authenticator. o You ask, "are databases now not portable across systems, or only if combined with master db?" Again, the same response. No new danger is introduced other than the danger already experienced with LDAP and user-supplied custom authenticators. If you move your database to another system, then you must make sure that the authentication service is available there too. I would even go so far as to argue that NATIVE authentication is more portable than LDAP for the usual case of an application with a single database which serves as the credentials database. The credentials are in the database. Nothing could be more portable than that. o You ask, "how does this affect replication?" I do not see any replication issues for a system which has only one database and which uses it as the credentials database. In systems which manage multiple databases in the same JVM, I do not think that replication failover has really been thought through. In those systems the slave database will be on a separate machine and will somehow have to be copied back to the system hosting the common JVM. After the slave database becomes usable by the common JVM, it will have access to the credentials database managed by that JVM. However, thank you for raising this topic. This is an area we will want to test. o You ask, "can the master db be encrypted, and if so how do you get multiple keys from single connection?" Yes, it can be encrypted. As with any encrypted database, it will have to be booted by a super-user before anyone else can use it. Once the encrypted database has been booted, no-one else needs to supply the encryption key. For the common case of a system with one database which also serves as the credentials database, no additional hurdles are introduced by NATIVE authentication. For systems which manage multiple databases, the credentials database will have to be booted with an encryption key before anyone can do any work. But there is nothing special about this situation either. LDAP has to be booted and so does a user-supplied custom authenticator. o You ask, "does collation of the 2 dbs have an effect, now do all dbs in the system need same collation?" I don't see why. In Derby, user names are stored in SYSSCHEMAS.AUTHORIZATIONID. The string columns of Derby's catalogs are not governed by the collation used for user tables. Even if you enable a collation for user tables, Derby's catalogs continue to obey the comparison/sorting semantics of databases which don't use collations. o You observe, "Having a separate db to me seems like even more administration for derby." I think that this is again a general comment about any authentication scheme. Yes, someone has to administer LDAP and the user-supplied custom authenticator. o You observe, "Also for derby a database is a pretty heavy weight object in the system. A good Derby application is one that is embedded and uses a single database. This is the best case usage of Derby. Of course multiple databases are allowed and work but often lead to unexpected problems." Agreed. I expect that the usual use case will be an application with a single database which also serves as the credentials database. o You continue, "Adding a 2nd db in a normal case may double the memory requirements of derby given the 1 cache per database model currently implemented, n background threads per active db, ..." Here we are talking about a system which has a separate credentials database. That would be a system which is already managing multiple Derby databases. Hope that this helps allay your concerns. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Kathey,

          Thanks for reading the spec and for your comments. Just to recap some of the points I made in my previous response to Mike:

          o A separate credentials database is only necessary for systems which already manage multiple Derby databases. Presumably those systems are familiar with the problems of managing multiple Derby databases in a single JVM.

          o I do not understand your comment about plugability. This feature will slightly increase the size of Derby's engine jar and the size of an empty Derby database. However, I expect those increases to be well within the percentage growth which we have tolerated for feature releases over the last 6 years.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kathey, Thanks for reading the spec and for your comments. Just to recap some of the points I made in my previous response to Mike: o A separate credentials database is only necessary for systems which already manage multiple Derby databases. Presumably those systems are familiar with the problems of managing multiple Derby databases in a single JVM. o I do not understand your comment about plugability. This feature will slightly increase the size of Derby's engine jar and the size of an empty Derby database. However, I expect those increases to be well within the percentage growth which we have tolerated for feature releases over the last 6 years. Thanks, -Rick
          Hide
          Mike Matrigali added a comment -

          you comment:
          o A separate credentials database is only necessary for systems which already manage multiple Derby databases. Presumably those systems are familiar with the problems of managing multiple Derby databases in a single JVM.

          Is it necessary to have a separate credentials database in a system with multiple databases, or is that an option?

          Show
          Mike Matrigali added a comment - you comment: o A separate credentials database is only necessary for systems which already manage multiple Derby databases. Presumably those systems are familiar with the problems of managing multiple Derby databases in a single JVM. Is it necessary to have a separate credentials database in a system with multiple databases, or is that an option?
          Hide
          Bryan Pendleton added a comment -

          > With derby it is nice that all databases are a unit unto themselves

          +1

          Rick, I know you have tried several times to explain the advantages of a separate database for certain
          purposes, but I'm struggling with it. Is the NetworkServer the primary issue here?

          Would it help to have a configuration option for the NetworkServer where it could be explicitly
          pointed at a specific database and told "use the credentials in THIS database for overall server
          authentication"?

          Then, the administrator of that NetworkServer could choose whether they want to designate
          some database solely for Network Server credentials, or whether they wanted to use one of
          their existing databases, or whether they didn't want to authorize those NetworkServer actions at all.

          Show
          Bryan Pendleton added a comment - > With derby it is nice that all databases are a unit unto themselves +1 Rick, I know you have tried several times to explain the advantages of a separate database for certain purposes, but I'm struggling with it. Is the NetworkServer the primary issue here? Would it help to have a configuration option for the NetworkServer where it could be explicitly pointed at a specific database and told "use the credentials in THIS database for overall server authentication"? Then, the administrator of that NetworkServer could choose whether they want to designate some database solely for Network Server credentials, or whether they wanted to use one of their existing databases, or whether they didn't want to authorize those NetworkServer actions at all.
          Hide
          Rick Hillegas added a comment -

          Thanks for continuing to help me think about this issue, Mike and Bryan. I believe that the scenarios you want to see should be possible to configure. Some responses follow:

          Mike asks: "Is it necessary to have a separate credentials database in a system with multiple databases, or is that an option?" It is an option. The credentials database could just be one of the databases already managed by the application.

          Bryan asks: "Would it help to have a configuration option for the NetworkServer where it could be explicitly
          pointed at a specific database and told 'use the credentials in THIS database for overall server
          authentication'?" Perhaps what you are asking for is a simple way to configure the following behavior:

          i) When connecting to an existing database, use the NATIVE credentials in that database.

          ii) When authenticating system-wide actions (server startup/shutdown, create/restore database), use the credentials in a specially designated credentials DB (which could be one of the existing databases or could be another, separate database).

          Bryan's use can be configured as follows:

          1) Create empty application databases without authentication (but supply credentials so that a dbo account is created).

          2) In each of those databases, set derby.authentication.provider=NATIVE::

          3) Bring down the installation.

          4) Now reboot the installation, setting the following property on the command line: derby.authentication.provider=NATIVE:networkServerCredentialsDB

          5) Continue creating the schemas in your application databases.

          If you can help me describe the scenarios which should be easiest to configure, I can adjust the api to make this possible.

          In any event, did I understand your scenario correctly, Bryan?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for continuing to help me think about this issue, Mike and Bryan. I believe that the scenarios you want to see should be possible to configure. Some responses follow: Mike asks: "Is it necessary to have a separate credentials database in a system with multiple databases, or is that an option?" It is an option. The credentials database could just be one of the databases already managed by the application. Bryan asks: "Would it help to have a configuration option for the NetworkServer where it could be explicitly pointed at a specific database and told 'use the credentials in THIS database for overall server authentication'?" Perhaps what you are asking for is a simple way to configure the following behavior: i) When connecting to an existing database, use the NATIVE credentials in that database. ii) When authenticating system-wide actions (server startup/shutdown, create/restore database), use the credentials in a specially designated credentials DB (which could be one of the existing databases or could be another, separate database). Bryan's use can be configured as follows: 1) Create empty application databases without authentication (but supply credentials so that a dbo account is created). 2) In each of those databases, set derby.authentication.provider=NATIVE:: 3) Bring down the installation. 4) Now reboot the installation, setting the following property on the command line: derby.authentication.provider=NATIVE:networkServerCredentialsDB 5) Continue creating the schemas in your application databases. If you can help me describe the scenarios which should be easiest to configure, I can adjust the api to make this possible. In any event, did I understand your scenario correctly, Bryan? Thanks, -Rick
          Hide
          Bryan Pendleton added a comment -

          Yes, I think that re-states my perspective clearly. Thanks!

          Show
          Bryan Pendleton added a comment - Yes, I think that re-states my perspective clearly. Thanks!
          Hide
          Rick Hillegas added a comment -

          Hi Bryan,

          Would the following api be easy enough to satisfy your use-case:

          1) At the system level, derby.authentication.provider can be set to the following special value:

          -Dderby.authentication.provider=NATIVE:$credentialsDB:

          2) The extra trailing : tells Derby that the $credentialsDB should only be used for system-wide authentication. When creating a new database, Derby performs the following steps:

          a) Authenticates the user/password combination against $credentialsDB.

          b) If that succeeds, creates the new database, stores the user/password in the SYSUSERS catalog of the new database, then sets derby.authentication.provider=NATIVE:: in the database.

          c) Subsequent attempts to connect to the newly created database will be authenticated against the NATIVE credentials stored in the database itself.

          d) However, system-wide operations will continue to be authenticated against $credentialsDB.

          At database creation time, the DBO's local credentials (the ones stored in the new database) start out being the same as the DBO's credentials in $credentialsDB. The DBO has to decide which of these behaviors is better:

          i) Keep $credentialsDB in sync with the local database. That is, whenever the DBO wants to update her local password, she should remember to update her password in $credentialsDB.

          ii) Or let the local password diverge from what's in $credentialsDB.

          If the DBO opts for , then she will use a single password to connect to her database, restore her database, and shutdown the engine.

          If the DBO opts for (ii), then she will use one password to connect to her database and another password to shutdown the engine and restore her database. She will trip over the following speedbump:

          When she restores a database, she will supply her system-wide password (stored in $credentialsDB). The database will be successfully restored. However, she will not have a connection to it. In fact, she will encounter an authentication error. She will have to get a fresh connection to the newly restored database, using her password as it is stored in that database.

          This odd behavior arises because database restoration causes Derby to verify the supplied credentials twice: first at the system-level and then at the local level. This is the existing behavior of Derby today. Currently there is no way to tell Derby to use one set of credentials for the restoration and a separate set of credentials for the database connection.

          Do people think that this extension would be useful/usable? Should this use-case be sanded down further?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Bryan, Would the following api be easy enough to satisfy your use-case: 1) At the system level, derby.authentication.provider can be set to the following special value: -Dderby.authentication.provider=NATIVE:$credentialsDB: 2) The extra trailing : tells Derby that the $credentialsDB should only be used for system-wide authentication. When creating a new database, Derby performs the following steps: a) Authenticates the user/password combination against $credentialsDB. b) If that succeeds, creates the new database, stores the user/password in the SYSUSERS catalog of the new database, then sets derby.authentication.provider=NATIVE:: in the database. c) Subsequent attempts to connect to the newly created database will be authenticated against the NATIVE credentials stored in the database itself. d) However, system-wide operations will continue to be authenticated against $credentialsDB. At database creation time, the DBO's local credentials (the ones stored in the new database) start out being the same as the DBO's credentials in $credentialsDB. The DBO has to decide which of these behaviors is better: i) Keep $credentialsDB in sync with the local database. That is, whenever the DBO wants to update her local password, she should remember to update her password in $credentialsDB. ii) Or let the local password diverge from what's in $credentialsDB. If the DBO opts for , then she will use a single password to connect to her database, restore her database, and shutdown the engine. If the DBO opts for (ii), then she will use one password to connect to her database and another password to shutdown the engine and restore her database. She will trip over the following speedbump: When she restores a database, she will supply her system-wide password (stored in $credentialsDB). The database will be successfully restored. However, she will not have a connection to it. In fact, she will encounter an authentication error. She will have to get a fresh connection to the newly restored database, using her password as it is stored in that database. This odd behavior arises because database restoration causes Derby to verify the supplied credentials twice: first at the system-level and then at the local level. This is the existing behavior of Derby today. Currently there is no way to tell Derby to use one set of credentials for the restoration and a separate set of credentials for the database connection. Do people think that this extension would be useful/usable? Should this use-case be sanded down further? Thanks, -Rick
          Hide
          Bryan Pendleton added a comment -

          Thanks Rick, lots of good material to chew on there.

          Using a trailing colon to enable special behaviors seems pretty subtle, syntactically. I can imagine
          overlooking it when proof-reading somebody else's configuration quickly.

          I agree with you that it will start to get confusing when I have a username/password pair in each
          database, and am not sure which username/password is being used when. Not sure I have a
          better answer for the problems you pose, though.

          Overall, what you propose seems useful to me. The biggest problem I was worried about
          was that there would be some "special" database with a "special" name that was unexpected
          and out of my control, and I would overlook it since it was created automatically by the system
          and I didn't realize it was there. Having a way to point at one of my own, existing databases
          seems to address that problem adequately, to me.

          Show
          Bryan Pendleton added a comment - Thanks Rick, lots of good material to chew on there. Using a trailing colon to enable special behaviors seems pretty subtle, syntactically. I can imagine overlooking it when proof-reading somebody else's configuration quickly. I agree with you that it will start to get confusing when I have a username/password pair in each database, and am not sure which username/password is being used when. Not sure I have a better answer for the problems you pose, though. Overall, what you propose seems useful to me. The biggest problem I was worried about was that there would be some "special" database with a "special" name that was unexpected and out of my control, and I would overlook it since it was created automatically by the system and I didn't realize it was there. Having a way to point at one of my own, existing databases seems to address that problem adequately, to me.
          Hide
          Rick Hillegas added a comment -

          Thanks, Bryan. I agree that the trailing : is a little subtle. How about making the trailing token something bigger, like :LOCAL. The system property could then have these legal values:

          1) derby.authentication.provider=NATIVE:$credentialsDB

          This setting means "Use $credentialsDB to authenticate system-wide operations as well as connections to existing databases."

          2) derby.authentication.provider=NATIVE:$credentialsDB:LOCAL

          This setting means "Use $credentialsDB to authenticate system-wide operations. When connecting to an existing database, authenticate using the credentials stored in its SYSUSERS table."

          To address your other point: Derby never automatically creates a Credentials DB. Derby always uses a database which the application knows about and asked for. The application has to ask Derby to create the Credentials DB and the application has to explicitly point Derby at that database. This is a significant way that a Credentials DB differs from the master databases created by other RDBMS engines.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks, Bryan. I agree that the trailing : is a little subtle. How about making the trailing token something bigger, like :LOCAL. The system property could then have these legal values: 1) derby.authentication.provider=NATIVE:$credentialsDB This setting means "Use $credentialsDB to authenticate system-wide operations as well as connections to existing databases." 2) derby.authentication.provider=NATIVE:$credentialsDB:LOCAL This setting means "Use $credentialsDB to authenticate system-wide operations. When connecting to an existing database, authenticate using the credentials stored in its SYSUSERS table." To address your other point: Derby never automatically creates a Credentials DB. Derby always uses a database which the application knows about and asked for. The application has to ask Derby to create the Credentials DB and the application has to explicitly point Derby at that database. This is a significant way that a Credentials DB differs from the master databases created by other RDBMS engines. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-02-ag-createDropUser.diff. This patch adds the syscs_create_user() and syscs_drop_user() system procedures. I will run regression tests.

          I had originally planned to add just syscs_create_user(). However, I found myself pulling on a ball of yarn because CleanDatabaseSetup needs to return to an initial state as far as possible, and that means dropping users who were added as part of the tests.

          I ended up pulling on another ball of yarn, which bloated up the number of files touched by this patch: I added StandardException to the exception signature of a method in the string datatypes. This percolated throughout the SQL interpreter, adding StandardException to the signatures of many methods. I debated swallowing the exception lower down and reporting it in the log. However, after some thought I decided that the changed methods all deserved to throw StandardException and should have done so from the start.

          In implementing syscs_create_user() I faced the problem that the implementing method phrases its password arg as a char[] array rather than a String. In order to get around the method resolution problems, I introduced a new, internal SQLPassword type. This type is not available and is mostly invisible to Derby users. SQLPassword has the following behavior:

          1) It appears in DatabaseMetaData.getProcedureColumns() as a VARCHAR type.

          2) But its corresponding Java type is char[] rather than String.

          3) When the SQL interpreter sees a SQLPassword, it treats the contents as a password which needs to be smudged out as soon as possible.

          4) I have found one place where knowledge of this internal type leaks out to users: If you select the aliasinfo of the syscs_create_user() procedure and then call the toString() method on that aliasinfo, you will see that the password arg has type PASSWORD rather than VARCHAR.

          Touches the following files:

          ------------

          M java/engine/org/apache/derby/impl/sql/compile/TypeCompilerFactoryImpl.java
          M java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
          M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java
          M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
          M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
          M java/engine/org/apache/derby/iapi/types/TypeId.java
          M java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
          M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
          M java/engine/org/apache/derby/iapi/types/DataType.java
          M java/engine/org/apache/derby/iapi/types/StringDataValue.java
          A java/engine/org/apache/derby/iapi/types/SQLPassword.java
          M java/engine/org/apache/derby/iapi/types/SQLChar.java
          M java/engine/org/apache/derby/iapi/types/SQLVarchar.java
          M java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
          M java/engine/org/apache/derby/iapi/types/DataValueFactory.java
          M java/engine/org/apache/derby/iapi/types/ReaderToUTF8Stream.java
          M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
          M java/engine/org/apache/derby/catalog/types/TypesImplInstanceGetter.java

          Support for new internal SQLPassword datatype.

          ------------

          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          Support for new system procedures syscs_create_user() and syscs_drop_user().

          ------------

          M java/shared/org/apache/derby/shared/common/reference/SQLState.java
          M java/engine/org/apache/derby/loc/messages.xml

          New error message.

          ------------

          M java/engine/org/apache/derby/impl/sql/GenericParameter.java
          M java/engine/org/apache/derby/impl/sql/execute/ValueRow.java
          M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
          M java/engine/org/apache/derby/impl/sql/execute/IndexValueRow.java
          M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
          M java/engine/org/apache/derby/impl/sql/execute/RowUtil.java
          M java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java
          M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
          M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java
          M java/engine/org/apache/derby/impl/sql/execute/BasicSortObserver.java
          M java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java
          M java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java
          M java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java
          M java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
          M java/engine/org/apache/derby/iapi/sql/Activation.java
          M java/engine/org/apache/derby/iapi/sql/execute/ExecRow.java
          M java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java
          M java/engine/org/apache/derby/iapi/types/SQLRef.java
          M java/engine/org/apache/derby/iapi/types/XML.java
          M java/testing/org/apache/derbyTesting/unitTests/store/T_AccessRow.java

          SQLException percolation across method signatures. Mostly these are trivial changes. The change to EmbedPreparedStatement looks big but it just involves wrapping the existing code in a try/catch block in order to transform the StandardException into a SQLException.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java
          M java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java

          Regression and upgrade tests for new system procedures.

          Show
          Rick Hillegas added a comment - Attaching derby-866-02-ag-createDropUser.diff. This patch adds the syscs_create_user() and syscs_drop_user() system procedures. I will run regression tests. I had originally planned to add just syscs_create_user(). However, I found myself pulling on a ball of yarn because CleanDatabaseSetup needs to return to an initial state as far as possible, and that means dropping users who were added as part of the tests. I ended up pulling on another ball of yarn, which bloated up the number of files touched by this patch: I added StandardException to the exception signature of a method in the string datatypes. This percolated throughout the SQL interpreter, adding StandardException to the signatures of many methods. I debated swallowing the exception lower down and reporting it in the log. However, after some thought I decided that the changed methods all deserved to throw StandardException and should have done so from the start. In implementing syscs_create_user() I faced the problem that the implementing method phrases its password arg as a char[] array rather than a String. In order to get around the method resolution problems, I introduced a new, internal SQLPassword type. This type is not available and is mostly invisible to Derby users. SQLPassword has the following behavior: 1) It appears in DatabaseMetaData.getProcedureColumns() as a VARCHAR type. 2) But its corresponding Java type is char[] rather than String. 3) When the SQL interpreter sees a SQLPassword, it treats the contents as a password which needs to be smudged out as soon as possible. 4) I have found one place where knowledge of this internal type leaks out to users: If you select the aliasinfo of the syscs_create_user() procedure and then call the toString() method on that aliasinfo, you will see that the password arg has type PASSWORD rather than VARCHAR. Touches the following files: ------------ M java/engine/org/apache/derby/impl/sql/compile/TypeCompilerFactoryImpl.java M java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java M java/engine/org/apache/derby/iapi/types/TypeId.java M java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java M java/engine/org/apache/derby/iapi/types/DataType.java M java/engine/org/apache/derby/iapi/types/StringDataValue.java A java/engine/org/apache/derby/iapi/types/SQLPassword.java M java/engine/org/apache/derby/iapi/types/SQLChar.java M java/engine/org/apache/derby/iapi/types/SQLVarchar.java M java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java M java/engine/org/apache/derby/iapi/types/DataValueFactory.java M java/engine/org/apache/derby/iapi/types/ReaderToUTF8Stream.java M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java M java/engine/org/apache/derby/catalog/types/TypesImplInstanceGetter.java Support for new internal SQLPassword datatype. ------------ M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java M java/engine/org/apache/derby/catalog/SystemProcedures.java Support for new system procedures syscs_create_user() and syscs_drop_user(). ------------ M java/shared/org/apache/derby/shared/common/reference/SQLState.java M java/engine/org/apache/derby/loc/messages.xml New error message. ------------ M java/engine/org/apache/derby/impl/sql/GenericParameter.java M java/engine/org/apache/derby/impl/sql/execute/ValueRow.java M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java M java/engine/org/apache/derby/impl/sql/execute/IndexValueRow.java M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java M java/engine/org/apache/derby/impl/sql/execute/RowUtil.java M java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java M java/engine/org/apache/derby/impl/sql/execute/BasicSortObserver.java M java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java M java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java M java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java M java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java M java/engine/org/apache/derby/iapi/sql/Activation.java M java/engine/org/apache/derby/iapi/sql/execute/ExecRow.java M java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java M java/engine/org/apache/derby/iapi/types/SQLRef.java M java/engine/org/apache/derby/iapi/types/XML.java M java/testing/org/apache/derbyTesting/unitTests/store/T_AccessRow.java SQLException percolation across method signatures. Mostly these are trivial changes. The change to EmbedPreparedStatement looks big but it just involves wrapping the existing code in a try/catch block in order to transform the StandardException into a SQLException. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java M java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java Regression and upgrade tests for new system procedures.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-02-ag-createDropUser.diff at subversion revision 1220807.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-02-ag-createDropUser.diff at subversion revision 1220807.
          Hide
          Rick Hillegas added a comment -

          Fixed javadoc problem introduced by previous submission: revision 1220828.

          Show
          Rick Hillegas added a comment - Fixed javadoc problem introduced by previous submission: revision 1220828.
          Hide
          Knut Anders Hatlen added a comment -

          I have some questions about the getRawDataAndZeroIt() method in SQLChar:

          1) Could this code

          char[] sourceValue = (rawData == null) ? getCharArray() : rawData;

          be replaced with

          char[] sourceValue = getCharArray();

          ? It looks to me as if getCharArray() already has a special case for returning rawData directly if it has one.

          2) Would it be better to use getLength() instead of sourceValue.length to get the length of the value? I don't think those two values are guaranteed to be equal (see for example the comment in readExternalFromArray()), and if they are not equal, I think code like this might end up returning a different (zero-padded) value instead of a clone:

          public DataValueDescriptor cloneValue(boolean forceMaterialization)
          throws StandardException

          { return new SQLPassword( getRawDataAndZeroIt() ); }

          3) It calls Arrays.fill( sourceValue, (char) 0 ) right before calling setAndZeroOldValue(). Isn't that redundant, since calling zeroRawData() is the first thing setAndZeroOldValue() does?

          4) If the value comes from a stream, the call to getCharArray() will create the char array by going via string representation (it calls getString()). Perhaps the stream case should be moved from getString() to getCharArray() to prevent this unnecessary intermediate representation?

          Show
          Knut Anders Hatlen added a comment - I have some questions about the getRawDataAndZeroIt() method in SQLChar: 1) Could this code char[] sourceValue = (rawData == null) ? getCharArray() : rawData; be replaced with char[] sourceValue = getCharArray(); ? It looks to me as if getCharArray() already has a special case for returning rawData directly if it has one. 2) Would it be better to use getLength() instead of sourceValue.length to get the length of the value? I don't think those two values are guaranteed to be equal (see for example the comment in readExternalFromArray()), and if they are not equal, I think code like this might end up returning a different (zero-padded) value instead of a clone: public DataValueDescriptor cloneValue(boolean forceMaterialization) throws StandardException { return new SQLPassword( getRawDataAndZeroIt() ); } 3) It calls Arrays.fill( sourceValue, (char) 0 ) right before calling setAndZeroOldValue(). Isn't that redundant, since calling zeroRawData() is the first thing setAndZeroOldValue() does? 4) If the value comes from a stream, the call to getCharArray() will create the char array by going via string representation (it calls getString()). Perhaps the stream case should be moved from getString() to getCharArray() to prevent this unnecessary intermediate representation?
          Hide
          Rick Hillegas added a comment -

          Thanks for looking at the implementation of SQLPassword, Knut. I think there may be some de-serialization issues here as you note. I will try to do something smarter in a follow-on patch. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for looking at the implementation of SQLPassword, Knut. I think there may be some de-serialization issues here as you note. I will try to do something smarter in a follow-on patch. Thanks.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-03-aa-resetModifyPassword.diff. This patch adds the syscs_reset_password() and syscs_modify_password() system procedures. I am running tests now.

          Touches the following files:

          ------------

          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/impl/sql/catalog/SYSUSERSRowFactory.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          Add new procedures.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java

          Add test cases for new procedures.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

          Add upgrade sanity check for new procedures.

          ------------

          M java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java

          Make test cleanup handle the fact that the credentials for the DBO can't be dropped.

          Show
          Rick Hillegas added a comment - Attaching derby-866-03-aa-resetModifyPassword.diff. This patch adds the syscs_reset_password() and syscs_modify_password() system procedures. I am running tests now. Touches the following files: ------------ M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/impl/sql/catalog/SYSUSERSRowFactory.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java M java/engine/org/apache/derby/catalog/SystemProcedures.java Add new procedures. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java Add test cases for new procedures. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java Add upgrade sanity check for new procedures. ------------ M java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java Make test cleanup handle the fact that the credentials for the DBO can't be dropped.
          Hide
          Rick Hillegas added a comment -

          Attaching version 3 of the functional spec. This version incorporates feedback gathered over the past two weeks:

          1) Introduces a new value for derby.authentication.provider. When this property is set to the value NATIVE:$credentialsDB:LOCAL, then $credentialsDB is used to authenticate system-wide operations but connections to an individual database are authenticated using credentials stored in its own SYSUSERS table.

          2) Fills in the section on Documentation changes.

          3) Adds an appendix of sample use cases. This may help people reason about whether the most likely scenarios will be easy to administer. This section may provide a helpful summary for people who don't have time to read the whole spec.

          Show
          Rick Hillegas added a comment - Attaching version 3 of the functional spec. This version incorporates feedback gathered over the past two weeks: 1) Introduces a new value for derby.authentication.provider. When this property is set to the value NATIVE:$credentialsDB:LOCAL, then $credentialsDB is used to authenticate system-wide operations but connections to an individual database are authenticated using credentials stored in its own SYSUSERS table. 2) Fills in the section on Documentation changes. 3) Adds an appendix of sample use cases. This may help people reason about whether the most likely scenarios will be easy to administer. This section may provide a helpful summary for people who don't have time to read the whole spec.
          Hide
          Rick Hillegas added a comment -

          Tests ran cleanly for me except for diffs in a couple tests caused by the fact that the new syscs_modify_password() procedure grants EXECUTE privilege to PUBLIC. That in turn adds a new tuple to SYSROUTINEPERMS, causing some tests to fail when they select from that catalog.

          Attaching derby-866-03-ab-resetModifyPassword.diff. This patch corrects GrantRevokeDDLTest to account for the extra row in SYSROUTINEPERMS. I will fix some other tests in a follow-on patch.

          Touches another file in addition to the files touched by the previous rev of the patch:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java

          Committed at subversion revision 1221423.

          Show
          Rick Hillegas added a comment - Tests ran cleanly for me except for diffs in a couple tests caused by the fact that the new syscs_modify_password() procedure grants EXECUTE privilege to PUBLIC. That in turn adds a new tuple to SYSROUTINEPERMS, causing some tests to fail when they select from that catalog. Attaching derby-866-03-ab-resetModifyPassword.diff. This patch corrects GrantRevokeDDLTest to account for the extra row in SYSROUTINEPERMS. I will fix some other tests in a follow-on patch. Touches another file in addition to the files touched by the previous rev of the patch: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Committed at subversion revision 1221423.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-04-aa-fixRolesTest.diff. This adjusts RolesTest to account for the new tuple in SYSROUTINEPERMS. Committed at subversion revision 1221434.

          Touches the following file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-04-aa-fixRolesTest.diff. This adjusts RolesTest to account for the new tuple in SYSROUTINEPERMS. Committed at subversion revision 1221434. Touches the following file: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-05-aa-grantRevoke.diff. This patch verifies that EXECUTE privilege can be granted and revoked on the new system procedures. Committed at subversion revision 1221456.

          Touches the following file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-05-aa-grantRevoke.diff. This patch verifies that EXECUTE privilege can be granted and revoked on the new system procedures. Committed at subversion revision 1221456. Touches the following file: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-06-aa-upgradeFrom10.1.diff. This patch fixes another regression test which hard-codes the EXECUTE permissions granted on system routines: when testing the hard-upgrade path from 10.1, we verify the tuples in SYS.SYSROUTINEPERMS. This was found by the tinderbox tests after checking in derby-866-03-aa-resetModifyPassword.diff. Committed at subversion revision 1221734.

          Touches the following file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-06-aa-upgradeFrom10.1.diff. This patch fixes another regression test which hard-codes the EXECUTE permissions granted on system routines: when testing the hard-upgrade path from 10.1, we verify the tuples in SYS.SYSROUTINEPERMS. This was found by the tinderbox tests after checking in derby-866-03-aa-resetModifyPassword.diff. Committed at subversion revision 1221734. Touches the following file: M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-07-aa-removeSQLPassword.diff. This backs out the addition of the SQLPassword internal datatype. I am running regression tests now.

          I have been thinking more about the problem which SQLPassword tries to tackle: password Strings are vulnerable to being sniffed in memory, in swap files, and in crash dumps. I have been reading more about the issue and I have come to the conclusion that char[] representations narrow the vulnerability but do not eliminate it. In addition, password maintenance is a rare event compared to the day-to-day event of logging in. As long as JDBC login uses String passwords, I don't see much value in securing password maintenance. The small security boost does not justify the extra complexity of a new, internal-only data type. I am therefore removing SQLPassword.

          This patch backs out much of derby-866-02-ag-createDropUser.diff (subversion revision 1220807).

          Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java
          M java/engine/org/apache/derby/impl/sql/compile/TypeCompilerFactoryImpl.java
          M java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
          M java/engine/org/apache/derby/impl/sql/GenericParameter.java
          M java/engine/org/apache/derby/impl/sql/execute/ValueRow.java
          M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
          M java/engine/org/apache/derby/impl/sql/execute/IndexValueRow.java
          M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
          M java/engine/org/apache/derby/impl/sql/execute/RowUtil.java
          M java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java
          M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
          M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java
          M java/engine/org/apache/derby/impl/sql/execute/BasicSortObserver.java
          M java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java
          M java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java
          M java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
          M java/engine/org/apache/derby/iapi/sql/Activation.java
          M java/engine/org/apache/derby/iapi/sql/execute/ExecRow.java
          M java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java
          M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
          M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
          M java/engine/org/apache/derby/iapi/types/TypeId.java
          M java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
          M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
          M java/engine/org/apache/derby/iapi/types/DataType.java
          M java/engine/org/apache/derby/iapi/types/ReaderToUTF8Stream.java
          M java/engine/org/apache/derby/iapi/types/SQLRef.java
          M java/engine/org/apache/derby/iapi/types/StringDataValue.java
          M java/engine/org/apache/derby/iapi/types/XML.java
          D java/engine/org/apache/derby/iapi/types/SQLPassword.java
          M java/engine/org/apache/derby/iapi/types/SQLChar.java
          M java/engine/org/apache/derby/iapi/types/SQLVarchar.java
          M java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
          M java/engine/org/apache/derby/iapi/types/DataValueFactory.java
          M java/engine/org/apache/derby/catalog/SystemProcedures.java
          M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
          M java/engine/org/apache/derby/catalog/types/TypesImplInstanceGetter.java
          M java/testing/org/apache/derbyTesting/unitTests/store/T_AccessRow.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-07-aa-removeSQLPassword.diff. This backs out the addition of the SQLPassword internal datatype. I am running regression tests now. I have been thinking more about the problem which SQLPassword tries to tackle: password Strings are vulnerable to being sniffed in memory, in swap files, and in crash dumps. I have been reading more about the issue and I have come to the conclusion that char[] representations narrow the vulnerability but do not eliminate it. In addition, password maintenance is a rare event compared to the day-to-day event of logging in. As long as JDBC login uses String passwords, I don't see much value in securing password maintenance. The small security boost does not justify the extra complexity of a new, internal-only data type. I am therefore removing SQLPassword. This patch backs out much of derby-866-02-ag-createDropUser.diff (subversion revision 1220807). Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java M java/engine/org/apache/derby/impl/sql/compile/TypeCompilerFactoryImpl.java M java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java M java/engine/org/apache/derby/impl/sql/GenericParameter.java M java/engine/org/apache/derby/impl/sql/execute/ValueRow.java M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java M java/engine/org/apache/derby/impl/sql/execute/IndexValueRow.java M java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java M java/engine/org/apache/derby/impl/sql/execute/RowUtil.java M java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java M java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java M java/engine/org/apache/derby/impl/sql/execute/BasicSortObserver.java M java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java M java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java M java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java M java/engine/org/apache/derby/iapi/sql/Activation.java M java/engine/org/apache/derby/iapi/sql/execute/ExecRow.java M java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java M java/engine/org/apache/derby/iapi/types/TypeId.java M java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java M java/engine/org/apache/derby/iapi/types/DataType.java M java/engine/org/apache/derby/iapi/types/ReaderToUTF8Stream.java M java/engine/org/apache/derby/iapi/types/SQLRef.java M java/engine/org/apache/derby/iapi/types/StringDataValue.java M java/engine/org/apache/derby/iapi/types/XML.java D java/engine/org/apache/derby/iapi/types/SQLPassword.java M java/engine/org/apache/derby/iapi/types/SQLChar.java M java/engine/org/apache/derby/iapi/types/SQLVarchar.java M java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java M java/engine/org/apache/derby/iapi/types/DataValueFactory.java M java/engine/org/apache/derby/catalog/SystemProcedures.java M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java M java/engine/org/apache/derby/catalog/types/TypesImplInstanceGetter.java M java/testing/org/apache/derbyTesting/unitTests/store/T_AccessRow.java
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-07-aa-removeSQLPassword.diff at subversion revision 1222215.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-07-aa-removeSQLPassword.diff at subversion revision 1222215.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-08-aa-passwordHasher.diff. This patch wires the NATIVE procedures to the new password hashing scheme which Knut introduced with DERBY-5539. I am running tests now.

          The patch abstracts the post-10.5 password hashing logic into a new class, PasswordHasher. The logic is now used by the SQL layer as well as the authentication code in the JDBC layer. So I put PasswordHasher in the lower layer. More specifically, I put PasswordHasher in the DataDictionary because authentication code was already calling into the DataDictionary in order to configure password hashing. But other people may have ideas about a better place to park this code--your thoughts are of course welcome.

          Touches the following files:

          ---------

          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/impl/jdbc/authentication/BasicAuthenticationServiceImpl.java
          M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          A java/engine/org/apache/derby/iapi/sql/dictionary/PasswordHasher.java

          Abstracts the password hashing code into a PasswordHasher class which lives in the DataDictionary.

          ---------

          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          Wires the PasswordHasher into syscs_create_user, syscs_modify_password, and syscs_reset_password.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java

          Adds new tests to verify that the NATIVE hashing scheme changes as expected when you tune the BUILTIN knobs which control password hashing.

          Show
          Rick Hillegas added a comment - Attaching derby-866-08-aa-passwordHasher.diff. This patch wires the NATIVE procedures to the new password hashing scheme which Knut introduced with DERBY-5539 . I am running tests now. The patch abstracts the post-10.5 password hashing logic into a new class, PasswordHasher. The logic is now used by the SQL layer as well as the authentication code in the JDBC layer. So I put PasswordHasher in the lower layer. More specifically, I put PasswordHasher in the DataDictionary because authentication code was already calling into the DataDictionary in order to configure password hashing. But other people may have ideas about a better place to park this code--your thoughts are of course welcome. Touches the following files: --------- M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/impl/jdbc/authentication/BasicAuthenticationServiceImpl.java M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java A java/engine/org/apache/derby/iapi/sql/dictionary/PasswordHasher.java Abstracts the password hashing code into a PasswordHasher class which lives in the DataDictionary. --------- M java/engine/org/apache/derby/catalog/SystemProcedures.java Wires the PasswordHasher into syscs_create_user, syscs_modify_password, and syscs_reset_password. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java Adds new tests to verify that the NATIVE hashing scheme changes as expected when you tune the BUILTIN knobs which control password hashing.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-08-ab-passwordHasher.diff. The previous patch disabled the case when derby.authentication.builtin.algorithm is not set. In that situation, Derby is supposed to default to a SHA-1 message digest. This second rev of the patch seeks to restore the old behavior. Running tests again...

          Show
          Rick Hillegas added a comment - Attaching derby-866-08-ab-passwordHasher.diff. The previous patch disabled the case when derby.authentication.builtin.algorithm is not set. In that situation, Derby is supposed to default to a SHA-1 message digest. This second rev of the patch seeks to restore the old behavior. Running tests again...
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly except for an error in the newly added tests for NATIVE procs. I need to add some defensive logic to protect against the condition when derby.authentication.builtin.algorithm is deliberately unset.

          Show
          Rick Hillegas added a comment - Tests passed cleanly except for an error in the newly added tests for NATIVE procs. I need to add some defensive logic to protect against the condition when derby.authentication.builtin.algorithm is deliberately unset.
          Hide
          Knut Anders Hatlen added a comment -

          The changes look fine to me. I noticed that the hashingscheme column contains the hashing scheme identifier, the salt and the name of the hash function. These parameters are also stored in the password column. I'm not sure exactly what the hashingscheme column will be used for, but the salt will make every user have a unique value in hashingscheme, and I think I'd expect every user that hashed the password using the same algorithm to have the same value in that column.

          Show
          Knut Anders Hatlen added a comment - The changes look fine to me. I noticed that the hashingscheme column contains the hashing scheme identifier, the salt and the name of the hash function. These parameters are also stored in the password column. I'm not sure exactly what the hashingscheme column will be used for, but the salt will make every user have a unique value in hashingscheme, and I think I'd expect every user that hashed the password using the same algorithm to have the same value in that column.
          Hide
          Rick Hillegas added a comment -

          Thanks for looking at the patch, Knut. I'm not understanding the problem you have found with it. I tried to separate the hashed result from the inputs to the hashing algorithm. For BUILTIN passwords, the inputs continue to be concatenated with the hashed password. However, for NATIVE authentication, the inputs are stored in SYSUSERS.HASHINGSCHEME and the hashed result is stored in SYSUSERS.PASSWORD. The inputs should not be concatenated to the result in SYSUSERS.PASSWORD. If they are, I have indeed made a mistake. The idea is that when verifying a NATIVE password, we will perform the following steps:

          1) The connecting user supplies a username and password.

          2) Derby keys into SYSUSERS using the supplied username and looks up the corresponding HASHINGSCHEME inputs and PASSWORD result.

          3) Derby constructs a PasswordHasher from the inputs encoded in HASHINGSCHEME.

          4) Then Derby runs the supplied username and password through the PasswordHasher to produce a candidate hashed password.

          5) Derby compares the candidate hashed password to the PASSWORD retrieved in step (2). If they are identical, then authentication succeeds.

          The following script output seems to verify to me that the NATIVE hashed PASSWORD does not contain the inputs to the hashing algorithm, unlike the BUILTIN analog. Does this help clear up the confusion or do you still see a problem here? Thanks.

          ij> connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword';
          ij> call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' );
          0 rows inserted/updated/deleted
          ij> call syscs_util.syscs_set_database_property( 'derby.user.TEST_DBO', 'test_dbopassword' );
          0 rows inserted/updated/deleted
          ij> select hashingscheme, password from sys.sysusers;
          HASHINGSCHEME |PASSWORD
          -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          3b62:07751aa502a988a6b604d3865997a0e9:1000:SHA-256 |dc0bfe6c3f705699cb6ff0ad7bf0ee815ab818cac3e8bd65851cdeab3c9b8faf

          1 row selected
          ij> values syscs_util.syscs_get_database_property( 'derby.user.TEST_DBO' );
          1
          --------------------------------------------------------------------------------------------------------------------------------
          3b62412e6e60e4e47c6aa6fcdc61a168ef737b99ec5c479b171934355f11536dbdf9:442941b76338dfc6e5cf2daa31b5b062:1000:SHA-256

          1 row selected
          ij> select length( hashingscheme ), length( password ) from sys.sysusers;
          1 |2
          -----------------------
          50 |64

          1 row selected
          ij> values length( syscs_util.syscs_get_database_property( 'derby.user.TEST_DBO' ) );
          1
          -----------
          114

          1 row selected

          Show
          Rick Hillegas added a comment - Thanks for looking at the patch, Knut. I'm not understanding the problem you have found with it. I tried to separate the hashed result from the inputs to the hashing algorithm. For BUILTIN passwords, the inputs continue to be concatenated with the hashed password. However, for NATIVE authentication, the inputs are stored in SYSUSERS.HASHINGSCHEME and the hashed result is stored in SYSUSERS.PASSWORD. The inputs should not be concatenated to the result in SYSUSERS.PASSWORD. If they are, I have indeed made a mistake. The idea is that when verifying a NATIVE password, we will perform the following steps: 1) The connecting user supplies a username and password. 2) Derby keys into SYSUSERS using the supplied username and looks up the corresponding HASHINGSCHEME inputs and PASSWORD result. 3) Derby constructs a PasswordHasher from the inputs encoded in HASHINGSCHEME. 4) Then Derby runs the supplied username and password through the PasswordHasher to produce a candidate hashed password. 5) Derby compares the candidate hashed password to the PASSWORD retrieved in step (2). If they are identical, then authentication succeeds. The following script output seems to verify to me that the NATIVE hashed PASSWORD does not contain the inputs to the hashing algorithm, unlike the BUILTIN analog. Does this help clear up the confusion or do you still see a problem here? Thanks. ij> connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword'; ij> call syscs_util.syscs_create_user( 'TEST_DBO', 'test_dbopassword' ); 0 rows inserted/updated/deleted ij> call syscs_util.syscs_set_database_property( 'derby.user.TEST_DBO', 'test_dbopassword' ); 0 rows inserted/updated/deleted ij> select hashingscheme, password from sys.sysusers; HASHINGSCHEME |PASSWORD ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3b62:07751aa502a988a6b604d3865997a0e9:1000:SHA-256 |dc0bfe6c3f705699cb6ff0ad7bf0ee815ab818cac3e8bd65851cdeab3c9b8faf 1 row selected ij> values syscs_util.syscs_get_database_property( 'derby.user.TEST_DBO' ); 1 -------------------------------------------------------------------------------------------------------------------------------- 3b62412e6e60e4e47c6aa6fcdc61a168ef737b99ec5c479b171934355f11536dbdf9:442941b76338dfc6e5cf2daa31b5b062:1000:SHA-256 1 row selected ij> select length( hashingscheme ), length( password ) from sys.sysusers; 1 |2 ----------------------- 50 |64 1 row selected ij> values length( syscs_util.syscs_get_database_property( 'derby.user.TEST_DBO' ) ); 1 ----------- 114 1 row selected
          Hide
          Knut Anders Hatlen added a comment -

          You're right, I misread the patch. The behaviour described above sounds fine. Sorry about the noise.

          Show
          Knut Anders Hatlen added a comment - You're right, I misread the patch. The behaviour described above sounds fine. Sorry about the noise.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-08-ad-passwordHasher.diff. This rev of the patch stops users from manipulating NATIVE passwords when derby.authentication.builtin.algorithm is not set. If derby.authentication.builtin.algorithm is not set, then Derby's old-style default password hashing strategy is considered too weak for use by NATIVE authentication. Tests passed cleanly for me with this version of the patch.

          I do not see much point in further complicating the logic to allow the use of weak hashing with NATIVE authentication. Of course, we could change the meaning of derby.authentication.builtin.algorithm=null when NATIVE authentication is enabled. However, that seems more confusing to me than just forbidding the use of weak hashing altogether.

          The functional spec should be updated to reflect this change.

          This version of the patch touches the same files as the previous version plus the following:

          -----------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New message raised if the user attempts to manipulate a NATIVE password when derby.authentication.builtin.algorithm is not set.

          -----------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

          The 10.9 upgrade tests now set derby.authentication.builtin.algorithm if it is not set already. This allows us to test the new NATIVE procedures in databases which were hard-upgraded from Derby versions which did not recognize this property.

          Show
          Rick Hillegas added a comment - Attaching derby-866-08-ad-passwordHasher.diff. This rev of the patch stops users from manipulating NATIVE passwords when derby.authentication.builtin.algorithm is not set. If derby.authentication.builtin.algorithm is not set, then Derby's old-style default password hashing strategy is considered too weak for use by NATIVE authentication. Tests passed cleanly for me with this version of the patch. I do not see much point in further complicating the logic to allow the use of weak hashing with NATIVE authentication. Of course, we could change the meaning of derby.authentication.builtin.algorithm=null when NATIVE authentication is enabled. However, that seems more confusing to me than just forbidding the use of weak hashing altogether. The functional spec should be updated to reflect this change. This version of the patch touches the same files as the previous version plus the following: ----------------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New message raised if the user attempts to manipulate a NATIVE password when derby.authentication.builtin.algorithm is not set. ----------------- M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java The 10.9 upgrade tests now set derby.authentication.builtin.algorithm if it is not set already. This allows us to test the new NATIVE procedures in databases which were hard-upgraded from Derby versions which did not recognize this property.
          Hide
          Knut Anders Hatlen added a comment -

          The simpler logic sounds easier to explain and makes the most sense, I agree. Since derby.authentication.builtin.algorithm gets set to a non-null value at database creation time, its being null means that the DBA has deliberately chosen the old (broken) hashing scheme, and it makes perfect sense to refuse that in NATIVE. There's one small bump, and that is that databases upgraded from 10.5 and earlier won't have the derby.authentication.builtin.algorithm property set, so there's one extra step needed to enable NATIVE in such databases.

          Show
          Knut Anders Hatlen added a comment - The simpler logic sounds easier to explain and makes the most sense, I agree. Since derby.authentication.builtin.algorithm gets set to a non-null value at database creation time, its being null means that the DBA has deliberately chosen the old (broken) hashing scheme, and it makes perfect sense to refuse that in NATIVE. There's one small bump, and that is that databases upgraded from 10.5 and earlier won't have the derby.authentication.builtin.algorithm property set, so there's one extra step needed to enable NATIVE in such databases.
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. I think we should mention the upgrade issue in the release note for NATIVE authentication. Checked in derby-866-08-ad-passwordHasher.diff at subversion revision 1227150.

          Show
          Rick Hillegas added a comment - Thanks, Knut. I think we should mention the upgrade issue in the release note for NATIVE authentication. Checked in derby-866-08-ad-passwordHasher.diff at subversion revision 1227150.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-09-ad-nativeAuthenticationService.diff. This is the first rev of a patch which adds a NATIVE authentication service to Derby. Regression tests passed cleanly for me. The patch also passes a battery of ad-hoc tests, but I have not yet collected them into a formal regression test for this new functionality. So the patch is not ready for commit. I am posting the patch now in order to get feedback on whether this approach seems reasonable.

          The new authentication service follows the pattern of the existing BASIC authentication service (BasicAuthenticationServiceImpl). I did not add support for network password substitution but it may be possible to add that in a later patch.

          I have taken the approach of normalizing the user name as a SQL identifier when storing credentials at database-creation time. This causes the values in SYSSCHEMAS.AUTHORIZATIONID and SYSUSERS.USERNAME to agree, making it possible to join on those columns. The user name is also normalized at connection-authorization time. I haven't thought through the implications of whether syscs_create_user, syscs_reset_password, and syscs_drop_user should also normalize the username. Your opinions are welcome. Perhaps a clear preference will emerge as I write more tests.

          Ad-hoc tests have verified the following behavior (at least in simple configurations):

          1) Creating a credentials DB. The initial credentials are automatically stored in SYSUSERS and the following properties are persisted:

          derby.database.sqlAuthorization=true
          derby.authentication.provider=NATIVE::LOCAL

          2) Creating a non-credentials DB, using the credentials DB to authorize the operation.

          3) Shutting down a credentials DB and restarting it.

          4) Trying to create a database with credentials which are not stored in the credentials DB. This operation fails as expected.

          5) Trying to connect to an existing database with credentials which are not stored in the credentials DB. This operation fails as expected.

          6) Verifying that the following system property setting is sufficient to turn on authentication and SQL authorization:

          derby.authentication.provider=NATIVE:$credentialsDB:LOCAL

          or

          derby.authentication.provider=NATIVE:$credentialsDB

          That is, there is no need to also set the following properties:

          derby.connection.requireAuthentication=true
          derby.database.sqlAuthorization=true

          7) Verifying that a credentials DB remembers that it is a credentials DB. When you reboot it, the connection is authenticated using credentials stored in the local SYSUSERS table.

          Tests have been run on disk-based and in-memory databases and on desktop and JSR169 platforms.

          I will start assembling a regression test out of the above cases. Even if my approach needs to be reworked, these tests are a low-bar for any implementation.

          I know that code has not yet been written to support the following features:

          i) Prevent NATIVE authentication from being enabled in a pre-existing database which lacks credentials for the DBO.

          ii) Enable password substitution over the network.

          Many more tests need to be written. In addition, more code may need to be written in order to implement the full functional spec. At a minimum, the following behaviors need to be verified and perhaps coded:

          A) Once a credentials database is created, NATIVE authentication cannot be disabled for that database.

          B) Similarly, SQL authorization cannot be disabled for a credentials DB.

          C) Upgrade paths have not been tested.

          D) No testing has been done with databases which are accessed via jar files, the classpath, or http/https URLs.

          E) No testing has been done over the network.

          Touches the following files:

          ----------

          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java

          Add a method to retrieve a user's stored credentials. Also added db creation logic to turn on SQL authorization when NATIVE authentication is enabled.

          ----------

          M java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
          M java/engine/org/apache/derby/iapi/services/monitor/ModuleFactory.java

          Added a new method which makes it possible for code outside the Store to calculate the canonicalized form of a database name. This is necessary in order to figure out whether the local database is the credentials DB identified by the derby.authentication.provider property.

          ----------

          M java/engine/org/apache/derby/iapi/services/property/PropertyUtil.java
          M java/engine/org/apache/derby/iapi/reference/Property.java

          Added code so that logic outside the Authentication module can figure out whether NATIVE and NATIVE::LOCAL authentication are enabled.

          ----------

          M java/engine/org/apache/derby/impl/jdbc/authentication/SpecificAuthenticationServiceImpl.java
          A java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java
          M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java
          M java/engine/org/apache/derby/iapi/jdbc/AuthenticationService.java
          M java/engine/org/apache/derby/modules.properties

          The NATIVE authentication service.

          ----------

          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          Refactored some code so that the DBO's credentials can be stored at database creation time.

          ----------

          M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java

          Added bootstrapping logic to skip NATIVE authentication when the system-wide credentials DB is being created.

          ----------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error messages.

          Show
          Rick Hillegas added a comment - Attaching derby-866-09-ad-nativeAuthenticationService.diff. This is the first rev of a patch which adds a NATIVE authentication service to Derby. Regression tests passed cleanly for me. The patch also passes a battery of ad-hoc tests, but I have not yet collected them into a formal regression test for this new functionality. So the patch is not ready for commit. I am posting the patch now in order to get feedback on whether this approach seems reasonable. The new authentication service follows the pattern of the existing BASIC authentication service (BasicAuthenticationServiceImpl). I did not add support for network password substitution but it may be possible to add that in a later patch. I have taken the approach of normalizing the user name as a SQL identifier when storing credentials at database-creation time. This causes the values in SYSSCHEMAS.AUTHORIZATIONID and SYSUSERS.USERNAME to agree, making it possible to join on those columns. The user name is also normalized at connection-authorization time. I haven't thought through the implications of whether syscs_create_user, syscs_reset_password, and syscs_drop_user should also normalize the username. Your opinions are welcome. Perhaps a clear preference will emerge as I write more tests. Ad-hoc tests have verified the following behavior (at least in simple configurations): 1) Creating a credentials DB. The initial credentials are automatically stored in SYSUSERS and the following properties are persisted: derby.database.sqlAuthorization=true derby.authentication.provider=NATIVE::LOCAL 2) Creating a non-credentials DB, using the credentials DB to authorize the operation. 3) Shutting down a credentials DB and restarting it. 4) Trying to create a database with credentials which are not stored in the credentials DB. This operation fails as expected. 5) Trying to connect to an existing database with credentials which are not stored in the credentials DB. This operation fails as expected. 6) Verifying that the following system property setting is sufficient to turn on authentication and SQL authorization: derby.authentication.provider=NATIVE:$credentialsDB:LOCAL or derby.authentication.provider=NATIVE:$credentialsDB That is, there is no need to also set the following properties: derby.connection.requireAuthentication=true derby.database.sqlAuthorization=true 7) Verifying that a credentials DB remembers that it is a credentials DB. When you reboot it, the connection is authenticated using credentials stored in the local SYSUSERS table. Tests have been run on disk-based and in-memory databases and on desktop and JSR169 platforms. I will start assembling a regression test out of the above cases. Even if my approach needs to be reworked, these tests are a low-bar for any implementation. I know that code has not yet been written to support the following features: i) Prevent NATIVE authentication from being enabled in a pre-existing database which lacks credentials for the DBO. ii) Enable password substitution over the network. Many more tests need to be written. In addition, more code may need to be written in order to implement the full functional spec. At a minimum, the following behaviors need to be verified and perhaps coded: A) Once a credentials database is created, NATIVE authentication cannot be disabled for that database. B) Similarly, SQL authorization cannot be disabled for a credentials DB. C) Upgrade paths have not been tested. D) No testing has been done with databases which are accessed via jar files, the classpath, or http/https URLs. E) No testing has been done over the network. Touches the following files: ---------- M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Add a method to retrieve a user's stored credentials. Also added db creation logic to turn on SQL authorization when NATIVE authentication is enabled. ---------- M java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java M java/engine/org/apache/derby/iapi/services/monitor/ModuleFactory.java Added a new method which makes it possible for code outside the Store to calculate the canonicalized form of a database name. This is necessary in order to figure out whether the local database is the credentials DB identified by the derby.authentication.provider property. ---------- M java/engine/org/apache/derby/iapi/services/property/PropertyUtil.java M java/engine/org/apache/derby/iapi/reference/Property.java Added code so that logic outside the Authentication module can figure out whether NATIVE and NATIVE::LOCAL authentication are enabled. ---------- M java/engine/org/apache/derby/impl/jdbc/authentication/SpecificAuthenticationServiceImpl.java A java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java M java/engine/org/apache/derby/iapi/jdbc/AuthenticationService.java M java/engine/org/apache/derby/modules.properties The NATIVE authentication service. ---------- M java/engine/org/apache/derby/catalog/SystemProcedures.java Refactored some code so that the DBO's credentials can be stored at database creation time. ---------- M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java Added bootstrapping logic to skip NATIVE authentication when the system-wide credentials DB is being created. ---------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error messages.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-09-ae-nativeAuthenticationServiceWithTests.diff. This adds a first batch of tests to the previous rev of the patch. The new tests pass for me on my desktop and on a JSR169 platform.

          The full regression tests also passed for me except for an error in ErrorMessageTest. ErrorMessageTest ran fine for me standalone. The error may relate to recent work on DERBY-5564? Details at the end of this comment.

          The new tests stress the following configurations for both embedded and client/server: no authentication, NATIVE authentication, NATIVE+LOCAL authentication. That's a total of 6 configurations.

          Some test framework methods had to be changed to account for the fact that there's a new authentication failure message raised when the credentials db doesn't exist yet. Also SystemPropertyTestSetup now shuts down the engine BEFORE changing the properties, rather than vice-versa. Doing those operations in the original order fails if the property you are setting is the one which enables NATIVE authentication. This is because engine shutdown is an operation which must be authenticated and it can't be authenticated by the NATIVE machinery if the credentials database hasn't been created yet.

          Touches the following files in addition to the files touched by the previous rev of the patch:

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java
          M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java
          M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
          M java/testing/org/apache/derbyTesting/junit/SystemPropertyTestSetup.java
          M java/testing/org/apache/derbyTesting/junit/DatabaseChangeSetup.java
          M java/testing/org/apache/derbyTesting/junit/NetworkServerTestSetup.java
          M java/testing/org/apache/derbyTesting/junit/DropDatabaseSetup.java

          ---------------------------------

          Here is the error I saw in ErrorMessageTest:

          There was 1 failure:
          1) testDeadlockTimeout(org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest)junit.framework.ComparisonFailure: Not a deadlock expected:<40[00]1> but was:<40[XL]1>
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertSQLState(BaseJDBCTestCase.java:790)
          at org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest.testDeadlockTimeout(ErrorMessageTest.java:171)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:116)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Caused by: java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested. The lockTable dump is:
          Wed Jan 18 11:51:46 PST 2012
          XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

              • The following row is the victim ***
                333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T |
              • The above row is the victim ***
                333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T |
                333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T |
                333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T |
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:89)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2331)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
          at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(EmbedResultSet.java:4406)
          at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:472)
          at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:376)
          at org.apache.derbyTesting.junit.JDBC.assertDrainResults(JDBC.java:678)
          at org.apache.derbyTesting.junit.JDBC.assertDrainResults(JDBC.java:655)
          at org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest$2.run(ErrorMessageTest.java:148)
          at java.lang.Thread.run(Thread.java:680)
          Caused by: java.sql.SQLException: A lock could not be obtained within the time requested. The lockTable dump is:
          Wed Jan 18 11:51:46 PST 2012
          XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

              • The following row is the victim ***
                333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T |
              • The above row is the victim ***
                333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T |
                333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T |
                333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T |
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71)
          ... 12 more
          Caused by: ERROR 40XL1: A lock could not be obtained within the time requested. The lockTable dump is:
          Wed Jan 18 11:51:46 PST 2012
          XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

              • The following row is the victim ***
                333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T |
              • The above row is the victim ***
                333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T |
                333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T |
                333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T |
                333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T |
                333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T |
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278)
          at org.apache.derby.impl.services.locks.Timeout.createException(Timeout.java:147)
          at org.apache.derby.impl.services.locks.Timeout.buildException(Timeout.java:246)
          at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(ConcurrentLockSet.java:613)
          at org.apache.derby.impl.services.locks.AbstractPool.lockObject(AbstractPool.java:119)
          at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(ConcurrentPool.java:28)
          at org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(RowLocking2.java:165)
          at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:520)
          at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:638)
          at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(B2IRowLocking3.java:309)
          at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(B2IRowLocking3.java:599)
          at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(B2IRowLockingRR.java:105)
          at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(BTreeForwardScan.java:305)
          at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(BTreeScan.java:1599)
          at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(TableScanResultSet.java:577)
          at org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(IndexRowToBaseRowResultSet.java:373)
          at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:478)
          at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:432)
          ... 5 more

          FAILURES!!!
          Tests run: 11906, Failures: 1, Errors: 0

          Show
          Rick Hillegas added a comment - Attaching derby-866-09-ae-nativeAuthenticationServiceWithTests.diff. This adds a first batch of tests to the previous rev of the patch. The new tests pass for me on my desktop and on a JSR169 platform. The full regression tests also passed for me except for an error in ErrorMessageTest. ErrorMessageTest ran fine for me standalone. The error may relate to recent work on DERBY-5564 ? Details at the end of this comment. The new tests stress the following configurations for both embedded and client/server: no authentication, NATIVE authentication, NATIVE+LOCAL authentication. That's a total of 6 configurations. Some test framework methods had to be changed to account for the fact that there's a new authentication failure message raised when the credentials db doesn't exist yet. Also SystemPropertyTestSetup now shuts down the engine BEFORE changing the properties, rather than vice-versa. Doing those operations in the original order fails if the property you are setting is the one which enables NATIVE authentication. This is because engine shutdown is an operation which must be authenticated and it can't be authenticated by the NATIVE machinery if the credentials database hasn't been created yet. Touches the following files in addition to the files touched by the previous rev of the patch: A java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java M java/testing/org/apache/derbyTesting/junit/SystemPropertyTestSetup.java M java/testing/org/apache/derbyTesting/junit/DatabaseChangeSetup.java M java/testing/org/apache/derbyTesting/junit/NetworkServerTestSetup.java M java/testing/org/apache/derbyTesting/junit/DropDatabaseSetup.java --------------------------------- Here is the error I saw in ErrorMessageTest: There was 1 failure: 1) testDeadlockTimeout(org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest)junit.framework.ComparisonFailure: Not a deadlock expected:<40 [00] 1> but was:<40 [XL] 1> at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertSQLState(BaseJDBCTestCase.java:790) at org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest.testDeadlockTimeout(ErrorMessageTest.java:171) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:116) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested. The lockTable dump is: Wed Jan 18 11:51:46 PST 2012 XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The following row is the victim *** 333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T | The above row is the victim *** 333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T | 333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T | 333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:89) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2331) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82) at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(EmbedResultSet.java:4406) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:472) at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:376) at org.apache.derbyTesting.junit.JDBC.assertDrainResults(JDBC.java:678) at org.apache.derbyTesting.junit.JDBC.assertDrainResults(JDBC.java:655) at org.apache.derbyTesting.functionTests.tests.lang.ErrorMessageTest$2.run(ErrorMessageTest.java:148) at java.lang.Thread.run(Thread.java:680) Caused by: java.sql.SQLException: A lock could not be obtained within the time requested. The lockTable dump is: Wed Jan 18 11:51:46 PST 2012 XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The following row is the victim *** 333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T | The above row is the victim *** 333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T | 333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T | 333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71) ... 12 more Caused by: ERROR 40XL1: A lock could not be obtained within the time requested. The lockTable dump is: Wed Jan 18 11:51:46 PST 2012 XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The following row is the victim *** 333883 |ROW |S |0 |(1,7) |WAIT |T |NULL |T | The above row is the victim *** 333883 |ROW |X |3 |(1,8) |GRANT|T |NULL |T | 333879 |ROW |S |0 |(1,8) |WAIT |T |NULL |T | 333879 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |T | 333883 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |TABLE |IS |1 |Tablelock |GRANT|T |NULL |T | 333879 |ROW |X |3 |(1,7) |GRANT|T |NULL |T | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278) at org.apache.derby.impl.services.locks.Timeout.createException(Timeout.java:147) at org.apache.derby.impl.services.locks.Timeout.buildException(Timeout.java:246) at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(ConcurrentLockSet.java:613) at org.apache.derby.impl.services.locks.AbstractPool.lockObject(AbstractPool.java:119) at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(ConcurrentPool.java:28) at org.apache.derby.impl.store.raw.xact.RowLocking2.lockRecordForRead(RowLocking2.java:165) at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:520) at org.apache.derby.impl.store.access.heap.HeapController.lockRow(HeapController.java:638) at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(B2IRowLocking3.java:309) at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(B2IRowLocking3.java:599) at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(B2IRowLockingRR.java:105) at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(BTreeForwardScan.java:305) at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(BTreeScan.java:1599) at org.apache.derby.impl.sql.execute.TableScanResultSet.getNextRowCore(TableScanResultSet.java:577) at org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore(IndexRowToBaseRowResultSet.java:373) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:478) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:432) ... 5 more FAILURES!!! Tests run: 11906, Failures: 1, Errors: 0
          Hide
          Rick Hillegas added a comment -

          Committed derby-866-09-ae-nativeAuthenticationServiceWithTests.diff at subversion revision 1233377.

          Show
          Rick Hillegas added a comment - Committed derby-866-09-ae-nativeAuthenticationServiceWithTests.diff at subversion revision 1233377.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-10-ac-propChanging.diff. This patch adds logic to govern the on-disk setting of the derby.authentication.provider property. Tests passed cleanly for me.

          This patch makes the following changes:

          1) Prevents an existing database from being set to NATIVE LOCAL authentication if the DBO's credentials haven't been stored.

          2) Prevents derby.authentication.provider from being set on disk to any NATIVE value other than NATIVE::LOCAL.

          3) Prevents NATIVE LOCAL authentication from being turned off or overridden once it has been turned on.

          4) No longer sets derby.database.sqlAuthorization=true when creating a credentials DB. Instead, the database figures out whether SQL authorization is on based on whether derby.database.sqlAuthorization is set or NATIVE authentication is on.

          Touches the following files

          -----------------

          M java/engine/org/apache/derby/iapi/services/property/PropertyUtil.java
          M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java

          If NATIVE authentication is set on-disk, then this trumps all other settings of derby.authentication.provider.

          -----------------

          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
          M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java

          Removes the transaction argument from DataDictionary.getUser(). The argument was not used.

          -----------------

          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

          Don't set derby.database.sqlAuthorization on disk when creating a credentials DB. Instead, determine whether SQL authorization is on by consulting both derby.database.sqlAuthorization and derby.authentication.provider.

          -----------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error message raised on bad attempts to set derby.authentication.provider on disk.

          -----------------

          M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
          M java/testing/org/apache/derbyTesting/junit/DatabaseChangeSetup.java

          Logic to shutdown a single-use database as part of a test.

          -----------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          New tests to verify the functionality added by this patch.

          Show
          Rick Hillegas added a comment - Attaching derby-866-10-ac-propChanging.diff. This patch adds logic to govern the on-disk setting of the derby.authentication.provider property. Tests passed cleanly for me. This patch makes the following changes: 1) Prevents an existing database from being set to NATIVE LOCAL authentication if the DBO's credentials haven't been stored. 2) Prevents derby.authentication.provider from being set on disk to any NATIVE value other than NATIVE::LOCAL. 3) Prevents NATIVE LOCAL authentication from being turned off or overridden once it has been turned on. 4) No longer sets derby.database.sqlAuthorization=true when creating a credentials DB. Instead, the database figures out whether SQL authorization is on based on whether derby.database.sqlAuthorization is set or NATIVE authentication is on. Touches the following files ----------------- M java/engine/org/apache/derby/iapi/services/property/PropertyUtil.java M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java If NATIVE authentication is set on-disk, then this trumps all other settings of derby.authentication.provider. ----------------- M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java Removes the transaction argument from DataDictionary.getUser(). The argument was not used. ----------------- M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Don't set derby.database.sqlAuthorization on disk when creating a credentials DB. Instead, determine whether SQL authorization is on by consulting both derby.database.sqlAuthorization and derby.authentication.provider. ----------------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error message raised on bad attempts to set derby.authentication.provider on disk. ----------------- M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java M java/testing/org/apache/derbyTesting/junit/DatabaseChangeSetup.java Logic to shutdown a single-use database as part of a test. ----------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java New tests to verify the functionality added by this patch.
          Hide
          Rick Hillegas added a comment -

          Committed derby-866-10-ac-propChanging.diff at subversion revision 1235750.

          Show
          Rick Hillegas added a comment - Committed derby-866-10-ac-propChanging.diff at subversion revision 1235750.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-11-aa-upgradeTest.diff. This patch adds a check to prevent you from trying to read a user's credentials in a database which hasn't been upgraded to 10.9, that is, a database which doesn't even have a SYSUSERS table. The patch adds another upgrade test to verify that you can't enable NATIVE LOCAL authentication in a soft-upgraded database. Committed at subversion revision 1236340.

          I have run the upgrade suite against the following starting versions:

          10.1.3.1
          10.2.2.1
          10.3.3.0
          10.4.2.1
          10.5.3.0
          10.6.2.1
          10.7.1.1
          10.8.2.2

          Touches the following files:

          ----------

          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

          Prevents code from calling DataDictionary.getUser() until the database has been hard-upgraded to 10.9.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

          New upgrade test.

          Show
          Rick Hillegas added a comment - Attaching derby-866-11-aa-upgradeTest.diff. This patch adds a check to prevent you from trying to read a user's credentials in a database which hasn't been upgraded to 10.9, that is, a database which doesn't even have a SYSUSERS table. The patch adds another upgrade test to verify that you can't enable NATIVE LOCAL authentication in a soft-upgraded database. Committed at subversion revision 1236340. I have run the upgrade suite against the following starting versions: 10.1.3.1 10.2.2.1 10.3.3.0 10.4.2.1 10.5.3.0 10.6.2.1 10.7.1.1 10.8.2.2 Touches the following files: ---------- M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Prevents code from calling DataDictionary.getUser() until the database has been hard-upgraded to 10.9. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java New upgrade test.
          Hide
          Rick Hillegas added a comment -

          I committed derby-866-11-aa-upgradeTest.diff on 2012-01-26 at subversion revision 1236340.

          Show
          Rick Hillegas added a comment - I committed derby-866-11-aa-upgradeTest.diff on 2012-01-26 at subversion revision 1236340.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-12-ac-passwordExpiration.diff. This patch adds password expiration. I am running the regression tests now.

          This patch adds 2 new Derby knobs:

          1) derby.authentication.native.passwordLifetimeMillis - This is the maximum time (in milliseconds) that a NATIVE password remains valid. If more than this time has expired since a password was updated, then the user can't log in. A special check prevents the DBO's password from expiring. This property defaults to be 31 days.

          2) derby.authentication.native.passwordLifetimeThreshold - This is the threshold (expressed as a double) for triggering a warning that a password is about to expire. This property defaults to be 0.125 (i.e. 1/8 the maximum password lifetime). This is a new property not described by the current functional spec. I will add a description to the spec.

          Touches the following files:

          ------------

          M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java
          M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java

          Adds a warning at connection time if a password is about to expire. Flunks authentication if the password has expired.

          ------------

          M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java

          Adds logic to prevent the new properties from being set to bad values.

          ------------

          M java/engine/org/apache/derby/iapi/reference/Property.java

          The new properties.

          ------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error messages.

          ------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          New tests.

          Show
          Rick Hillegas added a comment - Attaching derby-866-12-ac-passwordExpiration.diff. This patch adds password expiration. I am running the regression tests now. This patch adds 2 new Derby knobs: 1) derby.authentication.native.passwordLifetimeMillis - This is the maximum time (in milliseconds) that a NATIVE password remains valid. If more than this time has expired since a password was updated, then the user can't log in. A special check prevents the DBO's password from expiring. This property defaults to be 31 days. 2) derby.authentication.native.passwordLifetimeThreshold - This is the threshold (expressed as a double) for triggering a warning that a password is about to expire. This property defaults to be 0.125 (i.e. 1/8 the maximum password lifetime). This is a new property not described by the current functional spec. I will add a description to the spec. Touches the following files: ------------ M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java Adds a warning at connection time if a password is about to expire. Flunks authentication if the password has expired. ------------ M java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java Adds logic to prevent the new properties from being set to bad values. ------------ M java/engine/org/apache/derby/iapi/reference/Property.java The new properties. ------------ M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error messages. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java New tests.
          Hide
          Rick Hillegas added a comment -

          Attaching rev 5 of the functional spec for this feature. This version makes the following changes:

          1) Documents how the password hashing properties affect NATIVE passwords.
          2) Documents that you CAN'T connect to un-upgraded databases when NATIVE authentication is on.
          3) Changes the threshold for password-expiration warnings to be 1/8 the length of the maximum password lifetime. This can be configured via the derby.authentication.native.passwordLifetimeThreshold property.
          4) Clarifies that the password of the DBO of a Credentials DB never expires.

          Show
          Rick Hillegas added a comment - Attaching rev 5 of the functional spec for this feature. This version makes the following changes: 1) Documents how the password hashing properties affect NATIVE passwords. 2) Documents that you CAN'T connect to un-upgraded databases when NATIVE authentication is on. 3) Changes the threshold for password-expiration warnings to be 1/8 the length of the maximum password lifetime. This can be configured via the derby.authentication.native.passwordLifetimeThreshold property. 4) Clarifies that the password of the DBO of a Credentials DB never expires.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-12-ac-passwordExpiration.diff at subversion revision 1238727.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-12-ac-passwordExpiration.diff at subversion revision 1238727.
          Hide
          Rick Hillegas added a comment - - edited

          I have returned to the topic of implementing network password substitution for NATIVE authentication. I do not believe this is possible.

          NATIVE authentication only works if passwords are hashed by a strong algorithm specified by the derby.authentication.builtin.algorithm property. But password substitution is not possible with these strong algorithms, according to the "Network client security" section of the Admin Guide, and according to the 10.6.1.0 release note accompanying the work on DERBY-4483 (see http://db.apache.org/derby/releases/release-10.6.1.0.html#Note+for+DERBY-4483 ).

          This makes sense. For password substitution to work with strong algorithms, the network handshake would have to give the client the details about how the password is hashed on disk at the server end--those details would have to be transmitted BEFORE the substitute password is computed by the client. I am not aware of any such negotiation in the Derby network protocol.

          NATIVE passwords transmitted over the wire must be secured the same way that BUILTIN passwords are secured after the fix to CVE-2009-4269:

          1) Both ends of the connection must use a capable JCE which handles the 256 bit primes needed by ClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY.

          2) Or the entire network traffic must be encrypted with SSL/TLS.

          Show
          Rick Hillegas added a comment - - edited I have returned to the topic of implementing network password substitution for NATIVE authentication. I do not believe this is possible. NATIVE authentication only works if passwords are hashed by a strong algorithm specified by the derby.authentication.builtin.algorithm property. But password substitution is not possible with these strong algorithms, according to the "Network client security" section of the Admin Guide, and according to the 10.6.1.0 release note accompanying the work on DERBY-4483 (see http://db.apache.org/derby/releases/release-10.6.1.0.html#Note+for+DERBY-4483 ). This makes sense. For password substitution to work with strong algorithms, the network handshake would have to give the client the details about how the password is hashed on disk at the server end--those details would have to be transmitted BEFORE the substitute password is computed by the client. I am not aware of any such negotiation in the Derby network protocol. NATIVE passwords transmitted over the wire must be secured the same way that BUILTIN passwords are secured after the fix to CVE-2009-4269: 1) Both ends of the connection must use a capable JCE which handles the 256 bit primes needed by ClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY. 2) Or the entire network traffic must be encrypted with SSL/TLS.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-13-ab-systemWideOperationTests.diff. This adds an additional test to verify that NATIVE authentication operates correctly when restoring a database from a backup. The regression tests pass for me on this patch.

          The existing NATIVE authentication tests cover the following system-wide operations:

          1) Database creation.

          2) Engine shutdown.

          3) Server shutdown. This is hard to see because the decorators wrap the default credentials. But I have traced the server code and verified that the NATIVE authenticator is being called at server shutdown and it is being passed the default credentials.

          This patch adds tests for database restoration.

          Touches the following files

          --------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          The new test. I also reworked the decorators so that the fix for derby-5580 should not be needed anymore. Let me know if that bug resurfaces.

          --------------

          M java/testing/org/apache/derbyTesting/junit/NetworkServerTestSetup.java

          Removed a shutdown check added by a previous patch. It looks unnecessary to me.

          Show
          Rick Hillegas added a comment - Attaching derby-866-13-ab-systemWideOperationTests.diff. This adds an additional test to verify that NATIVE authentication operates correctly when restoring a database from a backup. The regression tests pass for me on this patch. The existing NATIVE authentication tests cover the following system-wide operations: 1) Database creation. 2) Engine shutdown. 3) Server shutdown. This is hard to see because the decorators wrap the default credentials. But I have traced the server code and verified that the NATIVE authenticator is being called at server shutdown and it is being passed the default credentials. This patch adds tests for database restoration. Touches the following files -------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java The new test. I also reworked the decorators so that the fix for derby-5580 should not be needed anymore. Let me know if that bug resurfaces. -------------- M java/testing/org/apache/derbyTesting/junit/NetworkServerTestSetup.java Removed a shutdown check added by a previous patch. It looks unnecessary to me.
          Hide
          Rick Hillegas added a comment -

          Committed derby-866-13-ab-systemWideOperationTests.diff at subversion revision 1240152.

          Show
          Rick Hillegas added a comment - Committed derby-866-13-ab-systemWideOperationTests.diff at subversion revision 1240152.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-14-ad-badNativeSpec.diff. This patch adds some logic to prevent the authentication service from booting if the user specifies NATIVE + LOCAL authentication at the system level. The patch also adds more tests for setting and changing the value of the authentication provider property. The patch also re-enables this test for all platforms except Windows (see the discussion on DERBY-5601).

          The functional spec says that derby.authentication.provider=NATIVE::LOCAL is illegal if specified at the system level. That is because this setting does not identify a credentials DB for system-wide operations. This patch enforces that rule. If you attempt this setting at the system level, Derby will refuse to boot.

          Touches the following files:

          --------------

          M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java

          Check for illegal system-wide setting of derby.authentication.provider=NATIVE::LOCAL.

          --------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          New tests for settings of derby.authentication.provider.

          --------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

          Re-enable NativeAuthenticationServiceTest on non-windows platforms.

          Show
          Rick Hillegas added a comment - Attaching derby-866-14-ad-badNativeSpec.diff. This patch adds some logic to prevent the authentication service from booting if the user specifies NATIVE + LOCAL authentication at the system level. The patch also adds more tests for setting and changing the value of the authentication provider property. The patch also re-enables this test for all platforms except Windows (see the discussion on DERBY-5601 ). The functional spec says that derby.authentication.provider=NATIVE::LOCAL is illegal if specified at the system level. That is because this setting does not identify a credentials DB for system-wide operations. This patch enforces that rule. If you attempt this setting at the system level, Derby will refuse to boot. Touches the following files: -------------- M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java Check for illegal system-wide setting of derby.authentication.provider=NATIVE::LOCAL. -------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java New tests for settings of derby.authentication.provider. -------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Re-enable NativeAuthenticationServiceTest on non-windows platforms.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-866-14-ad-badNativeSpec.diff.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-866-14-ad-badNativeSpec.diff.
          Hide
          Rick Hillegas added a comment -

          Committed derby-866-14-ad-badNativeSpec.diff at subversion revision 1241479. Among other actions, this patch re-enables NativeAuthenticationServiceTest on non-windows platforms. If the *nix nightly tests fall over again, then we should disable NativeAuthenticationServiceTest again until we understand the situation better.

          Show
          Rick Hillegas added a comment - Committed derby-866-14-ad-badNativeSpec.diff at subversion revision 1241479. Among other actions, this patch re-enables NativeAuthenticationServiceTest on non-windows platforms. If the *nix nightly tests fall over again, then we should disable NativeAuthenticationServiceTest again until we understand the situation better.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-15-ae-dbInJarFileOrOnClasspath.diff. This patch adds tests for booting databases which are protected by NATIVE/LOCAL authentication and which are reached via the jar and classpath subprotocols. I will run full regression tests.

          I have successfully run the patched NativeAuthenticationServiceTest on the following platforms:

          1) Java 6 on Mac OSX
          2) Java 6 on XP
          3) Java 5 on XP
          4) OJEC

          To test the subprotocols, I built a new jar file (nast1.jar) containing a test database. The jar file will be checked into the codeline. The patch includes a script and ant targets for regenerating this jar file. The following command regenerates nast1.jar:

          ant -quiet build-test-jars

          I think that we will have to regenerate this jar file when we create the 10.9 release candidate. That is because the jar file I am checking in now was created with an alpha version of Derby; I think that a production version of Derby will refuse to boot it.

          In testing against databases on the classpath, I tripped across a bug involving classpath databases and security managers: DERBY-5615. Currently, the classpath db test is run only when the security manager is disabled. Once DERBY-5615 is fixed, we should check to see whether we can re-enable the security manager for the classpath test in this patch. It may be that there are other bugs having to do with classpath dbs and the security manager.

          I also do not run the subprotocol tests on Windows platforms because of another bug I tripped across, having to do with releasing file handles during engine shutdown when databases have been booted from the classpath (DERBY-5618). When that bug is fixed, we can revisit NativeAuthenticationServiceTest.

          Touches the following files:

          --------------

          M java/engine/org/apache/derby/impl/store/raw/data/BaseDataFileFactory.java

          DERBY-5615 tripped an NPE in this class. I put in some defensive logic.

          --------------

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar
          M build.xml

          Script and ant targets for building a database in a jar file.

          --------------

          A java/testing/org/apache/derbyTesting/junit/ClasspathSetup.java
          M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java
          M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
          M java/testing/org/apache/derbyTesting/junit/SupportFilesSetup.java
          M java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy

          Support for booting databases in jar files and on the classpath.

          --------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          The actual new test cases.

          Show
          Rick Hillegas added a comment - Attaching derby-866-15-ae-dbInJarFileOrOnClasspath.diff. This patch adds tests for booting databases which are protected by NATIVE/LOCAL authentication and which are reached via the jar and classpath subprotocols. I will run full regression tests. I have successfully run the patched NativeAuthenticationServiceTest on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP 4) OJEC To test the subprotocols, I built a new jar file (nast1.jar) containing a test database. The jar file will be checked into the codeline. The patch includes a script and ant targets for regenerating this jar file. The following command regenerates nast1.jar: ant -quiet build-test-jars I think that we will have to regenerate this jar file when we create the 10.9 release candidate. That is because the jar file I am checking in now was created with an alpha version of Derby; I think that a production version of Derby will refuse to boot it. In testing against databases on the classpath, I tripped across a bug involving classpath databases and security managers: DERBY-5615 . Currently, the classpath db test is run only when the security manager is disabled. Once DERBY-5615 is fixed, we should check to see whether we can re-enable the security manager for the classpath test in this patch. It may be that there are other bugs having to do with classpath dbs and the security manager. I also do not run the subprotocol tests on Windows platforms because of another bug I tripped across, having to do with releasing file handles during engine shutdown when databases have been booted from the classpath ( DERBY-5618 ). When that bug is fixed, we can revisit NativeAuthenticationServiceTest. Touches the following files: -------------- M java/engine/org/apache/derby/impl/store/raw/data/BaseDataFileFactory.java DERBY-5615 tripped an NPE in this class. I put in some defensive logic. -------------- A java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/build.xml A java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar M build.xml Script and ant targets for building a database in a jar file. -------------- A java/testing/org/apache/derbyTesting/junit/ClasspathSetup.java M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java M java/testing/org/apache/derbyTesting/junit/SupportFilesSetup.java M java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy Support for booting databases in jar files and on the classpath. -------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java The actual new test cases.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me on derby-866-15-ae-dbInJarFileOrOnClasspath.diff. Committed at subversion revision 1244666.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me on derby-866-15-ae-dbInJarFileOrOnClasspath.diff. Committed at subversion revision 1244666.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-16-aa-credDBViaSubprotocol.diff. This patch adds tests for system-wide authentication using a credentials db accessed via the jar and classpath subprotocols.

          The modified NativeAuthenticationServiceTest ran cleanly for me on the following platforms:

          1) Java 6 on Mac OSX
          2) Java 6 on XP
          3) Java 5 on XP
          4) OJEC

          Committed at subversion revision 1245126.

          Touches the following file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-16-aa-credDBViaSubprotocol.diff. This patch adds tests for system-wide authentication using a credentials db accessed via the jar and classpath subprotocols. The modified NativeAuthenticationServiceTest ran cleanly for me on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP 4) OJEC Committed at subversion revision 1245126. Touches the following file: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-17-aa-grantRevokeNative.diff. This patch adds some more tests for granting/revoking EXECUTE permission on the NATIVE procs. Seemed useful to re-test this behavior with NATIVE authentication turned on. Committed at subversion revision 1245451.

          Touches the following file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-17-aa-grantRevokeNative.diff. This patch adds some more tests for granting/revoking EXECUTE permission on the NATIVE procs. Seemed useful to re-test this behavior with NATIVE authentication turned on. Committed at subversion revision 1245451. Touches the following file: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java
          Hide
          Rick Hillegas added a comment -

          Attaching rev 6 of the functional spec. This rev clarifies a couple points:

          o Clarify that if NATIVE authentication is set at the system level, it can still be overridden at the database level using database-only properties.

          o Clarify that you must shutdown the network server before shutting down the Derby engine when you are using NATIVE authentication.

          Show
          Rick Hillegas added a comment - Attaching rev 6 of the functional spec. This rev clarifies a couple points: o Clarify that if NATIVE authentication is set at the system level, it can still be overridden at the database level using database-only properties. o Clarify that you must shutdown the network server before shutting down the Derby engine when you are using NATIVE authentication.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-18-aa-encryptedCredentialsDB. This patch adds tests for encrypted credentials databases. I need to run full regression tests.

          I have successfully run NativeAuthenticationServiceTest on the following platforms:

          1) Java 6 on Mac OSX
          2) Java 6 on XP
          3) Java 5 on XP
          4) OJEC

          Touches the following files:

          ---------

          M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java
          M java/testing/org/apache/derbyTesting/junit/DataSourceConnector.java
          M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java
          M java/testing/org/apache/derbyTesting/junit/XADataSourceConnector.java
          M java/testing/org/apache/derbyTesting/junit/ConnectionPoolDataSourceConnector.java
          M java/testing/org/apache/derbyTesting/junit/Connector.java

          Added ability to create a test connection with arbitrary properties.

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar

          New test for encrypted credentials databases.

          Show
          Rick Hillegas added a comment - Attaching derby-866-18-aa-encryptedCredentialsDB. This patch adds tests for encrypted credentials databases. I need to run full regression tests. I have successfully run NativeAuthenticationServiceTest on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP 4) OJEC Touches the following files: --------- M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java M java/testing/org/apache/derbyTesting/junit/DataSourceConnector.java M java/testing/org/apache/derbyTesting/junit/TestConfiguration.java M java/testing/org/apache/derbyTesting/junit/XADataSourceConnector.java M java/testing/org/apache/derbyTesting/junit/ConnectionPoolDataSourceConnector.java M java/testing/org/apache/derbyTesting/junit/Connector.java Added ability to create a test connection with arbitrary properties. --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar New test for encrypted credentials databases.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-18-aa-encryptedCredentialsDB.diff at subversion revision 1294183.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-18-aa-encryptedCredentialsDB.diff at subversion revision 1294183.
          Hide
          Knut Anders Hatlen added a comment -

          I recently checked in a fix (as part of DERBY-5539) that made builtin authentication hash the supplied credentials also in the case where the specified user name does not exist in the user database. This was done to make it more difficult to determine whether or not a user exist without knowing the user's password. It looks like a similar fix is needed for native authentication, as NativeAuthenticationServiceImpl.authenticateLocally() returns early if a user descriptor is not found.

          Show
          Knut Anders Hatlen added a comment - I recently checked in a fix (as part of DERBY-5539 ) that made builtin authentication hash the supplied credentials also in the case where the specified user name does not exist in the user database. This was done to make it more difficult to determine whether or not a user exist without knowing the user's password. It looks like a similar fix is needed for native authentication, as NativeAuthenticationServiceImpl.authenticateLocally() returns early if a user descriptor is not found.
          Hide
          Knut Anders Hatlen added a comment -

          Another thing I noticed when I looked at NativeAuthenticationServiceImpl.authenticateLocally(), was this try/finally statement:

          try {
          if ( (candidatePassword == null) || (actualPassword == null))

          { return false; }

          (...)
          } finally

          { Arrays.fill( candidatePassword, (char) 0 ); Arrays.fill( actualPassword, (char) 0 ); }

          If the condition in the if statement evaluates to true, the finally block will run into a NullPointerException when trying to fill an array that's null.

          Show
          Knut Anders Hatlen added a comment - Another thing I noticed when I looked at NativeAuthenticationServiceImpl.authenticateLocally(), was this try/finally statement: try { if ( (candidatePassword == null) || (actualPassword == null)) { return false; } (...) } finally { Arrays.fill( candidatePassword, (char) 0 ); Arrays.fill( actualPassword, (char) 0 ); } If the condition in the if statement evaluates to true, the finally block will run into a NullPointerException when trying to fill an array that's null.
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. I am trying to puzzle through what we should do here. If I correctly understand your comments on DERBY-5539, the security vulnerability is this:

          1) It takes a different amount of time to reject a bad password if the username itself is invalid.

          2) This gives blackhats information about which usernames are good.

          We could use some default hashing algorithm to slow down credentials failure when NATIVE authentication discovers that the username itself is invalid. When the default algorithm changes or otherwise diverges from the algorithm stored for most users in SYSUSERS.HASHINGSCHEME, then failure timings will again leak information about what usernames are legal. Information will leak until existing users change their passwords so that they enjoy the new, stronger default algorithm. That's a smaller problem, but one which stumps me. I am inclined to keep this simple and not worry about the smaller vulnerability.

          Show
          Rick Hillegas added a comment - Thanks, Knut. I am trying to puzzle through what we should do here. If I correctly understand your comments on DERBY-5539 , the security vulnerability is this: 1) It takes a different amount of time to reject a bad password if the username itself is invalid. 2) This gives blackhats information about which usernames are good. We could use some default hashing algorithm to slow down credentials failure when NATIVE authentication discovers that the username itself is invalid. When the default algorithm changes or otherwise diverges from the algorithm stored for most users in SYSUSERS.HASHINGSCHEME, then failure timings will again leak information about what usernames are legal. Information will leak until existing users change their passwords so that they enjoy the new, stronger default algorithm. That's a smaller problem, but one which stumps me. I am inclined to keep this simple and not worry about the smaller vulnerability.
          Hide
          Rick Hillegas added a comment -

          Thanks for finding that potential NPE, Knut. Fixing it should be easy.

          Show
          Rick Hillegas added a comment - Thanks for finding that potential NPE, Knut. Fixing it should be easy.
          Hide
          Knut Anders Hatlen added a comment -

          I think it's OK to use the default algorithm regardless of which algorithm is used by the majority of the users, as that will catch the common case. And since password expire fairly quickly by default, we won't have diverging algorithms for a very long time. If we were to address that problem too, I think we could do it by always sleeping for a random amount of time on authentication failures.

          Show
          Knut Anders Hatlen added a comment - I think it's OK to use the default algorithm regardless of which algorithm is used by the majority of the users, as that will catch the common case. And since password expire fairly quickly by default, we won't have diverging algorithms for a very long time. If we were to address that problem too, I think we could do it by always sleeping for a random amount of time on authentication failures.
          Hide
          Kristian Waagan added a comment -

          +1 on adding the random sleep - seens like a low cost solution to implement.

          Show
          Kristian Waagan added a comment - +1 on adding the random sleep - seens like a low cost solution to implement.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-19-aa-replicationTest.diff. This patch adds a test for replicating databases when NATIVE authentication is on.

          Most of the changes in this patch result from replacing copy/pasted code with calls to some common methods which create connection URLs and databases. The common methods add credentials when authentication is on.

          I have successfully run ReplicationSuite with this patch on the following platforms:

          1) Java 6 on Mac OSX

          2) Java 6 on XP

          3) Java 5 on XP

          On the strength of those results, I have committed this patch at subversion revision 1294812.

          Touches the following files:

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationRun_Local.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationSuite.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationRun.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-19-aa-replicationTest.diff. This patch adds a test for replicating databases when NATIVE authentication is on. Most of the changes in this patch result from replacing copy/pasted code with calls to some common methods which create connection URLs and databases. The common methods add credentials when authentication is on. I have successfully run ReplicationSuite with this patch on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP On the strength of those results, I have committed this patch at subversion revision 1294812. Touches the following files: --------- M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationRun_Local.java M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationSuite.java M java/testing/org/apache/derbyTesting/functionTests/tests/replicationTests/ReplicationRun.java
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-20-aa-npeAndUserProbing.diff. This patch addresses Knut's recent suggestions:

          A) Now we perform a dummy password hash if the user doesn't exist. This is meant to confuse blackhats who probe for legal usernames by measuring how long password evaluation takes.

          B) Some defensive logic was added to prevent some NPEs. Note that I'm not sure that the NPEs could really happen. My attempt to trigger them resulted in tripping across an earlier NPE. I've plugged that one too. The defensive logic seems like a good idea, nonetheless.

          I have successfully run NativeAuthenticationServiceTest on the following platforms:

          1) Java 6 on Mac OSX
          2) Java 6 on XP
          3) Java 5 on XP
          4) OJEC

          I am running full regression tests now.

          Touches the following files:

          ---------

          M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java

          Addressed (A) and (B).

          ---------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java
          M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java

          Added a test for the NPE which was fixed.

          Show
          Rick Hillegas added a comment - Attaching derby-866-20-aa-npeAndUserProbing.diff. This patch addresses Knut's recent suggestions: A) Now we perform a dummy password hash if the user doesn't exist. This is meant to confuse blackhats who probe for legal usernames by measuring how long password evaluation takes. B) Some defensive logic was added to prevent some NPEs. Note that I'm not sure that the NPEs could really happen. My attempt to trigger them resulted in tripping across an earlier NPE. I've plugged that one too. The defensive logic seems like a good idea, nonetheless. I have successfully run NativeAuthenticationServiceTest on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP 4) OJEC I am running full regression tests now. Touches the following files: --------- M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java Addressed (A) and (B). --------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java M java/testing/org/apache/derbyTesting/junit/DriverManagerConnector.java Added a test for the NPE which was fixed.
          Hide
          Knut Anders Hatlen added a comment -

          I gave the patch a try, and now the authentication failures take approximately the same time for existing users and non-existing users.

          One small nit: You might want to use the helper method AuthenticationServiceBase.getDatabaseProperties() introduced by DERBY-5539 so that you don't need to explicitly obtain and commit a nested transaction. Otherwise, the patch looks fine to me.

          Show
          Knut Anders Hatlen added a comment - I gave the patch a try, and now the authentication failures take approximately the same time for existing users and non-existing users. One small nit: You might want to use the helper method AuthenticationServiceBase.getDatabaseProperties() introduced by DERBY-5539 so that you don't need to explicitly obtain and commit a nested transaction. Otherwise, the patch looks fine to me.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Attaching derby-866-20-ab-npeAndUserProbing.diff. This version uses AuthenticationServiceBase.getDatabaseProperties() as Knut suggested. Committed at subversion revision 1295189.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Attaching derby-866-20-ab-npeAndUserProbing.diff. This version uses AuthenticationServiceBase.getDatabaseProperties() as Knut suggested. Committed at subversion revision 1295189.
          Hide
          Rick Hillegas added a comment -

          Attaching first rev of a release note for this feature.

          Show
          Rick Hillegas added a comment - Attaching first rev of a release note for this feature.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-21-aa-emptyCredentials.diff. This patch adds logic to prevent the creation of a credentials db with vacuous username or password. Null and "" are not allowed as the username or password.

          I am running the full regression tests now. I have successfully run NativeAuthenticationServiceTest on the following platforms:

          1) Java 6 on Mac OSX
          2) Java 6 on XP
          3) Java 5 on XP
          4) OJEC

          Touches the following files:

          ----------

          M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java

          The new check for empty credentials.

          ----------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          A new error message.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          Some tests for this case.

          Show
          Rick Hillegas added a comment - Attaching derby-866-21-aa-emptyCredentials.diff. This patch adds logic to prevent the creation of a credentials db with vacuous username or password. Null and "" are not allowed as the username or password. I am running the full regression tests now. I have successfully run NativeAuthenticationServiceTest on the following platforms: 1) Java 6 on Mac OSX 2) Java 6 on XP 3) Java 5 on XP 4) OJEC Touches the following files: ---------- M java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java The new check for empty credentials. ---------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java A new error message. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java Some tests for this case.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-21-ab-emptyCredentials.diff. This patch adjusts ErrorCodeTest to account for the new error message.

          My regression test run also tripped over a series of NPEs in SecurityManagerSetup.getEffectivePolicyResource(). Those errors went away when I resynced with the head of trunk.

          I will re-run the full regression tests.

          This patch touches the same files as the previous rev plus:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ErrorCodeTest.java

          Show
          Rick Hillegas added a comment - Attaching derby-866-21-ab-emptyCredentials.diff. This patch adjusts ErrorCodeTest to account for the new error message. My regression test run also tripped over a series of NPEs in SecurityManagerSetup.getEffectivePolicyResource(). Those errors went away when I resynced with the head of trunk. I will re-run the full regression tests. This patch touches the same files as the previous rev plus: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ErrorCodeTest.java
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-21-ab-emptyCredentials.diff at subversion revision 1297528.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-21-ab-emptyCredentials.diff at subversion revision 1297528.
          Hide
          Rick Hillegas added a comment -

          At this point, I am declaring victory on the coding/unit-testing of NATIVE authentication. I believe that I have implemented and verified all of the functionality in the spec. I have reached the end of my ability to test this feature productively. My blinders, as the author of this feature, prevent me from discovering additional defects. The tests I have written lately do disclose defects in other parts of Derby outside the scope of this feature--but not new defects in NATIVE authentication itself.

          However, I'm sure there are plenty of remaining defects, perhaps even some real howlers. At this point, buddy-testers may be able to find some of the defects hidden behind my blinders.

          To help buddy-testers guess where the weaknesses lie, I would like to describe the current implementation and its touchpoints with other parts of Derby.

          --------------------------------------------------
          – NATIVE Authentication Service
          --------------------------------------------------

          The core of the implementation is the NATIVE authentication service itself, NativeAuthenticationServiceImpl. This implementation is parallel to the other authentication services, and like them, it extends AuthenticationServiceBase:

          1) BasicAuthenticationServiceImpl - This class implements BASIC authentication. I used it as the template for NativeAuthenticationServiceImpl because NATIVE and BASIC authentication share a key trickiness: the storing of credentials inside the database being connected to.

          2) JNDIAuthenticationServiceImpl - This class handles LDAP authentication.

          3) SpecificAuthenticationServiceImpl - This class handles user-written authenticators.

          4) NoneAuthenticationServiceImpl - This is the default, NOP authentication service used when the application does not require any authentication at all.

          At engine and database startup, all of these implementations of the authentication module are asked whether they match the setting of the Derby authentication properties. I had to modify SpecificAuthenticationServiceImpl because it is the catch-all implementation: by default it matches any setting of derby.authentication.provider which has not been excluded explicitly. There are quite a few Derby authentication properties:

          derby.authentication.builtin.algorithm
          derby.authentication.ldap.searchAuthDN
          derby.authentication.ldap.searchAuthPW
          derby.authentication.ldap.searchBase
          derby.authentication.ldap.searchFilter
          derby.authentication.provider
          derby.authentication.server
          derby.connection.requireAuthentication

          — Bootstrapping

          The first bit of trickiness in NativeAuthenticationServiceImpl is the logic which determines whether this implementation matches the setting of the Derby authentication properties. This is tricky because unlike the other authentication services, NATIVE authentication is supposed to be easy for the user to configure. It only requires that you set one property, viz. derby.authentication.provider. The other authentication services require that you redundantly set derby.connection.requireAuthentication.

          Bootstrapping NATIVE authentication follows the normal Derby model, which divides bootstrapping into two phases: canSupport() and boot(). Validation of property settings can't be performed by canSupport() because that method does not let you raise an invalidation exception. Note that this behavior gives rise to the quirky result that if you garble the setting of derby.authentication.provider, you will end up booting the catch-all SpecificAuthenticationServiceImpl, which will promptly fall over because the garbled derby.authentication.provider does not resolve to a valid class name.

          However, deferring the validation of derby.authentication.provider to NativeAuthenticationServiceImpl.boot() is fine. That is because the NATIVE authentication service needs to know whether it is being booted system-wide or inside a specific database. The value NATIVE::LOCAL is meaningless for system-wide authentication but it is the only legal value which can be stored in a database. That last point is true because we don't want to hard-code the name of the system-wide credentials db inside any specific database; we want the database to be portable and we want to give the system administrator the option of moving the credentials db if there is a problem.

          — Creating the credentials DB

          The next bit of trickiness involves coupling connection management with NATIVE authentication. Special logic in EmbedConnection checks to see whether the database being created is the credentials database. If so, authentication isn't performed: there is no authentication authority yet and the presented credentials are simply accepted as is and will be used to create the DBO account. It might make more sense if this logic were inside NativeAuthenticationServiceImpl. However, the knowledge that the database is being created is not passed across the authentication call, so NativeAuthenticationServiceImpl can't perform this check.

          — Steady-state

          The rest of the logic in NativeAuthenticationServiceImpl happens under authenticateUser() and processes the main execution path: authenticating users for system-wide or database-specific tasks. At this point the NATIVE authentication service is booted and we know that we are not authenticating the creation of the credentials db itself.

          The authenticateUser() method figures out whether credentials should be looked up in the current database or in another database.

          Looking up the credentials in another database gives rise to the next bit of trickiness. This logic has already been corrected to account for a deadlock in the Java 5 VM. We were doing something which fell outside the main execution path understood by the VM's JDBC layer, viz., we were trying to open a nested JDBC connection in the middle of establishing an outer JDBC connection. After the corrective logic, we are now merely trying to establish a nested Derby connection while trying to establish an outer Derby connection. Needless to say, this still falls outside the main execution path understood by Derby and there may be bugs lurking here.

          Other trickiness may lurk inside the logic which looks up credentials in the current database. To find the real credentials, we must access the Store before the connection has been completely established. Fortunately, BUILTIN authentication has been doing this for years so many bugs in this area may have been flushed out already. We also access the DataDictionary before the connection has been completely established. This unusual code path is not exercised by BUILTIN authentication and so it may be hiding some bugs.

          --------------------------------------------------
          – Properties Management
          --------------------------------------------------

          NATIVE authentication attempts to be simple to configure and hard to turn off. All you have to do is turn one knob (the derby.authentication.provider property). When you turn this knob to NATIVE authentication, you also turn on SQL authorization. Once NATIVE authentication has been set in the database itself, it can't be disabled.

          The interaction of Derby properties is already complicated and is influenced by the following considerations:

          o Whether the property is set on the VM command line, in
          derby.properties, or in the database.

          o Whether the special derby.database.propertiesOnly property is set.

          o Whether other, related properties have been set correctly.

          I confess to being confused repeatedly by the way that Derby properties interact. And now I have complicated that interaction by introducing the peculiar, trumping behaviors of NATIVE authentication. The goal was to simplify how users protect their data, but I can't promise that edge cases haven't crept in or been made worse. A crucial piece of trumping happens in PropertyUtil.getPropertyFromSet(). There could be more bugs in this area as well.

          --------------------------------------------------
          – Data Dictionary
          --------------------------------------------------

          The Data Dictionary assists NATIVE authentication. Here are the major touchpoints:

          1) A new SYSUSERS catalog has been added. Unlike other catalogs, ordinary users can't view this catalog (when authentication is on) and even the DBO can't view the PASSWORD column. These behaviors are unprecedented so they may be particularly buggy.

          2) I added 4 new procedures for managing passwords: syscs_util.syscs_create_user, syscs_util.syscs_drop_user, syscs_util.syscs_reset_password, and syscs_util.syscs_modify_password. The first 3 are modelled on other, existing DBO-only procedures. The last procedure is modelled on existing procedures which anyone can run.

          3) I moved the password-hashing logic into a PasswordHasher class in the Data Dictionary.

          Show
          Rick Hillegas added a comment - At this point, I am declaring victory on the coding/unit-testing of NATIVE authentication. I believe that I have implemented and verified all of the functionality in the spec. I have reached the end of my ability to test this feature productively. My blinders, as the author of this feature, prevent me from discovering additional defects. The tests I have written lately do disclose defects in other parts of Derby outside the scope of this feature--but not new defects in NATIVE authentication itself. However, I'm sure there are plenty of remaining defects, perhaps even some real howlers. At this point, buddy-testers may be able to find some of the defects hidden behind my blinders. To help buddy-testers guess where the weaknesses lie, I would like to describe the current implementation and its touchpoints with other parts of Derby. -------------------------------------------------- – NATIVE Authentication Service -------------------------------------------------- The core of the implementation is the NATIVE authentication service itself, NativeAuthenticationServiceImpl. This implementation is parallel to the other authentication services, and like them, it extends AuthenticationServiceBase: 1) BasicAuthenticationServiceImpl - This class implements BASIC authentication. I used it as the template for NativeAuthenticationServiceImpl because NATIVE and BASIC authentication share a key trickiness: the storing of credentials inside the database being connected to. 2) JNDIAuthenticationServiceImpl - This class handles LDAP authentication. 3) SpecificAuthenticationServiceImpl - This class handles user-written authenticators. 4) NoneAuthenticationServiceImpl - This is the default, NOP authentication service used when the application does not require any authentication at all. At engine and database startup, all of these implementations of the authentication module are asked whether they match the setting of the Derby authentication properties. I had to modify SpecificAuthenticationServiceImpl because it is the catch-all implementation: by default it matches any setting of derby.authentication.provider which has not been excluded explicitly. There are quite a few Derby authentication properties: derby.authentication.builtin.algorithm derby.authentication.ldap.searchAuthDN derby.authentication.ldap.searchAuthPW derby.authentication.ldap.searchBase derby.authentication.ldap.searchFilter derby.authentication.provider derby.authentication.server derby.connection.requireAuthentication — Bootstrapping The first bit of trickiness in NativeAuthenticationServiceImpl is the logic which determines whether this implementation matches the setting of the Derby authentication properties. This is tricky because unlike the other authentication services, NATIVE authentication is supposed to be easy for the user to configure. It only requires that you set one property, viz. derby.authentication.provider. The other authentication services require that you redundantly set derby.connection.requireAuthentication. Bootstrapping NATIVE authentication follows the normal Derby model, which divides bootstrapping into two phases: canSupport() and boot(). Validation of property settings can't be performed by canSupport() because that method does not let you raise an invalidation exception. Note that this behavior gives rise to the quirky result that if you garble the setting of derby.authentication.provider, you will end up booting the catch-all SpecificAuthenticationServiceImpl, which will promptly fall over because the garbled derby.authentication.provider does not resolve to a valid class name. However, deferring the validation of derby.authentication.provider to NativeAuthenticationServiceImpl.boot() is fine. That is because the NATIVE authentication service needs to know whether it is being booted system-wide or inside a specific database. The value NATIVE::LOCAL is meaningless for system-wide authentication but it is the only legal value which can be stored in a database. That last point is true because we don't want to hard-code the name of the system-wide credentials db inside any specific database; we want the database to be portable and we want to give the system administrator the option of moving the credentials db if there is a problem. — Creating the credentials DB The next bit of trickiness involves coupling connection management with NATIVE authentication. Special logic in EmbedConnection checks to see whether the database being created is the credentials database. If so, authentication isn't performed: there is no authentication authority yet and the presented credentials are simply accepted as is and will be used to create the DBO account. It might make more sense if this logic were inside NativeAuthenticationServiceImpl. However, the knowledge that the database is being created is not passed across the authentication call, so NativeAuthenticationServiceImpl can't perform this check. — Steady-state The rest of the logic in NativeAuthenticationServiceImpl happens under authenticateUser() and processes the main execution path: authenticating users for system-wide or database-specific tasks. At this point the NATIVE authentication service is booted and we know that we are not authenticating the creation of the credentials db itself. The authenticateUser() method figures out whether credentials should be looked up in the current database or in another database. Looking up the credentials in another database gives rise to the next bit of trickiness. This logic has already been corrected to account for a deadlock in the Java 5 VM. We were doing something which fell outside the main execution path understood by the VM's JDBC layer, viz., we were trying to open a nested JDBC connection in the middle of establishing an outer JDBC connection. After the corrective logic, we are now merely trying to establish a nested Derby connection while trying to establish an outer Derby connection. Needless to say, this still falls outside the main execution path understood by Derby and there may be bugs lurking here. Other trickiness may lurk inside the logic which looks up credentials in the current database. To find the real credentials, we must access the Store before the connection has been completely established. Fortunately, BUILTIN authentication has been doing this for years so many bugs in this area may have been flushed out already. We also access the DataDictionary before the connection has been completely established. This unusual code path is not exercised by BUILTIN authentication and so it may be hiding some bugs. -------------------------------------------------- – Properties Management -------------------------------------------------- NATIVE authentication attempts to be simple to configure and hard to turn off. All you have to do is turn one knob (the derby.authentication.provider property). When you turn this knob to NATIVE authentication, you also turn on SQL authorization. Once NATIVE authentication has been set in the database itself, it can't be disabled. The interaction of Derby properties is already complicated and is influenced by the following considerations: o Whether the property is set on the VM command line, in derby.properties, or in the database. o Whether the special derby.database.propertiesOnly property is set. o Whether other, related properties have been set correctly. I confess to being confused repeatedly by the way that Derby properties interact. And now I have complicated that interaction by introducing the peculiar, trumping behaviors of NATIVE authentication. The goal was to simplify how users protect their data, but I can't promise that edge cases haven't crept in or been made worse. A crucial piece of trumping happens in PropertyUtil.getPropertyFromSet(). There could be more bugs in this area as well. -------------------------------------------------- – Data Dictionary -------------------------------------------------- The Data Dictionary assists NATIVE authentication. Here are the major touchpoints: 1) A new SYSUSERS catalog has been added. Unlike other catalogs, ordinary users can't view this catalog (when authentication is on) and even the DBO can't view the PASSWORD column. These behaviors are unprecedented so they may be particularly buggy. 2) I added 4 new procedures for managing passwords: syscs_util.syscs_create_user, syscs_util.syscs_drop_user, syscs_util.syscs_reset_password, and syscs_util.syscs_modify_password. The first 3 are modelled on other, existing DBO-only procedures. The last procedure is modelled on existing procedures which anyone can run. 3) I moved the password-hashing logic into a PasswordHasher class in the Data Dictionary.
          Hide
          Kim Haase added a comment -

          I thought it was pretty cool that even as the DBO I could not view the passwords:

          ij> connect 'jdbc:derby:credsDB';
          ERROR 08004: Database connection refused.
          ij> connect 'jdbc:derby:credsDB;user=sysadm;password=little7xylamb';
          ij> select * from sys.sysusers;
          ERROR 4251E: No-one can view the 'SYSUSERS'.'PASSWORD' column.

          One typo-quibble, though: It should be "No one", not "No-one".

          Show
          Kim Haase added a comment - I thought it was pretty cool that even as the DBO I could not view the passwords: ij> connect 'jdbc:derby:credsDB'; ERROR 08004: Database connection refused. ij> connect 'jdbc:derby:credsDB;user=sysadm;password=little7xylamb'; ij> select * from sys.sysusers; ERROR 4251E: No-one can view the 'SYSUSERS'.'PASSWORD' column. One typo-quibble, though: It should be "No one", not "No-one".
          Hide
          Rick Hillegas added a comment -

          Thanks Kim. I logged DERBY-5644 for the typo.

          Show
          Rick Hillegas added a comment - Thanks Kim. I logged DERBY-5644 for the typo.
          Hide
          Kristian Waagan added a comment -

          Just a tiny nit about error messages:
          01J15 and 01J16 use upper case for system procedures
          XCY05.S uses lower case for system procedure

          Show
          Kristian Waagan added a comment - Just a tiny nit about error messages: 01J15 and 01J16 use upper case for system procedures XCY05.S uses lower case for system procedure
          Hide
          Rick Hillegas added a comment -

          Private discussions with Dag have suggested an improvement to the functional spec for NATIVE authentication. This improvement would support the overriding design goals of this feature:

          1) Easy to setup.

          2) Hard to subvert.

          The improvement would eliminate the following weird edge case. The edge case doesn't cause any correctness problems which we know of. It just doesn't fit well with the design goals. Here's the edge case:

          A) You can store credentials in an unsecured database using the SYSCS_CREATE_USER() system procedure.

          B) Then you can use the unsecure database as a credentials db by setting a system property and rebooting the engine:

          derby.authentication.provider=NATIVE:unsecuredDB:LOCAL

          C) Because the database already exists, Derby won't automatically set derby.authentication.provider=NATIVE::LOCAL in unsecuredDB. This means that you can later take the database offline and use it without authentication. It seems odd that the credentials DB itself could be so unsecure.

          To eliminate this weird edge case, we propose the following:

          NATIVE::LOCAL authentication is on iff the DBO's credentials are stored in SYS.SYSUSERS.

          Here's how we would enforce this rule:

          i) The first user created by SYSCS_CREATE_USER() must be the DBO.

          ii) When the DBO's credentials are stored in SYS.SYSUSERS, Derby will automatically set derby.authentication.provider=NATIVE::LOCAL in the database.

          This change would further the overriding design goals as follows:

          1') It would now be even easier to convert a database to use NATIVE authentication. All you would have to do is store credentials for the DBO. You would no longer need to also set derby.authentication.provider=NATIVE::LOCAL.

          2') The new rule would eliminate one more way of subverting Derby security.

          Show
          Rick Hillegas added a comment - Private discussions with Dag have suggested an improvement to the functional spec for NATIVE authentication. This improvement would support the overriding design goals of this feature: 1) Easy to setup. 2) Hard to subvert. The improvement would eliminate the following weird edge case. The edge case doesn't cause any correctness problems which we know of. It just doesn't fit well with the design goals. Here's the edge case: A) You can store credentials in an unsecured database using the SYSCS_CREATE_USER() system procedure. B) Then you can use the unsecure database as a credentials db by setting a system property and rebooting the engine: derby.authentication.provider=NATIVE:unsecuredDB:LOCAL C) Because the database already exists, Derby won't automatically set derby.authentication.provider=NATIVE::LOCAL in unsecuredDB. This means that you can later take the database offline and use it without authentication. It seems odd that the credentials DB itself could be so unsecure. To eliminate this weird edge case, we propose the following: NATIVE::LOCAL authentication is on iff the DBO's credentials are stored in SYS.SYSUSERS. Here's how we would enforce this rule: i) The first user created by SYSCS_CREATE_USER() must be the DBO. ii) When the DBO's credentials are stored in SYS.SYSUSERS, Derby will automatically set derby.authentication.provider=NATIVE::LOCAL in the database. This change would further the overriding design goals as follows: 1') It would now be even easier to convert a database to use NATIVE authentication. All you would have to do is store credentials for the DBO. You would no longer need to also set derby.authentication.provider=NATIVE::LOCAL. 2') The new rule would eliminate one more way of subverting Derby security.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-22-aa-dboFirst.diff. This patch addresses the edge case described above on 2012-03-19. Now the DBO must be the first user whose credentials are stored. Storing credentials for the DBO automatically marks the database as a credentials DB. Committed at subversion revision 1302868.

          Turning a legacy database into a credentials DB is now simpler. All you have to do is call syscs_create_user() to store the DBO's credentials.

          Regression tests passed cleanly for me. I also successfully ran NativeAuthenticationServiceTest on OJEC.

          Touches the following files:

          ----------

          M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java
          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          Make sure that the first credentials which are stored are those of the DBO. If they are, then set derby.authentication.provider=NATIVE::LOCAL in the database. That last piece of logic was moved out of the NATIVE authentication service into the syscs_create_user procedure.

          ----------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error message raised if you try to store credentials for some other user before you store the DBO's credentials.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar

          This is the script which creates the jar files used by NativeAuthenticationServiceTest. The script was changed so that it no longer explicitly sets derby.authentication.provider=NATIVE::LOCAL in the database (not needed anymore). In addition, I turned off password expiration in the jar'd databases so that NativeAuthenticationServiceTest won't fail 32 days after checking in new jar'd databases.

          ----------

          D java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

          NativeAuthProcs was failing to shutdown correctly because user creation now turns on NATIVE+LOCAL authentication. This test is largely redundant--except for the password hashing tests, all of its cases are also tested in NativeAuthenticationServiceTest now. The password hashing tests were moved to NativeAuthenticationServiceTest and NativeAuthProcs was deprecated.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          This test needed lots of little tweaks to handle the new behavior.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

          Now the upgrade tests have to be careful to not actually store the DBO's credentials since that will turn on NATIVE+LOCAL authentication.

          Show
          Rick Hillegas added a comment - Attaching derby-866-22-aa-dboFirst.diff. This patch addresses the edge case described above on 2012-03-19. Now the DBO must be the first user whose credentials are stored. Storing credentials for the DBO automatically marks the database as a credentials DB. Committed at subversion revision 1302868. Turning a legacy database into a credentials DB is now simpler. All you have to do is call syscs_create_user() to store the DBO's credentials. Regression tests passed cleanly for me. I also successfully ran NativeAuthenticationServiceTest on OJEC. Touches the following files: ---------- M java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java M java/engine/org/apache/derby/catalog/SystemProcedures.java Make sure that the first credentials which are stored are those of the DBO. If they are, then set derby.authentication.provider=NATIVE::LOCAL in the database. That last piece of logic was moved out of the NATIVE authentication service into the syscs_create_user procedure. ---------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error message raised if you try to store credentials for some other user before you store the DBO's credentials. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast_init.sql M java/testing/org/apache/derbyTesting/functionTests/tests/lang/nast1.jar This is the script which creates the jar files used by NativeAuthenticationServiceTest. The script was changed so that it no longer explicitly sets derby.authentication.provider=NATIVE::LOCAL in the database (not needed anymore). In addition, I turned off password expiration in the jar'd databases so that NativeAuthenticationServiceTest won't fail 32 days after checking in new jar'd databases. ---------- D java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java NativeAuthProcs was failing to shutdown correctly because user creation now turns on NATIVE+LOCAL authentication. This test is largely redundant--except for the password hashing tests, all of its cases are also tested in NativeAuthenticationServiceTest now. The password hashing tests were moved to NativeAuthenticationServiceTest and NativeAuthProcs was deprecated. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java This test needed lots of little tweaks to handle the new behavior. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java Now the upgrade tests have to be careful to not actually store the DBO's credentials since that will turn on NATIVE+LOCAL authentication.
          Hide
          Rick Hillegas added a comment -

          Attaching version 7 of the functional spec. This version describes the new behavior which was just checked in. Changes are summarized in the version 7.0 comment at the head of the spec:

          "Introduced a new rule to simplify the conversion of legacy databases to NATIVE authentication and to make it harder to subvert a Credentials DB. The new rule is this: A database is a Credentials DB iff credentials have been stored in its SYS.SYSUSERS table.

          o Clarified that derby.authentication.provider is set to the value NATIVE::LOCAL by Derby itself and that this value is never explicitly set by an application.
          o Clarified that a legacy database becomes a Credentials DB when the DBO stores her credentials in SYS.SYSUSERS. Revised the example in the Database Creation section accordingly. Repeated this clarification in the section on Hard Upgrade.
          o Clarified that the DBO's credentials must be the very first credentials stored in a legacy database via the syscs_util.syscs_create_user procedure. Calling this procedure permanently marks a database as a Credentials DB.

          In addition, clarified that when NATIVE authentication is enabled, Derby behaves as if derby.connection.requireAuthentication=true and derby.database.sqlAuthorization=true regardless of how those properties are set by any other means."

          Show
          Rick Hillegas added a comment - Attaching version 7 of the functional spec. This version describes the new behavior which was just checked in. Changes are summarized in the version 7.0 comment at the head of the spec: "Introduced a new rule to simplify the conversion of legacy databases to NATIVE authentication and to make it harder to subvert a Credentials DB. The new rule is this: A database is a Credentials DB iff credentials have been stored in its SYS.SYSUSERS table. o Clarified that derby.authentication.provider is set to the value NATIVE::LOCAL by Derby itself and that this value is never explicitly set by an application. o Clarified that a legacy database becomes a Credentials DB when the DBO stores her credentials in SYS.SYSUSERS. Revised the example in the Database Creation section accordingly. Repeated this clarification in the section on Hard Upgrade. o Clarified that the DBO's credentials must be the very first credentials stored in a legacy database via the syscs_util.syscs_create_user procedure. Calling this procedure permanently marks a database as a Credentials DB. In addition, clarified that when NATIVE authentication is enabled, Derby behaves as if derby.connection.requireAuthentication=true and derby.database.sqlAuthorization=true regardless of how those properties are set by any other means."
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-23-aa-improveErrorMessages.diff. This patch improves the wording of some error messages, based on Kim's advice. Committed at subversion revision 1303069.

          Touches the following file:

          M java/engine/org/apache/derby/loc/messages.xml

          Show
          Rick Hillegas added a comment - Attaching derby-866-23-aa-improveErrorMessages.diff. This patch improves the wording of some error messages, based on Kim's advice. Committed at subversion revision 1303069. Touches the following file: M java/engine/org/apache/derby/loc/messages.xml
          Hide
          Rick Hillegas added a comment -

          Attaching derby-866-24-aa-dboMustTurnOnSecurity.diff. This patch forbids another weird Derby behavior when NATIVE authentication is involved. I am running tests now.

          The weird behavior is this: If authentication and authorization are not on, then any legal user can hijack the database by turning them on and specifying credentials which lock out everyone else. This patch prevents an insider from using NATIVE authentication to do this. Because of backward compatibility, I am reluctant to close down this behavior for other authentication schemes. But since NATIVE authentication has not gone GA yet, we have the opportunity to make it behave better.

          More exactly, this patch enforces the restriction that only the DBO can turn on NATIVE authentication.

          We can relax this restriction in the future if someone comes up with a supporting use case. However, right now this looks like a security hole to me.

          Touches the following files:

          -----------

          M java/engine/org/apache/derby/catalog/SystemProcedures.java

          The new check to let only the DBO turn on NATIVE authentication.

          -----------

          M java/engine/org/apache/derby/loc/messages.xml

          Slight rewording of an existing error message so that is can be re-used for this check.

          -----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java

          New test case to verify the new behavior.

          Show
          Rick Hillegas added a comment - Attaching derby-866-24-aa-dboMustTurnOnSecurity.diff. This patch forbids another weird Derby behavior when NATIVE authentication is involved. I am running tests now. The weird behavior is this: If authentication and authorization are not on, then any legal user can hijack the database by turning them on and specifying credentials which lock out everyone else. This patch prevents an insider from using NATIVE authentication to do this. Because of backward compatibility, I am reluctant to close down this behavior for other authentication schemes. But since NATIVE authentication has not gone GA yet, we have the opportunity to make it behave better. More exactly, this patch enforces the restriction that only the DBO can turn on NATIVE authentication. We can relax this restriction in the future if someone comes up with a supporting use case. However, right now this looks like a security hole to me. Touches the following files: ----------- M java/engine/org/apache/derby/catalog/SystemProcedures.java The new check to let only the DBO turn on NATIVE authentication. ----------- M java/engine/org/apache/derby/loc/messages.xml Slight rewording of an existing error message so that is can be re-used for this check. ----------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthenticationServiceTest.java New test case to verify the new behavior.
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly for me. Committed derby-866-24-aa-dboMustTurnOnSecurity.diff at subversion revision 1328431.

          Show
          Rick Hillegas added a comment - Tests passed cleanly for me. Committed derby-866-24-aa-dboMustTurnOnSecurity.diff at subversion revision 1328431.
          Hide
          Rick Hillegas added a comment -

          Attaching new version of release note. This improves the instructions for how to migrate from BUILTIN to NATIVE authentication.

          Show
          Rick Hillegas added a comment - Attaching new version of release note. This improves the instructions for how to migrate from BUILTIN to NATIVE authentication.
          Hide
          Rick Hillegas added a comment -

          There are some outstanding issues still linked to this task. Some of those issues will be addressed for 10.9. The others deal with other topics which are outside the scope of this JIRA. I think it is safe to resolve this issue now. That will make its release note turn up in the 10.9 release notes.

          Show
          Rick Hillegas added a comment - There are some outstanding issues still linked to this task. Some of those issues will be addressed for 10.9. The others deal with other topics which are outside the scope of this JIRA. I think it is safe to resolve this issue now. That will make its release note turn up in the 10.9 release notes.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Francois Orsini
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development