It looked like a good idea because I did not have to care with users management, but I have now realized that using SYSDBA for database development can cause problems when the database is deployed to the customer's computer.
A Firebird database and all its tables have an owner: it is the username that was used for connecting to the server when the object was created. Tables can have a different owner than the database, but this an advanced topic. The simplest solution is having a single owner for the database and all the tables.
Both the owner and SYSDBA have full access to the database, so you need to connect to the server as the owner or as SYSDBA. Of course things can be more complicated about database security, but I want to keep things simple.
The server stores usernames and passwords in a special database (security2.fdb). When a user connects to the server the username and the password are checked using the security database. If the username does not exist or the password is incorrect the connection is refused. When a user is connected to a database he can open a table only if he is the owner or if he is SYSDBA.
I am targeting small customers, so most of them will use the embedded server. The embedded server works differently about security: it will not check the username and the password (there is no security2.fdb) so you will always be able to connect with any username/password couple. Nevertheless Firebird will still check that the username is the owner or SYSDBA before opening a table. Other usernames can connect to the server but cannot open tables.
Customers that will require a multi-user solution will need to install the full server, but they will probably have no experience with Firebird or other database servers. Most of them will use Firebird only for our software, but there is a chance that some of them will already have a Firebird server installed and used by other software.
Deploying a database owned by SYSDBA will cause problems to those users. The only way to connect to such a database is using the SYSDBA username, and since the server has been installed by other people users might not know the SYSDBA password. A database administrator would not like to let a software connect as SYSDBA, since that user has full access to all the databases handled by the server.
For this reason a much better solution is creating a new username and connecting with that username to create and develop the database, so that the owner is not SYSDBA. Then, after database deployment, the software can connect in different ways for different needs.
The software can connect to an embedded server using the SYSDBA username and any password. It will be able to access data without problems. Users will not not be asked for a username.
When connecting to a full server the software will ask for a username and a password. There are two options:
- If the server will only host our database the simplest option is connecting as SYSDBA. It will be possible to access the database with minimal effort.
- If using SYSDBA is not an option somebody must create a new user named as the database owner (the same username used for database development) using any password he likes. The software will connect using that username and password so it will be able to access the database without administrator privileges. The username must be the same as the owner, but the password does not need to be the same used for development. Remember that the password is only used to connect to the server: once you are connected Firebird only checks the username to grant access to tables.
If you have a lot of data and you need to keep them you can try FBOwnerMigrator by Thomas Steinmaurer.
