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
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 isGRANT <privilege>
A <privilege> granted to a <user> on an <object> constitutes a permission
For removing a permission:REVOKE <privilege>
Several variants to the “core” syntax are available. We’ll explore them a little later.
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|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.