Sybase 12.4.2 User Manual

Database object names and prefixes

368

SELECT *

FROM "DBA".employee

The employee 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: