Using multiplex procedures for tailored security

For strict security, you can disallow all access to underlying tables, and grant permissions to users or groups of users to execute certain stored procedures. This approach strictly defines who can define data in the database.

To allow users with the particular authorities to administer certain tasks using IQ system procedures:

1. Create a group for each desired authority.

2. Grant the authority to the designated group.

3. Grant EXECUTE permissions on the IQ procedure for performing the authority tasks to the group.

When you create a new user who is to be granted the authority, grant membership for this user to the group created for that authority. Since most authorities are inherited through group membership, the user inherits the authority and also the execute permissions for the IQ procedures from the group.

Except as noted, the following steps require DBA or PERMS ADMIN authority.

To grant MULTIPLEX ADMIN authority and permissions to execute procedures related to multiplex administration to a user named user1:

1. Connect to the database as a user with DBA authority or a user with USER ADMIN and PERMS ADMIN authority.

2. Create a group MPX ADMIN_GRP. You could write this as:

   CREATE USER MPXADMIN_GRP
   GRANT GROUP TO MPXADMIN_GRP
   

   or:

   call sp_addgroup('MPXADMIN_GRP')
   

3. Grant MULTIPLEX ADMIN authority to MPXADMIN_GRP:

   GRANT MULTIPLEX ADMIN TO MPXADMIN_GRP
   

4. Grant EXECUTE permission on Sybase IQ stored procedures for user administration to MPXADMIN_GRP:

   GRANT EXECUTE on sp_iqmpxgetconnversion 
   to MPXADMIN_GRP
   GRANT EXECUTE on sp_iqmpxinfo 
   to MPXADMIN_GRP
   GRANT EXECUTE on sp_iqmpxincconnpoolinfo 
   to MPXADMIN_GRP
   GRANT EXECUTE on sp_iqmpxincheartbeatinfo 
   to MPXADMIN_GRP
   GRANT EXECUTE on sp_iqmpxvalidate 
   to MPXADMIN_GRP
   GRANT EXECUTE on sp_iqmpxversioninfo 
   to MPXADMIN_GRP
   

5. Grant membership in group MPXADMIN_GRP to user1. user1 inherits the MULTIPLEX ADMIN authority and the ability to execute the assigned IQ procedures through membership in MPXADMIN_GRP group.

   GRANT MEMBERSHIP IN GROUP MPXADMIN_GRP TO user1