Kjell,

Good job! This pretty much recaps what I would like to do.
I'm glad you don't see any serious holes too.

Regards,
Zd

----- Original Message -----
From: Kjell Rilbe
To: firebird-support@yahoogroups.com
Sent: Tuesday, November 11, 2008 8:12 AM
Subject: Re: [firebird-support] Guys! I got it! - Re: Avoiding hard-coding db pass in app - without using db users


This whole thread seems rather confusing now - a lot of replies to
various subthreads discussing only parts of the problem at a time. SO,
I'll try to summarize my take on it, as a rather unexperienced FB user
as well as a novice on security, but with a good thinking brain. :-)
Please point out any holes in my reasoning!

In general we assume that the physical DB server is "secure", so that
the DB can only be accessed through client connections. No direct file
access and so forth.

Zd wrote:

> Database has two users:
> SYSDBA, and SECUSER

Or rather MYSPECIALSYSDBA and SECUSER. Substitute SYSDBA with
MYSPECIALSYSDBA everywhere in the following. MYSPECIALSYSDBA has grants
to everything the app needs, but not to GBAK, shutdown and so forth.

> 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;

Executed once locally on the physical DB server I assume.

> 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;

As I understand it, this procedure checks that the provided credentials
are valid and if so returns the MYSPECIALSYSDBA password.

The application executes this procedure logged in as SECUSER, who only
has access to the procedure, and only to execute it. So, it's of no
importance to protect the procedure's source - a hacker can't do
anything with it except executing it anyway.

Assuming the procedure is free of bugs and there's no risk of buffer
overflow exploits, it doesn't even really matter what grants the
procedure has, as long as it has those mentioned above, which are
required for the procedure to do its job.

It is assumed that the client application always connects through a
secure tunnel, both when calling this procedure and when logging in with
the obtained MYSPECIALSYSDBA password. This eliminates the risk of a
hacker snatching the password, unless he/she has access to a client
application where a user has already logged on. In this case, nothing
can protect the database if the application has to connect with a
SYSDBA-like user.

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

As far as I can see, there are no security holes here, assuming
potential hacker don't have access to user credentials or to an already
logged in application, and assuming the authorized users are not hackers
able of extracting the MYSPECIALSYSDBA password from the running
application.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64




[Non-text portions of this message have been removed]