Type-Level Privileges

• Usage privilege, which controls authorization to use a user-defined data type
• Under privilege, which controls the authorization to define a named row type as a supertype in an inheritance hierarchy

Usage Privileges for User-Defined Types

To control who can use an opaque type, distinct type, or named row type, specify the Usage privilege on the data type. The Usage privilege allows the DBA or owner of the type to restrict a user's ability to assign a data type to a column, program variable (or table or view for a named row type), or assign a cast to the data type. The Usage privilege is granted to public automatically when a data type is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Usage privilege on a data type is granted to the owner of the data type.

To limit who can use an opaque, distinct, or named row type, you must first revoke the Usage privilege for public and then specify the names of the users to whom you want to grant the Usage privilege. For example, to limit the use of a data type named circle to a group of users, you might execute the following statements:

REVOKE USAGE ON circle
   FROM PUBLIC;

GRANT USAGE ON circle
   TO dawns, stevep, terryk, camber;

Under Privileges for Named Row Types

For named row types, you can grant or revoke the Under privilege, which controls whether users can assign a named row type as the supertype of another named row type in an inheritance hierarchy. The Under privilege is granted to public automatically when a named row type is created (except in ANSI-compliant databases). In an ANSI-compliant database, the Under privilege on a named row type is granted to the owner of the type.

To restrict certain users' ability to define a named row type as a supertype in an inheritance hierarchy, you must first revoke the Under privilege for public and then specify the names of the users to whom you want to grant the Under privilege. For example, to specify that only a limited group of users can use the named row type person_t as a supertype in an inheritance hierarchy, you might execute the following statements:

REVOKE UNDER ON person_t
   FROM PUBLIC;

GRANT UNDER ON person_t
   TO howie, jhana, alison

For information about how to use the UNDER clause to create named row types in an inheritance hierarchy, see Type Inheritance.