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

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

Closed
firebird-automations opened this issue Jul 23, 2019 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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);

    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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Seems to duplicate CORE5823.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE5823 [ CORE5823 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE6114 [ CORE6114 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant