Hi,

> OK, so I got the solution I think.

> Please take a look as I don't want to overlook anything and open any security holes:

> Database has two users:
> SYSDBA, and SECUSER

> SYSDBA's password is stored in a table called "CODETABLE".

> The following grants are executed using SYSDBA:

> GRANT EXECUTE ON PROCEDURE GET_DB_PASSWORD TO SECUSER;
> GRANT SELECT ON CodeTable TO PROCEDURE GET_DB_PASSWORD;
> GRANT SELECT ON Users TO PROCEDURE GET_DB_PASSWORD;

> As you see, SECUSER has access only to GET_DB_PASSWORD, and only
> the stored procedure has access to the SYSDBA password in CODETABLE!

> The stored procedure's code is the following:

> CREATE PROCEDURE GET_DB_PASSWORD(
> USERNAME VARCHAR(100) CHARACTER SET UTF8,
> "PASSWORD" VARCHAR(100) CHARACTER SET UTF8)
> RETURNS(
> DB_PASSWORD VARCHAR(8) CHARACTER SET UTF8,
> ISSUCCESSFUL SMALLINT)
> AS
> BEGIN
> IsSuccessful = 0;

> SELECT (SELECT CodeValue FROM CodeTable WHERE
> CodeName = 'DatabasePassword')
> FROM Users
> WHERE
> Username = :Username AND
> Passwd = :"PASSWORD"
> INTO :DB_Password;

> IF (ROW_COUNT = 1) THEN
> IsSuccessful = 1;

> SUSPEND;
> END;

> So what do you think? I tried and it works, but any ideas whether this is a SECURE solution too?

Why return the password.
Let the proc make the comaprison of input pw an d pw in db.
so you can avoid to transfer pw via wire twice.

And as others mentioned: Never use sysdba for that purpose!

I'm using a different approach in my applications to store the pw
on local computers: I'm saving the username/password combination
in a extra file in a private home or in profile of the user. (Win)

And I'm not storing it in readable format, but crypted (of course the
key is inside the exe!)

Björn


--
Björn Reimer - RRZE