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.

Objects referenced by a routine can include:

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

When a routine is run, the effective privilege is defined as the union of:

The privileges of the user running the routine,
The privileges that the owner has with the GRANT option.

By default, the database administrator has all the privileges in a database with the GRANT option. Therefore, users executing routines that are owned by database administrators can select from all of the tables in a given database.

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.