db-derby-dev mailing list archives

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

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

Tiago R. Espinha commented on DERBY-4156:

Hello Rick,

There is just one thing that I am not quite getting yet. If I'm using built-in authentication,
why would Derby create a permission tuple for a user that does not exist? If I am not mistaken,
on built-in authentication the only way to have users added to our system is by adding them
to the derby.properties file; that is, you cannot have users added manually through DDL.

I just think that seeing that the standard stipulates the GRANT/REVOKE should work across
both users and roles, then maybe we simply should not create that permission tuple to accommodate
the GRANT.

If no role or user exists with the specified name, then in my opinion an error should be thrown...
unless of course, the standard also mentions the behavior seen in Derby.


> 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