You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2007/01/03 18:10:27 UTC
[jira] Created: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Improve usability of Derby's client/server security by implementing ANSI Roles
------------------------------------------------------------------------------
Key: DERBY-2207
URL: https://issues.apache.org/jira/browse/DERBY-2207
Project: Derby
Issue Type: New Feature
Components: Security, SQL
Reporter: Rick Hillegas
Fix For: 10.3.0.0
Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploaded version 10.0, adding VTI SYSCS_DIAG.CONTAINED_ROLES notes.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploading a new version of the specification, rev. 9.0:
Brought in line with final implementation.
Most sections were sections touched, changed tense from future to present.
Added design and implementation information.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-2207.
--------------------------------
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Fix For: 10.5.1.1
>
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563735#action_12563735 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
Minor comment on the spec, the syntax section for SET ROLE says it supports a value specification or NONE.
However the text indicates it *also* supports an identifier, the syntax section should be updated to reflect what is planned.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploaded revision 7.0 of the specification. Adds missing syntax alternative to SET ROLE; thanks, Dan!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
New rev of specification, just minor changes.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563653#action_12563653 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
As for the setting of NONE, please also see the discussion here: https://issues.apache.org/jira/browse/DERBY-3137?focusedCommentId=12540465#action_12540465
I think I agree that your reading is correct, but it seems restrictive..
I also notice that the corrigendum also now states in 18.3 (my emphasis):
"Set the *SQL-session role name* and the current role name for the current SQL-session context."
whereas previously it said:
"Set the current role name for the current SQL-session context."
Does this mean that the role set inside a stored procedure should be in effect also after returning? Or is it just semantic
finery?
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploading rev. 6.0 of the specification.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik resolved DERBY-2207.
----------------------------------
Resolution: Fixed
Fix Version/s: 10.5.0.0
Resolving; I don't intend to improve this functionality further for now.
The present functionality should implement the specification, modulo bugs.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Fix For: 10.5.0.0
>
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12524587 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
I have started working on roles for Derby. I plan to make a functional
specification based on a subset of the SQL standard. I will be uploading drafts, so I can get early feedback.
If anybody has an interest in participating in this effort, please let me know :)
Any spare cycles would be appreciated! Since roles builds on the privileges work done for 10.2, any help from people who know this part of Derby will be especially appreciated!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563673#action_12563673 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
Re: changing current role after a prepare of a statement.
I am working on code to register dependencies of prepared statements (or just the activation?) as well as triggers, views and constraints, of one (or more) roles being depended on (for the authorization check to succeed), cf. discussion in DERBY-3223.
I think the changing of the current role (or dropping of a role which the ps depends on) should invalidate the (activation of) the prepared statement
here, forcing a re-check at the second execute.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Thanks, Rick!
It is option a): "PUBLIC" means all authentication identifiers,
cf. section 12.3 GR 2:
"A <grantee> of PUBLIC denotes at all times a list of <grantee>s
containing all of the <authorization identifier>s in the
SQL-environment."
I added a sentence to explain PUBLIC on first reference to it,
and reworked the wording a bit in 5.8.
Uploaded a new revision.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563603#action_12563603 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
The SQL TECHNICAL CORRIGENDUM 1 section 18.3 changes the syntax rules for SET ROLE. My reading of it is that NONE can now only appear as a keyword, NONE as a value expression does not mean set the current role to nothing. Not sure what the code is doing at the moment, I couldn't see any processing for NONE.
For connection pooling I think that the reset of a connection from Derby's PooledConnection needs to reset the role to nothing. Is that being handled?
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561425#action_12561425 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
Thanks for looking at the specification again!
> Section 5.3) > (Implementation restriction) Currently in Derby, user
> identifier can be max 30 characters long. Until this restriction is
> lifted, roles will have the same limit,
> Can this restriction be explained? Since a role is not a user
> identifier, why should a limit on user identifier impact role name?
I think the only reason I inherited this restriction is that
the system tables represent GRANTEE/GRANTOR as VARCHAR(30), but I
could change that I guess? It may be only the documentation which says
max 30, I think the actual column is SQLIdentifier
(VARCHAR(255)). I'll see if I can remove this restriction from
roles.
>
> Section 6.1 The name authorization identifier name space issue or
> maybe section 5.4
>
> When granting a privilege can you specify the behaviour for
> roles/users. I think it is that if the grantee exists as a role then
> the privilege is granted to that role, otherwise grantee is treated
> as a user identifier. I think this falls out of the current grant
> implementation, ie. the grant doesn't actually care if grantee is a
> role name or a user name, it just updates the catalogs. Good to be
> explicit here.
Yes, this is how I understand it also. Will add some explicitness here :)
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12527561 ]
Rick Hillegas commented on DERBY-2207:
--------------------------------------
Thanks, Dag. Crystal clear now!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Fix For: 10.5.0.0
>
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526270 ]
Rick Hillegas commented on DERBY-2207:
--------------------------------------
Thanks for the great spec, Dag. I have a couple comments.
General - I think that it would be nice if the introductory paragraphs of section 5 spelled out the semantics of ANSI roles in greater detail. For instance, I think that something like the following is true, but the spec doesn't exactly say this:
"At any given time, a session has a user and a role associated with it. At that point in time, the session enjoys all of the privileges explicitly granted to the user plus the transitive closure of all privileges granted to the role and its ancestors in the role graph."
A related, recurrent usage confuses me. Throughout the spec, statements like the following appear: "the privilege is revoked from any user who has the role". I don't think that granting a role to a user results in the role's privileges being granted to the user. I don't think that you can revoke those privileges from the user--you can only revoke them from the role.
Also, it would be nice if divergences from the standard were highlighted somehow.
5.6 (Granting a role to a role) - I think that cycles are not allowed in the role graph. Do you agree?
5.7 (Revoking privileges from a role) - The phrasing confuses me (see my general comment above). I think what you are saying is that, after revoking privilege P from role A, then P is no longer enjoyed by a session operating as A or s one of its descendants in the role graph. Even this is not strictly speaking true, though--or so it seems to me. The session could still enjoy P if P is granted to some other ancestor of the current role.
I am afraid I became terribly muddled from paragraph 4 onward. It might help if you could tease apart the concepts of session, user, and role.
5.8 (Revoking a role from a user) - I got muddled in paragraph 2. Maybe teasing apart the concepts, again, would help.
5.9 (Revoking a role from a role) - I did not understand what was meant by saying that role A is also role C. I did not understand the reference to drop behavior in the previous section.
5.10 (Setting a role) - I recommend moving this section further up. I think that it will give the reader more context for understanding how a session enjoys privileges by changing role.
6.1 (The name authorization identifier name space issue) - There is lots of good discussion of the issues in this section. However, I did not come away with a clear picture of what behavior will be implemented.
6.2 & 5.4 - I get the sense that we may be diverging from the standard here. Is this because the current GRANT/REVOKE behavior diverges from the standard?
Would it be fair to say that the set of roles is determined by the following query "select distinct roleid from sys.sysroles where grantor='_SYSTEM'". This might argue for adding a secondary index on (grantor, roleid).
Thanks!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642996#action_12642996 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
I notice I forgot to answer Dan's comment:
> Need to ensure CREATE ROLE PUBLIC is not allowed before any release.
This has been fixed long ago with svn 655948 (DERBY-3333).
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12555545#action_12555545 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
Yes, that's how I read it too. It is not implemented yet; not sure how
to do it, lcc (LanguageConnectionContext) is shared between root and nested
connections, but seemed the natural place for holding current role.
The authorization stack that the standard prescribes would need to be
implemented somehow.
Btw, it would seem we need this for running with definer's right
(stored procedures) as well? (if we want to allow that in future, I
see vol 13, section 4.10 says its implementation defined.)
Is setting isolation level currently persistent beyond a nested
connection?
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563728#action_12563728 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
I meant using the existing prepared statement invalidation was too heavy for SET ROLE.
Something is needed to ensure security is maintained and that one cannot execute a statement that requires role R if R is not the current role.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553412 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
I believe that according to 4.27.3 of the SQL Standard that a SET ROLE within a routine should only take affect during the routine's execution.
One the routine returns the current role should revert to its previous value.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
New version (4.0) of the specification, added support for ? in prepared
statement for SET ROLE, similar to what exists for SET SCHEMA. Added
some small clarifications.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12555550#action_12555550 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
> Is setting isolation level currently persistent beyond a nested
> connection?
Tried this, and the answer is yes.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563707#action_12563707 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
> Is this some new mechanism, because today prepared statements are
> invalidated, not activations?
Yes, one I was contemplating..
> I think invalidating the prepared statements is a bad plan. A SET ROLE
> will become a common operation, especially for routines. Having a
> routine execution invalidate any prepared statements will cause
> tremendous performance problems as all users executing the routine
> will be constantly invalidating each other's plans.
Yes, this was my worry too, which is why I considered invalidating
just the activation.
> I agree dropping a role should perform the invalidations, but not
> resetting a role.
This seems right for persistent objects (view, triggers, constraints),
but in the case of the prepared statement (your example), it seems to
me be the role should still be in place when the execute is performed?
(although not for every cursor access :)
If so, some kind of invalidation seems called for...?
We could just wave our hands and say the check is only performed on
the first execute of a ps (when the checks are done for the activation
as it is created) I guess. Maybe that is acceptable although not
strictly compliant? Or does your interpretation lead you to believe it *is* compliant?
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik reassigned DERBY-2207:
------------------------------------
Assignee: Dag H. Wanvik
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12560002#action_12560002 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
Added DERBY-3327 for implementing an authorization stack to keep
current role name, cf. discussion above.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12527522 ]
Rick Hillegas commented on DERBY-2207:
--------------------------------------
Thanks for rev 2 of the spec, Dag. This is very clear now! I have a couple small remarks:
5.4 (Granting a privilege to a role) I became a little muddled reading this section. I think it would be easier to understand if the next to last paragraph read "grant privileges on that object to a role"
5.8 (Revoking privileges from a role) I became a little muddled by the reference to PUBLIC. Is PUBLIC semantically a) the union of all users, b) the root of the role graph, or c) both? I suspect it is (a). I think this section would be easier to read if it were phrased in terms of the privileges enjoyed by the session.
6.2 (Catalog changes) +1 to the ISDEF field!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526290 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
Thanks for the quick response, Rick!
I will answer your comments here for now and upload a new version as
soon as I have incorporated your suggestions.
>>>>> Rick Hillegas (JIRA) writes:
Rick> General - I think that it would be nice if the introductory
Rick> paragraphs of section 5 spelled out the semantics of ANSI roles
Rick> in greater detail. For instance, I think that something like the
Rick> following is true, but the spec doesn't exactly say this:
Rick>
Rick> "At any given time, a session has a user and a role associated
Rick> with it. At that point in time, the session enjoys all of the
Rick> privileges explicitly granted to the user plus the transitive
Rick> closure of all privileges granted to the role and its ancestors
Rick> in the role graph."
You are right, it will be good to spell out basic behavior better in
the introductory paragraph.
Rick>
Rick> A related, recurrent usage confuses me. Throughout the spec,
Rick> statements like the following appear: "the privilege is revoked
Rick> from any user who has the role". I don't think that granting a
Rick> role to a user results in the role's privileges being granted to
Rick> the user.
You are correct. A user session that has set the role, however, could
possibly lose that privilege from its set of current privileges. I
will try to find such occurences and clarify.
Rick> I don't think that you can revoke those privileges
Rick> from the user--you can only revoke them from the role.
Correct. Revoking a privilege from a role only revokes it from the
set of privileges granted to that role. Only a "revoke privilege from
user" statement will revoke privileges from a user.
Rick>
Rick> Also, it would be nice if divergences from the standard were highlighted somehow.
Agreed. I will do that.
Rick>
Rick>
Rick> 5.6 (Granting a role to a role) - I think that cycles are not
Rick> allowed in the role graph. Do you agree?
I think I say that in the first paragraph of 5.6 ? ".. can not contain
cycles.."
Rick>
Rick>
Rick> 5.7 (Revoking privileges from a role) - The phrasing confuses me
Rick> (see my general comment above). I think what you are saying is
Rick> that, after revoking privilege P from role A, then P is no
Rick> longer enjoyed by a session operating as A or s one of its
Rick> descendants in the role graph. Even this is not strictly
Rick> speaking true, though--or so it seems to me. The session could
Rick> still enjoy P if P is granted to some other ancestor of the
Rick> current role.
Again, I see I have used "user" when thinking of "user session", will
clarify throughout, thanks! What you say next is also correct.
Rick>
Rick> I am afraid I became terribly muddled from paragraph 4
Rick> onward. It might help if you could tease apart the concepts of
Rick> session, user, and role.
Rick>
Rick>
Rick> 5.8 (Revoking a role from a user) - I got muddled in paragraph
Rick> 2. Maybe teasing apart the concepts, again, would help.
I am afraid the lack of precision reflects my own process of digesting
this and trying to paraphrase the verbose semantic descriptions in the
standard down to something readable, Rick! ;) I will have another go
at this section!
Rick>
Rick>
Rick> 5.9 (Revoking a role from a role) - I did not understand what
Rick> was meant by saying that role A is also role C. I did not
Rick> understand the reference to drop behavior in the previous
Rick> section.
It should read "unless A is also contained in (not through B) another
role C".
Rick>
Rick>
Rick> 5.10 (Setting a role) - I recommend moving this section further
Rick> up. I think that it will give the reader more context for
Rick> understanding how a session enjoys privileges by changing role.
Good idea, thanks.
Rick>
Rick>
Rick> 6.1 (The name authorization identifier name space issue) - There
Rick> is lots of good discussion of the issues in this
Rick> section. However, I did not come away with a clear picture of
Rick> what behavior will be implemented.
Right, I have not decided. Suggestions are welcome!
Rick>
Rick>
Rick> 6.2 & 5.4 - I get the sense that we may be diverging from the
Rick> standard here. Is this because the current GRANT/REVOKE behavior
Rick> diverges from the standard?
Do you mean "5.2 No initial role"? This is according to the standard.
For 5.4, yes, I think this is a deviation, in the the dbo has
automatic power to gtant any privilege. I model this on the current
behavior (who can grant a privilege to a user: dbo + object owner).
Rick>
Rick> Would it be fair to say that the set of roles is determined by
Rick> the following query "select distinct roleid from sys.sysroles
Rick> where grantor='_SYSTEM'".
Yes.
Rick> This might argue for adding a secondary index on (grantor,
Rick> roleid).
Yes, I agree.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563655#action_12563655 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
> For connection pooling I think that the reset of a connection from Derby's PooledConnection needs to reset the role to nothing. Is that being handled?
I have not handled this area yet, it sounds reasonable. Thanks for the reminder!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643288#action_12643288 ]
Rick Hillegas commented on DERBY-2207:
--------------------------------------
Hi Dag,
Thanks for the revised spec. As I read section 5.9, I wondered if you could clarify what should happen in the following situation:
create role A;
create role B;
grant A to B;
grant B to FRED;
revoke A from FRED;
At this point should FRED be able to do the following:
1) "set role A"
2) "set role B" then access objects which need privileges granted to A?
Thanks,
-Rick
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561420#action_12561420 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
Couple of questions about section 5.3 of the spec.
Section 5.3) > (Implementation restriction) Currently in Derby, user identifier can be max 30 characters long. Until this restriction is lifted, roles will have the same limit,
Can this restriction be explained? Since a role is not a user identifier, why should a limit on user identifier impact role name?
Section 6.1 The name authorization identifier name space issue or maybe section 5.4
When granting a privilege can you specify the behaviour for roles/users. I think it is that if the grantee exists as a role then the privilege is granted to that role, otherwise grantee is treated as a user identifier. I think this falls out of the current grant implementation, ie. the grant doesn't actually care if grantee is a role name or a user name, it just updates the catalogs. Good to be explicit here.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12469543 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
I have made a Wiki page for this topic and added some thoughts from
a preliminary investigation. Please see:
http://wiki.apache.org/db-derby/SqlRoles
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploading revision 2.0 of the spec for roles. Hopefully this version
is more readable the the first attempt.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563682#action_12563682 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
> I think the changing of the current role should invalidate the (activation of) the prepared statement here, forcing a re-check at the second execute.
Is this some new mechanism, because today prepared statements are invalidated, not activations?
I think invalidating the prepared statements is a bad plan. A SET ROLE will become a common operation, especially for routines. Having a routine execution invalidate any prepared statements will cause tremendous performance problems as all users executing the routine will be constantly invalidating each other's plans.
I agree dropping a role should perform the invalidations, but not resetting a role.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploading a first draft of the func spec. Please have a look!
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Thanks for looking at the updated spec, Rick!
You can't revoke A from FRED because A was never granted to FRED.
A is only enabled for FRED due to it being contained in B. By the same token,
FRED can't set A, either.
Uploading the spec again, fixing some opaque phrases that Kim spotted working on DERBY-3193.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Uploading version 11 of the spec to cater for changes in DERBY-3930.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563677#action_12563677 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
Section 4.34.1.1 says the SQL-session role name is a property of the authorization stack and that is a property of the SQL-session context.
Section 10.4 says a routine invocation pushes a new SQL-session context.
So I think the role still needs to be reverted when a routine is completed.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: spec.html
Updated specification to lift maximum role name identifier length to 128,
and to reserve SYS prefix for role names.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-2207:
---------------------------------
Attachment: (was: spec.html)
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563790#action_12563790 ]
Dag H. Wanvik commented on DERBY-2207:
--------------------------------------
> I meant using the existing prepared statement invalidation was too
> heavy for SET ROLE. Something is needed to ensure security is
> maintained and that one cannot execute a statement that requires role
> R if R is not the current role.
Then we are in agreement :) What do you think of the idea of making is
possible to invalidate activations? Anyway, I'll keep looking for a good way to
solve this...
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12561472#action_12561472 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
The system columns for grantee/grantor are VARCHAR(128) - this was discussed during the grant/revoke implementation and I think the func spec was never updated which lead to the documentation being wrong.
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2207) Improve usability of Derby's
client/server security by implementing ANSI Roles
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563612#action_12563612 ]
Daniel John Debrunner commented on DERBY-2207:
----------------------------------------------
Another question around SET ROLE.
What happens to a connection's prepared statements that were prepared when a role was in effect and now the role is not in effect?
E.g.
SET ROLE PAYROLL_ADMIN
prepare statement P1 -- which requires role PAYROLL_ADMIN
execute P1
SET ROLE NONE
execute P1 -- what happens here?
> Improve usability of Derby's client/server security by implementing ANSI Roles
> ------------------------------------------------------------------------------
>
> Key: DERBY-2207
> URL: https://issues.apache.org/jira/browse/DERBY-2207
> Project: Derby
> Issue Type: New Feature
> Components: Security, SQL
> Reporter: Rick Hillegas
> Assignee: Dag H. Wanvik
> Attachments: spec.html, spec.html, spec.html, spec.html, spec.html, spec.html
>
>
> Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.