Database object names and prefixes

The name of every database object is an identifier. In the sample queries used in this documentation, database objects from the sample database are generally referred to using only their identifier. For example:

SELECT * FROM Employees;

Tables, procedures, and views all have an owner. The GROUPO user owns the sample 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 GROUPO.Employees;

The Employees table reference is 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 role that you have been granted.

Example

Consider the following example of a corporate database for the Acme company. A user ID Admin is created with full administrative privileges on the database. Two other user IDs, Joe and Sally, are created for employees who work in the sales department.

CREATE USER Admin IDENTIFIED BY secret;
GRANT ROLE SYS_AUTH_DBA_ROLE TO Admin;
CREATE USER Sally IDENTIFIED BY xxxxx;
CREATE USER Joe IDENTIFIED BY xxxxx;

The Admin user creates the tables in the database and assigns ownership to the Acme role.

CREATE ROLE Acme;
CREATE TABLE Acme.Customers ( ... );
CREATE TABLE Acme.Products ( ... );
CREATE TABLE Acme.Orders ( ... );
CREATE TABLE Acme.Invoices ( ... );
CREATE TABLE Acme.Employees ( ... );
CREATE TABLE Acme.Salaries ( ... );

Not everybody in the company should have access to all information. Joe and Sally, who work in the sales department, should have access to the Customers, Products, and Orders tables but not other tables. To do this, you create a SalesForce role, assign this role the privileges required to access a restricted set of the tables, and assign the role to these two employees.

CREATE ROLE SalesForce; 
GRANT ALL ON Acme.Customers TO SalesForce;
GRANT ALL ON Acme.Orders TO SalesForce;
GRANT SELECT ON Acme.Products TO SalesForce;
GRANT ROLE SalesForce TO Sally;
GRANT ROLE SalesForce TO Joe;

Joe and Sally have the privileges required to use these tables, but they still have to qualify their table references because the table owner is Acme.

SELECT * FROM Acme.Customers;

To rectify the situation, you grant the Acme role to the Sales role.

GRANT ROLE Acme TO SalesForce;

Joe and Sally, having been granted the Sales role, are now indirectly granted the Acme role, and can reference their tables without qualifiers. The SELECT statement can be simplified as follows:

SELECT * FROM Customers;

Note

The Acme user-defined role does confer any object-level privileges. This role simply permits a user to reference the objects owned by the role without owner qualification. Joe and Sally do not have any extra privileges because of the Acme role. The Acme role has not been explicitly granted any special privileges. The Admin user has implicit privilege to look at tables like Salaries because it created the tables and has the SYS_AUTH_DBA_ROLE system role. So, Joe and Sally still get an error executing either of the following statements:

SELECT * FROM Acme.Salaries;
SELECT * FROM Salaries;

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