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.