This section describes the role and usage of the Replication Server Maintenance User.
To update replicated data, Replication Server logs in to the replicate data server as the Maintenance User. The Database Owner (or the System Administrator) must grant to the Maintenance User the permissions required to insert, delete, and update rows in replicated tables, and to execute replicated stored procedures. In an Adaptive Server replicate database, Sybase Central or rs_init creates the user ID for the Replication Server Maintenance User and adds the user to the replicate database.
The Maintenance User ID and password are defined to Replication Server with the Replication Server create connection command for the replicate database. In Adaptive Server, Sybase Central or the rs_init program executes this command automatically. If you change the password for the Maintenance User ID in the data server, you can use Sybase Central or the Replication Server alter connection command to change the password for the Replication Server connection.
The Replication Server Maintenance User must also have permission to access the rs_lastcommit and rs_info system tables in the replicate database, and any stored procedures that use those tables.
Neither Sybase Central nor rs_init grants database permissions to the Maintenance User for user tables and stored procedures. You must grant database permissions on replicated tables and stored procedures before you can replicate transactions for replicated tables or replicate executions of the replicated stored procedures. For each table replicated in the database, and for each stored procedure executed due to replication, you must execute the following grant command:
grant all on table_name to maint_user
Alternatively, you can assign the Maintenance User ID (maint_user) to a Database Administrator role, if that role has the required authority on all replicate objects.
When the Replication Agent connects to a Replication Server connection, the Replication Agent requests the user ID of the Maintenance User and validates that the user ID exists in the primary database. This validation requires that the Maintenance User ID defined in any Replication Server connection be valid for the database the connection represents, regardless of whether that connection is for primary transactions only, replicate transactions only, or both.
The Replication Agent does not log in to the primary database using the Maintenance User ID. Other than validating that the user ID exists, the only reference the Replication Agent makes to the Maintenance User ID is to filter out primary database transactions created by the Maintenance User.
The Replication Agent filters out Maintenance User transactions to avoid having a transaction applied more than once to the primary database. In a bidirectional replication scheme, replication can occur both to and from the same database (a single database having both a primary and a replicate role). When a primary transaction is applied to a replicate database, the applying user ID is the Maintenance User for the replicate database. A Replication Agent scanning transactions at the replicate database must ignore the transactions applied by the Replication Server Maintenance User to prevent those transactions from being sent back and applied to the primary database.
The Replication Agent accesses the database using a user ID defined at the primary database (or for DB2, a user ID that can access the DB2 log files). This user ID is not the same as the Maintenance User defined in the Replication Server connection. The Replication Agent user ID used to access the primary database has a different role and purpose than the Maintenance User defined to apply replicated transactions.
There may also be another user ID defined to the Replication Agent that is used to administer the Replication Agent. This user ID is also separate from the Replication Server Maintenance User that applies replicate transactions.
A Replication Agent can have knowledge of three different users:
A user ID defined at the primary database, which the Replication Agent uses to log in to the primary data server and manipulate primary replication objects or read the database transaction log.
A user ID that can log in to the Replication Agent and issue Replication Agent commands and configure Replication Agent parameters.
A Maintenance User ID, defined at the primary database and recorded in the primary Replication Server connection. The Replication Agent validates this user ID on behalf of the Replication Server, and the Replication Agent can be configured to ignore transactions that are created by this user ID.
In general, using a DirectConnect database gateway does not change the use or purpose of the Replication Server Maintenance User. The only difference is that Adaptive Server provides some utilities that automatically create and assign permissions to the Maintenance User (for example, rs_init). With non-Sybase databases, the Maintenance User ID must be defined and granted the appropriate permissions.
In a Sybase replication system, there are several unique user IDs required. Sybase recommends that you provide a unique user ID for each role and component combination in your environment.
Each component (database, Replication Server, Replication Agent, and DirectConnect) must have some user ID defined to maintain or administer that component. Typically, maintenance tasks involve changing configuration parameters and access for the component “server.”
For Sybase products, the default component System Administrator user ID is typically sa. While it may be tempting to leave all of the components with an sa user defined, you may grant access to more components and more authority than intended or prudent. Do not confuse a component System Administrator user with a Replication Server connection Maintenance User. The Replication Server Maintenance User has authority to apply database transactions at a replicate database. It does not require authority to maintain the configuration of a replication system component.
Each Sybase Replication Agent requires a user ID in the primary database that can modify primary tables and procedures marked for replication and scan log files. This user ID does not perform the same function as the Replication Server Maintenance User ID, which is responsible for applying replicate transactions in a replicate database.
Following are the requirements for the Replication Server Maintenance User:
A Maintenance User ID must be created in every primary and replicate database in a replication system.
The Maintenance User ID must be identified in the Replication Server create connection command for every primary and replicate database in a replication system.
For replicate databases, the Maintenance User ID must have authority to apply replicate transactions and update any Replication Server tables or functions installed at the replicate database (for example, rs_info and rs_lastcommit).
