Setting up access rights

Local users must have appropriate access rights (in both the master database and the user’s local replica database) to the tables they use for transactions and execute rights to the procedures they execute. Note that if procedures are used to perform synchronization functions in the replica database, the local user who has created the procedure must be mapped to a master user.

The DBA of the master database should grant to master users

Note Once access rights are granted, they take effect when the user who is granted the rights logs on to the database. If the user is already logged on to the database when the rights are granted, the rights take effect only if the user:

In the applicable replica database, specify which is the currently active master user by mapping the replica user id with the master user id by using the ALTER USER SET MASTER command.

When setting up access rights, you can use the following advanced replication SQL commands:

CREATE USER username IDENTIFIED BY password
GRANT rolename TO username
GRANT [SELECT | UPDATE | INSERT | DELETE] ON tablename TO
username
GRANT EXECUTE ON procedure_name TO username

Read the solidDB^® SQL Guide for details.

To grant users access to publications, use:

For more information, see Granting REFRESH access.

To map a replica user id to a master user id:

Read the solidDB^® SQL Guide for details.

To grant access rights on a publication to a local user, user role (created with the create role statement), or all users, use the GRANT REFRESH statement in the master database. The syntax is:

To revoke access rights on a publication to a local user, user role (created with the create role statement), or all users, use the REVOKE REFRESH statement in the master database. The syntax is:

When a local user saves a statement of a transaction in the replica, the transaction in the statement is labeled with the current master user’s username. When the transaction is re-executed in the master database, it uses the access rights defined for the master user.

When the master encounters a user access violation during transaction propagation, it terminates the execution of the synchronization message. This ensures that a local replica user is not able to execute any unauthorized statements in the master database.

In a multi-master environment, you can map a single user id to a different master user in each catalog. When you change the active replica catalog using the SET CATALOG command, the current master user changes automatically. For example, in a replica database assume there is one local user. This user is mapped to the CALENDARUSER master user of the calendar application and the NEWSUSER master user of the news application. The SET CATALOG command is used to set the current catalog to either CALENDAR or NEWS. If CALENDAR catalog is set, then the current master user is automatically set to the CALENDARUSER master user. Similarly, if NEWS is set, the current master user is set to NEWSUSER.

If mapping is not defined, the first advanced replication data synchronization operation (for example, SAVE or MESSAGE statement) returns the “no active master user” error.

A user on the master who defines a publication must have read and write access rights to the tables referenced by that publication.

Note that subscriptions are executed in the master database using the master username of the creator of the message that contained the REFRESH publication clause.

To use a table that is involved in synchronization, the local user must have rights to the actual subscription tables in the replica database, and the corresponding (mapped) master user must have subscribe access rights to the publication in the master database. When subscription rows are inserted (or deleted) in a replica, solidDB^® verifies that the subscriber has INSERT and DELETE rights on the tables.

The user who defines a publication also has the right to drop that publication.

For information about granting publications access rights, see Setting up access rights.

When a new advanced replication replica database is created, the SYS_SYNC_USERS table of the new database is empty (contains no data). To register the new replica database with the master database and to initially populate the table with data requires a username that is from the master database and that has registration rights.

You can provide registration rights for a master user in the master database by designating the user with the SYS_SYNC_REGISTER_ROLE or the SYS_SYNC_ADMIN_ROLE using the GRANT rolename TO user command.

You must provide this registration username and password to the replica site that wants to register with the master. This allows each replica site to explicitly set the registration user at the replica with the following command:

Since the username resides in the master database, this command allows the registration user to explicitly register the replica. The SYS_SYNC_USERS replica table can then be populated with the public SYS_USERS information from the master database as part of the replica registration process.

When the replica has successfully executed registration, execute the following statement:

Otherwise, if SET SYNC USER username is active and a user saves statements, propagates, refreshes, or registers to a publication, the following error message is returned:

Note The SET SYNC USER command is used for replica registration only. Aside from registration, all other synchronization operations require a valid master user ID that has been downloaded to the replica database using the SYNC_CONFIG task. If you want to designate a different master user for a replica, you must map the replica ID on the replica database to the master ID on the master database. For details, see Mapping replica user id with master user id.