Knowledgebase

When do I have to specify the owner of a table?

The answer depends on the SQL owner of the table:

• the user who creates the table owns the table and does not need to specify the owner's name
• users who did not create the table must specify the owner's name

In SQL, any user can create a table within their user space (a "schema"). The user name in FairCom DB SQL acts as the schema name. If no schema is explicitly specified, the user is the assumed schema. The user who created the table does not need to specify their own name when accessing the table.

To access tables outside of your schema, you must have the appropriate privileges (through GRANT or by being an ADMIN) and you must explicitly specify the owner of the table (the schema).

Example:

The ADMIN user creates a table called "custmast" and GRANTs SELECT privileges to a user called JOHNDOE. This allows JOHNDOE to access this table by explicitly specifying "ADMIN"."custmast" to properly distinguish the correct "custmast" table. (Quotes not necessary in most cases.)

Imported Tables

With imported tables, the owner defaults to the user who authenticated with the import utility. If the user has ADMIN privileges, a different owner can be specified with the ‑o switch. See ctsqlimp Usage in the c-treeSQL Server Operations and Utilities Guide.

Setting a Default Schema

To set a default "schema" use the SET SCHEMA statement. If JOHNDOE executes the following, then "ADMIN".<tablename> will be assumed for tables without an explicit schema for the remainder of JOHNDOE's session:

SET SCHEMA ADMIN

SET SCHEMA can be called as needed within a session.