Granting or revoking authorizations to call federated procedures

The administrator of the federated database must grant other users the required authorizations to call the federated procedures.

Before you begin

The user that calls the federated procedure must have a valid user mapping from the federated server to the data source. The remote user ID must have the authorization on the data source that is equivalent to the EXECUTE authorization on the federated server. A user can be granted EXECUTE authorization on the federated procedure. But if the user authorization on the data source is not equivalent to the EXECUTE authorization on the federated server, calls to the data source procedure fail.

The authorization ID for the GRANT statement must have at least one of the following authorities:

The WITH GRANT OPTION for EXECUTE on the federated procedure
SYSADM or DBADM authority

Procedure

Issue the GRANT statement from the command line to specify the authorization privileges.

Example 1:

To grant the EXECUTE privilege on all procedures in the BHATIA schema, including any procedures that are created in the future, to users in the HR_DEPT group, use the following statement:

GRANT EXECUTE ON PROCEDURE 
 BHATIA.* TO HR_DEPT

Example 2:

To grant the EXECUTE privilege on the PROC1 procedure to user ZELLER and give the this person the ability to grant the EXECUTE privilege on this procedure to others, use the following statement:

GRANT EXECUTE ON PROCEDURE PROC1 
 TO ZELLER 
	WITH GRANT OPTION

Example 3:

To grant the EXECUTE privilege to user ERFAN on the PROC2 procedure that was created with a specific name of MY_PROC2, use the following statement:

GRANT EXECUTE ON SPECIFIC 
 PROCEDURE MY_PROC2 TO ERFAN