User's Guide

Part III. Using SQL Anywhere

Chapter 28. SQL Remote Administration

Users of a database involved in SQL Remote replication are identified by one of the following sets of permissions:

• PUBLISH A single user ID in a database is identified as the publisher for the database. All outgoing SQL Remote messages, including both publication updates and receipt confirmations, are identified by the publisher user ID. The publisher is the user who has been granted PUBLISH permissions on the database, and can be found by selecting the special constant CURRENT PUBLISHER or by opening the SQL Remote folder in Sybase Central. Every database in a SQL Remote setup must have a single publisher user ID, as every database in a SQL Remote setup sends messages.
• REMOTE All recipients of messages from the current database, or senders of messages to the current database, who are immediately lower on the SQL Remote hierarchy than the current database must be granted REMOTE permissions. The GRANT REMOTE statement identifies a user ID, as well as a message system and an address appropriate to that message system to where the messages are sent. It also gives the frequency at which messages are sent.
• CONSOLIDATE At most one user ID may be granted CONSOLIDATE permissions in a database. CONSOLIDATE permissions identifies a database immediately above the current database in a SQL Remote setup. Each database can have only one consolidated database directly above it. The permissions in a three-level SQL Remote setup are summarized in the following diagrams. In each diagram one database is gray; the diagram shows the permissions that need to be granted in that database for the user ID representing each of the other databases. The phrase "No permissions" means that the database is not granted any permissions in the gray database.

Granting the appropriate PUBLISH and CONSOLIDATE permissions at remote databases is done automatically by the database extraction utility.

Granting and revoking PUBLISH permissions

When a database sends a message, a user ID representing that database is included with the message to identify its source to the recipient. The GRANT PUBLISH statement identifies a user ID as the publisher user ID of the database.

A publisher is required even for read-only remote databases within a replication system, as even these databases send confirmations to the consolidated database to maintain information about the status of the replication. The GRANT PUBLISH statement for remote databases is carried out automatically by the database extraction utility.

Granting PUBLISH permissions

PUBLISH permissions are granted using the GRANT PUBLISH statement:

     GRANT PUBLISH TO userid ;

The userid is a user with CONNECT permissions on the current database.

For example, the following statement grants PUBLISH permissions to user S_Beaulieu:

     GRANT PUBLISH TO S_Beaulieu

You can grant PUBLISH permissions from Sybase Central. You should connect to the database as a user with DBA permissions.

1. Click the SQL Remote folder on the left panel.
2. Double click Set Publisher, and select a user from the list.
3. Click OK to set the selected user as the database publisher.

Revoking PUBLISH permissions

The REVOKE PUBLISH statement revokes the PUBLISH permissions from the current publisher:

     REVOKE PUBLISH FROM userid

You can revoke PUBLISH permissions from the Sybase Central utility:

1. Right-click the current publisher.
2. Click Revoke Publish on the popup menu.

Notes on PUBLISH permissions

• Only one user ID on a database can hold PUBLISH permissions at one time. That user ID is called the publisher for the database.
• To see the publisher user ID in Sybase Central, open the SQL Remote folder; the publisher is shown on the right panel.
• To see the publisher user ID outside Sybase Central, use the CURRENT PUBLISHER special constant. The following statement retrieves the publisher user ID:

       SELECT CURRENT PUBLISHER
  

• If PUBLISH permissions is granted to a user ID with GROUP permissions, it is not inherited by members of the group.
• PUBLISH permissions carry no authority except to identify the publisher in outgoing messages.
• For messages sent from the current database to be received and processed by a recipient, the publisher user ID must have REMOTE or CONSOLIDATE permissions on the receiving database.
• The publisher user ID for a database cannot also have REMOTE or CONSOLIDATE permissions on that database. This would identify them as both the sender of outgoing messages and a recipient of such messages.
• A column with the DEFAULT CURRENT PUBLISHER setting will be filled in with the user ID of the current publisher. This is useful in ensuring unique primary keys throughout a replication system.

    For more information, see the section "Designing publications".

• Changing the user ID of a publisher at a remote database will cause serious problems for any subscriptions that database is involved in, including loss of information. You should not change a remote database publisher user ID unless you are prepared to resynchronize the remote user from scratch.
• Changing the user ID of a publisher at a consolidated database while a SQL Remote setup is operating will cause serious problems, including loss of information. You should not change the consolidated database publisher user ID unless you are prepared to close down the SQL Remote setup and resynchronize all remote users.

Granting and revoking REMOTE and CONSOLIDATE permissions

REMOTE and CONSOLIDATE permissions are very similar. Each database receiving messages from the current database must have an associated user ID on the current database that is granted one of REMOTE or CONSOLIDATE permissions. This user ID represents the receiving database in the current database.

Databases directly below the current database on a SQL Remote hierarchy are granted REMOTE permissions, and the at most one database above the current database in the hierarchy is granted CONSOLIDATE permissions. The GRANT REMOTE and GRANT CONSOLIDATE statements identify the message system and address to which replication messages must be sent.

CONSOLIDATE permissions must be granted even at read-only remote databases, for the consolidated database, as receipt confirmations are sent back from the remote databases to the consolidated database. The GRANT CONSOLIDATE statement at remote databases is executed automatically by the database extraction utility.

Granting REMOTE and CONSOLIDATE permissions

Each remote database must be represented by a single user ID in the consolidated database. This user ID must be granted REMOTE permissions to identify their user ID and address as a subscriber to publications.

In the remote database, the publish and subscribe user IDs are inverted. The subscriber (remote user) in the consolidated database becomes the publisher in the remote database. The publisher of the consolidated database becomes a subscriber to publications from the remote database, and is granted CONSOLIDATE permissions.

The GRANT REMOTE statement identifies a user ID as a remote user, specifies a message type to use for exchanging messages with this user ID, and provides an address to where messages are to be sent. For example, the following statement grants remote permissions to user S_Beaulieu, using a MAPI e-mail system and with MAPI mail address Beaulieu, S:

     GRANT REMOTE TO S_Beaulieu
     TYPE mapi ADDRESS 'Beaulieu, S'
     SEND AT '22:00'

The SEND AT clause is one of three alternatives that specify when messages should be sent to the remote user. The three alternatives are:

SEND EVERY 'HH: MM' A frequency can be specified in hours and minutes. When any user with SEND EVERY set is sent messages, all users with the same frequency are sent messages also. For example, all remote users who receive updates every twelve hours are sent updates at the same times, rather than being staggered. This reduces the number of times the transaction log has to be processed. You should use as few unique frequencies as possible.

SEND AT 'HH: MM' A time of day, in hours and minutes. Updates are started daily at the specified time. It is more efficient to use as few distinct times as possible than to stagger the sending times. Also, choosing times when the database is not busy minimizes interference with other users.

Default setting (no SEND clause) If any user has no SEND AT or SEND EVERY clause, the Message Agent sends messages every time it is run, and then stops. It runs in batch mode. SQL Remote is not intended for up-to-the-minute replication. Frequencies of less than ten minutes are not recommended.

The following statement grants remote user status to an office in Wakefield, U.K., using a user ID wakefield_office. The statement assumes that both offices are using MAPI e-mail systems as interfaces to internet e-mail, and that the e-mail system has an alias wakefield for the internet e-mail address.

     GRANT REMOTE TO wakefield_office
     TYPE mapi ADDRESS 'wakefield'
     SEND EVERY '02:00'

The following statement grants REMOTE permissions using a file system to user S_Beaulieu.

     GRANT REMOTE TO S_Beaulieu
     TYPE file ADDRESS 'Beaulieu'
     SEND AT '22:00'

The messages are placed in a subdirectory Beaulieu of the current working directory for the Message Agent. If the SQLREMOTE environment variable has been set, or if the Directory setting in the FILE message control parameters (held in the registry of INI files) has been set, the messages are stored under the named directory.

You can add a remote user to a database using Sybase Central:

1. Click the Users & Groups folder.
2. Select the user or users you wish to grant REMOTE permissions to, right-click to display a popup menu, and click Set Remote.
3. For each user, select the message type from the list, enter an address, choose the frequency of sending messages, and click OK to make the user a remote user.

At each remote database, the consolidated database must be granted CONSOLIDATE permissions. When you produce a remote database by running the database extraction utility, the GRANT CONSOLIDATE statement is executed automatically at the remote database.

The following statement grants CONSOLIDATE permissions to the hq_user user ID:

     GRANT CONSOLIDATE TO hq_user
     TYPE mapi ADDRESS 'hq_address'

There is no SEND clause in this statement. Messages will be sent to the consolidated database every time the Message Agent is run.

Revoking REMOTE and CONSOLIDATE permissions

By analogy with other permissions, REMOTE and CONSOLIDATE permissions can be revoked from a user using the REVOKE statement. The following statement revokes REMOTE permission from user S_Beaulieu.

     REVOKE REMOTE FROM S_Beaulieu

DBA authority is required to revoke REMOTE or CONSOLIDATE access.

Revoking remote user permissions also drops any subscriptions for that user.