Using procedures for tailored security

While views restrict access on the basis of data, procedures restrict the actions a user may take. As described in “Granting permissions on procedures” a user may have EXECUTE permission on a procedure without having any permissions on the table or tables on which the procedure acts.

By default, procedures execute with the permissions of the procedure owner. For a procedure that updates a table, if the procedure owner has UPDATE permissions on the table, the user can execute the procedure. The owner of the procedure can restrict the procedure to execute with the permissions of the user executing the procedure by specifying SQL SECURITY INVOKER to CREATE/ALTER PROCEDURE statement.

Strict security

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

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

Create a group for each desired authority.
Grant the authority to the designated group.
Grant EXECUTE permissions on the IQ procedure for performing the authority tasks to the group.

When a new user is created 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.

Granting users the permissions to run related stored procedures

The following steps require DBA or PERMS ADMIN and USER ADMIN authority.

To grant a user user1, USER ADMIN authority and permissions to execute procedures related to user administration. The statement CREATE USER USERADMIN_GRP requires USER ADMIN authority.

Create a group USER ADMIN_GRP

CREATE USER USERADMIN_GRP
GRANT GROUP TO USERADMIN_GRP

Grant USER ADMIN authority to USERADMIN_GRP.
```
GRANT USER ADMIN TO USERADMIN_GRP
```

Grant EXECUTE permission on Sybase IQ stored procedures for user administration to USERADMIN_GRP.

GRANT EXECUTE on sp_iqaddlogin 
to USERADMIN_GRP
GRANT EXECUTE on sp_iqcopyloginpolicy 
to USERADMIN_GRP
GRANT EXECUTE on sp_iqdroplogin 
to USERADMIN_GRP
GRANT EXECUTE on sp_iqmodifyadmin 
to USERADMIN_GRP
GRANT EXECUTE on sp_iqmodifylogin 
to USERADMIN_GRP

Grant membership in group USERADMIN_GRP to user1. The user user1 inherits the USER ADMIN authority and the ability to execute the assigned IQ procedures through membership in USERADMIN_GRP group.
```
GRANT MEMBERSHIP IN GROUP USERADMIN_GRP TO user1
```

Using the same steps, you may create similar groups for other authorities, for example:

Group name	Grant authority	Grant EXECUTE permission on stored procedures
OPERATOR_GRP	OPERATOR	sp_iqbackupdetails, sp_iqbackupsummary, sp_iqconnection, and sp_iqsysmon
SPACEADMIN_GRP	SPACE ADMIN	sp_iqdbspace, sp_iqdbspaceinfo, sp_iqdbspaceobjectinfo, sp_iqemptyfile, sp_iqestdbspaces, sp_iqfile, sp_iqobjectinfo, and sp_iqspaceused

For a multiplex example, see Using Sybase IQ Multiplex.