System Table Security

From InterBase
Jump to: navigation, search

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, PUBLIC users have only SELECT privileges 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 GRANT statement.

Older Databases

InterBase applies this default security (no write access for PUBLIC) to older databases whenever possible:

  • The gbak backup/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 <ib_install>/examples/security directory: readmeta.sql, writemeta.sql and 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.sql applies the default PUBLIC access 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.sql grants all system table privileges to PUBLIC. This is the behavior that existed in InterBase 6.0 and earlier.
  • blindmeta.sql revokes all system table privileges from PUBLIC. This prevents any PUBLIC user from querying the system tables, including InterBase and third-party utilities run by PUBLIC users. For example, gstat, 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_blob_lookup_desc() and isc_array_lookup_bounds(), cannot execute without SELECT metadata privileges, because the APIs directly query some InterBase system tables. Thus databases that have had blindmeta.sql run against them are not able to execute these APIs for any users except the owner, the system administrator, and SYSDBA.
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.sql on 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.sql against 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.

See Also

Advance To: