When a transaction is executed on any server, that transaction must be executed with appropriate privileges (for example, INSERT, DELETE, UPDATE, and so on privileges on the tables). When a transaction is propagated from a replica server to the master server, the transaction must execute with appropriate privileges on the master. Executing with appropriate privileges on the replica is not sufficient to guarantee that the transaction will execute with appropriate privileges on the master.
To ensure that propagated transactions can be executed on the master, you must map a replica user to a corresponding master user who has the appropriate privileges. For example, you might map the user kathy_on_replica1 to the user kathy_on_master, if the user kathy_on_master has the appropriate privileges.
When the users on the master change, you may need to update the "mapping" information about the replicas; otherwise, your replica users might map to master users that are no longer appropriate or that no longer exist. To download the updated information to applicable replicas, you need to execute the MESSAGE APPEND SYNC_CONFIG command in the replicas. Once the updated master userinformation has been downloaded, you then need to re-map replica user ids to the appropriate master user ids using the ALTER USER SET MASTER statements (refer to Mapping replica user ID with master user ID below).
The MESSAGE APPEND SYNC_CONFIG command itself requires appropriate privileges. When you create a new replica, that replica has no privileges and thus cannot connect to the master. You need to create a replica registration user to initially populate the SYS_SYNC_USERS table with the list of master users from the master database; from then on, the list of master users can be downloaded as needed from the master database.
Updating master users for advanced replication operations
To update master users in a replica:
From the replica, subscribe user information from the master database in a separate message using the following command:
The sync_config_arg defines the search pattern of the user names that are returned from the master database to the replica. If you want all names to be sent to the replica, specify the SQL wildcard % as the input argument.
Master users control all access to the data synchronization functions of solidDB®. To allow a replica database to synchronize its data with the master database, the replica must download master user information from the master database and map one or more local user ids to a master user id.
Mapping replica user id with master user id
To map a replica user id to a master user id, you use the ALTER USER SET MASTER statement. When you use the ALTER USER SET MASTER statement, you provide the master user names and passwords for the local users that you want to map to master users. The same local user can be mapped to multiple master users.
After you have completed the mapping, when a local user logs into a replica database, solidDB® checks to see whether that local user is mapped to any master user id. If no mapping is specified, by default, solidDB® looks for the same user id and password in the master and replica. Thus, if mapping is not used, the user id and password in both the master and replica must be the same.
Replica table SYS_SYNC_USERS can be updated with the latest master usernames using the MESSAGE APPEND SYNC_CONFIG command. For an illustration of this concept, see Advanced replication user access rights.
Setting public and private users
Database Administrators can alter users in the SYS_USERS table of the master database to designate those users as private or public. If the PRIVATE option is set
for a user, this user's id and password are never sent to the replica during a subscription of the publication to the replica.
Even if a PRIVATE user matches a specified subscription request in the MESSAGE APPEND SYNC_CONFIG command, as long as that user is set for PRIVATE, the user's information stays in the master's SYS_USERS table. Only PUBLIC users are downloaded from master to replica to fulfill a SYS_SYNC_USERS table subscription request. By default, a user is set for PUBLIC. For details on setting users to public and private, read the solidDB® SQL Guide.
To change a user from public to private or vice-versa, use the command:
ALTER USER SET { PRIVATE | PUBLIC }
Note There is no way to set a user as private for some replicas and public for others. Users are designated as either public or private throughout the system.
You should grant all users with administrative rights (DBA) to PRIVATE. This provides an extra measure of security by preventing a DBA's password from ever being sent to a replica and becoming public. If the DBA password became exposed and you needed to restore security, all replicas of the system would need to be dropped and re-created after the password was changed in the master database.
Other users may also be set to PRIVATE if those users are not needed in replicas.
Note If a replica builds messages or executes transactions using a user who is private in a master database, then the operation in the master (when receiving messages or when executing transactions) fails with a security error.
Determining access rights of transactions and refresh commands
Synchronization messages are labeled with the master username of the creator of the message. solidDB® uses the master username to specify the account under which the message is executed. All subscriptions are executed using this account. Each transaction uses the master user who saved the statements in the replica.