The System and Public Schemas
Two schemas exist by default in every SAND CDBMS database: SYSTEM and PUBLIC. The SYSTEM schema contains tables that record information about each object (including user authorizations, privileges, schemas, domains, tables/views, columns, and constraints) in the database. These tables are updated automatically to reflect changes made to the database, and should never be altered by a user.
Only the user DBA, or another user that has been granted DBA privileges, can access the information in the SYSTEM schema. Other users can see more restricted information by querying the views in the PUBLIC schema. The PUBLIC schema is accessible to all database users, and contains views based on the tables in the SYSTEM schema. These views allow database users to view information about only those objects which they own or on which they have privileges. The user DBA, as the database “super-user”, may query the public views and retrieve information about all objects in the database.
Public Views
The views contained in the PUBLIC schema include the following:
- AUTHORIZATIONS
- SCHEMAS
- TABLES
- COLUMNS
- DOMAINS
- CONSTRAINTS
- CONSTRAINTCOLS (columns declared as NOT NULL, or participating in a PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint)
- TABPRIVS (privileges held on tables)
- COLPRIVS (privileges held on individual columns)
- SCHEMAPRIVS (privileges held on schemas)
- DATATYPES
- VIEWTEXT (text definitions of views)
These views may be queried in the way that tables are normally queried using SELECT statements. For example, the following query retrieves all available information regarding domains owned by the user executing the query or upon which the user has some privilege:
SELECT * FROM PUBLIC.domains;
see also
SAND CDBMS SQL Reference Guide, chapter 5: System Tables and Public Views