[
https://issues.apache.org/jira/browse/DERBY-4156?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas closed DERBY-4156.
--------------------------------
Resolution: Invalid
Hi Tiago,
This behavior is decidely weird, but it is expected. What you have run across
is another symptom of Derby's lack of user management. This particular issue is
discussed in section 6.2 of the functional spec attached to the master roles
issue, DERBY-2207. You have tripped across one of the speedbumps at the
intersection of two awkward facts:
1) The ANSI/ISO GRANT/REVOKE language does not distinguish the rolename and
username spaces. Role names and user names live in a single, conflated
namespace called "authorization ids". So, just looking at a GRANT/REVOKE
statement, you can't tell whether it wants to operate on a role or a user.
2) Derby's flexible authentication scheme does not give you any foolproof way
to figure out if an authorization id is the name of an existing user.
Here's what's going on in your problem case:
1) Derby interprets your "grant update on t1 to testWithoutCreate" statement as
a privilege GRANT to a user named TESTWITHOUTCREATE. At this time, Derby
creates a permission tuple to record this GRANT.
2) When you then try to create a role named TESTWITHOUTCREATE, Derby looks to
see if there will be any collisions with that authorization id. Derby sees that
a permission has already been granted to that authorization id so that
authorization id is not available as the name of a new role.
> 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.
