Securing routines

When creating routines it is important to ensure that the routines, routine libraries (in the case of external routines), and the privileges of the users that will interact with the routines are managed with routine security in mind.

Before you begin

Although it might not be necessary to have anything as elaborate as a routine security strategy, it helps to be mindful of the factors contributing to the security of routines and to follow a disciplined approach when securing routines.

  • Read the topic, "Security of routines".
  • To fully secure routines within the database system you must have:
    • Root user access on the database server operating system.
    • One of the SECADM or ACCESSCTRL authorities.

About this task

Whether you are creating a routine, or assessing an existing routine, the procedure for securing a routine is similar.

Procedure

  1. Limit the number of user IDs with the privileges required to create routines and ensure that these users are allowed to have these privileges.
    • Upon successful execution of the CREATE statement for a routine, this user ID will automatically be granted other privileges including the EXECUTE privilege, which allows the user to invoke the routine, and the GRANT EXECUTE privilege, which allows the user to grant the ability to invoke the routine to other users.
    • Ensure that the users with this privilege are few and that the right users get this privilege.
  2. Assess the routine for potentially malicious or inadequately reviewed or tested code.
    • Consider the origin of the routine. Is the party that supplied the routine reliable?
    • Look for malicious code such as code that attempts to read or write to the database server file system and or replace files there.
    • Look for poorly implemented code related to memory management, pointer manipulation, and the use of static variables that might cause the routine to fail.
    • Verify that the code has been adequately tested.
  3. Reject routines that appear to be excessively unsafe or poorly coded - the risk is not always worth it.
  4. Contain the risks associated with only somewhat potentially risky routines.
    • SQL user-defined SQL routines are by default created as NOT FENCED THREADSAFE routines, because they are safe to run within the database manager memory space. For these routines you do not need to do anything.
    • Specify the FENCED clause in the CREATE statement for the routine. This will ensure that the routine operation does not affect the database manager. This is a default clause.
    • If the routine is multi-threaded, specify the NOT THREADSAFE clause in the CREATE statement for the routine. This will ensure that any failures or malicious code in the routine do not impact other routines that might run in a shared thread process.
  5. If the routine is an external routine, you must put the routine implementation library or class file on the database server. Follow the general recommendations for deploying routines and the specific recommendations for deploying external routine library or class files.