SQL Anywhere, distribute database user (how to replicate users and password to remote db)

[obrungot]

For a replicated database environment, what is the recommended approach to replicate database user, to ensure that a user created in a consolidated database, gets replicated to remote databases?

[VolkerBarth]

Late to the game: You may also consider this question from the SQL Anywhere Forum.

It basically uses the same (undocumented) approach as discussed here. 🙂

[chris_keating]

To add new users, you would use SQL Remote Passthrough Mode. Here is an example:

passthrough for subscription to p;
    grant connect to u1 identified by ...;
passthrough stop;

[obrungot]

Well, that table would then need to contain the password. I would really not do that. Is is possible to set the grant with a hashed string

GRANT CONNECT user1 identified by encrypted 'HASHED STRING'

If so, then I don't see the problem adding it to helper table.

[chris_keating]

Storing credentials plain text is certainly not something you would do in an implementation. One option using existing SQLA features would be to use ENCRYPT and DECRYPT functions on both the user id and password.

[obrungot]

Yes, but only encrypt/decrypt don't offer much security by itself. Is it not possible to grant with password string already hashed? Or must the hashing be performed by the database?

[chris_keating]

The only issue relates to the syntax you are proposing is undocumented and intended for use by the unload mechanism. As it is undocumented, there is no guarantee that the syntax will no be changed.

The password hash is SHA256. ENCRYPT offers several algorithm and format options.

[chris_keating]

PASSTHROUGH allows for operations to be defined at the consolidated to be sent to all remotes subscribed to a given publication or to the consolidated if initiated by a remote. If initiated by a remote, the operation is not sent to other remotes. For that requirement, you could use a table that is replicated that contains the operations and then using EXECUTE IMMEDIATE to run on each node or as a PASSTHROUGH at the consolidated once it replicates to that node.

[chris_keating]

PASSTHROUGH allows for operations to be defined at the consolidated to be sent to all remotes subscribed to a given publication or to the consolidated if initiated by a remote. If initiated by a remote, the operation is not sent to other remotes. For that requirement, you could use a table that is replicated that contains the operations and then using EXECUTE IMMEDIATE to run on each node or as a PASSTHROUGH at the consolidated once it replicates to that node.

[obrungot]

Hi Chris,

Yes, we've tried that approach. However, in an environment with one consolidated database, and two or more remote databases, we run in to a problem if the password is changed at the remote site.

Changes can only be performed at the consolidated database. We need to also to add/change users on the remote databases.

Scenario 1: Change password of existing user on remote database

1. A: GRANT CONNECT TO U1 IDENTIFIED BY HELLO (add and or

2. DB REMOTE triggers and adds user to remote databases.

3. B: GRANT CONNECT TO UI IDENFITIED BY HELLO123 (change password)

4. The change is replicated from database B to A, but database C will never get the new passord.

Scenario 2: Similar if you create a user on a remote database it will only be replicated to the consolidated database, not to the other remote.

1. B: GRANT CONNECT TO U2 IDENTIFIED BY QWERTY

2. DB REMOTE triggers and add user to database

Database C will never receive the GRANT message to add the user.