isc_open_blob2 fails when selecting blob field from view with "no permission
for SELECT access to TABLE X" error.
-----------------------------------------------------------------------------------------------------------------
Key: CORE-6114
URL: http://tracker.firebirdsql.org/browse/CORE-6114
Project: Firebird Core
Issue Type: Bug
Components: Security
Affects Versions: 3.0.5
Environment: Window 10 Pro
Reporter: Tom Devereux
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 (
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 = old.ID;
end
if (DELETING) then
delete from BLOB_TABLE
WHERE
ID = 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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
