Thanks Ivan,

but my question remains unanswered. Why can't users execute this
stored procedure inside the security database?

The reason for using the SP is that we create a Java-Application with
it's own user administration. I don't like the idea of having two
totally different methods to access the database. Calling a
command-line tool (gsec) is not acceptable. Indeed, the user
maintainance should be possible using DDL statements, as we know from
many other databases. Please don't misunderstand - I like Firebird
very much, but this is definitely one of it's few weak points.

Greetings,
Georgios

--- In firebird-support@yahoogroups.com, "Ivan Prenosil"
<Ivan.Prenosil@s...> wrote:

> I would recommend using standard methods for changing passwords,
> and just change users's privileges. See
> http://www.volny.cz/iprenosil/interbase/ip_ib_users.htm
> http://www.volny.cz/iprenosil/interbase/ip_ib_isc4.htm
>
> Ivan
>
>
> ----- Original Message -----
> From: "gskempes" <gskempes@w...>
> To: <firebird-support@yahoogroups.com>
> Sent: Wednesday, September 15, 2004 7:49 AM
> Subject: [firebird-support] REPOST, please help: Cannot execute SP

in security DB

>
>
> > I would like to execute the stored procedure "ChangePassword"
> > (downloaded from IBExpert) inside the security database in order to
> > allow all users to modify their passwords.
> >
> > The script for creating this stored procedure contains grants:
> > "grant execute on procedure ChangePassword to public;"
> > "grant update (passwd) on users to procedure ChangePassword;"
> >
> > Although I'm able to connect to the security as non-SYSDBA user and
> > execute queries like "select * from users", all I get while executing
> > "execute procedure ChangePassword('MYUSER', 'newPass')" is:
> >
> > "This user does not have privilege to perform this operation on this
> > object.
> > no permission for execute access to DATABASE ."
> >
> > Do I need to grant special rights for executing stored procedures in
> > general or where is the problem?
> >
> > Thanks in advance,
> > Georgios Skempes
> >
> >
> > P.S.: The source code of the stored procedure is:
> >
> > declare external function f_IBPassword
> > cstring(32)
> > returns
> > cstring(32)
> > entry_point 'IBPassword' module_name 'FreeUDFLib.dll';
> >
> > create exception exc_not_permitted 'Action not permitted';
> >
> > set term ^ ;
> >
> > create procedure ChangePassword
> > (user_name varchar(128), passwd varchar(32))
> > as
> > begin
> > if (((USER != 'SYSDBA') and (USER != :user_name)) or
> > (:user_name is null) or
> > (:user_name = '') or
> > (:passwd is null) or
> > (:passwd = '')) then
> > exception exc_not_permitted;
> >
> > update users
> > set passwd = f_IBPassword(:passwd)
> > where user_name = UPPER(:user_name);
> > end ^