SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

In some cases, all users of a database system might need to invoke an SPJ. For example, the SPJ
named MONTHLYORDERS determines the number of product orders during a given month. This
SPJ does not handle sensitive information or modify data and might be useful to anyone within the
company. Therefore, the database administrator (the super ID), acting on behalf of the SPJ owner,
grants the EXECUTE privilege on MONTHLYORDERS to PUBLIC, meaning all present and future
user IDs:
GRANT EXECUTE
ON samdbcat.sales.monthlyorders
TO PUBLIC;
After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset of
users. You must revoke the privilege from PUBLIC and then grant the privilege to specific users,
excluding users who should not have the privilege.
Granting Privileges on Referenced Database Objects
If the SPJ operates on a database object, the user ID that invokes the SPJ must have the appropriate
privileges on that database object.
For example, users with the EXECUTE privilege on the SPJ named ADJUSTSALARY, which selects
data from and updates a table, must have the SELECT and UPDATE privileges on the SQL/MX
table named EMPLOYEE. The database administrator (the super ID), acting on behalf of the object
owner, grants these access privileges to regional HR directors:
GRANT SELECT, UPDATE (salary)
ON TABLE samdbcat.persnl.employee
TO "HR.MGRNA", "HR.MGREU"
WITH GRANT OPTION;
The HR director of North America then grants these access privileges to department managers:
GRANT SELECT, UPDATE (salary)
ON TABLE samdbcat.persnl.employee
TO "MKG.RAYMOND", "RD.MCKAY", "CORP.GREEN";
84 Managing SPJs in NonStop SQL/MX