Hi Calin,

At 12:54 PM 10/08/2007, you wrote:

>Hi All,
>
>We found a problem when using a UDF that receives the value of a
>subselect from a view. Firebird does parse the access right correctly
>and says that there is no permission for read/select on the
>underlaying table.
>
>Ex:
>/* Make sure there is a user "test" in firebird.*/
>
>create database "c:\test.fdb" user "sysdba" password "masterkey";
>create table test(test integer);
>create view test_view
>as
>select *
>from test;
>grant all on test to view test_view;
>grant all on test_view to public;
>commit;
>insert into test values (1);
>commit;
>select abs((select sum(test) from test_view)) from test_view;
>/*The select statement was succesfull*/
>commit;
>connect c:\test.fdb user "test" password "password";
>select * from test_view;
>DECLARE EXTERNAL FUNCTION ABS
> DOUBLE PRECISION
> RETURNS DOUBLE PRECISION BY VALUE
> ENTRY_POINT 'IB_UDF_abs' MODULE_NAME 'ib_udf';
>commit;
>select abs((select sum(test) from test_view)) from test_view;
>/*last statement raises an exception:
>Statement failed, SQLCODE = -551
>
>no permission for read/select access to COLUMN TEST*/
>quit;
>
>Does anyone know about this problem?

No; but I'd not have written such a query for any reason I can think
of! And, if I had, I would treat it as a self-referencing query and
apply aliasing, viz.

select abs((select sum(v2.test) from test_view v2)) as whatever
from test_view v1;

Could you test whether that throws the same exception? Possibly,
without the correct references, the subquery is being aliased
internally into a structure to which only the owner has privileges...?

./heLen