Details
-
Task
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
Attachments
Attachments
- DERBY-3193-4.zip
- 19 kB
- Camilla Haase
- DERBY-3193-4.stat
- 0.2 kB
- Camilla Haase
- DERBY-3193-4.diff
- 9 kB
- Camilla Haase
- DERBY-3193-3.zip
- 67 kB
- Camilla Haase
- DERBY-3193-3.diff
- 83 kB
- Camilla Haase
- DERBY-3193-2.zip
- 66 kB
- Camilla Haase
- DERBY-3193-2.stat
- 1 kB
- Camilla Haase
- DERBY-3193-2.diff
- 73 kB
- Camilla Haase
- DERBY-3193.zip
- 44 kB
- Camilla Haase
- DERBY-3193.stat
- 0.7 kB
- Camilla Haase
- DERBY-3193.diff
- 46 kB
- Camilla Haase
- derby3193-tmp.stat
- 0.2 kB
- Dag H. Wanvik
- derby3193-tmp.diff
- 15 kB
- Dag H. Wanvik
Issue Links
- is part of
-
DERBY-2207 Improve usability of Derby's client/server security by implementing ANSI Roles
- Closed
Activity
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."
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.
> 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)
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.
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.
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.
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.
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.
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.
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.
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."
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?
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!
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.
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.
Committed patch DERBY-3193-3.diff to documentation trunk at revision 731664.
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.
It looks good now, Kim. Again, thanks so much for undertaking the doc work for roles!
Thanks, Dag!
Committed patch DERBY-3193-4.diff to documentation trunk at revision 732365.
It now looks as if SQL Roles will be documented at 10.5, not 10.4.