[
http://tracker.firebirdsql.org/browse/CORE-6114?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sean Leyne reopened CORE-6114:
------------------------------
> 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.4, 3.0.5
> Environment: Window 10 Pro
> Reporter: Andrew Ayre
> Assignee: Dmitriy Starodubov
> Attachments: CreateDb.sql
>
>
> 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 (
> 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;
> 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)
--
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
