Details

    • Task
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 10.5.1.1
    • Documentation
    • None

    Attachments

      1. DERBY-3193-4.zip
        19 kB
        Camilla Haase
      2. DERBY-3193-4.stat
        0.2 kB
        Camilla Haase
      3. DERBY-3193-4.diff
        9 kB
        Camilla Haase
      4. DERBY-3193-3.zip
        67 kB
        Camilla Haase
      5. DERBY-3193-3.diff
        83 kB
        Camilla Haase
      6. DERBY-3193-2.zip
        66 kB
        Camilla Haase
      7. DERBY-3193-2.stat
        1 kB
        Camilla Haase
      8. DERBY-3193-2.diff
        73 kB
        Camilla Haase
      9. DERBY-3193.zip
        44 kB
        Camilla Haase
      10. DERBY-3193.stat
        0.7 kB
        Camilla Haase
      11. DERBY-3193.diff
        46 kB
        Camilla Haase
      12. derby3193-tmp.stat
        0.2 kB
        Dag H. Wanvik
      13. derby3193-tmp.diff
        15 kB
        Dag H. Wanvik

      Issue Links

        Activity

          chaase3 Camilla Haase added a comment -

          It now looks as if SQL Roles will be documented at 10.5, not 10.4.

          chaase3 Camilla Haase added a comment - It now looks as if SQL Roles will be documented at 10.5, not 10.4.
          chaase3 Camilla Haase added a comment -

          The following changes will need to be made to the Reference Manual, Tools Guide, and Developer's Guide. There are a couple of questions at the end asking for clarifications of some of the Roles spec language.

          Ref Manual

          Information mostly comes from section 4 of the Roles spec, unless otherwise stated.

          rrefrauthid.html (AuthorizationIdentifier): Update to include a role as well as a user as a valid identifier.

          New topic: add CURRENT_ROLE built-in function.

          rrefsqlj30540.html (column-definition): Update "Column default" section to include CURRENT_ROLE in the list of values for DefaultConstantExpression. Note: the Roles spec also mentions the ALTER TABLE topic, but this topic points to rrefsqlj30540.html in a couple of places; it doesn't seem that the topic itself needs any fixes.

          rrefsqljgrant.html (GRANT statement): Add syntax for granting a role to a grantee. Under "grantees" section, make "authorization ID" a link to the AuthorizationIdentifier section? Also change text to mention roles as well as users? And add examples of GRANT statements for roles.

          New topic: add CREATE ROLE statement.

          New topic: add DROP ROLE statement.

          rrefsqljrevoke.html (REVOKE statement): Under "grantees" section, make "authorization ID" a link to the AuthorizationIdentifier section? Also change text to mention roles as well as users? And add an example of a REVOKE statement for a role.

          New topic: add SET ROLE statement.

          rrefkeywords29722.html (SQL reserved words): Add ROLE to list. (Oddly, CURRENT_ROLE is already on the list.)

          rrefsqlj31580.html (CREATE SCHEMA statement): add note that "AUTHORIZATION user-name" in the syntax really does mean that only a user, not a role, can create a schema. (section 5.12 of roles spec)

          rrefsistabssyscolperms.html (SYSCOLPERMS system table): for the GRANTEE row, change "user" to "user or role". (Section 6.1)

          rrefsistabssysroutineperms.html (SYSROUTINEPERMS system table): ditto.
          rrefsistabssystableperms.html (SYSTABLEPERMS system table): ditto.

          New topic: add SYSROLES system table. Has primary key and unique key but no foreign key. (Section 6.1)

          Tools Guide:

          ctoolsgenddldb.html (Generating the DDL for a database, under dblook): Add roles to the list of objects? (Section 6.4)

          rtoolsijcomrefshow.html (Show command): Add descriptions of the commands commands "show roles", "show settable_roles" and "show enabled_roles". (Section 8)

          Developer's Guide:

          cdevcsecure866060.html (Setting the SQL standard authorization mode): Add subsection explaining roles. (Section 8)


          Questions:

          In section 5.6, the second paragraph is a bit confusing. Does "the latter" refer to "all the privileges granted to the current role and to the roles contained in the current role"? And should "the union of privileges roles" be just "the union of privileges"? And is the rest of the sentence correct?

          In section 5.8, the second sentence is confusing:

          "If the role loses that privilege, and a session that has a current role which is that role or a role that contains that role, the session may lose that privilege, unless it available the current user, or to PUBLIC or another role contained in the current role."

          Should it say the following instead?

          "If the role loses that privilege, and a session has a current role which is that role or a role that contains that role, the session may lose that privilege, unless it is available to the current user, or to PUBLIC or another role contained in the current role."

          chaase3 Camilla Haase added a comment - The following changes will need to be made to the Reference Manual, Tools Guide, and Developer's Guide. There are a couple of questions at the end asking for clarifications of some of the Roles spec language. Ref Manual Information mostly comes from section 4 of the Roles spec, unless otherwise stated. rrefrauthid.html (AuthorizationIdentifier): Update to include a role as well as a user as a valid identifier. New topic: add CURRENT_ROLE built-in function. rrefsqlj30540.html (column-definition): Update "Column default" section to include CURRENT_ROLE in the list of values for DefaultConstantExpression. Note: the Roles spec also mentions the ALTER TABLE topic, but this topic points to rrefsqlj30540.html in a couple of places; it doesn't seem that the topic itself needs any fixes. rrefsqljgrant.html (GRANT statement): Add syntax for granting a role to a grantee. Under "grantees" section, make "authorization ID" a link to the AuthorizationIdentifier section? Also change text to mention roles as well as users? And add examples of GRANT statements for roles. New topic: add CREATE ROLE statement. New topic: add DROP ROLE statement. rrefsqljrevoke.html (REVOKE statement): Under "grantees" section, make "authorization ID" a link to the AuthorizationIdentifier section? Also change text to mention roles as well as users? And add an example of a REVOKE statement for a role. New topic: add SET ROLE statement. rrefkeywords29722.html (SQL reserved words): Add ROLE to list. (Oddly, CURRENT_ROLE is already on the list.) rrefsqlj31580.html (CREATE SCHEMA statement): add note that "AUTHORIZATION user-name" in the syntax really does mean that only a user, not a role, can create a schema. (section 5.12 of roles spec) rrefsistabssyscolperms.html (SYSCOLPERMS system table): for the GRANTEE row, change "user" to "user or role". (Section 6.1) rrefsistabssysroutineperms.html (SYSROUTINEPERMS system table): ditto. rrefsistabssystableperms.html (SYSTABLEPERMS system table): ditto. New topic: add SYSROLES system table. Has primary key and unique key but no foreign key. (Section 6.1) Tools Guide: ctoolsgenddldb.html (Generating the DDL for a database, under dblook): Add roles to the list of objects? (Section 6.4) rtoolsijcomrefshow.html (Show command): Add descriptions of the commands commands "show roles", "show settable_roles" and "show enabled_roles". (Section 8) Developer's Guide: cdevcsecure866060.html (Setting the SQL standard authorization mode): Add subsection explaining roles. (Section 8) Questions: In section 5.6, the second paragraph is a bit confusing. Does "the latter" refer to "all the privileges granted to the current role and to the roles contained in the current role"? And should "the union of privileges roles" be just "the union of privileges"? And is the rest of the sentence correct? In section 5.8, the second sentence is confusing: "If the role loses that privilege, and a session that has a current role which is that role or a role that contains that role, the session may lose that privilege, unless it available the current user, or to PUBLIC or another role contained in the current role." Should it say the following instead? "If the role loses that privilege, and a session has a current role which is that role or a role that contains that role, the session may lose that privilege, unless it is available to the current user, or to PUBLIC or another role contained in the current role."
          dagw Dag H. Wanvik added a comment -

          Thanks for looking at this, Kim!

          > In section 5.6, the second paragraph is a bit confusing. Does "the
          > latter" refer to "all the privileges granted to the current role and
          > to the roles contained in the current role"? And should "the union of
          > privileges roles" be just "the union of privileges"? And is the rest
          > of the sentence correct?

          Right, "latter" is ambiguous here. I'll try to phrase it differently:

          When a role is set for a session, the session has a set of privileges
          which is the union of

          • the privileges granted directly to the current user
          • the privileges granted to PUBLIC
          • the privileges granted to the current role and to roles contained in
            the current role.

          The term "contained" needs to be explained somewhere and linked to
          from usage sites, I think:

          "A role A is contained in another role B when A is granted to B, or A
          is contained in a role C which is granted to B" (recursive definition).

          > In section 5.8, the second sentence is confusing:

          > "If the role loses that privilege, and a session has a current
          > role which is that role or a role that contains that role, the session
          > may lose that privilege, unless it available the current user, or to
          > PUBLIC or another role contained in the current role."

          Indeed pretty opaque Another go:

          "If the role loses that privilege, and a session has a current
          role which is that role or a role that contains that role, the session
          will lose that privilege, unless:

          • it is granted directly to the current user, or
          • it is granted to PUBLIC, or
          • it is also granted to another role in the set of the current role and its
            contained roles.
          dagw Dag H. Wanvik added a comment - Thanks for looking at this, Kim! > In section 5.6, the second paragraph is a bit confusing. Does "the > latter" refer to "all the privileges granted to the current role and > to the roles contained in the current role"? And should "the union of > privileges roles" be just "the union of privileges"? And is the rest > of the sentence correct? Right, "latter" is ambiguous here. I'll try to phrase it differently: When a role is set for a session, the session has a set of privileges which is the union of the privileges granted directly to the current user the privileges granted to PUBLIC the privileges granted to the current role and to roles contained in the current role. The term "contained" needs to be explained somewhere and linked to from usage sites, I think: "A role A is contained in another role B when A is granted to B, or A is contained in a role C which is granted to B" (recursive definition). > In section 5.8, the second sentence is confusing: > "If the role loses that privilege, and a session has a current > role which is that role or a role that contains that role, the session > may lose that privilege, unless it available the current user, or to > PUBLIC or another role contained in the current role." Indeed pretty opaque Another go: "If the role loses that privilege, and a session has a current role which is that role or a role that contains that role, the session will lose that privilege, unless: it is granted directly to the current user, or it is granted to PUBLIC, or it is also granted to another role in the set of the current role and its contained roles.
          dagw Dag H. Wanvik added a comment -

          > rrefkeywords29722.html (SQL reserved words): Add ROLE to
          > list. (Oddly, CURRENT_ROLE is already on the list.)

          I added this for 10.4 since the parser had been modified although the
          roles feature did not go in at that time. I don't think ROLE should be
          added, since it is strangely enough not a reserved keyword, just a
          keyword according to the standard, cf. SQL 2003, section 5.2: the
          production "<non-reserved word>" contains "ROLE", whereas the production
          "<reserved word>" contains both "NONE" and "CURRENT_ROLE". (which is
          the way I implemented it)

          dagw Dag H. Wanvik added a comment - > rrefkeywords29722.html (SQL reserved words): Add ROLE to > list. (Oddly, CURRENT_ROLE is already on the list.) I added this for 10.4 since the parser had been modified although the roles feature did not go in at that time. I don't think ROLE should be added, since it is strangely enough not a reserved keyword, just a keyword according to the standard, cf. SQL 2003, section 5.2: the production "<non-reserved word>" contains "ROLE", whereas the production "<reserved word>" contains both "NONE" and "CURRENT_ROLE". (which is the way I implemented it)
          dagw Dag H. Wanvik added a comment - - edited

          Kim, uploading my some drafts I did, feel free to use or rework as you think best.

          Another thing: The exisiting docs use "AuthorizationIdentifier"/"Authorization ID"/"authorization identifier" more or less synonymously with "user name"/"user-name"/"userName"/"user ID""user id" (a bit of a mess). We could tighten up that usage throughout, or introduce role name explicitly when allowed, not sure what is best.

          dagw Dag H. Wanvik added a comment - - edited Kim, uploading my some drafts I did, feel free to use or rework as you think best. Another thing: The exisiting docs use "AuthorizationIdentifier"/"Authorization ID"/"authorization identifier" more or less synonymously with "user name"/"user-name"/"userName"/"user ID""user id" (a bit of a mess). We could tighten up that usage throughout, or introduce role name explicitly when allowed, not sure what is best.
          chaase3 Camilla Haase added a comment -

          Thanks for all the answers and the drafts, Dag. That will give me a big head start.

          Yes, it would be very helpful to be consistent with the authid/user/role terminology. Ideally I guess we would say "user" only when something applied only to a user and not to a role. I'm still undecided on whether to use AuthorizationIdentifier instead of "user or role" in all cases or just when talking about formal syntax. We have time to think this over.

          chaase3 Camilla Haase added a comment - Thanks for all the answers and the drafts, Dag. That will give me a big head start. Yes, it would be very helpful to be consistent with the authid/user/role terminology. Ideally I guess we would say "user" only when something applied only to a user and not to a role. I'm still undecided on whether to use AuthorizationIdentifier instead of "user or role" in all cases or just when talking about formal syntax. We have time to think this over.
          chaase3 Camilla Haase added a comment -

          I've made the needed changes to the Reference Manual and Tools Guide, I believe, so I'm providing an initial patch for this material: see DERBY-3193.diff, DERBY-3193.stat, and DERBY-3193.zip. The changes are as follows:

          M src/ref/crefsqlj35312.dita
          A src/ref/rrefcurrentrole.dita
          A src/ref/rrefcreaterole.dita
          A src/ref/rrefsetrole.dita
          M src/ref/rrefsqljrevoke.dita
          M src/ref/crefsqlj80721.dita
          A src/ref/rrefrolename.dita
          M src/ref/rrefsistabssystableperms.dita
          A src/ref/rrefsistabssysroles.dita
          M src/ref/rrefsyscsdiagtables.dita
          M src/ref/crefsqlj95081.dita
          M src/ref/rrefsistabssysroutineperms.dita
          A src/ref/rrefdroprole.dita
          M src/ref/rrefsistabssyscolperms.dita
          M src/ref/rrefsqlj30540.dita
          M src/ref/rrefsqlj31580.dita
          M src/ref/rrefsqljgrant.dita
          M src/ref/refderby.ditamap
          M src/tools/rtoolsijcomrefshow.dita

          The Developer's Guide, where the overall conceptual material belongs, is a bit harder to work with. The new material belongs under the "User authorizations" section, I believe. However, the section seems also to need some reorganization. The sections "Setting the default connection mode" and its subsection "Setting the user authorizations for individual users" are currently under "Setting the SQL standard authorization mode", but aren't they independent of SQL authorization? I think you can use the builtin user authorizations independent of SQL authorization.

          The general "User authorizations" topic lists the properties in this order:

          derby.database.defaultConnectionMode
          derby.database.sqlAuthorization
          derby.database.fullAccessUsers, derby.database.readOnlyAccessUsers

          So it might be better to organize this section as follows:

          User authorizations
          Setting the default connection access mode
          Setting the SQL standard authorization mode
          SQL standard authorization (retitle as "Using SQL standard authorization")
          Setting the user authorizations for individual users
          Read-only and full access permissions
          User authorization exceptions

          Does this make sense?

          The additional conceptual information on roles belongs in "Using SQL standard authorization", I think. I'll work on adding it.

          chaase3 Camilla Haase added a comment - I've made the needed changes to the Reference Manual and Tools Guide, I believe, so I'm providing an initial patch for this material: see DERBY-3193 .diff, DERBY-3193 .stat, and DERBY-3193 .zip. The changes are as follows: M src/ref/crefsqlj35312.dita A src/ref/rrefcurrentrole.dita A src/ref/rrefcreaterole.dita A src/ref/rrefsetrole.dita M src/ref/rrefsqljrevoke.dita M src/ref/crefsqlj80721.dita A src/ref/rrefrolename.dita M src/ref/rrefsistabssystableperms.dita A src/ref/rrefsistabssysroles.dita M src/ref/rrefsyscsdiagtables.dita M src/ref/crefsqlj95081.dita M src/ref/rrefsistabssysroutineperms.dita A src/ref/rrefdroprole.dita M src/ref/rrefsistabssyscolperms.dita M src/ref/rrefsqlj30540.dita M src/ref/rrefsqlj31580.dita M src/ref/rrefsqljgrant.dita M src/ref/refderby.ditamap M src/tools/rtoolsijcomrefshow.dita The Developer's Guide, where the overall conceptual material belongs, is a bit harder to work with. The new material belongs under the "User authorizations" section, I believe. However, the section seems also to need some reorganization. The sections "Setting the default connection mode" and its subsection "Setting the user authorizations for individual users" are currently under "Setting the SQL standard authorization mode", but aren't they independent of SQL authorization? I think you can use the builtin user authorizations independent of SQL authorization. The general "User authorizations" topic lists the properties in this order: derby.database.defaultConnectionMode derby.database.sqlAuthorization derby.database.fullAccessUsers, derby.database.readOnlyAccessUsers So it might be better to organize this section as follows: User authorizations Setting the default connection access mode Setting the SQL standard authorization mode SQL standard authorization (retitle as "Using SQL standard authorization") Setting the user authorizations for individual users Read-only and full access permissions User authorization exceptions Does this make sense? The additional conceptual information on roles belongs in "Using SQL standard authorization", I think. I'll work on adding it.
          dagw Dag H. Wanvik added a comment -

          Thanks so much for working on this, Kim! I will be out the next 2 weeks,, but will get back
          to reading the patch in detail as soon as I am back. Just some comments on the Dev guide org:

          You reorg of "User authorizations" makes sense to me, abolutely. It is wrong presently, I agree.
          One thing, I would suggest renaming the section:

          "Setting the user authorizations for individual users" to
          "Setting access for individual users" in the hope of using the work "authorization" primarily for SQL authorization
          makes this subject less confusing. I would also finish off the treatment of connection level access control before we open up the subject of SQL authorization. Maybe like this:

          User authorizations
          Setting the default connection access mode
          Setting access for individual users
          Read-only and full access permissions
          Setting the SQL standard authorization mode
          SQL standard authorization (retitle as "Using SQL standard authorization")
          User authorization exceptions

          The section "User authorization exceptions" is currently very thin, treating only exception occuring under connection level access control. Lacking SQL privileges under SQL authorization give different errors, but are not treated. It does mention (again) how to set SQL authorization. Maybe this can be removed, and the whole section moved to under "Setting access for individual user", on the same level as "Read-only and full access permissions ". That way, we coul dlater add a section on exception that one can see under SQL authorization under "Setting the SQL standard authorization mode".

          A new roles section would then also go under "Setting the SQL standard authorization mode", I presume.

          dagw Dag H. Wanvik added a comment - Thanks so much for working on this, Kim! I will be out the next 2 weeks,, but will get back to reading the patch in detail as soon as I am back. Just some comments on the Dev guide org: You reorg of "User authorizations" makes sense to me, abolutely. It is wrong presently, I agree. One thing, I would suggest renaming the section: "Setting the user authorizations for individual users" to "Setting access for individual users" in the hope of using the work "authorization" primarily for SQL authorization makes this subject less confusing. I would also finish off the treatment of connection level access control before we open up the subject of SQL authorization. Maybe like this: User authorizations Setting the default connection access mode Setting access for individual users Read-only and full access permissions Setting the SQL standard authorization mode SQL standard authorization (retitle as "Using SQL standard authorization") User authorization exceptions The section "User authorization exceptions" is currently very thin, treating only exception occuring under connection level access control. Lacking SQL privileges under SQL authorization give different errors, but are not treated. It does mention (again) how to set SQL authorization. Maybe this can be removed, and the whole section moved to under "Setting access for individual user", on the same level as "Read-only and full access permissions ". That way, we coul dlater add a section on exception that one can see under SQL authorization under "Setting the SQL standard authorization mode". A new roles section would then also go under "Setting the SQL standard authorization mode", I presume.
          chaase3 Camilla Haase added a comment -

          Attaching DERBY-3193-2.diff, DERBY-3193-2.stat, and DERBY-3193-2.zip, updated versions of the first patch that include changes to the Developer's Guide as well as a few tweaks to Reference Manual topics.

          M src/ref/crefsqlj35312.dita
          A src/ref/rrefcurrentrole.dita
          A src/ref/rrefcreaterole.dita
          A src/ref/rrefsetrole.dita
          M src/ref/rrefsqljrevoke.dita
          M src/ref/crefsqlj80721.dita
          A src/ref/rrefrolename.dita
          M src/ref/rrefsistabssystableperms.dita
          A src/ref/rrefsistabssysroles.dita
          M src/ref/rrefsyscsdiagtables.dita
          M src/ref/crefsqlj95081.dita
          M src/ref/rrefsistabssysroutineperms.dita
          A src/ref/rrefdroprole.dita
          M src/ref/rrefsistabssyscolperms.dita
          M src/ref/rrefsqlj30540.dita
          M src/ref/rrefsqlj31580.dita
          M src/ref/rrefsqljgrant.dita
          M src/ref/refderby.ditamap
          M src/devguide/cdevcsecure866060.dita
          M src/devguide/derbydev.ditamap
          A src/devguide/rdevcsecuresqlauthexceptions.dita
          M src/devguide/cdevcsecuregrantrevokeaccess.dita
          M src/devguide/cdevcsecure36595.dita
          M src/devguide/rdevcsecure379.dita
          M src/devguide/rdevcsecure190.dita
          M src/devguide/cdevcsecure865880.dita
          A src/devguide/cdevcsecureroles.dita
          M src/tools/rtoolsijcomrefshow.dita

          I reorganized the Developer's Guide section as follows:

          User authorizations
          Setting the default connection access mode
          Setting access for individual users
          Read-only and full access permissions
          User authorization exceptions
          Setting the SQL standard authorization mode
          Using SQL standard authorization
          Using SQL roles (new topic)
          SQL standard authorization exceptions (new topic)

          Reordered bullet list in "User authorizations" (cdevcsecure36595.dita) to reflect this order.

          Retitled "Setting the user authorizations for individual users" (cdevcsecure865880.dita) to "Setting access for individual users".

          Retitled "SQL standard authorization" (cdevcsecuregrantrevokeaccess.dita) to "Using SQL standard authorization".

          Fixed a table in "Read-only and full access permissions" (rdevcsecure190.dita) so that empty cells don't have single quote marks in them in the PDF and HTML-single versions.

          Removed language specific to SQL authorization from "User authorization exceptions" (rdevcsecure379.dita).

          Added new topics, cdevcsecureroles.dita and rdevcsecuresqlauthexceptions.dita.

          Updated map file to reflect fixes (including reltable).

          —

          Additional fixes to the Reference Manual:

          rrefsqljgrant.dita: only db owner can grant a role.

          rrefsqljrevoke.dita: only db owner can revoke a role.

          rrefsetrole.dita: setting role is not transactional; added examples.

          rrefcreaterole.dita: just some typographical consistency fixes.

          The Tools Guide topic is still lacking in content because I can't seem to get roles to work right with current Derby trunk libraries. I set SQLAuthorization, but then I always get an error when I try to set a role:

          java -cp /home/chaase/derby10.5alpha/lib/derby.jar:. RoleExample
          org.apache.derby.jdbc.EmbeddedDriver loaded.
          Trying to connect to jdbc:derby:sqlAuthEmbDB;user=mary;create=true
          Connected to database jdbc:derby:sqlAuthEmbDB;user=mary;create=true
          Turning on authentication and SQL authorization.
          Value of requireAuthentication is true
          Value of sqlAuthorization is true
          Value of defaultConnectionMode is noAccess

          --SQLException Caught--

          SQLState: 42Z60
          Severity: 30000
          Message: CREATE ROLE not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'.

          Same thing happened when I tried setting the properties on the ij command line and then doing CREATE ROLE there.

          chaase3 Camilla Haase added a comment - Attaching DERBY-3193 -2.diff, DERBY-3193 -2.stat, and DERBY-3193 -2.zip, updated versions of the first patch that include changes to the Developer's Guide as well as a few tweaks to Reference Manual topics. M src/ref/crefsqlj35312.dita A src/ref/rrefcurrentrole.dita A src/ref/rrefcreaterole.dita A src/ref/rrefsetrole.dita M src/ref/rrefsqljrevoke.dita M src/ref/crefsqlj80721.dita A src/ref/rrefrolename.dita M src/ref/rrefsistabssystableperms.dita A src/ref/rrefsistabssysroles.dita M src/ref/rrefsyscsdiagtables.dita M src/ref/crefsqlj95081.dita M src/ref/rrefsistabssysroutineperms.dita A src/ref/rrefdroprole.dita M src/ref/rrefsistabssyscolperms.dita M src/ref/rrefsqlj30540.dita M src/ref/rrefsqlj31580.dita M src/ref/rrefsqljgrant.dita M src/ref/refderby.ditamap M src/devguide/cdevcsecure866060.dita M src/devguide/derbydev.ditamap A src/devguide/rdevcsecuresqlauthexceptions.dita M src/devguide/cdevcsecuregrantrevokeaccess.dita M src/devguide/cdevcsecure36595.dita M src/devguide/rdevcsecure379.dita M src/devguide/rdevcsecure190.dita M src/devguide/cdevcsecure865880.dita A src/devguide/cdevcsecureroles.dita M src/tools/rtoolsijcomrefshow.dita I reorganized the Developer's Guide section as follows: User authorizations Setting the default connection access mode Setting access for individual users Read-only and full access permissions User authorization exceptions Setting the SQL standard authorization mode Using SQL standard authorization Using SQL roles (new topic) SQL standard authorization exceptions (new topic) Reordered bullet list in "User authorizations" (cdevcsecure36595.dita) to reflect this order. Retitled "Setting the user authorizations for individual users" (cdevcsecure865880.dita) to "Setting access for individual users". Retitled "SQL standard authorization" (cdevcsecuregrantrevokeaccess.dita) to "Using SQL standard authorization". Fixed a table in "Read-only and full access permissions" (rdevcsecure190.dita) so that empty cells don't have single quote marks in them in the PDF and HTML-single versions. Removed language specific to SQL authorization from "User authorization exceptions" (rdevcsecure379.dita). Added new topics, cdevcsecureroles.dita and rdevcsecuresqlauthexceptions.dita. Updated map file to reflect fixes (including reltable). — Additional fixes to the Reference Manual: rrefsqljgrant.dita: only db owner can grant a role. rrefsqljrevoke.dita: only db owner can revoke a role. rrefsetrole.dita: setting role is not transactional; added examples. rrefcreaterole.dita: just some typographical consistency fixes. The Tools Guide topic is still lacking in content because I can't seem to get roles to work right with current Derby trunk libraries. I set SQLAuthorization, but then I always get an error when I try to set a role: java -cp /home/chaase/derby10.5alpha/lib/derby.jar:. RoleExample org.apache.derby.jdbc.EmbeddedDriver loaded. Trying to connect to jdbc:derby:sqlAuthEmbDB;user=mary;create=true Connected to database jdbc:derby:sqlAuthEmbDB;user=mary;create=true Turning on authentication and SQL authorization. Value of requireAuthentication is true Value of sqlAuthorization is true Value of defaultConnectionMode is noAccess -- SQLException Caught -- SQLState: 42Z60 Severity: 30000 Message: CREATE ROLE not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'. Same thing happened when I tried setting the properties on the ij command line and then doing CREATE ROLE there.
          dagw Dag H. Wanvik added a comment -

          Hi Kim!

          Thanks for your quick and good work on this! Here is feedback for ref
          man & tools. Will look at devguide next.

          • M src/ref/crefsqlj35312.dita

          Good!

          • A src/ref/rrefcurrentrole.dita

          > This function returns a string of up to 128 characters.

          Actually, no. This has changed. The identifier length (after case
          normalization) is max 128, but the returned identifer string returned
          may be up to 2 + (2*128) long due to quoting.

          For example:
          create role "A""B"; – case normal form: A"B
          set role "A""B";
          values current_role – returns the string "A""B"

          • A src/ref/rrefcreaterole.dita

          Good!

          • A src/ref/rrefsetrole.dita

          > - The privileges the role inherits from any other roles granted to it

          I think we should try to use the term "contained role" and define once
          so we can avoid having to speak about grant relationship between roles
          all the time:

          "A role contains another role if that role is granted to it, or is
          contained in a role granted to it."

          Given this definition we can say for this bullet:

          "The union of privileges of roles contained in that role."

          > The privileges of the current role, if set

          should be (I think):

          "The privileges identified by the current role, if set" (since you
          just defined "identified by").

          Examples:

          > // These examples show the use of SET ROLE in Java statements.
          ****
          > // The case normal form is visible in the SYSROLES system table.
          ********

          We should use JDBC rather than Java, I think. Indent the comment to
          the margin of the code. It should be "SYS.SYSROLES".

          • M src/ref/rrefsqljrevoke.dita

          > "Only the database owner can revoke a role."

          "database owner" could be linked here.

          (Probably not from this diff, but anyway):

          > Before you issue a REVOKE statement, check that the
          derby.database.sqlAuthorization property is set to true.

          This sentence is a bit disingenuous. Since sqlAuthorization must be
          set before a GRANT can be performed, and a GRANT must logically be
          performed before a REVOKE is meaningful, and sqlAuthorization can't be
          switched off once set, the "check that..." sounds a bit weird..

          > You can revoke privileges from an object

          Should be "You can revoke privileges for an object".

          > The syntax that you use for the REVOKE statement depends on whether
          you are revoking privileges to a table or to a routine.

          .. or whether you are revoking a role.

          The link to grantees points to
          rrefsqljgrant.html#rrefsqljgrant__grantgrantees, but there is a syntax
          definition for grantees in rrefsqljrevoke as well? Is this
          intentional? Since they are equal, maybe the link can be retained, but
          the definition in rrefsqljrevoke can be removed..

          > You can revoke the privileges from specific users or roles or from
          all users.

          Add: "You can revoke a role from a role, a user, and from PUBLIC".

          Limitations section:

          Add:

          "Derby tracks any dependencies on the definer's current role for
          views, constraints and triggers. If privileges were obtainable only
          via the current role when the object in question was defined, that
          object will depend on the current role. The object will get dropped if
          the role is revoked from the defining user or from PUBLIC, as the case
          may be. Also, if a contained role of the current role in such cases
          gets revoked, dependent objects will get dropped. Note that dropping
          may be too pessimistic. This is because Derby does not presently make
          an attempt to re-check if the necessary privileges are still available
          in such cases."

          • M src/ref/crefsqlj80721.dita

          Good!

          • A src/ref/rrefrolename.dita

          Good!

          • M src/ref/rrefsistabssystableperms.dita

          Good!

          • A src/ref/rrefsistabssysroles.dita

          Bullet

          > - A role grant (result of a GRANT statement in which the grantee is a role)

          The parenthesized statement is a bit misleading. Just remove it, I think:

          • "A role grant"

          The entry for WITHADMINOPTION is wrong. The Contents filed should
          read some such:

          "A role definition is modelled as a grant from "_SYSTEM" to the data
          base owner so in such cases the value is always 'Y'. This means the
          creator (data base owner) is allowed to grant the newly created role
          (of course). Currently roles can not be granted WITH ADMIN OPTION, so
          in other cases its value is 'N'."

          • M src/ref/rrefsyscsdiagtables.dita

          "Spec says "can be used to get the contained roles for a role (or the
          inverse relation)." What does "or the inverse relation" mean in this
          case?"

          Referring to the GRANT relationship between roles, the GRANT^-1
          relationship is its inverse. The contained roles set is the transitive
          closure of the GRANT^-1 relationship, cf definition of "contained
          role" above. I guess you can just link to the definition of contained
          role here.

          • M src/ref/crefsqlj95081.dita

          Good!

          Shouldn't crefsqlj18919.html have an entry for roleName?

          • M src/ref/rrefsistabssysroutineperms.dita

          Good!

          • A src/ref/rrefdroprole.dita

          Good!

          • M src/ref/rrefsistabssyscolperms.dita

          Good!

          • M src/ref/rrefsqlj30540.dita

          Good!

          • M src/ref/rrefsqlj31580.dita

          Good!

          • M src/ref/rrefsqljgrant.dita

          Good!

          M src/ref/refderby.ditamap

          Good!

          • M src/tools/rtoolsijcomrefshow.dita

          > SHOW ROLES displays all the roles in the current session. That is,
          it shows all roles that have been created.

          Not just in current session, this is misleading. Just say:

          "SHOW ROLES displays the names of all roles created, whether
          settable available for the current session or not."

          > SHOW ENABLED ROLES displays all the enabled roles in the current
          session. That is, it shows all roles that have been both created and
          set.

          SHOW ENABLED ROLES displays all the roles whose privileges are
          available for the current session. That is, it shows the current role
          and any role granted to the current role etc (contained roles, see
          definition).

          > SHOW SETTABLE ROLES displays all the settable roles in the current
          session. That is, it shows all roles that have been created but have
          not been set.

          SHOW SETTABLE ROLES displays all the roles that the current session
          can set, that is, all roles that have been granted to the current
          user.

          Example:

          ij> show roles;
          ROLEID
          ------------------------------
          CASUALUSER
          ANYUSER
          POWERUSER

          3 rows selected

          ij> show enabled roles;
          ROLEID
          ------------------------------
          CASUALUSER
          ANYUSER

          2 rows selected

          ij> show settable roles;
          ROLEID
          ------------------------------
          CASUALUSER
          POWERUSER

          2 rows selected

          In the examples above, presumably, both CASUALUSER and POWERUSER
          contains ANYUSER, but ANYUSER is not settable directly.

          dagw Dag H. Wanvik added a comment - Hi Kim! Thanks for your quick and good work on this! Here is feedback for ref man & tools. Will look at devguide next. M src/ref/crefsqlj35312.dita Good! A src/ref/rrefcurrentrole.dita > This function returns a string of up to 128 characters. Actually, no. This has changed. The identifier length (after case normalization) is max 128, but the returned identifer string returned may be up to 2 + (2*128) long due to quoting. For example: create role "A""B"; – case normal form: A"B set role "A""B"; values current_role – returns the string "A""B" A src/ref/rrefcreaterole.dita Good! A src/ref/rrefsetrole.dita > - The privileges the role inherits from any other roles granted to it I think we should try to use the term "contained role" and define once so we can avoid having to speak about grant relationship between roles all the time: "A role contains another role if that role is granted to it, or is contained in a role granted to it." Given this definition we can say for this bullet: "The union of privileges of roles contained in that role." > The privileges of the current role, if set should be (I think): "The privileges identified by the current role, if set" (since you just defined "identified by"). Examples: > // These examples show the use of SET ROLE in Java statements. **** > // The case normal form is visible in the SYSROLES system table. ******** We should use JDBC rather than Java, I think. Indent the comment to the margin of the code. It should be "SYS.SYSROLES". M src/ref/rrefsqljrevoke.dita > "Only the database owner can revoke a role." "database owner" could be linked here. (Probably not from this diff, but anyway): > Before you issue a REVOKE statement, check that the derby.database.sqlAuthorization property is set to true. This sentence is a bit disingenuous. Since sqlAuthorization must be set before a GRANT can be performed, and a GRANT must logically be performed before a REVOKE is meaningful, and sqlAuthorization can't be switched off once set, the "check that..." sounds a bit weird.. > You can revoke privileges from an object Should be "You can revoke privileges for an object". > The syntax that you use for the REVOKE statement depends on whether you are revoking privileges to a table or to a routine. .. or whether you are revoking a role. The link to grantees points to rrefsqljgrant.html#rrefsqljgrant__grantgrantees, but there is a syntax definition for grantees in rrefsqljrevoke as well? Is this intentional? Since they are equal, maybe the link can be retained, but the definition in rrefsqljrevoke can be removed.. > You can revoke the privileges from specific users or roles or from all users. Add: "You can revoke a role from a role, a user, and from PUBLIC". Limitations section: Add: "Derby tracks any dependencies on the definer's current role for views, constraints and triggers. If privileges were obtainable only via the current role when the object in question was defined, that object will depend on the current role. The object will get dropped if the role is revoked from the defining user or from PUBLIC, as the case may be. Also, if a contained role of the current role in such cases gets revoked, dependent objects will get dropped. Note that dropping may be too pessimistic. This is because Derby does not presently make an attempt to re-check if the necessary privileges are still available in such cases." M src/ref/crefsqlj80721.dita Good! A src/ref/rrefrolename.dita Good! M src/ref/rrefsistabssystableperms.dita Good! A src/ref/rrefsistabssysroles.dita Bullet > - A role grant (result of a GRANT statement in which the grantee is a role) The parenthesized statement is a bit misleading. Just remove it, I think: "A role grant" The entry for WITHADMINOPTION is wrong. The Contents filed should read some such: "A role definition is modelled as a grant from "_SYSTEM" to the data base owner so in such cases the value is always 'Y'. This means the creator (data base owner) is allowed to grant the newly created role (of course). Currently roles can not be granted WITH ADMIN OPTION, so in other cases its value is 'N'." M src/ref/rrefsyscsdiagtables.dita "Spec says "can be used to get the contained roles for a role (or the inverse relation)." What does "or the inverse relation" mean in this case?" Referring to the GRANT relationship between roles, the GRANT^-1 relationship is its inverse. The contained roles set is the transitive closure of the GRANT^-1 relationship, cf definition of "contained role" above. I guess you can just link to the definition of contained role here. M src/ref/crefsqlj95081.dita Good! Shouldn't crefsqlj18919.html have an entry for roleName? M src/ref/rrefsistabssysroutineperms.dita Good! A src/ref/rrefdroprole.dita Good! M src/ref/rrefsistabssyscolperms.dita Good! M src/ref/rrefsqlj30540.dita Good! M src/ref/rrefsqlj31580.dita Good! M src/ref/rrefsqljgrant.dita Good! M src/ref/refderby.ditamap Good! M src/tools/rtoolsijcomrefshow.dita > SHOW ROLES displays all the roles in the current session. That is, it shows all roles that have been created. Not just in current session, this is misleading. Just say: "SHOW ROLES displays the names of all roles created, whether settable available for the current session or not." > SHOW ENABLED ROLES displays all the enabled roles in the current session. That is, it shows all roles that have been both created and set. SHOW ENABLED ROLES displays all the roles whose privileges are available for the current session. That is, it shows the current role and any role granted to the current role etc (contained roles, see definition). > SHOW SETTABLE ROLES displays all the settable roles in the current session. That is, it shows all roles that have been created but have not been set. SHOW SETTABLE ROLES displays all the roles that the current session can set, that is, all roles that have been granted to the current user. Example: ij> show roles; ROLEID ------------------------------ CASUALUSER ANYUSER POWERUSER 3 rows selected ij> show enabled roles; ROLEID ------------------------------ CASUALUSER ANYUSER 2 rows selected ij> show settable roles; ROLEID ------------------------------ CASUALUSER POWERUSER 2 rows selected In the examples above, presumably, both CASUALUSER and POWERUSER contains ANYUSER, but ANYUSER is not settable directly.
          dagw Dag H. Wanvik added a comment -

          When I test, I use derby.properties containing:

          derby.connection.requireAuthentication=true
          derby.database.sqlAuthorization=true

          Works for me. If you can't make it work, feel free to send me your program. Note that
          derby.connection.requireAuthentication is static so you need a reboot of the engine if you set
          it programmatically.

          dagw Dag H. Wanvik added a comment - When I test, I use derby.properties containing: derby.connection.requireAuthentication=true derby.database.sqlAuthorization=true Works for me. If you can't make it work, feel free to send me your program. Note that derby.connection.requireAuthentication is static so you need a reboot of the engine if you set it programmatically.
          dagw Dag H. Wanvik added a comment -

          Here are comments for the devguide part of patch DERBY-3193-2.

          > User authorizations
          > Setting the default connection access mode
          > Setting access for individual users
          > Read-only and full access permissions
          > User authorization exceptions
          > Setting the SQL standard authorization mode
          > Using SQL standard authorization
          > Using SQL roles (new topic)
          > SQL standard authorization exceptions (new topic)

          New structure is good, I think!

          • M src/devguide/cdevcsecure866060.dita

          > The REVOKE statement is used to revoke permissions.

          This statement is too narrow now. Suggest:

          "The REVOKE statement is used to revoke permissions and role grants."

          > See the Derby Reference Manual for more information on the GRANT and
          REVOKE statements and on roles.

          The last part "and on roles" is a bit misleading perhaps; I guess the
          principal conceptual information on roles is in the dev guide, section
          "Using SQL roles". So this link should be specifically for "more
          information on granting and revoking SQL roles"?

          • A src/devguide/rdevcsecuresqlauthexceptions.dita

          This information is correct, but just the start, I guess. We should
          flesh out with other exception for other actions as well.

          For a start I can list some more for roles:

          • CREATE role: Add to what you already have: If role already exists:
            X0Y68. If not dbo: 4251A
          • DROP role: If not dbo: 4251A. Role does not exist 0P000.
          • SET role: if role does not exist: 0P000 if the role exists but has
            not been granted 0P000. If transaction is not idle: 25001. If NONE
            or a malformed identifier is used as a string or ? argument, XCXA0.
          • REVOKE role: If you try to revoke the role "PUBLIC": 4251B. if role
            does not exist: 0P000. If not dbo: 4251A.
          • GRANT role: same as REVOKE plus, if trying to grant would create a
            circularity: 4251C.

          For all, if identifier is more than 128 chars long, exception 42622.

          M src/devguide/cdevcsecuregrantrevokeaccess.dita

          • M src/devguide/cdevcsecure36595.dita

          Good!

          M src/devguide/rdevcsecure379.dita

          • M src/devguide/rdevcsecure190.dita

          Good!

          • M src/devguide/cdevcsecure865880.dita

          Good!

          A src/devguide/cdevcsecureroles.dita

          > Only the database owner can create, grant, revoke, and drop roles.

          This is correct, but I think we should add here: "... but object owners
          can GRANT and REVOKE privileges for those objects to/from roles, as
          well as to/from users and PUBLIC (all users)."

          It may also be good to say at the outset that Derby implements a
          subset of the SQL roles. The fact that only dbo can do the above is an
          implementation restriction.

          > Old databases must be (hard) upgraded to the target release before
          roles can be used.

          Substitute "10.5 or newer" for "target release".

          > The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function returns the
          contained roles for a role (or the inverse relation).

          Suggest: "The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function can
          be used to determine the set of contained roles for a role".

          > The role can be any role that has been granted to you (the current
          user) or to PUBLIC.

          Suggest: "The role set can be any role that has been granted to the
          session's current user or to PUBLIC."

          > To retrieve the current role, call the CURRENT_ROLE function.

          Suggest: "To retrieve the current role identifier in SQL, invoke the
          CURRENT_ROLE function."

          Example:
          VALUES CURRENT_ROLE

          > If a role loses a privilege, and a session has a current role which
          is that role or a role that contains that role, the session also
          loses that privilege, unless one or more of the following is true:

          More precisely:

          "If a role loses a privilege to an object, and a session whose current
          user is different than the owner of that object, has a current role
          which is that role or a role that contains that role, the session also
          loses that privilege, unless one or more of the following is true:"

          And add one final item:

          • The session's current user is the data base owner

          > When a role is revoked from a user, that session can no longer keep
          that role, nor can it take on that role in a SET ROLE statement.

          Correction:

          "When a role is revoked from a user, that session can no longer keep
          that role, nor can it take on that role in a SET ROLE statement,
          unless the role is also granted to PUBLIC.

          > The default drop behavior is CASCADE. Therefore, all persistent
          objects (constraints, views and triggers) that rely on that role are
          dropped.

          Correction:

          "The default drop behavior is CASCADE. Therefore, all persistent
          objects (constraints, views and triggers) that rely on that role are
          dropped. Although there may be other ways of fulfilling that
          privilege at the time of the revoke, any dependent objects are still
          dropped. This is an implementation limitation."

          dagw Dag H. Wanvik added a comment - Here are comments for the devguide part of patch DERBY-3193 -2. > User authorizations > Setting the default connection access mode > Setting access for individual users > Read-only and full access permissions > User authorization exceptions > Setting the SQL standard authorization mode > Using SQL standard authorization > Using SQL roles (new topic) > SQL standard authorization exceptions (new topic) New structure is good, I think! M src/devguide/cdevcsecure866060.dita > The REVOKE statement is used to revoke permissions. This statement is too narrow now. Suggest: "The REVOKE statement is used to revoke permissions and role grants." > See the Derby Reference Manual for more information on the GRANT and REVOKE statements and on roles. The last part "and on roles" is a bit misleading perhaps; I guess the principal conceptual information on roles is in the dev guide, section "Using SQL roles". So this link should be specifically for "more information on granting and revoking SQL roles"? A src/devguide/rdevcsecuresqlauthexceptions.dita This information is correct, but just the start, I guess. We should flesh out with other exception for other actions as well. For a start I can list some more for roles: CREATE role: Add to what you already have: If role already exists: X0Y68. If not dbo: 4251A DROP role: If not dbo: 4251A. Role does not exist 0P000. SET role: if role does not exist: 0P000 if the role exists but has not been granted 0P000. If transaction is not idle: 25001. If NONE or a malformed identifier is used as a string or ? argument, XCXA0. REVOKE role: If you try to revoke the role "PUBLIC": 4251B. if role does not exist: 0P000. If not dbo: 4251A. GRANT role: same as REVOKE plus, if trying to grant would create a circularity: 4251C. For all, if identifier is more than 128 chars long, exception 42622. M src/devguide/cdevcsecuregrantrevokeaccess.dita M src/devguide/cdevcsecure36595.dita Good! M src/devguide/rdevcsecure379.dita M src/devguide/rdevcsecure190.dita Good! M src/devguide/cdevcsecure865880.dita Good! A src/devguide/cdevcsecureroles.dita > Only the database owner can create, grant, revoke, and drop roles. This is correct, but I think we should add here: "... but object owners can GRANT and REVOKE privileges for those objects to/from roles, as well as to/from users and PUBLIC (all users)." It may also be good to say at the outset that Derby implements a subset of the SQL roles. The fact that only dbo can do the above is an implementation restriction. > Old databases must be (hard) upgraded to the target release before roles can be used. Substitute "10.5 or newer" for "target release". > The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function returns the contained roles for a role (or the inverse relation). Suggest: "The SYSCS_DIAG.CONTAINED_ROLES diagnostic table function can be used to determine the set of contained roles for a role". > The role can be any role that has been granted to you (the current user) or to PUBLIC. Suggest: "The role set can be any role that has been granted to the session's current user or to PUBLIC." > To retrieve the current role, call the CURRENT_ROLE function. Suggest: "To retrieve the current role identifier in SQL, invoke the CURRENT_ROLE function." Example: VALUES CURRENT_ROLE > If a role loses a privilege, and a session has a current role which is that role or a role that contains that role, the session also loses that privilege, unless one or more of the following is true: More precisely: "If a role loses a privilege to an object, and a session whose current user is different than the owner of that object, has a current role which is that role or a role that contains that role, the session also loses that privilege, unless one or more of the following is true:" And add one final item: The session's current user is the data base owner > When a role is revoked from a user, that session can no longer keep that role, nor can it take on that role in a SET ROLE statement. Correction: "When a role is revoked from a user, that session can no longer keep that role, nor can it take on that role in a SET ROLE statement, unless the role is also granted to PUBLIC. > The default drop behavior is CASCADE. Therefore, all persistent objects (constraints, views and triggers) that rely on that role are dropped. Correction: "The default drop behavior is CASCADE. Therefore, all persistent objects (constraints, views and triggers) that rely on that role are dropped. Although there may be other ways of fulfilling that privilege at the time of the revoke, any dependent objects are still dropped. This is an implementation limitation."
          chaase3 Camilla Haase added a comment -

          Thanks, Dag, for these really helpful comments. I think I've implemented them and will file another patch tomorrow. I do have a few comments and questions.

          Definition of role containment:

          "A role contains another role if that role is granted to it, or is contained in a role granted to it."

          I think this needs to be placed in the Developer's Guide topic cdevcsecureroles.dita (Using SQL roles) and then in a logical place in the Ref Manual. I think it's also useful to use "A" and "B" to clarify things, as in the first definition of containment (in the Dev Guide topic):

          "If a role A is granted to another role B, the privileges identified by role A are inherited by role B. We say that B contains A."

          Adapting this to your definition, I think we get the following (A and B get reversed):

          "A role A contains another role B if role B is granted to role A, or is contained in a role granted to role A. In this case, the privileges identified by role B are inherited by role A."

          Please let me know if further changes would be useful.

          --------

          The Reference Manual doesn't seem to have any references to the Developer's Guide for material on roles or on GRANT/REVOKE. I've put some in.

          --------

          src/ref/rrefsqljrevoke.dita:

          I think the first paragraph should have something about revoking a role. I've added a sentence – hope it's okay. (Parallel to the sentence for the GRANT statement.)

          I modified the sentence about sqlAuthorization – you can let me know if that's okay or if I should just remove it.

          The link to grantgrantees instead of revokegrantees was accidental (I probably copied from the GRANT statement text and forgot to make the change).


          src/ref/rrefsqljgrant.dita:

          Added link to "database owner" as with revoke.

          Added definition of role containment.

          -----------

          src/ref/rrefsetrole.dita

          Added link to definition of role containment.

          -------

          You ask, "Shouldn't crefsqlj18919.html have an entry for roleName?" It does, because all the topics under "SQL identifiers" are listed automatically in the HTML frames version.

          ----------

          The comments on src/devguide/cdevcsecure866060.dita actually refer to text that is in cdevcsecuregrantrevokeaccess.dita, so I made the changes there.

          ---------

          src/devguide/rdevcsecuresqlauthexceptions.dita

          If the exception for an identifier over 128 characters long applies to all statements, does that mean that it always comes up first? Since you can't create a role using an identifier over 128 characters long, then using DROP ROLE with a too-long argument should result in both 0P000 (for a nonexistent role) and 42622. Would the user see 42622 and not 0P000?

          chaase3 Camilla Haase added a comment - Thanks, Dag, for these really helpful comments. I think I've implemented them and will file another patch tomorrow. I do have a few comments and questions. Definition of role containment: "A role contains another role if that role is granted to it, or is contained in a role granted to it." I think this needs to be placed in the Developer's Guide topic cdevcsecureroles.dita (Using SQL roles) and then in a logical place in the Ref Manual. I think it's also useful to use "A" and "B" to clarify things, as in the first definition of containment (in the Dev Guide topic): "If a role A is granted to another role B, the privileges identified by role A are inherited by role B. We say that B contains A." Adapting this to your definition, I think we get the following (A and B get reversed): "A role A contains another role B if role B is granted to role A, or is contained in a role granted to role A. In this case, the privileges identified by role B are inherited by role A." Please let me know if further changes would be useful. -------- The Reference Manual doesn't seem to have any references to the Developer's Guide for material on roles or on GRANT/REVOKE. I've put some in. -------- src/ref/rrefsqljrevoke.dita: I think the first paragraph should have something about revoking a role. I've added a sentence – hope it's okay. (Parallel to the sentence for the GRANT statement.) I modified the sentence about sqlAuthorization – you can let me know if that's okay or if I should just remove it. The link to grantgrantees instead of revokegrantees was accidental (I probably copied from the GRANT statement text and forgot to make the change). src/ref/rrefsqljgrant.dita: Added link to "database owner" as with revoke. Added definition of role containment. ----------- src/ref/rrefsetrole.dita Added link to definition of role containment. ------- You ask, "Shouldn't crefsqlj18919.html have an entry for roleName?" It does, because all the topics under "SQL identifiers" are listed automatically in the HTML frames version. ---------- The comments on src/devguide/cdevcsecure866060.dita actually refer to text that is in cdevcsecuregrantrevokeaccess.dita, so I made the changes there. --------- src/devguide/rdevcsecuresqlauthexceptions.dita If the exception for an identifier over 128 characters long applies to all statements, does that mean that it always comes up first? Since you can't create a role using an identifier over 128 characters long, then using DROP ROLE with a too-long argument should result in both 0P000 (for a nonexistent role) and 42622. Would the user see 42622 and not 0P000?
          chaase3 Camilla Haase added a comment -

          Attaching DERBY-3193-3.diff and DERBY-3193-3.zip (no change to the stat file) with, I hope, all the comments incorporated. Please let me know if further changes are needed. Thanks very much!

          chaase3 Camilla Haase added a comment - Attaching DERBY-3193 -3.diff and DERBY-3193 -3.zip (no change to the stat file) with, I hope, all the comments incorporated. Please let me know if further changes are needed. Thanks very much!
          dagw Dag H. Wanvik added a comment -

          Thanks for your good work on this issue, Kim!
          I looked again at the changes, and have a much shorter list this time, mostly stuff I missed the first time around:

          • rrefsqljrevoke:

          > You can also use the REVOKE statement to revoke a role from a user
          or from another role.

          Thanks for adding this sentence. It should also mention "PUBLIC":

          "You can also use the REVOKE statement to revoke a role from a user,
          PUBLIC or from another role."

          • rrefsqljgrant:

          > A role A contains another role B if role B is granted to role A, or
          is contained in a role granted to role A. In this case, the
          privileges identified by role B are inherited by role A.

          Thanks for the improvement, perhaps we can improve it even further:

          "A role A contains another role B if role B is granted to role A, or
          is contained in a role C granted to role A. Privileges granted to a
          contained role are inherited by the containing roles. So, the
          privileges identified by role A is the union of privileges granted
          to A and privileges granted to any contained roles of A."

          • rtoolsijcomrefshow
            > whether available or settable

          Sorry, I was unclear here. Let's strike out "available":

          ".. whether settable for the current session or not."

          > That is, it shows the current role and any role granted to or
          contained in the current role.

          Again, we have two way of saying the same thing (almost), so I suggest
          we stick to the definition here, and strike out "granted to or":

          That is, it shows the current role and any role contained in the
          current role.

          > SHOW SETTABLE ROLES displays all the roles that the current session
          can set, that is, all roles that have been granted to the current
          user.

          Oops, slight omission here (mea culpa), it should read:

          "SHOW SETTABLE_ROLES displays all the roles that the current session
          can set, that is, all roles that have been granted to the current user
          or to PUBLIC."

          Finally, I missed the fact that the syntax has been changed here:
          The implemented syntax entry for roles is:

          ENABLED_ROLES | SETTABLE_ROLES | ROLES |

          Please update all relevant references.

          We might also add that the roles shown by these commands are sorted in
          ascending order. This means the 2 first examples should be adjusted too:

          ij> show roles;
          ROLEID
          ------------------------------
          ANYUSER
          CASUALUSER
          POWERUSER

          3 rows selected
          ij> show enabled_roles;
          ROLEID
          ------------------------------
          ANYUSER
          CASUALUSER

          2 rows selected

          • rdev

          > If the exception for an identifier over 128 characters long applies
          to all statements, does that mean that it always comes up first?

          Yes, with the latest patch I have cooking, it does. We haven't
          normally been very specific in our documentation when it comes to
          error messages, and specifically not when more than one may
          apply. Maybe its best to leave the precedence undefined?

          • cdevcsecureroles:

          This section is wrong (probably wrong is spec as well):

          > If a role loses a privilege to an object, and a session whose
          > current user is different from the owner of that object has a
          > current role which is that role or a role that contains that role,
          > the session also loses that privilege, unless one or more of the
          > following is true:
          > * The role is granted directly to the current user
          > * The role is granted to PUBLIC
          > * The role is also granted to another role in the current role's
          > contained roles
          > * The session's current user is the database owner

          If a privilege to an object is revoked from role A, a session will
          lose that privilege if it has a current role set to A or a role that
          contains A, unless one or more of the
          following is true:

          • The privilege is granted directly to the current user
          • The privilege is granted to PUBLIC
          • The privilege is also granted to another role B in the current role's
            set of contained roles
          • The session's current user is the database owner or the object owner.
          dagw Dag H. Wanvik added a comment - Thanks for your good work on this issue, Kim! I looked again at the changes, and have a much shorter list this time, mostly stuff I missed the first time around: rrefsqljrevoke: > You can also use the REVOKE statement to revoke a role from a user or from another role. Thanks for adding this sentence. It should also mention "PUBLIC": "You can also use the REVOKE statement to revoke a role from a user, PUBLIC or from another role." rrefsqljgrant: > A role A contains another role B if role B is granted to role A, or is contained in a role granted to role A. In this case, the privileges identified by role B are inherited by role A. Thanks for the improvement, perhaps we can improve it even further: "A role A contains another role B if role B is granted to role A, or is contained in a role C granted to role A. Privileges granted to a contained role are inherited by the containing roles. So, the privileges identified by role A is the union of privileges granted to A and privileges granted to any contained roles of A." rtoolsijcomrefshow > whether available or settable Sorry, I was unclear here. Let's strike out "available": ".. whether settable for the current session or not." > That is, it shows the current role and any role granted to or contained in the current role. Again, we have two way of saying the same thing (almost), so I suggest we stick to the definition here, and strike out "granted to or": That is, it shows the current role and any role contained in the current role. > SHOW SETTABLE ROLES displays all the roles that the current session can set, that is, all roles that have been granted to the current user. Oops, slight omission here (mea culpa), it should read: "SHOW SETTABLE_ROLES displays all the roles that the current session can set, that is, all roles that have been granted to the current user or to PUBLIC." Finally, I missed the fact that the syntax has been changed here: The implemented syntax entry for roles is: ENABLED_ROLES | SETTABLE_ROLES | ROLES | Please update all relevant references. We might also add that the roles shown by these commands are sorted in ascending order. This means the 2 first examples should be adjusted too: ij> show roles; ROLEID ------------------------------ ANYUSER CASUALUSER POWERUSER 3 rows selected ij> show enabled_roles; ROLEID ------------------------------ ANYUSER CASUALUSER 2 rows selected rdev > If the exception for an identifier over 128 characters long applies to all statements, does that mean that it always comes up first? Yes, with the latest patch I have cooking, it does. We haven't normally been very specific in our documentation when it comes to error messages, and specifically not when more than one may apply. Maybe its best to leave the precedence undefined? cdevcsecureroles: This section is wrong (probably wrong is spec as well): > If a role loses a privilege to an object, and a session whose > current user is different from the owner of that object has a > current role which is that role or a role that contains that role, > the session also loses that privilege, unless one or more of the > following is true: > * The role is granted directly to the current user > * The role is granted to PUBLIC > * The role is also granted to another role in the current role's > contained roles > * The session's current user is the database owner If a privilege to an object is revoked from role A, a session will lose that privilege if it has a current role set to A or a role that contains A, unless one or more of the following is true: The privilege is granted directly to the current user The privilege is granted to PUBLIC The privilege is also granted to another role B in the current role's set of contained roles The session's current user is the database owner or the object owner.
          chaase3 Camilla Haase added a comment -

          Thanks very much for the additional helpful comments, Dag. I think I will commit the big patch I submitted and then create another smaller patch with just these relatively few changes, to make it easier for you to spot them.

          I agree it's best to leave the exception precedence undefined.

          chaase3 Camilla Haase added a comment - Thanks very much for the additional helpful comments, Dag. I think I will commit the big patch I submitted and then create another smaller patch with just these relatively few changes, to make it easier for you to spot them. I agree it's best to leave the exception precedence undefined.
          chaase3 Camilla Haase added a comment -

          Committed patch DERBY-3193-3.diff to documentation trunk at revision 731664.

          chaase3 Camilla Haase added a comment - Committed patch DERBY-3193 -3.diff to documentation trunk at revision 731664.
          chaase3 Camilla Haase added a comment -

          Attaching DERBY-3193-4.diff, DERBY-3193-4.stat, and DERBY-3193-4.zip, which incorporate the latest comments, and also fix a couple of typos. There are changes to the following files:

          M src/devguide/rdevcsecuresqlauthexceptions.dita
          M src/devguide/cdevcsecureroles.dita
          M src/ref/rrefsqljrevoke.dita
          M src/ref/rrefsqljgrant.dita
          M src/tools/rtoolsijcomrefshow.dita

          Please let me know if more fixes are needed.

          chaase3 Camilla Haase added a comment - Attaching DERBY-3193 -4.diff, DERBY-3193 -4.stat, and DERBY-3193 -4.zip, which incorporate the latest comments, and also fix a couple of typos. There are changes to the following files: M src/devguide/rdevcsecuresqlauthexceptions.dita M src/devguide/cdevcsecureroles.dita M src/ref/rrefsqljrevoke.dita M src/ref/rrefsqljgrant.dita M src/tools/rtoolsijcomrefshow.dita Please let me know if more fixes are needed.
          dagw Dag H. Wanvik added a comment -

          It looks good now, Kim. Again, thanks so much for undertaking the doc work for roles!

          dagw Dag H. Wanvik added a comment - It looks good now, Kim. Again, thanks so much for undertaking the doc work for roles!
          chaase3 Camilla Haase added a comment -

          Thanks, Dag!

          Committed patch DERBY-3193-4.diff to documentation trunk at revision 732365.

          chaase3 Camilla Haase added a comment - Thanks, Dag! Committed patch DERBY-3193 -4.diff to documentation trunk at revision 732365.
          chaase3 Camilla Haase added a comment -

          Changes now appear in latest alpha docs, so closing issue.

          chaase3 Camilla Haase added a comment - Changes now appear in latest alpha docs, so closing issue.

          People

            chaase3 Camilla Haase
            dagw Dag H. Wanvik
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: