Re: Issues with SQL Roles
[email protected] (Dag H. Wanvik) writes: > "A result set that depends on a role will remain open even if that > role is revoked from a user." > > This is true, but holds more generally; revoke actions (privileges, > roles) do not invalidate open result sets. I added DERBY-4164 to clarify this. Dag
Re: Issues with SQL Roles
[email protected] (Dag H. Wanvik) writes: >> "Any open result sets will remain usable as before, since these remain >> open; even though the old (base)activation is no longer referenced >> from the GenericActivationHolder, there is a reference to the old >> activation from the result set, so it stays alive." > > Yes, this is the current behavior. I think we should keep this. The > privilege checking occurs at execute time (when the result set is > constructed), and I guess it's logical that if you can see *one* row > with a SELECT privilege, you should be able to see them all. In any > case, prefetching of rows at several levels in Derby makes it hard to > present a consistent picture if we chose to try to make enforcement > immediate. I could not find anything in the standard on this. > > It is not there alrady it should be mentioned in the docs. The behavior is mentioned here: http://db.apache.org/derby/docs/dev/devguide/cdevcsecureroles.html See the section "Revoking roles": "A result set that depends on a role will remain open even if that role is revoked from a user." This is true, but holds more generally; revoke actions (privileges, roles) do not invalidate open result sets. Dag
Re: Issues with SQL Roles
Rick Hillegas writes: > Hi Tiago, > > I believe that this behavior is deliberate and expected. I believe > that permissions are not checked on every fetch from a ResultSet but > that, instead, an in-flight ResultSet remains usable even though > permissions may change after the ResultSet is opened. This, at least, > is the sense I get from Dag's August 26, 2008 comment on > http://issues.apache.org/jira/browse/DERBY-3223 Here's a quote from > the comment: > > "Any open result sets will remain usable as before, since these remain > open; even though the old (base)activation is no longer referenced > from the GenericActivationHolder, there is a reference to the old > activation from the result set, so it stays alive." Yes, this is the current behavior. I think we should keep this. The privilege checking occurs at execute time (when the result set is constructed), and I guess it's logical that if you can see *one* row with a SELECT privilege, you should be able to see them all. In any case, prefetching of rows at several levels in Derby makes it hard to present a consistent picture if we chose to try to make enforcement immediate. I could not find anything in the standard on this. It is not there alrady it should be mentioned in the docs. Dag
Re: Issues with SQL Roles
Kathey Marsden writes: > Kim Haase wrote: >> >> I can file (and fix) an issue for this if you think it makes sense. >> > Thanks Kim for offering to jump on this. Let's see what folks have to > say about Tiago's comment on this first and determine whether a > behavior change or a change in documentation is most appropriate. I think it would be good to add a possibility to have a default role set on connection (as an option), since this is allowed by the standard in that is implementation defined. Feel free to add an improvement issue to that effect, Tiago! For now, I suggest we just clarify the docs as suggested; an example is good, thanks Kim! Dag
Re: Issues with SQL Roles
Tiago Espinha wrote: Kathey, here's what I have so far: http://docs.google.com/Doc?id=dgdrw3xt_3fp3pbngf Thank you Tiago for testing SQL Roles. After some hiccups with the user documentation for SQL Roles (which ended up being my fault), I managed to get the roles working without the need to check any additional documentation. I think the suggestion to make it clearer in the documentation that a role needs to be explicitly set to be used was a good one. Please file an issue for that. I also made some functional tests (those on the doc.) and maybe I should/could make them a JUnit test? Please check RolesTest to see if any of these cases are not covered and add them if they are not. I'm also open to more suggestions for tests, stuff where the roles can break so if anyone has any ideas I'd appreciate it. I'll think about it but it looks like Dag has stumped us so far. Kathey
Re: Issues with SQL Roles
Hello Rick, Thank you for your input. If that is expected then so much the better, since it is not a bug. Kathey, here's what I have so far: http://docs.google.com/Doc?id=dgdrw3xt_3fp3pbngf After some hiccups with the user documentation for SQL Roles (which ended up being my fault), I managed to get the roles working without the need to check any additional documentation. I also made some functional tests (those on the doc.) and maybe I should/could make them a JUnit test? I'm also open to more suggestions for tests, stuff where the roles can break so if anyone has any ideas I'd appreciate it. Tiago On Mon, Apr 13, 2009 at 4:02 PM, Rick Hillegas wrote: > Hi Tiago, > > I believe that this behavior is deliberate and expected. I believe that > permissions are not checked on every fetch from a ResultSet but that, > instead, an in-flight ResultSet remains usable even though permissions may > change after the ResultSet is opened. This, at least, is the sense I get > from Dag's August 26, 2008 comment on > http://issues.apache.org/jira/browse/DERBY-3223 Here's a quote from the > comment: > > "Any open result sets will remain usable as before, since these remain > open; even though the old (base)activation is no longer referenced > from the GenericActivationHolder, there is a reference to the old > activation from the result set, so it stays alive." > > Dag may want to comment further. > > Hope this helps, > -Rick > > > Tiago Espinha wrote: >> >> Hey everyone, >> >> I have been throwing tests at the SQL Roles. At this point I get a >> certain behavior and perhaps Rick or Dag can give me some insight on >> whether this is the expected behavior or if it is a bug. >> >> So, I'm not totally sure of how cursors work underneath, and I decided >> to run a simple test: >> >> 1. Create the role testCursor with SELECT privileges on table t1 >> 2. Grant the role testCursor to the userTest >> 3. With userTest get a cursor for t1 >> 4. Without closing the cursor, revoke the SELECT privileges to userTest >> 5. Try to move the cursor forward >> >> Here the cursor keeps going until it reaches the end of the table; >> this is, revoking the privileges doesn't affect the cursor. Is this >> expected? >> >> Tiago >> > >
Re: Issues with SQL Roles
Hi Tiago, I believe that this behavior is deliberate and expected. I believe that permissions are not checked on every fetch from a ResultSet but that, instead, an in-flight ResultSet remains usable even though permissions may change after the ResultSet is opened. This, at least, is the sense I get from Dag's August 26, 2008 comment on http://issues.apache.org/jira/browse/DERBY-3223 Here's a quote from the comment: "Any open result sets will remain usable as before, since these remain open; even though the old (base)activation is no longer referenced from the GenericActivationHolder, there is a reference to the old activation from the result set, so it stays alive." Dag may want to comment further. Hope this helps, -Rick Tiago Espinha wrote: Hey everyone, I have been throwing tests at the SQL Roles. At this point I get a certain behavior and perhaps Rick or Dag can give me some insight on whether this is the expected behavior or if it is a bug. So, I'm not totally sure of how cursors work underneath, and I decided to run a simple test: 1. Create the role testCursor with SELECT privileges on table t1 2. Grant the role testCursor to the userTest 3. With userTest get a cursor for t1 4. Without closing the cursor, revoke the SELECT privileges to userTest 5. Try to move the cursor forward Here the cursor keeps going until it reaches the end of the table; this is, revoking the privileges doesn't affect the cursor. Is this expected? Tiago
Re: Issues with SQL Roles
Hey everyone, I have been throwing tests at the SQL Roles. At this point I get a certain behavior and perhaps Rick or Dag can give me some insight on whether this is the expected behavior or if it is a bug. So, I'm not totally sure of how cursors work underneath, and I decided to run a simple test: 1. Create the role testCursor with SELECT privileges on table t1 2. Grant the role testCursor to the userTest 3. With userTest get a cursor for t1 4. Without closing the cursor, revoke the SELECT privileges to userTest 5. Try to move the cursor forward Here the cursor keeps going until it reaches the end of the table; this is, revoking the privileges doesn't affect the cursor. Is this expected? Tiago
Re: Issues with SQL Roles
Tiago Espinha wrote: Hello Kathey, I overlooked this. I thought that roles were set and used on a by-user basis; personally I find it a little counter-productive that the role has to be chosen on every session. I am not totally sure how it goes on SQL Server or MySQL for example, but at least Oracle loads up all the privileges set to that user through roles. Then you can choose during that session whether you want to enable or disable certain roles: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10004.htm We might want to have a look into what's the most common behavior and stick with that one; if on the other hand we are sticking to our way on this and it differs from the other RDBMS' way then we might want indeed to make this more explicit and obvious. I think it is easy to overlook the fact that you have to SET a role on top of having been granted that role. Hi Tiago, Thanks for testing this feature. At startup, a Derby session does not have a default role. This is discussed in section 5.2 of the functional specification attached to the master JIRA for roles, DERBY-2207. Having a fresh set of eyes look at this feature is very helpful. As the spec notes, according to the ANSI/ISO standard, the default role for a session is vendor-specific. Derby is starting out neutral on this topic. Thanks, -Rick Tiago On Thu, Apr 9, 2009 at 5:00 PM, Kathey Marsden mailto:[email protected]>> wrote: Tiago Espinha wrote: 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'. I think before you do the select, you have to set the role, e.g ij>set role readRole; Should we make this clearer in the documentation? http://db.apache.org/derby/docs/dev/devguide/cdevcsecureroles.html Kathey
Re: Issues with SQL Roles
Sure, that'll be fine. No rush. Kim On 04/09/09 13:46, Kathey Marsden wrote: Kim Haase wrote: I can file (and fix) an issue for this if you think it makes sense. Thanks Kim for offering to jump on this. Let's see what folks have to say about Tiago's comment on this first and determine whether a behavior change or a change in documentation is most appropriate. Kathey
Re: Issues with SQL Roles
Kim Haase wrote: I can file (and fix) an issue for this if you think it makes sense. Thanks Kim for offering to jump on this. Let's see what folks have to say about Tiago's comment on this first and determine whether a behavior change or a change in documentation is most appropriate. Kathey
Re: Issues with SQL Roles
On 04/09/09 12:00, Kathey Marsden wrote: Tiago Espinha wrote: 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'. I think before you do the select, you have to set the role, e.g ij>set role readRole; Should we make this clearer in the documentation? http://db.apache.org/derby/docs/dev/devguide/cdevcsecureroles.html Good idea. Probably in the "Setting Roles" section something like this should be added: For example, if you created and granted the roles shown in the previous session, you would have to issue a SET ROLE statement to have them take effect. For example, suppose you used the following statement; SET ROLE taskLeaderA; If the database owner granted the taskLeaderA role to a user, that user would have all the privileges associated with the taskLeaderA, updateUser and readUser roles. I can file (and fix) an issue for this if you think it makes sense. Kim
Re: Issues with SQL Roles
Hello Kathey, I overlooked this. I thought that roles were set and used on a by-user basis; personally I find it a little counter-productive that the role has to be chosen on every session. I am not totally sure how it goes on SQL Server or MySQL for example, but at least Oracle loads up all the privileges set to that user through roles. Then you can choose during that session whether you want to enable or disable certain roles: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10004.htm We might want to have a look into what's the most common behavior and stick with that one; if on the other hand we are sticking to our way on this and it differs from the other RDBMS' way then we might want indeed to make this more explicit and obvious. I think it is easy to overlook the fact that you have to SET a role on top of having been granted that role. Tiago On Thu, Apr 9, 2009 at 5:00 PM, Kathey Marsden wrote: > Tiago Espinha wrote: > >> 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'. >> > > I think before you do the select, you have to set the role, e.g > ij>set role readRole; > > Should we make this clearer in the documentation? > http://db.apache.org/derby/docs/dev/devguide/cdevcsecureroles.html > > Kathey >
Re: Issues with SQL Roles
Tiago Espinha wrote: 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'. I think before you do the select, you have to set the role, e.g ij>set role readRole; Should we make this clearer in the documentation? http://db.apache.org/derby/docs/dev/devguide/cdevcsecureroles.html Kathey
Re: Issues with SQL Roles
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 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 > > >
Re: Issues with SQL Roles
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
Re: Issues with SQL Roles
Hello François,
I believe I had indeed set that property before creating the database. Just
to be sure, I did as you said and set it as a database property by calling
that statement. However, after doing so, I'm still able to access the
'adm.t2' table as I wish from the 'tiago' user.
I'm also sure that Derby is seeing the correct properties file. I tried to
login with a wrong password and I wasn't allowed in.
Any more ideas?
Tiago
On Sat, Apr 4, 2009 at 9:35 PM, Francois Orsini
wrote:
> Hi Tiago,
>
> Did you set 'derby.database.sqlAuthorization=TRUE' *before* creating the
> database?
>
> If not, you would have to specifically set it for this database using:
> ij> CALL
> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','true');
>
> http://db.apache.org/derby/docs/dev/devguide/cdevcsecure866060.html
>
> Otherwise, make sure your derby.properties is actually accessed by the
> Derby instance - For example, try to connect with an incorrect password and
> see if it denies the connection for a user.
>
> --francois
>
>
> On Sat, Apr 4, 2009 at 5:03 AM, Tiago Espinha wrote:
>
>> Hi everyone,
>>
>> I am testing the SQL Roles for the 10.5 buddy testing and I'm facing
>> some issues. By following the documentation, I have created the
>> following derby.properties file:
>>
>> derby.connection.requireAuthentication=TRUE
>> derby.database.sqlAuthorization=TRUE
>> derby.authentication.provider=BUILTIN
>> derby.user.adm=java
>> derby.user.tiago=espinha
>>
>> Now, the steps I've followed afterwards (on ij) are:
>> > connect
>> 'jdbc:derby://localhost:1527/goodone;create=true;user=adm;password=java';
>>
>> And the database gets created properly. Then I proceed to create a
>> test table, while still logged in as 'adm':
>> > create table t2 (f1 int, f2 varchar(20));
>>
>> Then I disconnect; from the server and connect back on, this time with
>> the user 'tiago':
>> > connect
>> 'jdbc:derby://localhost:1527/goodone;user=tiago;password=espinha';
>>
>> At this point I am logged in but, opposed to what I would think, I
>> have access to the table I just created with 'adm'. I do have to
>> specify the 'adm' schema to access it but I do not have any
>> restrictions whatsoever. I can INSERT, SELECT and even DROP the table
>> without any restriction. By default I should not have any privileges
>> at all over the table adm.t2 should I?
>>
>> Can someone help me out and tell me what exactly am I doing wrong?
>> Keep in mind that I would like to have only the SQL authentication
>> turned on.
>>
>> Thanks in advance,
>> Tiago
>>
>
>
Re: Issues with SQL Roles
Hi Tiago,
Did you set 'derby.database.sqlAuthorization=TRUE' *before* creating the
database?
If not, you would have to specifically set it for this database using:
ij> CALL
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','true');
http://db.apache.org/derby/docs/dev/devguide/cdevcsecure866060.html
Otherwise, make sure your derby.properties is actually accessed by the Derby
instance - For example, try to connect with an incorrect password and see if
it denies the connection for a user.
--francois
On Sat, Apr 4, 2009 at 5:03 AM, Tiago Espinha wrote:
> Hi everyone,
>
> I am testing the SQL Roles for the 10.5 buddy testing and I'm facing
> some issues. By following the documentation, I have created the
> following derby.properties file:
>
> derby.connection.requireAuthentication=TRUE
> derby.database.sqlAuthorization=TRUE
> derby.authentication.provider=BUILTIN
> derby.user.adm=java
> derby.user.tiago=espinha
>
> Now, the steps I've followed afterwards (on ij) are:
> > connect
> 'jdbc:derby://localhost:1527/goodone;create=true;user=adm;password=java';
>
> And the database gets created properly. Then I proceed to create a
> test table, while still logged in as 'adm':
> > create table t2 (f1 int, f2 varchar(20));
>
> Then I disconnect; from the server and connect back on, this time with
> the user 'tiago':
> > connect
> 'jdbc:derby://localhost:1527/goodone;user=tiago;password=espinha';
>
> At this point I am logged in but, opposed to what I would think, I
> have access to the table I just created with 'adm'. I do have to
> specify the 'adm' schema to access it but I do not have any
> restrictions whatsoever. I can INSERT, SELECT and even DROP the table
> without any restriction. By default I should not have any privileges
> at all over the table adm.t2 should I?
>
> Can someone help me out and tell me what exactly am I doing wrong?
> Keep in mind that I would like to have only the SQL authentication
> turned on.
>
> Thanks in advance,
> Tiago
>
