Preventing Public Access to the DB2 Database

September 11, 2003

Protecting and securing important information has always been a number one priority. Software companies try to build a product with as high an integrated level of security as possible that is still easy to handle and setup. IBM is no exception. With DB2,they provided an out-of-the-box database, wide-open for PUBLIC access, yet well equipped with advanced security options. the serious DBA has to implement a database security policy choosing between the operating system security, additional specialized security software or using integrated database security features. The target security policy that is implemented is often a combination of all of them. One substantial security threat involves the PUBLIC database group, which is installed by default. Companies need strong security regulations that answer the question of how the PUBLIC group should be tailored to satisfy security restrictions. In this article, I will cover security aspects of the DB2 database default PUBLIC group.

This article covers:

• Security Levels
• Database Privileges and Schema
• A Special Database Group Public
• The Public Group Research
• Applying Security Restrictions to the Public
• Public and Support for Static SQL and Views
• Conclusion

Security Levels

On every system, several security levels are defined to protect valuable database information. A typical implementation has the following configuration:

Users connecting to the Sun Solaris, Unix server have to pass an operating system authentication as well as the database authorization.

Operating System Authentication
user accounts are controlled with UNIX security or with a separate product such as DCE Security Services. User names are stored in the /etc/passwd file groups in the /etc/group file and their passwords are stored in the /etc/shadow file. The UNIX administrator has to implement the protection and security for the user accounts and their passwords. User names (user ID) have to be created as lower case, as DB2 database authorization will not permit user IDs with mixed characters. The UNIX group membership can be used during a database authorization. The DB2 database manager will obtain a list of operating system groups up to a maximum of 64 groups.

A database authentication method is predefined with several DBM configuration parameters. The default security settings for a fresh, newly installed database manager are as follows:

 Database manager authentication        (AUTHENTICATION) = SERVER
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT

These parameters will define the main security behavior between the client and the server. A DBA, together with the UNIX system administrator, need to find a single solution that best fits the company's regulations.

Database Authorization
The database security check for the authenticated user. The DB2 system catalogs are read for user ID and group settings. The user ID is checked against granted authority levels, the authorized commands and the properties for creating and controlling database objects. Depending upon the results of this process, the user either will gain or be denied access to the DB2 database. Logical structure for the authorization system:

Connection between the OS authentication and the database authorization is defined on the instance level, over database manager (DBM) configuration parameters. DB2 has five integrated authorities or roles:

SYSADM system administrator authority
An authority level that provides unlimited access for the instance and for all existing databases in the instance. This authority has the full privileges for the all database objects.

SYSCTRL system control authority
An authority level, supposed to manage a system and to authorize owners for the DBM instance administration. SYSCTRL provides an unlimited instance, limited database access and has no access to the database objects. This authority level uses UNIX group sysctrl_group for users grouping.

SYSMAINT system maintenance authority
An authority level, used to manage a system and to authorize owners for database administration. A SYSMAINT authority has unlimited instance, limited database access, and has no access to the database objects. This authority level uses UNIX group sysmain_group for users grouping.

DBADM database administrator authority
This authority level provides only unlimited access to the specified database, with full privileges for the all database objects. Even DBADM is powerful authority, it cannot be used for the grant/revoke DBADM authority, create /drop database, update DBM CFG, backup/restore/rollforward database, start/stop database, trace or obtain monitor snapshots. For this task we will need to use the SYSADM authority.

LOAD authority
LOAD is a new authority level introduced in DB2 version 7.1. Using this authority a DBA can define LOAD or AutoLoader utility users, without the need to give them DBA righta, the right to execute RUNSTATS or LIST TABLESPACES command.

The authorities SYSADM, SYSCTRL and SYSMAINT are the instance-level authorities and DBADM and LOAD are the database level authorities. The instance level authorities can be assigned only to the group while the database level authorities can be assigned to the user or group for a particular database.

Database version DB2 UDB v.8.1 has some enhancements. This version introduces new database authorities CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED_ROUTINE.

The CREATE_EXTERNAL_ROUTINE authority is used for registration and control of the external routines (stored procedures, UDFs, and methods), while CREATE_NOT_FENCED_ROUTINE authority is used to control NOT FENCED routines running in the same process as the database manager.