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.
- Revoke implicit authorities and privileges from PUBLIC
- Use explicit values for the SYSxxx_GROUP parameters
- Track implicit privileges
- Do not grant unnecessary privileges
- Use an encrypted AUTHENTICATION mode
- Use orphan IDs to create and own objects
- Use views to control data access
- Use stored procedures to control data access
- Use LBAC to control data access
- Prevent SQL injection in applications
- Apply the latest DB2 fix packs
- 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 privilege||Description|
|BINDADD||Allows the user to create new packages in the database|
|CREATETAB||Allows the user to create new tables in the database|
|CONNECT||Allows the user to connect to the database|
|IMPLICIT_SCHEMA||Allows the user to create objects in a schema that does not already exist (it creates the schema on-the-fly)|
|USE privilege on the USERSPACE1||Allows the user to create tables or indexes in the USERSPACE1 table space|
|CREATEIN on schema NULLID||Allows the user to create objects in the NULLID schema|
|CREATEIN on schema SQLJ||Allows the user to create objects in the SQLJ schema|
|EXECUTE WITH GRANT privilege on all functions and procedures in the SYSPROC schema||Allows 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 schema||Allows the user to invoke stored procedures in the SYSPROC schema|
|BIND and EXECUTE privilege on all packages created in NULLID schema||Allows the user to BIND and EXECUTE packages in the NULLID schema|
|SELECT privilege on tables in the SYSIBM schema||Allows the user to view information in the system catalog tables|
|SELECT privilege on views in the SYSCAT schema||Allows the user to view information in the system catalog views|
|SELECT privilege on administrative views in the SYSIBMADM schema||Allows the user to view information contained in these administrative views|
|SELECT privilege on catalog views in the SYSSTAT schema||Allows the user to view information in the system catalog views|
|UPDATE privilege on views in the SYSSTAT schema||Allows 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
|Action||Implicit privileges granted to the user performing the action|
|Create a new database|
|Grant DBADM authority|
|Create object (table, index, package)|
|Create a view|
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
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
* 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:
- Create a new user ID in your external security facility and mark this user ID as invalid so that it cannot be used.
- 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.
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
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
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
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
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.
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
At 12pm, you start the audit facility:
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.
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.
- DB2 9 Online Information Center: The DB2 online (and searchable) documentation.
- Read more articles about DB2 9 on developerWorks.
- "Understand how user and group accounts interact with DB2 UDB" (developerWorks, August 2005): This article describes the different user and group accounts that are needed to install and work with IBM DB2 Universal Database for Linux, UNIX, and Windows, Version 8.2. It also introduces the DB2 UDB security model, including user authentication, user and group authorization, and super users..
- "Understand the DB2 Universal Database security plug-ins" (developerWorks, December 2005): Learn about the IBM DB2 Universal Database security plug-ins, a new feature introduced in Version 8.2. This article explains what the security plug-ins accomplish and teaches you how to enable and write your own security plug-ins. .
- "Understand how authorities and privileges are implemented in DB2 UDB" (developerWorks, January 2006): This article reviews the different administrative authority levels and privileges available in DB2 UDB and how they can be granted to and revoked from user and group accounts. .
- "Understand the DB2 audit facility" (developerWorks, March 2006): Learn about the DB2 audit facility, its purpose, how to use and configure it with the db2audit command, and get tips for using it effectively. .
- "DB2 Label-Based Access Control, A Practical Guide, Part 1: Understand the basics of LBAC in DB2" (developerWorks, May 2006): This tutorial includes use case scenarios that demonstrate how users can apply Label-Based Access Control (LBAC) to protect their data from illegal access, and yet have the flexibility of allowing users to access data restrictively. The tutorial provides a step-by-step guide to creating LBAC solutions based on use-case scenarios..
- "DB2 Label-Based Access Control, A Practical Guide, Part 2: A step-by-step guide to protect sensitive data using LBAC" (developerWorks, May 2006): This tutorial includes use-case scenarios that demostrate how users can apply LBAC to protect their data from illegal access, and yet has the flexibility of allowing user to access data restrictively. The tutorial provides a step-by-step guide to create LBAC solutions based on use-case scenarios..
- developerWorks Information Management zone: Find more resources for DB2 for Linux, UNIX, and Windows developers and administrators.
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.
- Participate in the discussion forum.
- developerWorks blogs: Get involved in the developerWorks community.