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