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-16
Regenerating CREATE PROCEDURE Statements
REVOKE EXECUTE Statements in an OBEY Command File
You can use another or the same OBEY command file to revoke privileges on a series
of SPJs. For example, the OBEY command file, revokeprocs.sql, contains a series
of REVOKE EXECUTE statements:
To revoke privileges on the SPJs, issue the OBEY command in MXCI:
OBEY revokeprocs.sql "REVOKE SALES SPJs";
Regenerating CREATE PROCEDURE Statements
Use the SHOWDDL command to regenerate the CREATE PROCEDURE statements
of registered SPJs in a new or existing OBEY command file:
1. In an MXCI session, enter the LOG command so that MXCI writes the output of the
SHOWDDL command to a log file:
LOG createprocs.sql;
2. In the same MXCI session, enter the SHOWDDL command for specific SPJs in the
database:
SHOWDDL PROCEDURE samdbcat.sales.dailyorders;
SHOWDDL PROCEDURE samdbcat.sales.monthlyorders;
...
For the syntax of the SHOWDDL command, see the SQL/MX Reference Manual.
?SECTION "REVOKE SALES SPJs"
REVOKE EXECUTE
ON PROCEDURE samdbcat.sales.monthlyorders
FROM PUBLIC;
REVOKE SELECT
ON TABLE samdbcat.sales.orders
FROM PUBLIC;
?SECTION "REVOKE PERSNL SPJs"
REVOKE GRANT OPTION FOR EXECUTE
ON PROCEDURE samdbcat.persnl.adjustsalary
FROM "HR.MGRNA", "HR.MGREU"
CASCADE;
REVOKE EXECUTE
ON PROCEDURE samdbcat.persnl.adjustsalary
FROM "HR.MGRNA", "HR.MGREU"
CASCADE;
REVOKE SELECT, UPDATE(salary)
ON TABLE samdbcat.persnl.employee
FROM "HR.MGRNA", "HR.MGREU"
CASCADE;