Derby
  1. Derby
  2. DERBY-2207

Improve usability of Derby's client/server security by implementing ANSI Roles

    Details

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

      Description

      Implementing ANSI Roles will make it easier to manage security for multi-user applications with high user turnover.

      1. spec.html
        57 kB
        Dag H. Wanvik
      2. spec.html
        57 kB
        Dag H. Wanvik
      3. spec.html
        57 kB
        Dag H. Wanvik
      4. spec.html
        56 kB
        Dag H. Wanvik
      5. spec.html
        56 kB
        Dag H. Wanvik
      6. spec.html
        43 kB
        Dag H. Wanvik
      7. spec.html
        43 kB
        Dag H. Wanvik
      8. spec.html
        42 kB
        Dag H. Wanvik
      9. spec.html
        42 kB
        Dag H. Wanvik
      10. spec.html
        41 kB
        Dag H. Wanvik
      11. spec.html
        40 kB
        Dag H. Wanvik
      12. spec.html
        40 kB
        Dag H. Wanvik
      13. spec.html
        34 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          Resolving; I don't intend to improve this functionality further for now.
          The present functionality should implement the specification, modulo bugs.

          Show
          Dag H. Wanvik added a comment - Resolving; I don't intend to improve this functionality further for now. The present functionality should implement the specification, modulo bugs.
          Hide
          Dag H. Wanvik added a comment -

          Uploading version 11 of the spec to cater for changes in DERBY-3930.

          Show
          Dag H. Wanvik added a comment - Uploading version 11 of the spec to cater for changes in DERBY-3930 .
          Hide
          Dag H. Wanvik added a comment -

          Uploaded version 10.0, adding VTI SYSCS_DIAG.CONTAINED_ROLES notes.

          Show
          Dag H. Wanvik added a comment - Uploaded version 10.0, adding VTI SYSCS_DIAG.CONTAINED_ROLES notes.
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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 .
          Hide
          Rick Hillegas added a comment -

          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

          Show
          Rick Hillegas added a comment - 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
          Hide
          Dag H. Wanvik added a comment -

          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).

          Show
          Dag H. Wanvik added a comment - 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 ).
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          Need to ensure CREATE ROLE PUBLIC is not allowed before any release. Last time I tried it worked.

          Show
          Daniel John Debrunner added a comment - Need to ensure CREATE ROLE PUBLIC is not allowed before any release. Last time I tried it worked.
          Hide
          Dag H. Wanvik added a comment -

          Updated specification to lift maximum role name identifier length to 128,
          and to reserve SYS prefix for role names.

          Show
          Dag H. Wanvik added a comment - Updated specification to lift maximum role name identifier length to 128, and to reserve SYS prefix for role names.
          Hide
          Dag H. Wanvik added a comment -

          > 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...

          Show
          Dag H. Wanvik added a comment - > 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...
          Hide
          Dag H. Wanvik added a comment -

          Uploaded revision 7.0 of the specification. Adds missing syntax alternative to SET ROLE; thanks, Dan!

          Show
          Dag H. Wanvik added a comment - Uploaded revision 7.0 of the specification. Adds missing syntax alternative to SET ROLE; thanks, Dan!
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          > 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?

          Show
          Dag H. Wanvik added a comment - > 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?
          Hide
          Daniel John Debrunner added a comment -

          > 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.

          Show
          Daniel John Debrunner added a comment - > 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.
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          > 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!

          Show
          Dag H. Wanvik added a comment - > 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!
          Hide
          Dag H. Wanvik added a comment -

          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?

          Show
          Dag H. Wanvik added a comment - 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?
          Hide
          Daniel John Debrunner added a comment -

          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?

          Show
          Daniel John Debrunner added a comment - 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?
          Hide
          Daniel John Debrunner added a comment -

          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?

          Show
          Daniel John Debrunner added a comment - 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?
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          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

          Show
          Dag H. Wanvik added a comment - 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
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          Uploading rev. 6.0 of the specification.

          Show
          Dag H. Wanvik added a comment - Uploading rev. 6.0 of the specification.
          Hide
          Dag H. Wanvik added a comment -

          Added DERBY-3327 for implementing an authorization stack to keep
          current role name, cf. discussion above.

          Show
          Dag H. Wanvik added a comment - Added DERBY-3327 for implementing an authorization stack to keep current role name, cf. discussion above.
          Hide
          Dag H. Wanvik added a comment -

          > Is setting isolation level currently persistent beyond a nested
          > connection?

          Tried this, and the answer is yes.

          Show
          Dag H. Wanvik added a comment - > Is setting isolation level currently persistent beyond a nested > connection? Tried this, and the answer is yes.
          Hide
          Dag H. Wanvik added a comment -

          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?

          Show
          Dag H. Wanvik added a comment - 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?
          Hide
          Daniel John Debrunner added a comment -

          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.

          Show
          Daniel John Debrunner added a comment - 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.
          Hide
          Dag H. Wanvik added a comment -

          New rev of specification, just minor changes.

          Show
          Dag H. Wanvik added a comment - New rev of specification, just minor changes.
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          Thanks, Dag. Crystal clear now!

          Show
          Rick Hillegas added a comment - Thanks, Dag. Crystal clear now!
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          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!

          Show
          Rick Hillegas added a comment - 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!
          Hide
          Dag H. Wanvik added a comment -

          Uploading revision 2.0 of the spec for roles. Hopefully this version
          is more readable the the first attempt.

          Show
          Dag H. Wanvik added a comment - Uploading revision 2.0 of the spec for roles. Hopefully this version is more readable the the first attempt.
          Hide
          Dag H. Wanvik added a comment -

          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.

          Show
          Dag H. Wanvik added a comment - 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.
          Hide
          Rick Hillegas added a comment -

          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!

          Show
          Rick Hillegas added a comment - 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!
          Hide
          Dag H. Wanvik added a comment -

          Uploading a first draft of the func spec. Please have a look!

          Show
          Dag H. Wanvik added a comment - Uploading a first draft of the func spec. Please have a look!
          Hide
          Dag H. Wanvik added a comment -

          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!

          Show
          Dag H. Wanvik added a comment - 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!
          Hide
          Dag H. Wanvik added a comment -

          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

          Show
          Dag H. Wanvik added a comment - 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
          Hide
          Andrew McIntyre added a comment -

          Unsetting Fix Version on unassigned issues.

          Show
          Andrew McIntyre added a comment - Unsetting Fix Version on unassigned issues.

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development