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

No permission for SELECT access to blob field in stored procedure. [CORE5823] #6084

Closed
firebird-issue-importer opened this issue May 10, 2018 · 26 comments

Comments

@firebird-issue-importer

Submitted by: Sergey Borisov (bsv)

Assigned to: @dmitry-starodubov

Is duplicated by CORE6114
Depends on CORE5801
Is duplicated by CORE6107

CREATE ROLE TEST_ROLE;

CREATE TABLE TEST (
ID INTEGER,
BLB BLOB SUB_TYPE 1 SEGMENT SIZE 80
);
INSERT INTO TEST (ID, BLB) VALUES (1, 'blob1');
commit;

SET TERM ^ ;

create procedure TEST_PROC (ID integer)
returns (BLB TYPE OF COLUMN TEST.BLB)
as
begin
FOR SELECT BLB
from TEST
WHERE ID = :ID
into BLB
DO suspend;
end^

SET TERM ; ^

GRANT SELECT ON TEST TO PROCEDURE TEST_PROC;
GRANT EXECUTE ON PROCEDURE TEST_PROC TO TEST_ROLE;
GRANT TEST_ROLE TO USERNAME;

connect 'server:base' user 'USERNAME' role 'TEST_ROLE';

/* Next query mistakenly generate error:
This user does not have privilege to perform this operation on object.
no permission for SELECT access to TABLE TEST.
*/
select * from TEST_PROC(1);

Commits: 50cd232 0c275c1 9d01fb9

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: @hvlad

Blob access check was added in 3.0.4, iirc, see CORE5801
Are you sure you see it at 3.0.3 ?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Modified by: @hvlad

Link: This issue depends on CORE5801 [ CORE5801 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Modified by: Sergey Borisov (bsv)

Version: 3.0.4 [ 10863 ]

Version: 3.0.3 [ 10810 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: Sergey Borisov (bsv)

You're right. 3.0.4.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: Sergey Borisov (bsv)

Where is the description of the CORE5801? I can not find.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Modified by: @AlexPeshkoff

assignee: Roman Simakov [ roman-simakov ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Modified by: @AlexPeshkoff

assignee: Roman Simakov [ roman-simakov ] => Dmitriy Starodubov [ dmitriy starodubov ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: @hvlad

It is about unauthorized access to blobs. Details will be available when 3.0.4 gets released.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: Nick (nick)

From one side it is a security. From other side it is not so useful to grant access to table.
I think we must to have parameter in config for disabling this security feature.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: @asfernandes

Agree with Nick. For example, how it will be possible that a view or SP returns a subset (including blob) of a table that the user can't access directly?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 10, 2018

Commented by: @hvlad

So, blob access check should be more smart, just that.

First, if PSQL unit have own privileges to access blob (table) and want to return such blob to the client - should it be allowed or we need to introduce additional privilege ?
Second, if it is allowed, such blob id (s) should be marked somehow and bypass additional check of access.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 11, 2018

Commented by: Sergey Borisov (bsv)

If I change the type of the returned parameter to varhar (10000) is s/p, then the error does not occur. Is this normal behavior?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 18, 2018

Commented by: Nick (nick)

There is another way for implementation:
if blob id was selected in active connection then it can be accessed from this connection at any time. But can't be accessed from another.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 19, 2018

Commented by: @hvlad

Sergey,

can't say if it is normal, but it is expected.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 15, 2018

Modified by: @dmitry-starodubov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 12, 2018

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 12, 2018

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2019

Commented by: Pavel (sense)

This access check creates a lot of problems for me in my program. I need to give "publuc select" to the whole table if there is a blob. In my opinion, build 3.0.3 in this regard is more correct as I have to publish more data to the pact fact. (It turns out that the incident of aggravating the control in this way makes any control refuse at all). Version 3.0.5 on which the bug is closed, as I understand it, simply declares it as the correct behavior. Because I did not see any changes in 3.0.5. In my opinion, this behavior violates a fairly well-defined rights understanding scheme that coalesced in Firebird to 3.0.4.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2019

Commented by: @dyemanov

It wasn't "just closed", the fix was committed 21-Jan-2019.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2019

Commented by: @hvlad

PSQL unit's privileges still have no effect after the fix, seems we need to rethink it

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2019

Commented by: @hvlad

Re-check it again and now i see that

GRANT EXECUTE ON PROCEDURE TO USERNAME;

is enough for USERNAME to execute procedure and access blob, even without a TEST_ROLE.
So, i see no problem currently.

Pavel,
why do you think you need to grant SELECT privs to PUBLIC ?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 6, 2019

Commented by: Pavel (sense)

Yes, it really works on a test example. However, I definitely have a problem in my application. While I could not highlight the essence. I already see that in my case the error does not occur during the "select". The error occurs when the BLOB itself is read on isc_open_blob2.

I still keep looking for the reason for this behavior.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 6, 2019

Commented by: @hvlad

Pavel,

provide me with reproducible test case and i could help to find the reason

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 6, 2019

Commented by: Pavel (sense)

Thanks for the response Vlad!

All the same, I found a problem. It was definitely my fault.

However, you may be wondering what happened:

I closed the query and even changed the transaction after select. Then I tried to call the blob (query closed, transaction changed). isc_open_blob2 retur error select privilegy for table ....

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 24, 2019

Modified by: @dyemanov

Link: This issue is duplicated by CORE6107 [ CORE6107 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 2, 2019

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE6114 [ CORE6114 ]

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

1 participant