Firebird provides the gsec utility as a command-line interface for maintaining the security database. It has its own shell for interactive use, or gsec commands can be run directly from an operating system command shell or an executable script (shell script or batch file).
Any authentic user can run gsec, but only the SYSDBA user can modify the user data stored in it. The user name and password will be required unless the environment variables ISC _USER and ISC _PASSWORD are visible to the command shell in which you are running. On POSIX, if you are logged in as root, you can invoke gsec without entering –user and –password parameters.
Starting a gsec Interactive Session
At the command line, at Firebird’s /bin directory prompt, type the following.
For POSIX:./gsec -user sysdba -password masterkey
gsec -user sysdba -password masterkey
The prompt changes to GSEC>, indicating that you are running gsec in interactive mode.
To end an interactive session, type QUIT at the prompt.
Running gsec As a Remote Client
SYSDBA can use gsec on a client machine to administer user authentication on a remote server. The invocation syntax is different: It needs the –database switch followed by the full network path to the security database. For example (single command), this example shows a POSIX client to Windows server v.1.5:./gsec -database hotchicken:c:Program FilesFirebirdFirebird_1_5 security.fdb -user sysdba -password masterkey
This example shows a Windows client to POSIX server v.1.0.x:gsec -database coolduck:/opt/firebird/isc4.gdb -user sysdba -password masterkey
The gsec interactive commands are display, add, modify, delete, help, and quit. They are not case sensitive.
The USERS Table and gsec Options
Table shows the columns in the USERS table, together with their corresponding gsec option switches. Only USER_NAME and PASSWD are required fields when using gsec.
Columns in the USERS Table and gsec Options
This displays the main columns of the USERS table in the security database. Passwords are never displayed.user_name uid gid full name ---------------------------------------------- SYSDBA MICKEY 123 345 Mickey Mouse D_DUCK 124 345 Donald Duck JULIUS 125 345 J. Caesar ...
To display the same information for a single row from the USERS table:
GSEC> display username
For example:GSEC> display julius user_name uid gid full name ---------------------------------------------- JULIUS 125 345 J. Caesar
This adds a user to the USERS table:a[dd] user_name -pw password [other switches]
where username is a unique, new user name and password is the password associated with that user.
For example:GSEC> add mmouse -pw veritas
To add authorization for a user named Harry Potter with the user name hpotter and the password noMuggle, enterGSEC> add hpotter -fname Harry -lname Potter -pw noMuggle
To verify the new entry:GSEC> display hpotter user_name uid gid full name ---------------------------------------------- HPOTTER Harry Potter
This is for changing (editing) a column value on an existing USERS record. Supply the user name of the user to change, followed by one or more switches indicating the items to change and the new value for each.
For example, to set the user ID of user mickey to 25, change the first name to Michael and change the password to icecream, enter the following switches:GSEC> modify mickey -uid 25 -fname Michael -pw icecream
You can’t change a username. Delete the old user and add a new one.
This deletes the user username from the USERS table. Delete takes no other arguments or switches.de[lete] username
For example:GSEC> delete mickey
You can use the display command to confirm that the entry has been deleted.
h[elp] or ?
Either of these switches displays a summary of the gsec commands, switches, and syntax.
This ends the interactive session.
Using gsec from a Command Prompt
To use gsec from a command prompt, convert each gsec interactive command to a command switch by prefixing it with a hyphen (–). The option switches are the same.
For example, to add the user Claudio and assign the password dbkeycop, you would enter the following on the command line.
On Windows:..BIN> gsec -add claudio -pw dbkeycop -user SYSDBA -password masterkey
On POSIX:bin]$ ./gsec -add claudio -pw dbkeycop -user SYSDBA -password masterkey
To display the contents of the USERS table, enter> gsec -display
and so on.
gsec Error Messages
gsec Error Messages
SPECIAL TOPIC: Customizing User Security
This section written and printed here with permission by Ivan Prenosil.
Firebird user authentication is—wel l—problematic. The good news is that it’s all going to change for the better in Firebird 2. The bad news is that we have to live with it for some time yet. Ivan Prenosil, a long-time, well-practiced expert in developing with Firebird and its ancestors, developed some techniques for customizing the security database. Ivan agreed to share his tips and scripts with you in this special topic.
The Security Database
When a user logs into a Firebird database, its password is verified against an encrypted password in the security database. On v.1.0.x, the name of the database is isc4.gdb. It is security.fdb on v.1.5. It is an ordinary Firebird database, and the table that’s used for user authentication is USERS. Its (simplified) structure looks like this:CREATE TABLE USERS ( USER_NAME VARCHAR(128), PASSWD VARCHAR(32) ); /* */ GRANT SELECT ON USERS TO PUBLIC;
On examining the permissions for this table, two major drawbacks are obvious. One is that any user can read the full list of users and encrypted passwords. The other is that only SYSDBA can modify the table—so users cannot change their own passwords.
However, if you are SYSDBA, you can connect to the security database, just as you can to any other database, and modify its structure. That means you can improve it.
In Firebird 1.0.x, when authenticating users, the server connects to the security database, retrieves the necessary information, and then disconnects. Between connections, you can “mess around” with isc4.gdb.
In Firebird 1.5, however, once a connection has been established to the security database, it is retained until the server is shut down. With v.1.5, therefore, you’ll need make all changes to a copy of the security database and install it afterward. Here are the steps:
Allowing Users to Change Their Own Passwords
The easiest and best-known modification is to grant update permissions to give non-SYSDBA users access, GRANT UPDATE ON USERS TO PUBLIC, and add a trigger to prevent any user except SYSDBA from modifying somebody else’s password.
Here is my script:/* Copyright Ivan Prenosil 2002-2004 */ CONNECT 'C:Program FilesFirebirdFirebird_1_5security.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
It’s awkward having to grant permissions on all of the columns when PASSWD is the only one you really want to make accessible. Unfortunately, it is needed if your password application is going to use gsec or the Services API.
This modification doesn’t get around the problem that the full list of users and their encrypted passwords is visible to PUBLIC. It makes it very easy for a user to download a list of the other users’ passwords and try to break them locally by brute force.
How to Hide the Users/Passwords List
If you rename the USERS table and re-create USERS as a view of the renamed table, you can have the best of both worlds. Users will be able to modify their own passwords, and the full list of users and passwords can be hidden from PUBLIC. Each non-SYSDBA user will see only one record in security.fdb (or isc4.gdb, if your server is v.1.0.x). The new structures in security.fdb will be more like the following schema:/* Copyright Ivan Prenosil 2002-2004 */
The real table USERS2 is visible only to SYSDBA. The conditionUSER = USER_NAME
ensures that each user sees its own record. The conditionUSER = 'SYSDBA'
ensures that SYSDBA can see all records. The conditionUSER = ''
is important because the USER and CURRENT_USER variables contain empty strings during password verification.
How to Log Login Attempts
Replacing the USERS table in the security database with a USERS view has one great benefit: It allows us to call a stored procedure whenever a user tries to log in. When the
Firebird server executes the statement
we can execute a procedure that acts like a select trigger or a login trigger. The procedure could be written so that it could, for example, refuse login during some parts of the day, or log a timestamp when users try to log in.
We can log only the names of known users—that is, names already stored in the not USERS table—and it is possible to report whether login was successful. Still, even the limited information we can log can be useful. It will inform us of login attempts at unusual times, and it will reveal suspicious numbers of logins over short periods.
To implement this, we need a table to log into and a stored procedure to do the work.
The Log Table
The log table has to be an external table, because the transaction used by the server to do its authentication is not committed, but rolled back. External tables don’t get “unwritten” by rollback as inserts to a regular table would.CREATE TABLE log_table EXTERNAL FILE 'C:Program FilesFirebirdFirebird_1_5security.log' ( tstamp TIMESTAMP, uname CHAR(31) );
If you want the log table to be readable as a text file, you can use CHAR(20) instead of TIMESTAMP and cast it, and add two more columns: a single CHAR to separate the tstamp and uname columns and a CHAR(2) to be filled with carriage return and line- feed codes.
The Logging Procedure
The stored procedure is a selectable type, able to be called from the view. When a login succeeds, SUSPEND gets called and a row is written to the log. When the login fails, because the row requested from the view is forbidden, the procedure just terminates without writing anything. The output parameter is just formal; its value is ignored.CREATE PROCEDURE log_proc (un VARCHAR(31)) RETURNS (x CHAR(1)) AS BEGIN IF (USER = '') THEN INSERT INTO log_table (TSTAMP, UNAME) VALUES ( CURRENT_TIMESTAMP, :un); /* and don't forget to change the fields written if you modify log_table to make it a text file! */ IF (USER = '' OR USER = 'SYSDBA' OR USER = :un) THEN SUSPEND; END
We test (USER = '') because, when Firebird verifies the password, the USER variable is empty. It helps distinguish whether the password is being verified by the server or the user is directly connected to the security database.
Implementing the New Setup
We need to drop the view that is being used in our restructured security database and create a new version that calls the stored procedure:CREATE VIEW USERS (USER_NAME) AS
Remember to reinstate the permissions that were lost when the view was dropped:GRANT SELECT ON USERS TO PUBLIC;
There are some more permissions to add, relating to the stored procedure:GRANT INSERT
Because log entries are constantly appended to log_table, it will be necessary to delete or rename the external file from time to time. The security database and, thus, the external file, are released after the user authentication is finished, so renaming the file should not be a problem.
How to Slow Down Intruders
Once we are able to log the user names that tried to log into the database, and the times they tried, we can use this information to further restrict access.
For example, it would be possible to count the number of login attempts by a given user name during the last minute and, if the number reached some ceiling, to block connection by that user name. It would allow us some protection from brute force attacks, where someone tries to break into the database by repeatedly scanning possible passwords. We can put a temporary block on any user name that displays attacker like behavior.
The time interval and the login count limit need to be chosen carefully, so as to slow down the intruder but not impose penalties on bona fide users who just type badly. Open VMS uses a similar approach.
The relevant piece of code in the stored procedure is as simple as this:...
You can change the constants, namely 3 (the allowed number of mistakes) and 0.0007 (the interval, approximately 1 minute). This procedure will affect all users. One possible modification would be to choose one non-SYSDBA user (not SYSDBA, because it is the user name most likely to be targeted) and exclude it from the blocking procedure. Make it the user that owns all the databases, thus endowing it with the rights to perform database shutdowns.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.