Enables Sybase IQ User Administration, and modifies IQ user account information in the IQ_SYSTEM_LOGIN_INFO_TABLE system table.
call sp_iqmodifyadmin ( ‘{ enable | disable| user_connections | db_connections | password_expiration | password_warning }’ , [ value ] )
sp_iqmodifyadmin ‘{ enable | disable| user_connections | db_connections | password_expiration | password_warning }’ , [ value ]
DBA authority required.
enable | disable Enables or disables Sybase IQ User Administration. Use enable at
any time to bring the IQ_USER_LOGIN_INFO_TABLE
system table up to date.
The next four options require a value.
user_connections Sets the default number of connections per user. 0 means no limit is enforced.
db_connections Sets the default number of connections to the database. 0 means no limit is enforced.
password_expiration Sets the default number of days a password is valid. 0 means the password does not expire.
password_warning Sets the number of days before a password expires that a warning is sent to the user console.
value Value to which the named option is set. Values can be from 0 through 32767.
Sybase IQ User Administration relies on two system tables:
IQ_SYSTEM_LOGIN_INFO_TABLE contains database-wide defaults for connection limits and password expirations. When you execute sp_iqmodifyadmin, Sybase IQ modifies this table.
IQ_USER_LOGIN_INFO_TABLE contains connection limits and password expiration information for individual users. When you enable Sybase IQ User Administration, IQ updates this table with users created or modified without sp_iqaddlogin or sp_iqmodifylogin since Sybase IQ User Administration was last enabled.
The values in IQ_SYSTEM_LOGIN_INFO_TABLE are the default option settings for users in the IQ_USER_LOGIN_INFO_TABLE.
When a user logs on, Sybase IQ calls the stored procedure specified by the database option Login_Procedure. The default setting of this option is DBA.sp_iq_process_login. When Sybase IQ User Administration is enabled, this procedure checks that the user is not locked out, that the maximum number of connections for the user and database is not exceeded, and that the user’s password has not expired, then either allows the user to log in or sends an error message. When Sybase IQ User Administration is disabled, the user is allowed to log in without any checking. If you set Login_Procedure to a different value, no checking occurs.
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');
The following errors may occur. Causes are listed after each error.
Permission denied: You do not have permission to execute the procedure sp_iqmodifyadmin.
Cause: A user without DBA role tried to execute sp_iqmodifyadmin.
RAISERROR executed: "The number of connections allowed must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for user_connections.
RAISERROR executed: "The number of connections allowed must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for db_connections.
RAISERROR executed: "The number of days the password is valid must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for password_expiration.
RAISERROR executed: "The number of days to warn before a password expires must specified and be greater than or equal to zero and less than or equal to 32767."
Cause: A value other than 0 through 32767 was entered for password_warning.
RAISERROR executed: "Invalid input was supplied to sp_iqmodifyadmin."
Cause: Invalid data was entered somewhere in the command.
The following procedure calls set the maximum number of connections to the database at 200. They do not enable or disable Sybase IQ User Administration.
call sp_iqmodifyadmin ('db_connections', 200)
sp_iqmodifyadmin 'db_connections', 200
If Sybase IQ User Administration is not enabled, this change is not enforced.
