If a stored procedure which has SELECT grantee to a table consisting BLOB field
accesses that table "no permission for SELECT access to TABLE..." error arises
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-6107
URL: http://tracker.firebirdsql.org/browse/CORE-6107
Project: Firebird Core
Issue Type: Bug
Components: Security
Affects Versions: 3.0.4
Environment: Problem arises both in Windows/Linux distributions.
Reporter: MEHMET SAIT YASAR
Consider a table which has at least one BLOB field and other fields. I want
users access that table only using a stored procedure. Therefore I grant that
procedure only.
Table creation SQL:
CREATE DATABASE 'localhost/3051:/fb3/try/Try.fdb'
USER 'SYSDBA' PASSWORD 'masterke'
PAGE_SIZE 8192
DEFAULT CHARACTER SET WIN1254;
CREATE TABLE TBL (
SEQ INTEGER,
BLB BLOB SUB_TYPE TEXT
);
set term ^;
CREATE PROCEDURE READ_TBL_REG_FIELD ( KEY INTEGER)
RETURNS (
SEQ INTEGER
)
AS
BEGIN
SELECT SEQ FROM TBL WHERE SEQ = :KEY INTO :SEQ;
END
^
CREATE PROCEDURE READ_TBL_BLOB ( KEY INTEGER)
RETURNS (
BLB BLOB SUB_TYPE TEXT
)
AS
BEGIN
SELECT BLB FROM TBL WHERE SEQ = :KEY INTO :BLB;
END
^
set term ;^
GRANT SELECT ON TABLE TBL TO PROCEDURE READ_TBL_REG_FIELD;
GRANT SELECT ON TABLE TBL TO PROCEDURE READ_TBL_BLOB;
insert into TBL(SEQ, BLB) values (1, null);
insert into TBL(SEQ, BLB) values (2, '');
grant execute on procedure READ_TBL_REG_FIELD to user USER1;
grant execute on procedure READ_TBL_BLOB to user USER1;
-----------------------------------------------------------------------------------------------------------
USER1 EXECUTES:
execute PROCEDURE READ_TBL_REG_FIELD(1);
SEQ
===========
1
execute PROCEDURE READ_TBL_BLOB (1);
BLB
=================
<null>
execute PROCEDURE READ_TBL_BLOB (2);
Statement failed, SQLCODE = -551
no permission for SELECT access to TABLE TBL
--------------------------------------------------------------------------------
As you see, if BLOB field has a value, then permission error arises.
This situation does not exist in version 2.5, only in 3.
--
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
