Getting Started with Firebird

Administration Tools

The default system administration account has the username SYSDBA (this username does not appear to be case-sensitive, when I tested it) and the case-sensitive password masterkey. For users of previous versions of Interbase (and people who worked through the Fish Catalog tutorial for Delphi), this will seem very familiar. You will use this account to create another user and the sample database initially.

Important

Editor note :: Official POSIX builds for Firebird v.1.5 and higher do not use 'masterkey' as the default password. Instead, a password is randomly created during the running of the installation scripts and written into a text file named SYSDBA.password. If 'masterkey' returns a 'user name and password not defined' error, it is likely you are using a build that conforms to the POSIX build rules in this respect.

The administrative tools that are available with the software are:

gsec - This is the security administrator. You will use this command-line tool for creating, modifying and deleting database users, changing passwords, etc.
isql - This is the interactive SQL tool, similar to Oracle's SQL*Plus and Postgresql's psql command. You can use this to test or run SQL queries. [Ed.- along with several other jobs!]

gsec Security Administrator

You will need to run gsec as SYSDBA. To invoke it, execute the following in your Terminal session:

~/temp $  gsec -user sysdba -password masterkey

This will bring up the GSEC> prompt. You can display current users by typing “display” at the prompt, like so:

GSEC> display

It is a good idea to change the SYSDBA password, because the default is so well-known. To change it, we modify the SYSDBA account using the following command:

GSEC> modify SYSDBA -pw newpasswd

Ok, newpassword is not exactly a strong password. You should generate your own, which should contain both numbers and letters, and they should be changed frequently. But we will not go into that here.

Warning

Editor note: Only the first 8 characters of a password are meaningful. Hence, for example, a password like 'password01' is authenticated to be identical to 'password02'.

isql Interactive SQL Processor

As mentioned previously, isql is analogous to psql for PostgreSQL and SQL*Plus for Oracle. You can type in an SQL command and get the query results from the database.

Firebird comes with an example EMPLOYEE database, and we will use it to test our SQL commands. To begin, execute the following command (all in one command):

~/temp $ isql 
      localhost:/Library/Frameworks/Firebird.framework/Res
                                    ources/examples/employee.fdb

This will connect you to the sample EMPLOYEE database and display an SQL> prompt. You can type in your SQL commands at the prompt. Remember to put a semicolon (;) at the end of every statement to terminate it, before pressing ENTER to execute it.

To test, type the following SQL command and press ENTER:

SQL> SELECT emp_no, full_name, job_code, job_country FROM employee;

This should give you output similar to the following:

 EMP_NO FULL_NAME                             JOB_CODE JOB_COUNTRY

======= ===================================== ======== ===============

      2 Nelson, Robert                        VP       USA

      4 Young, Bruce                          Eng      USA

      5 Lambert, Kim                          Eng      USA

      8 Johnson, Leslie                       Mktg     USA

      9 Forest, Phil                          Mngr     USA

     11 Weston, K. J.                         SRep     USA

     12 Lee, Terri                            Admin    USA

     14 Hall, Stewart                         Finan    USA

     15 Young, Katherine                      Mngr     USA

     20 Papadopoulos, Chris                   Mngr     USA

     24 Fisher, Pete                          Eng      USA

     28 Bennet, Ann                           Admin    England

     29 De Souza, Roger                       Eng      USA

     34 Baldwin, Janet                        Sales    USA

If you wish to see all the tables in the database, type the following:

SQL> SHOW TABLES;

This will give you all the tables in that database:

       COUNTRY                                CUSTOMER
       DEPARTMENT                             EMPLOYEE

       EMPLOYEE_PROJECT                       JOB

       PHONE_LIST                             PROJECT

       PROJ_DEPT_BUDGET                       SALARY_HISTORY

       SALES

To exit from isql, simply type quit; and press ENTER.

Creating Your First Database

So far, we have executed our commands as SYSDBA, and used the default examples provided with the software. Now, we are going to create a database of our own, create a user that will have rights to view and modify the database, and try operating on the database.

To create our database, we will need to use the isql tool. Firebird saves its databases under discrete files, and, by convention, the extension is .fdb. Note that this is just a convention, and that you can save the database as any extension you wish. For this demonstration, we will first create a database using the SYSDBA user.

We first make sure we are in our temp directory, then run the isql tool as follows:

$ cd ~/temp

~/temp $ isql

Then we execute the CREATE DATABASE command:

SQL> CREATE DATABASE 'firstdb.fdb' USER 'sysdba' PASSWORD 'masterkey';

This creates a file called firstdb.fdb inside the current directory (our temp directory). The database is owned by SYSDBA. We will now create a very rudimentary Sales catalog and fill it with data. If you are already familiar with SQL, the following commands should be easily understood. If not, you should probably read up on the ANSI SQL-92 standard.

SQL> CREATE TABLE sales_catalog (
CON> item_id varchar(10) not null primary key,
CON> item_name varchar(40) not null,
CON> item_desc varchar(50)
CON> );

SQL> INSERT INTO sales_catalog VALUES('001',
CON> 'Aluminium Wok', 'Chinese wok used for stir fry dishes');
SQL> INSERT INTO sales_catalog
CON> VALUES('002', 'Chopsticks extra-long', '60-cm chopsticks');
SQL> INSERT INTO sales_catalog
CON> VALUES('003', 'Claypot', 'Pot for stews');
SQL> INSERT INTO sales_catalog
CON> VALUES('004', 'Charcoal Stove', 'For claypot dishes');
SQL> SELECT * FROM sales_catalog;

ITEM_ID    ITEM_NAME                   ITEM_DESC
======== =========================== ================================

001     Aluminium Wok                Chinese wok used for stir fry dishes
002     Chopsticks extra-long        60-cm chopsticks
003     Claypot                      Pot for stews
004     Charcoal Stove               For claypot dishes

To exit isql, simply type quit; and press ENTER.

Adding Users and Roles

We now have a database, but it may not be a good idea to create and administer all databases using the SYSDBA account. In some cases, for example, if I am running multiple databases belonging to different people or groups, I may want each user or group to own their respective database, with no rights to view other databases. Another scenario may be a requirement to create a proxy user that will execute all database operations, but which may not have all the superuser rights of SYSDBA.

In this section we will create a database user, and assign the account viewing and updating rights.

We will need to use the gsec utility for this operation. So, supposing we want to create a user called TestAdmin with password testadmin (I know, I know, another weak password) and give him viewing, modification and deletion rights to firstdb.fdb, we will execute the following commands. Note that only the first 8 characters are used for the password.

$ gsec -user SYSDBA -password masterkey
GSEC> add TestAdmin -pw testadmin -fname FirstDB -lname Administrator
Warning - maximum 8 significant bytes of password used
GSEC> quit

Next, we open the database, create a firstdbadmin ROLE for the database, assign the appropriate rights to that role, then add TestAdmin to the role.

$ isql firstdb.fdb -user SYSDBA -password masterkey
Database:  firstdb.fdb, User: SYSDBA
SQL> CREATE ROLE firstdbadmin;
SQL> GRANT SELECT, UPDATE, INSERT, DELETE ON sales_catalog
CON> TO ROLE firstdbadmin;
SQL> GRANT firstdbadmin TO TestAdmin;
SQL> quit;

Now, we are ready to test our database.

Testing the Database

First, exit gsec and isql, if you have not already done so.

We will login to firstdb.fdb as user TestAdmin with the role firstdbadmin, run some queries, then exit. The commands, and the results are shown below:

$ isql firstdb.fdb -user TestAdmin -password testadmin -role firstdbadmin
SQL> DELETE FROM sales_catalog;
SQL> INSERT INTO sales_catalog 
CON> VALUES('001', 'Aluminum Wok', 'Chinese wok');
SQL> INSERT INTO sales_catalog 
CON> VALUES('002', 'Microwave Oven', '300W Microwave oven');
SQL> INSERT INTO sales_catalog 
CON> VALUES('003', 'Chopsticks extra-long', '60cm chopsticks');
SQL> SELECT * FROM sales_catalog;

ITEM_ID    ITEM_NAME                       ITEM_DESC
=========  ============================= ========================

001        Aluminum Wok                   Chinese wok
002        Microwave Oven                 300W Microwave oven
003        Chopsticks extra-long          60cm chopsticks

If you encounter any SQL errors at any point, you will need to check with the additional references section at the end of this doc for sources of Firebird info.

If everything worked, congratulations ! Your Firebird is now ready to fly !