Database object names and prefixes

The name of every database object is an identifier.

For information about the rules for valid identifiers, see Identifiers.

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

SELECT *
FROM Employees;

Tables, procedures, and views all have an owner. The DBA user ID owns the tables in the sample database. 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 other circumstances it is enough to give the object name. This section describes when you need to use the owner prefix to identify tables, views and procedures, and when you do not.

When referring to a database object, you require a prefix 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. The user ID company created all the tables, and since this user ID belongs to the database administrator, it therefore has DBA authority.

CREATE USER Company
IDENTIFIED BY secret;
GRANT DBA TO Company;

The company user ID created the tables in the database.

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.

CREATE USER Sally IDENTIFIED BY xxxxx;
CREATE USER Joe IDENTIFIED BY xxxxx;
CREATE USER 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 statement now works:

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.) So, Joe and Sally still get an error executing either of these statements:

SELECT *
FROM Salaries;
SELECT *
FROM company.Salaries;

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

Discuss this page in DocCommentXchange.