Hello
We are trying to migrate some existing users to the new RDB$ADMIN using some
dynamic SQL. We have the following SQL
for
--Gets a list of users from our system
select ib_username from user_group_users where user_group_no = 1
into :v_username
do
begin
--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;
end
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
begin
--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;
end
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?
Thanks,
Piers Smith
Development Team Lead
Communicare Systems Pty Ltd
Tel: 08 6212 6900 Fax: 08 6212 6980
[email protected]<mailto:[email protected]>
www.communicaresystems.com.au<http://www.communicaresystems.com.au/>
[Verbose footer content removed by Moderator]
[Non-text portions of this message have been removed]
