Granting privileges for using distinct types
Stored procedures can pass parameters that have a distinct type as a data type. When a distinct type is used as a stored procedure parameter, users who create the stored procedure need the USAGE privilege on the distinct type.
About this task
When you create a distinct type, you, as the owner of that type, implicitly have the USAGE privilege on the type. You also have the EXECUTE privilege on the associated cast functions. If other users want to create stored procedures that pass a parameter with that distinct type, you need to explicitly grant the USAGE privilege to them.
Procedure
To grant privileges for using distinct types:
Issue the GRANT statement with the USAGE ON TYPE clause,
and specify the name of the distinct type.
- You can grant privileges for using distinct types to an authorization
ID. For example, assume that you want the user whose authorization
ID is PAOLORW to be able to use the US_DOLLARS distinct type, which
you created. Specifically, this user needs to create a stored procedure
that passes a parameter with this data type. To grant this privilege,
you can issue this statement:
GRANT USAGE ON TYPE US_DOLLARS TO PAOLORW;
- You can grant privileges for using distinct types to a role.
For example, if you want the role named ADMINISTRATOR to be able to
use the US_DOLLARS distinct type, you can issue this statement:
GRANT USAGE ON TYPE US_DOLLARS TO ROLE ADMINISTRATOR;