Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

The ROLE.PAYROLLA role cannot revoke the EXECUTE or WITH GRANT OPTION privilege
from the ROLE.PAYROLLB role because it was the SPJ owner (or creator) who granted those
privileges.
The SPJ owner (or creator) can revoke the WITH GRANT OPTION privilege on ADJUSTSALARY
from any user with this privilege. In this example, the SPJ owner (or creator) revokes the WITH
GRANT OPTION privilege from the ROLE.PAYROLLA role:
REVOKE GRANT OPTION FOR EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.PAYROLLA";
The SPJ owner (or creator) can also revoke the EXECUTE privilege from any user with this
privilege and from any dependent privileges by using the CASCADE option. In this example,
the SPJ owner (or creator) revokes the EXECUTE privilege from the ROLE.PAYROLLA role and
from the regional department managers to whom the ROLE.PAYROLLA role granted privileges:
REVOKE GRANT OPTION FOR EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.PAYROLLA"
CASCADE;
For SPJs on which all users (that is, PUBLIC) have privileges, you can revoke privileges from
PUBLIC but not from one or more specific users. For example, this statement revokes the
EXECUTE privilege on the SPJ named TOTALPRICE from all users (that is, PUBLIC):
REVOKE EXECUTE
ON PROCEDURE sales.totalprice
FROM PUBLIC;
To revoke privileges by using DB Admin, see “Using DB Admin to Revoke Privileges on SPJs”
(page 57).
Using Script Files to Grant and Revoke Privileges
Consider keeping your SPJ statements in script files. That way, you can quickly and easily grant
or revoke privileges to the SPJs, as needed.
Script File for Granting Privileges
You can use another or the same script file to grant privileges on a series of SPJs. For example,
the script file, grantprocs.sql, contains a series of GRANT EXECUTE and GRANT statements:
?SECTION GrantSalesProcs
GRANT EXECUTE
ON sales.monthlyorders
TO PUBLIC;
GRANT SELECT
ON TABLE sales.orders
TO PUBLIC;
?SECTION GrantPersnlProcs
GRANT EXECUTE
ON PROCEDURE persnl.adjustsalary
TO "ROLE.PAYROLLA", "ROLE.PAYROLLB"
WITH GRANT OPTION;
GRANT SELECT, UPDATE(salary)
ON TABLE persnl.employee
TO "ROLE.PAYROLLA", "ROLE.PAYROLLB"
WITH GRANT OPTION;
To grant privileges on the SPJs, run the script file in the NCI interface:
Using Script Files to Grant and Revoke Privileges 53