SQL Remote User's Guide
SQL Remote Administration
Managing SQL Remote 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 database can have only one publisher. You can find out who the publisher of an Adaptive Server Anywhere database is at any time in Sybase Central by opening the Users & Groups folder.
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.
You can grant PUBLISH permissions on an Adaptive Server Anywhere database from Sybase Central. You must connect to the database as a user with full system or database administrator permissions.
To create a new user as the publisher (Sybase Central)
In the left pane, select the Users & Groups folder.
From the File menu, choose New
The User Creation wizard appears.
Follow the instructions in the wizard. Ensure that the user has a password and is granted Remote DBA authority; this enables the user ID to run the Message Agent.
Click Finish to create the user.
In the Users & Groups folder, right-click the user you just created and choose Change to Publisher from the popup menu.
To make an existing user the publisher (Sybase Central)
In the Users & Groups folder, right-click a user and choose Change to Publisher from the popup menu.
You can also revoke PUBLISH permissions from Sybase Central.
To revoke PUBLISH permissions (Sybase Central)
In the Users & Groups folder, right-click the user who has granted PUBLISH permissions and choose Revoke Publisher from the popup menu.
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
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
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, 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.