Subject | Re: stored procedure to GRANT priveledges not working |
---|---|
Author | robertgilland |
Post date | 2006-06-15T01:35:06Z |
I found out how to do it. By Using Execute Statement
SET TERM #;
CREATE PROCEDURE PROC_ADDUSER( USERORROLE CHAR(31)) AS
DECLARE VARIABLE THERELNAME VARCHAR(80);
DECLARE VARIABLE THESTATEMENT VARCHAR(1024);
BEGIN
FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT LIKE 'RDB$%'
ORDER BY RDB$RELATION_NAME INTO :THERELNAME DO
BEGIN
THESTATEMENT = 'GRANT ALL ON ' || :THERELNAME || ' TO '
|| :USERORROLE;
EXECUTE STATEMENT THESTATEMENT;
END
END#
SET TERM ;
SET TERM #;
CREATE PROCEDURE PROC_ADDUSER( USERORROLE CHAR(31)) AS
DECLARE VARIABLE THERELNAME VARCHAR(80);
DECLARE VARIABLE THESTATEMENT VARCHAR(1024);
BEGIN
FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT LIKE 'RDB$%'
ORDER BY RDB$RELATION_NAME INTO :THERELNAME DO
BEGIN
THESTATEMENT = 'GRANT ALL ON ' || :THERELNAME || ' TO '
|| :USERORROLE;
EXECUTE STATEMENT THESTATEMENT;
END
END#
SET TERM ;