Subject Grant to RDB$ADMIN - capture exception
Author Piers Smith

We are trying to migrate some existing users to the new RDB$ADMIN using some dynamic SQL. We have the following SQL

--Gets a list of users from our system
select ib_username from user_group_users where user_group_no = 1
into :v_username
--Give the user admin rights in our database
v_grant_statement = 'GRANT RDB$ADMIN TO ' || :v_username || ' GRANTED BY SYSDBA ';
execute statement v_grant_statement;

--Give the user admin rights in the security database
--The security database MUST be Dialect 3. This should be handled by the installer
v_grant_statement = 'ALTER USER ' || :v_username || ' GRANT ADMIN ROLE ';
execute statement v_grant_statement;

However if the value in :v_username does not exist in the Security2.fdb then we get an exception. We tried a "when any do" to add the user if there was an error;

when any do
--user is not in the security2.fdb, so add them
declare variable v_grant_statement2 varchar(51);
v_grant_statement2 = 'CREATE USER ' || :v_username || ' password ''' || :v_username || ''' ';
execute statement v_grant_statement2;

however this brings up syntax errors when run. Is there an easy way in SQL to test if a user exists in the Security2.fdb?


Piers Smith
Development Team Lead
Communicare Systems Pty Ltd
Tel: 08 6212 6900 Fax: 08 6212 6980

[Verbose footer content removed by Moderator]
[Non-text portions of this message have been removed]