Managing IQ user accounts and connections

The Sybase IQ User Administration facility helps you manage users and connections to a database. There are two ways to use the facility:

DBAs can add or drop users and control connections by:

limiting the number of active logins for the database

limiting the number of active logins for a single user

locking out a user

setting user password expirations

For the procedure you call to perform each Sybase IQ User Administration function, see Table 12-1. DBA authority is required to run each of these procedures, with the exception that all users can run sp_iqpassword to change their own password.

**Table 12-1: Stored procedures for Sybase IQ User Administration**
Call this stored procedure...	To perform this task...
sp_iqaddlogin	Add users and define their password, number of concurrent connections, and password expiration
sp_iqdroplogin	Drop users
sp_iqlistexpiredpasswords	List users whose passwords have expired
sp_iqlistlockedusers	List users who are locked out of the database
sp_iqlistpasswordexpirations	List password expiration information for all users
sp_iqlocklogin	Lock a user account so that the user cannot connect to the database
sp_iqmodifyadmin	Enable Sybase IQ User Administration, or set database defaults for active user or database connections or password expirations
sp_iqmodifylogin	Modify the number of concurrent connections or password expiration for one or all users
sp_iqpassword	Modify a user’s password. Users can modify their own password. DBAs can modify any password.

Enabling Sybase IQ User Administration updates the system tables with changes made since user administration was last enabled. This means that users added with GRANT are added to the system administration tables when you enable Sybase IQ User Administration.

Sybase IQ User Administration requires that the LOGIN_PROCEDURE database option be set to dba.sp_iq_process_login. With this option setting, when Sybase IQ User Administration is enabled, each time a user tries to connect to the database, the sp_iq_process_login procedure executes. This procedure determines whether the user is allowed to connect. It checks that the user is not locked out, that the maximum number of connections for that user and for the database are not exceeded, and that the user’s password has not expired. It then allows login to proceed or sends an error message if any login condition is not met.

When Sybase IQ User Administration is disabled, user login proceeds without these checks.

While you can also use the GRANT CONNECT and REVOKE CONNECT commands or Sybase Central to add or drop users, you cannot manage logins by those users with the Sybase IQ User Administration stored procedures, unless you also add them with sp_iqaddlogin. When you upgrade your database or enable Sybase IQ User Administration, existing users are automatically added to the system table used by the Sybase IQ User Administration procedures.

Database upgrades automatically add existing users to the Sybase IQ User Administration tables. New users added with the GRANT CONNECT command after a database upgrade are not automatically added to the Sybase IQ User Administration tables.

Users added to the database with GRANT CONNECT after IQ User Administration has been enabled or a database upgrade has been done will not be managed by IQ User Administration until IQ User Administration is enabled again.

To identify such users, compare the output of SELECT * FROM SYSUSERPERM and sp_iqlistpasswordexpirations. Users who appear in the SYSUSERPERM table but not in the output of sp_iqlistpasswordexpirations are not managed by IQ User Administration. To manage these users with IQ User Administration, simply enable IQ User Administration again, as follows:

call sp_iqmodifyadmin('enable');

For more information, see the Sybase IQ Reference Manual: