Saturday, January 12, 2013

Restrict users to create implicit databases like DSN00001 ...

Problem Definition: Application developer can create their own tables taking away controls from DBA.

Explanation: Recently while analyzing the Catalog table space we found many tables and databases created by application developers for their testing purpose. They are sitting on the space pool that the DB2 catalog is using. This is a wired situation from a DBA point of View.

We will discuss how to prevent application users to create a table/database in DB2 environment.

How it is possible: In DB2 V9, IBM introduced a new feature of implicit Database creation. It means if you try to create a table in DB2 without mentioning table space and database, DB2 will take care of that by implicitly creating them.

On a CREATE TABLE statement, if you do not specify a database name, DB2 will use an existing implicitly created database. If an implicitly created database does not exist, DB2 creates one using the naming convention of DSNxxxxx. The DSNxxxxx values can range from DSN00001 to DSNnnnnn, where nnnnn is the maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000.

To remind you that in Pre-V9, the database is default to DSNDB04 while creating the new table.

Just imagine one Database of each table. It's really difficult task for DBA to relate the objects logically and maintain them. More over they will take the space from the storage group where DB2 catalog resides.

IBM has taken this steps for future to simplify the object creation without providing table space and database name which are not familiar to users other than DBAs. it may be beneficial for certain tools or SAP environment where lots tables to be created while setting up the environment.

How to prevent the users to create the implicit databases:

Revoke the Create object privilege on database DSNDB04 from PUBLIC. You can't restrict SYSADM users in this case.
Restrict the cataloging of high level qualifier containing DSNxxxxx to register the dataset in VTOC
Alter the default maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB to 1

Prakash Singh
IBM Certified DB2 DBA

No comments: