Consultor Eletrônico

SUMMARY:

This Knowledge Base Solution contains information about the GRANT EXECUTE statement on stored procedures. This information is missing from the SQL-92 Guide and Reference but will be added in a later version of the documentation.

EXPLANATION:

In order for SQL-92 users to be able to execute a Java Stored Procedure, you will need to grant appropriate privileges to the users on the Stored Procedure.

Some important points about privileges:

a) To create a stored procedure, a user must have RESOURCE or DBA
privileges.

b) The DBA privilege entitles a user to execute any stored procedure.

c) The DBA privilege entitles a user to drop any stored procedure.

d) The owner of a stored procedure is given EXECUTE privilege on that
procedure at creation time, by default.

e) The privileges on a procedure can be granted to another user or to
public either by the owner of that procedure or by the DBA.

f) Stored procedures are executed with the definer's rights, not the
invoker's. In other words, when a procedure is being executed on
behalf of a user with EXECUTE privilege on that procedure, for the
objects that are accessed by the procedure, the procedure owner's
privileges are checked and not the user's. This enables a user to
execute a procedure successfully even when the user does not have
the privileges to directly access the objects that are accessed by
the procedure, so long as the user has EXECUTE privilege on the
procedure.

SOLUTION:

To grant privileges to SQL-92 users on the Stored Procedure, use the following syntax:

GRANT EXECUTE ON StoredJavaProcedureName() TO { username [, username ] , ... | PUBLIC };


Reference to Written Documentation:

Progress SQL-92 Guide and Reference

Progress Knowledge Base Solutions:
19142, "procedure not found (7678) when using Java Stored Procedure"
21181, "9.1X: Java Setting to Make Stored Procedures_ Triggers Work"