2 Security_db2-cert7302-a4.pdf

DB2 9 Fundamentals exam 730 prep, Part 2:

Security

Skill Level: Intermediate

Graham G. Milne (gmilne@ca.ibm.com)

I/T Specialist DB2 UDB

IBM Canada

20 Jul 2006

This tutorial introduces the concepts of authentication, authorization, and privileges

as they relate to DB2® 9. It is the second in a series of seven tutorials designed to

Thinking about seeking certification on DB2 fundamentals (Exam 730)? If so, you've

landed in the right spot. This series of seven DB2 certification preparation tutorials

covers all the basics -- the topics you'll need to understand before you read the first

exam question. Even if you're not planning to seek certification right away, this set of

tutorials is a great place to start getting to learn what's new in DB2 9.

About this tutorial

In this tutorial, you'll learn about DB2 9 security features, including DB2 9

authentication, authorization, and privileges.

This is the second in a series of seven tutorials you can use to help prepare for the

DB2 9 Fundamentals exam 730. The material in this tutorial primarily covers the

To understand the concepts described in this tutorial, you should already have a

basic knowledge of database concepts and an understanding of operating system

security features.

System requirements

The examples in this tutorial are specific to DB2 9 running on a Windows™

operating system (with native security features). However, the concepts and

information provided are relevant to DB2 running on any distributed platform.

You do not need a copy of DB2 9 to complete this tutorial. However, you will get

more out of the tutorial if you download the free trial version of IBM DB2 9 to work

along with this tutorial.

3. Created a new group on the machine on which DB2 was installed. In this

tutorial, the group ID db2grp1 is used.

4. Created a second user ID on the machine on which DB2 was installed. In

this tutorial, for this purpose we will use the user ID test1. Note that the

test1 user is not a member of the Administrators group.

Section 2. DB2 security

Aspects of database security

Database security is of utmost importance today. Your database might allow

customers to purchase products over the Internet, or it can contain historical data

developerWorks® ibm.com/developerWorks

• Data that a user is allowed to read and/or alter

• Database objects a user is allowed to create, alter, and/or drop

DB2 security mechanisms

There are three main mechanisms within DB2 that allow a DBA to implement a

database security plan: authentication, authorization, and privileges.

Authentication is the first security feature you'll encounter when you attempt to

access a DB2 instance or database. DB2 authentication works closely with the

security features of the underlying operating system to verify user IDs and

passwords. DB2 can also work with security protocols like Kerberos to authenticate

users.

and/or groups. Privileges help define the objects that a user can create or drop.

They also define the commands that a user can use to access objects like tables,

views, indexes, and packages. New to DB2 9 is the concept of label-based access

control (LBAC), which allows more granular control of who can access individual

rows and/or columns.

To prepare for the next section of the tutorial, you will need to create a database

within the DB2 instance. Make sure that the %DB2INSTANCE% variable is still set to

DB2, and then create the sample database using the command db2sampl drive

, using the name of the drive where you want to create the sample. For the

examples in this tutorial, you'll create the sample database on your D: drive, as

ibm.com/developerWorks developerWorks®

database at any potential data access point. The concepts of clients, servers,

gateways, and hosts are particularly important when dealing with DB2

authentication.

The diagram below illustrates a basic client-server-host configuration.

The database server is the machine (or machines in a partitioned database system)

on which the database physically resides. The DB2 database clients are machines

that are configured to run queries against the database on the server. These clients

can be local (reside on the same physical machine as the database server) or they

can be remote (reside on separate machines).

If the database resides on a mainframe machine running an operating system like

developerWorks® ibm.com/developerWorks

When DB2 authenticates

DB2 authentication controls the following aspects of a database security plan:

• Who is allowed access to the instance and/or database

• Where and how a user's password will be verified

It does this with the help of the underlying operating system security features

whenever an attach or connect command is issued. An attach command is used to

connect to the DB2 instance, whereas a connect command is used to connect to a

database within a DB2 instance. The examples below walk you through the different

ways that DB2 will authenticate a user issuing these commands. These examples

use the default authentication type of SERVER in the database manager

used and is assumed to be already verified by the operating system.

db2 connect to sample user test1 using password

Database Connection Information

Database server = DB2/NT 9.1.0

SQL authorization ID = TEST1

Local database alias = SAMPLE

Here, authentication is done explicitly. The user test1 with the password password is

verified by the operating system. User test1 is successfully connected to the sample

database.

db2 connect to sample user test1 using password new chgpass confirm chgpass

ibm.com/developerWorks developerWorks®