Derby
  1. Derby
  2. DERBY-3282

Add a mechanism for managing users in Derby

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Services
    • Labels:
      None
    • Bug behavior facts:
      Security

      Description

      Currently, managing users in Derby is awkward. The BUILTIN mechanism seems appropriate for testing purposes, but has problems in a production setting. DERBY-866 describes part of a new mechanism for managing users. DERBY-866 may be part of the right solution--or it may not be. I think it would be worthwhile to step back from this issue and first describe at a high level what the customer experience should be. By introducing a new mechanism, we have the opportunity to think through the complete experience of user management. Here are my initial thoughts:

      1) This mechanism is mutually exclusive with the currently supported settings of the derby.authentication.provider property.

      2) There should be a super user who has the power to create, view, and drop users, including database owners. The design should let this super user delegate these powers to other users.

      3) In the new mechanism it is sufficient that user credentials are system-wide.

      4) Database owners should nevertheless have the power to state which usernames can connect to their databases. DBOs should also have the power to state who can shut down their databases. This mechanism should be extensible to managing other database-specific powers which fall outside the SQL spec. The design should let the DBO delegate these powers to other users.

      5) Users should be able to change their own credentials whenever they want.

      6) No password needed for this mechanism should be stored in plaintext anywhere on the system.

        Issue Links

          Activity

          Hide
          Øystein Grøvlen added a comment -

          I find it a bit difficult to relate to the concept system-wide users
          since I feel it is not very well defined what a Derby system actually
          is. Hence, I think we first need to step back even further and
          discuss the need for system-wide administration, in general. Why do
          we want system-wide administration? How will an administrator define a
          system and add databases to a system? What kind of properties should
          the databases of a system share?

          With the current flexibility where you are able to just copy a
          database to another location and keep on using it as before, it
          becomes very difficult to maintain information at system level. It
          also seems like it confuses many that users can be defined at two
          levels, system and database. I think the most important goal is to
          make Derby easy to get started with for new users. For that purpose,
          and for the purpose of single database administration, users at
          database level should suffice. However, the problem is that the
          current administration of built-in database users is awkward and
          different from what people are used to from other database systems. I
          think DERBY-866 shows how this situation can be improved.

          Then you have the minority of Derby users that administrates several
          databases and find it tedious (and maybe error-prone) to repeat the
          same administrative operations on all databases. They would probably
          like to have some tool that ease the administration of all databases
          of the system. A tool that let them create databases, add users at
          system level etc. It seems to me that the simplest would be to
          require that the only way to update information at system level is
          through this tool. To keep the current flexibility, if some of the
          system information is to affect a database, this tool will have to
          push the information into the database. For example, when a user is
          defined in this tool, the tool will create a built-in database level
          user in all the databases for which this user is to be known.

          While such a tool would be good to have, I feel it is much less
          important than to get a usable solution at database level. I also feel
          that it is pretty independent of what we do with respect to DERBY-866.

          Show
          Øystein Grøvlen added a comment - I find it a bit difficult to relate to the concept system-wide users since I feel it is not very well defined what a Derby system actually is. Hence, I think we first need to step back even further and discuss the need for system-wide administration, in general. Why do we want system-wide administration? How will an administrator define a system and add databases to a system? What kind of properties should the databases of a system share? With the current flexibility where you are able to just copy a database to another location and keep on using it as before, it becomes very difficult to maintain information at system level. It also seems like it confuses many that users can be defined at two levels, system and database. I think the most important goal is to make Derby easy to get started with for new users. For that purpose, and for the purpose of single database administration, users at database level should suffice. However, the problem is that the current administration of built-in database users is awkward and different from what people are used to from other database systems. I think DERBY-866 shows how this situation can be improved. Then you have the minority of Derby users that administrates several databases and find it tedious (and maybe error-prone) to repeat the same administrative operations on all databases. They would probably like to have some tool that ease the administration of all databases of the system. A tool that let them create databases, add users at system level etc. It seems to me that the simplest would be to require that the only way to update information at system level is through this tool. To keep the current flexibility, if some of the system information is to affect a database, this tool will have to push the information into the database. For example, when a user is defined in this tool, the tool will create a built-in database level user in all the databases for which this user is to be known. While such a tool would be good to have, I feel it is much less important than to get a usable solution at database level. I also feel that it is pretty independent of what we do with respect to DERBY-866 .
          Hide
          Rick Hillegas added a comment -

          Thanks for continuing the conversation about this topic, Øystein. Yes indeed, let's step further back!

          I agree that the definition of a Derby system is not what you might want. In practical terms it is something like this: the set of Derby resources managed by a single VM relying on a single set of system properties and a single master property file. It is not a set of Derby databases, which might be what you want. Multiple Derby systems could run on a single machine and, in the worst case, a given database could drift back and forth between two systems, depending on which system touches it first after a reboot.

          I understand the appeal of moving databases from one location to another, maybe even between Derby systems. I think that this is particularly appealing for single-user, shrink-wrapped applications. However, there are already limitations on this useful feature. For instance, important aspects of a database's behavior may be governed by the master property file and this does not live in the database's directory. In addition, a database is sensitive to the version of Derby which created it. You can't move a 10.3 database into a Derby system which is running 10.2. In order to move databases safely, you may also have to move some of the "system" context.

          Unfortunately, some authentication is already part of this "system" context. That is because you need system-wide credentials in order to create databases and bring down the "system". It seems awkward to me that you technically need one set of credentials in order to perform these system tasks and another set of credentials in order to perform work in a database. In practice, I seriously doubt that there are many (or even any) Derby applications which use different credentials for system-wide vs. database-specific tasks. Good evidence for this is provided I think by the fact that no production user has tripped across DERBY-3271 yet.

          In fact, the system-wide credentials which you use to create a database MUST BE the very same database-specific credentials which you use to do work in the database, at least initially. This is because database creation actually involves two authentication passes. In the first pass, you authenticate at the system level in order to create the system tables on disk. In the second pass, you then authenticate (using the same credentials) at the database level in order to get a connection to the newly created database. I don't believe that this coupling of the two credential schemes is documented anywhere. It is an unwritten assumption about the way that Derby authentication behaves.

          I think it would be confusing and awkward to require customers to operate two different mechanisms for administering users: one mechanism for administering users at the system level and another mechanism for administering users at the database level.

          For the record, I don't have any quarrel with the syntax described in DERBY-866. I do have reservations about the database-specific scope of the syntax and about the requirement that system-wide credentials be stored in the master property file.

          Show
          Rick Hillegas added a comment - Thanks for continuing the conversation about this topic, Øystein. Yes indeed, let's step further back! I agree that the definition of a Derby system is not what you might want. In practical terms it is something like this: the set of Derby resources managed by a single VM relying on a single set of system properties and a single master property file. It is not a set of Derby databases, which might be what you want. Multiple Derby systems could run on a single machine and, in the worst case, a given database could drift back and forth between two systems, depending on which system touches it first after a reboot. I understand the appeal of moving databases from one location to another, maybe even between Derby systems. I think that this is particularly appealing for single-user, shrink-wrapped applications. However, there are already limitations on this useful feature. For instance, important aspects of a database's behavior may be governed by the master property file and this does not live in the database's directory. In addition, a database is sensitive to the version of Derby which created it. You can't move a 10.3 database into a Derby system which is running 10.2. In order to move databases safely, you may also have to move some of the "system" context. Unfortunately, some authentication is already part of this "system" context. That is because you need system-wide credentials in order to create databases and bring down the "system". It seems awkward to me that you technically need one set of credentials in order to perform these system tasks and another set of credentials in order to perform work in a database. In practice, I seriously doubt that there are many (or even any) Derby applications which use different credentials for system-wide vs. database-specific tasks. Good evidence for this is provided I think by the fact that no production user has tripped across DERBY-3271 yet. In fact, the system-wide credentials which you use to create a database MUST BE the very same database-specific credentials which you use to do work in the database, at least initially. This is because database creation actually involves two authentication passes. In the first pass, you authenticate at the system level in order to create the system tables on disk. In the second pass, you then authenticate (using the same credentials) at the database level in order to get a connection to the newly created database. I don't believe that this coupling of the two credential schemes is documented anywhere. It is an unwritten assumption about the way that Derby authentication behaves. I think it would be confusing and awkward to require customers to operate two different mechanisms for administering users: one mechanism for administering users at the system level and another mechanism for administering users at the database level. For the record, I don't have any quarrel with the syntax described in DERBY-866 . I do have reservations about the database-specific scope of the syntax and about the requirement that system-wide credentials be stored in the master property file.
          Hide
          Øystein Grøvlen added a comment -

          > Rick Hillegas commented on DERBY-3282:
          > --------------------------------------
          > Thanks for continuing the conversation about this topic,
          > Øystein. Yes indeed, let's step further back!
          >
          > I agree that the definition of a Derby system is not what you might
          > want. In practical terms it is something like this: the set of Derby
          > resources managed by a single VM relying on a single set of system
          > properties and a single master property file. It is not a set of
          > Derby databases, which might be what you want. Multiple Derby
          > systems could run on a single machine and, in the worst case, a
          > given database could drift back and forth between two systems,
          > depending on which system touches it first after a reboot.

          What you are saying is that a Derby system is basically defined by the
          derby.properties file that is read at start-up. It is not quite clear
          to me what are the implications of such a definition, but it seems to
          imply that a system is just something that is just defined at
          run-time. If you stop the system and later start a system based on
          the same property file, is that the same system or a different system?
          I guess that two concurrent Derby instances that have been started
          with the same property file, would be viewed as two different systems.
          Or maybe it is it two instances of the same system? As you can tell,
          it is still not quite clear to me what a Derby system is.-

          It seems like the definition is made from a technical viewpoint more
          than from a user's viewpoint since it is defined in the context of
          program artifacts and not based on how it is to be used. I think we
          need to establish concepts which reflects the user's need, and not
          just are a result of how Derby is implemented.

          >
          > I understand the appeal of moving databases from one location to
          > another, maybe even between Derby systems. I think that this is
          > particularly appealing for single-user, shrink-wrapped
          > applications. However, there are already limitations on this useful
          > feature. For instance, important aspects of a database's behavior
          > may be governed by the master property file and this does not live
          > in the database's directory. In addition, a database is sensitive to
          > the version of Derby which created it. You can't move a 10.3
          > database into a Derby system which is running 10.2. In order to move
          > databases safely, you may also have to move some of the "system"
          > context.

          I acknowledge that just copying the database will not necessarily give
          you the same behavior, and I think that is OK as long as we are
          talking about system aspects like the size of the page cache.
          However, if there we are taliking about properties that change the way
          data may be observed, I think it needs to be stored a the database
          level. This includes user level access.

          > Unfortunately, some authentication is already part of this "system"
          > context. That is because you need system-wide credentials in order
          > to create databases and bring down the "system". It seems awkward to
          > me that you technically need one set of credentials in order to
          > perform these system tasks and another set of credentials in order
          > to perform work in a database. In practice, I seriously doubt that
          > there are many (or even any) Derby applications which use different
          > credentials for system-wide vs. database-specific tasks. Good
          > evidence for this is provided I think by the fact that no production
          > user has tripped across DERBY-3271 yet.

          I agree, and should I guess, I would think that most use system-wide
          credentials because they are easy to work with. You can just edit the
          property file, and should you need users for another database, you can
          just copy the file and modify it if needed. Working with
          database-specific users are a more tedious and involves SQL procedures
          with long names. In other words, user data in system.properties is a
          way of administer users that can be reused for other databases much
          like what I discussed in my previous post. (One of the points of the
          proposal in this JIRA seems to be to restrict this easy manipulation
          of user data.)

          The recent work on security has created another need for system-level
          users in that authentication is used to limit what can be performed at a
          system level (create databases, shutdown system). As far as I can
          tell this is mostly relevant to network server configurations, since
          this does not prevent a local user from circumventing the security
          restrictions by starting his system with another set of configuration
          files. This make me wonder whether it would be better to rather talk
          about servers than systems in this context. The main goal seems to be
          to restrict what clients of servers will be able to do.

          > In fact, the system-wide credentials which you use to create a
          > database MUST BE the very same database-specific credentials which
          > you use to do work in the database, at least initially. This is
          > because database creation actually involves two authentication
          > passes. In the first pass, you authenticate at the system level in
          > order to create the system tables on disk. In the second pass, you
          > then authenticate (using the same credentials) at the database level
          > in order to get a connection to the newly created database. I don't
          > believe that this coupling of the two credential schemes is
          > documented anywhere. It is an unwritten assumption about the way
          > that Derby authentication behaves.
          >
          > I think it would be confusing and awkward to require customers to
          > operate two different mechanisms for administering users: one
          > mechanism for administering users at the system level and another
          > mechanism for administering users at the database level.

          I agree that this will be awkward, and what I suggest is that for
          those who want to do administration at system level, we should provide
          a tool that do the database level administration for them. E.g., if
          you add a user at system-level, the tool should make sure this user is
          added to all the databases the user should have access to.

          >
          > For the record, I don't have any quarrel with the syntax described
          > in DERBY-866. I do have reservations about the database-specific
          > scope of the syntax and about the requirement that system-wide
          > credentials be stored in the master property file.

          I think the database-specific scope is what makes it easy to
          understand and use.

          I am all for a better way of administering system-wide data, but I
          think that can be solved separately from DERBY-866.

          Show
          Øystein Grøvlen added a comment - > Rick Hillegas commented on DERBY-3282 : > -------------------------------------- > Thanks for continuing the conversation about this topic, > Øystein. Yes indeed, let's step further back! > > I agree that the definition of a Derby system is not what you might > want. In practical terms it is something like this: the set of Derby > resources managed by a single VM relying on a single set of system > properties and a single master property file. It is not a set of > Derby databases, which might be what you want. Multiple Derby > systems could run on a single machine and, in the worst case, a > given database could drift back and forth between two systems, > depending on which system touches it first after a reboot. What you are saying is that a Derby system is basically defined by the derby.properties file that is read at start-up. It is not quite clear to me what are the implications of such a definition, but it seems to imply that a system is just something that is just defined at run-time. If you stop the system and later start a system based on the same property file, is that the same system or a different system? I guess that two concurrent Derby instances that have been started with the same property file, would be viewed as two different systems. Or maybe it is it two instances of the same system? As you can tell, it is still not quite clear to me what a Derby system is.- It seems like the definition is made from a technical viewpoint more than from a user's viewpoint since it is defined in the context of program artifacts and not based on how it is to be used. I think we need to establish concepts which reflects the user's need, and not just are a result of how Derby is implemented. > > I understand the appeal of moving databases from one location to > another, maybe even between Derby systems. I think that this is > particularly appealing for single-user, shrink-wrapped > applications. However, there are already limitations on this useful > feature. For instance, important aspects of a database's behavior > may be governed by the master property file and this does not live > in the database's directory. In addition, a database is sensitive to > the version of Derby which created it. You can't move a 10.3 > database into a Derby system which is running 10.2. In order to move > databases safely, you may also have to move some of the "system" > context. I acknowledge that just copying the database will not necessarily give you the same behavior, and I think that is OK as long as we are talking about system aspects like the size of the page cache. However, if there we are taliking about properties that change the way data may be observed, I think it needs to be stored a the database level. This includes user level access. > Unfortunately, some authentication is already part of this "system" > context. That is because you need system-wide credentials in order > to create databases and bring down the "system". It seems awkward to > me that you technically need one set of credentials in order to > perform these system tasks and another set of credentials in order > to perform work in a database. In practice, I seriously doubt that > there are many (or even any) Derby applications which use different > credentials for system-wide vs. database-specific tasks. Good > evidence for this is provided I think by the fact that no production > user has tripped across DERBY-3271 yet. I agree, and should I guess, I would think that most use system-wide credentials because they are easy to work with. You can just edit the property file, and should you need users for another database, you can just copy the file and modify it if needed. Working with database-specific users are a more tedious and involves SQL procedures with long names. In other words, user data in system.properties is a way of administer users that can be reused for other databases much like what I discussed in my previous post. (One of the points of the proposal in this JIRA seems to be to restrict this easy manipulation of user data.) The recent work on security has created another need for system-level users in that authentication is used to limit what can be performed at a system level (create databases, shutdown system). As far as I can tell this is mostly relevant to network server configurations, since this does not prevent a local user from circumventing the security restrictions by starting his system with another set of configuration files. This make me wonder whether it would be better to rather talk about servers than systems in this context. The main goal seems to be to restrict what clients of servers will be able to do. > In fact, the system-wide credentials which you use to create a > database MUST BE the very same database-specific credentials which > you use to do work in the database, at least initially. This is > because database creation actually involves two authentication > passes. In the first pass, you authenticate at the system level in > order to create the system tables on disk. In the second pass, you > then authenticate (using the same credentials) at the database level > in order to get a connection to the newly created database. I don't > believe that this coupling of the two credential schemes is > documented anywhere. It is an unwritten assumption about the way > that Derby authentication behaves. > > I think it would be confusing and awkward to require customers to > operate two different mechanisms for administering users: one > mechanism for administering users at the system level and another > mechanism for administering users at the database level. I agree that this will be awkward, and what I suggest is that for those who want to do administration at system level, we should provide a tool that do the database level administration for them. E.g., if you add a user at system-level, the tool should make sure this user is added to all the databases the user should have access to. > > For the record, I don't have any quarrel with the syntax described > in DERBY-866 . I do have reservations about the database-specific > scope of the syntax and about the requirement that system-wide > credentials be stored in the master property file. I think the database-specific scope is what makes it easy to understand and use. I am all for a better way of administering system-wide data, but I think that can be solved separately from DERBY-866 .
          Hide
          Rick Hillegas added a comment -

          I agree that "network server" is often the meaning of "system".

          The Developer's Guide, in a section called "Derby system", has this to say:

          "A Derby database exists within a system.

          "A Derby system is a single instance of the Derby database engine and the environment in which it runs. It consists of a system directory, zero or more databases, and a system-wide configuration. The system directory contains any persistent system-wide configuration parameters, or properties, specific to that system in a properties file called derby.properties. This file is not automatically created; you must create it yourself.

          "The Derby system is not persistent; you must specify the location of the system directory at every startup."

          I think this definition could easily mislead and confuse people. For instance, after reading this description, you could come away with the idea that databases live inside systems, when in fact they can join and leave systems in non-deterministic ways. The wording around persistence is also confusing to me. However, this may very well be the most complete attempt to date to describe what a Derby system is.

          Here is an attempt to list the behaviors which are managed at the "system" level:

          1) Network management, including
          a) legal sockets
          b) DRDA configuration
          c) network encryption

          2) Safety and security management, including
          a) Server and engine shutdown.
          b) Database creation.
          c) System-wide authentication.
          d) Jar files that are eligible for use by all databases.
          e) Java security policy
          f) Protection against sharing a database between two systems (derby.database.forceDatabaseLock property
          g) Durability (derby.system.durability)

          3) Info log management

          4) Global defaults for a lot of database-specific behaviors

          From this list I would say that network and safety management are big concerns of the "system". If you consider that (1a) and (1c) have implications for safety-as do many of the defaults swept up in (4)-it seems to me that one of the big behaviors of the "system" is to configure a security domain.

          Show
          Rick Hillegas added a comment - I agree that "network server" is often the meaning of "system". The Developer's Guide, in a section called "Derby system", has this to say: "A Derby database exists within a system. "A Derby system is a single instance of the Derby database engine and the environment in which it runs. It consists of a system directory, zero or more databases, and a system-wide configuration. The system directory contains any persistent system-wide configuration parameters, or properties, specific to that system in a properties file called derby.properties. This file is not automatically created; you must create it yourself. "The Derby system is not persistent; you must specify the location of the system directory at every startup." I think this definition could easily mislead and confuse people. For instance, after reading this description, you could come away with the idea that databases live inside systems, when in fact they can join and leave systems in non-deterministic ways. The wording around persistence is also confusing to me. However, this may very well be the most complete attempt to date to describe what a Derby system is. Here is an attempt to list the behaviors which are managed at the "system" level: 1) Network management, including a) legal sockets b) DRDA configuration c) network encryption 2) Safety and security management, including a) Server and engine shutdown. b) Database creation. c) System-wide authentication. d) Jar files that are eligible for use by all databases. e) Java security policy f) Protection against sharing a database between two systems (derby.database.forceDatabaseLock property g) Durability (derby.system.durability) 3) Info log management 4) Global defaults for a lot of database-specific behaviors From this list I would say that network and safety management are big concerns of the "system". If you consider that (1a) and (1c) have implications for safety- as do many of the defaults swept up in (4) -it seems to me that one of the big behaviors of the "system" is to configure a security domain.
          Hide
          Rick Hillegas added a comment -

          Closing this issue. Marking it as a duplicate of DERBY-866. I think that the problem raised by this issue is largely addressed by NATIVE authentication.

          Show
          Rick Hillegas added a comment - Closing this issue. Marking it as a duplicate of DERBY-866 . I think that the problem raised by this issue is largely addressed by NATIVE authentication.

            People

            • Assignee:
              Unassigned
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development