Security of routines
The security of routines is paramount to ensure their continued functioning, to minimize the risk of tampering, and to protect the database system environment. There are a few categories of routine security considerations each with varying levels of risk. One must be aware of these risks when developing or maintaining routines so as to mitigate unfortunate outcomes as much as possible.
Security control of who can create routines
The
security of routines begins when users are given the necessary privileges
to execute the CREATE statement required to create routines in the
database. When granting these privileges, it is important to understand
the corresponding risks:
- Users with the privilege to execute the CREATE statement for a routine can create multiple routines.
- Users with the privilege to execute the CREATE statement for a routine can create routines that can modify the database layout or database data subject to the other privileges that user has.
- Users that successfully create routines are automatically granted the EXECUTE privilege required to invoke the routine.
- Users that successfully create routines are automatically granted the ALTER ROUTINE privilege required to modify the routine.
To minimize the risk of users modifying the database and
data:
- Minimize the number of users that have the privilege to create routines.
- Ensure that the user IDs of departed employees are removed, or if they are re-used, be sure to assess the procedure related privileges.
Refer to the topics on controlling access to database objects and data for more on how to grant and revoke privileges from one, many, or all database users.
Security control of who can invoke routines
It
is easy to determine when users require privileges: they are unable
to do something. It is harder to determine when users no longer require
these privileges. This is particularly true when it comes to users
with privileges to invoke routines, as allowing them to retain their
privileges can introduce risks:
- Users that have been granted the EXECUTE privilege to invoke a routine will continue to be able to invoke the routine until this privilege is removed. If the routine contains sensitive logic or acts on sensitive data this can be a business risk.
To minimize the risk of users modifying the database and
data:
- Minimize the number of users that have the privilege to invoke routines.
- Ensure that the user IDs of departed employees are removed, or if they are re-used, be sure to assess the procedure related privileges.
- If you suspect that someone is maliciously invoking routines, you should revoke the EXECUTE privilege for each of those routines.
Security control of routines defined with FENCED or NOT FENCED clauses
When formulating the CREATE statement
for a routine, you must determine whether you want to specify the
FENCED clause or NOT FENCED clause. Once you understand the benefits
of creating a routine as fenced or unfenced it is important to assess
the risks associated with running routines with external implementations
as NOT FENCED.
- Routines created with the NOT FENCED clause can accidentally or maliciously corrupt the database manager's shared memory, damage the database control structures, or access database manager resources which can cause the database manager to fail. There is also the risk that they will corrupt databases and their tables.
To ensure the integrity of the database manager and its
databases:
- Thoroughly screen routines you intend to create that specify the NOT FENCED clause. These routines must be fully tested, debugged, and not exhibit any unexpected side-effects. In the examination of the routine code, pay close attention to memory management and the use of static variables. The greatest potential for corruption arises when code does not properly manage memory or incorrectly uses static variables. These problems are prevalent in languages other than Java™ and .NET programming languages.
In order to register a NOT FENCED routine, the CREATE_NOT_FENCED_ROUTINE authority is required. When granting the CREATE_NOT_FENCED_ROUTINE authority, be aware that the recipient can potentially gain unrestricted access to the database manager and all its resources.
Note: NOT
FENCED routines are not supported in Common Criteria compliant configurations.