Managing SQL Remote permissions

Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 10. SQL Remote Administration

Managing SQL Remote permissions

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 that database. All outgoing SQL Remote messages, including both publication updates and receipt confirmations, are identified by the publisher user ID. 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.
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.

Information about these permissions are held in the SQL Remote system tables, and are independent of other database permissions.

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. This user ID is 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 Adaptive Server Anywhere databases is carried out automatically by the database extraction utility.

Granting and revoking PUBLISH permissions from Sybase Central

You can grant PUBLISH permissions from Sybase Central. You must connect to the database as a user with full system or database administrator permissions.

To set the publisher for a database:

Open the SQL Remote folder.
Double click Set Publisher, and select a user from the list.
Click OK to set the selected user as the database publisher.

You can also revoke PUBLISH permissions from Sybase Central.

To revoke PUBLISH permissions from Sybase Central:

Open the SQL Remote folder. The current publisher is displayed in the right pane.
Right-click the current publisher.
Click Revoke Publish on the popup menu.

Granting and revoking PUBLISH permissions [Adaptive Server Anywhere]

For Adaptive Server Anywhere, 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

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

REVOKE PUBLISH FROM userid

Granting and revoking PUBLISH permissions [Adaptive Server Enterprise]

For Adaptive Server Enterprise, PUBLISH permissions are granted using the sp_publisher procedure:

sp_publisher 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:

exec sp_publisher 'S_Beaulieu'
go

The database is set to have no publisher by executing the sp_publisher procedure with no argument:

exec sp_publisher
go

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 for an Adaptive Server Anywhere database outside Sybase Central, use the CURRENT PUBLISHER special constant. The following statement retrieves the publisher user ID:
```
SELECT CURRENT PUBLISHER
```
To see the publisher user ID for an Adaptive Server Enterprise database outside Sybase Central, use the following statement:
```
SELECT name
FROM sysusers
WHERE uid = ( SELECT user_id
               FROM sr_publisher )
go
```
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.
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.

Setting REMOTE and CONSOLIDATE permissions

For Adaptive Server Anywhere, the GRANT REMOTE and GRANT CONSOLIDATE statements identify the message system and address to which replication messages must be sent.

For Adaptive Server Enterprise, the sp_grant_remote procedure sets REMOTE permissions, and the sp_grant_consolidate procedure sets CONSOLIDATE permissions.

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

Granting REMOTE 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.

Granting REMOTE permissions accomplishes several tasks:

It identifies a user ID as a remote user.
It specifies a message type to use for exchanging messages with this user ID.
It provides an address to where messages are to be sent.
It indicates how often messages should be sent to the remote user.

Granting REMOTE permissions is also referred to as adding a remote user to the database.

Sybase Central example

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

To add a new user to the database, as a remote user:

Open the Remote Users folder in the SQL Remote folder.
Double-click Add Remote User, and follow the instructions in the wizard.

To make an existing user a remote user:

Open the SQL Remote folder, so the Remote Users folder is displayed in the left pane.
Open the Users and Groups folder, so that the user you want to make a remote user is displayed in the right pane.
Drag the user icon in the right pane to the Remote Users folder in the SQL Remote folder on the left pane. The Make User a Remote User window is displayed.
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.

Adaptive Server Anywhere example

The following statement grants remote permissions to user S_Beaulieu, with the following options:

Use an SMTP e-mail system
Send messages to e-mail address s_beaulieu@acme.com:
Send message daily, at 10 pm.

GRANT REMOTE TO S_Beaulieu
TYPE smtp
ADDRESS 's_beaulieu@acme.com'
SEND AT '22:00'

Adaptive Server Enterprise example

The following statement grants remote permissions to user S_Beaulieu with the following options:

Use the file-sharing system to exchange messages.
Place messages in the directory beaulieu under the address root directory.

The address root directory (for both Adaptive Server Anywhere and Adaptive Server Enterprise) is indicated by the SQLREMOTE environment variable, if it is set. Alternatively, it is indicated by the Directory setting in the FILE message control parameters (held in the registry or INI file).
Send messages every twelve hours:

exec sp_grant_remote 'S_Beaulieu',
   'file',
   'beaulieu',
   'SEND EVERY',
   '12:00'
go

Selecting a send frequency

There are three alternatives for the setting the frequency with which messages are sent. The three alternatives are:

SEND EVERY 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 Adaptive Server Anywhere transaction log or Adaptive Server Enterprise stable queue has to be processed. You should use as few unique frequencies as possible.

SQL Remote is not intended for up-to-the-minute replication. Frequencies of less than ten minutes are not recommended.
SEND AT 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.

Granting CONSOLIDATE permissions

In the remote database, the publish and subscribe user IDs are inverted compared to the consolidated database. 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.

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.

Adaptive Server Anywhere example

The following Adaptive Server Anywhere statement grants CONSOLIDATE permissions to the hq_user user ID, using the VIM e-mail system:

GRANT CONSOLIDATE TO hq_user
TYPE vim 
ADDRESS 'hq_address'

There is no SEND clause in this statement, so the default is used and messages will be sent to the consolidated database every time the Message Agent is run.

Adaptive Server Enterprise example

The following Adaptive Server Enterprise statement grants CONSOLIDATE permissions to user hq_user:

exec sp_grant_consolidate 'hq_user', address
go

Revoking REMOTE and CONSOLIDATE permissions

A user can be removed from a SQL Remote installation by revoking their REMOTE permissions. Revoking remote user permissions also drops any subscriptions for that user.

Revoking permissions from Sybase Central

You can revoke REMOTE permissions on both Adaptive Server Enterprise and Adaptive Server Anywhere from Sybase Central.

To revoke REMOTE permissions from Sybase Central :

Open the SQL Remote folder.
Open the Remote Users folder.
Right-click the user whose permission you wish to revoke, and select Revoke Remote from the popup menu.

Revoking permissions in Adaptive Server Anywhere

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 permissions in Adaptive Server Enterprise

REMOTE permissions can be revoked from a user using the sp_revoke_remote procedure. This procedure takes a single argument, which is the user ID of the user. The following statement revokes REMOTE permission from user S_Beaulieu.

exec sp_revoke_remote 'S_Beaulieu'
go

Assigning permissions in multi-tier installations

Special considerations are needed for assigning permissions in multi-tier installations. The permissions in a three-level SQL Remote setup are summarized in the following diagrams. In each diagram one database is shaded; 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 shaded database.

The following picture shows SQL Remote permissions, as granted at the consolidated site of a three-tier installation.

The following picture shows SQL Remote permissions, as granted at an internal site of a three-tier installation.

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