text: Details
  - Type:
  Bug
  - Status: Closed
  - Priority:
  Major
  - Resolution: Fixed
  - Affects Version/s: 10.9.1.0
  -
  - Component/s: Documentation, Services
  - Labels:None
  Description.
  Activity
  Thanks, Rick and Dag.
  Committed patch
  DERBY-5747.diff to documentation trunk at revision 1338873.
  Thanks Kim. These changes look good to me. +1
  Thanks, Kim. Looks right to me; it reflects the current implementation, I think. +1 from me, but I hope Rick can also vet this.
  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!
  Thanks, Dag, that's a more helpful thing to emphasize. I'll work on a patch.
  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.
  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.
  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!
  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.
  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..
  If we decide to rename the NATIVE procedures, we should consider renaming SYSUSERS to be SYSPASSWORDS. That would further reduce the confusion.
  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.
  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
  Changes have appeared in Latest Alpha Manuals.