Issue Details (XML | Word | Printable)

Key: CORE-927
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Sergey Fetiskin
Votes: 4
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Grants don't work for procedures used inside views

Created: 12/Sep/06 11:45 PM   Updated: 27/Mar/14 12:25 PM
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: 1. File view_grant.sql (0.4 kB)

Issue Links:
Relate
 


 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: Ascending order - Click to sort in descending order
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


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;