[jira] Commented: (DERBY-4191) Lack of SELECT privilege does not prevent SELECT COUNT(*)

[Rick Hillegas (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DERBY-4191?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12789368#action_12789368
 ] 

Rick Hillegas commented on DERBY-4191:
--------------------------------------

Thanks for the new patch, Mamta. I think this is moving in the right direction. 
I have a couple comments:

1) It's hard to tell whether the new tests cover the cases where permissions 
come from roles rather than from direct grants. It would be good to write some 
tests for those cases.

2) After applying this patch, the following query succeeds if the user has 
update privilege on the table but not select privilege. I believe that this 
query should fail because the user does not have select privilege:

    update admin.t set a = ( select max(a) + 2 from admin.t );

Here is a full script which shows this problem:

connect 'jdbc:derby:memory:dummy;create=true;user=admin;password=adminpassword';

create table t( a int, b int );

insert into t( a, b ) values ( 1, 1 );
call syscs_util.syscs_set_database_property( 'derby.database.sqlAuthorization', 
'true' );

connect 
'jdbc:derby:memory:dummy;shutdown=true;user=admin;password=adminpassword';

connect 'jdbc:derby:memory:dummy;user=admin;password=adminpassword';

grant update on t to public;

connect 'jdbc:derby:memory:dummy;user=ruth;password=ruthpassword';

-- fails
select * from admin.t;

update admin.t set a = 2;

-- succeeds but should not
update admin.t set a = ( select max(a) + 2 from admin.t );


> Lack of SELECT privilege does not prevent SELECT COUNT(*)
> ---------------------------------------------------------
>
>                 Key: DERBY-4191
>                 URL: https://issues.apache.org/jira/browse/DERBY-4191
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0, 10.5.1.1
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>         Attachments: 
> DERBY4191_ColumnLevelCheckInStatmentColumnPerm_diff_patch2.txt, 
> DERBY4191_ColumnLevelCheckInStatmentColumnPerm_stat_patch2.txt, 
> DERBY4191_ColumnLevelCheckInStatmentTablePerm_diff_patch1.txt, 
> DERBY4191_countStar_privilege_diff_patch1.txt, 
> DERBY4191_miniumSelectPrivOnAllTables_diff_patch3.txt, 
> DERBY4191_miniumSelectPrivOnAllTables_stat_patch3.txt, repro.sql
>
>
> A user that does not have SELECT privilege on a table can still perform a 
> SELECT COUNT(*) on that table. Counting a specific column (e.g., SELECT 
> COUNT(X)) is prevented.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.