6 Managing Oracle Objects

System Privileges for Object Types

Oracle defines the following system privileges for object types:

• CREATE TYPE enables you to create object types in your own schema

• CREATE ANY TYPE enables you to create object types in any schema

• ALTER ANY TYPE enables you to alter object types in any schema

• DROP ANY TYPE enables you to drop named types in any schema

• EXECUTE ANY TYPE enables you to use and reference named types in any schema

• UNDER ANY TYPE enables you to create subtypes under any non-final object types

• UNDER ANY VIEW enables you to create subviews under any object view

The RESOURCE role includes the CREATE TYPE system privilege. The DBA role includes all of these privileges.

Schema Object Privileges

Two schema object privileges apply to object types:

• EXECUTE on an object type enables you to use the type to:

  • Define a table.

  • Define a column in a relational table.

  • Declare a variable or parameter of the named type.

  EXECUTE lets you invoke the type's methods, including the constructor.

  Method execution and the associated permissions are the same as for stored PL/SQL procedures.

• UNDER enables you to create a subtype or subview under the type or view on which the privilege is granted

  The UNDER privilege on a subtype or subview can be granted only if the grantor has the UNDER privilege on the direct supertype or superview WITH GRANT OPTION.

The phrase WITH HIERARCHY OPTION grants a specified object privilege on all subobjects of the object. This option is meaningful only with the SELECT object privilege granted on an object view in an object view hierarchy. In this case, the privilege applies to all subviews of the view on which the privilege is granted.

Using Types in New Types or Tables

In addition to the permissions detailed in the previous sections, you need specific privileges to:

• Create types or tables that use types created by other users.

• Grant use of your new types or tables to other users.

You must have the EXECUTE ANY TYPE system privilege, or you must have the EXECUTE object privilege for any type you use in defining a new type or table. You must have received these privileges explicitly, not through roles.

If you intend to grant access to your new type or table to other users, you must have either the required EXECUTE object privileges with the GRANT option or the EXECUTE ANY TYPE system privilege with the option WITH ADMIN OPTION. You must have received these privileges explicitly, not through roles.

Example: Privileges on Object Types

Assume that three users exist with the CREATE SESSION and RESOURCE roles: USER1, USER2, and USER3.

USER1 performs the following DDL in the USER1 schema:

CONNECT user1/user1
CREATE TYPE type1 AS OBJECT ( attr1 NUMBER );
/
CREATE TYPE type2 AS OBJECT ( attr2 NUMBER );
/
GRANT EXECUTE ON type1 TO user2;
GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;

USER2 performs the following DDL in the USER2 schema:

CONNECT user2/user2
CREATE TABLE tab1 OF user1.type1;
CREATE TYPE type3 AS OBJECT ( attr3 user1.type2 );
/
CREATE TABLE tab2 (col1 user1.type2 );

The following statements succeed because USER2 has EXECUTE on USER1's TYPE2 with the GRANT option:

GRANT EXECUTE ON type3 TO user3;
GRANT SELECT ON tab2 TO user3;

However, the following grant fails because USER2 does not have EXECUTE on USER1.TYPE1 with the GRANT option:

GRANT SELECT ON tab1 TO user3 -- incorrect statement;

USER3 can successfully perform the following actions:

CONNECT user3/user3
CREATE TYPE type4 AS OBJECT (attr4 user2.type3);
/
CREATE TABLE tab3 OF type4;

Privileges on Type Access and Object Access

While object types only make use of EXECUTE privilege, object tables use all the same privileges as relational tables:

• SELECT lets you access an object and its attributes from the table.

• UPDATE lets you modify attributes of objects in the table.

• INSERT lets you add new objects to the table.

• DELETE lets you delete objects from the table.

Similar table and column privileges regulate the use of table columns of object types.

Selecting columns of an object table does not require privileges on the type of the object table. Selecting the entire row object, however, does.

Consider the following schema and queries in Example 6-1:

CREATE TYPE emp_type as object (
  eno     NUMBER,
  ename   VARCHAR2(36));
/
CREATE TABLE emp OF emp_type;
GRANT SELECT on emp TO user1;
SELECT VALUE(e) FROM emp e;
SELECT eno, ename FROM emp;

For either query, Oracle checks the user's SELECT privilege for the emp table. For the first query, the user needs to obtain the emp_type type information to interpret the data. When the query accesses the emp_type type, Oracle checks the user's EXECUTE privilege.

Execution of the second query, however, does not involve named types, so Oracle does not check type privileges.

Additionally, using the schema described in "Example: Privileges on Object Types", USER3 can perform the following queries:

SELECT t.col1.attr2 from user2.tab2 t;

SELECT t.attr4.attr3.attr2 FROM tab3 t;

Note that in both selects by USER3, USER3 does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT option.

Oracle checks privileges on the following requests, and returns an error if the requestor does not have the privilege for the action:

• Pinning an object in the object cache using its REF value causes Oracle to check SELECT privilege on the object table containing the object and EXECUTE privilege on the object type.

  
  

  See Also: Oracle Call Interface Programmer's Guide for tips and techniques for using OCI program effectively with objects

  
  

• Modifying an existing object or flushing an object from the object cache, causes Oracle to check UPDATE privilege on the destination object table. Flushing a new object causes Oracle to check INSERT privilege on the destination object table.

• Deleting an object causes Oracle to check DELETE privilege on the destination table.

• Invoking a method causes Oracle to check EXECUTE privilege on the corresponding object type.

Oracle does not provide column level privileges for object tables.

Completing Incomplete Types

When all the types that an incomplete type refers to have been created, there is no longer any need for the incomplete type to remain incomplete, and you should complete the declaration of the type. Completing the type recompiles it and enables the system to release various locks.

You must complete an incomplete object type as an object type: you cannot complete an object type as a collection type (a nested table type or an array type). The only alternative to completing a type declaration is to drop the type.

You must also complete any incomplete types that Oracle creates for you because you did not explicitly create them yourself. The example in the preceding section explicitly creates department as an incomplete type. If department were not explicitly created as an incomplete type, Oracle would create it as one so that the employee type can compile (with errors). You must complete the declaration of department as an object type whether you or Oracle declared it as an incomplete type.

Manually Recompiling a Type

If a type was created with compilation errors, and you attempt an operation on it, such as creating tables or inserting rows, you may receive an error. You need to recompile type typename before attempting the operation. To manually recompile a type, execute an ALTER TYPE typename COMPILE statement. After you have successfully compiled the type, attempt the operation again.

Type Dependencies of Substitutable Tables and Columns

A substitutable table or column of type T is dependent not only on T but on all subtypes of T as well. This is because a hidden column is added to the table for each attribute added in a subtype of T. The hidden columns are added even if the substitutable table or column contains no data of that subtype.

So, for example, a persons table of type person_typ is dependent not only on person_typ but also on the person_typ subtypes student_typ and part_time_student_typ.

If you attempt to drop a subtype that has a dependent type, table, or column, the DROP TYPE statement returns an error and aborts. For example, trying to drop part_time_student_typ will raise an error because of the dependent persons table.

If dependent tables or columns exist but contain no data of the type that you want to drop, you can use the VALIDATE keyword to drop the type. The VALIDATE keyword causes Oracle to check for actual stored instances of the specified type and to drop the type if none are found. Hidden columns associated with attributes unique to the type are removed as well.

For example, the first DROP TYPE statement in the following example fails because part_time_student_typ has a dependent table (persons). But if persons contains no instances of part_time_student_typ (and no other dependent table or column does, either), the VALIDATE keyword causes the second DROP TYPE statement to succeed:

-- Following generates an error due to presence of Persons table 
DROP TYPE part_time_student_typ -- incorrect statement;
-- Following succeeds if there are no stored instances of part_time_student_typ
DROP TYPE part_time_student_typ VALIDATE;

Creating a Type Synonym

You create a type synonym with a CREATE SYNONYM statement. For example, these statements create a type typ1 and then create a synonym for it:

-- For the synonym examples, CREATE SYNONYM and CREATE PUBLIC SYNONYM 
-- are granted to USER1
CREATE TYPE typ1 AS OBJECT (x number);
/
CREATE SYNONYM syn1 FOR typ1;

Synonyms can be created for collection types, too. The following example creates a synonym for a nested table type:

CREATE TYPE typ2 AS TABLE OF NUMBER;
/
CREATE SYNONYM syn2 FOR typ2;

You create a public synonym by using the PUBLIC keyword:

CREATE TYPE shape AS OBJECT ( name VARCHAR2(10) );
/
CREATE PUBLIC SYNONYM pub_shape FOR shape;

The REPLACE option enables you to have the synonym point to a different underlying type. For example, the following statement causes syn1 to point to type typ2 instead of the type it formerly pointed to:

CREATE OR REPLACE SYNONYM syn1 FOR typ2;

Using a Type Synonym

You can use a type synonym anywhere that you can refer to a type. For instance, you can use a type synonym in a DDL statement to name the type of a table column or type attribute. In Example 6-3, synonym syn1 is used to specify the type of an attribute in type typ3:

CREATE TYPE typ1 AS OBJECT (x number);
/
CREATE SYNONYM syn1 FOR typ1;

CREATE TYPE typ3 AS OBJECT ( a syn1 );
/

The next example shows a type synonym syn1 used to call the constructor of the object type typ1, for which syn1 is a synonym. The statement returns an object instance of typ1:

SELECT syn1(0) FROM dual;

In the following example, syn2 is a synonym for a nested table type. The example shows the synonym used in place of the actual type name in a CAST expression:

SELECT CAST(MULTISET(SELECT eno FROM USER3.EMP) AS syn2) FROM dual;

Type synonyms can be used in the following kinds of statements:

• DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE

• DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT

CREATE TYPE typ3 AS OBJECT ( a syn1 );
/

CREATE SYNONYM syn4 FOR typ1;

DROP SYNONYM syn4;

RENAME syn1 TO syn3 -- invalid statement;

-- Following uses public synonym pub_shape
CREATE TABLE shape_tab ( c1 pub_shape );
-- Following is not allowed
CREATE TABLE pub_shape ( c1 NUMBER ) -- invalid statement;