User's Manual

Chapter 11. Administering SQL Remote for Adaptive Server
Anywhere
The Message Agent and replication security
In the tutorials in the previous chapter, the Message Agent was run using a
user ID with DBA permissions. The operations in the messages are carried
out from the user ID specified in the Message Agent connection string; by
using the user ID DBA, you can be sure that the user has permissions to
make all the changes.
In many situations, distributing the DBA user ID and password to all remote
database users is an unacceptable practice for security and data privacy
reasons. SQL Remote provides a solution that enables the Message Agent to
have full access to the database in order to make any changes contained in
the messages without creating security problems.
A special permission, REMOTE DBA, has the following properties:
♦ No distinct permissions when not connected from the Message
Agent A user ID granted REMOTE DBA authority has no extra
privileges on any connection apart from the Message Agent. Therefore,
even if the user ID and password for a REMOTE DBA user is widely
distributed, there is no security problem. As long as the user ID has no
permissions beyond CONNECT granted on the database, no one can use
this user ID to access data in the database.
♦ Full DBA permissions from the Message Agent When connecting
from the Message Agent, a user ID with REMOTE DBA authority has
full DBA permissions on the database.
Using REMOTE DBA
permission
A suggested practice is to grant REMOTE DBA authority at the
consolidated database to the publisher and to each remote user. When the
remote database is extracted, the remote user becomes the publisher of the
remote database, and is granted the same permissions they were granted on
the consolidated database, including the REMOTE DBA authority which
enables them to use this user ID in the Message Agent connection string.
Adopting this procedure means that there are no extra user IDs to administer,
and each remote user needs to know only one user ID to connect to the
database, whether from the Message Agent (which then has full DBA
authority) or from any other client application (in which case the REMOTE
DBA authority grants them no extra permissions).
Granting REMOTE DBA
permission
You can grant REMOTE DBA permissions to a user ID named dbremote as
follows:
GRANT REMOTE DBA
TO dbremote
IDENTIFIED BY dbremote
243