At 07:49 AM 15/05/2007, you wrote:

>I am trying to create a stored procedure to revoke/grant permissions
>on table names output from a simple select.
>I can't seem to use execute immediate or declare/prepare/execute
>statement within the procedure - all fail with "compile" errors.

Correct. Those are API calls across the client interface. Stored
procedures execute on the server and use their own language called
"procedural SQL" (PSQL).

>Can someone assist?

The architecture of Firebird doesn't allow metadata updates (DDL
statements) directly from PSQL. However, from Fb 1.5 on, you *can*
execute both DML and DDL statements using the EXECUTE STATEMENT
syntax (refer to the V.1.5 release notes for full details and syntax).

Executing DDL with EXECUTE STATEMENT is fraught with risk - it can be
analogous to ritual disembowelment, i.e. just because you *can*
doesn't mean you *should*. GRANT and REVOKE are (generally) OK from
the POV of system integrity, although it's relatively easy to mess up
your database privileges since PSQL modules execute within a user
context, which might not be the correct user context for modifying
database objects and attributes.

I won't do it. I prefer a less flukey approach to modifying
metadata. Sure, I will use a SP to construct a script from reading
the system tables and passing statements to an external file. But I
will always apply scripts when I have exclusive access as Owner to
ensure that I don't end up with wreckage.

./heLen