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

GRANT EXECUTE
ON sales.totalprice
TO PUBLIC;
After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset
of roles. You must revoke the privilege from PUBLIC and then revoke the privilege from specific
roles.
Granting Privileges on Referenced Database Objects
If the SPJ operates on a database object, the roles that invoke the SPJ must have the appropriate
privileges on that database object.
For example, roles 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 table named EMPLOYEE. The SPJ owner (or creator) grants these access privileges to the
Payroll directors:
GRANT SELECT, UPDATE (salary)
ON TABLE persnl.employee
TO "ROLE.PAYROLLA", "ROLE.PAYROLLB"
WITH GRANT OPTION;
One of the Payroll directors then grants these access privileges to the regional department
managers:
GRANT SELECT, UPDATE (salary)
ON TABLE persnl.employee
TO "ROLE.MGMTRGN1", "ROLE.MGMTRGN2", "ROLE.MGMTRGN3";
All users with the EXECUTE privilege on the SPJ named TOTALPRICE are not required to have
any privileges on database tables because that SPJ does not access any database tables.
The types of SQL statements in the underlying SPJ method, such as SELECT, UPDATE, DELETE,
and INSERT, indicate which privileges are required for the referenced database objects.
For the syntax of the GRANT statement, see the Neoview SQL Reference Manual.
Revoking Privileges on an SPJ
Use the REVOKE EXECUTE or REVOKE statement to remove the EXECUTE or WITH GRANT
OPTION privilege on an SPJ from specific user roles. In a REVOKE statement, specify ALL
PRIVILEGES to revoke the EXECUTE privilege on an SPJ. For the syntax of the REVOKE
EXECUTE and REVOKE statements, see the Neoview SQL Reference Manual.
If you own the SPJ, you can revoke the EXECUTE and WITH GRANT OPTION privileges on
the SPJ from any roles to whom you granted those privileges. If you are not the owner of the
SPJ, you must have been granted the WITH GRANT OPTION privilege to revoke privileges from
other roles, and you can revoke privileges only from other roles to whom you have granted
privileges. For example, the ROLE.PAYROLLA role can revoke the EXECUTE privilege on
ADJUSTSALARY from one or more sets of regional department managers to whom the
ROLE.PAYROLLA role granted privileges. In this example, the ROLE.PAYROLLA role revokes
the EXECUTE privilege from the Region 2 department managers:
REVOKE EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.MGMTRGN2";
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:
48 Granting Privileges for Executing SPJs