System Table Security
Go Up to Database User Management
InterBase stores the database metadata in its system tables. These tables have an intricate set of dependencies between them, and writing to one without sufficient knowledge can corrupt the database. For this reason, the system tables have the following default security access applied to them:
- By default,
PUBLICusers have only
SELECTprivileges on the system tables.
- The database owner, the SYSDBA user, and the operating system administrator (root on UNIX and Administrator on Windows server platforms) have full access to the system tables, including write permission. These users can, if desired, assign write privileges to individual users or to
PUBLIC, using the
InterBase applies this default security (no write access for
PUBLIC) to older databases whenever possible:
gbakbackup/restore utility applies the default security to any database when it is restored to ODS 10.1 (InterBase 6.5) or later.
- When an InterBase server that is version 6.5 or later attaches an older database, it applies the default privileges to that database if they are not already present, even if the database is ODS 10.0 or earlier.
Scripts for Changing Database Security
Three SQL scripts are included in
blindmeta.sql. These scripts can be run against databases with isql to make wholesale changes to system tables access privileges of a database, except or rdb$users for security purposes.
readmeta.sqlapplies the default
PUBLICaccess privileges: PUBLIC can only select from the system tables, but the database owner, system administrator, and SYSDBA user have full access. This script can be used to return a database that has customized system table privileges back to this default.
writemeta.sqlgrants all system table privileges to
PUBLIC. This is the behavior that existed in InterBase 6.0 and earlier.
blindmeta.sqlrevokes all system table privileges from
PUBLIC. This prevents any
PUBLICuser from querying the system tables, including InterBase and third-party utilities run by
PUBLICusers. For example,
gbak, QLI and IBConsole would not be able to query system metadata. This script allows developers to protect their intellectual property by hiding the database design of tables, stored procedures and triggers from the general public and competitors. Blind access makes it difficult, if not impossible, for a general user to generate ad hoc queries against a database.
- A database with blind access does not prevent any user from using InterBase Data Definition Language (DDL) to define new database objects. It just prevents a user from querying or writing to the system tables directly.
- isc_blob_lookup_desc() and isc_array_lookup_bounds() Two client-side APIs,
isc_array_lookup_bounds(), cannot execute without
SELECTmetadata privileges, because the APIs directly query some InterBase system tables. Thus databases that have had
blindmeta.sqlrun against them are not able to execute these APIs for any users except the owner, the system administrator, and
- Older InterBase clients InterBase 6.0 and previous InterBase kits cannot access a database on behalf of a user if that user has no privileges to the system tables. Thus an InterBase developer who runs
blindmeta.sqlon an InterBase database cannot ship that database to customers with InterBase 6.0 or older runtime kits and expect those users to be able to access the database. The developer would have to run
readmeta.sqlagainst a copy of the database and ship that to customers who have older InterBase runtimes.
System Table Security Migration Issues
The InterBase engine automatically installs the default (
SELECT-only) SQL privileges for
PUBLIC on the system tables when attaching ODS 10.0 or older databases. Thus if all users must be able to write to database metadata,
writemeta.sql will have to be run against each database to restore that behavior.
- Security Model
- The InterBase Security Database
- Implementing Stronger Password Protection
- Enabling Embedded User Authentication
- SQL Privileges
- Groups of Users
- Other Security Measures
- User Administration with IBConsole
- User Administration With the InterBase API
- Using gsec to Manage Security
- Using gsec to Manage Database Alias
- gsec Error Messages