Default Security and Access - Firebird

A database and all its objects (tables, views, and stored procedures) are secured against unauthorized access when they are created. That is, access is “opt-in.” No user can access any object in the database unless granted permission to do so. Except for especially privileged users —owner, SYSDBA, and (on POSIX) the Superuser—users must be granted SQL privileges for any operation, even a SELECT.

And Now the Bad News

There is a big catch with this, so don’t be lulled into a false sense of security. Non- existent objects are not protected. Any user with access to a database may create any valid database object—including declarations for external functions and tables linked toexternal tables—that could potentially be used in combination to install and run malicious code on the server.

With the ability to limit the places a server is allowed to access external artifacts in Firebird 1.5 installations, the situation is less risky. However, it has not gone away—you must take explicit steps to implement the feature and the supporting operating system filesystem restrictions. Default access to external files is set to NONE by the installers, and the external function directories are restricted to the UDF tree. It is up to you to take care of the system restrictions.

The system tables, where Firebird stores all metadata, including the SQL privileges themselves, are not protected by SQL privileges at all.

A Trick to Beat Idiot Users and Bad Guys

My esteemed colleague Pavel Cisar offered to share his workaround for the lack of SQL privilege protection for Firebird metadata, which tempts idiot users to avoid DDL and change metadata by messing with the system tables. It also beats malicious attempts to break your metadata with a script. Here it is.

Although it appears that PUBLIC has ALL access to the system tables, it’s just a weird backdoor to SQL rights management that could be easily fixed. You can restrict access to the system tables, just as to any other table in the database, by granting and revoking permissions. However, rights have to be granted in order to be revoked.

What is needed is just to set access management for the system tables to the standard routine by executing a series of GRANT ALL ON <system-table> TO PUBLIC statements. After that, we can revoke rights at will.

I don’t know the exact technical explanation for why it works the way it does, but here’s my guess. The GRANT statement creates an access control list (ACL) for the system table, which is checked by the code. Some branch in the code assumes “Grant all to public if no ACL is found for a system table, else use the ACL.”

Keep in mind that

• This setup doesn’t survive a restore from backup, so write a script that you can use each time you kick in a freshly restored database.
• You need to be careful about removing SELECT rights from PUBLIC on certain system tables, as the API functions isc _blob _lookup _desc( ) and isc_array_lookup_bounds( ) depend on its being available. Some tools and libraries may depend on it as well.
• Removing write rights to system tables doesn’t restrict the ability to update them the right and proper way, through DDL commands. Only fiddling directly with the system tables is prevented.

A privilege enables a user to have some kind of access to an object in the database. It is enabled using a GRANT statement and taken away using a REVOKE statement. The syntax pattern for enabling access is

A <privilege> granted to a <user> on an <object> constitutes a permission

For removing a permission:

Several variants to the “core” syntax are available. We’ll explore them a little later.