Derby
  1. Derby
  2. DERBY-5522

Document the NATIVE authentication scheme.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Security

      Description

      We should document NATIVE authentication after we have implemented the changes described on DERBY-866. The documentation changes are described by the functional spec UserManagement.html attached to that issue.

      1. CreateNativeUsers.java
        4 kB
        Kim Haase
      2. CreateNativeUsers.java
        4 kB
        Kim Haase
      3. derby.log
        5 kB
        Kim Haase
      4. DERBY-5522-4.diff
        11 kB
        Kim Haase
      5. DERBY-5522-4.stat
        0.3 kB
        Kim Haase
      6. DERBY-5522-4.zip
        14 kB
        Kim Haase
      7. DERBY-5522-devguide.diff
        166 kB
        Kim Haase
      8. DERBY-5522-devguide.stat
        1 kB
        Kim Haase
      9. DERBY-5522-devguide.zip
        64 kB
        Kim Haase
      10. DERBY-5522-devguide-2.diff
        166 kB
        Kim Haase
      11. DERBY-5522-devguide-2.stat
        1 kB
        Kim Haase
      12. DERBY-5522-devguide-2.zip
        54 kB
        Kim Haase
      13. DERBY-5522-devguide-3.diff
        167 kB
        Kim Haase
      14. DERBY-5522-devguide-3.zip
        13 kB
        Kim Haase
      15. DERBY-5522-ref.diff
        38 kB
        Kim Haase
      16. DERBY-5522-ref.stat
        0.6 kB
        Kim Haase
      17. DERBY-5522-ref.zip
        40 kB
        Kim Haase
      18. DERBY-5522-ref-2.diff
        40 kB
        Kim Haase
      19. DERBY-5522-ref-2.zip
        22 kB
        Kim Haase
      20. NativeAuthenticationExample.java
        17 kB
        Kim Haase
      21. NativeAuthenticationExample.java
        17 kB
        Kim Haase
      22. NativeAuthenticationExample.java
        17 kB
        Rick Hillegas
      23. NativeAuthenticationExample.java
        16 kB
        Kim Haase
      24. NativeAuthenticationExample.java
        14 kB
        Rick Hillegas
      25. NativeAuthExampleClient1.java
        5 kB
        Kim Haase
      26. NativeAuthExampleClient2.java
        10 kB
        Kim Haase
      27. NativeAuthExampleEmbedded.java
        14 kB
        Kim Haase
      28. NativeAuthExampleEmbedded.java
        14 kB
        Kim Haase
      29. NativeAuthExampleEmbedded.java
        14 kB
        Kim Haase
      30. NativeAuthExampleEmbedded.java
        14 kB
        Kim Haase
      31. NativeAuthExampleEmbedded.java
        16 kB
        Kim Haase
      32. NativeAuthExampleEmbedded.java
        16 kB
        Kim Haase
      33. UseNativeUsers.java
        11 kB
        Kim Haase
      34. UseNativeUsers.java
        11 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          I'm putting the following exchange between me and Rick into the public record, since my questions don't seem to have been too stupid:

          On 3/7/12 7:00 AM, Kim Haase wrote:
          > On 03/07/12 08:45 AM, Rick Hillegas wrote:
          >> Hi Kim,
          >>
          >> Thanks for picking up this issue. Some comments inline...
          >>
          >> On 3/6/12 1:34 PM, Kim Haase wrote:
          >>> Hi, Rick,
          >>>
          >>> I'm starting to work on documenting native authentication. (Or should
          >>> I say NATIVE? I'm thinking of using NATIVE vs. BUILTIN rather than
          >>> native vs. built-in – would that make sense?)
          >> I've been using NATIVE instead of native for the same reason: it's more
          >> parallel to BUILTIN.
          >
          > Thanks, Rick. We've been calling it "built-in" except when referring explicitly to the keyword, but my idea is to change that.

          Sounds good.

          >
          >>>
          >>> Where does native authentication fit into the hierarchy of recommended
          >>> mechanisms? We've had this note inserted in various places –
          >> NATIVE authentication is intended to be a production-quality replacement
          >> for BUILTIN. The release note tells people how to upgrade from BUILTIN
          >> to NATIVE. Companies should be able to rely on NATIVE authentication and
          >> feel as secure as they do with LDAP.
          >>>
          >>> "Important: Derby's built-in authentication mechanism is suitable only
          >>> for development and testing purposes. It is strongly recommended that
          >>> production systems rely on an external directory service such as LDAP
          >>> or a user-defined class for authentication. It is also strongly
          >>> recommended that production systems protect network connections with
          >>> SSL/TLS."
          >>>
          >>> I'm guessing that for production we would still recommend LDAP over
          >>> the others? Would native be preferable to a user-defined class? How
          >>> likely are people to grow their own? Native would certainly be easier
          >>> to use. I notice that you recommend SSL/TLS with native, implying that
          >>> it might be used in production? Or is SSL/TLS just a good idea anyway?
          >> Here's how I see it:
          >>
          >> 1) NATIVE is the secure authentication mechanism which comes with Derby
          >> and works out of the box. It's great for standalone applications.
          >>
          >> 2) LDAP is good for departmental applications where users can be
          >> expected to have a company-wide identity. It simplifies identity
          >> management for enterprises: a user just needs one set of credentials in
          >> order to use many applications.
          >>
          >> 3) User-defined authentication provides a thin bridge to external
          >> authentication mechanisms other than LDAP. It's for companies and
          >> application suites which use something other than LDAP to define user
          >> identity across their departments and products.
          >>
          >> 4) BUILTIN authentication is a testing tool intended for Derby
          >> developers. It lets you declare a canned set of users for your tests.
          >> It's unfortunate that it escaped from the laboratory into the wild. I
          >> think that we should remove all mention of it from the user guides--but
          >> we probably want to clear that with the community.
          >
          > I think we need to warn people ahead of time – perhaps at 10.9 we can say that use of BUILTIN is deprecated and will no longer be documented at the next release, and then remove it at the 10.9 bugfix release.
          That sounds prudent.
          >
          > Would you actually remove support for BUILTIN itself at some future release? Or leave it in there for testing purposes?

          I think we have to leave it in for backward compatibility reasons and because our own tests rely on it. But it would be good to stop advertising it.

          Thanks,
          -Rick
          >
          >>
          >> In listing authentication mechanisms, I would lead with the
          >> functionality which comes with Derby (NATIVE) and then follow with the
          >> plugins (LDAP and user-supplied).
          >
          > Thanks – that's helpful.
          >
          >>
          >> About SSL/TLS: it's a good way to protect credentials from being sniffed
          >> in networked applications, regardless of the authentication mechanism.
          >>>
          >>> I'm guessing those user authentication/authorization examples Dag and
          >>> I worked on for the Developer's Guide should probably be rewritten to
          >>> use native authentication instead? Not providing examples of builtin
          >>> authentication should discourage people from using it.
          >> Yes please!
          >
          > OK!
          >
          > Kim
          >
          >>
          >> Thanks,
          >> -Rick
          >>>
          >>> Thanks for your thoughts!
          >>>
          >>> Kim

          Show
          Kim Haase added a comment - I'm putting the following exchange between me and Rick into the public record, since my questions don't seem to have been too stupid: On 3/7/12 7:00 AM, Kim Haase wrote: > On 03/07/12 08:45 AM, Rick Hillegas wrote: >> Hi Kim, >> >> Thanks for picking up this issue. Some comments inline... >> >> On 3/6/12 1:34 PM, Kim Haase wrote: >>> Hi, Rick, >>> >>> I'm starting to work on documenting native authentication. (Or should >>> I say NATIVE? I'm thinking of using NATIVE vs. BUILTIN rather than >>> native vs. built-in – would that make sense?) >> I've been using NATIVE instead of native for the same reason: it's more >> parallel to BUILTIN. > > Thanks, Rick. We've been calling it "built-in" except when referring explicitly to the keyword, but my idea is to change that. Sounds good. > >>> >>> Where does native authentication fit into the hierarchy of recommended >>> mechanisms? We've had this note inserted in various places – >> NATIVE authentication is intended to be a production-quality replacement >> for BUILTIN. The release note tells people how to upgrade from BUILTIN >> to NATIVE. Companies should be able to rely on NATIVE authentication and >> feel as secure as they do with LDAP. >>> >>> "Important: Derby's built-in authentication mechanism is suitable only >>> for development and testing purposes. It is strongly recommended that >>> production systems rely on an external directory service such as LDAP >>> or a user-defined class for authentication. It is also strongly >>> recommended that production systems protect network connections with >>> SSL/TLS." >>> >>> I'm guessing that for production we would still recommend LDAP over >>> the others? Would native be preferable to a user-defined class? How >>> likely are people to grow their own? Native would certainly be easier >>> to use. I notice that you recommend SSL/TLS with native, implying that >>> it might be used in production? Or is SSL/TLS just a good idea anyway? >> Here's how I see it: >> >> 1) NATIVE is the secure authentication mechanism which comes with Derby >> and works out of the box. It's great for standalone applications. >> >> 2) LDAP is good for departmental applications where users can be >> expected to have a company-wide identity. It simplifies identity >> management for enterprises: a user just needs one set of credentials in >> order to use many applications. >> >> 3) User-defined authentication provides a thin bridge to external >> authentication mechanisms other than LDAP. It's for companies and >> application suites which use something other than LDAP to define user >> identity across their departments and products. >> >> 4) BUILTIN authentication is a testing tool intended for Derby >> developers. It lets you declare a canned set of users for your tests. >> It's unfortunate that it escaped from the laboratory into the wild. I >> think that we should remove all mention of it from the user guides--but >> we probably want to clear that with the community. > > I think we need to warn people ahead of time – perhaps at 10.9 we can say that use of BUILTIN is deprecated and will no longer be documented at the next release, and then remove it at the 10.9 bugfix release. That sounds prudent. > > Would you actually remove support for BUILTIN itself at some future release? Or leave it in there for testing purposes? I think we have to leave it in for backward compatibility reasons and because our own tests rely on it. But it would be good to stop advertising it. Thanks, -Rick > >> >> In listing authentication mechanisms, I would lead with the >> functionality which comes with Derby (NATIVE) and then follow with the >> plugins (LDAP and user-supplied). > > Thanks – that's helpful. > >> >> About SSL/TLS: it's a good way to protect credentials from being sniffed >> in networked applications, regardless of the authentication mechanism. >>> >>> I'm guessing those user authentication/authorization examples Dag and >>> I worked on for the Developer's Guide should probably be rewritten to >>> use native authentication instead? Not providing examples of builtin >>> authentication should discourage people from using it. >> Yes please! > > OK! > > Kim > >> >> Thanks, >> -Rick >>> >>> Thanks for your thoughts! >>> >>> Kim
          Hide
          Kim Haase added a comment -

          Okay, now it's time for stupid questions.

          I thought I would start by trying out the feature (best way to learn how it works), so I modified the SQL authorization embedded example we already have. I'm running into a bit of a catch-22, however. At first I tried setting the authentication provider to NATIVE::LOCAL before I created any users –

          java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Turning on NATIVE authentication.

          --SQLException Caught--

          SQLState: XCY05
          Severity: 30000
          Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed. NATIVE::LOCAL is the only NATIVE value accepted by derby.authentication.provider. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the syscs_util.syscs_create_user procedure.

          I was confused by the first part of this because I hadn't set the property previously, but I figured I had to create the users first and then set it. But I still get the same error:

          java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Storing some sample users in the database.
          Turning on NATIVE authentication.

          --SQLException Caught--

          SQLState: XCY05
          Severity: 30000
          Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed. NATIVE::LOCAL is the only NATIVE value accepted by derby.authentication.provider. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the syscs_util.syscs_create_user procedure.

          I thought I was following Bootstrapping method 3, "The database already exists and the DBO calls syscs_util.syscs_set_database_property('derby.authentication.provider', 'NATIVE::LOCAL' )."

          Maybe it should exist, but there shouldn't be an active connection to it, because the default provider is still BUILTIN, and what I'm really doing is changing it from BUILTIN to NATIVE? (Which ought to be possible, I should think?) But the only way I can call statements is with an active connection. I'm perplexed.

          Show
          Kim Haase added a comment - Okay, now it's time for stupid questions. I thought I would start by trying out the feature (best way to learn how it works), so I modified the SQL authorization embedded example we already have. I'm running into a bit of a catch-22, however. At first I tried setting the authentication provider to NATIVE::LOCAL before I created any users – java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true Turning on NATIVE authentication. -- SQLException Caught -- SQLState: XCY05 Severity: 30000 Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed. NATIVE::LOCAL is the only NATIVE value accepted by derby.authentication.provider. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the syscs_util.syscs_create_user procedure. I was confused by the first part of this because I hadn't set the property previously, but I figured I had to create the users first and then set it. But I still get the same error: java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true Storing some sample users in the database. Turning on NATIVE authentication. -- SQLException Caught -- SQLState: XCY05 Severity: 30000 Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed. NATIVE::LOCAL is the only NATIVE value accepted by derby.authentication.provider. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the syscs_util.syscs_create_user procedure. I thought I was following Bootstrapping method 3, "The database already exists and the DBO calls syscs_util.syscs_set_database_property('derby.authentication.provider', 'NATIVE::LOCAL' )." Maybe it should exist, but there shouldn't be an active connection to it, because the default provider is still BUILTIN, and what I'm really doing is changing it from BUILTIN to NATIVE? (Which ought to be possible, I should think?) But the only way I can call statements is with an active connection. I'm perplexed.
          Hide
          Knut Anders Hatlen added a comment -

          Hi Kim,

          I think the user names are stored in upper case internally, and the call to SYSCS_CREATE_USER needs to be passed 'MARY' instead of 'mary' to match the user name of the DBO. Alternatively, the user attribute in the connection URL can be changed from mary (unquoted) to "mary" (quoted).

          We have a wiki page that describes this behaviour: http://wiki.apache.org/db-derby/UserIdentifiers (not updated for NATIVE yet).

          Confusing? No...

          Show
          Knut Anders Hatlen added a comment - Hi Kim, I think the user names are stored in upper case internally, and the call to SYSCS_CREATE_USER needs to be passed 'MARY' instead of 'mary' to match the user name of the DBO. Alternatively, the user attribute in the connection URL can be changed from mary (unquoted) to "mary" (quoted). We have a wiki page that describes this behaviour: http://wiki.apache.org/db-derby/UserIdentifiers (not updated for NATIVE yet). Confusing? No...
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          What Knut says. Credentials have been stored for user "mary" but not for MARY, the DBO. Native authentication can't be turned on until credentials are stored for MARY. I think that this will confuse users of NATIVE authentication just as it confuses users of BUILTIN authentication. This probably indicates that the NATIVE documentation should say something about the casing of Derby usernames.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, What Knut says. Credentials have been stored for user "mary" but not for MARY, the DBO. Native authentication can't be turned on until credentials are stored for MARY. I think that this will confuse users of NATIVE authentication just as it confuses users of BUILTIN authentication. This probably indicates that the NATIVE documentation should say something about the casing of Derby usernames. Thanks, -Rick
          Hide
          Kim Haase added a comment -

          Thanks! I was just following the example in the spec:

          CallableStatement cs = conn.prepareCall( "call syscs_util.syscs_create_user( 'fred', 'fredpassword' )" );
          cs.execute();

          And with BUILTIN you can specify the derby.user names in lowercase. So this is a change.

          I'll try with uppercase and make sure it works.

          Show
          Kim Haase added a comment - Thanks! I was just following the example in the spec: CallableStatement cs = conn.prepareCall( "call syscs_util.syscs_create_user( 'fred', 'fredpassword' )" ); cs.execute(); And with BUILTIN you can specify the derby.user names in lowercase. So this is a change. I'll try with uppercase and make sure it works.
          Hide
          Kim Haase added a comment -

          Yup, just changing the names in the SYSCS_UTIL.SYSCS_CREATE_USER call – nowhere else – causes the program to work fine:

          java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true
          Storing some sample users in the database.
          Turning on NATIVE authentication.
          Value of requireAuthentication is null
          Value of sqlAuthorization is null
          Value of defaultConnectionMode is noAccess
          Value of fullAccessUsers is sqlsam,mary
          Value of readOnlyAccessUsers is guest
          Closed connection
          Database shut down normally
          Trying to connect to jdbc:derby:nativeAuthEmbDB without username or password
          Correct behavior: SQLException: Database connection refused.
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=noacc;password=ajaxj3x9
          Correct behavior: SQLException: Database connection refused.
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=guest;password=java5w6x
          Connected to database nativeAuthEmbDB with read-only access
          Correct behavior: SQLException: DDL is not permitted for a read-only connection, user or database.
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;password=little7xylamb
          Connected to database nativeAuthEmbDB
          Created table accessibletbl
          Value of accessibletbl/textcol is hello
          Granted select/insert privileges to sqlsam
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sqlsam;password=light8q9bulb
          Connected to database nativeAuthEmbDB
          Value of accessibletbl/textcol is hello
          Inserted string into table
          Value of accessibletbl/textcol is hello
          Value of accessibletbl/textcol is sam
          Correct behavior: SQLException: User 'SQLSAM' does not have DELETE permission on table 'MARY'.'ACCESSIBLETBL'.
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;password=little7xylamb
          Connected to database nativeAuthEmbDB
          Removed table accessibletbl
          Closed connection
          Database shut down normally
          Derby system shut down normally

          The only oddity is that turning on NATIVE auth is supposed to automatically set requireAuthentication and sqlAuthorization to true, but when I retrieve the values of these properties after turning on NATIVE auth, the program output indicates that they are not set.

          BTW, it is helpful to learn that the readOnlyAccessUsers and fullAccessUsers properties work just the same with NATIVE as with BUILTIN.

          Show
          Kim Haase added a comment - Yup, just changing the names in the SYSCS_UTIL.SYSCS_CREATE_USER call – nowhere else – causes the program to work fine: java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=mary;create=true Storing some sample users in the database. Turning on NATIVE authentication. Value of requireAuthentication is null Value of sqlAuthorization is null Value of defaultConnectionMode is noAccess Value of fullAccessUsers is sqlsam,mary Value of readOnlyAccessUsers is guest Closed connection Database shut down normally Trying to connect to jdbc:derby:nativeAuthEmbDB without username or password Correct behavior: SQLException: Database connection refused. Trying to connect to jdbc:derby:nativeAuthEmbDB;user=noacc;password=ajaxj3x9 Correct behavior: SQLException: Database connection refused. Trying to connect to jdbc:derby:nativeAuthEmbDB;user=guest;password=java5w6x Connected to database nativeAuthEmbDB with read-only access Correct behavior: SQLException: DDL is not permitted for a read-only connection, user or database. Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;password=little7xylamb Connected to database nativeAuthEmbDB Created table accessibletbl Value of accessibletbl/textcol is hello Granted select/insert privileges to sqlsam Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sqlsam;password=light8q9bulb Connected to database nativeAuthEmbDB Value of accessibletbl/textcol is hello Inserted string into table Value of accessibletbl/textcol is hello Value of accessibletbl/textcol is sam Correct behavior: SQLException: User 'SQLSAM' does not have DELETE permission on table 'MARY'.'ACCESSIBLETBL'. Trying to connect to jdbc:derby:nativeAuthEmbDB;user=mary;password=little7xylamb Connected to database nativeAuthEmbDB Removed table accessibletbl Closed connection Database shut down normally Derby system shut down normally The only oddity is that turning on NATIVE auth is supposed to automatically set requireAuthentication and sqlAuthorization to true, but when I retrieve the values of these properties after turning on NATIVE auth, the program output indicates that they are not set. BTW, it is helpful to learn that the readOnlyAccessUsers and fullAccessUsers properties work just the same with NATIVE as with BUILTIN.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          Thanks for running these experiments. The spec should say that Derby behaves as though requireAuthentication and sqlAuthorization are set to true. They are not actually set in the database.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, Thanks for running these experiments. The spec should say that Derby behaves as though requireAuthentication and sqlAuthorization are set to true. They are not actually set in the database. Thanks, -Rick
          Hide
          Kim Haase added a comment -

          Thanks for that clarification about requireAuthentication and sqlAuthorization, Rick.

          I'm moving on to creating a dedicated credentials database using derby.properties. It worked fine. When I created the credentials DB and added some users, I also set the default connection mode to noAccess and specified some users as full access and others as read only access. Would this be customary when you create the database?

          Show
          Kim Haase added a comment - Thanks for that clarification about requireAuthentication and sqlAuthorization, Rick. I'm moving on to creating a dedicated credentials database using derby.properties. It worked fine. When I created the credentials DB and added some users, I also set the default connection mode to noAccess and specified some users as full access and others as read only access. Would this be customary when you create the database?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I would consider this to be edge-case behavior. I regard the connection access mode as a poor-man's substitute for SQL authorization. I believe it was added because Derby did not support SQL authorization at the time. I suppose it lets you deploy an application which has a class of users who can't write any data at all, not even in their own schemas. I imagine that kind of restriction is unusual. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, I would consider this to be edge-case behavior. I regard the connection access mode as a poor-man's substitute for SQL authorization. I believe it was added because Derby did not support SQL authorization at the time. I suppose it lets you deploy an application which has a class of users who can't write any data at all, not even in their own schemas. I imagine that kind of restriction is unusual. Thanks.
          Hide
          Kim Haase added a comment -

          Great, so all those access properties can be scrapped along with the rest of the builtin stuff. I will use roles and grant statements only then. I guess we should not have left the old properties in the SQL authorization examples.

          Should they also be listed as deprecated and not to be documented in the future?

          So would you normally set up roles in the credentials db, or only in the clients that used it? I suppose you might want to grant different access to different users for different applications, so doing it in the clients would give you greater flexibility?

          Show
          Kim Haase added a comment - Great, so all those access properties can be scrapped along with the rest of the builtin stuff. I will use roles and grant statements only then. I guess we should not have left the old properties in the SQL authorization examples. Should they also be listed as deprecated and not to be documented in the future? So would you normally set up roles in the credentials db, or only in the clients that used it? I suppose you might want to grant different access to different users for different applications, so doing it in the clients would give you greater flexibility?
          Hide
          Kim Haase added a comment -

          Hm, this is odd. I turn on NATIVE authentication and then try to create some roles:

          // Set authentication scheme to Derby NATIVE
          System.out.println(
          "Turning on NATIVE authentication.");
          s.executeUpdate(setProperty + provider + ", 'NATIVE::LOCAL')");

          s.executeUpdate("CREATE ROLE ADDER");
          s.executeUpdate("CREATE ROLE USER");
          s.executeUpdate("GRANT ADDER TO sqlsam");
          s.executeUpdate("GRANT USER TO guest, mary");

          But I get this error:

          java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded
          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Storing some sample users in the database.
          Turning on NATIVE authentication.

          --SQLException Caught--

          SQLState: 42Z60
          Severity: 30000
          Message: CREATE ROLE not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'.

          So apparently behaving "as if" derby.database.sqlAuthorization is set isn't good enough for CREATE ROLE?

          Show
          Kim Haase added a comment - Hm, this is odd. I turn on NATIVE authentication and then try to create some roles: // Set authentication scheme to Derby NATIVE System.out.println( "Turning on NATIVE authentication."); s.executeUpdate(setProperty + provider + ", 'NATIVE::LOCAL')"); s.executeUpdate("CREATE ROLE ADDER"); s.executeUpdate("CREATE ROLE USER"); s.executeUpdate("GRANT ADDER TO sqlsam"); s.executeUpdate("GRANT USER TO guest, mary"); But I get this error: java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. NativeAuthExampleEmbedded Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Storing some sample users in the database. Turning on NATIVE authentication. -- SQLException Caught -- SQLState: 42Z60 Severity: 30000 Message: CREATE ROLE not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'. So apparently behaving "as if" derby.database.sqlAuthorization is set isn't good enough for CREATE ROLE?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          Thanks for continuing to think about how NATIVE authentication interacts with other Derby security features.

          >Should they also be listed as deprecated and not to be documented in the future?

          We probably want to discuss that with the broader community and understand who (if anyone) is still using these features.

          >So would you normally set up roles in the credentials db, or only in the clients that used it? I suppose you might want to grant different access to different users for different applications, so doing it in the clients would give you greater flexibility?

          You would set up roles in each database. That's an interesting asymmetry between Derby authentication and authorization. Authentication can be system-wide but authorization is always database-specific. We have talked a little about system-wide authorization but only in the context of system-wide privileges which are not addressed by the SQL Standard (e.g., database creation and
          engine shutdown).

          >So apparently behaving "as if" derby.database.sqlAuthorization is set isn't good enough for CREATE ROLE?

          I am unable to reproduce this behavior. Is it possible that you didn't reboot the database after turning on NATIVE authentication but before trying to create a role? I don't think that the functional spec touched this point: the derby.authentication.provider property continues to be one of the properties which doesn't take effect until you bounce the database.

          If you did bounce the database, then I don't know what's happening. Could you attach the latest version of your test program so that I can look into this one?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, Thanks for continuing to think about how NATIVE authentication interacts with other Derby security features. >Should they also be listed as deprecated and not to be documented in the future? We probably want to discuss that with the broader community and understand who (if anyone) is still using these features. >So would you normally set up roles in the credentials db, or only in the clients that used it? I suppose you might want to grant different access to different users for different applications, so doing it in the clients would give you greater flexibility? You would set up roles in each database. That's an interesting asymmetry between Derby authentication and authorization. Authentication can be system-wide but authorization is always database-specific. We have talked a little about system-wide authorization but only in the context of system-wide privileges which are not addressed by the SQL Standard (e.g., database creation and engine shutdown). >So apparently behaving "as if" derby.database.sqlAuthorization is set isn't good enough for CREATE ROLE? I am unable to reproduce this behavior. Is it possible that you didn't reboot the database after turning on NATIVE authentication but before trying to create a role? I don't think that the functional spec touched this point: the derby.authentication.provider property continues to be one of the properties which doesn't take effect until you bounce the database. If you did bounce the database, then I don't know what's happening. Could you attach the latest version of your test program so that I can look into this one? Thanks, -Rick
          Hide
          Kim Haase added a comment -

          Yes, silly me, I created the roles without first rebooting the database, even though there was a comment in the source reminding me that this needed to be done.

          So I did that, but now I am getting odd results.

          I find that now that defaultConnectionMode is not set, if I try to log in to the database as a user who has no assigned role, I can do it. What are the default privileges for a user? Everything, apparently?

          Also, when I log in as a user whose role was granted only SELECT privileges on a table, I find that I can create the table all over again.

          I'm guessing I still don't understand the uppercase/lowercase thing? Here is the latest version.

          Show
          Kim Haase added a comment - Yes, silly me, I created the roles without first rebooting the database, even though there was a comment in the source reminding me that this needed to be done. So I did that, but now I am getting odd results. I find that now that defaultConnectionMode is not set, if I try to log in to the database as a user who has no assigned role, I can do it. What are the default privileges for a user? Everything, apparently? Also, when I log in as a user whose role was granted only SELECT privileges on a table, I find that I can create the table all over again. I'm guessing I still don't understand the uppercase/lowercase thing? Here is the latest version.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kim, for running these additional experiments and for attaching the latest version of the test program.

          >I find that now that defaultConnectionMode is not set, if I try to log in to the database as a user who has no assigned role, I can do it. What are the default privileges for a user? Everything, apparently?

          The Reference Guide says that fullAccess is the default value of derby.database.defaultConnectionMode.

          >Also, when I log in as a user whose role was granted only SELECT privileges on a table, I find that I can create the table all over again.

          Maybe the confusion arises because user GUEST is creating a table named ACCESSIBLETBL? That table is being created in the GUEST schema and is different from SYSADMIN.ACCESSIBLETBL, the table which is the object of the GRANT statements.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks, Kim, for running these additional experiments and for attaching the latest version of the test program. >I find that now that defaultConnectionMode is not set, if I try to log in to the database as a user who has no assigned role, I can do it. What are the default privileges for a user? Everything, apparently? The Reference Guide says that fullAccess is the default value of derby.database.defaultConnectionMode. >Also, when I log in as a user whose role was granted only SELECT privileges on a table, I find that I can create the table all over again. Maybe the confusion arises because user GUEST is creating a table named ACCESSIBLETBL? That table is being created in the GUEST schema and is different from SYSADMIN.ACCESSIBLETBL, the table which is the object of the GRANT statements. Thanks, -Rick
          Hide
          Kim Haase added a comment -

          Ouch, I really needed a refresher course on SQL authorization. Now that I'm using SET ROLE properly, it works as expected.

          I dropped the no-access user because, as you said, it doesn't illustrate anything useful (users can do anything within their own schemas).

          Show
          Kim Haase added a comment - Ouch, I really needed a refresher course on SQL authorization. Now that I'm using SET ROLE properly, it works as expected. I dropped the no-access user because, as you said, it doesn't illustrate anything useful (users can do anything within their own schemas).
          Hide
          Kim Haase added a comment -

          A question about the various authorization properties mentioned in http://db.apache.org/derby/docs/dev/devguide/cdevcsecure36595.html: would any of them make sense with LDAP or a user-defined class, or only with BUILTIN authentication?

          I was guessing that derby.database.sqlAuthorization might apply to all of them but that derby.database.defaultConnectionMode, derby.database.fullAccessUsers, and derby.database.readOnlyAccessUsers might apply only to BUILTIN. Given my usual track record, though, I'd better not guess.

          Show
          Kim Haase added a comment - A question about the various authorization properties mentioned in http://db.apache.org/derby/docs/dev/devguide/cdevcsecure36595.html: would any of them make sense with LDAP or a user-defined class, or only with BUILTIN authentication? I was guessing that derby.database.sqlAuthorization might apply to all of them but that derby.database.defaultConnectionMode, derby.database.fullAccessUsers, and derby.database.readOnlyAccessUsers might apply only to BUILTIN. Given my usual track record, though, I'd better not guess.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          In general, authorization is not affected by the kind of authentication mechanism which Derby uses. The same authorization options are available regardless of whether you use LDAP, custom, or BUILTIN.

          NATIVE authentication slightly complicates this situation because NATIVE authentication automatically turns on SQL authorization (and makes it impossible to turn off SQL authorization).

          I believe you are talking about the properties which configure the old-style authorization scheme which Derby used before SQL authorization was introduced. Those properties can be used with LDAP, custom, BUILTIN, and NATIVE authentication.

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, In general, authorization is not affected by the kind of authentication mechanism which Derby uses. The same authorization options are available regardless of whether you use LDAP, custom, or BUILTIN. NATIVE authentication slightly complicates this situation because NATIVE authentication automatically turns on SQL authorization (and makes it impossible to turn off SQL authorization). I believe you are talking about the properties which configure the old-style authorization scheme which Derby used before SQL authorization was introduced. Those properties can be used with LDAP, custom, BUILTIN, and NATIVE authentication. Hope this helps, -Rick
          Hide
          Kim Haase added a comment -

          Actually, it is impossible to turn off SQL authorization once it has been turned on, regardless of which authentication scheme is being used – according to http://db.apache.org/derby/docs/dev/devguide/cdevcsecure866060.html.

          Thanks for the clarification on what works and what doesn't. You had implied, I think, that the defaultConnectionMode (at least) is old and should not be used with SQL authorization – but that, I guess, is just a usage recommendation.

          Show
          Kim Haase added a comment - Actually, it is impossible to turn off SQL authorization once it has been turned on, regardless of which authentication scheme is being used – according to http://db.apache.org/derby/docs/dev/devguide/cdevcsecure866060.html . Thanks for the clarification on what works and what doesn't. You had implied, I think, that the defaultConnectionMode (at least) is old and should not be used with SQL authorization – but that, I guess, is just a usage recommendation.
          Hide
          Kim Haase added a comment -

          Attaching the working examples that use NATIVE authentication by setting database properties.

          Show
          Kim Haase added a comment - Attaching the working examples that use NATIVE authentication by setting database properties.
          Hide
          Kim Haase added a comment -

          If you can see ways to improve the database-properties version of the NATIVE authentication programs, that would be great.

          In the meantime, I am trying to do something similar by using system properties and a separate credentials DB, and I am totally perplexed. Probably this is yet another stupid user error ...

          I created derby.properties with the following contents:

          derby.authentication.provider=NATIVE:credsDB

          I can run CreateNativeUsers just fine to create the credentials DB. But when I then try to run UseNativeUsers, I get the following error:

          jdench 275 =>java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. UseNativeUsers
          Trying to connect to jdbc:derby:nativeAuthDB;user=sysadm;password=i2have3power;create=true

          --SQLException Caught--

          SQLState: XJ004
          Severity: 40000
          Message: Database 'nativeAuthDB' not found.

          I'm trying to create it, so this seems like a strange error.

          I've attached the source files. Thanks for any help.

          Show
          Kim Haase added a comment - If you can see ways to improve the database-properties version of the NATIVE authentication programs, that would be great. In the meantime, I am trying to do something similar by using system properties and a separate credentials DB, and I am totally perplexed. Probably this is yet another stupid user error ... I created derby.properties with the following contents: derby.authentication.provider=NATIVE:credsDB I can run CreateNativeUsers just fine to create the credentials DB. But when I then try to run UseNativeUsers, I get the following error: jdench 275 =>java -cp /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derby.jar:. UseNativeUsers Trying to connect to jdbc:derby:nativeAuthDB;user=sysadm;password=i2have3power;create=true -- SQLException Caught -- SQLState: XJ004 Severity: 40000 Message: Database 'nativeAuthDB' not found. I'm trying to create it, so this seems like a strange error. I've attached the source files. Thanks for any help.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I can reproduce the problem you are seeing. The actual connection url you are passing to DriverManager is

          jdbc:derby:nativeAuthDB

          I think that you want the connection url to look like this:

          jdbc:derby:nativeAuthDB;user=sysadm;password=i2have3power;create=true

          The trailing attributes on that connection url appear in your printout, but they are just appended by your println() call. They don't actually appear in the connection url passed to DriverManager.

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, I can reproduce the problem you are seeing. The actual connection url you are passing to DriverManager is jdbc:derby:nativeAuthDB I think that you want the connection url to look like this: jdbc:derby:nativeAuthDB;user=sysadm;password=i2have3power;create=true The trailing attributes on that connection url appear in your printout, but they are just appended by your println() call. They don't actually appear in the connection url passed to DriverManager. Hope this helps, -Rick
          Hide
          Kim Haase added a comment -

          Thanks, Rick! I knew it was something stupid. After fixing some more items, I'm attaching a pair of programs that I can get to work in both embedded and client mode by changing one assignment near the top. Had to add a condition at the end for shutdown. Let me know if these could use improvements.

          Show
          Kim Haase added a comment - Thanks, Rick! I knew it was something stupid. After fixing some more items, I'm attaching a pair of programs that I can get to work in both embedded and client mode by changing one assignment near the top. Had to add a condition at the end for shutdown. Let me know if these could use improvements.
          Hide
          Kim Haase added a comment -

          Attaching the first chunk of work on this issue, with the changes to the Developer's Guide, in DERBY-5522-devguide.diff, DERBY-5522-devguide.stat, and DERBY-5522-devguide.zip. The changes are listed below, in the order in which they appear in the manual. I'll move on to the Reference Manual topics, which I've barely begun. I'll be grateful for all comments!

          Scope of properties (cdevsetprop824451.dita): changed BUILTIN note

          Configuring security in a client/server environment (tdevcsecure82556.dita): changed step 3 (added NATIVE; changed BUILTIN note)

          Configuring security in an embedded environment (tdevcsecure81850.dita): changed step 5 (changed BUILTIN note)

          Working with user authentication (cdevcsecure42374.dita): added info about NATIVE, changed BUILTIN note

          Using NATIVE authentication (cdevcsecurenativeauth.dita): new topic

          Enabling user authentication (cdevcsecure36127.dita): modified to mention NATIVE first

          Defining users (cdevcsecure37817.dita): added bullet for NATIVE

          BUILTIN Derby users (cdevcsecure21547.dita): changed BUILTIN term, note

          List of user authentication properties (rdevcsecure557.dita): added new properties, xref to ref man, changed BUILTIN note

          Authorization identifiers, user authentication, and user authorization (cdevcsecure24458.dita): replaced examples of derby.fullAccessUsers property with examples of SYSCS_UTIL.SYSCS_CREATE_USER

          Database owner (cdevcsecureDbOwner.dita): added mention of NATIVE

          User authorizations (cdevcsecure36595.dita): added info that some properties are not relevant if using NATIVE; reorder subtopics to put "Setting the SQL standard authorization mode" first

          Setting the SQL standard authorization mode (cdevcsecure866060.dita): mentioned that SQL auth is automatic with NATIVE

          Setting the default connection access mode (cdevcsecure865818.dita): mentioned that this mode is not usually needed if you use SQL authorization

          Setting access for individual users (cdevcsecure865880.dita): mentioned that this is not usually needed if you use SQL authorization

          User authentication and authorization examples (cdevcsecure51876.dita): corrected syntax

          User authentication example in a client/server environment (rdevcsecure125.dita): Renamed to "Setting LDAP user authentication properties in a client/server environment", moved to after the SQL authorization examples

          User authentication and authorization client example (rdevcsecureclientexample.dita): removed; uses BUILTIN

          User authentication example in a single-user, embedded environment (rdevcsecure13713.dita): removed; uses BUILTIN

          User authentication and authorization embedded example (rdevcsecure26537.dita): removed; uses BUILTIN

          User authentication examples using SQL authorization (rdevcsecuresqlauthexs.dita): moved to top of section, rewrote to mention NATIVE

          User authentication and SQL authorization client example using database properties (rdevcsecuresqlauthclientex.dita): rewrote to use NATIVE

          User authentication and SQL authorization embedded example using database properties (rdevcsecuresqlauthembeddedex.dita): rewrote to use NATIVE

          User authentication and SQL authorization example using system properties (rdevcsecuresqlauthsyspropex.dita): new topic

          Show
          Kim Haase added a comment - Attaching the first chunk of work on this issue, with the changes to the Developer's Guide, in DERBY-5522 -devguide.diff, DERBY-5522 -devguide.stat, and DERBY-5522 -devguide.zip. The changes are listed below, in the order in which they appear in the manual. I'll move on to the Reference Manual topics, which I've barely begun. I'll be grateful for all comments! Scope of properties (cdevsetprop824451.dita): changed BUILTIN note Configuring security in a client/server environment (tdevcsecure82556.dita): changed step 3 (added NATIVE; changed BUILTIN note) Configuring security in an embedded environment (tdevcsecure81850.dita): changed step 5 (changed BUILTIN note) Working with user authentication (cdevcsecure42374.dita): added info about NATIVE, changed BUILTIN note Using NATIVE authentication (cdevcsecurenativeauth.dita): new topic Enabling user authentication (cdevcsecure36127.dita): modified to mention NATIVE first Defining users (cdevcsecure37817.dita): added bullet for NATIVE BUILTIN Derby users (cdevcsecure21547.dita): changed BUILTIN term, note List of user authentication properties (rdevcsecure557.dita): added new properties, xref to ref man, changed BUILTIN note Authorization identifiers, user authentication, and user authorization (cdevcsecure24458.dita): replaced examples of derby.fullAccessUsers property with examples of SYSCS_UTIL.SYSCS_CREATE_USER Database owner (cdevcsecureDbOwner.dita): added mention of NATIVE User authorizations (cdevcsecure36595.dita): added info that some properties are not relevant if using NATIVE; reorder subtopics to put "Setting the SQL standard authorization mode" first Setting the SQL standard authorization mode (cdevcsecure866060.dita): mentioned that SQL auth is automatic with NATIVE Setting the default connection access mode (cdevcsecure865818.dita): mentioned that this mode is not usually needed if you use SQL authorization Setting access for individual users (cdevcsecure865880.dita): mentioned that this is not usually needed if you use SQL authorization User authentication and authorization examples (cdevcsecure51876.dita): corrected syntax User authentication example in a client/server environment (rdevcsecure125.dita): Renamed to "Setting LDAP user authentication properties in a client/server environment", moved to after the SQL authorization examples User authentication and authorization client example (rdevcsecureclientexample.dita): removed; uses BUILTIN User authentication example in a single-user, embedded environment (rdevcsecure13713.dita): removed; uses BUILTIN User authentication and authorization embedded example (rdevcsecure26537.dita): removed; uses BUILTIN User authentication examples using SQL authorization (rdevcsecuresqlauthexs.dita): moved to top of section, rewrote to mention NATIVE User authentication and SQL authorization client example using database properties (rdevcsecuresqlauthclientex.dita): rewrote to use NATIVE User authentication and SQL authorization embedded example using database properties (rdevcsecuresqlauthembeddedex.dita): rewrote to use NATIVE User authentication and SQL authorization example using system properties (rdevcsecuresqlauthsyspropex.dita): new topic
          Hide
          Rick Hillegas added a comment -

          Thanks for this first tranche of changes, Kim. I can see that this is a big effort indeed. On the whole, these changes look great to me.

          I now understand why you asked so many questions about the sample programs. I see that the programs originated in the docs examples. Now that I see that the programs are going to be exposed, I think that we should overhaul them. They don't showcase how easy Derby is to use--and how much easier authentication has become with the new NATIVE mechanism. If you like, I would be happy to rewrite these examples to try to showcase how simple Derby security is becoming. We just need to agree on what points the examples should punch up.

          cdevcsecure36127:

          I stumbled over the first paragraph. I would be tempted to reword it slightly:

          "If you use NATIVE authentication, then you do not need to set the derby.connection.requireAuthentication property. When creating a database with NATIVE authentication, simply specify a username and password--that user becomes the database owner."

          cdevcsecure36595:

          I would recommend some changes to the pre-existing wording. In general, I don't think that the term "user authorizations" conveys any more meaning than the simpler term "authorizations". On the contrary, I think it is easy to be confused by the term "user authorizations" and to suppose that it is yet another kind of authorization scheme in addition to "connection authorizations" and "SQL authorizations". There are only 2 kinds of authorizations and I find that the qualifiers "coarse-grained" and "fine-grained" help me tease them apart.

          o Paragraph 2 under "User authorizations":

          "Connection authorization specifies the access" -> "Connection authorization specifies the coarse-grained access"

          "SQL authorization controls the permissions" -> "SQL authorization controls the fine-grained permissions"

          o Paragraph 4 under "User authorizations": Coarse-grained connection authorization and fine-grained SQL authorization are independent of one another. You can enable both of them if you want to. NATIVE authentication does not affect this independence. If you turn on NATIVE authentication, then you can also turn on coarse-grained connection authorization. I have verified this. Here is an attempt to reword this paragraph:

          "Attention: If you use NATIVE authentication, then fine-grained SQL authorization is automatically enabled, and by default, all users enjoy full coarse-grained access to the database. In this situation, fine-grained SQL authorization can't be turned off. However, you can still adjust coarse-grained access to the database."

          o Paragraph 3 under "User authorization properties": The html output is missing the blank line which I think should introduce the new paragraph that begins with the sentence "The following properties affect authorization:"

          o Paragraph 4 under "User authorization properties": This paragraph really confuses me. I think that it is supposed to be talking about coarse-grained connection authorizations, but it uses the ambiguous term "user authorizations". I recommend rewording it:

          "If you do not specify the coarse-grained connection authorizations for a specific user ID, then that user ID inherits the database's default coarse-grained connection authorization."

          o Bullet 3 under "How user authorization properties work together": The two authorization mechanisms do not override one another. They supplement or refine one another. A user's authorizations end up being the intersection of her coarse-grained and fine-grained permissions. I would reword the first sentence of this bullet as follows:

          "The coarse-grained access mode specified by the derby.database.defaultConnectionMode property supplements the permissions that re granted by the owner of a database object."

          cdevcsecure42374:

          One specific comment, then a general comment about the pre-existing wording.

          o Bullet 1: I would drop the sentence about robustness. I don't think we want to imply that LDAP or user-written mechanisms are less safe.

          The pre-existing wording seems a bit confused to me. It creates the impression that authentication controls access to the Derby engine and authorization controls access to individual databases. That's not how it works. Here's how I see it:

          1) Authentication determines whether you are a legal user. It establishes your identity.

          2) Authorization determines what operations can be performed by you, that is, by your Derby identity.

          So far, so good. Those are just generic definitions of authentication and authorization which hold true for lots of software, not just Derby. Now for the tricky bit, which is specific to Derby:

          Derby understands two kinds of identity:

          A) System-wide identity. Currently, any legal system-wide identity enjoys authorization to perform the following operations:

          i) Create databases.

          ii) Restore databases.

          iii) Shutdown the Derby engine.

          B) Database-specific identity. If you are a legal identity in a specific-database, then you may enjoy the following rights:

          i) You can connect to that database--provided that coarse-grained connection authorization has not been set to noAccess.

          ii) You can shutdown that database, encrypt it, and upgrade it--provided that you are the database owner.

          iii) You can create your own SQL objects and write data to your own tables--provided that your coarse-grained connection authorization has not be set to readOnlyAccess.

          iv) You can access other SQL objects--provided that the owners have granted you fine-grained SQL access to those objects and provided you have not been limited by coarse-grained readOnlyAccess.

          cdevcsecure51876:

          o Bullet 2: I was confused by multiple references to authorization in this sentence. I would reword it to something like this:

          "These examples show how to use fine-grained SQL authorization in conjunction with Derby's NATIVE user authentication."

          cdevcsecurenativeauth:

          o Paragraph 1 under "Using NATIVE authentication": Again, I would drop the claim that NATIVE authentication is safer than LDAP. Maybe something like this:

          "Derby's simplest authentication mechanism is NATIVE authentication."

          o Last paragraph under "Using NATIVE authentication": I would clarify when passwords are hashed. I would reword the first sentence of this paragraph:

          "Use the derby.authentication.builtin.algorithm property to change how passwords are encrypted when they are stored in SYS.SYSUSERS."

          o Last bullet under "Managing users and passwords": I would punch up the purpose of this procedure:

          "To change a password" -> "To change her own password"

          rdevcsecuresqlauthclientex:

          o Paragraph 3 under "User authentication and SQL authorization client example": I was confused by the last sentence of this paragraph. I couldn't figure out what "format" referred to.

          See my introductory comment about these examples: I see that these programs are pre-existing code, which you have reworked to use NATIVE authentication. I think it would be great if the examples could be simplified to highlight how easy it is to set up NATIVE authentication. In particular, because BUILTIN isn't being used anymore, there is no need to call syscs_set_database_property() at all. If the following property is set on the server...

          -Dderby.authentication.provider=NATIVE:nativeAuthClientDB:LOCAL

          ...then the following work will happen automagically:

          1) derby.authentication.provider=NATIVE::LOCAL will be set in the database.

          2) The SYSADM credentials will be loaded automatically.

          3) And there is no need to set databaseOnlyProperties. The database has been secured by NATIVE::LOCAL authentication so there is no way to subvert authentication now.

          I also think that we should remove the code which sets coarse-grained connection authorizations. I believe that this material should have been removed when we introduced fine-grained SQL authorizations in 10.2.

          Please be patient as we improve these examples. I think that this may take a couple iterations. As I said earlier, if you like, I can create a simpler example from scratch once we agree on what the example should show.

          rdevcsecuresqlauthembeddedex:

          Similar comments.

          rdevcsecuresqlauthsyspropex:

          I'm tempted to get rid of this page and just roll its material into the previous examples. Can you think of a reason why we need this extra page?

          tdevcsecure81850:

          o Item 4: Again, I think that we only need to recommend SQL authorization. I would reword the first sentence as follows:

          "To prevent unauthorized users from accessing databases once they are booted, turn on user authentication and SQL authorization for the database."

          o Item 5: I think that this page is about how to configure security in a production environment. I don't think that we should mention BUILTIN authentication at all since it is not secure. I would just reduce this item to the following. If we remove the bit about BUILTIN authentcation, then there's nothing left to this item except the advice to turn on SSL/TLS. But that doesn't make sense for an embedded scenario. So I would reduce this item to the following and move it to the next page (which deals with configuring network security):

          "It is also strongly recommended that production systems protect network connections with SSL/TLS."

          tdevcsecure82556:

          o Item 3: Again, let's not mention BUILTIN authentication. Strike the last two sentences of this item.

          o Item 4: This text assumes that you are using coarse-grained connection authorization rather than fine-grained SQL authorization. Again, I think that we should stop recommending the old-style, Derby-specific coarse-grained mechanism. Instead, we should recommend that people use portable SQL Standard features wherever possible. I would simplify this item as follows:

          "Configure SQL authorization for your databases."

          Show
          Rick Hillegas added a comment - Thanks for this first tranche of changes, Kim. I can see that this is a big effort indeed. On the whole, these changes look great to me. I now understand why you asked so many questions about the sample programs. I see that the programs originated in the docs examples. Now that I see that the programs are going to be exposed, I think that we should overhaul them. They don't showcase how easy Derby is to use--and how much easier authentication has become with the new NATIVE mechanism. If you like, I would be happy to rewrite these examples to try to showcase how simple Derby security is becoming. We just need to agree on what points the examples should punch up. cdevcsecure36127: I stumbled over the first paragraph. I would be tempted to reword it slightly: "If you use NATIVE authentication, then you do not need to set the derby.connection.requireAuthentication property. When creating a database with NATIVE authentication, simply specify a username and password--that user becomes the database owner." cdevcsecure36595: I would recommend some changes to the pre-existing wording. In general, I don't think that the term "user authorizations" conveys any more meaning than the simpler term "authorizations". On the contrary, I think it is easy to be confused by the term "user authorizations" and to suppose that it is yet another kind of authorization scheme in addition to "connection authorizations" and "SQL authorizations". There are only 2 kinds of authorizations and I find that the qualifiers "coarse-grained" and "fine-grained" help me tease them apart. o Paragraph 2 under "User authorizations": "Connection authorization specifies the access" -> "Connection authorization specifies the coarse-grained access" "SQL authorization controls the permissions" -> "SQL authorization controls the fine-grained permissions" o Paragraph 4 under "User authorizations": Coarse-grained connection authorization and fine-grained SQL authorization are independent of one another. You can enable both of them if you want to. NATIVE authentication does not affect this independence. If you turn on NATIVE authentication, then you can also turn on coarse-grained connection authorization. I have verified this. Here is an attempt to reword this paragraph: "Attention: If you use NATIVE authentication, then fine-grained SQL authorization is automatically enabled, and by default, all users enjoy full coarse-grained access to the database. In this situation, fine-grained SQL authorization can't be turned off. However, you can still adjust coarse-grained access to the database." o Paragraph 3 under "User authorization properties": The html output is missing the blank line which I think should introduce the new paragraph that begins with the sentence "The following properties affect authorization:" o Paragraph 4 under "User authorization properties": This paragraph really confuses me. I think that it is supposed to be talking about coarse-grained connection authorizations, but it uses the ambiguous term "user authorizations". I recommend rewording it: "If you do not specify the coarse-grained connection authorizations for a specific user ID, then that user ID inherits the database's default coarse-grained connection authorization." o Bullet 3 under "How user authorization properties work together": The two authorization mechanisms do not override one another. They supplement or refine one another. A user's authorizations end up being the intersection of her coarse-grained and fine-grained permissions. I would reword the first sentence of this bullet as follows: "The coarse-grained access mode specified by the derby.database.defaultConnectionMode property supplements the permissions that re granted by the owner of a database object." cdevcsecure42374: One specific comment, then a general comment about the pre-existing wording. o Bullet 1: I would drop the sentence about robustness. I don't think we want to imply that LDAP or user-written mechanisms are less safe. The pre-existing wording seems a bit confused to me. It creates the impression that authentication controls access to the Derby engine and authorization controls access to individual databases. That's not how it works. Here's how I see it: 1) Authentication determines whether you are a legal user. It establishes your identity. 2) Authorization determines what operations can be performed by you, that is, by your Derby identity. So far, so good. Those are just generic definitions of authentication and authorization which hold true for lots of software, not just Derby. Now for the tricky bit, which is specific to Derby: Derby understands two kinds of identity: A) System-wide identity. Currently, any legal system-wide identity enjoys authorization to perform the following operations: i) Create databases. ii) Restore databases. iii) Shutdown the Derby engine. B) Database-specific identity. If you are a legal identity in a specific-database, then you may enjoy the following rights: i) You can connect to that database--provided that coarse-grained connection authorization has not been set to noAccess. ii) You can shutdown that database, encrypt it, and upgrade it--provided that you are the database owner. iii) You can create your own SQL objects and write data to your own tables--provided that your coarse-grained connection authorization has not be set to readOnlyAccess. iv) You can access other SQL objects--provided that the owners have granted you fine-grained SQL access to those objects and provided you have not been limited by coarse-grained readOnlyAccess. cdevcsecure51876: o Bullet 2: I was confused by multiple references to authorization in this sentence. I would reword it to something like this: "These examples show how to use fine-grained SQL authorization in conjunction with Derby's NATIVE user authentication." cdevcsecurenativeauth: o Paragraph 1 under "Using NATIVE authentication": Again, I would drop the claim that NATIVE authentication is safer than LDAP. Maybe something like this: "Derby's simplest authentication mechanism is NATIVE authentication." o Last paragraph under "Using NATIVE authentication": I would clarify when passwords are hashed. I would reword the first sentence of this paragraph: "Use the derby.authentication.builtin.algorithm property to change how passwords are encrypted when they are stored in SYS.SYSUSERS." o Last bullet under "Managing users and passwords": I would punch up the purpose of this procedure: "To change a password" -> "To change her own password" rdevcsecuresqlauthclientex: o Paragraph 3 under "User authentication and SQL authorization client example": I was confused by the last sentence of this paragraph. I couldn't figure out what "format" referred to. See my introductory comment about these examples: I see that these programs are pre-existing code, which you have reworked to use NATIVE authentication. I think it would be great if the examples could be simplified to highlight how easy it is to set up NATIVE authentication. In particular, because BUILTIN isn't being used anymore, there is no need to call syscs_set_database_property() at all. If the following property is set on the server... -Dderby.authentication.provider=NATIVE:nativeAuthClientDB:LOCAL ...then the following work will happen automagically: 1) derby.authentication.provider=NATIVE::LOCAL will be set in the database. 2) The SYSADM credentials will be loaded automatically. 3) And there is no need to set databaseOnlyProperties. The database has been secured by NATIVE::LOCAL authentication so there is no way to subvert authentication now. I also think that we should remove the code which sets coarse-grained connection authorizations. I believe that this material should have been removed when we introduced fine-grained SQL authorizations in 10.2. Please be patient as we improve these examples. I think that this may take a couple iterations. As I said earlier, if you like, I can create a simpler example from scratch once we agree on what the example should show. rdevcsecuresqlauthembeddedex: Similar comments. rdevcsecuresqlauthsyspropex: I'm tempted to get rid of this page and just roll its material into the previous examples. Can you think of a reason why we need this extra page? tdevcsecure81850: o Item 4: Again, I think that we only need to recommend SQL authorization. I would reword the first sentence as follows: "To prevent unauthorized users from accessing databases once they are booted, turn on user authentication and SQL authorization for the database." o Item 5: I think that this page is about how to configure security in a production environment. I don't think that we should mention BUILTIN authentication at all since it is not secure. I would just reduce this item to the following. If we remove the bit about BUILTIN authentcation, then there's nothing left to this item except the advice to turn on SSL/TLS. But that doesn't make sense for an embedded scenario. So I would reduce this item to the following and move it to the next page (which deals with configuring network security): "It is also strongly recommended that production systems protect network connections with SSL/TLS." tdevcsecure82556: o Item 3: Again, let's not mention BUILTIN authentication. Strike the last two sentences of this item. o Item 4: This text assumes that you are using coarse-grained connection authorization rather than fine-grained SQL authorization. Again, I think that we should stop recommending the old-style, Derby-specific coarse-grained mechanism. Instead, we should recommend that people use portable SQL Standard features wherever possible. I would simplify this item as follows: "Configure SQL authorization for your databases."
          Hide
          Kim Haase added a comment -

          Thanks so much, Rick. I would be very happy for you to redo the code examples to make them as simple as possible. I have always felt more comfortable using database properties rather than system properties because everything is done in the code and I don't have to worry about a properties file or command-line arguments. That explains why I never tried using NATIVE:$credsDB:LOCAL. I didn't really understand what it did.

          I had an interesting experience just now trying to use ij to do what the code examples did.

          When I use NATIVE authentication programmatically, I create the database, create the users, set the authentication provider to NATIVE::LOCAL, then shut down the database and reconnect to it. But in ij this doesn't seem to work.

          Here I try connecting to the database as the owner, creating the user, then setting the authentication provider. I thought if I connected to the database with credentials when I created it, the credentials were supposed to be stored in the database, but they don't seem to be.

          ij> connect 'jdbc:derby:testDB;user=myself;password=mypass;create=true';
          ij> select * from sys.sysusers;
          USERNAME |HASHINGSCHEME |PASSWORD |LASTMODIFIED
          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

          0 rows selected
          ij> call SYSCS_UTIL.SYSCS_CREATE_USER('myself','mypass');
          0 rows inserted/updated/deleted
          ij> select * from sys.sysusers;
          USERNAME |HASHINGSCHEME |PASSWORD |LASTMODIFIED
          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          myself |3b62:8b7e520587f398ddf91d7e51fdc027d8:1000:SHA-256 |40b3e6fadf4d9c87d3e7ce206b4c41d1e76f174bf17dd8b185316b0a3240c2c1 |2012-03-19 16:01:04.778

          1 row selected
          ij> call syscs_util.syscs_set_database_property('derby.authentication.provider', 'NATIVE::LOCAL' );
          ERROR XCY05: Invalid change of the derby.authentication.provider property. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the SYSCS_UTIL.SYSCS_CREATE_USER procedure.

          Well, I thought that's what I did. Also, it's interesting that after I create the user, I can see the password. I didn't think I was supposed to be able to do that.

          So if you use ij with NATIVE authentication, you have to start it with JVM args or a derby.properties file?

          In the meantime I will work on your comments.

          Show
          Kim Haase added a comment - Thanks so much, Rick. I would be very happy for you to redo the code examples to make them as simple as possible. I have always felt more comfortable using database properties rather than system properties because everything is done in the code and I don't have to worry about a properties file or command-line arguments. That explains why I never tried using NATIVE:$credsDB:LOCAL. I didn't really understand what it did. I had an interesting experience just now trying to use ij to do what the code examples did. When I use NATIVE authentication programmatically, I create the database, create the users, set the authentication provider to NATIVE::LOCAL, then shut down the database and reconnect to it. But in ij this doesn't seem to work. Here I try connecting to the database as the owner, creating the user, then setting the authentication provider. I thought if I connected to the database with credentials when I created it, the credentials were supposed to be stored in the database, but they don't seem to be. ij> connect 'jdbc:derby:testDB;user=myself;password=mypass;create=true'; ij> select * from sys.sysusers; USERNAME |HASHINGSCHEME |PASSWORD |LASTMODIFIED -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij> call SYSCS_UTIL.SYSCS_CREATE_USER('myself','mypass'); 0 rows inserted/updated/deleted ij> select * from sys.sysusers; USERNAME |HASHINGSCHEME |PASSWORD |LASTMODIFIED -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- myself |3b62:8b7e520587f398ddf91d7e51fdc027d8:1000:SHA-256 |40b3e6fadf4d9c87d3e7ce206b4c41d1e76f174bf17dd8b185316b0a3240c2c1 |2012-03-19 16:01:04.778 1 row selected ij> call syscs_util.syscs_set_database_property('derby.authentication.provider', 'NATIVE::LOCAL' ); ERROR XCY05: Invalid change of the derby.authentication.provider property. This property cannot be set to NATIVE::LOCAL unless credentials for the database owner have been stored in the database using the SYSCS_UTIL.SYSCS_CREATE_USER procedure. Well, I thought that's what I did. Also, it's interesting that after I create the user, I can see the password. I didn't think I was supposed to be able to do that. So if you use ij with NATIVE authentication, you have to start it with JVM args or a derby.properties file? In the meantime I will work on your comments.
          Hide
          Kim Haase added a comment -

          I was also going to mention that I am planning to add the material from the spec that follows "Here's how Derby treats these new values for derby.authentication.provider:" to the Developer's Guide topic cdevcsecurenativeauth (Using NATIVE authentication). I was going to put it in the derby.authentication.provider reference topic, but I don't think it really fits there – it seems more conceptual.

          I'm omitting the "Old databases" bullet because that seems more appropriate for the release notes – does that make sense?

          Show
          Kim Haase added a comment - I was also going to mention that I am planning to add the material from the spec that follows "Here's how Derby treats these new values for derby.authentication.provider:" to the Developer's Guide topic cdevcsecurenativeauth (Using NATIVE authentication). I was going to put it in the derby.authentication.provider reference topic, but I don't think it really fits there – it seems more conceptual. I'm omitting the "Old databases" bullet because that seems more appropriate for the release notes – does that make sense?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          Some more responses:

          >So if you use ij with NATIVE authentication, you have to start it with JVM args or a derby.properties file?

          Right. If you want Derby to mark the database as a credentials DB and put the DBO's credentials into SYS.SYSUSERS, then you need to specify derby.authentication.provider=NATIVE:myDB:LOCAL (in command line properties or derby.properties) before you create the database. This is true for any application, including ij. There's no special logic for ij.

          >I was also going to mention that I am planning to add the material from the spec that follows "Here's how Derby treats these new values for derby.authentication.provider:" to the Developer's Guide topic cdevcsecurenativeauth (Using NATIVE authentication). I was going to put it in the derby.authentication.provider reference topic, but I don't think it really fits there – it seems more conceptual.

          This sounds good to me. If there's a pointer from the reference topic back to "Using NATIVE authentication" then all bases are covered.

          >I'm omitting the "Old databases" bullet because that seems more appropriate for the release notes – does that make sense?

          Agreed. Thanks!

          Show
          Rick Hillegas added a comment - Hi Kim, Some more responses: >So if you use ij with NATIVE authentication, you have to start it with JVM args or a derby.properties file? Right. If you want Derby to mark the database as a credentials DB and put the DBO's credentials into SYS.SYSUSERS, then you need to specify derby.authentication.provider=NATIVE:myDB:LOCAL (in command line properties or derby.properties) before you create the database. This is true for any application, including ij. There's no special logic for ij. >I was also going to mention that I am planning to add the material from the spec that follows "Here's how Derby treats these new values for derby.authentication.provider:" to the Developer's Guide topic cdevcsecurenativeauth (Using NATIVE authentication). I was going to put it in the derby.authentication.provider reference topic, but I don't think it really fits there – it seems more conceptual. This sounds good to me. If there's a pointer from the reference topic back to "Using NATIVE authentication" then all bases are covered. >I'm omitting the "Old databases" bullet because that seems more appropriate for the release notes – does that make sense? Agreed. Thanks!
          Hide
          Kim Haase added a comment -

          Thanks again, Rick. So it appears that setting the provider to NATIVE::LOCAL on an existing database by calling syscs_util.syscs_set_database_property() does not work in ij, whereas it does work in a Java program. Is that correct?

          Couple of other items – under cdevcsecure36595 you notice a lack of vertical space between the note and the paragraph – I think that might be a browser-specific effect. I don't see it in either Firefox or IE.

          Also, under cdevcsecure42374, I will certainly clarify the distinction between authentication and authorization. I think the more detailed explanation will go into the "Derby and Security" topic for DERBY-5636, a level above, which contains the same misinformation, I believe. I am planning copy this info into that topic just so I don't forget.

          Show
          Kim Haase added a comment - Thanks again, Rick. So it appears that setting the provider to NATIVE::LOCAL on an existing database by calling syscs_util.syscs_set_database_property() does not work in ij, whereas it does work in a Java program. Is that correct? Couple of other items – under cdevcsecure36595 you notice a lack of vertical space between the note and the paragraph – I think that might be a browser-specific effect. I don't see it in either Firefox or IE. Also, under cdevcsecure42374, I will certainly clarify the distinction between authentication and authorization. I think the more detailed explanation will go into the "Derby and Security" topic for DERBY-5636 , a level above, which contains the same misinformation, I believe. I am planning copy this info into that topic just so I don't forget.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          >So it appears that setting the provider to NATIVE::LOCAL on an existing database by calling syscs_util.syscs_set_database_property() does not work in ij, whereas it does work in a Java program. Is that correct?

          Perhaps the simplest thing to say about this topic is that the behavior has changed as a result of the spec change which I just checked in on DERBY-866: Now you can't set the authentication provider to NATIVE::LOCAL by calling syscs_util.syscs_set_database_property() at all. Instead, an existing database becomes a credentials DB (and that property is automatically set by Derby) when the DBO calls syscs_create_user() to store her own credentials. This is true regardless of whether you are trying to convert a database into a credentials db via ij or via some other application.

          But I think there is some other peculiarity which you are experiencing and which I don't understand. When you create a database from scratch, the DBO's credentials are stored in SYS.SYSUSERS (and the database is marked as a credentials DB) only if you have set the authentication provider to NATIVE authentication via command line properties or derby.properties. This is supposed to be true regardless of whether you are creating the database in an ij script or in some other application. More specifically...

          derby.authentication.provider=NATIVE:credsDB:LOCAL

          will cause every newly created database to be a credentials db. And

          derby.authentication.provider=NATIVE:credsDB

          will cause only credsDB to be marked as a credentials db when it is created.

          Is there an application which you think is behaving differently than this? If so, can you point me at it?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, >So it appears that setting the provider to NATIVE::LOCAL on an existing database by calling syscs_util.syscs_set_database_property() does not work in ij, whereas it does work in a Java program. Is that correct? Perhaps the simplest thing to say about this topic is that the behavior has changed as a result of the spec change which I just checked in on DERBY-866 : Now you can't set the authentication provider to NATIVE::LOCAL by calling syscs_util.syscs_set_database_property() at all. Instead, an existing database becomes a credentials DB (and that property is automatically set by Derby) when the DBO calls syscs_create_user() to store her own credentials. This is true regardless of whether you are trying to convert a database into a credentials db via ij or via some other application. But I think there is some other peculiarity which you are experiencing and which I don't understand. When you create a database from scratch, the DBO's credentials are stored in SYS.SYSUSERS (and the database is marked as a credentials DB) only if you have set the authentication provider to NATIVE authentication via command line properties or derby.properties. This is supposed to be true regardless of whether you are creating the database in an ij script or in some other application. More specifically... derby.authentication.provider=NATIVE:credsDB:LOCAL will cause every newly created database to be a credentials db. And derby.authentication.provider=NATIVE:credsDB will cause only credsDB to be marked as a credentials db when it is created. Is there an application which you think is behaving differently than this? If so, can you point me at it? Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I have attached version 7 of the func spec to DERBY-866. I tried to summarize all of the changes in the 7.0 header comment at the start of the spec. Let me know if this is not clear.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, I have attached version 7 of the func spec to DERBY-866 . I tried to summarize all of the changes in the 7.0 header comment at the start of the spec. Let me know if this is not clear. Thanks, -Rick
          Hide
          Kim Haase added a comment -

          The NativeAuthExampleEmbedded application used to behave differently from your description – but with the latest changes, it no longer works that way. The error messages are not crystal clear, though. When I tried to run the version attached above, this was the error:

          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Storing some sample users in the database.

          --SQLException Caught--

          SQLState: 4251K
          Severity: 30000
          Message: The first credentials created must be those of the DBO.

          So I switched the order of user creation and got

          Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true
          Storing some sample users in the database.
          Turning on NATIVE authentication.

          --SQLException Caught--

          SQLState: XCY05
          Severity: 30000
          Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed.

          The problem is that I'm using SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to set the provider to NATIVE, which is now illegal. The provider was never set before, to NATIVE or anything else. So the message is misleading.

          Show
          Kim Haase added a comment - The NativeAuthExampleEmbedded application used to behave differently from your description – but with the latest changes, it no longer works that way. The error messages are not crystal clear, though. When I tried to run the version attached above, this was the error: Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Storing some sample users in the database. -- SQLException Caught -- SQLState: 4251K Severity: 30000 Message: The first credentials created must be those of the DBO. So I switched the order of user creation and got Trying to connect to jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Connected to database jdbc:derby:nativeAuthEmbDB;user=sysadm;create=true Storing some sample users in the database. Turning on NATIVE authentication. -- SQLException Caught -- SQLState: XCY05 Severity: 30000 Message: Invalid change of the derby.authentication.provider property. Once set to NATIVE authentication, this property cannot be changed. The problem is that I'm using SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY to set the provider to NATIVE, which is now illegal. The provider was never set before, to NATIVE or anything else. So the message is misleading.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          You will need to not call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY. With my latest changes, that step is no longer necessary and, as you noticed, that step fails now. I agree that the error message is confusing. What do you think the error should say? Maybe:

          "Invalid change of the derby.authentication.provider property. This property is already set to NATIVE::LOCAL and cannot be changed."

          Something else?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, You will need to not call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY. With my latest changes, that step is no longer necessary and, as you noticed, that step fails now. I agree that the error message is confusing. What do you think the error should say? Maybe: "Invalid change of the derby.authentication.provider property. This property is already set to NATIVE::LOCAL and cannot be changed." Something else? Thanks, -Rick
          Hide
          Kim Haase added a comment -

          That message is much clearer! Though maybe "Invalid setting of ..." would be more precise?

          Show
          Kim Haase added a comment - That message is much clearer! Though maybe "Invalid setting of ..." would be more precise?
          Hide
          Kim Haase added a comment -

          Thanks for the revised spec. I had not noticed that the Documentation section did not mention documenting the derby.authentication.native.passwordLifetimeThreshold property as well as the derby.authentication.native.passwordLifetimeMillis property. Was that an oversight or did you mean to keep the threshold one undocumented? I already wrote a topic for it.

          I noticed already that the spec mentions two other currently undocumented properties, derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations, and I'm not planning to expose them.

          Show
          Kim Haase added a comment - Thanks for the revised spec. I had not noticed that the Documentation section did not mention documenting the derby.authentication.native.passwordLifetimeThreshold property as well as the derby.authentication.native.passwordLifetimeMillis property. Was that an oversight or did you mean to keep the threshold one undocumented? I already wrote a topic for it. I noticed already that the spec mentions two other currently undocumented properties, derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations, and I'm not planning to expose them.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          Your instincts about documenting the properties sound good to me. I agree that derby.authentication.native.passwordLifetimeThreshold deserves to be documented. I don't think that we need to expose derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations at this time.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, Your instincts about documenting the properties sound good to me. I agree that derby.authentication.native.passwordLifetimeThreshold deserves to be documented. I don't think that we need to expose derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations at this time. Thanks, -Rick
          Hide
          Kim Haase added a comment -

          I have been trying to figure out under what circumstances I would need to call SYSCS_UTIL.SYSCS_CREATE_USER to create credentials for the DBO for a pre-existing database. It seems that this never happens.

          With authentication disabled, I create a database, testDB; then I set the provider to NATIVE:credsDB. I create credsDB, in which I (the DBO) am already entered. Then I create the user APP, who owns testDB. I can then connect to testDB as APP. Nothing is stored in testDB's sysusers table, because credsDB is used for everything.

          jdench 100 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:testDB;create=true';
          ij> create table t1(n int);
          0 rows inserted/updated/deleted
          ij> select * from t1;
          N
          -----------

          0 rows selected
          ij> exit;
          jdench 101 =>mv notderby.properties derby.properties
          jdench 102 =>more derby.properties
          derby.authentication.provider=NATIVE:credsDB
          jdench 103 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:credsDB;user=myself;password=mypass;create=true';
          ij> select username from sys.sysusers;
          USERNAME
          -------------------------------------------------------------------------------
          MYSELF

          1 row selected
          ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app');
          0 rows inserted/updated/deleted
          ij> select username from sys.sysusers;
          USERNAME
          -------------------------------------------------------------------------------
          APP
          MYSELF

          2 rows selected
          ij> disconnect;
          ij> connect 'jdbc:derby:testDB;user=app;password=app';
          ij> select * from t1;
          N
          -----------

          0 rows selected
          ij> insert into t1 values(3);
          1 row inserted/updated/deleted
          ij> select * from t1;
          N
          -----------
          3

          1 row selected
          ij> select username from sys.sysusers;
          USERNAME
          -------------------------------------------------------------------------------

          0 rows selected
          ij> exit;

          I then do exactly the same thing with the property set to NATIVE:credsDB:LOCAL, after deleting the databases. This time I get an authentication failure when I try to log in to testDB.

          jdench 105 =>mv derby.properties notderby.properties
          jdench 106 =>/bin/rm -rf testDB credsDB
          jdench 107 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:testDB;create=true';
          ij> create table t1(n int);
          0 rows inserted/updated/deleted
          ij> select * from t1;
          N
          -----------

          0 rows selected
          ij> exit;
          jdench 108 =>mv notderby.properties derby.properties
          jdench 109 =>more derby.properties
          derby.authentication.provider=NATIVE:credsDB:LOCAL
          jdench 110 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:credsDB;user=myself;password=mypass;create=true';
          ij> select username from sys.sysusers;
          USERNAME
          --------------------------------------------------------------------------------------------------------------------------------
          MYSELF

          1 row selected
          ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app');
          0 rows inserted/updated/deleted
          ij> select username from sys.sysusers;
          USERNAME
          --------------------------------------------------------------------------------------------------------------------------------
          APP
          MYSELF

          2 rows selected
          ij> disconnect;
          ij> connect 'jdbc:derby:testDB;user=app;password=app';
          ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
          ij> exit;

          I cannot connect to testDB at all, so there is no way I can store my credentials in it. What am I doing wrong?

          Show
          Kim Haase added a comment - I have been trying to figure out under what circumstances I would need to call SYSCS_UTIL.SYSCS_CREATE_USER to create credentials for the DBO for a pre-existing database. It seems that this never happens. With authentication disabled, I create a database, testDB; then I set the provider to NATIVE:credsDB. I create credsDB, in which I (the DBO) am already entered. Then I create the user APP, who owns testDB. I can then connect to testDB as APP. Nothing is stored in testDB's sysusers table, because credsDB is used for everything. jdench 100 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:testDB;create=true'; ij> create table t1(n int); 0 rows inserted/updated/deleted ij> select * from t1; N ----------- 0 rows selected ij> exit; jdench 101 =>mv notderby.properties derby.properties jdench 102 =>more derby.properties derby.authentication.provider=NATIVE:credsDB jdench 103 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:credsDB;user=myself;password=mypass;create=true'; ij> select username from sys.sysusers; USERNAME ------------------------------------------------------------------------------- MYSELF 1 row selected ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app'); 0 rows inserted/updated/deleted ij> select username from sys.sysusers; USERNAME ------------------------------------------------------------------------------- APP MYSELF 2 rows selected ij> disconnect; ij> connect 'jdbc:derby:testDB;user=app;password=app'; ij> select * from t1; N ----------- 0 rows selected ij> insert into t1 values(3); 1 row inserted/updated/deleted ij> select * from t1; N ----------- 3 1 row selected ij> select username from sys.sysusers; USERNAME ------------------------------------------------------------------------------- 0 rows selected ij> exit; I then do exactly the same thing with the property set to NATIVE:credsDB:LOCAL, after deleting the databases. This time I get an authentication failure when I try to log in to testDB. jdench 105 =>mv derby.properties notderby.properties jdench 106 =>/bin/rm -rf testDB credsDB jdench 107 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:testDB;create=true'; ij> create table t1(n int); 0 rows inserted/updated/deleted ij> select * from t1; N ----------- 0 rows selected ij> exit; jdench 108 =>mv notderby.properties derby.properties jdench 109 =>more derby.properties derby.authentication.provider=NATIVE:credsDB:LOCAL jdench 110 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:credsDB;user=myself;password=mypass;create=true'; ij> select username from sys.sysusers; USERNAME -------------------------------------------------------------------------------------------------------------------------------- MYSELF 1 row selected ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app'); 0 rows inserted/updated/deleted ij> select username from sys.sysusers; USERNAME -------------------------------------------------------------------------------------------------------------------------------- APP MYSELF 2 rows selected ij> disconnect; ij> connect 'jdbc:derby:testDB;user=app;password=app'; ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication.. ij> exit; I cannot connect to testDB at all, so there is no way I can store my credentials in it. What am I doing wrong?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          What you have to do is connect to testDB without setting derby.authentication.provider (or you can connect to testDB using whatever legacy authentication mechanism you were using with Derby 10.8). Once you are connected to testDB, you should be able to call syscs_create_user(). Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, What you have to do is connect to testDB without setting derby.authentication.provider (or you can connect to testDB using whatever legacy authentication mechanism you were using with Derby 10.8). Once you are connected to testDB, you should be able to call syscs_create_user(). Thanks.
          Hide
          Kim Haase added a comment -

          Thanks, Rick! That worked. I didn't try to see what would happen if I'd tried to create a user other than APP, since APP owned the only schema with data. Without an authentication provider, would there have been an error?

          jdench 121 =>mv derby.properties notderby.properties
          jdench 122 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:testDB';
          ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app');
          0 rows inserted/updated/deleted
          ij> select username from sys.sysusers;
          USERNAME
          --------------------------------------------------------------------------------------------------------------------------------
          APP

          1 row selected
          ij> exit;
          jdench 123 =>mv notderby.properties derby.properties
          jdench 124 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:testDB;user=app;password=app';
          ij> select * from t1;
          N
          -----------

          0 rows selected
          ij> insert into t1 values(33);
          1 row inserted/updated/deleted
          ij> select * from t1;
          N
          -----------
          33
          1 row selected
          ij> values SYSCS_UTIL.SYSCS_get_database_property('derby.authentication.provider');
          1
          --------------------------------------------------------------------------------------------------------------------------------
          NATIVE::LOCAL

          1 row selected

          Show
          Kim Haase added a comment - Thanks, Rick! That worked. I didn't try to see what would happen if I'd tried to create a user other than APP, since APP owned the only schema with data. Without an authentication provider, would there have been an error? jdench 121 =>mv derby.properties notderby.properties jdench 122 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:testDB'; ij> call SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app'); 0 rows inserted/updated/deleted ij> select username from sys.sysusers; USERNAME -------------------------------------------------------------------------------------------------------------------------------- APP 1 row selected ij> exit; jdench 123 =>mv notderby.properties derby.properties jdench 124 =>java -jar ../codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:testDB;user=app;password=app'; ij> select * from t1; N ----------- 0 rows selected ij> insert into t1 values(33); 1 row inserted/updated/deleted ij> select * from t1; N ----------- 33 1 row selected ij> values SYSCS_UTIL.SYSCS_get_database_property('derby.authentication.provider'); 1 -------------------------------------------------------------------------------------------------------------------------------- NATIVE::LOCAL 1 row selected
          Hide
          Kim Haase added a comment -

          Can all four of the system procedures be called when NATIVE authentication is not enabled? I had it in my head that they would only work when it was turned on. But of course the spec does not say that anywhere.

          Show
          Kim Haase added a comment - Can all four of the system procedures be called when NATIVE authentication is not enabled? I had it in my head that they would only work when it was turned on. But of course the spec does not say that anywhere.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          The NATIVE procedures can be called by anyone if authentication is off. This is the same behavior which you see with other system procedures which are restricted to the DBO when authentication is on. If authentication is off, then all of those procedures can be called by anyone. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, The NATIVE procedures can be called by anyone if authentication is off. This is the same behavior which you see with other system procedures which are restricted to the DBO when authentication is on. If authentication is off, then all of those procedures can be called by anyone. Thanks.
          Hide
          Kim Haase added a comment -

          Thanks, Rick. I will make sure the "Execute privileges" boilerplate matches that for other procedures. (Except for modify_password.)

          Show
          Kim Haase added a comment - Thanks, Rick. I will make sure the "Execute privileges" boilerplate matches that for other procedures. (Except for modify_password.)
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I am attaching NativeAuthenticationExample.java. This is a single program which can be run embedded or client/server (see the header comment for how to run it). I recommend this as a replacement for the Dev Guide code examples which show how to turn on NATIVE authentication and work with SQL authorization. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, I am attaching NativeAuthenticationExample.java. This is a single program which can be run embedded or client/server (see the header comment for how to run it). I recommend this as a replacement for the Dev Guide code examples which show how to turn on NATIVE authentication and work with SQL authorization. Thanks.
          Hide
          Kim Haase added a comment -

          Thank you, Rick! I will incorporate the new example and provide a revised Developer's Guide patch.

          In the meantime, here is a first draft of the Reference Manual material, DERBY-5522-ref.diff, DERBY-5522-ref.stat, and DERBY-5522-ref.zip, with the following changes:

          M src/ref/rrefproper27467.dita
          A src/ref/rrefnativemodifypasswordproc.dita
          M src/ref/rrefpropersqlauth.dita
          M src/ref/crefproper22250.dita
          A src/ref/rrefsistabssysusers.dita
          M src/ref/rrefproper13766.dita
          A src/ref/rrefproperpasswordmillis.dita
          A src/ref/rrefnativedropuserproc.dita
          A src/ref/rrefnativecreateuserproc.dita
          M src/ref/rrefproper25025.dita
          M src/ref/rrefproper24846.dita
          A src/ref/rrefnativeresetpasswordproc.dita
          M src/ref/rrefproperbuiltinalgorithm.dita
          A src/ref/rrefproperpasswordthreshold.dita
          M src/ref/refderby.ditamap

          I'm sure plenty of fixes will be needed here, too. Thanks in advance!

          Show
          Kim Haase added a comment - Thank you, Rick! I will incorporate the new example and provide a revised Developer's Guide patch. In the meantime, here is a first draft of the Reference Manual material, DERBY-5522 -ref.diff, DERBY-5522 -ref.stat, and DERBY-5522 -ref.zip, with the following changes: M src/ref/rrefproper27467.dita A src/ref/rrefnativemodifypasswordproc.dita M src/ref/rrefpropersqlauth.dita M src/ref/crefproper22250.dita A src/ref/rrefsistabssysusers.dita M src/ref/rrefproper13766.dita A src/ref/rrefproperpasswordmillis.dita A src/ref/rrefnativedropuserproc.dita A src/ref/rrefnativecreateuserproc.dita M src/ref/rrefproper25025.dita M src/ref/rrefproper24846.dita A src/ref/rrefnativeresetpasswordproc.dita M src/ref/rrefproperbuiltinalgorithm.dita A src/ref/rrefproperpasswordthreshold.dita M src/ref/refderby.ditamap I'm sure plenty of fixes will be needed here, too. Thanks in advance!
          Hide
          Kim Haase added a comment -

          Do you mind if I make changes as in the attached version?

          To run the client you need derbyclient.jar in the classpath, not derby.jar.

          Also, I made some line break changes so the code will fit in the PDF and typical browser topic window, and then made the rest of the code consistent (curly braces, comments), for readability.

          If you feel strongly about some of this (like putting comments all on one line if possible), I can easily revert.

          Show
          Kim Haase added a comment - Do you mind if I make changes as in the attached version? To run the client you need derbyclient.jar in the classpath, not derby.jar. Also, I made some line break changes so the code will fit in the PDF and typical browser topic window, and then made the rest of the code consistent (curly braces, comments), for readability. If you feel strongly about some of this (like putting comments all on one line if possible), I can easily revert.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kim. These changes look like improvements to me. +1

          Show
          Rick Hillegas added a comment - Thanks, Kim. These changes look like improvements to me. +1
          Hide
          Kim Haase added a comment -

          Could you possibly also provide instructions on how to shut down the Network Server after running the program? This does not work:

          jdench 64 =>java -cp $

          {DERBY_LIB}/derby.jar:${DERBY_LIB}

          /derbynet.jar:. -Dderby.authentication.provider=NATIVE:nativeAuthDB:LOCAL org.apache.derby.drda.NetworkServerControl shutdown
          Thu Mar 22 14:46:42 EDT 2012 : 08004:Connection authentication failure occurred. Reason: Invalid authentication..

          I've gotten in this state before and the only way I could get out of it was to kill the server process ...

          Show
          Kim Haase added a comment - Could you possibly also provide instructions on how to shut down the Network Server after running the program? This does not work: jdench 64 =>java -cp $ {DERBY_LIB}/derby.jar:${DERBY_LIB} /derbynet.jar:. -Dderby.authentication.provider=NATIVE:nativeAuthDB:LOCAL org.apache.derby.drda.NetworkServerControl shutdown Thu Mar 22 14:46:42 EDT 2012 : 08004:Connection authentication failure occurred. Reason: Invalid authentication.. I've gotten in this state before and the only way I could get out of it was to kill the server process ...
          Hide
          Kim Haase added a comment -

          Actually, now that I study the client version output clearly, is there a logic issue? It ends with the following:

          Shutting down engine via this URL: jdbc:derby://localhost:1527/;user=sysadm;password=shh123ihtybb87m;shutdown=true

          But I thought if you were running client-server instead of embedded, you had to shut down the database but not the engine? I'm probably confused again.

          Show
          Kim Haase added a comment - Actually, now that I study the client version output clearly, is there a logic issue? It ends with the following: Shutting down engine via this URL: jdbc:derby://localhost:1527/;user=sysadm;password=shh123ihtybb87m;shutdown=true But I thought if you were running client-server instead of embedded, you had to shut down the database but not the engine? I'm probably confused again.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I am reworking the program to start and stop the server programmatically. This will simplify running the example. I am puzzling through how to get user credentials to the server. Stand by while I figure this out...

          As far as bringing down the engine vs. bringing down the database: you want to bring down the whole engine gracefully before your program exits. But to do this, you need to present valid credentials. The credentials can only be validated in the credentials DB, so there is no point in bringing that database down before you request engine shutdown.

          Hope this makes sense,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, I am reworking the program to start and stop the server programmatically. This will simplify running the example. I am puzzling through how to get user credentials to the server. Stand by while I figure this out... As far as bringing down the engine vs. bringing down the database: you want to bring down the whole engine gracefully before your program exits. But to do this, you need to present valid credentials. The credentials can only be validated in the credentials DB, so there is no point in bringing that database down before you request engine shutdown. Hope this makes sense, -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I'm attaching a new version of the program. The instructions for running the client/server version are simpler now. The program takes responsibility for bringing the server up and down.

          Hope this works better,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, I'm attaching a new version of the program. The instructions for running the client/server version are simpler now. The program takes responsibility for bringing the server up and down. Hope this works better, -Rick
          Hide
          Kim Haase added a comment -

          It does, much better! Thanks! I could not have figured out how to start and stop the Network Server programmatically – this is useful to provide.

          I would like to make a couple of small improvements – one is to tell people how to compile, since they now need to specify a classpath for that –

          javac -cp $

          {DERBY_LIB}

          /derbynet.jar NativeAuthenticationExample.java

          Also, I would like to insert the following in the execute() method, between the calls to verifyWriterPrivileges() and dropTable( dboConn ), since there is currently no output to indicate that the user is now the DBO again:

          println( "Using database owner connection again" );

          I'm attaching a version with those changes and a couple more formatting consistency fixes for the new code.

          Show
          Kim Haase added a comment - It does, much better! Thanks! I could not have figured out how to start and stop the Network Server programmatically – this is useful to provide. I would like to make a couple of small improvements – one is to tell people how to compile, since they now need to specify a classpath for that – javac -cp $ {DERBY_LIB} /derbynet.jar NativeAuthenticationExample.java Also, I would like to insert the following in the execute() method, between the calls to verifyWriterPrivileges() and dropTable( dboConn ), since there is currently no output to indicate that the user is now the DBO again: println( "Using database owner connection again" ); I'm attaching a version with those changes and a couple more formatting consistency fixes for the new code.
          Hide
          Rick Hillegas added a comment -

          Thanks for these improvements, Kim. +1

          Show
          Rick Hillegas added a comment - Thanks for these improvements, Kim. +1
          Hide
          Kim Haase added a comment -

          A bit late ...

          Show
          Kim Haase added a comment - A bit late ...
          Hide
          Kim Haase added a comment -

          Another version, reformatted so that it doesn't look too bad in PDF (which limits line length).

          Show
          Kim Haase added a comment - Another version, reformatted so that it doesn't look too bad in PDF (which limits line length).
          Hide
          Kim Haase added a comment -

          Attaching a second draft of Developer's Guide changes, DERBY-5522-devguide-2.diff, DERBY-5522-devguide-2.stat, and DERBY-5522-devguide-2.zip, with changes as follows:

          D src/devguide/rdevcsecure26537.dita
          D src/devguide/rdevcsecureclientexample.dita
          M src/devguide/cdevcsecure42374.dita
          M src/devguide/rdevcsecure557.dita
          M src/devguide/cdevcsecure866060.dita
          D src/devguide/rdevcsecuresqlauthclientex.dita
          M src/devguide/derbydev.ditamap
          A src/devguide/rdevcsecurenativeauthex.dita
          D src/devguide/rdevcsecuresqlauthembeddedex.dita
          M src/devguide/tdevcsecure82556.dita
          A src/devguide/cdevcsecurenativeauth.dita
          M src/devguide/cdevcsecureDbOwner.dita
          M src/devguide/rdevcsecure125.dita
          D src/devguide/rdevcsecure13713.dita
          M src/devguide/cdevcsecure24458.dita
          M src/devguide/cdevcsecure37817.dita
          M src/devguide/cdevcsecure51876.dita
          M src/devguide/rdevcsecure766.dita
          D src/devguide/rdevcsecuresqlauthexs.dita
          M src/devguide/cdevcsecure21547.dita
          M src/devguide/tdevcsecure81850.dita
          M src/devguide/cdevcsecure36595.dita
          M src/devguide/cdevcsecure36127.dita
          M src/devguide/cdevcsecure865818.dita
          M src/devguide/cdevcsecure865880.dita
          M src/devguide/cdevsetprop824451.dita

          Thanks to Rick, instead of having 3 examples that all do almost the same things, we have just one that illustrates NATIVE authentication and SQL authorization.

          Thanks in advance for further comments.

          Show
          Kim Haase added a comment - Attaching a second draft of Developer's Guide changes, DERBY-5522 -devguide-2.diff, DERBY-5522 -devguide-2.stat, and DERBY-5522 -devguide-2.zip, with changes as follows: D src/devguide/rdevcsecure26537.dita D src/devguide/rdevcsecureclientexample.dita M src/devguide/cdevcsecure42374.dita M src/devguide/rdevcsecure557.dita M src/devguide/cdevcsecure866060.dita D src/devguide/rdevcsecuresqlauthclientex.dita M src/devguide/derbydev.ditamap A src/devguide/rdevcsecurenativeauthex.dita D src/devguide/rdevcsecuresqlauthembeddedex.dita M src/devguide/tdevcsecure82556.dita A src/devguide/cdevcsecurenativeauth.dita M src/devguide/cdevcsecureDbOwner.dita M src/devguide/rdevcsecure125.dita D src/devguide/rdevcsecure13713.dita M src/devguide/cdevcsecure24458.dita M src/devguide/cdevcsecure37817.dita M src/devguide/cdevcsecure51876.dita M src/devguide/rdevcsecure766.dita D src/devguide/rdevcsecuresqlauthexs.dita M src/devguide/cdevcsecure21547.dita M src/devguide/tdevcsecure81850.dita M src/devguide/cdevcsecure36595.dita M src/devguide/cdevcsecure36127.dita M src/devguide/cdevcsecure865818.dita M src/devguide/cdevcsecure865880.dita M src/devguide/cdevsetprop824451.dita Thanks to Rick, instead of having 3 examples that all do almost the same things, we have just one that illustrates NATIVE authentication and SQL authorization. Thanks in advance for further comments.
          Hide
          Kim Haase added a comment -

          Oops, this is interesting. I decided to look at the log file from running the new example program. Although it appeared to run fine on the command line, there's a stack trace in derby.log. Is this expected?

          Show
          Kim Haase added a comment - Oops, this is interesting. I decided to look at the log file from running the new example program. Although it appeared to run fine on the command line, there's a stack trace in derby.log. Is this expected?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          I see 3 exceptions in derby.log, all expected exceptions raised by cases which are supposed to fail:

          1) an authentication failure (when presenting null credentials)

          2) a failed insert by the user in the read-only role

          3) a failed delete by the user in the read/write role who nevertheless was not granted delete privilege

          So the errors look ok to me.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kim, I see 3 exceptions in derby.log, all expected exceptions raised by cases which are supposed to fail: 1) an authentication failure (when presenting null credentials) 2) a failed insert by the user in the read-only role 3) a failed delete by the user in the read/write role who nevertheless was not granted delete privilege So the errors look ok to me. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Thanks for posting these changes to the Ref and Dev Guides, Kim. They look great. Some suggested changes follow:

          General comment about the reference pages for the new NATIVE system procedures:

          I think it would be good if these pages pointed out that the username argument is an authorization id. Its case-sensitivity is handled the same way that Derby handles the case-sensitivity of schema names and schema object names which are passed to other Derby procedures. To reduce confusion, I also recommend making the examples use uppercase usernames. E.g.:

          CALL SYSCS_UTIL.SYSCS_CREATE_USER('FRED', 'fredpassword')

          rrefnativecreateuserproc:

          I think it would be good if this page stated that if NATIVE authentication is not already turned on, then...

          1) The first user whose credentials are stored must be the DBO.

          2) Calling this procedure will turn on NATIVE authentication the next time the database is booted.

          3) Once you turn on NATIVE authentication with this procedure, it remains turned on permanently. There is no way to turn it off.

          rrefnativedropuserproc:

          I think that this page should state that you can't drop the credentials of the DBO.

          rrefnativemodifypasswordproc:

          I would reword the first sentence slightly in order to distinguish this procedure from the similar syscs_reset_password() procedure:

          "The SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure is called by a user to change her own password."

          rrefnativeresetpasswordproc

          Slight expansion of the first sentence:

          "has been forgotten" -> "has expired or been forgotten"

          rrefproper13766:

          While you're in there, it would be good to cleanup an existing false statement. The default value for derby.authentication.provider is "no authentication", not BUILTIN. By default, no authentication mechanism protects the database.

          rrefproper27467:

          I see from the diff file that this section states that derby.connection.requireAuthentication is irrelevant if NATIVE authentication is turned on. That's good. For some reason, that change doesn't appear in the html output in the zip file.

          rrefproperpasswordthreshold:

          I would reword the 3rd paragraph:

          "A warning is raised when a user logs in and the remaining lifetime of her password is less than this proportion of the maximum password lifetime. That is, Derby rasies a warning when the remaining lifetime of a password is less than (derby.authentication.native.passwordLifetimeThreshold * derby.authentication.native.passwordLifetimeMillis).

          rrefpropersqlauth:

          Again, for some reason the extra material in the diff doesn't appear in the html output in the zip file.

          cdevcsecure866060:

          Paragraph 5: "anabled" -> "enabled"

          cdevcsecurenativeauth:

          Bullet 3 under "Managing users and passwords":

          "forgotten" -> "forgotten or expired"

          Bullets under "Converting an existing database to use NATIVE authentication"

          I would reword bullet 1 this way:

          "If you specify NATIVE:credentialsDB, then add users of the existing database to the credentialsDB. Typically, you would specify uppercase user names and case-sensitive passwords. For instance, if the old database was created without any authentication, then its default username is APP and you would do the following:"

          I would reword bullet 2 this way:

          "If you plan to specify NATIVE:credentialsDB:LOCAL, then first connect to the existing database as its database owner using its old authentication scheme. Call SYSCS_UTIL.SYSCS_CREATE_USER to add credentials for the database owner. For example, if the existing database was created with no authentication, then the database owner is APP and you would add credentials for APP as shown above."

          rdevcsecurenativeauthex:

          Last paragraph of "NATIVE authentication and SQL authorization example":

          "DERBY_LIB is DERBY_HOME/lib" -> "DERBY_LIB is the directory which holds the Derby jar files, typically DERBY_HOME/lib"

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for posting these changes to the Ref and Dev Guides, Kim. They look great. Some suggested changes follow: General comment about the reference pages for the new NATIVE system procedures: I think it would be good if these pages pointed out that the username argument is an authorization id. Its case-sensitivity is handled the same way that Derby handles the case-sensitivity of schema names and schema object names which are passed to other Derby procedures. To reduce confusion, I also recommend making the examples use uppercase usernames. E.g.: CALL SYSCS_UTIL.SYSCS_CREATE_USER('FRED', 'fredpassword') rrefnativecreateuserproc: I think it would be good if this page stated that if NATIVE authentication is not already turned on, then... 1) The first user whose credentials are stored must be the DBO. 2) Calling this procedure will turn on NATIVE authentication the next time the database is booted. 3) Once you turn on NATIVE authentication with this procedure, it remains turned on permanently. There is no way to turn it off. rrefnativedropuserproc: I think that this page should state that you can't drop the credentials of the DBO. rrefnativemodifypasswordproc: I would reword the first sentence slightly in order to distinguish this procedure from the similar syscs_reset_password() procedure: "The SYSCS_UTIL.SYSCS_MODIFY_PASSWORD system procedure is called by a user to change her own password." rrefnativeresetpasswordproc Slight expansion of the first sentence: "has been forgotten" -> "has expired or been forgotten" rrefproper13766: While you're in there, it would be good to cleanup an existing false statement. The default value for derby.authentication.provider is "no authentication", not BUILTIN. By default, no authentication mechanism protects the database. rrefproper27467: I see from the diff file that this section states that derby.connection.requireAuthentication is irrelevant if NATIVE authentication is turned on. That's good. For some reason, that change doesn't appear in the html output in the zip file. rrefproperpasswordthreshold: I would reword the 3rd paragraph: "A warning is raised when a user logs in and the remaining lifetime of her password is less than this proportion of the maximum password lifetime. That is, Derby rasies a warning when the remaining lifetime of a password is less than (derby.authentication.native.passwordLifetimeThreshold * derby.authentication.native.passwordLifetimeMillis). rrefpropersqlauth: Again, for some reason the extra material in the diff doesn't appear in the html output in the zip file. cdevcsecure866060: Paragraph 5: "anabled" -> "enabled" cdevcsecurenativeauth: Bullet 3 under "Managing users and passwords": "forgotten" -> "forgotten or expired" Bullets under "Converting an existing database to use NATIVE authentication" I would reword bullet 1 this way: "If you specify NATIVE:credentialsDB, then add users of the existing database to the credentialsDB. Typically, you would specify uppercase user names and case-sensitive passwords. For instance, if the old database was created without any authentication, then its default username is APP and you would do the following:" I would reword bullet 2 this way: "If you plan to specify NATIVE:credentialsDB:LOCAL, then first connect to the existing database as its database owner using its old authentication scheme. Call SYSCS_UTIL.SYSCS_CREATE_USER to add credentials for the database owner. For example, if the existing database was created with no authentication, then the database owner is APP and you would add credentials for APP as shown above." rdevcsecurenativeauthex: Last paragraph of "NATIVE authentication and SQL authorization example": "DERBY_LIB is DERBY_HOME/lib" -> "DERBY_LIB is the directory which holds the Derby jar files, typically DERBY_HOME/lib" Thanks, -Rick
          Hide
          Kim Haase added a comment -

          Thanks so much, Rick! I have made the changes you suggest, I hope.

          There is one exception – I prefer not to use gender-specific terminology for users. People have gender, but users are objects.

          There is some sort of goofiness in the way files are transferred during processing – I think it involves the temp or temp_source directory – sometimes a modified file is not refreshed. I have to remove those directories to make sure all files are processed correctly. It's annoying and also unpredictable. But I think the output files all reflect the source now.

          I'm attaching DERBY-5522-devguide-3.diff, DERBY-5522-devguide-3.zip, DERBY-5522-ref-2.diff, and DERBY-5522-ref-2.zip. For this iteration, the zip files contain only the topics that changed as a result of the latest review.

          Show
          Kim Haase added a comment - Thanks so much, Rick! I have made the changes you suggest, I hope. There is one exception – I prefer not to use gender-specific terminology for users. People have gender, but users are objects. There is some sort of goofiness in the way files are transferred during processing – I think it involves the temp or temp_source directory – sometimes a modified file is not refreshed. I have to remove those directories to make sure all files are processed correctly. It's annoying and also unpredictable. But I think the output files all reflect the source now. I'm attaching DERBY-5522 -devguide-3.diff, DERBY-5522 -devguide-3.zip, DERBY-5522 -ref-2.diff, and DERBY-5522 -ref-2.zip. For this iteration, the zip files contain only the topics that changed as a result of the latest review.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kim. The latest tranche of changes looks good to me. +1

          Show
          Rick Hillegas added a comment - Thanks, Kim. The latest tranche of changes looks good to me. +1
          Hide
          Kim Haase added a comment -

          Thank you, Rick! I'm committing the two patches separately.

          Committed patch DERBY-5522-devguide-3.diff to documentation trunk at revision 1304566.

          Show
          Kim Haase added a comment - Thank you, Rick! I'm committing the two patches separately. Committed patch DERBY-5522 -devguide-3.diff to documentation trunk at revision 1304566.
          Hide
          Kim Haase added a comment -

          Committed patch DERBY-5522-ref-2.diff to documentation trunk at revision 1304569.

          If more changes are needed, please let me know. BTW, I noticed that those two new properties mentioned in the spec (derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations) are part of another issue, DERBY-5550, filed by Knut. I'll be getting to them.

          Show
          Kim Haase added a comment - Committed patch DERBY-5522 -ref-2.diff to documentation trunk at revision 1304569. If more changes are needed, please let me know. BTW, I noticed that those two new properties mentioned in the spec (derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations) are part of another issue, DERBY-5550 , filed by Knut. I'll be getting to them.
          Hide
          Kim Haase added a comment -

          Changes have appeared in the Latest Alpha Manuals.

          Show
          Kim Haase added a comment - Changes have appeared in the Latest Alpha Manuals.
          Hide
          Kim Haase added a comment -

          I just noticed a topic I missed, in the Admin Guide:

          http://db.apache.org/derby/docs/dev/adminguide/radminappsclientxmp.html

          This should be rewritten to use NATIVE authentication, I think. If I find any other Admin Guide topics that need work, I will include them too. I'll file a patch in due course.

          Show
          Kim Haase added a comment - I just noticed a topic I missed, in the Admin Guide: http://db.apache.org/derby/docs/dev/adminguide/radminappsclientxmp.html This should be rewritten to use NATIVE authentication, I think. If I find any other Admin Guide topics that need work, I will include them too. I'll file a patch in due course.
          Hide
          Kim Haase added a comment -

          The Admin Guide topic "Network client security" (http://db.apache.org/derby/docs/dev/adminguide/cadminappsclientsecurity.html) may need changes that depend on the resolution of DERBY-5651. Should we no longer document strong password substitution? It is also mentioned in "User authentication differences" (http://db.apache.org/derby/docs/dev/adminguide/cadminapps49914.html), "Enabling the encrypted user ID and password security mechanism" (http://db.apache.org/derby/docs/dev/adminguide/cadminapps811695.html), and "derby.drda.securityMechanism property" (http://db.apache.org/derby/docs/dev/adminguide/radmindrdasecmechanism.html).

          Thanks for any advice.

          Show
          Kim Haase added a comment - The Admin Guide topic "Network client security" ( http://db.apache.org/derby/docs/dev/adminguide/cadminappsclientsecurity.html ) may need changes that depend on the resolution of DERBY-5651 . Should we no longer document strong password substitution? It is also mentioned in "User authentication differences" ( http://db.apache.org/derby/docs/dev/adminguide/cadminapps49914.html ), "Enabling the encrypted user ID and password security mechanism" ( http://db.apache.org/derby/docs/dev/adminguide/cadminapps811695.html ), and "derby.drda.securityMechanism property" ( http://db.apache.org/derby/docs/dev/adminguide/radmindrdasecmechanism.html ). Thanks for any advice.
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          With the introduction of NATIVE authentication, we are urging applications to migrate away from the unsafe BUILTIN mechanism. Since password substitution can only be used with an authentication scheme which we think is unsafe, we are sending mixed signals by continuing to document password substitution. I think that we should remove all references to password substitution. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, With the introduction of NATIVE authentication, we are urging applications to migrate away from the unsafe BUILTIN mechanism. Since password substitution can only be used with an authentication scheme which we think is unsafe, we are sending mixed signals by continuing to document password substitution. I think that we should remove all references to password substitution. Thanks.
          Hide
          Kim Haase added a comment -

          Thanks, Rick. There is also a reference manual topic that covers password substitution, so my next patch will include that too.

          Show
          Kim Haase added a comment - Thanks, Rick. There is also a reference manual topic that covers password substitution, so my next patch will include that too.
          Hide
          Kim Haase added a comment -

          Attaching DERBY-5522-4.diff, DERBY-5522-4.stat, and DERBY-5522-4.zip, with the following changes:

          M src/adminguide/cadminapps811695.dita
          M src/adminguide/radminappsclientxmp.dita
          M src/adminguide/radmindrdasecmechanism.dita
          M src/adminguide/cadminapps811631.dita
          M src/adminguide/cadminapps49914.dita
          M src/adminguide/cadminappsclientsecurity.dita
          M src/ref/rrefattribsecmech.dita

          These include not only changing the "Network client driver examples" topic to mention NATIVE authentication, but also removing mention of the strong password substitution security mechanism from the admin and ref manuals.

          Show
          Kim Haase added a comment - Attaching DERBY-5522 -4.diff, DERBY-5522 -4.stat, and DERBY-5522 -4.zip, with the following changes: M src/adminguide/cadminapps811695.dita M src/adminguide/radminappsclientxmp.dita M src/adminguide/radmindrdasecmechanism.dita M src/adminguide/cadminapps811631.dita M src/adminguide/cadminapps49914.dita M src/adminguide/cadminappsclientsecurity.dita M src/ref/rrefattribsecmech.dita These include not only changing the "Network client driver examples" topic to mention NATIVE authentication, but also removing mention of the strong password substitution security mechanism from the admin and ref manuals.
          Hide
          Rick Hillegas added a comment -

          Thanks for these changes, Kim. They look good to me. +1

          Show
          Rick Hillegas added a comment - Thanks for these changes, Kim. They look good to me. +1
          Hide
          Kim Haase added a comment -

          Thanks, Rick!

          Committed patch DERBY-5522-4.diff to documentation trunk at revision 1326631.

          Show
          Kim Haase added a comment - Thanks, Rick! Committed patch DERBY-5522 -4.diff to documentation trunk at revision 1326631.
          Hide
          Kim Haase added a comment -

          Just for clarification, I had a typo in the commit message for revision 1326631 – it said DERBY-522 instead of DERBY-5522. My apologies.

          Show
          Kim Haase added a comment - Just for clarification, I had a typo in the commit message for revision 1326631 – it said DERBY-522 instead of DERBY-5522 . My apologies.
          Hide
          Knut Anders Hatlen added a comment -

          I've edited the commit message so that it has the correct issue number. For future reference, here's the command I used: svn propedit svn:log --revprop -r 1326631

          Show
          Knut Anders Hatlen added a comment - I've edited the commit message so that it has the correct issue number. For future reference, here's the command I used: svn propedit svn:log --revprop -r 1326631
          Hide
          Kim Haase added a comment -

          Thanks very much, Knut – I've made a note of the edit instructions.

          Show
          Kim Haase added a comment - Thanks very much, Knut – I've made a note of the edit instructions.
          Hide
          Kim Haase added a comment -

          Changes have appeared in Latest Alpha Manuals.

          Show
          Kim Haase added a comment - Changes have appeared in Latest Alpha Manuals.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development