Extending Informix Dynamic Server 2000 Creating an Opaque Data Type

Once you have created the opaque data type and registered it with the database, use the GRANT statement to define the following privileges on this data type:

• Privileges on the use of the opaque data type
• Privileges on the support functions of the opaque data type

Important: The GRANT statement registers the privileges on the opaque data type and support functions in the current database. For users of another database to have access to the opaque data type and its support functions, you must run the GRANT statement when this second database is the current database.

To create a new opaque data type within a database, you must have the Resource privilege on the database. The CREATE OPAQUE TYPE statement creates a new opaque data type with the Usage privilege granted to the owner of the opaque data type and the DBA. To use the opaque data type in an SQL statement, you must have the Usage privilege. The owner can grant the Usage privilege to other users with the USAGE ON TYPE clause of the GRANT statement.

The database server checks for the Usage privilege whenever the opaque-type name appears in an SQL statement (such as a column data type in CREATE TABLE or a cast data type in CREATE CAST). The database server does not check for the Usage privilege when an SQL statement:

• accesses columns of the opaque data type.

The Select, Insert, Update, and Delete table-level privileges determine access to a column.

• invokes a user-defined routine with the opaque data type as an argument.

The Execute routine privilege determines access to a user-defined routine.

For example, the following GRANT statement assigns the Usage privilege on the circle opaque data type to the user dexter:

GRANT USAGE ON TYPE circle TO dexter

The sysxtdtypeauth system catalog table stores data type-level privileges. This table contains privileges for each opaque and distinct data type that is defined in the database. The table contains one row for each set of privileges granted.

To register a support function within a database, you must have the Resource privilege on the database. The CREATE FUNCTION statement registers the new support function with the Execute privilege granted to the owner of the support function and the DBA. Such a function is called an owner-privileged function.

To execute a support function in an SQL statement, the user must have the Execute privilege. Usually, the default privilege is adequate for support functions that are implicit casts because implicit casts should not generally be called within SQL statements. Support functions that are explicit casts might have the Execute privilege granted so that users can call them explicitly. The owner grants the Execute privilege to other users with the EXECUTE ON clause of the GRANT statement.

The sysprocauth system catalog table stores routine-level privileges. This table contains privileges for each user-defined routine and therefore for all support functions that are defined in the database. The table contains one row for each set of privileges granted.