Derby
  1. Derby
  2. DERBY-5747

Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.9.1.0
    • Component/s: Documentation, Services
    • Labels:
      None

      Description

      Currently, the schema and the objects remain after the user is dropped, cf. repro2.sh attached.
      The authorization id of the schema of the dropped user is still that id (dangling) after DROP.
      Perhaps ownership should revert to the DBO when a user is dropped, or should DROP USER do a cascade delete?
      There is no way currently to change the ownership of the schema to another user.
      At the very least we should document what happens.

      1. repro2.sh
        0.7 kB
        Dag H. Wanvik
      2. DERBY-5747.diff
        0.9 kB
        Kim Haase
      3. rrefnativedropuserproc.html
        5 kB
        Kim Haase

        Issue Links

          Activity

          Dag H. Wanvik created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Attachment repro2.sh [ 12525837 ]
          Hide
          Rick Hillegas added a comment -

          Thanks for thinking about this issue, Dag. I agree that the reference material should say that SYSCS_DROP_USER drops credentials and does not drop the authorization id itself.

          Would it be less confusing if we renamed some of the NATIVE procedures:

          SYSCS_CREATE_PASSWORD
          SYSCS_DROP_PASSWORD

          rather than

          SYSCS_CREATE_USER
          SYSCS_DROP_USER

          The alternative names might set more reasonable expectations.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for thinking about this issue, Dag. I agree that the reference material should say that SYSCS_DROP_USER drops credentials and does not drop the authorization id itself. Would it be less confusing if we renamed some of the NATIVE procedures: SYSCS_CREATE_PASSWORD SYSCS_DROP_PASSWORD rather than SYSCS_CREATE_USER SYSCS_DROP_USER The alternative names might set more reasonable expectations. Thanks, -Rick
          Rick Hillegas made changes -
          Link This issue relates to DERBY-866 [ DERBY-866 ]
          Hide
          Dag H. Wanvik added a comment -

          I guess I was thinking that we wanted to make native users more "solid" than the present mechanisms. Note: bogus user ids can also be introduced even with native authenticaion, e.g. by the DBO creating a schema and setting ownership to some non-existing user. Let me think about this some more.

          Show
          Dag H. Wanvik added a comment - I guess I was thinking that we wanted to make native users more "solid" than the present mechanisms. Note: bogus user ids can also be introduced even with native authenticaion, e.g. by the DBO creating a schema and setting ownership to some non-existing user. Let me think about this some more.
          Hide
          Rick Hillegas added a comment -

          I think we should stay focused on the issue of credentials management and not veer off into the management of authorization ids. The term "user" covers both topics. A couple random thoughts:

          1) Right now, the DBO can disable an account by dropping its credentials. The DBO may need to disable an account until a security threat is cleared. You don't want the account's data to disappear in this situation.

          2) Cascaded drop of an authorization id is a fair-sized project. It implies cascaded DROP SCHEMA. That, in turn, implies implementing cascade semantics for all statements which DROP objects.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - I think we should stay focused on the issue of credentials management and not veer off into the management of authorization ids. The term "user" covers both topics. A couple random thoughts: 1) Right now, the DBO can disable an account by dropping its credentials. The DBO may need to disable an account until a security threat is cleared. You don't want the account's data to disappear in this situation. 2) Cascaded drop of an authorization id is a fair-sized project. It implies cascaded DROP SCHEMA. That, in turn, implies implementing cascade semantics for all statements which DROP objects. Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          If we decide to rename the NATIVE procedures, we should consider renaming SYSUSERS to be SYSPASSWORDS. That would further reduce the confusion.

          Show
          Rick Hillegas added a comment - If we decide to rename the NATIVE procedures, we should consider renaming SYSUSERS to be SYSPASSWORDS. That would further reduce the confusion.
          Kim Haase made changes -
          Assignee Kim Haase [ chaase3 ]
          Hide
          Dag H. Wanvik added a comment -

          I agree 1) is a valid use case, although I'd probably prefer something like DISABLE_USER for such a functionality. Understood, a cascading delete is non-trivial. Options: introduce an extra argument to DROP_:USER(<user>, <mode>) where <mode> could be CASCADE later, or for now, KEEP to let schema(s) owned by the user hang around. A new ALTER SCHEMA could be used for that purpose later.
          I think the new mechanism should be viewed as more than just adding and dropping passwords, although that's mainly what the current code does in n addition to introducing a user auth id repository. But I think we should try to design this in such a way that we can extend the functionality in a later increment to encompass management of users in a broader sense. If so, I think we should retain the syntax CREATE_USER..

          Show
          Dag H. Wanvik added a comment - I agree 1) is a valid use case, although I'd probably prefer something like DISABLE_USER for such a functionality. Understood, a cascading delete is non-trivial. Options: introduce an extra argument to DROP_:USER(<user>, <mode>) where <mode> could be CASCADE later, or for now, KEEP to let schema(s) owned by the user hang around. A new ALTER SCHEMA could be used for that purpose later. I think the new mechanism should be viewed as more than just adding and dropping passwords, although that's mainly what the current code does in n addition to introducing a user auth id repository. But I think we should try to design this in such a way that we can extend the functionality in a later increment to encompass management of users in a broader sense. If so, I think we should retain the syntax CREATE_USER..
          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. Can you list other uses of SYSUSERS which you think we may want to explore in the future (probably not in 10.9)? This may help us understand whether there are some cheap changes which we can make in 10.9 in order to support those later extensions. Thanks.

          Show
          Rick Hillegas added a comment - Thanks, Dag. Can you list other uses of SYSUSERS which you think we may want to explore in the future (probably not in 10.9)? This may help us understand whether there are some cheap changes which we can make in 10.9 in order to support those later extensions. Thanks.
          Hide
          Kim Haase added a comment -

          I am trying to figure out what exactly to tell people. Should they be careful not to drop a user permanently until after they have removed the database objects owned by that user, because otherwise no one will be able to access the objects?

          I gather we're not actually changing any table/procedure names or any Derby behavior at this point.

          Thanks!

          Show
          Kim Haase added a comment - I am trying to figure out what exactly to tell people. Should they be careful not to drop a user permanently until after they have removed the database objects owned by that user, because otherwise no one will be able to access the objects? I gather we're not actually changing any table/procedure names or any Derby behavior at this point. Thanks!
          Hide
          Dag H. Wanvik added a comment -

          Rick, we probably should use SYSUSERS to vet new roles as well to avoid collisions: roles names and user names are in the same SQL namespace. Other future possiblities include
          usage statistics, foreign key for action trail logging ("who changed this?"), password history.

          Show
          Dag H. Wanvik added a comment - Rick, we probably should use SYSUSERS to vet new roles as well to avoid collisions: roles names and user names are in the same SQL namespace. Other future possiblities include usage statistics, foreign key for action trail logging ("who changed this?"), password history.
          Hide
          Dag H. Wanvik added a comment -

          Kim, at least the user should know the user's schemas' data won't be removed. The data can still be accessed by the DBO (or others GRANTed access), or by recreating the user.

          Show
          Dag H. Wanvik added a comment - Kim, at least the user should know the user's schemas' data won't be removed. The data can still be accessed by the DBO (or others GRANTed access), or by recreating the user.
          Hide
          Kim Haase added a comment -

          Thanks, Dag, that's a more helpful thing to emphasize. I'll work on a patch.

          Show
          Kim Haase added a comment - Thanks, Dag, that's a more helpful thing to emphasize. I'll work on a patch.
          Hide
          Kim Haase added a comment -

          Attaching DERBY-5747.diff and rrefnativedropuserproc.html, which adds a new paragraph to the topic on SYSCS_UTIL.SYSCS_DROP_USER:

          M src/ref/rrefnativedropuserproc.dita

          Thanks in advance for comments!

          Show
          Kim Haase added a comment - Attaching DERBY-5747 .diff and rrefnativedropuserproc.html, which adds a new paragraph to the topic on SYSCS_UTIL.SYSCS_DROP_USER: M src/ref/rrefnativedropuserproc.dita Thanks in advance for comments!
          Kim Haase made changes -
          Attachment DERBY-5747.diff [ 12526531 ]
          Attachment rrefnativedropuserproc.html [ 12526532 ]
          Kim Haase made changes -
          Issue & fix info Patch Available [ 10102 ]
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim. Looks right to me; it reflects the current implementation, I think. +1 from me, but I hope Rick can also vet this.

          Show
          Dag H. Wanvik added a comment - Thanks, Kim. Looks right to me; it reflects the current implementation, I think. +1 from me, but I hope Rick can also vet this.
          Hide
          Rick Hillegas added a comment -

          Thanks Kim. These changes look good to me. +1

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

          Thanks, Rick and Dag.

          Committed patch DERBY-5747.diff to documentation trunk at revision 1338873.

          Show
          Kim Haase added a comment - Thanks, Rick and Dag. Committed patch DERBY-5747 .diff to documentation trunk at revision 1338873.
          Kim Haase made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Issue & fix info Patch Available [ 10102 ]
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Resolution Fixed [ 1 ]
          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.
          Kim Haase made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12666246 ] Default workflow, editable Closed status [ 12797054 ]

            People

            • Assignee:
              Kim Haase
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development