Restricting Data Reads

The routine in the following example hides the SQL syntax from users, but it requires that users have the Select privilege on the customer table. If you want to restrict what users can select, write your routine to work in the following environment:

• You are the DBA of the database.
• The users have the Connect privilege to the database. They do not have the Select privilege on the table.
• You use the DBA keyword to create the SPL routine (or set of SPL routines).
• Your SPL routine (or set of SPL routines) reads from the table for users.

If you want users to read only the name, address, and telephone number of a customer, you can modify the procedure as the following example shows:

CREATE DBA PROCEDURE read_customer(cnum INT)
RETURNING CHAR(15), CHAR(15), CHAR(18);

DEFINE p_lname,p_fname CHAR(15);
DEFINE p_phone CHAR(18);

SELECT fname, lname, phone
   INTO p_fname, p_lname, p_phone
   FROM customer
   WHERE customer_num = cnum;

RETURN p_fname, p_lname, p_phone;

END PROCEDURE;