Installation, configuration and use of Firebird database

Installation, configuration and use of Firebird database

[warm tip: all operations in this document are performed under the root user]

1, Installation

1. rpm package mode

The installation method I chose was to use the rpm package

Execute the following command to install:

$ rpm -ivh FirebirdSS-2.5.9.27139-0.amd64.rpm --force --nodeps

Without - force --nodeps, / bin / SH is needed by XXXXXX (the name of the installation package) will appear

View firebird process after successful installation

The installation location will be / opt/firebird by default

The details of the installation package show that the installation cannot be redirected [relocations: (not relocatable)]

$ ps aux | grep firebird
firebird   4684  0.0  0.0  35396   408 ?        S    Oct15   0:00 /opt/firebird/bin/fbguard -pidfile /var/run/firebird/firebird.pid -daemon -forever
firebird   4686  0.0  0.6 362008 12592 ?        Sl   Oct15   0:00 /opt/firebird/bin/fbserver

The installation location of firebird is / opt/firebird by default. Enter the folder

$ cd /opt/firebird
$ ls
aliases.conf  examples       firebird.log  IDPLicense.txt  lib      security2.fdb
bin           fb_guard       firebird.msg  include         misc     SYSDBA.password
de_DE.msg     fbtrace.conf   fr_FR.msg     intl            plugins  UDF
doc           firebird.conf  help          IPLicense.txt   README   WhatsNew

2. tar package mode

Unzip the tar package by executing the following command:

$ tar -zxvf FirebirdSS-2.5.9.27139-0.amd64.tar.gz
# Enter the extracted folder
$ cd FirebirdSS-2.5.9.27139-0.amd64
# Use install.sh to install
$ ./install.sh

Firebird super 2.5.9.27139-0.amd64 Installation

Press Enter to start installation or ^C to abort # Type enter to start the installation or Ctrl+C to abort the installation
Extracting install data
update-rc.d: warning: start and stop actions are no longer supported; falling back to defaults
update-rc.d: warning: start runlevel arguments (2 3 5) do not match firebird Default-Start values (2 3 4 5)
Starting Firebird server: Please enter new password for SYSDBA user: root123 # Set the password of the user sysdba here
Install completed

View firebird process after successful installation

$ ps aux | grep firebird
firebird 18132  0.0  0.0  35388   412 ?        S    16:54   0:00 /opt/firebird/bin/fbguard -pidfile /var/run/firebird/firebird.pid -daemon -forever
firebird 18133  0.0  0.0 230664 11048 ?        Sl   16:54   0:00 /opt/firebird/bin/fbserver

The installation location of firebird is / opt/firebird by default. Enter the folder

$ cd /opt/firebird
$ ls
aliases.conf  examples       firebird.log  IDPLicense.txt  lib      security2.fdb
bin           fb_guard       firebird.msg  include         misc     UDF
de_DE.msg     fbtrace.conf   fr_FR.msg     intl            plugins  WhatsNew
doc           firebird.conf  help          IPLicense.txt   README
# Since the password of SYSDBA user has been set during tar package installation, there is no SYSDBA.password file here

2, Disposition

Add the installation directory to the / etc/profile configuration file for firebird system wide use

$ vi /etc/profile
# Add the following fields to the / etc/profile configuration file
export FIREBIRD_HOME=/opt/firebird
export PATH=$PATH:$FIREBIRD_HOME/bin
# After joining, save the file and execute the configuration file
$ source /etc/profile

View the default login account and password (rpm installation mode)

cat /opt/firebird/SYSDBA.password

The following will be displayed, ISC_USER is the user, ISC_PASSWD is the password

# Firebird generated password for user SYSDBA is:

ISC_USER=sysdba
ISC_PASSWD=4FCEB54C

# generated on koal at time Fri Oct 15 07:00:16 UTC 2021

# Your password can be changed to a more suitable one using the
# /opt/firebird/bin/gsec utility.

◆ gsec:
This is a secure system program. You can use the command line to create, modify and delete database users, change passwords, etc. You must run gsec as SYSDBA, call and execute the following command as root.

Enter the security system through the following command and then modify the password (if firebird is not added to the configuration file, you can go to the bin folder of the installation directory. / gsec -user sysdba -password 4FCEB54C)

gsec -user sysdba -password 4FCEB54C

A gsec > prompt will appear, and you can use the display command to display the current user

GSEC> display
     user name                    uid   gid admin     full name
-------------------------------------------------------------------------------------
	SYSDBA                        0     0			Sql Server Administrator

The default password is not secure. It is recommended to change the password of SYSDBA. You can use the following command to change the password

GSEC> modify SYSDBA -pw root123

root123 in the command is the new password, which can be set according to your own needs

3, Use

1. Test run

◆ isql:
This is an interactive SQL tool, similar to SQL*Plus of Oracle and psql command of Postgresql. You can use it to test and run SQL queries.

firebird comes with a database instance named employee.fdb, which can be used to test SQL commands. The commands are as follows:

# Since some database related systems also install a tool named isql, try to run the isql tool in the bin folder of the installation directory
cd /opt/firebird/bin
./isql /opt/firebird/examples/empbuild/employee.fdb -u sysdba -p root123
# The following is displayed, indicating that you are connected to the employee database
Database:  /opt/firebird/examples/empbuild/employee.fdb, User: sysdba
SQL>

Note: the storage location of each version of employee.fdb is different. 1.5.2 is in / opt/firebird/examples / directory, and 2.5.9 is in / opt/firebird/examples/empbuild /

Use the show tables command to view all current tables and display the following:

SQL> show tables;
       COUNTRY                                CUSTOMER
       DEPARTMENT                             EMPLOYEE
       EMPLOYEE_PROJECT                       JOB
       PROJECT                                PROJ_DEPT_BUDGET
       SALARY_HISTORY                         SALES
       
SQL> 

2. Create database

You can create a database in two ways

(1) The user is indicated when the isql tool is opened

$ isql -user sysdba -password root123
# A message will be displayed
Use CONNECT or CREATE DATABASE to specify a database
SQL> 
# Enter the following SQL statement to create the database
SQL> CREATE DATABASE 'firstdb.fdb';
SQL> 
# If there is no response, the creation is successful

(2) Specify user when creating database

$ isql
# A message will be displayed
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'firstdb.fdb' USER 'sysdba' PASSWORD 'root123';
SQL>
# If there is no response, the creation is successful, and the database is owned by sysdba
# Now create a table and insert data
$ isql firstdb.fdb -user sysdba -password root123
Database:  firstdb.fdb, User: sysdba
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', 'Aluminum Wok', 'Chinese wok');
SQL> INSERT INTO sales_catalog VALUES('002', 'Microwave Oven', '300W Microwave oven');
SQL> INSERT INTO sales_catalog 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       

SQL> 

3. Add users and assign permissions

(1) Create user: use gsec tool to create user LiuYu

The statement format for creating a user is: [add user name - pw password - fname last name - lname first name]

$ gsec -user sysdba -password root123
# Enter the gsec tool and enter the following statement to create a user
GSEC> add LiuYu -pw root123 -fname Liu -lname Yu
GSEC> 
# No prompt indicates success; Enter the following statement to view all current users
GSEC> display
     user name                    uid   gid admin     full name
-------------------------------------------------------------------------------
SYSDBA                              0     0           Sql Server Administrator
TESTADMIN                           0     0           FirstDB  Administrator
MASTERYI                            0     0           Master  Yi
LIUYU                               0     0           Liu  Yu
GSEC> 

(2) Assign permissions: use the isql tool to assign permissions to the specified user

Format of permission assignment statement: GRANT permission ON object TO user [with grant option]

Permissions: SELECT, DELETE, UPDATE, INSERT, ALL

Objects: table / view names

User: the specified user. PUBLIC represents all users

with grant option: optional -- let the user have the permission of the specified user

Format of REVOKE permission statement: REVOKE permission ON object FROM user

$ isql firstdb.fdb -user sysdba -password root123
# Enter the isql tool and use the specified database firstdb.fdb
# Enter the following statement to assign permissions to the specified table or view to the specified user
SQL> GRANT SELECT,UPDATE,INSERT,DELETE ON sales_catalog TO LiuYu;
SQL> quit; 
# No prompt indicates that the statement is executed successfully. Next, use the new user operation firstdb.fdb to test whether the permission has been allocated
$ isql firstdb.fdb -user LiuYu -password root123
Database:  firstdb.fdb, User: LiuYu
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             004        What is this                             762mm ammo                 

SQL> DELETE FROM sales_catalog;
SQL> INSERT INTO sales_catalog VALUES('666', 'Lucky Dog', 'You are so lucky');
SQL> SELECT * FROM sales_catalog;

ITEM_ID    ITEM_NAME                                ITEM_DESC                  
=============================================================================
001        Lucky Dog                                You are so lucky        

SQL>

4, Error reporting

The following errors are likely to occur during database creation:

1. If the user is not specified, the following error will appear, which means that the user name and password are not defined

Statement failed, SQLSTATE = 28000
Your user name and password are not defined. Ask your database administrator to set up a Firebird login.

You only need to specify the user when creating the database. Both methods above are OK.

2. The following errors are likely to occur during database creation

Statement failed, SQLSTATE = 08001
I/O error during "open O_CREAT" operation for file "/opt/firebird/examples/firstdb.fdb"
-Error while trying to create file
-Permission denied

This is because other users do not have write permission to the examples folder, so they cannot create files

You can consider creating a new folder as the storage location of the database in the future, and changing the permissions of this folder to be readable and writable by all users

$ cd /opt/firebird
$ mkdir db
# Add write permissions for other users to the db folder
$ chmod o+w db/
# Add read, write and execute permissions for all users to the db folder
$ chmod a+rwx db/

After that, you can create a database in the db folder without reporting an error

Guess (not verified, only personal guess):

The user firebird is added after installing firebird. Because root permission must be used during installation, the owner of most files of firebird is root and the owner of a small number of files is firebird.

Personally, I think it may be that firebird uses the firebird user when creating the database. If you create files in the folder with firebird as the owner, there should be no problem. When writing files to the folder with root as the owner, it depends on whether the write permission of this folder to other users (that is, the user who actually creates the database) is enabled.

Tags: Database

Posted on Fri, 22 Oct 2021 05:20:21 -0400 by gojakie