Neoview Guide to Stored Procedures in Java (R2.5)

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;
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:
obey c:\grantprocs.sql (GrantSalesProcs)
Script File for Revoking Privileges
You can use another or the same script file to revoke privileges on a series of SPJs. For example,
the script file, revokeprocs.sql, contains a series of REVOKE EXECUTE and REVOKE
statements:
?SECTION RevokeSalesProcs
REVOKE EXECUTE
Using Script Files to Grant and Revoke Privileges 49