DB2 security, Part 8: Twelve DB2 security best practices

With the escalating number of publicized system security breaches, administrators must constantly be on the lookout for security holes in their systems so that their company does not become the next public embarrassment. Security is a large topic, and can be applied at various levels in a system architecture. This article, part eight of an eight-part series, focuses on twelve security best practices that database administrators (DBAs) and developers can follow to ensure the highest level of security in DB2® for Linux®, UNIX®, and Windows®. These practices should complement other proactive security measures being applied at the other system levels.

Share:

Ted J. Wasserman (tedwas.ibm@gmail.com), Database Consultant, IBM, Software Group

Ted J. Wasserman's photoTed J. Wasserman is a database consultant at the IBM Silicon Valley Laboratory in San Jose, California. Ted works on the DB2 Business Partner Technical Enablement team, where he specializes in helping IBM Business Partners migrate their applications and databases to DB2. Ted has a master's degree in computer science, as well as a bachelor's degree in computer science from Queen's University in Kingston, Ontario, Canada.



20 July 2006

Introduction

A number of reports detailing wide-ranging system security breaches have been at the forefront of the news in the past couple of years. Typically, sensitive personal data such as Social Security Numbers (SSNs), credit card numbers, and bank account numbers are stolen from insecure systems, resulting in identity theft, financial fraud, or other unauthorized use of the information. As a result, system administrators must constantly be monitoring their systems and ensuring appropriate security precautions are taken.

Security can be applied at different levels of a system architecture. For example, a firewall might be installed to prevent unauthorized server access from outside of the network. A secure network protocol technology such as IPSec might be used to secure the communication channel between computers on a network. A strict password policy might be put in effect that requires users to select a strong password and change it on a frequent basis. Database-level security measures including authentication and authorization might also be used to enhance application security.

In this article, twelve security best practices for DB2 for Linux, UNIX, and Windows are described. They focus specifically on elements that can be controlled from a database administration and programming standpoint, and do not include other security technologies or policies that might also be applicable on a wider system scale. The best practices are not listed in any particular order, but rather, all of them are equally important, as they all contribute toward the overall security level of your DB2 data server.

  1. Revoke implicit authorities and privileges from PUBLIC
  2. Use explicit values for the SYSxxx_GROUP parameters
  3. Track implicit privileges
  4. Do not grant unnecessary privileges
  5. Use an encrypted AUTHENTICATION mode
  6. Use orphan IDs to create and own objects
  7. Use views to control data access
  8. Use stored procedures to control data access
  9. Use LBAC to control data access
  10. Prevent SQL injection in applications
  11. Apply the latest DB2 fix packs
  12. Perform random security audits

Revoke implicit authorities and privileges from PUBLIC

DB2 internally uses a pseudo-group called PUBLIC, which privileges can be granted to and revoked from. PUBLIC is not actually a group defined in the external security facility, but is a way to assign privileges to any user who successfully authenticates with DB2.

When a new database is created, certain database authorities and privileges are automatically granted to PUBLIC, as shown in Table 1.

Table 1. A list of the authorities and privileges granted to PUBLIC after database creation
Authority or privilegeDescription
BINDADDAllows the user to create new packages in the database
CREATETABAllows the user to create new tables in the database
CONNECTAllows the user to connect to the database
IMPLICIT_SCHEMAAllows the user to create objects in a schema that does not already exist (it creates the schema on-the-fly)
USE privilege on the USERSPACE1Allows the user to create tables or indexes in the USERSPACE1 table space
CREATEIN on schema NULLIDAllows the user to create objects in the NULLID schema
CREATEIN on schema SQLJAllows the user to create objects in the SQLJ schema
EXECUTE WITH GRANT privilege on all functions and procedures in the SYSPROC schemaAllows the user to invoke stored procedures and execute functions in the SYSPROC schema and grant that permission to other users
EXECUTE WITH GRANT privilege on all procedures in the SQLJ schemaAllows the user to invoke stored procedures in the SYSPROC schema
BIND and EXECUTE privilege on all packages created in NULLID schemaAllows the user to BIND and EXECUTE packages in the NULLID schema
SELECT privilege on tables in the SYSIBM schemaAllows the user to view information in the system catalog tables
SELECT privilege on views in the SYSCAT schemaAllows the user to view information in the system catalog views
SELECT privilege on administrative views in the SYSIBMADM schemaAllows the user to view information contained in these administrative views
SELECT privilege on catalog views in the SYSSTAT schemaAllows the user to view information in the system catalog views
UPDATE privilege on views in the SYSSTAT schemaAllows the user to update statistical information in these system catalog views

As a best practice, immediately revoke the implicit privileges granted to PUBLIC after the creation of a new database.

For example, Listing 1 shows a subset of statements you can execute to revoke privileges from the system catalog views and other privileges implicitly granted to PUBLIC. This list is not comprehensive.

Listing 1. Revoking implicit privileges from PUBLIC after database creation
CREATE DATABASE testdb;
CONNECT TO testdb;

REVOKE BINDADD ON DATABASE FROM PUBLIC;
REVOKE CREATETAB ON DATABASE FROM PUBLIC;
REVOKE CONNECT ON DATABASE FROM PUBLIC;
REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC;
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.COLAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.DBAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.INDEXAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PACKAGEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PASSTHRUAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.ROUTINEAUTH FROM PUBLIC; 
REVOKE SELECT ON TABLE SYSCAT.SCHEMAAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYLABELACCESS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SECURITYPOLICYEXEMPTIONS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SEQUENCEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.SURROGATEAUTHIDSFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.TBSPACEAUTH FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.XSROBJECTAUTHFROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.AUTHORIZATIONIDS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.OBJECTOWNERS FROM PUBLIC;
REVOKE SELECT ON TABLE SYSCAT.PRIVILEGES FROM PUBLIC;
...
...

Beginning in DB2 V9.1, the CREATE DATABASE command syntax has been extended with the RESTRICTIVE option. If the RESTRICTIVE option is included, it causes the RESTRICT_ACCESS database configuration parameter to be set to YES and no privileges are automatically granted to PUBLIC. If the RESTRICTIVE option is omitted then the RESTRICT_ACCESS database configuration parameter is set to NO and all the privileges described above are automatically granted to PUBLIC.


Use explicit values for the SYSxxx_GROUP parameters

DB2 defines a hierarchy of super-user authorities (SYSADM, SYSCTRL, SYSMAINT, and SYSMON), each with the ability to perform a subset of administrative operations such as creating a database, forcing users off a system, and taking a database backup. Their associated instance-level parameters (SYSADM_GROUP, SYSCTRL_GROUP, SYSMAIN_GROUP, and SYSMON_GROUP) control which users inherit that authority.

Each parameter can be set to the name of a group of users (defined in the external security facility) who should have that authority. Once set, all users in the specified group inherit the authority.

For example, if you have an operating system group called DBAGRP1 that all the DBA users are a member of, all users in this group would inherit the SYSADM authority by setting the value of the SYSADM_GROUP instance parameter to the value DBAGRP1, using the commands shown in Listing 2.

Listing 2. Updating the SYSADM_GROUP instance parameter
UPDATE DBM CFG USING SYSADM_GROUP dbagrp1
db2stop
db2start

During a default DB2 installation on Windows, the value of these parameters defaults to NULL. This means that super-user authority is granted to any valid user account that belongs to the local Administrators group. On Linux and UNIX platforms, a NULL value defaults to the primary group of the instance owner, which by default after an installation only contains the user ID of the instance owner.

As a best practice, change the default value of each instance-level authority parameter to an explicit group name in order prevent unintended super-user access.

In a small business where one DBA fills many roles, these parameters might be set to the same group name. In a large environment where multiple DBAs are responsible for a system, different group names might be used. In addition to ensuring these parameters are given an explicit value, you should also do your best to ensure that all users who are a member of the specified group actually need to be a member of the group. If they don't, remove them! Since user and group account management is handled outside of DB2, DB2 does not scrutinize whether users should or should not be a member of a group.


Track implicit privileges

As previously mentioned, PUBLIC is implicitly granted certain privileges when a new database is created. This isn't the only time implicit privileges are granted. In some circumstances, the database manager implicitly grants certain privileges to a user when a user creates a database object, such as a table or a package, or when the DBADM authority level is granted. It is important to understand which implicit privileges are granted and the security implications of these implicit privileges. Table 2 summarizes the cases where implicit privileges are granted.

Table 2. Summary of the implicit privileges granted for different actions
ActionImplicit privileges granted to the user performing the action
Create a new database
  • GRANT of DBADM authority with BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, and QUIESCE_CONNECT authorities to creator (SYSADM or SYSCTRL)
  • GRANT of BINDADD, CREATETAB, CONNECT and IMPLICIT_SCHEMA to PUBLIC
  • USE privilege on USERSPACE1 table space to PUBLIC
  • EXECUTE WITH GRANT privilege on all procedures and functions in the SYSPROC schema to PUBLIC
  • EXECUTE with GRANT on all procedures in the SQLJ schema to PUBLIC
  • BIND and EXECUTE privilege on all packages in the NULLID schema to PUBLIC
  • CREATEIN on schemas SQLJ and NULLID to PUBLIC
  • SELECT privilege on the SYSIBM catalog tables to PUBLIC
  • SELECT privilege on the SYSCAT catalog views to PUBLIC
  • SELECT privilege on the SYSIBMADM administrative views to PUBLIC
  • SELECT privilege on the SYSSTAT catalog views to PUBLIC
  • UPDATE privilege on the SYSSTAT catalog views to PUBLIC
Grant DBADM authority
  • GRANT of BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD and QUIESCE_CONNECT to the target user
Schema
  • When explicitly created, CREATEIN, ALTERIN, DROPIN is granted to the user that created the schema
  • When implicitly created, CREATEIN is additionally given to PUBLIC
Create object (table, index, package)
  • GRANT of CONTROL to object creator
Create a view
  • GRANT of the CONTROL privilege only if the user has CONTROL privilege for all tables, views, and nicknames referenced in the view definition

As a best practice, carefully scrutinize and track the implicit privileges that are granted when you perform an action. If you later undo the action, ensure you have a process that revokes any implicit privileges as well.

For example, suppose you initially granted the DBADM authority to the user JEFF and at a later time you decided to revoke that authority. To revoke the DBADM authority from JEFF, you could use the following statement:

REVOKE DBADM ON DATABASE FROM USER jeff

After executing this statement, JEFF would no longer have DBADM authority; however, he would still have the GRANT, BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE,CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD and QUIESCE_CONNECT authorities on the database that were implicitly granted when JEFF was originally granted the DBADM authority. These would need to be explicitly revoked from JEFF.


Do not grant unnecessary privileges

While developing an application, it is tempting for developers not to worry about security issues right away. For example, developers will typically develop and test their application with a super-user account (DBADM or SYSADM) in order not to be bogged down with security error messages when they try to run their code. It is quite easy to grant a user all database permissions and authorities using the Control Center, as is shown in Figure 1.

Figure 1. Granting permissions from the Control Center
The Control Center Add User dialog

Often, once the application makes it through the development and testing phases, the permissions granted during the development process to suppress security error messages still linger, even though they are not necessarily required.

As a best practice, carefully review the privileges that are granted to each user as part of your application installation and configuration process. Make sure that all the permissions and authorities being granted are actually required.

It is easy for a developer who is not very familiar with the DB2 security model to simply grant all available privileges using the Control Center (see Figure 1) to suppress security error messages. You should ensure that all the permissions and authorities that are granted are actually required, or if only one or two of them are really needed.


Use an encrypted AUTHENTICATION mode

Authentication is the process of validating a supplied user ID and password using a security mechanism. User and group authentication is managed in a facility external to DB2, such as the operating system, a domain controller, or a Kerberos security system. The actual authentication location is determined by the value of the instance parameter AUTHENTICATION. The various authentication schemes include having users authenticated on the DB2 server itself (using the server's security facility), on the client (allowing "single sign-on" access), a Kerberos security facility, or through a user-defined Generic Security Service (GSS) plug-in. Additional authentication options include the ability to encrypt user names and passwords, as well as data, as they travel across the network between client and server. Table 3 summarizes each of the encrypted authentication options.

Table 3. A summary of encrypted AUTHENTICATION modes
AUTHENTICATION modeDescription
SERVER_ENCRYPT
  • Specifies that authentication occurs on the security facility defined on the server. If a user ID and password are specified during the connection or attachment attempt, they are compared to valid user ID and password combinations defined at the server to determine if the user is permitted to access the instance or database.
  • In this mode, the user ID and password are encrypted as they travel over the network.
KRB_SERVER_ENCRYPT
  • Specifies that the server accepts KERBEROS authentication or encrypted SERVER authentication schemes.
DATA_ENCRYPT
  • Specifies that the server accepts encrypted SERVER authentication schemes and the encryption of user data.
  • The following user data is also encrypted when using this authentication type:
    • SQL and XQuery* statements
    • SQL program variable data
    • Output data from the server processing of an SQL or XQuery* statement and including a description of the data
    • Some or all of the answer set data resulting from a query
    • Large object (LOB) data streaming
    • SQLDA descriptors
DATA_ENCRYPT_CMP
  • Specifies that the server accepts encrypted SERVER authentication schemes and the encryption of user data.
  • This authentication type allows for compatibility with down level products not supporting the DATA_ENCRYPT authentication type. These products are permitted to connect with the SERVER_ENCRYPT authentication type without encrypting user data. Products supporting the DATA_ENCRYPT authentication type must use it.
GSS_SERVER_ENCRYPT
  • Specifies that the server accepts GSS API based plug-in authentication or encrypted server authentication schemes.

* Note: XQuery is an officially supported query language as of DB2 9.

As a best practice, use an encrypted authentication mode.

The authentication mode you choose for your environment will be determined by the level of sensitivity of your data. If all of your data is sensitive, you might opt to choose the DATA_ENCRYPT authentication mode, which encrypts a lot of the data traveling between client and server. If only a small subset of your data is sensitive, you might choose to use the SERVER_ENCRYPT mode so that at least your password is encrypted, while the sensitive data can be secured through a different mechanism.

To update the AUTHENTICATION instance parameter - in this example to a value of DATA_ENCRYPT - use the commands shown in Listing 3.

Listing 3. Updating the AUTHENTICATION instance parameter
UPDATE DBM CFG USING AUTHENTICATION DATA_ENCRYPT
db2stop
db2start

Note that the AUTHENTICATION parameter is set at the instance level, meaning that databases created in the same instance share the authentication mode. If you have two databases and each requires a different AUTHENTICATION mode, you would need to create the databases in separate instances.


Use orphan IDs to create and own objects

When a database object is created, it is owned by the user ID that executed the DDL statement to create it. If that user ID is later retired (e.g. the user leaves the company) or if the user no longer needs database access or authority on database objects, a DBA must revoke privileges from the user. This can result in other dependent database objects or packages becoming invalid (or inoperative).

Once successfully created, a database object or package is considered to be valid (versus inoperative) for as long as the object creator or binder of the package continues to hold the necessary privileges on the database objects that are referenced by it. Hence, objects and packages containing static SQL statements can become invalidated when the privileges of the object creator or binder of the package are revoked.

As a best practice, use an orphan ID to create and own objects, as described in a Technote.

To summarize this process:

  1. Create a new user ID in your external security facility and mark this user ID as invalid so that it cannot be used.
  2. Ensure that the user ID has no CONNECT authority by removing this user ID from all operating system groups and by ensuring that the CONNECT privilege is revoked from the user or a group the user belongs to.
  3. When new database objects need to be created, or other DDL statements must be executed, grant the necessary privileges required to perform the action to the new user ID by executing the GRANT statement. For example, to create a view on table T1, you must grant the SELECT privilege on table T1 to the new authorization ID:

    GRANT SELECT ON TABLE T1 TO USER <user_auth_id>

    Set the current session authorization ID to the new user ID temporarily. For example:

    SET SESSION_USER = <user_auth_id>

    Under this authorization ID, create database objects and bind packages. For example, to create the view on table T1, you would execute the following statement:

    CREATE VIEW V1 AS SELECT * FROM T1

  4. Once all required database objects and packages are created, use group membership and group privileges to control access to the created database objects and packages.

    GRANT SELECT ON VIEW V1 TO GROUP1
    GRANT EXECUTE ON PKG TO GROUP1

  5. When you have finished, reset the current session authorization ID to your regular authorization ID by executing one of the two following statements:

    SET SESSION_USER = SYSTEM_USER

    or

    SET SESSION_USER = <user_auth_id>

This approach ensures that a single user ID is associated with the role of creating database objects, binding packages, and granting privileges. Over time as users come and go, this will greatly simplify database schema and privilege management.


Use views to control data access

A common way to control access to table data is using views. Rather than exposing your entire set of table data to application users, you can create a view based on a subset of columns. For example, assume that the table defined in Listing 4 that contains insurance policy information:

Listing 4. A sample table definition containing insurance data
CREATE TABLE INSURANCE (
   CUSTID               INTEGER NOT NULL PRIMARY KEY,
   SALARY               FLOAT,
   RENEWAL_MONTH        VARCHAR(3),
   SEX                  CHAR(1),
   MARITAL_STATUS       CHAR(1),
   NUM_DEPENDENTS       INTEGER,
   YEAR_1ST_POLICY      INTEGER,
   NUM_CLAIMS           INTEGER,
   CYCLES               INTEGER,
   AGE                  FLOAT,
   COMMUTE_DIST         FLOAT
);

Suppose that one of the insurance company's sister companies wants access to customer data so they can analyze it and make additional customized offers to customers. However, suppose that by law, the insurance company is not allowed to divulge a person's age or how many claims they've made. In order to satisfy these requirements, a view such as the one in Listing 5 could be defined over the table so that the customer's age and claim history is left out:

Listing 5. A sample view definition over the previous table containing insurance data
CREATE VIEW ins_v1_sis_comp_1 AS 
    SELECT custid, salary, sex, marital_status, num_dependents, year_1st_policy, 
           cycles, age, commute_dist
    FROM insurance;
);

Access to the data could then be governed through the view, instead of the base table. Access to the view can be controlled using GRANT statements, so that not every user could view this data. For example, you can control who can SELECT, INSERT, UPDATE, and DELETE from/to the view.

As a best practice, use views to control access to tables when you want to hide a subset of table columns or rows. Using views also helps insulate the application when the underlying table definition changes. Specific privileges can also be granted on views, similar to tables.

In addition, predicates can be added to a view definition that will further qualify a set of data, while keeping private information hidden. Using the above example, to only view male customers who are age 65 or above, the view definition in Listing 6 could be used:

Listing 6. A sample view definition over the previous insurance data further limiting the rows returned
CREATE VIEW ins_v1_sis_comp_1 AS
   SELECT custid, salary, sex, marital_status, num_dependents, year_1st_policy, 
          cycles, age, commute_dist 
   FROM insurance
   WHERE age >= 65 AND sex = 'M';
);

This would still meet the legal requirements of not providing a customer's age; however, it would give the sister company more relevant information to further customize their offers.


Use stored procedures to control data access

Another popular method of controlling access to table data is through the use of stored procedures. A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. They are created and run on the data server and used to encapsulate a set of frequently run operations or queries. For example, operations on an employee database (hire, fire, give raise, lookup) could be coded as stored procedures and invoked by the application instead of being coded directly inside the application. Stored procedures can be compiled and executed with different parameters and results, and they can have any combination of input, output, and input/output parameters. Listing 7 shows an example of a stored procedure that determines an employee's new salary and bonus, depending on their performance rating.

Listing 7. A stored procedure to give an employee a raise and bonus depending on their performance rating
CREATE PROCEDURE UPDATE_SALARY (
	IN empNum  CHAR(6), 
	IN rating  SMALLINT)
  LANGUAGE SQL
  BEGIN
    IF rating = 1 THEN
      UPDATE employee
        SET salary = salary * 1.10, bonus = 1500
        WHERE empno = empNum;
    ELSE
      UPDATE employee
        SET salary = salary * 1.05, bonus = 1000
        WHERE empno = empNum;
    END IF;
  END

This stored procedure accepts two input parameters, the employee number and a rating, then updates the employee's salary and bonus depending on the rating given. For employees with a rating of "1", the employee is given a ten percent raise and a $1500 bonus. For all other ratings, the employee is given a five percent raise and a $1000 bonus.

As a best practice, consider using stored procedures as a way to control access to your data. Access to tables would be allowed indirectly through a stored procedure call, thereby limiting the actions a user could perform on a table while also controlling what users could invoke the stored procedure.

Many applications nowadays design their database layer as stored procedures. That is, all database access is performed through stored procedure invocations. Applications wanting to begin a transaction, such as updating an order or purchasing a product simply need to invoke the appropriate stored procedure from the application. A side benefit of this approach is the fact that all logic is centralized in one place, making management and maintenance easier, as well as making the functionality available to other applications. This approach lends itself to a Service Oriented Architecture (SOA) quite nicely.

You can also control access to stored procedures through GRANT and REVOKE statements. Users wanting to invoke the procedure would need to be granted the EXECUTE privilege. Additional privileges may be required on individual objects being referenced by the stored procedure, depending on the bind options and whether the SQL statement is static or dynamic.


Use LBAC to control data access

A new and exciting feature in DB2 9 is Label Based Access Control (LBAC). LBAC lets you decide exactly who has write access and who has read access to individual rows and individual columns.

A special new security administrator authority (SECADM) is used to configure LBAC by creating security policies which essentially define the criteria that is used to decide who has access to what data. After creating a security policy, the security administrator creates objects, called security labels that are also part of that policy. Labels can be based on any criteria, such as a job title, whether the user is a manager or not, or whether the user belongs to a specific department. Once created, a security label can be associated with individual columns and rows in a table to protect the data held there. The security administrator allows users access to protected data by granting them security labels. When a user tries to access protected data, that user's security label is compared to the security label protecting the data.

A security administrator can also grant exemptions to users. An exemption allows a user to access protected data that their security labels might otherwise prevent them from accessing. If a user tries to access a protected column that their LBAC credentials do not allow them to access, then the access will fail and they will get an error message.

As a best practice, consider using LBAC as a way to control access to sensitive data. LBAC is very configurable and you can tailor it to match your particular security environment. This exciting new security feature in DB2 9 might alone be worth migrating for!

Many applications implement similar row and column-based security access mechanisms natively. Why not let your developers concentrate on developing the business logic? It is now easy to offload this capability to the data server using this very customizable new security feature.


Prevent SQL injection in applications

With the wealth of new web applications replacing traditional client-side applications, security must be designed into the system from the start. A common security hole in web-based applications is known as SQL injection. SQL injection is a technique which enables an attacker to execute unauthorized SQL commands by taking advantage of non-scrutinized input opportunities in applications that build dynamic SQL queries. This typically occurs when the web application combines the strings of a query with an unchecked input variable so that someone could add a second query or otherwise change the query to give them information or access that they should not have.

For example, consider the following PHP code snippet:

$sql = 'SELECT * FROM staff WHERE empID="'.$_GET['empid'].'"'
$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt, array(10));

This query selects all rows from the STAFF table where the employee ID is equal to the one retrieved from a form. This statement is vulnerable to SQL injection - quotes in $_GET['username'] are not escaped and will be concatenated as part of the statement text, which can result in malicious behavior.

Consider what would happen if $_GET['empid'] were the following at runtime:

" OR 1=1 OR empID = "

When concatenated into the original expression, the query would look like this:

SELECT * FROM staff WHERE empID = "" OR 1=1 OR empID = ""

This statement selects all rows from the table, potentially exposing private information. While this particular example might not be considered severe, other more malicious code could be added, especially DELETE or UPDATE statements that modify tables.

As a best practice, code your applications with security in mind. Avoid retrieving redundant data and scrub all input values.

SQL injection attacks are mainly based on exploiting code not written with security in mind. In order to prevent this, the PHP manual makes several recommendations, including:

  • Never trusting any kind of input, even if it comes from a select box, a hidden input field or a cookie.
  • Never connect to the database as a user with super authority or as the database owner. Use always customized users with very limited privileges.
  • Check if the given input has the expected data type. Languages like PHP have a wide range of input validation functions.
  • Quote each non-numeric user supplied value that is passed to the database with a database-specific string escape function.
  • Do not print out any database specific information, especially about the schema, by fair means or foul.

Apply the latest DB2 FixPaks

DB2 FixPaks contain important bug fixes and performance enhancements. They are usually scheduled for release on a quarterly basis and can be freely downloaded from the DB2 Technical Support Web site.

FixPaks are cumulative. This means, for example, that FixPak 10 also contains the fixes in FixPak 9, FixPak 8, FixPak 7, and so on, so you only have to download the latest one to take advantage of all the updates in the previous ones.

If you want to find out what version and FixPak level of DB2 you currently have installed, use the db2level command from a command window. The output displays the bit-size of your instance - either 32 or 64 bit, the FixPak level currently installed, and the installation directory.

As a best practice, develop all new applications using the latest FixPak level. That way, you will be able to take advantage of all the latest performance enhancements and fixes. For existing applications, you should seriously consider moving to the latest FixPak if it contains a fix that addresses an important security concern.

A FixPak download contains the code used to upgrade your installation along with supporting documentation. The APAR list, contained in the aparlist.txt and aparlist.html files, contains a list of APARs, or known product defects, that are fixed in the FixPak. If you were experiencing incorrect or unexpected behavior in your current level of DB2, it might have been due to a product defect. You can view the APAR file to see if the FixPak contains a fix for it. The fix pack README file, called FixPackReadme.txt, contains the instructions for installation. You should always read this file before starting a FixPak installation in order to understand the exact sequence of steps you should follow. Finally, the FixPak release notes, contained in the release.txt and relnotes.pdf files, contain the latest information about the DB2 product and known issues and workarounds.fix.


Perform random security audits

Finally, any proactive security plan should include random security audits. These audits involve logging database events such as authorization checking, database object maintenance, security maintenance, system administration, and user validation and ensuring that access patterns look normal.

Fortunately, DB2 comes with an auditing facility that generates and allows a DBA to maintain an audit trail for a series of predefined database events. Auditing takes place at the instance level, meaning that once it is started, it audits the activity for all databases in that instance. The audit facility can monitor different types of database events and you can specify whether only successful or failed events, or both, should be logged.

The db2audit command is used to configure and operate the audit facility. Once auditing is configured and audit records generated, they can be extracted into a text file, which can then be analyzed. They can also be extracted into delimited ASCII files, which can then be loaded into DB2 relational tables for analysis and querying.

For example, suppose that you get an anonymous tip from one of your application users that a user called SAM is attempting to gain access to database objects and tables that he is not supposed to have access to. You decide to randomly monitor the DB2 instance for failed authorization checking attempts.

During one lunch hour, you begin by configuring the audit facility to audit the CHECKING event type, recording only failed attempts and using NORMAL error processing:

db2audit configure scope checking status failure errortype normal

At 12pm, you start the audit facility:

db2audit start

During the auditing period, SAM walks over to the database server and logs in. He opens a command line window, connects to the SAMPLE database and unsuccessfully tries to update the employee salaries in the EMPLOYEE table. He issues the following SQL statements:

connect to sample user sam using bad123boy
update tedwas.employee set salary = salary * 1.5

Upon receiving the error message:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0551N "SAM" does not have the privilege to perform operation "UPDATE" on
object "TEDWAS.EMPLOYEE". SQLSTATE=42501

indicating that he does not have permission to update that table, he quickly logs off the server and leaves, thinking that nobody will notice anything.

An hour passes by and you decide to check the contents of the audit log. You extract the records from the db2audit.log file into ASCII delimited files:

db2audit extract delasc delimiter ; category checking database sample status failure

Having previously created the DB2 tables to hold the audit data, you load the extracted data from the checking.del file into the CHECKING table, using the following command:

LOAD FROM checking.del OF del MODIFIED BY CHARDEL; INSERT INTO audit.checking

You attempt to find out more information about the failed authorization attempt by querying the AUDIT.CHECKING table:

SELECT category, event, appid, appname, userid, authid FROM audit.checking

From the query results, shown in Listing 8, you can see that one audit record was generated for the failed update statement.

Listing 8. Result of querying the CHECKING table
SELECT category, event, appid, appname, userid, authid FROM audit.checking


CATEGORY EVENT             APPID                    APPNAME           AUTHID
------------------------  -----------------------  ----------------  ------------
CHECKING CHECKING_OBJECT  *LOCAL.DB2.060206220334   db2bp.exe         SAM

1 record(s) selected.

The output confirms that SAM tried to access a table he was not supposed to. Now that your suspicions have been validated, you will continue to gather additional evidence to present to your management, so they can take corrective action.

As a best practice, perform random security audits on your database. You can also perform an audit after receiving information that would lead you to believe someone is trying to compromise the security of your system.

The DB2 auditing facility is very powerful and can provide you with the detailed information you need to audit access attempts. While reactive monitoring to unforeseen events is inevitable, proactive auditing should also be an important component of your security plan and you should allot time at different points during the month to perform monitoring and analysis.


Conclusion

In this article, twelve DB2 security best practices were reviewed, ranging from using encrypted authentication modes to performing security audits. Monitoring your system's security is becoming an increasingly important task, given the escalating number of occurrences of system security breaches. By following these best practices, you can help minimize the security threats to your DB2 data server. You should use the best practices in conjunction with security best practices and policies that are in place at other levels in your system architecture to ensure a comprehensive secure solution.

Resources

Learn

Get products and technologies

  • Download DB2 9 (test drive) to try out the features described in this article.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=148318
ArticleTitle=DB2 security, Part 8: Twelve DB2 security best practices
publish-date=07202006