To run a stored procedure (locally or remotely), you must have execute privileges on that procedure, see GRANT.
When a procedure is called locally, it is executed with the privileges of the caller. When a procedure is called remotely, the method of determining the user whose privileges are used depends on whether the calling server is the master or replica server, whether user details are specified, and whether a user mapping exists between the master and replica server:
▪ Procedure called from replica server, master user specified: Before calling the remote stored procedure, the SET SYNC USER statement is executed on the replica server to supply the ID and password of the user on the master database that should run the remote stored procedure, see SET SYNC USER.
Each time a remote stored procedure is called, the master server tries to execute the procedure by using the credentials provided. The specified user must exist in the master server, and have appropriate access rights to the database and EXECUTE privilege on the called procedure.
▪ Procedure called from replica server, no master user specified but user mapping exists: The servers attempt to determine the user on the master server that corresponds to the user on the replica server.
The replica server sends the following information to the master server when calling a remote procedure:
▪ Name of the master (SYS_SYNC_MASTERS.NAME).
▪ Replica id (SYS_SYNC_MASTERS.REPLICA_ID).
▪ Master user id that corresponds to the user id of the replica user who called the procedure. The user must be mapped to the corresponding master user, see Setting up access rights.
Note that this method of selecting the master user id is the same as the method used when a replica database refreshes data — the replica database checks the SYS_SYNC_USERS table to find the master user who is mapped to the current replica user.
▪ Procedure called from master server, no replica user specified but user mapping exists: The servers attempt to determine the user on the replica server that corresponds to the user on the master server.
The master server sends the following information to the replica server when calling a remote procedure:
▪ Name of the master (SYS_SYNC_REPLICAS.MASTER_NAME).
▪ Replica id (SYS_SYNC_REPLICAS.ID).
▪ User name of the caller.
▪ User id of the caller.
When the replica server receives the master user id, the replica user that is mapped to the master id is identified. Since more than one replica user can be mapped to a single master user, the server executes the stored procedure with the privileges of the first matching replica user that it finds.
Before a master server can call a stored procedure on a replica server, the master server must know the connect string of the replica server. If a replica server allows calls from a master server, then the replica server must define the connect string information by using the Synchronizer.ConnectStrForMaster parameter, see Synchronizer section. The connect string is provided when the replica server forwards any message to the master server and replaces any previous value (if the new value is different).
It is also possible to inform the master server of the replica server connect string by using the SET SYNC CONNECT statement, see SET SYNC CONNECT. This method is useful if the master server needs to call the replica server but the replica server has not yet provided the connect string to the master server (that is, the replica server has not yet forwarded any message to the master server).