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
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;cr
eate=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.
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.
