2015-03-17

How to access Firebird database on localhost without knowing the appropriate user name and password

Assume you are a system administrator and you have forgotten your username or password so you have no longer access to your database. You don't have any other access to your database, you cannot reset the SYSDBA user's password because other people or applications are using it and you don't have access to them either but you really need access to certain records quickly. How annoying is that? Luckily, there is a solution for this problem but we also have to assume that we can shut down the database server for a short time.

Specification

  • Operating system: Windows
  • Database server: Firebird 2.5
  • Database client: FlameRobin and gsec program
We are going to use another, temporary instance of the database server, where we have full access to everything, we shut down the original Firebird server for the time of process, start the temporary Firebird server and access to our databases using that server. We also take that we have to access those databases regularly before we can fix our original database server the proper way.

Let's call the database we are trying to access 'Test Database' and its file is located:
<FIREBIRD_PROGRAM_FOLDER>\databases\testdb.fdb

Step-by-step guide

  1. Download and extract the ZIP edition of Firebird 2.5 database server;
  2. Shut down the original Firebird services;
    Firebird Windows services
    Firebird Windows services

  3. Run Windows console as system administrator, go to the extracted ZIP version of Firebird server, enter bin subfolder and start the server with the following command:
    fbserver -a
  4. Open FlameRobin client and create a new connection with the appropriate parameters specified in firebird.conf located in the main directory (if you haven't changed anything in that file then you can use localhost in the Hostname field and leave the Port number field empty):
    FlameRobin - Firebird server
    FlameRobin - Firebird server
  5. Now we need to create a new administrative user within the original Firebird database server. To do this we use the gsec program in the temporary Firebird server. Run the following command in the bin directory:
    gsec -user sysdba -pass masterkey -database "localhost:<FIREBIRD_FOLDER>\security2.fdb" -add <NEW_USER> -pw <PASSWORD> -admin yes
    where <FIREBIRD_FOLDER> is the absolute path of your original Firebird database server (C:\Program Files\Firebird\Firebird_2_5 in my case), <NEW_USER> is the username of your new administrative user (admin2 in my case) and <PASSWORD> is the password of this new user (simply admin2 in my case);
  6. Go back to FlameRobin client and register the database you are trying to access:
    FlameRobin - Firebird database registration
    FlameRobin - Firebird database registration
    The Display name field can be anything you like (Test Database in my case), the Database path field is the absolute path of your database you are trying to access (C:\Program Files\Firebird\Firebird_2_5\databases\TESTDB.FDB in my case), the Authentification mode should be the 'Use saved user name and password' item, the User name is SYSDBA and the password is masterkey (both is the default in the temporary Firebird server), the Charset is the character set in your database, the Role should be admin. Save this information, then double-click on the new database item. Now the system should be able to connect the database.
  7. Use FlameRobin client to grant permissions to the new database user on the database elements you need access to. Don't forget to commit all transactions. Now the new user should have access to the desired database elements.
  8. Disconnect, close FlameRobin, stop the temporary Firebird server and restart the original database server. Now you should have access to your database using the new user name and password without killing the other connections.

Notes

  • This tutorial works only when you physically have the target databases on your computer and have all administrative rights on your operating system;
  • I don't really know if this is a security hole in the database server or is a normal backdoor to regain control on your system. Normally, you should not be able to have access to your databases without knowing the appropriate user name and password or replacing the security database with the default one. In this case, we used another instance of the database server after shutting down all original services. Other database servers (for example: PostgreSQL) may fail to access a foreign database without registering the tablespaces and databases on them properly on the administrative interface, however there are other (official) methods for accessing a database where you cannot sign in. Don't blame me if this tutorial will not work after a few months.

2015-01-28

Linux Bash: creating employee codes from basic personal details

Few years ago I wrote a simple bash script to generate employee codes for future project management systems. Now, that the time has come to create a system that must be able to generate employee codes automatically, I realized that it is more elegant to generate employee codes directly with PL/SQL functions so the script has become completely useless. I was going to delete it permanently from my filesystem, but then checked the code and decided to post it here because it is a simple but perfect example of how fast problems can be solved using Linux bash only. Experts can skip this post because probably it will show nothing new for them.

The code itself

The script on my computer is called generatePID.sh and it is expecting 4 parameters, where the last one is optional: forename, surname, birthdate and creation date logging. When the value of the last parameter is 1 then it will print out the generated employee code with its creation date, otherwise nothing will be printed but the employee code.
You can run the script from terminal with the parameters described above. For example:
./generatePID.sh George Smith 1980.01.01
will print out
GESMIAABAB150128
and
./generatePID.sh George Smith 1980.01.01 1
will print out
GESMIAABAB150128
Generation date: 2015. jan. 28., wednesday, 19.13.16 CET

The code

#!/bin/bash
#PERSONAL ID GENERATOR SCRIPT
#HELP
if [ $# -lt 3 ]; then
echo "PERSONAL ID GENERATOR SCRIPT"
echo "Usage: $0 FORENAME SURNAME BIRTHDATE [0|1]"
echo "FORENAME: the forename of a specified person"
echo "SURNAME: the surname of a specified person"
echo "BIRTHDATE: the birthdate of a specified person, dot-separated"
echo "[0|1]: hide generation date (default) / show generation date"
echo "Examples:"
echo "George Smith 1980.01.01"
echo "George Smith 1980.01.01 0"
echo "George Smith 1980.01.01 1"
exit 1
fi
#CONVENTIONS
fnameMax=2
snameMax=2
#DEPENDING ON PERSONAL DETAILS
forename=$1; fnamePref=`echo ${forename:0:$fnameMax} | tr [a-z] [A-Z]`
surname=$2; snamePref=`echo ${surname:0:$snameMax} | tr [a-z] [A-Z]`
nameCode=$fnamePref$snamePref
birthdate=$3; bdateCode=`echo ${birthdate:2} | sed "s/[^0-9]//g" | tr [0-9] [A-J]`
#GENERATION DATE
dateCode=`date +%y%m%d | sed "s/[^0-9]//g"`
#RANDOM 4 DIGIT CODE
#randCode=$[ 1000 + $[ RANDOM % 9000 ] ]
#finalCode=$nameCode$bdateCode$randCode
#DISPLAY RESULT
finalCode=$nameCode$bdateCode$dateCode
echo $finalCode
#DISPLAY GENERATION DATE
if [ $# -ge 4 ]; then
if [ $4 -eq 1 ]; then
echo -n "Generation date: "
date
fi

fi

How it works

The first line tells the OS we want to use bash for the entire script. It's quite useful to start every Linux/Unix script by defining which shell we want to use to avoid unexpected results.
Everything else after the '#' character to the end of the line is handled as a comment and is skipped from processing.
The following line checks if less than 3 parameters are given and then the script will print out its help and exit:
if [ $# -lt 3 ]; then
The "echo" command will print out its arguments, "exit 1" will make sure the execution of the script is aborted with exit code 1.
"fnameMax" is the maximum length of the forename in the employee code. "snameMax" is the same with the surname.
The following commands will create a temporary variable for the forename, cut everything after the last allowed character from it and convert all characters to capitals:
forename=$1; fnamePref=`echo ${forename:0:$fnameMax} | tr [a-z] [A-Z]`
The same will apply to the surname. Then, the new temporary variables will be merged and that will be the name-code component of the employee code.
The following commands will create a temporary variable for the birthdate, cut the first 2 characters from it (making the year component a 2-digit code), delete everything from it leaving only numbers and convert the remaining numbers to letter-codes (capitals). The new temporary variable will be the birthdate-code component in the employee code.
birthdate=$3; bdateCode=`echo ${birthdate:2} | sed "s/[^0-9]//g" | tr [0-9] [A-J]`
Then we create the last component of the employee code from the current system date:
dateCode=`date +%y%m%d | sed "s/[^0-9]//g"`
Now we merge all new temporary variables in the correct order and that will be the employee code which will be printed out at the end of the script:
finalCode=$nameCode$bdateCode$dateCode
echo $finalCode
Lastly, we check the value of the last parameter if it was given. If its value is 1 then we print out the creation date:
echo -n "Generation date: "
date
("-n" will strip the end-of-line character in the echo command so the result of the "date" command will be printed out in the same line.)
There is a second version of the script commented out, which will generate random numbers as the last component of the employee code, but I never planned to use it actually to make sure that the generated codes will be unique.

The reason for deletion

A production environment should not call shell scripts for creating codes in a database since it is not considered being safe. Also, the uniqueness of these codes cannot be guaranteed when working with huge amount of data. This shell script does not deal with the unique database IDs of the records which are taking an important part in the final employee codes to make sure they are completely unique.
Anyway, the script can be useful when you are working only with a few records and you want to generate employee codes which are easy to remember.