[#CORE-927] Grants don't work for procedures used inside views

Firebird Core

Grants don't work for procedures used inside views

Created: 12/Sep/06 11:45 PM Updated: 06/Dec/11 10:00 AM

Component/s:

Engine

Affects Version/s:

2.0 RC4

Fix Version/s:

2.5.2, 3.0 Alpha 1

Time Tracking:

Not Specified

File Attachments:

view_grant.sql (0.4 kB)

Description

« Hide

This problem similar ~~CORE-93~~ 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.

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Adam Iredale added a comment - 17/Sep/08 05:21 AM

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

[ Permalink | « Hide ]

Pavel Zotov added a comment - 02/Dec/11 06:04 PM - edited

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;