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,
PUBLIC
users have onlySELECT
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 theGRANT
statement.
Contents
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 defaultPUBLIC
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 toPUBLIC
. This is the behavior that existed in InterBase 6.0 and earlier.blindmeta.sql
revokes all system table privileges fromPUBLIC
. This prevents anyPUBLIC
user from querying the system tables, including InterBase and third-party utilities run byPUBLIC
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
() andisc_array_lookup_bounds
(), cannot execute withoutSELECT
metadata privileges, because the APIs directly query some InterBase system tables. Thus databases that have hadblindmeta.sql
run against them are not able to execute these APIs for any users except the owner, the system administrator, andSYSDBA
. - 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 runreadmeta.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
- 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