Hello Anthonie,

> A question regarding GRANTS on a database. In the database I have a
> table RM_USERS with the following layout:
>
> RM_USER_ID
> RM_USER_ROLE
> ...
> PRIMARY KEY(RM_USER_ID, RM_USER_ROLE)
>
> In the database I have 5 roles, SALES, STOCK, FINANCE, CLIENTS,
> ADMIN whereas each role is responsible for certain tables.
>
> The procedure is as follows:
>
> CREATE PROCEDRE ...(
> userid CHAR(10),
> userrole CHAR(8))
> AS
> BEGIN
> INSERT INTO RM_USERS(RM_USER_ID, RM_USER_ROLE, ...)
> VALUES (:userid, :userrole, ...);
> WHEN SQLCODE -803 DO EXCEPTION DUP_USER;
> GRANT :userrole TO :userid;
> END^
>
> When trying to add a record and also assigning the roles within the
> procedure I am being prompted that GRANT is an unknown statement. Is
> it possible to have a GRANT in a stored procedure or must role be
> explicitly assigned to a user using isql (or any other interface)? I
> am using Firebird 2.0.1

A GRANT is a DDL statement, these are not allowed in PSQL.

You can use them using EXECUTE STATEMENT though.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com