Grants don't work for procedures used inside views [CORE927]

[firebird-automations]

Submitted by: Sergey Fetiskin (fetis)

Is related to QA473

Attachments:
view_grant.sql

Votes: 4

This problem similar CORE93 except that view have subselect with stored procedure.
Script (in attach) illustrate the problem. TEST is test user without any rights before.

After executing script, when you try do (logged as user TEST)

select *
from VIEW_A

you get

This user does not have privilege to perform this operation on this object.
no permission for execute access to PROCEDURE A.

Commits: efa1adf 8ffffb7 1575ff5 6706f10

[firebird-automations]

Modified by: Sergey Fetiskin (fetis)

Attachment: view_grant.sql [ 10125 ]

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: Sergey Fetiskin (fetis)

description: This problem similar CORE93 except that view have subselect with stored procedure.
Script (in attach) illustrate the problem. TEST is test user without any rights before.

=>

This problem similar CORE93 except that view have subselect with stored procedure.
Script (in attach) illustrate the problem. TEST is test user without any rights before.

After executing script, when you try do (logged as user TEST)

select *
from VIEW_A

you get

This user does not have privilege to perform this operation on this object.
no permission for execute access to PROCEDURE A.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 11201 ] => Firebird [ 14697 ]

[firebird-automations]

Commented by: Adam Iredale (ai.software.engineer)

This is still an issue in 2.1.1.17910.

I have created a view that relies on stored procedures to build it's data.

CREATE VIEW V_SOMETHING
AS
SELECT VD.*
FROM V_DATA VD
WHERE VD.DATAID IN (
SELECT ALLOWEDID
FROM P_CURRENT_USER_ALLOWED_IDS('V_DATA', 'DATAID', (
SELECT ACCESS_TYPE_READ
FROM P_CONSTANTS
)
)
);

The view has been granted access to both procedures and the role that the user is logged in with has been granted permission to use the view.

An error occurs when trying to SELECT * FROM V_SOMETHING:

Message: isc_dsql_prepare failed

SQL Message : -551
This user does not have privilege to perform this operation on this object.

Engine Code : 335544352
Engine Message :
no permission for execute access to PROCEDURE P_CONSTANTS

[firebird-automations]

Commented by: @pavel-zotov

Still the same in 2.5.2.26390

DDL:
create or alter view v_proced as select 1 id from rdb$database;
create or alter view v_direct as select 1 id from rdb$database;
set term ^;
create or alter procedure p_info as begin end^
set term ;^
commit;
recreate table agt(id int, f01 int);
commit;
insert into agt values(1,123);
commit;
revoke all on all from tu0; -- user 'tu0' will be created, see below
commit;
------------------------------
set term ^;
-- the `p_info` is SP about existence of which user `tu0` should NOT know
create or alter procedure p_info returns(id int, f01 int)
as
begin
for select id,f01 from agt into id,f01 do suspend;
end^
set term ;^
commit;
grant select on agt to procedure p_info;
commit;
------------------------------
create or alter view v_proced as
select id,f01 from p_info;
commit;
grant select on v_proced to tu0;
commit;
------------------------------
create or alter view v_direct as
select id,f01 from agt;
commit;
grant select on v_direct to tu0;
commit;
------------------------------
drop user tu0;
create user tu0 password 'tu0';
commit;
--------------------------------
-- The following GRANT statement MUST present.
-- Otherwise the user `tu0` will not be able to select from view v_proced.
-- Since the GRANT on SP `p_info` is given to `tu0` this user can know that SP `p_info` is really present:
grant execute on procedure p_info to tu0;
commit;

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

summary: Grants for view with stored procedures (see also CORE93) => Grants don't work for procedures used inside views

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA473 [ QA473 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]