Subject Re: [firebird-support] Database owner question
Author Thomas Steinmaurer

> I am developing an accounting program and some recent posts made me
> seriously think about security.
> Until now I have developed the database as SYSDBA, but I am now
> realizing that it is not a good option.


> I am targeting small customers, so most of them will use the embedded
> server. Few of them will require a multi-user solution so they will need
> a full server, but they will probably have no experience with Firebird
> or other database servers.
> The program will ship with an (almost) empty database that will be used
> by customers.
> It looks like the best solution for me is to create and handle the
> database connecting with a custom username, so that it will be the
> database owner.

At least you should create and develop the database with a Non-SYSDBA
user and use that user when connecting to the database through your
application. What you will loose when your application connects only
with one user is, that e.g. CURRENT_USER is the same for all
connections, so in respect to e.g. logging data changes via triggers you
won't really see who changed some data.

> After deployment the situation should be the following:
> Embedded does not make any authentication so I can connect using that
> username and everything should work.

It does not use authentication, but it uses the provided user name for
checking SQL privileges on database objects.

> Users that need a full server and do not have any Firebird installation
> can simply install the server and connect as SYSDBA if mine is the only
> database handled by the server. This should avoid the need to create a
> new user for unexperienced people.


> If the customer has already a Firebird server he can create a username
> identical to the one used for developing the database. He can choose any
> password he likes, then he can use that username/password to connect to
> the database. In this way there is no need to use the SYSDBA account
> (and to know its password) to connect to the database.


> Is what I have written correct?


> Can I assume that copying a database to
> another server and creating the same username as the one used as the
> database owner on the development computer will give it full access to
> the database?


> I mean, is the owner reference made using the username or
> with some internal ID that might change from server to server even if
> the username is the same?

The user name is used.

If you are on Windows, you could also investigate Firebird's Windows
authentication capabilities, which has been added in Firebird 2.1.
CURRENT_USER gives you the Windows user then.

With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist