Privileges on Objects Associated with a Routine

The database server checks the existence of any referenced objects and verifies that the user invoking the routine has the necessary privileges to access the referenced objects. For example, if a user executes a routine that updates data in a table, the user must have the Update privilege for the table or columns referenced in the routine.

Objects referenced by a routine include:

• Tables and columns
• User-defined data types
• Other routines executed by the routine

When the owner of a routine grants the Execute privilege, some privileges on objects automatically accompany the Execute privilege. A GRANT EXECUTE ON statement confers to the grantee any table-level privileges that the grantor received from a GRANT statement that contained the WITH GRANT keywords.

The owner of the routine, and not the user who runs the routine, owns the unqualified objects created in the course of executing the routine. For example, assume user howie registers an SPL routine that creates two tables, with the following SPL routine:

CREATE PROCEDURE promo()
. . .
   CREATE TABLE newcatalog 
   (
   catlog_num INTEGER
   cat_advert VARCHAR(255, 65)
   cat_picture BLOB
   ) ;
   CREATE TABLE dawn.mailers
   (
   cust_num INTEGER
   interested_in SET(catlog_num INTEGER)
   );
END PROCEDURE;

User julia runs the routine, which creates the table newcatalog. Because no owner name qualifies table name newcatalog, the routine owner (howie) owns newcatalog. By contrast, the qualified name dawn.maillist identifies dawn as the owner of maillist.