Hi Alfred,

> I am slowly realising what this means. The reason I did not want to
> change the SYSDBA password is because for the instances where I
> didnt care about security, I didn't want to deploy the security.fdb
> as well.

You should never deploy security.fdb, for several reasons:

- On the target system, there may already be Firebird databases in
use, and existing users that you don't know of. If you overwrite
security.fdb by your own version, other applications may stop
functioning because the users they connect as no longer exist.

- You should not assume that you or your users have filesystem access
to security.fdb. Iow maybe you can't overwrite it at all.

- In future Firebird versions, security.fdb may disappear or change.
This will break your installation procedure, the functioning of the
Firebird server, or both.

> That's why I let them connect with SYSDBA/masterkey.

If you don't care about security for certain databases, create them as
user e.g. EVERYMAN and make EVERYMAN's password known publicly. But
are you sure you want this? This means that everybody can destroy or
corrupt the data, and even change the metadata (database structure) or
drop the database.

> Do you know why the reason why FB was designed like this, and why
> the security is not included in each DB?

No, it's an InterBase legacy and I don't know the reasons. But I can
live with it. It's not unlike user management on a computer system:
users are defined system-wide and granted or denied access to certain
directories, files, and other resources. Users are not defined
*within* each file or directory.

> My problem with this is that should someone get hold of the DB, they
> can access it easily with their own security FDB.

That is correct. If you get the database file, you can copy it to a
system where you are SYSDBA and you have all the data. That's why the
database files themselves (but also the Firebird tree where the
executable, config files and security.fdb live!) should not be
accessible to normal users of the computer system - not even
read-only. As long as the Firebird server has access to the files,
user can connect and do their business with the databases - if they
provide a valid username + password.

> Also, when you have multiple DB's on one machine, what happens with
> duplicate users on different DB's?

There can only be one user ALFRED registered with the Firebird
server. So if there are multiple databases owned by ALFRED (or where
ALFRED has certain access rights), it's always the same ALFRED (or
rather: anyone who knows ALFERD's password).

Note that the password too is stored in security.fdb, not in the
database files. Only the *name* of the owner, and possibly of other
people who have rights, are stored in the database.

> I also don't like the fact that I have to deploy the security.fdb
> with my app. What happens if the user should overwrite an existing
> security.fdb with a new one?

Big trouble, see above! :-)
Should not be possible.

> I am sorry if all these questions sounds dumb, but what is the
> preferred method of user management on a FireBird server with
> multiple databases?

In a nutshell:

- Always change SYSDBA's password and make it known only to a couple
of Gods.

- For each database you set up, ask yourself who is going to use it
and how. Often I create a user specifically for that database,
e.g. BLAHBLAHMANAGER for the person(s) who manage the BlahBlah
data. You give those people BLAHBLAHMANAGER'S password and they
can all log in as the same user and play with blahblah.fdb.

Other times you need to refine, and grant different kinds of access
for different users. But I always try to create usernames that
reflect the *task* a user has, not the name of the person who
happens to carry out that task at the moment. After all, Joe may
quit his job in two months and Mary might not like to have to log
in as JOE.

Sometimes you can let the users decide which name the database is
created under. You can even design your app so that it creates the
Firebird user(s) for them if they don't exist yet, and then create
the database after connecting with the "main" username. (BTW, to
create users, you must know SYSDBA's password.)


Greetings,
Paul Vinkenoog