db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Closed: (DERBY-4156) SQL Roles - Granting privileges to a role before creating it generates weird behavior
Date Fri, 10 Apr 2009 17:47:14 GMT

     [ 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:
>         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
> 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.

View raw message