privilegs doesn't work in procedures with execute statemet [CORE183]

[firebird-automations]

Submitted by: webionbrano (webionbrano)

SFID: 1110620#⁠ Submitted By: webionbrano

DB is set like this: - User PETER has exec. privilegs on procedure PROC_B - PROC_B has sel. privilegs on TAB_A

This works:

create procedure PROC_B() begin select * from TAB_A; end

This doesn't works:

create procedure PROC_B() as declare variable sel varchar(500); begin sel = 'select * from TAB_A'; execute statement :sel; end

When we add privilegs to user PETER to selelect from TAB_A everything seems to be alright. For security reasons it is fatal error to able users read from tables directly. And when we have 1000 users and 500 tables and 500 procedures it is crazyness grant everything for everything.

P.S. The problem is the same also when we use roles.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-01-30 09:43 Sender: alexpeshkoff Logged In: YES user_id=423445

Yes, your understanding is absolutely correct.

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-01-28 19:47 Sender: seanleyne Logged In: YES user_id=71163

Alex,

(Want to be sure I understood)

Are you saying:

Although Table A is being accessed via Procedure B, the fact that it is being invoked via EXECUTE STATEMENT means the engine treats the call as DSQL which requires the user PETER to have rights on Table A?

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2005-01-28 15:31 Sender: alexpeshkoff Logged In: YES user_id=423445

This is as designed. This was done almost exactly like in, for example, MS SQL. Procedure in it has procedure owner (normally sa) rights, but dynamic SQL doesn't allow procedure rights to be used in dynamically called statements. I suggest to mark this issue "as desinged" and close it. May be it's worth adding feature request to EXECUTE STATEMENT WITH OBJECT GRANTS?

[firebird-automations]

Modified by: @dyemanov

status: Closed \[ 6 \] =\> Reopened \[ 4 \] assignee: Dmitry Yemanov \[ dimitr \] SF\_ID: 1110620 =\>

[firebird-automations]

Commented by: @dyemanov

As designed.

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \] =\> Alexander Peshkov \[ alexpeshkoff \] status: Reopened \[ 4 \] =\> Resolved \[ 5 \] resolution: Won't Fix \[ 2 \] SF\_ID: 1110620 =\>

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \] SF\_ID: 1110620 =\>

[firebird-automations]

Modified by: @pcisar

Workflow: jira \[ 10207 \] =\> Firebird \[ 14432 \]