Hi, I've never tried to do anything similar to what you try to do, and
it may well be illegal. However, I think you have one error in your code
that makes it fail regardless of whether it is legal or not. Double
quoting makes things case sensitive, so "M_USER" and m_user is not the same.

Try changing to

ALTER PROCEDURE SP_GRANT_ALL ( M_USER CHAR(31) )
AS
DECLARE VARIABLE M_TABLE CHAR(31);
begin
for select RDB$RELATION_NAME as myTable
from rdb$relations
WHERE SUBSTRING(RDB$RELATION_NAME from 1 for 4) <> 'RDB$'
INTO :m_table
do
begin
execute statement 'grant all on ' || :m_table || ' to ' || :m_user;
end
exit;
end

I also changed SUSPEND to EXIT. I don't think you need that statement at
all, but SUSPEND is for selectable stored procedures (called by SELECT
... FROM ... SP_GRANT_ALL(...) ...) and this is very much an executable
stored procedure (called by EXECUTE PROCEDURE SP_GRANT_ALL (...)).

Note that even if it should happen to work, having such a procedure
isn't the normal way to do this. What would be more common, is to create
a role, give that role lots of priviledges and grant the users rights to
the role (but then you also have to log in with the role).

HTH,
Set

Fernando Salaices wrote:

> Hi, new to firebird here.
>
> I'm dabling with EXECUTE STATEMENT and I want to grant privileges with
> it byt I'm having problems with it. I found this code some were and it
> seems perfectly logical, and ir executes with no errors, but it just
> does not assign the privileges I want:
>
> ALTER PROCEDURE "SP_GRANT_ALL" ( "M_USER" CHAR(31) )
> AS
> DECLARE VARIABLE M_TABLE CHAR(31);
> begin
> for select RDB$RELATION_NAME as myTable
> from rdb$relations
> WHERE SUBSTRING(RDB$RELATION_NAME from 1 for 4) <> 'RDB$'
> INTO :m_table
> do
> begin
> execute statement 'grant all on ' || :m_table || ' to ' || :m_user; end
> suspend;
> end
> ^
>
> is it possible to use GRANT PRIVILEGES with EXECUTE STATEMENT?