Database object names and prefixes

The name of every database object is an identifier. The rules for valid identifiers are described in the section “Identifiers” in Chapter 2, “SQL Language Elements,”in Reference: Building Blocks, Tables, and Procedures.

In queries and sample SQL statements throughout this guide, database objects from the demo database are generally referred to using their simple name. For example:

SELECT *
FROM Employees

Tables, procedures, and views all have an owner. The owner of the tables in the demo database is the user ID DBA. In some circumstances, you must prefix the object name with the owner user ID, as in the following statement.

SELECT *
FROM "DBA".Employees

The Employees table reference is said to be qualified. (In this case the owner name is enclosed in double quotes, as DBA is a SQL keyword.) In other circumstances it is sufficient to give the object name. This section describes when you need to use the owner prefix to identify tables, view and procedures, and when you do not.

When referring to a database object, a prefix is required unless:

You are the owner of the database object.
The database object is owned by a group ID of which you are a member.

Example

Consider the following example of a corporate database. All the tables are created by the user ID company. This user ID is used by the database administrator and is therefore given DBA authority.

GRANT CONNECT TO company
IDENTIFIED BY secret;
GRANT DBA TO company;

The tables in the database are created by the company user ID.

CONNECT USER company IDENTIFIED BY secret;
CREATE TABLE company.Customers ( ... );
CREATE TABLE company.Products ( ... );
CREATE TABLE company.Orders ( ... );
CREATE TABLE company.Invoices ( ... );
CREATE TABLE company.Employees ( ... );
CREATE TABLE company.Salaries ( ... );

Not everybody in the company should have access to all information. Consider two user IDs in the sales department, Joe and Sally, who should have access to the Customers, Products and Orders tables. To do this, you create a Sales group.

GRANT CONNECT TO Sally IDENTIFIED BY xxxxx;
GRANT CONNECT TO Joe IDENTIFIED BY xxxxx;
GRANT CONNECT TO Sales IDENTIFIED BY xxxxx;
GRANT GROUP TO Sales;
GRANT ALL ON Customers TO Sales;
GRANT ALL ON Orders TO Sales;
GRANT SELECT ON Products TO Sales;
GRANT MEMBERSHIP IN GROUP Sales TO Sally;
GRANT MEMBERSHIP IN GROUP Sales TO Joe;

Now Joe and Sally have permission to use these tables, but they still have to qualify their table references because the table owner is company, and Sally and Joe are not members of the company group:

SELECT *
FROM company.customers

To rectify the situation, make the Sales group a member of the company group.

GRANT GROUP TO company;
GRANT MEMBERSHIP IN GROUP company TO Sales;

Now Joe and Sally, being members of the Sales group, are indirectly members of the company group, and can reference their tables without qualifiers. The following command will now work:

SELECT *
FROM Customers

Note

Joe and Sally do not have any extra permissions because of their membership in the company group. The company group has not been explicitly granted any table permissions. (The company user ID has implicit permission to look at tables like Salaries because it created the tables and has DBA authority.) Thus, Joe and Sally still get an error executing either of these commands:

SELECT *
FROM Salaries;
SELECT *
FROM company.Salaries

In either case, Joe and Sally do not have permission to look at the Salaries table.