When a stored procedure is called remotely, you must take into account the access rights — that is, does the caller have the right to execute this procedure on the remote server?
CASE 1: If the Sync user is set with the command SET SYNC USER
The caller sends the user name and password of the “sync user” to the remote server, and the remote server tries to execute the procedure using that user name and password. In this case, the username and password must exist in the remote server (that is, the server that the stored procedure will be executed on) and the user must have appropriate access rights to the database and the called procedure.
CASE 2: If the Sync user is not set
The caller sends the following information to the remote server when calling a remote procedure:
If the caller is the master and the remote server is the replica (M ? R):
▪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.
If the caller is the replica and the remote procedure is the master (R ? M):
▪Name of the master (SYS_SYNC_MASTERS.NAME).
▪Replica id (SYS_SYNC_MASTERS.REPLICA_ID).
▪Master user id (The same user id is used as when a replica refreshes data. There has to be a mapping from the local replica user to a master user in SYS_SYNC_USERS table.)
The following actions are performed in the called node:
If the remote node is a replica (M ? R):
▪Get the master id from table SYS_SYNC_MASTERS according to the master name received from the caller (master itself does not know its id in the replica). From the table SYS_SYNC_USERMAPS get the replica user ids according to master user name and master id. Select the first user that has access rights to the procedure.
▪If there are no matching rows in SYS_SYNC_USERMAPS, then get NAME and PASSWD from the table SYS_SYNC_USERS according to master id and master user name received from the caller and try to execute the procedure using them.
If the remote node is a master (R ? M)
▪Try to execute the procedure using the user id received from the replica.
If the replica allows calls from any master it should define its own connect string information in the solid.inifile, for example:
The replica sends that connect string automatically to the master when it forwards any message to the master. When the master receives the connect string from the replica, it replaces any previous value (if it differs).
The master can set the connect string to the replica (if the replica has not done any messaging and the master needs to call it and knows that the connect string has changed) using the following statement: