Hello again everyone, I think I have hit yet another speed bump with the SQL Roles. After ironing out the previous issue (François was right, I had created the table *before* I set the sqlAuthentication on the derby.properties file), I have now ran into another one that is just as odd.
Here's what I did: 1) Connected with the user 'adm' and created a test table: > create table t1 (f1 int, f2 varchar(200)); 2) Created the role readRole: > create role readRole; 3) Granted SELECT on t1 to readRole: > grant select on t1 to readRole; 4) Granted readRole to another user, 'tiago': > grant readRole to TIAGO; 5) Tried to use TIAGO on another ij spawn and here is what I get: ij> select * from adm.t1; ERROR 42502: User 'TIAGO' does not have SELECT permission on column 'F1' of table 'ADM'.'T1'. So what exactly can I be missing? I have followed the user documentation and I cannot find where might have I failed. This should be the most basic operation to do and I can't seem to get it to work. Any ideas? My 'tiago' user was connected at the time of the table and role creation, but I have since tried disconnecting it and getting it back on, with no luck. I have also tried explicitly defining which columns the role has SELECT privileges on, also with no luck: > grant select(f1,f2) on t1 to readRole; Tiago On Mon, Apr 6, 2009 at 3:26 PM, Kathey Marsden <[email protected]>wrote: > > Tiago Espinha wrote: > >> Hi everyone, >> >> I am testing the SQL Roles for the 10.5 buddy testing and I'm facing >> some issues. >> > Hi Tiago, > > I followed the steps you describe and get the following error when tiago > tries to select from the table. > 1) created derby.properties, cut and paste from mail > 2) java org.apache.derby.drda.NetworkServerControl start & > > >java org.apache.derby.tools.ij > ij> connect > 'jdbc:derby://localhost:1527/goodone;create=true;user=adm;password=java'; > ij> create table t2 (f1 int, f2 varchar(20)); > 0 rows inserted/updated/deleted > ij> exit; > [C:/kmarsden/projects/10.5testing/sqlroles] java org.apache.derby.tools.ij > ij version 10.5 > ij> connect > 'jdbc:derby://localhost:1527/goodone;user=tiago;password=espinha'; > ij> select * from adm.t2; > ERROR 42502: User 'TIAGO' does not have SELECT permission on column 'F1' of > table 'ADM'.'T2'. > ij> > > I am not sure why you are hitting this problem. > > Kathey > > >
