Predicated Privileges

There are several requirements for Replication Server support for Adaptive Server predicated privileges.

Adaptive Server 15.7 ESD #2 includes predicated privileges. Replication Servers in a mixed-version environment support predicated privileges.

To use predicated privileges in a replication system, the maintenance user on the replicate Replication Server must have nonpredicated privileges on the replicate tables and databases.

See Predicated Privileges in the Adaptive Server Enterprise 15.7 ESD #2 New Features Summary.

Stored Procedure Replication with Predicated Privileges

Executing a replicated stored procedure on the primary database by a user with predicated privileges for the DML statements in the procedure, may cause inconsistency between the primary and replicate data.

Suppose that you, as the Adaptive Server and Replication Server administrator, grant the update privilege on the accounts table to Marc:

grant update on accounts to Marc (1)
where owner = ‘Marc’

In addition, create the balance_limit stored procedure on the primary database and mark balance_limit for replication:

create procedure balance_limit
as
update accounts
set balance = 0
where balance < 0

When Marc executes balance_limit on the primary database, Adaptive Server applies the predicated privilege (1) that you defined earlier, and executes the resulting SQL statement, which is equivalent to:

update accounts Marc
set balance = 0
where balance < 0
and owner = ‘Marc’

Replication Server replicates the execution of balance_limit to the replicate database, using the maintenance user for the procedure execution on the accounts table. Since the maintenance user does not have any predicated privileges, the executed SQL statement at the replicate database is equivalent to:

update accounts Marc
set balance = 0
where balance < 0

In this example, the SQL statement executed at the primary database is different from the statement executed at the replicate database, resulting in an inconsistency between replicate and primary data.