Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Sep 13, 2006 · 14 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: Sergey Fetiskin (fetis)

Attachment: view_grant.sql [ 10125 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA473 [ QA473 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants