[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13274626#comment-13274626 ] Rick Hillegas commented on DERBY-5747: -- Thanks Kim. These changes look good to me. +1 > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: DERBY-5747.diff, repro2.sh, rrefnativedropuserproc.html > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13273664#comment-13273664 ] Dag H. Wanvik commented on DERBY-5747: -- Thanks, Kim. Looks right to me; it reflects the current implementation, I think. +1 from me, but I hope Rick can also vet this. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: DERBY-5747.diff, repro2.sh, rrefnativedropuserproc.html > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13273248#comment-13273248 ] Kim Haase commented on DERBY-5747: -- Thanks, Dag, that's a more helpful thing to emphasize. I'll work on a patch. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13272786#comment-13272786 ] Dag H. Wanvik commented on DERBY-5747: -- 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. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[
https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13272781#comment-13272781
]
Dag H. Wanvik commented on DERBY-5747:
--
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.
> Native user authentication: Docs do not describe what happens to schema and
> its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
> --
>
> Key: DERBY-5747
> URL: https://issues.apache.org/jira/browse/DERBY-5747
> Project: Derby
> Issue Type: Bug
> Components: Documentation, Services
>Affects Versions: 10.9.0.0
>Reporter: Dag H. Wanvik
>Assignee: Kim Haase
> Attachments: repro2.sh
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13272700#comment-13272700 ] Kim Haase commented on DERBY-5747: -- 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! > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13270557#comment-13270557 ] Rick Hillegas commented on DERBY-5747: -- 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. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13270403#comment-13270403 ] Dag H. Wanvik commented on DERBY-5747: -- 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(, ) where 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.. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik >Assignee: Kim Haase > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13269780#comment-13269780 ] Rick Hillegas commented on DERBY-5747: -- If we decide to rename the NATIVE procedures, we should consider renaming SYSUSERS to be SYSPASSWORDS. That would further reduce the confusion. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13269621#comment-13269621 ] Rick Hillegas commented on DERBY-5747: -- 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 > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13269582#comment-13269582 ] Dag H. Wanvik commented on DERBY-5747: -- 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. > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5747) Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
[ https://issues.apache.org/jira/browse/DERBY-5747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13269575#comment-13269575 ] Rick Hillegas commented on DERBY-5747: -- 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 > Native user authentication: Docs do not describe what happens to schema and > its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call > -- > > Key: DERBY-5747 > URL: https://issues.apache.org/jira/browse/DERBY-5747 > Project: Derby > Issue Type: Bug > Components: Documentation, Services >Affects Versions: 10.9.0.0 >Reporter: Dag H. Wanvik > Attachments: repro2.sh > > > 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. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
