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

Jump to bottom

isc_open_blob2 fails when selecting blob field from view with "no permission for SELECT access to TABLE X" error. [CORE6114] #6363

Closed
firebird-automations opened this issue Aug 2, 2019 · 15 comments
Closed

isc_open_blob2 fails when selecting blob field from view with "no permission for SELECT access to TABLE X" error. [CORE6114] #6363

firebird-automations opened this issue Aug 2, 2019 · 15 comments
Labels
affect-version: 3.0.4 affect-version: 3.0.5 component: security priority: major resolution: wontfix type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Andrew Ayre (communicare)

Assigned to: @dmitry-starodubov

Duplicates CORE5823
Is related to CORE6107

Attachments:
CreateDb.sql

Votes: 1

Error occurs in Firebird 3.0.4 and daily snapshot 3.0.5.33160.

Error "no permission for SELECT access to TABLE BLOB_TABLE"

When attempting to select BLOB from VIEW that DOES have has permission to select from BLOB_TABLE.

Reproduce as follows:

Create a test database containing a single table (BLOB_TABLE), single view (BLOB_VIEW) with grants that allow public to only to the view.
Grant all on BLOB_TABLE to VIEW BLOB_VIEW.
Use SQL the following script to create test database below:

--=== Start Script ===--
CREATE TABLE BLOB_TABLE (
ID INTEGER NOT NULL,
TEXT_VALUE VARCHAR(50),
BLOB_VALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
BLOB_BINARY BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
COMMIT;

CREATE VIEW BLOB_VIEW(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
AS
SELECT ID, TEXT_VALUE, BLOB_VALUE, BLOB_BINARY FROM BLOB_TABLE;
COMMIT;

ALTER TABLE BLOB_TABLE ADD CONSTRAINT PK_BLOB_TABLE PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER TRIGGER BIUD_BLOB_VIEW FOR BLOB_VIEW
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
if (INSERTING) then
begin
INSERT INTO BLOB_TABLE(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
values (
http://new.ID,
new.TEXT_VALUE,
new.BLOB_VALUE,
new.BLOB_BINARY);
end

if (UPDATING) then
begin
UPDATE BLOB_TABLE SET
TEXT_VALUE = new.TEXT_VALUE,
BLOB_VALUE = new.BLOB_VALUE,
BLOB_BINARY = new.BLOB_BINARY
WHERE
ID = http://old.ID;
end

if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = http://old.ID;
END^

COMMIT^

SET TERM ; ^

GRANT ALL ON BLOB_VIEW TO PUBLIC;
GRANT ALL ON BLOB_TABLE TO TRIGGER BIUD_BLOB_VIEW;
COMMIT;

INSERT INTO BLOB_TABLE (ID, TEXT_VALUE, BLOB_VALUE) VALUES (1, 'test data', x'414243');
COMMIT;

--=== End Script ===--

We are moving to Firebird 3 from Firebird 2.5.8. which does NOT have this bug.
Our Delphi application uses IBO 4.8.7 to access the Firebird database.
We have also tested using newer version of IBO (Ver 5.9.7) with the same result.

Tracing the Firebird API calls from IBO we can see the following pattern.

isc_attach_database(db: 1)
isc_dsql_allocate_statement(st: 1)
isc_start_multiple(tr: 1)
isc_dsql_prepare(tr: 1, st: 1)
isc_dsql_execute(tr: 1, st: 1)
isc_dsql_fetch(st: 1)
isc_commit_transaction(tr: 1)
isc_start_multiple(tr: 2)
isc_open_blob2(tr: 2, blob:ID)

A simpler pattern that produces the same error is below. In the following steps it is assumed that we know the ID of the BLOB to be opened.

We have further narrowed the error down as follows:
isc_attach_database(db: 1)
isc_start_multiple(tr: 1)
isc_open_blob2(tr: 1, blob:ID)
@firebird-automations
Copy link
Collaborator Author

Modified by: Andrew Ayre (communicare)

Version: 3.0.4 [ 10863 ]

description: Error occurs in Firebird 3.0.4 and daily snapshot 3.0.5.33160.

Error "no permission for SELECT access to TABLE BLOB_TABLE"

When attempting to select BLOB (SubType 1) from VIEW that DOES have has permission to select from BLOB_TABLE.

Reproduce as follows:

Create a test database containing a single table (BLOB_TABLE), single view (BLOB_VIEW) with grants that allow public to only to the view.
Grant all on BLOB_TABLE to VIEW BLOB_VIEW.
Use SQL the following script to create test database below:

--=== Start Script ===--
CREATE TABLE BLOB_TABLE (
ID INTEGER NOT NULL,
TEXT_VALUE VARCHAR(50),
BLOB_VALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
BLOB_BINARY BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
COMMIT;

CREATE VIEW BLOB_VIEW(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
AS
SELECT ID, TEXT_VALUE, BLOB_VALUE, BLOB_BINARY FROM BLOB_TABLE;
COMMIT;

ALTER TABLE BLOB_TABLE ADD CONSTRAINT PK_BLOB_TABLE PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER TRIGGER BIUD_BLOB_VIEW FOR BLOB_VIEW
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
if (INSERTING) then
begin
INSERT INTO BLOB_TABLE(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
values (
http://new.ID,
new.TEXT_VALUE,
new.BLOB_VALUE,
new.BLOB_BINARY);
end

if (UPDATING) then
begin
UPDATE BLOB_TABLE SET
TEXT_VALUE = new.TEXT_VALUE,
BLOB_VALUE = new.BLOB_VALUE,
BLOB_BINARY = new.BLOB_BINARY
WHERE
ID = http://old.ID;
end

if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = http://old.ID;
END^

COMMIT^

SET TERM ; ^

GRANT ALL ON BLOB_VIEW TO PUBLIC;
GRANT ALL ON BLOB_TABLE TO TRIGGER BIUD_BLOB_VIEW;
COMMIT;

--=== End Script ===--

We are moving to Firebird 3 from Firebird 2.5.8. which does NOT have this bug.
Our Delphi application uses IBO 4.8.7 to access the Firebird database.
We have also tested using newer version of IBO (Ver 5.9.7) with the same result.

Tracing the Firebird API calls from IBO we can see the following pattern.

isc_attach_database(db: 1)
isc_dsql_allocate_statement(st: 1)
isc_start_multiple(tr: 1)
isc_dsql_prepare(tr: 1, st: 1)
isc_dsql_execute(tr: 1, st: 1)
isc_dsql_fetch(st: 1)
isc_commit_transaction(tr: 1)
isc_start_multiple(tr: 2)
isc_open_blob2(tr: 2, blob:ID)

A simpler pattern that produces the same error is below. In the following steps it is assumed that we know the ID of the BLOB to be opened.

We have further narrowed the error down as follows:
isc_attach_database(db: 1)
isc_start_multiple(tr: 1)
isc_open_blob2(tr: 1, blob:ID)

=>

Error occurs in Firebird 3.0.4 and daily snapshot 3.0.5.33160.

Error "no permission for SELECT access to TABLE BLOB_TABLE"

When attempting to select BLOB from VIEW that DOES have has permission to select from BLOB_TABLE.

Reproduce as follows:

Create a test database containing a single table (BLOB_TABLE), single view (BLOB_VIEW) with grants that allow public to only to the view.
Grant all on BLOB_TABLE to VIEW BLOB_VIEW.
Use SQL the following script to create test database below:

--=== Start Script ===--
CREATE TABLE BLOB_TABLE (
ID INTEGER NOT NULL,
TEXT_VALUE VARCHAR(50),
BLOB_VALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
BLOB_BINARY BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
COMMIT;

CREATE VIEW BLOB_VIEW(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
AS
SELECT ID, TEXT_VALUE, BLOB_VALUE, BLOB_BINARY FROM BLOB_TABLE;
COMMIT;

ALTER TABLE BLOB_TABLE ADD CONSTRAINT PK_BLOB_TABLE PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER TRIGGER BIUD_BLOB_VIEW FOR BLOB_VIEW
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
if (INSERTING) then
begin
INSERT INTO BLOB_TABLE(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
values (
http://new.ID,
new.TEXT_VALUE,
new.BLOB_VALUE,
new.BLOB_BINARY);
end

if (UPDATING) then
begin
UPDATE BLOB_TABLE SET
TEXT_VALUE = new.TEXT_VALUE,
BLOB_VALUE = new.BLOB_VALUE,
BLOB_BINARY = new.BLOB_BINARY
WHERE
ID = http://old.ID;
end

if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = http://old.ID;
END^

COMMIT^

SET TERM ; ^

GRANT ALL ON BLOB_VIEW TO PUBLIC;
GRANT ALL ON BLOB_TABLE TO TRIGGER BIUD_BLOB_VIEW;
COMMIT;

--=== End Script ===--

We are moving to Firebird 3 from Firebird 2.5.8. which does NOT have this bug.
Our Delphi application uses IBO 4.8.7 to access the Firebird database.
We have also tested using newer version of IBO (Ver 5.9.7) with the same result.

Tracing the Firebird API calls from IBO we can see the following pattern.

isc_attach_database(db: 1)
isc_dsql_allocate_statement(st: 1)
isc_start_multiple(tr: 1)
isc_dsql_prepare(tr: 1, st: 1)
isc_dsql_execute(tr: 1, st: 1)
isc_dsql_fetch(st: 1)
isc_commit_transaction(tr: 1)
isc_start_multiple(tr: 2)
isc_open_blob2(tr: 2, blob:ID)

A simpler pattern that produces the same error is below. In the following steps it is assumed that we know the ID of the BLOB to be opened.

We have further narrowed the error down as follows:
isc_attach_database(db: 1)
isc_start_multiple(tr: 1)
isc_open_blob2(tr: 1, blob:ID)

@firebird-automations
Copy link
Collaborator Author

Modified by: Andrew Ayre (communicare)

Attachment: CreateDb.sql [ 13364 ]

description: Error occurs in Firebird 3.0.4 and daily snapshot 3.0.5.33160.

Error "no permission for SELECT access to TABLE BLOB_TABLE"

When attempting to select BLOB from VIEW that DOES have has permission to select from BLOB_TABLE.

Reproduce as follows:

Create a test database containing a single table (BLOB_TABLE), single view (BLOB_VIEW) with grants that allow public to only to the view.
Grant all on BLOB_TABLE to VIEW BLOB_VIEW.
Use SQL the following script to create test database below:

--=== Start Script ===--
CREATE TABLE BLOB_TABLE (
ID INTEGER NOT NULL,
TEXT_VALUE VARCHAR(50),
BLOB_VALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
BLOB_BINARY BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
COMMIT;

CREATE VIEW BLOB_VIEW(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
AS
SELECT ID, TEXT_VALUE, BLOB_VALUE, BLOB_BINARY FROM BLOB_TABLE;
COMMIT;

ALTER TABLE BLOB_TABLE ADD CONSTRAINT PK_BLOB_TABLE PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER TRIGGER BIUD_BLOB_VIEW FOR BLOB_VIEW
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
if (INSERTING) then
begin
INSERT INTO BLOB_TABLE(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
values (
http://new.ID,
new.TEXT_VALUE,
new.BLOB_VALUE,
new.BLOB_BINARY);
end

if (UPDATING) then
begin
UPDATE BLOB_TABLE SET
TEXT_VALUE = new.TEXT_VALUE,
BLOB_VALUE = new.BLOB_VALUE,
BLOB_BINARY = new.BLOB_BINARY
WHERE
ID = http://old.ID;
end

if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = http://old.ID;
END^

COMMIT^

SET TERM ; ^

GRANT ALL ON BLOB_VIEW TO PUBLIC;
GRANT ALL ON BLOB_TABLE TO TRIGGER BIUD_BLOB_VIEW;
COMMIT;

--=== End Script ===--

We are moving to Firebird 3 from Firebird 2.5.8. which does NOT have this bug.
Our Delphi application uses IBO 4.8.7 to access the Firebird database.
We have also tested using newer version of IBO (Ver 5.9.7) with the same result.

Tracing the Firebird API calls from IBO we can see the following pattern.

isc_attach_database(db: 1)
isc_dsql_allocate_statement(st: 1)
isc_start_multiple(tr: 1)
isc_dsql_prepare(tr: 1, st: 1)
isc_dsql_execute(tr: 1, st: 1)
isc_dsql_fetch(st: 1)
isc_commit_transaction(tr: 1)
isc_start_multiple(tr: 2)
isc_open_blob2(tr: 2, blob:ID)

A simpler pattern that produces the same error is below. In the following steps it is assumed that we know the ID of the BLOB to be opened.

We have further narrowed the error down as follows:
isc_attach_database(db: 1)
isc_start_multiple(tr: 1)
isc_open_blob2(tr: 1, blob:ID)

=>

Error occurs in Firebird 3.0.4 and daily snapshot 3.0.5.33160.

Error "no permission for SELECT access to TABLE BLOB_TABLE"

When attempting to select BLOB from VIEW that DOES have has permission to select from BLOB_TABLE.

Reproduce as follows:

Create a test database containing a single table (BLOB_TABLE), single view (BLOB_VIEW) with grants that allow public to only to the view.
Grant all on BLOB_TABLE to VIEW BLOB_VIEW.
Use SQL the following script to create test database below:

--=== Start Script ===--
CREATE TABLE BLOB_TABLE (
ID INTEGER NOT NULL,
TEXT_VALUE VARCHAR(50),
BLOB_VALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
BLOB_BINARY BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
COMMIT;

CREATE VIEW BLOB_VIEW(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
AS
SELECT ID, TEXT_VALUE, BLOB_VALUE, BLOB_BINARY FROM BLOB_TABLE;
COMMIT;

ALTER TABLE BLOB_TABLE ADD CONSTRAINT PK_BLOB_TABLE PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER TRIGGER BIUD_BLOB_VIEW FOR BLOB_VIEW
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
if (INSERTING) then
begin
INSERT INTO BLOB_TABLE(
ID,
TEXT_VALUE,
BLOB_VALUE,
BLOB_BINARY)
values (
http://new.ID,
new.TEXT_VALUE,
new.BLOB_VALUE,
new.BLOB_BINARY);
end

if (UPDATING) then
begin
UPDATE BLOB_TABLE SET
TEXT_VALUE = new.TEXT_VALUE,
BLOB_VALUE = new.BLOB_VALUE,
BLOB_BINARY = new.BLOB_BINARY
WHERE
ID = http://old.ID;
end

if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = http://old.ID;
END^

COMMIT^

SET TERM ; ^

GRANT ALL ON BLOB_VIEW TO PUBLIC;
GRANT ALL ON BLOB_TABLE TO TRIGGER BIUD_BLOB_VIEW;
COMMIT;

INSERT INTO BLOB_TABLE (ID, TEXT_VALUE, BLOB_VALUE) VALUES (1, 'test data', x'414243');
COMMIT;

--=== End Script ===--

We are moving to Firebird 3 from Firebird 2.5.8. which does NOT have this bug.
Our Delphi application uses IBO 4.8.7 to access the Firebird database.
We have also tested using newer version of IBO (Ver 5.9.7) with the same result.

Tracing the Firebird API calls from IBO we can see the following pattern.

isc_attach_database(db: 1)
isc_dsql_allocate_statement(st: 1)
isc_start_multiple(tr: 1)
isc_dsql_prepare(tr: 1, st: 1)
isc_dsql_execute(tr: 1, st: 1)
isc_dsql_fetch(st: 1)
isc_commit_transaction(tr: 1)
isc_start_multiple(tr: 2)
isc_open_blob2(tr: 2, blob:ID)

A simpler pattern that produces the same error is below. In the following steps it is assumed that we know the ID of the BLOB to be opened.

We have further narrowed the error down as follows:
isc_attach_database(db: 1)
isc_start_multiple(tr: 1)
isc_open_blob2(tr: 1, blob:ID)

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

In description of an error I see:

> Grant all on BLOB_TABLE to VIEW BLOB_VIEW

But there is no appropriate grant inyour script, only trigger is granted all rights on the table.
You should explicitly grant select on BLOB_TABLE to BLOB_VIEW in order to avoid that error.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

After private talk to Roman Simakov described behavior appears buggy

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Dmitriy Starodubov [ dmitriy starodubov ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue duplicates CORE5823 [ CORE5823 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Sean, I doubt it's duplicate.

In 5823 explicit access rights were granted to procedure to select from the table with BLOBs. In this case view is not explicitly granted any rights but it has an _implicit_ grant to select from the tables used in the view. It's a bit another case therefore fix for 5823 is not OK here.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE6107 [ CORE6107 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Duplicate [ 3 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Alexander,

Have re-opened the case, though it reads very similar to the first/original linked ticket, as well as the new linked ticket.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dmitry-starodubov

The problem is accessing to the BLOB by ID in another transaction.
If a user has explicit permissions on a table, engine can check it at any time.
But if permissions are implicit like in the case of VIEW or TRIGGER or PROCEDURE, then engine stores BLOB IDs fetched by requests in transaction context. And a client can access fetched BLOBs by ID in the same transaction. When transaction is finished, the list of IDs is deleted.
To keep the list of IDs at the attachment level is a bad idea for two reasons.
1. The long-running attachments will consume a lot of memory to store fetched IDs.
2. It's not guaranteed that the BLOB ID will refer to the same BLOB after transaction's finish.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Really - I did not notice that it was done in another transaction. In that case that's user's bug which was earlier hidden by missing BLOBs access control.

@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
Assignees
No one assigned
Labels
affect-version: 3.0.4 affect-version: 3.0.5 component: security priority: major resolution: wontfix type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations