Neoview SQL Reference Manual (R2.3)

Considerations for REVOKE EXECUTE
Authorization and Availability Requirements
You can revoke the EXECUTE privilege from a role only if you have previously granted it to the
role. If the privilege does not exist, the system returns a warning.
To revoke privileges by using the CASCADE option, you must be the SPJ owner (that is, the
schema owner or the creator of the stored procedure).
You cannot revoke the EXECUTE privilege from a specific role if the EXECUTE privilege is
granted to PUBLIC.
Examples of REVOKE EXECUTE
• This REVOKE EXECUTE statement issued by the SPJ owner, ROLE.DBA, fails because a
dependent privilege exists for the roles to whom the role ROLE.HR granted the EXECUTE
privilege on ADJUSTSALARY:
REVOKE EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.HR" RESTRICT;
*** ERROR[1014] Privileges were not revoked. Dependent privilege descriptors still exist.
--- SQL operation failed with errors.
• This REVOKE EXECUTE statement issued by the SPJ owner, ROLE.DBA, does not fail
because no dependent privileges exist for this set of roles, which have only the EXECUTE
privilege on ADJUSTSALARY:
REVOKE EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.PAYROLL", "ROLE.FINANCE" RESTRICT;
• To revoke the WITH GRANT OPTION privilege on ADJUSTSALARY from the role ROLE.HR,
the SPJ owner, ROLE.DBA, issues this REVOKE EXECUTE statement:
REVOKE GRANT OPTION FOR EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.HR";
The role ROLE.HR no longer has the WITH GRANT OPTION privilege but still has the
EXECUTE privilege on ADJUSTSALARY.
• To revoke the EXECUTE privilege on ADJUSTSALARY from the role ROLE.HR, the SPJ
owner, ROLE.DBA, issues this REVOKE EXECUTE statement with the CASCADE option:
REVOKE EXECUTE
ON PROCEDURE persnl.adjustsalary
FROM "ROLE.HR" CASCADE;
140 SQL Statements