SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Managing SPJs in NonStop SQL/MX
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
6-3
Granting Privileges on Referenced Database
Objects
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";
All users with the EXECUTE privilege on the SPJ named MONTHLYORDERS must
have the SELECT privilege on the ORDERS table:
GRANT SELECT
ON TABLE samdbcat.sales.orders
TO PUBLIC;
The types of SQL statements in the underlying SPJ method, such as SELECT,
UPDATE, DELETE, and INSERT, should indicate which privileges, such as SELECT,
UPDATE, DELETE, and INSERT, are required for the referenced database objects.
For the syntax of the GRANT statement, see the SQL/MX Reference Manual. For more
information about granting privileges on SQL/MX database objects, see the SQL/MX
Installation and Management Guide. For information about securing SQL/MP database
objects, see the SQL/MP Installation and Management Guide.