From derby-dev-return-65195-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Mon Dec 22 16:07:09 2008 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 29483 invoked from network); 22 Dec 2008 16:07:09 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Dec 2008 16:07:09 -0000 Received: (qmail 13033 invoked by uid 500); 22 Dec 2008 16:07:08 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 13012 invoked by uid 500); 22 Dec 2008 16:07:08 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 13003 invoked by uid 99); 22 Dec 2008 16:07:08 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Dec 2008 08:07:08 -0800 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Dec 2008 16:07:05 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 66923234C40B for ; Mon, 22 Dec 2008 08:06:44 -0800 (PST) Message-ID: <301730757.1229962004418.JavaMail.jira@brutus> Date: Mon, 22 Dec 2008 08:06:44 -0800 (PST) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-3193) SQL roles: Add documentation In-Reply-To: <30537919.1194621651207.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3193?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658580#action_12658580 ] Dag H. Wanvik commented on DERBY-3193: -------------------------------------- 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." > SQL roles: Add documentation > ---------------------------- > > Key: DERBY-3193 > URL: https://issues.apache.org/jira/browse/DERBY-3193 > Project: Derby > Issue Type: Task > Components: Documentation > Reporter: Dag H. Wanvik > Assignee: Kim Haase > Fix For: 10.5.0.0 > > Attachments: DERBY-3193-2.diff, DERBY-3193-2.stat, DERBY-3193-2.zip, DERBY-3193.diff, DERBY-3193.stat, DERBY-3193.zip, derby3193-tmp.diff, derby3193-tmp.stat > > -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.