| Subject | Re: [firebird-support] Firebird sysdba account |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-08-13T01:05:58Z |
At 06:32 AM 13/08/2008, you wrote:
What you *should* have done before you began this development was to study up the security model as it operates in Firebird. You've done about the worst possible thing while, in the meantime, the Firebird developers are working hard to tighten up things to make it harder and harder to do things the wrong way and get it to work.
While Set's comments aren't untrue - if people are allowed to steal database files then all bets are off - they don't help much to answer your question. Firebird is a database server that can be serving one or many databases simultaneously. Authentication (all the login jazz that your app requests through the API that lives in the Firebird client library) operates at *server* level.
Authentication is managed in a database that lives in Fb root directory. On v.2.1 its name is security2.fdb. SYSDBA is a special user that not only owns the security database but has full destructive rights on all databases. You're not meant to deploy applications into the wild that give SYSDBA rights to all users.
The security DB contains the login particulars for all users known to the server. To get users into it, SYSDBA uses the gsec code - either by running the command-line gsec tool or by accessing the same code through the Services API. SYSDBA is the only user that can do this, so if you're not the person in control of the production software you will need to make the SYSDBA credentials available to a trusted person on site who can manage this without breaking stuff.
An important operation that only the SYSDBA or the DB owner can do is backups. There are other maintenance functions that are so restricted. Therefore, it is important that you have an Owner user. This user needs to have been the one that created the database and also all of the objects in it.
The Owner has to create the database and the objects, of course...the easy (and proper) way to do that is to have started out all DB development with this Owner user and to have stuck with it. The postfix is for SYSDBA to extract the database metadata into a script; modify the script to make the Owner user the owner at CREATE DATABASE and also during the creation of all objects; and finally for SYSDBA to pump all the data into the new database. It's not as big a deal as it sounds - there are free tools around for pumping data.
Beyond the Owner user come SQL privileges. It's "as designed" that only the creator of a database object has privileges to access it in any way. If the developer is serious about protecting the objects and their data from unauthorised access then he should design a proper scheme to define who can access what and in what way. The SQL standard - and Firebird - provide ways to do this properly, albeit that it's all to common to see systems deployed with totally horrible schemes!! An understanding of roles and users is kinda *given*.
Now, considering your neglect of security issues to date, it's likely that you're not overly concerned about protecting access to data within the organisation - everyone who uses your application can have full destructive access to all data. In that case, having just the one user that is non-SYSDBA would be enough for you and you could just ignore the security scheme. But hard-coding passwords into applications is not a good approach for any software that you're not running at home for your own private use.
Deploying a system outside your front door with the SYSDBA password set to 'masterkey' (hard-coded or not) is a no-no - the whole world knows it. That said, as Set explained, if the database file can be copied by anyone, then it's kindergarten stuff to put the copy onto another server and use that server's SYSDBA password to access it. Database files should *not* be in folders that users can access via a share and the server should be in a locked room.
Assuming the production site *is* sufficiently protected to prevent unauthorised users from copying the database, there are ways to enable your application to access the database with full rights to everything, without the users being aware that they are logging in each time they start the app. Some data access layers provide tricks to do this automatically - IBObjects does, with a variety of security levels, I don't know about others. You can implement similar using the client's Registry, or ini files, to achieve this to some degree - you have your application read the Registry entry or the ini file in your BeforeConnect event.
It all depends on how secure you want it to be. There's not a lot you can do to prevent a savvy user from wreaking havoc with a third-party tool if it's just a matter of browsing his own system and discovering what the user names and passwords are. On the whole, a solution like this won't pass Go in a security audit.
./heLen
>Is there any way to change the sysdba password when releasing an applicationYes...but....
>to someone?
>
>I have a Delphi application that uses a Firebird database (was using 1.5 but
>will now use 2.1).
>
>Is there any way to change the password so people cannot directly access the
>database? At the moment I have the password hardcoded into the Delphi
>application.
>
>I would prefer to protect the database which a unique password that I could
>hardcode into my application (or at least put the password in the registry
>or somewhere) but is this possible?
What you *should* have done before you began this development was to study up the security model as it operates in Firebird. You've done about the worst possible thing while, in the meantime, the Firebird developers are working hard to tighten up things to make it harder and harder to do things the wrong way and get it to work.
While Set's comments aren't untrue - if people are allowed to steal database files then all bets are off - they don't help much to answer your question. Firebird is a database server that can be serving one or many databases simultaneously. Authentication (all the login jazz that your app requests through the API that lives in the Firebird client library) operates at *server* level.
Authentication is managed in a database that lives in Fb root directory. On v.2.1 its name is security2.fdb. SYSDBA is a special user that not only owns the security database but has full destructive rights on all databases. You're not meant to deploy applications into the wild that give SYSDBA rights to all users.
The security DB contains the login particulars for all users known to the server. To get users into it, SYSDBA uses the gsec code - either by running the command-line gsec tool or by accessing the same code through the Services API. SYSDBA is the only user that can do this, so if you're not the person in control of the production software you will need to make the SYSDBA credentials available to a trusted person on site who can manage this without breaking stuff.
An important operation that only the SYSDBA or the DB owner can do is backups. There are other maintenance functions that are so restricted. Therefore, it is important that you have an Owner user. This user needs to have been the one that created the database and also all of the objects in it.
The Owner has to create the database and the objects, of course...the easy (and proper) way to do that is to have started out all DB development with this Owner user and to have stuck with it. The postfix is for SYSDBA to extract the database metadata into a script; modify the script to make the Owner user the owner at CREATE DATABASE and also during the creation of all objects; and finally for SYSDBA to pump all the data into the new database. It's not as big a deal as it sounds - there are free tools around for pumping data.
Beyond the Owner user come SQL privileges. It's "as designed" that only the creator of a database object has privileges to access it in any way. If the developer is serious about protecting the objects and their data from unauthorised access then he should design a proper scheme to define who can access what and in what way. The SQL standard - and Firebird - provide ways to do this properly, albeit that it's all to common to see systems deployed with totally horrible schemes!! An understanding of roles and users is kinda *given*.
Now, considering your neglect of security issues to date, it's likely that you're not overly concerned about protecting access to data within the organisation - everyone who uses your application can have full destructive access to all data. In that case, having just the one user that is non-SYSDBA would be enough for you and you could just ignore the security scheme. But hard-coding passwords into applications is not a good approach for any software that you're not running at home for your own private use.
Deploying a system outside your front door with the SYSDBA password set to 'masterkey' (hard-coded or not) is a no-no - the whole world knows it. That said, as Set explained, if the database file can be copied by anyone, then it's kindergarten stuff to put the copy onto another server and use that server's SYSDBA password to access it. Database files should *not* be in folders that users can access via a share and the server should be in a locked room.
Assuming the production site *is* sufficiently protected to prevent unauthorised users from copying the database, there are ways to enable your application to access the database with full rights to everything, without the users being aware that they are logging in each time they start the app. Some data access layers provide tricks to do this automatically - IBObjects does, with a variety of security levels, I don't know about others. You can implement similar using the client's Registry, or ini files, to achieve this to some degree - you have your application read the Registry entry or the ini file in your BeforeConnect event.
It all depends on how secure you want it to be. There's not a lot you can do to prevent a savvy user from wreaking havoc with a third-party tool if it's just a matter of browsing his own system and discovering what the user names and passwords are. On the whole, a solution like this won't pass Go in a security audit.
./heLen