Privileges

There are a handful of system-level privileges associated with object types, summarized here:

CREATE [ ANY ] TYPE

Create, alter, and drop object types and type bodies. ANY means in any schema.

CREATE [ ANY ] VIEW

Create and drop views, including object views. ANY means in any schema.

ALTER ANY TYPE

Use ALTER TYPE facilities on types in any schema.

EXECUTE ANY TYPE

Use an object type from any schema for purposes including instantiating, executing methods, referencing, and dereferencing.

UNDER ANY TYPE

Create a subtype in one schema under a type in any other schema.

UNDER ANY VIEW

Create a subview in one schema under a view in any other schema.

There are three kinds of object-level privileges on object types: EXECUTE, UNDER, and DEBUG. It is also important to understand how the conventional DML privileges apply to object tables and views.

The EXECUTE privilege

If you want your associate Joe to use one of your types in his own PL/SQL programs or tables, you can grant the EXECUTE privilege to him:

GRANT EXECUTE on catalog_item_t TO joe;

If Joe has the privilege needed to create synonyms and is running Oracle9i Release 2 or later, he will be able to create a synonym:

CREATE SYNONYM catalog_item_t FOR scott.catalog_item_t;

and use it as follows:

CREATE TABLE catalog_items OF catalog_item_t;

and/or:

DECLARE an_item catalog_item_t;

Joe can also use a qualified reference to the type scott.catalog_item_t.

If you refer to an object type in a stored program and grant EXECUTE privilege on that program to a user or role, having EXECUTE on the type is not required, even if the program is defined using invoker rights (described in Chapter 20). Similarly, if a user has a DML privilege on a view that has an INSTEAD OF trigger for that DML operation, that user doesn't need explicit EXECUTE privileges if the trigger refers to the object type because triggers run under the definer rights model. However, the EXECUTE privilege is required by users who need to run anonymous blocks that use the object type.

The UNDER privilege

The UNDER privilege gives the grantee the right to create asubtype. You can grant it as follows:

GRANT UNDER ON image_t TO scott;

For a schema to be able to create a subtype, the supertype must be defined using invoker rights (AUTHID CURRENT_USER).

This privilege can also grant the recipient the right to create a subview:

GRANT UNDER ON images_v TO scott;

An apparent bug prevented this from working on the Oracle version where I tested it, however.