Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <94284298.1239629234928.JavaMail.jira@brutus>
Date: Mon, 13 Apr 2009 06:27:14 -0700 (PDT)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4156) SQL Roles - Granting privileges to a
 role before creating it generates weird behavior
In-Reply-To: <1473325925.1239314352925.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12698387#action_12698387 ] 

Rick Hillegas commented on DERBY-4156:
--------------------------------------

Hi Tiago,

I don't believe that there is any special logic which changes the behavior of GRANT/REVOKE based on the kind of authentication Derby is doing. That is, I don't think that there is a separate code path for GRANT/REVOKE if you are using BUILTIN vs. LDAP authentication. I believe we decided that it would be less confusing if GRANT/REVOKE behaved the same way regardless of whether authentication happened via BUILTIN, LDAP, or another user-coded scheme.

In the LDAP and user-coded cases, we don't know what the universe of user names is--the best we can do is calculate the universe of users who are currently logged in. Rather than forbid useful GRANTs to users who aren't logged in, we decided to allow GRANTs to invisible  users.

I wouldn't recommend putting in a separate code path for BUILTIN authentication. I regard BUILTIN as a testing tool and not a production-ready scheme for managing passwords.

Cheers,
-Rick

> SQL Roles - Granting privileges to a role before creating it generates weird behavior
> -------------------------------------------------------------------------------------
>
>                 Key: DERBY-4156
>                 URL: https://issues.apache.org/jira/browse/DERBY-4156
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.0
>         Environment: - Windows Vista x64
> - Sun Java 6
>            Reporter: Tiago R. Espinha
>         Attachments: derby.properties
>
>
> While doing the buddy testing for the SQL roles, I believe I have found a bug.
> Reproduction:
> ij> connect 'jdbc:derby://localhost:1527/testro;user=tiago2;password=alentejo;create=true';
> ij> create table t1 (f1 int, f2 varchar(200));
> 0 rows inserted/updated/deleted
> ij> create role testCreateFirst;
> 0 rows inserted/updated/deleted
> ij> grant select on t1 to testCreateFirst;
> 0 rows inserted/updated/deleted
> ij> grant testCreateFirst to tiago;
> 0 rows inserted/updated/deleted
> ij> grant update on t1 to testWithoutCreate;
> 0 rows inserted/updated/deleted
> ij> grant testWithoutCreate to adm;
> ERROR 0P000: Invalid role specification, role does not exist: 'TESTWITHOUTCREATE'.
> ij> create role testWithoutCreate;
> ERROR X0Y68: User 'TESTWITHOUTCREATE' already exists.
> ---------------------8<----------------------
> This reproduction was made on a freshly created database. Basically I created the table, then created a role and gave it SELECT privileges on that table. Finally I granted the said role to a user (I'm using built-in authentication) and it all went smoothly.
> The problem came when I accidentally forgot to create a role and proceeded to grant privileges to that role. So when we grant some privilege to a non-existing role, instead of getting an error it all seems to go fine. It is when we try to grant this role to a user that we are told that the role does not exist, which makes sense. Still, when I try to create that role afterwards, I get an error message saying that an ->user<- with this username already exists.
> Granting privileges to a non-existing role should issue an error and instead it is probably causing some havoc in the database.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.