The ident creating a routine must, as is usual, have the appropriate access rights on the tables and other database objects referenced from the SQL statements in the routine. The creating ident must also have the right to create objects in the schema to which the routine is to belong (i.e. the ident must be the creator of the schema).
The right of the creator to access referenced database objects is verified when the
CREATE MODULEor the
CREATE PROCEDUREstatement is executed.
If an ident wishes to invoke a routine, that ident must have
EXECUTEprivilege on the routine.
Note: In order for the creator of a routine to grant EXECUTE privilege on the routine to another ident, the creator must have the WITH GRANT option in affect for all the access rights held on all the database objects referenced within the routine.
The above note is an important security point, because granting
EXECUTEprivilege on a routine is effectively granting appropriate access rights to the given ident on all the database objects referenced in the routine, therefore all those access rights must be held by the grantor with the
An ident may be granted
EXECUTEprivilege on a routine with the
WITH GRANToption and if this option is in affect, the ident may grant
EXECUTEprivilege on that routine to other idents.
Routines can be used as a security layer in the database. By having
EXECUTEprivilege on a routine granted, an ident only gets the right to perform the specific operations specified in the routine and not general access to the referenced database objects.
Note: It is not possible to grant EXECUTE privilege on a module, only on routines.
Mimer Information Technology AB
Phone: +46 18 780 92 00