Subject | GRANT in a stored procedure |
---|---|
Author | Anthonie |
Post date | 2007-08-27T09:07:57Z |
Hi,
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
Kindest regards,
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
Kindest regards,
Anthonie.