GRANT PUBLISH statement [SQL Remote]

Grants publisher rights to a user ID. You must have the SYS_REPLICATION_ADMIN_ROLE system role to grant publisher rights.

Syntax

GRANT PUBLISH TO userid

Remarks

Each database in a SQL Remote installation is identified in outgoing messages by a user ID, called the publisher. The GRANT PUBLISH statement sets the publisher user ID associated with these outgoing messages. To change publishers, you must revoke publisher rights from the current publisher (REVOKE PUBLISH), and then grant them to the new publisher.

The database publisher can be determined by querying the special value CURRENT PUBLISHER as follows:

SELECT CURRENT PUBLISHER;

If there is no publisher, the value of CURRENT PUBLISHER is NULL.

The CURRENT PUBLISHER special value can be used as a default setting for columns. It is often useful to have a CURRENT PUBLISHER column as part of the primary key for replicating tables, as this configuration helps prevent primary key conflicts due to updates at more than one site.

To change the publisher, you must first drop the current publisher using the REVOKE PUBLISH statement, and then create a new publisher using the GRANT PUBLISH statement.

Executing this statement changes the value of the PUBLIC.db_publisher database option.

Privileges

You must have the SET ANY SYSTEM OPTION system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement sets the database publisher to user ID JohnS.

GRANT PUBLISH TO JohnS;

The following statements revoke database publisher from JohnS and grant it to IrisM.

REVOKE PUBLISH FROM JohnS;
GRANT PUBLISH TO IrisM;