Preventing Public Access to the DB2 Database

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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.
Previous articleSymtrax releases StarQuery
Next articleOracle Time Zone

Latest Articles