Lesson 2: Granting PUBLISH and REMOTE permissions at the consolidated database

Every database in a SQL Remote system requires a publisher, which is a unique user with PUBLISH permission. All outgoing SQL Remote messages, including publication updates and receipt confirmations, are identified by their publisher. Every database in a SQL Remote system sends receipt confirmations.

 Create the publisher for the consolidated database (Interactive SQL)

  1. If you are not currently connected to the consolidated database (hq), run the following command:

    dbisql -c "UID=DBA;PWD=sql;SERVER=server_hq;DBF=c:\tutorial\hq.db"

  2. Execute the following statement to create the user hq_user that has CONNECT and PUBLISH permissions:

    CREATE USER hq_user IDENTIFIED BY hq_pwd;
GRANT CONNECT TO hq_user IDENTIFIED BY hq_pwd;
GRANT PUBLISH TO hq_user;

  3. Execute the following statement to check the publishing user ID of the database:

    SELECT CURRENT PUBLISHER;

A database, such as a consolidated database, that sends messages to other databases must specify which remote databases it sends messages to. To specify these remote databases on the consolidated database, grant REMOTE permission to the publishers of the remote databases. REMOTE permission identifies databases that receive messages from the current database.

 Grant remote permission

  • Execute the following statements to create the remote user field_user with the password field_pwd that has CONNECT and REMOTE permissions: 

    CREATE USER field_user IDENTIFIED BY field_pwd;
GRANT CONNECT TO field_user IDENTIFIED BY field_pwd;
GRANT REMOTE TO field_user 
TYPE file 
ADDRESS 'field';
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1