Multiplex login management

With some exceptions, you can perform login management operations on any server in a multiplex and propagate them to other servers.

Table 12-4 shows operations that you can propagate.

**Table 12-4: Scope of multiplex login management**
Operation	Propagates From	Scope
Enable/disable login management	Write or query server	Multiplex
Add a user (sp_iqaddlogin or GRANT CONNECT)	Write server only¹	Multiplex
Drop a user (sp_iqdroplogin or REVOKE CONNECT)	Write server only¹	Multiplex
Change a user password	Write or query server	Multiplex
Modify password expiration	Write or query server	Multiplex
Lock user out of multiplex	Write or query server	Multiplex
Lock user out of a server²	Write or query server	Server
Set per-user connect limit³	Write or query server	Multiplex
Set global per-server connect limit²	Write or query server	Multiplex
Set per-server connect limit	Write or query server	Server
Set default password expiration	Write or query server	Multiplex
Set password expiration warning period	Write or query server	Multiplex
1. By default, disallowed on query server. Allowed only if database option MPX_LOCAL_SPEC_PRIV is set to the appropriate numeric value. For details, see “MPX_LOCAL_SPEC_PRIV option”in Sybase IQ Reference Manual. In this case, the query server allows the operation but does not propagate it. Changes are lost when the query server is synchronized. 2. The user locked setting and the per-user connection limit on a particular query server persist after synchronizing, unless SQL Remote was not running. Sybase recommends that you perform these changes on a write server to avoid this situation. 3. The per-user connection limit applies to each server in a multiplex. For example, if the limit is 10, the user may have 10 connections to one query server and another 10 to another query server.

DBA privileges are required for all of the operations in the table except changing a user password. Users can change their own passwords.

Table 12-3 lists procedures for managing logins.

Propagation of login management operations requires:

An active write server.
Operational and active SQL Remote processes.
Servers upgraded to Sybase IQ 12.7.

If the write server or SQL Remote processes are temporarily inactive, settings propagate only when they become active.

For example, you start a query server in single-node mode, perform login management operations, then restart it as a write server, all login activities propagate to query servers when they are synchronized. If, however, you restart the server as a query server instead, the write server overwrites all login changes at synchronization.

Any changes, such as a reset password, made on the query server when the write server or SQL Remote processes are temporarily down propagate once they come back up. However, when a write server is inactive for an extended period, then brought up, and query servers are synchronized, any changes made earlier on query servers are lost.

When login management is disabled, IQ continues to propagate settings to other servers, but does not perform checks like password expiration at login. GRANT and REVOKE propagate from the write server to other servers. GRANT CONNECT to change a user password also propagates from a query server to other servers. GRANT CONNECT to add a new user or REVOKE connect to drop a user will not propagate from a query server to other servers.

The database option MPX_LOCAL_SPEC_PRIV enables you to add a user to a query server, but Sybase IQ does not propagate such users. If the same user is added to the write server, the current user on the query server is replaced by the one on the write server.

If you drop a user from a write server and the user owns objects on a query server, the user’s password is set to null, and connection is denied. If a drop operation fails on more than one query server, that user is also locked out.