Hi,

 

I am working on migrating a DB and application from Firebird 2.5 (dialect 1) to Firebird 3.0 dialect 3. The database is now dialect 3 and running a Firebird 3.0 default installation on a Windows 10 server.

 

Mostly it has gone quite smoothly, but I’ve encountered this problem, which seems a little strange. I’ve found a workaround, but I thought I’d post here in the hope I can understand what I’m missing, or perhaps document a bug.

 

I have a somewhat bizarre view:

CREATE VIEW PROVIDER_ORG_VIEW(

    PROVIDER_NO, …)

AS

select p.* from provider P

join current_organisation co on exists(select 1

                                                          from user_organisation uo

                                                          where uo.ib_username = p.logon_user_name

                                                          and uo.parameters_organisation_no = co.parameters_organisation_no)

                                                     or p.logon_user_name is null

 

grants are:

GRANT SELECT ON CURRENT_ORGANISATION TO PUBLIC; -- CURRENT_ORGANISATION is a global temporary table

GRANT SELECT ON PROVIDER_ORG_VIEW TO PUBLIC;

GRANT SELECT ON USER_ORGANISATION TO VIEW PROVIDER_ORG_VIEW;

 

When a non-administrative user attempts to select from PROVIDER_ORG_VIEW, it gets an exception:

“This user does not have privilege to perform this operation on this object.

No permission for SELECT access to TABLE USER_ORGANISATION.”

 

If I change the grant on user_organisation to:

GRANT SELECT ON USER_ORGANISATION TO public;

Then the select on the view works OK.

 

Also, if I remove the EXISTS and join all the same tables in the view, then there is no exception, but the results are different of course, so it doesn’t really work.

 

Any help or insight will be greatly appreciated 😊

 

Thanks,

 

Brian