Privileges on Objects Associated with a UDR

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

A routine can reference the following objects:

• Tables and columns
• UDTs
• Other routines executed by the routine

In the course of routine execution, the owner of the routine, not the user who runs the routine, owns any unqualified objects that the routine creates. The database server verifies that the objects exist and that the UDR owner has the necessary privileges to access them. The user who executes the UDR runs with the privileges of the owner of the UDR.

The following example shows an SPL procedure called promo() that creates two tables, hotcatalog and libby.mailers:

CREATE PROCEDURE promo()

   CREATE TABLE hotcatalog
   (
      catlog_num INTEGER
      cat_advert VARCHAR(255, 65)
      cat_picture BLOB
   ) PUT cat_picturein sb1;

   CREATE TABLE libby.maillist
   (
      cust_num INTEGER
      interested_in SET(catlog_num INTEGER)
   );
END PROCEDURE;

Suppose user tony executes the CREATE PROCEDURE statement to register the SPL promo() procedure. User marty executes the promo() procedure with an EXECUTE PROCEDURE statement, which creates the table hotcatalog. Because no owner name qualifies table name hotcatalog, the routine owner (tony) owns hotcatalog. By contrast, the qualified name libby.maillist identifies libby as the owner of maillist.