Thread: Access to the DB
Dear all,
I am writing an app that would run natively on some client machines that should connect to a database as a single DB user and later pretend to be more users (there's nothing new in this approach, I think). Now my problem is the following. Authentication is password based, that means that the app has to know it but Joe User must not (otherwise he could do arbitrary things with the DB). DB passwords change over time and I think recompiling the app every time the password changes is just silly.
So: how to store the DB access password so that Joe User doesn't see it but the admin can update it when it is necessary? Should I have an app on the server that the client would connect to or how?
I am using stored procedures for everything but selects (in fact - imitating object oriented programming on the PgSQL server), but I am not quite sure I could prevent anyone from using "delete" on a table who would use a stored function for that. How could I force people to use my stored functions for insert, update and delete operations instead of insert, update, delete commands in the DB?
Thanks in advance
Zoltan
> So: how to store the DB access password so that Joe User doesn't see it but=
> the admin can update it when it is necessary? Should I have an app on the =
> server that the client would connect to or how?
This is probably overkill for your app (and may be for mine as well), but
here's how I addressed this situation.
First a little background:
I have been developing a program which is a table-driven web browser-based
table query/update program designed for users rather than DBA's. (It
allows the user to query/insert/update/delete data but not to change
the data structure.)
It was written in PHP with postgres in mind but also works with mysql
and should work with any relational database that can be connected to
via PHP/Pear.
Because it is designed to work in a client-server environment, it has its
own user/password scheme.
The userid/password controls what applications the user can launch and
which database it connects to as well as what userid it is on that database,
and thus what data access privileges the user has.
The password itself is stored using a one-way encryption, but the connect
string is stored using the password as an encryption key using the pgcrypto
package. That way it cannot be easily decrypted.
Here's my 'user' table description. (There are a few other fields whose
purpose I haven't referred to in this note.)
Column | Type | Modifiers
--------------------+-----------------------+-----------
pb_user_name | character varying(20) |
pb_user_pw_timeout | integer |
pb_user_connect | bytea |
pb_user_pass | text |
pb_user_perm | text |
pb_user_comment | text |
The tables that drive the app, including the user file, are in a separate
database, with a connect string supplying a userid/password for read-ony
access to that database. (That connect string can be kept in a hidden file
in the web directory or in the psql/lib directory.)
I hope to release this program into the open-source community either
later this year or in 2005, I don't think there's anything quite like
it out there. It is currently in use at a client's office, and I use
it myself to manage several personal databases. I already have a
potential alpha tester in mind.
There are some hooks for native postgres-ism's like arrays that may have
to be worked around in the next rewrite before I can release it, and of
course any database-specific features could only be used in the entries
for an app which accesses that database platform. Also, the password
concept may need to be reworked to make it compatible with the
encrypt/decrypt capabilities of other database engines.
I hope this gives you some ideas.
--
Mike Nolan