You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 [CORE6107]
#6356
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;
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.
The text was updated successfully, but these errors were encountered:
Submitted by: MEHMET SAIT YASAR (msait)
Duplicates CORE5823
Relate to CORE6114
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);
===========
1
execute PROCEDURE READ_TBL_BLOB (1);
=================
<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.
The text was updated successfully, but these errors were encountered: