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
