Filter
Top Products
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: