Developing and Implementing Applications, concluded

Security for Abstract Datatypes

The examples in the previous sections assumed that the same user owned the ADDRESS_TY datatype and the CUSTOMER table. What if the owner of the datatype is not the table owner? What if another user wants to create a datatype based on a datatype you have created? In the development environment, you should establish guidelines for the ownership and use of abstract datatypes.

For example, what if the account named DORA owns the ADDRESS_TY datatype, and the user of the account named GEORGE tries to create a PERSON_TY datatype? GEORGE executes the following command:

create type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  ADDRESS_TY);
/

If GEORGE does not own the ADDRESS_TY abstract datatype, Oracle will respond to this create type command with the following message:

Warning: Type created with compilation errors.

The compilation errors are caused by problems creating the constructor method when the datatype is created. Oracle cannot resolve the reference to the ADDRESS_TY datatype because GEORGE does not own a datatype with that name.

GEORGE will not be able to create the PERSON_TY datatype (which includes the ADDRESS_ TY datatype) unless DORA first grants him EXECUTE privilege on her type. The following listing shows this grant:

grant EXECUTE on ADDRESS_TY to George;

NOTE

You must also grant EXECUTE privilege on the type to any user who will perform DML operations on the table.

Now that the proper grants are in place, GEORGE can create a datatype that is based on DORA’s ADDRESS_TY datatype:

create or replace type PERSON_TY as object (Name     VARCHAR2(25),
 Address  Dora.ADDRESS_TY);
/

GEORGE’s PERSON_TY datatype will now be successfully created. However, using datatypes based on another user’s datatypes is not trivial. For example, during insert operations, you must fully specify the name of the owner of each type. GEORGE can create a table based on his PERSON_ TY datatype (which includes DORA’s ADDRESS_TY datatype), as shown in the following listing:

create table GEORGE_CUSTOMERS
(Customer_ID NUMBER,
 Person   PERSON_TY);

If GEORGE owned the PERSON_TY and ADDRESS_TY datatypes, an insert into CUSTOMER would use the following format:

insert into GEORGE_CUSTOMERS values (1,PERSON_TY('SomeName',
   ADDRESS_TY('StreetValue','CityValue','ST',11111)));

This command will not work. During the insert, the ADDRESS_TY constructor method is used, and DORA owns it. Therefore, the insert command must be modified to specify DORA as the owner of ADDRESS_TY. The following example shows the corrected insert statement, with the reference to DORA shown in bold:

insert into GEORGE_CUSTOMERS values
(1,PERSON_TY('SomeName', 
   Dora.ADDRESS_TY('StreetValue','CityValue','ST',11111)));

NOTE

In Oracle Database 10g, you can use a synonym for another user’s datatype.

Whenever possible, limit the ability to create abstract datatypes to those users who will own the rest of the application schema objects.

NOTE

When you create a synonym, Oracle does not check the validity of the object for which you are creating the synonym. If you use create synonym x for y, for example, Oracle does not check to make sure that “y” is a valid object name or valid object type. The validation of that object’s accessibility via synonyms is only checked when the object is accessed via the synonym.

In a relational-only implementation of Oracle, you grant the EXECUTE privilege on procedural objects, such as procedures and packages. Within the object-relational implementation of Oracle, the EXECUTE privilege is extended to cover abstract datatypes as well. The EXECUTE privilege is used because abstract datatypes can include methods—PL/SQL functions and procedures that operate on the datatypes. If you grant someone the privilege to use your datatype, you are granting the user the privilege to execute the methods you have defined on the datatype. Although DORA did not yet define any methods on the ADDRESS_TY datatype, Oracle automatically creates constructor methods that are used to access the data. Any object (such as PERSON_TY) that uses the ADDRESS_TY datatype uses the constructor method associated with ADDRESS_TY.

You cannot create public types, and you cannot create public synonyms for your types. Therefore, you will need to either reference the owner of the type or create the type under each account that can create tables in your database. Neither of these options is a simple solution to the problem of datatype management.