© 2001 International Business Machines Corporation. All rights reserved.

Reprinted with permission from DB2 Magazine .

Let's face it: The topic of database security just doesn't turn heads the way benchmarking world records and reports of ever-shorter downtimes do. When was the last time you read a scintillating article about security tokens and encryption? But security breaches do turn heads - and can undermine customer confidence, as last year's well-publicized thefts of credit card numbers from a few e-businesses showed. Even if it's not the most exciting topic, security is a vital consideration for any business that uses a database management system. And, as more businesses participate in the e-space, it becomes particularly important to separate private from public data.

For more information about DB2 UDB security features, see the DB2 Administration Guide .

The database system at any given company probably collects, stores, and analyzes millions of rows of information ranging in nature from public to private. With all that responsibility, the database must enable DBAs to grant and restrict access appropriately. And it must offer ways to keep unauthorized users from accessing classified data.

But sometimes, database security information is hard to come by or difficult to understand. Although you often hear about how scalable and robust DB2 Universal Database (UDB) is, how often do you hear details about DB2's security features?

Because securing data is one of the most important DBA responsibilities, you shouldn't try to learn about database security by trial and error. Securing your database involves:

• Preventing unauthorized access to classified data by anyone without a business need to know
• Preventing unauthorized users from committing mischief through malicious deletion or tampering of data
• Monitoring user access of data through auditing techniques

In this article, I'll walk you through the security features in DB2 UDB v.7.1 for Windows, Unix, and OS/2 and describe some internal controls that can help you maximize your security.

Authentication

One of the most basic concepts in database security is authentication, which is quite simply the process by which a system verifies a user's identity. A user can respond to a request to authenticate by providing a proof of identity, or an authentication token.

You're probably already familiar with this concept. If you have ever been asked to show a photo ID (for example, when opening a bank account), you have been presented with a request for authentication. You proved your identity by showing your driver's license (or other photo ID). In this case, your driver's license served as your authentication token.

Figure 1. DB2 authorization roles

Despite what you see in the movies, most software programs can't use futuristic systems such as face recognition for authentication. Instead, most authentication requests ask you to provide a user ID and a password. Your user ID represents your claim to being a person authorized to access the environment, and the password provides your authentication. Of course, this kind of authentication assumes that your password is well protected and you are the only person who knows it.

User authentication is completed by a security facility outside DB2, often part of the operating system or a separate product. In fact, security is not just a database issue; operating system vendors spend a great deal of time, money, and care to ensure that their products are secure. However, some operating systems, including Microsoft Windows 95 and 98, have no native security mechanism. If you use an operating system that lacks security mechanisms, you can configure your environment to rely on the DB2 server on a more secure system to provide the security. For example, you can use trusted client options, which I'll discuss more later in the article. (For more information, refer to the DB2 Administration Guide .)

You can also use third-party products, such as Distributed Computing Environment (DCE) Security Services defined by the Open Group, to add a layer of security to your environment. DB2 can coordinate these external security efforts with its security initiatives to protect a transaction or analytic environment.

Once a user is successfully authenticated, DB2 makes note of the user's identity and other relevant security information, such as the user's group list. The user must be identified to DB2 using a SQL authorization name or authid, which can be the same as the user ID or a mapped value. This connection information is kept for the duration of the user's connection.

Authentication options

Because authentication can be handled either by the operating system or by third-party products, DB2 offers different authentication options that you can set in the database manager configuration (dbm cfg) file using the AUTHENTICATION parameter. DB2 uses this parameter to determine how and where authentication should take place.

The many settings for the dbm cfg AUTHENTICATION parameter can be logically grouped into four distinct categories: Server, Client, DCE, and Kerberos.

Server authentication . This group offers two main options:

• SERVER , the default security mechanism, specifies that the authentication is to occur on the server using the server's operating system. If a user ID and password are specified during the connection, DB2 calls an operating system function to validate the submitted user ID and password. (In a Windows-based environment, a user ID is often referred to as a username. The combination of a username and password is often referred to as a user account.)
• SERVER_ENCRYPT is essentially the same as the default option, except that the password passed from client to server is encrypted. DB2 uses 56-bit single DES encryption of the password and the Diffie-Hellman algorithm to generate a key for the encryption algorithm at connect time. The RSA BSAFE toolkit provides this support.

If a server receives requests from trusted and untrusted clients, the TRUST_ ALLCLNTS and TRUST_CLNTAUTH options allow trusted clients to gain access using client authentication while untrusted clients must provide a password for successful authentication. Please see the B2 Administration Guide for details.

DCE authentication options. Some administrators choose to implement DCE security services because DCE provides centralized administration of users and passwords, no transmission of clear text passwords and user IDs, and a single sign-on for users. DB2 provides integrated support for DCE security services using third-party DCE products. You can choose between two settings:

• DCE specifies that the user is authenticated using DCE security services. A DB2 client that has logged into DCE can obtain an encrypted "ticket" it can use to authenticate itself with the DB2 server.
• DCE_SERVER_ENCRYPT specifies that the server will accept DCE tickets or user ID and encrypted password as proof of authentication, as selected by the DB2 client.

Kerberos authentication options . Kerberos is a new authentication mechanism added to DB2 UDB v.7.1 as part of its tight integration with Microsoft Windows 2000.Using Kerberos security, DB2 authentication can be accomplished with single sign-on facilities. Once authenticated, a user will not be challenged again by any server that exists in the Kerberos environment. This method of authentication can only be used when both the DB2 client and the DB2 server are on Windows 2000.

DCE and Kerberos use essentially the same underlying technologies. When a client logs into the Kerberos security environment, the DB2 client can obtain an encrypted Kerberos ticket for use in authenticating itself with the specified DB2 server.

You can choose between two settings:

• KERBEROS specifies that the user is to be authenticated using Kerberos security services only.
• KRB_SERVER_ENCRYPT specifies that the server is to accept either Kerberos tickets or user ID and encrypted password as proof of authentication, as selected by the client.

Authorization

An authenticated user goes through the second DB2 layer of security, authorization. Authorization is the process through which DB2 obtains information about the authenticated DB2 user, including which database operations that user may perform and which data objects that user may access.

Your driver's license is a perfect example of an authorization document. Though it can be used for authentication purposes, it also authorizes you to drive a certain class of car. Furthermore, the type of authorization you have gives you more or fewer privileges as far as driving a vehicle goes.

In Ontario, Canada, for example, a class G license gives you the privilege to drive a car practically wherever and whenever. The class G license sits at the top of the hierarchical structure of authorizations for driving cars. The lower-level G2 license allows a user to drive a car at any time of day, but with certain restrictions. (For example, a user with this authorization scheme is not allowed to drive after consuming any alcoholic beverages whatsoever.) Below the G2 license is the G1 license, which includes many restrictions (for example, the driver must be accompanied by a class-G licensed passenger, may not drive on highways, or drive after dark).

Your driver's license authorizes you to drive a vehicle and limits access to certain "objects" (for example, you cannot drive on a highway with a G2 license). DB2 works in much the same way.

Authorization can be broken down into two distinct categories: authorities and privileges.

Authorities, . Authorities provide a method of grouping privileges and control over higher-level database manager maintenance and utility operations. Database-specific authorities are stored in the database catalogs; system authorities are associated with group membership and are stored in the database manager configuration file for a given instance. DB2 has four predefined authority levels: SYSADM, SYSCTRL, SYSMAINT, and DBADM. Figure 1 illustrates the hierarchical scheme of the predefined authorization levels used in DB2. SYSADM, SYSCTRL, and SYSMAINT operate at the instance level and have a serverwide scope. Each has its own grouped privileges and authorized-access rules (much like the class G license). These authorities are defined in the database manager configuration file for each instance. The DBADM authorization level is linked to a specific database within the server instance and is automatically granted to a user who creates a database. A DBADM has all possible grouped privileges on the database and all the objects within that database. By default, a SYSADM has all possible grouped privileges for the entire system, including databases (it has implicit DBADM authority).

DB2 uses more than one vertical authorization stream. With each user request, there may be more than one authorization check, depending on the objects and operations involved. Authorization is performed using DB2 facilities. DB2 system catalogs contain a record of the privileges associated with each authorization name. The authorization name of an authenticated user, and the groups to which the user belongs, are compared with their recorded privileges. Based on this comparison, DB2 decides whether or not to allow the requested access.

Figure 2. An error message returned to a user not authorized to perform a specific command

For example, Figure 2 shows the results of entering a command that requires a user to have a particular authorization privilege.

The DB2 security mechanism blocks the TESTING user ID because it knows that this user does not have the authorization to perform such a command. In this case, it is obvious that TESTING is not a SYSADM, since that authority level is required to execute the command shown in Figure 2.

You gain SYSADM, SYSCTRL, and SYSMAINT authorities in DB2 by being a member of the group specified by the SYSADM_GROUP, SYSCTRL_GROUP, and SYSMAINT_GROUP database manager configuration parameters, respectively.

For a complete listing of all the authorization privileges granted to a user belonging to any of the DB2 predefined authorization groups, refer to the DB2 Administration Guide .

Privileges . A privilege defines a single permission for an authorization name, enabling a user to modify or access database resources. Privileges are stored in the database catalogs. Whereas authority groups have a predefined group of privileges that are implicitly granted to their members, privileges are single permissions.

Figure 3: Sample privileges available in DB2

DB2 can take advantage of the user groups maintained by an operating system's security feature. Groups help reduce the database system's total cost of ownership by allowing the database administrator to assign privileges to groups.

For example, if you wanted to allow the privileges of creating tables (CREATETAB) and connecting to the database (CONNECT) to 50 users, it would be easier to create a group and add the privileges to it than to explicitly grant each privilege to each individual user. And, anytime you needed to add or revoke privileges, you could do it once for the group and all the members of the group would be affected.

In general, group membership is applicable for dynamic SQL and nondatabase object authorizations (such as instance level commands and utilities). Dynamic SQL is SQL that is generated ad hoc, or on the fly. Group membership (except the PUBLIC group) is not applicable for static SQL. Static SQL is known to DB2 before execution and the access plan for the SQL has already been generated (by the DB2 optimizer) and stored as a package in the catalogs.

Each privilege can be granted or revoked from a specific authid, a specific group (PUBLIC) to which all users automatically belong, or more than one group. Figure 3 gives an example of some of the different privileges available in DB2. (Note: In the Tables and Views group, the ALTER, INDEX, REFERENCES, and UPDATE Column privileges are only available for tables.) For an in-depth discussion of these privileges, see the DB2 Administration Guide .

Access to all sorts of operations and objects is controlled through privileges. You must have the privilege to do something before DB2 lets you do it.

Access control methods

Now that you understand how DB2 manages authentication and authorization, let's look at the framework that DB2 provides to combine these options for strong access control. Access control methods are used to create subsets of the contents of information, so that the user can only see and access data that is relevant to their needs. You can use a number of different access control methods in DB2. Access controls exists for everything you do in a database.

Consider the row of a view in Table 1. You may want all users in the company to have access to the L_Name, F_Name, Phone, email, and Title columns. However, to minimize the risk of email spam, you may want users accessing a company directory over the Web to only see the L_Name, F_Name, and Phone columns. Finally, employees who are part of the Human Resources department should have access to all the rows in this table. Access controls provide a very important framework in securing your data in DB2 and providing row-level access to your data.

Access controls using packages . Packages, collections of information that pertain to one or more SQL statements, are the primary access control point for SQL within DB2. Packages include such information as the access plan generated by the optimizer and the authorization model. Any statement issued to the database engine is related to a specific package.

When a package is created, it is bound to the database with specific privileges. The person who creates a package must have the privileges required to execute all the static SQL statements in the package. Users who run the package must also have the EXECUTE privilege for the package, but they don't have to have the individual privileges for executing the static statements contained in it. For example, without the CREATETAB privilege, a user can't create a table in the database. However, a user with the privilege to run a package containing a static CREATE TABLE statement could create a table that way. Packages play a key role in controlling the access that users have to database objects.

Access control using views . Views are another major access control method to restrict low-level (also called row-level) access to data. Using a view, you can hide sensitive rows and columns of information that reside in the original table from SQL statements. You can give a user access to information by granting privileges on a view. Because these privileges are only for the view and do not affect the base table, the user's access is confined to the view, which is generated by creating a subset of the data contained in required table. The WITH CHECK option provides even more security because it will not let a particular SQL statement change rows that the user is not privileged to read in the view.

Table 1. A row of a view

L_Name	F_Name	Phone	e-mail	Title	Salary	Bonus	Total Salary
Godfrey	Mike	2447337	mk@money.com	Mgr	$23,000	$122,000	$145,000

Access control using triggers . Using a trigger, you can create a more complex security mechanism that gets initiated whenever a certain event happens. An INSERT statement on a table is one example of an event that can be used to fire a trigger. Furthermore, the trigger can fire before or after a specified event, to provide even more rigorous security checks. If the user's statement fails to pass a security check in a trigger, an error can be signaled from within the body of the trigger to prevent the table from being modified.

Access control using the USER special register . DB2 provides a special register, called USER, which contains the user ID used to connect to the database for the current session. You can use the value stored in the special register in a view to make customizations for the view for a particular user. Using the special register, you can make the view, which is based on a table, look different from user to user. You can also use this technique with triggers and SQL statements.

Auditing features

DB2's auditing facility lets you maintain an audit trail of events that occurred within an instance. Successful monitoring of data access attempts and subsequent analysis can lead to improvements in the control of data access and the ultimate prevention of malicious or careless unauthorized access to the data. These recorded events can then be extracted into a report for analysis. Auditing is detailed in the DB2 Administration Guide .

Trust the database

DB2's many features and mechanisms let you trust the database engine with even your most secure and confidential data. When your data is stored and managed by DB2, you can be confident that your business can run in a scalable, robust, and secure environment. And, if you're in charge of database security, that means a better night's sleep for you.

About the author

Paul Zikopoulos is a database specialist with the DB2 Sales Support team. He has written many magazine articles and books about DB2. Most recently, Paul coauthored the books A DBA's Guide to Databases on Linux (Syngress Media, 2000) and DB2 for Dummies (IDG Books, 2000). Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at paulz_ibm@yahoo.com .