Preventing Public Access to the DB2 Database

Thursday Sep 11th 2003 by Marin Komadina
Share:

Concerned about who is walking unchallenged through the Public group door into your database? Marin Komadina examines the security aspects of the DB2 database default PUBLIC group.

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.

Database Privileges and User Schema

A database privilege is a user or a group right to create or access the database resource. Privileges are granted on the database level and they are under the control of the DBADM and the SYSADM authority.

A list of privileges, grouped on the database object level:

PRIVILEDGE GROUP

PRIVILEDGES

DATABASE

Createtab,Load,Bindadd,Connect,Implict_Schema,Create_Not_Fenced

TABLE SPACES

Use

SCHEMA

Createin,Alterin,Dropin

TABLES

Control,Alter,Delete,Indeks,Insert,References,Select,Update

VIEWS

Control,All,Alter,Delete,Indeks,Insert,References,Select,Update

INDEXES

Control

PACKAGES

Control,Bind,Execute

A list of privileges, grouped on the security level:

PRIVILEDGE GROUP

PRIVILEDGES

OWNERSHIP

control privilege gives full control over database object

INDUVIDUAL

Select/delete/insert/update privilege allows user to performs special actions on database objects

IMPLICIT

granted to the user or group when the user got some higher level privilege

EXPLICIT

granted directly to the user or group

An ownership privilege (also called CONTROL privilege) gives full privileges for a specific object. Individual privileges may be granted to allow the user to carry out specific functions on specific objects. The users with CONTROL privilege, or administrative authority (SYSADM or DBADM) can grant and revoke the individual privileges. For the privilege granted with GRANT OPTION, the user can further grant privilege to another user. Restriction exists only for the CONTROL privilege for database authorities, package privileges, index privileges, or control privilege on the table or view that cannot be further granted. As the granting procedure is easy to understand and control, revoking (removing granted privilege) is a little bit tricky, since a person with the GRANT OPTION cannot revoke granted privilege without having direct CONTROL privilege on the object.

User schema is the center of user objects. A schema can be created explicitly using the create schema command or implicitly by the DB2 database manger. For example, a user can run the CREATE command, with an intention to create a new database object. The database manager will implicitly create the user schema for the connected user, with a requested object inside.

The owner of all schemas is a generic internal user, SYSIBM. The schema objects are tables, indexes, views, packages, user-define data types, functions, triggers, stored procedures and aliases. Minimal set of privileges, user or group needed to create the table:

  1. CONNECT - database privilege to connect to the database
  2. CREATEIN - schema privilege to create objects inside schema
  3. CREATETAB - database privilege to create table inside the schema

The creator of the table has automatic CONTROL privilege over the table.

The minimal set of privileges, user or group needed to control the package:

  1. CONNECT - database privilege to connect to the database

  2. CREATEIN - schema privilege to create objects inside schema

  3. BINDADD - database privilege to create package

  4. BIND - object privilege to rebind existing package

The creator of the package has automatic CONTROL privilege on the package.

A Special Database Group Public

User and group definition in DB2 security are left to the control of the operating system or the external security facility. In addition, DB2 has one special PUBLIC group.

Listing of the user and group privileges, for an empty DB2 database:

db2 => select substr(GRANTOR,1,8) Grantor, substr(GRANTEE,1,8) Grantee, GRANTEETYPE T, \
db2 (cont.) => DBADMAUTH DBA, CREATETABAUTH CRTAB, BINDADDAUTH BIND, CONNECTAUTH CONN, \
db2 (cont.) => NOFENCEAUTH NOFNC,IMPLSCHEMAAUTH IMPLSCH, LOADAUTH LOAD from syscat.dbauth \
db2 (cont.) => where GRANTEE = 'PUBLIC'
 
GRANTOR  GRANTEE  T DBA CRTAB BIND CONN NOFNC IMPLSCH LOAD
-------- -------- - --- ----- ---- ---- ----- ------- ----
SYSIBM   PUBLIC   G N   Y     Y    Y    N     Y       N   
DB2INST1 PUBLIC   G N   Y     Y    Y    N     N       N 
SYSIBM   DB2INST1 U Y   Y     Y    Y    Y     Y       Y

The grantor gives a privilege and the grantee receives a privilege. In the example above, the instance owner (db2inst1) has granted DBADM authority (privilege) on a user level (DBADMAUTH=U), and has granted implicitly all the others database privileges.

The grantee PUBLIC holds Createtab, Bindadd, Connect,Implicit_Schema database privileges and Select privilege on the system catalog views. This privilege has been granted to PUBLIC during database creation.

Note that instance owner db2inst1 had granted to PUBLIC the same privilege set as user SYSIBM, only without Implict_Schema database privilege. The effective group rights for PUBLIC will be a combination of both, the PUBLIC group will hold Implicit_Schema database privilege.

The Implicit_schema database privilege provides that any user can create a schema implicitly by creating an object using the CREATE statement with a new, nonexistent schema name. SYSIBM becomes the owner of the implicitly created schema and PUBLIC is given the privilege to create objects in this schema. To remove any privilege from the PUBLIC, SYSIBM or DBADM, the privilege must be explicitly revoked.

The PUBLIC group has by default, the select right to 151 system views in the version 7.1.

The user SYSIBM granted implicit privileges, GRANTEETYPE of G to the PUBLIC group, meaning that PUBLIC will receive granted privileges and can grant to the others.

Directly granted (explicit) privileges, granted to the user are shown as GRANTEETYPE of U, meaning that the user received the privilege to the database object without the ability to grant to the others.

All users on the machine, by default belong to the PUBLIC group. Using that, any user can connect (Connect) to the database, create a table (Createtab), or execute the package bind (Bindadd) command.

Additionally, PUBLIC has implicitly granted SELECT right on the system catalog tables. Every member of the PUBLIC group has all privileges granted to the group.

The Public Group Research

For our test environment, we have a UNIX server, with Sun Solaris 2.7 operating system and DB2 UDB EEE database, version 7.1.

The following groups and users relevant to the DB2 database exist on the operating system:

$ cat /etc/passwd
db2fenc1:x:59555:103::/export/home/db2fenc1:/bin/ksh
db2inst1:x:59556:102::/export/home/db2inst1:/bin/ksh

$ cat /etc/group
db2fadm1::103:
db2iadm1::102:

User db2inst1 is the instance owner, and d2fenc1 is the default fenced DB2 user.

We are going to attempt to connect a nonexistent user "ARTIST" to the local ARTIST DB2 database, using a local UNIX console:

# db2 connect to artist user artist using artist

SQL1403N The username and/or password supplied is incorrect. SQLSTATE=08004

We cannot connect since user does not exist. Next, we will create the group db2user for regular users, and a new user with the name ARTIST and password xxx123.

   # groupadd db2user
   # useradd -g db2user -G db2user -d /home/artist artist
   # passwd artist 

The situation on the operating system side now looks like this:

$ cat /etc/group
db2fadm1::103:
db2iadm1::102:
db2user::104:

$ cat /etc/passwd
db2fenc1:x:59555:103::/home/db2fenc1:/bin/ksh
db2inst1:x:59556:102::/home/db2inst1:/bin/ksh
artist:x:40000:104::/home/artist:/bin/ksh

We are ready to connect to the Unix machine with the newly created user ARTIST for the first time.

# su - artist
Password:
$ id 
uid=40000(artist) gid=5555(db2user)groups=104(db2user) 

We will set the normal DB2 profile for user ARTIST, with a path to the DB2 commands and then try to connect to the DB2 database.

$ db2ilist
artist

$ db2 connect to artist user artist using xxx123

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = ARTIST
 Local database alias   = ARTIST

db2 =>  LIST DATABASE DIRECTORY
 
 System Database Directory
 
 Number of entries in the directory = 1
 
Database 1 entry:
 
 Database alias                  = ARTIST
 Database name                   = ARTIST
 Local database directory        = /data/artist/node0/artist
 Database release level          = 9.00
 Comment                         = ARTIST test database 
 Directory entry type            = Indirect
 Catalog node number             = 0

Viewing a table definition for the schema ARTIST:

db2 => list tables for schema artist

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------
  0 record(s) selected.

The user ARTIST has no defined objects. Because the PUBLIC group is enabled, the user ARTIST will have all of the rights belonging to PUBLIC. He can connect, and select from the database catalog views:

db2 => select count(*) from syscat.tables

1
-----------
        151

  1 record(s) selected.

The Actual authorities and database level privileges for the user ARTIST are as follows:

db2 => get authorizations

 Administrative Authorizations for Current User

 Direct SYSADM authority              = NO
 Direct SYSCTRL authority             = NO
 Direct SYSMAINT authority            = NO
 Direct DBADM authority               = NO
 Direct CREATETAB authority           = NO
 Direct BINDADD authority             = NO
 Direct CONNECT authority             = NO
 Direct CREATE_NOT_FENC authority     = NO
 Direct IMPLICIT_SCHEMA authority     = NO
 Direct LOAD authority                = NO

 Indirect SYSADM authority            = NO
 Indirect SYSCTRL authority           = NO
 Indirect SYSMAINT authority          = NO
 Indirect DBADM authority             = NO
 Indirect CREATETAB authority         = YES
 Indirect BINDADD authority           = YES
 Indirect CONNECT authority           = YES
 Indirect CREATE_NOT_FENC authority   = NO
 Indirect IMPLICIT_SCHEMA authority   = YES
 Indirect LOAD authority              = NO

The user has the CREATETAB, BINDADD, CONNECT privileges and IMPLICT_SCHEMA authority.

db2 => list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- ------------------
  0 record(s) selected.

The user has no tables and the user schema does not exist on the system.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1

We are going to create a new schema for the user ARTIST:

db2 => create schema artist authorization xxx123
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0552N  "ARTIST" does not have the privilege to perform operation "CREATE
SCHEMA".  SQLSTATE=42502

User ARTIST cannot create a schema object, but can create a table. A newly created table will be created in his new implicitly created schema:

db2 => CREATE TABLE test (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB20000I  The SQL command completed successfully.
db2 => list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TEST                            ARTIST          T     2003-09-06-17.20.17.396000
  1 record(s) selected.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1
ARTIST     SYSIBM     ARTIST  > new schema implicitly created

The new schema implicitly created for the user ARTIST exists in the database.

We saw that the user could not explicitly create a schema, however, by creating a table he will succeed in implicitly creating a database schema. The user can also create a table in another, new schema. For example:

db2 => CREATE TABLE noone.test (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB20000I  The SQL command completed successfully.

The operation is successful. We have succeeded to create implicitly a new database schema using PUBLIC grants.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1
VIP333     DB2INST1   DB2INST1
ARTIST     SYSIBM     ARTIST  > new schema explicitly created
NOONE      SYSIBM     ARTIST  > new schema implicitly created

The user NOONE does not exist on the operating system but we have succeeded in creating the additional schema with a new object in that schema.

The user ARTIST has the ability to change his current schema to the schema of another user, or list tables from another schema:

db2 => set schema vip333
DB20000I  The SQL command completed successfully.

db2 => list tables for schema vip333

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- ----------------------
PROTO                           VIP333           T     2001-05-08-21.33.31.886865
WELLA                           VIP333           T     2001-05-08-21.33.32.246662


db2 => select * from vip333.wella
SQL0551N  "ARTSIT" does not have the privilege to perform operation "SELECT" on
object "VIP333.WELLA".  SQLSTATE=42501

The user cannot look inside the other data, but he can select information from the catalog tables regarding other database users. Sometimes, just reading activity for a user from the catalog tables can be very valuable information. This is a situation we would like to avoid.

PUBLIC is just public. It was created with the intention of helping everyone, to provide as much database information as possible and to focus end users on business logic. It is like good buddy, keeping a door open for visitors. However, do we want visitors to walk in and out of a business critical, high cost database, showing only a badge given by system administrators? We cannot stop them, but we can limit their view.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved