DB2 UDB security, Part 1: Understand how user and group accounts interact with DB2 UDB

Granting database access, authorities, and privileges to users and groups is one of the primary means of ensuring the security of your data. This article describes the different user and group accounts that are needed to install and work with IBM® DB2® Universal Database™ for Linux®, UNIX®, and Windows® (DB2 UDB) Version 8.2. It also introduces the DB2 UDB security model, including user authentication, user and group authorization, and super users.

Ted J. Wasserman (tedwas.ibm@gmail.com), Database Consultant, IBM

Ted J. Wasserman's photoTed J. Wasserman is a database consultant at the IBM Silicon Valley Laboratory in San Jose, California. Ted works on the DB2 Business Partner Technical Enablement team, where he specializes in helping IBM Business Partners migrate their applications and databases to DB2. Ted has a master's degree in computer science, as well as a bachelor's degree in computer science from Queen's University in Kingston, Ontario, Canada.



Raul F. Chong (rfchong@ca.ibm.com), DB2 UDB Specialist, IBM

Raul F. Chong's photoRaul F. Chong is a database specialist from the IBM Toronto Laboratory and works in DB2 UDB Technical Support. Raul has worked for eight years at IBM, and has extensive knowledge in DB2 UDB. Raul has written many articles and is the lead author of the book Understanding DB2: Learning Visually with Examples, and co-author of the book DB2 SQL PL (2nd edition). You can reach him at rfchong@ca.ibm.com.


developerWorks Contributing author
        level

25 August 2005

Also available in Vietnamese

Introduction

Users new to DB2 UDB usually have some questions about the user and group accounts that are required for a DB2 UDB installation and operating environment. In this article, you will learn about DB2 UDB's primary interactions with users and groups. The article describes the user account you'll need when you install DB2 UDB on Linux, UNIX, and Windows operating systems, as well as the additional accounts you'll need in order to run the various DB2 UDB processes and services. It also reviews the DB2 UDB security model, including user authentication, user and group authorization, and super users. This article applies to DB2 UDB for Linux, UNIX, and Windows, Version 8.2; however, many of the sections are applicable to prior versions of DB2 UDB.


The DB2 UDB security model

The DB2 UDB security model consists of two main components: authentication and authorization. Figure 1 provides an overview of the DB2 UDB security model.

Figure 1. The DB2 UDB security model
A high level illustration of the DB2 UDB security model

Authentication

Authentication is the process of validating a supplied user ID and password using a security mechanism. User and group authentication is managed in a facility external to DB2 UDB, such as the operating system, a domain controller, or a Kerberos security system. This is different from other database management systems (DBMSs), such as Oracle and SQL Server, where user accounts may be defined and authenticated in the database itself, as well as in an external facility such as the operating system.

Any time a user ID and password is explicitly provided to DB2 UDB as part of an instance attachment or database connection request, DB2 UDB attempts to authenticate that user ID and password using this external security facility. If no user ID or password is provided with the request, DB2 UDB implicitly uses the user ID and password that were used to log in to the workstation where the request originated.

The actual authentication location is determined by the value of the DB2 UDB instance parameter AUTHENTICATION. The various authentication schemes include having users authenticated on the DB2 UDB server itself (using the server's security facility), on the client (allowing "single sign-on" access), a Kerberos security facility, or a user-defined Generic Security Service (GSS) plug-in. Additional authentication options include the ability to encrypt user names and passwords, as well as data, as they travel across the network between client and server. The value you choose for the AUTHENTICATION parameter depends on your particular environment as well as local security policies. For a full description of the different authentication schemes, refer to the DB2 UDB Documentation. (See Resources.)

For example, Figure 1 shows the connection statement used by a user called bob to connect to the finance database using the password bobpsw. There are six steps that occur in the authentication process:

  1. The CONNECT statement is passed to the DB2 UDB server.
  2. If a security plug-in has not been explicitly configured, the default plug-in is used. When the AUTHENTICATION parameter of the instance containing the finance database is set to SERVER (the default setting), the user ID and password provided in the connection request are authenticated by the security facility on the DB2 UDB server. The default plug-in sends the user ID and password to the operating system for validation.
  3. The operating system confirms whether the bob/bobpsw combination is valid or not, and returns this information to the security plug-in.
  4. The security plug-in invokes the DB2 UDB security mechanism which queries the DB2 UDB catalog tables for user bob to see if a user by that name has been granted the CONNECT privilege for that database. By default, the CONNECT privilege is granted to PUBLIC which means that any user that authenticates successfully can connect to the database.
  5. The DB2 UDB security mechanism validates the user bob, or returns an error.
  6. The security plug-in returns a success or failure message to the user. If a user is not able to authenticate successfully, DB2 UDB refuses the connection request and returns an error message to the client application, similar to the following:
Listing 1. The message returned to the application by DB2 UDB when user authentication fails
SQL30082N  Attempt to establish connection failed with security 
reason "24" ("USERNAME AND/OR PASSWORD 
INVALID").  SQLSTATE=08001

An entry similar to the following also appears in the DB2 UDB diagnostic log (db2diag.log) on the DB2 UDB server:

Listing 2. The message in the DB2 diagnostic log when user authentication fails
2005-07-09-16.18.33.546000-240 I729347H256        LEVEL: Severe
PID     : 3888                 TID : 604
FUNCTION: DB2 Common, Security, Users and Groups, secLogMessage, probe:20
DATA #1 : String, 44 bytes
check password failed with rc = -2146500502

On Windows, the diagnostic log can be found in the Instance home directory, which by default is in C:\Program Files\IBM\SQLLIB\DB2 . On UNIX, by default, it is located in <DB2PATH>/DB2/db2dump, where <DB2PATH> is the path of the instance owner.

If you ever encounter these messages, ensure that the user or application connecting to the database has provided a valid user ID and password. This user ID and password must exist in the facility where user authentication is set to take place (determined by the AUTHENTICATION parameter of the target DB2 UDB instance).

Authorization

Authorization is the process of determining access and privilege information about specific database objects and actions for a supplied user ID. DB2 UDB stores and maintains user and group authorization information internally. Each time you submit a command, DB2 UDB performs authorization checking to ensure that you have the correct set of privileges to perform that action.

Privileges can be granted to specific users or to groups of users. Again, both the user and group definition themselves are defined outside of DB2 UDB. Users that are a member of a group automatically inherit the group's privileges. Specific privileges granted to a user take precedent over the privileges associated with any group(s) the user is a member of, and persist even if the user is later removed from the group(s). That is, explicit privileges granted to a user must be explicitly revoked.

Most database objects have a set of associated privileges that can be assigned to users and groups, using the SQL statements GRANT and REVOKE. For example, the SQL statement below grants the SELECT privilege on the table called ADM.ACCTABC to a user called bob:

GRANT SELECT ON TABLE ADM.ACCTABC TO USER BOB

Once this statement is issued, the user bob can submit SELECT statements referencing this table. Similarly, the following SQL statement revokes the INSERT privilege on a view called ADM.LEGERS from a group called clerks:

REVOKE INSERT ON VIEW ADM.LEGERS FROM GROUP CLERKS

You can only revoke a privilege that was previously granted. For detailed information about all the various database object privileges that can be granted to users and groups, refer to the DB2 UDB documentation (see Resources).

It is important to note, especially for new DB2 UDB users, that the GRANT statement does not verify the existence of a user or group account in the external facility. This means that privileges can be granted to non-existent user and group accounts with the information being stored in the database. This causes a false impression that user and group accounts can be defined within DB2 UDB. For example, if you issue the following statement while connected to the finance database:

GRANT SELECT ON TABLE ADM.TAXCODE TO USER XYZ

where xyz is any string that does not map to an existing user in the external security facility, then DB2 UDB will show xyz in its GUI tools or in some of the catalog tables, as is illustrated in Figure 2. This does not mean that a user called xyz exists or has been created.

Figure 2. Table privileges after granting privileges to an undefined user
The Table Privileges window after granting privileges to an undefined user

The bottom of Figure 1 shows an example of an authorization scenario. The user called bob, who previously connected successfully, now attempts to perform a SELECT statement on the table ADM.TAXCODES. DB2 UDB looks in its catalog tables to see if this user has been granted permission to SELECT from this table. Since this privilege was previously granted to bob, DB2 UDB allows the SELECT statement to proceed.

If a user is not authorized to perform an operation against a specific object, DB2 UDB refuses the operation and returns an error message to the client application. For example, if the user bob tried to INSERT a row into the ADM.TAXCODES table, but did not have sufficient privileges to do so, the following error message would be returned:

Listing 3. The message returned by DB2 UDB when user authorization fails
DB21034E  The command was processed as an SQL statement because it 
was not a valid Command Line Processor command.  During SQL 
processing it returned:
SQL0551N  "BOB" does not have the privilege to perform 
operation "INSERT" on object "ADM.TAXCODES".  
SQLSTATE=42501

If you ever encounter similar types of messages, ensure that the user ID provided to connect to the database has the appropriate privileges to perform the target operation. The user must be explicitly granted the privilege, be part of a group that was granted the privilege, or be a super-user.

Super Users

Certain groups of users can be designated as having special instance and database authorities. DB2 UDB defines a hierarchy of super user authorities (SYSADM, SYSCTRL, SYSMAINT, SYSMON), each with the ability to perform a subset of administrative operations such as creating a database, forcing users off a system, and taking a database backup. For a full discussion of the authority levels, refer to the DB2 UDB documentation (see Resources).

You can configure instance authorities using the associated instance-level parameters (SYSADM_GROUP, SYSCTRL_GROUP, SYSMAIN_GROUP, SYSMON_GROUP). Each parameter can be set to the name of the group of users (defined outside of DB2 UDB) who can have that authority.

For example, if you define a group called snrdba which contains all the senior DBA user accounts, you can make all of these users SYSADM super users by setting the value of the SYSADM_GROUP instance parameter to the value snrdba, using the following commands:

Listing 4. Updating the SYSADM_GROUP instance parameter
	UPDATE DBM CFG USING SYSADM_GROUP snrdba
	db2stop
	db2start

All users in the snrdba group would then have all the privileges associated with the SYSADM authority level and thus be able to perform all administrative tasks requiring that authority level.

Defining authorities in this way allows you to discriminate between DB2 UDB administrators and system/network administrators. For example, perhaps a DBA is required to have full administrative authority over the DB2 UDB instance, but not over the local machine or network. In this situation, the DBA's user account can be added to a group that does not have full access rights to the system/network, but can have full access rights to the DB2 UDB instance, by specifying that group name in the SYSADM_GROUP instance parameter.

During a default DB2 UDB installation on Windows, the values of these instance-level super-user authority parameters (SYSADM_GROUP, SYSCTRL_GROUP, SYSMAIN_GROUP, SYSMON_GROUP)) default to NULL. This means that super-user authority is granted to any valid account that belongs to the local Administrators group. We highly recommend explicitly changing the value of these parameters to valid group names in order prevent unauthorized access. On Linux and UNIX installations, this is not as big a concern, since a NULL value defaults to the primary group of the instance owner, which by default after an installation only contains the user ID of the instance owner.

A set of database-level authorities can also be assigned to users. Database authorities are granted using the standard SQL GRANT and REVOKE statements. More information about these database authority levels can be found in the DB2 UDB documentation (see Resources.

DB2 UDB system commands such as db2, db2ilist, db2start, db2stop, db2iupdt, and so on, are operating system executables. Therefore, the security mechanism to run these commands is based on the operating system permissions of the files. The permissions of these files are set at DB2 UDB installation time.


DB2 UDB user and group account naming rules

In DB2 UDB, user and group accounts must conform to naming rules described in Tables 1 and 2. These restrictions are in addition to any restricitions in effect in the facility where the accounts are defined.

Table 1. Limits and restrictions by platform
LimitWindowsLinux/UNIX
Group Name LengthUp to 30 charactersUp to 30 characters
User Name LengthUp to 30 characters (1) (2)Up to 8 characters
CaseCase insensitiveLowercase only

(1) Windows NT®, Windows 2000®, Windows XP® and Windows Server® 2003 currently have a practical limit of 20 characters.
(2) When not using Client authentication, non-Windows 32-bit clients connecting to Windows NT, Windows 2000, Windows XP and Windows Server 2003 with user names longer than 8 characters are supported when the user name and password are specified explicitly.

Table 2 shows the naming restrictions for all platforms.

Table 2. Naming restrictions for all platforms
RuleValue
Reserved words not allowedUSERS, ADMINS, GUESTS, PUBLIC (3), LOCAL, or any SQL reserved word
Names cannot begin withIBM, SQL, SYS, a number, or an underscore character
Characters not allowedAccented characters
Characters allowed (4)A through Z (When used in most names, characters A through Z are converted from lowercase to uppercase.)

0 through 9

! % ( ) { } . - ^ ~ _ (underscore) 7 @, #, $, \ (backslash) and space

(3) DB2 UDB internally uses a pseudo-group called PUBLIC, which privileges can be granted to and revoked from. PUBLIC is not actually a group defined in the external security facility. It is a way to assign privileges to any user who successfully authenticates.
(4) There are other special characters that might also work, depending on your operating system and where you are working with DB2 UDB. However, to avoid inconsistencies and potential problems, do not use these other special characters when naming objects in your database.

Installing DB2 UDB with the default user IDs (for example, db2admin) and providing a weak password (or none at all) can put your system at risk. Many computer viruses, worms, and trojan horses are designed to exploit weak passwords. For instance, many of these programs try to use common passwords such as "password," "123456," "111111," "db2admin," etc. to gain access to the system. It is therefore important to not trivialize your passwords.

Passwords are also very important when authenticating users. For example, on Linux and UNIX operating systems, undefined passwords are treated as NULL. Any user without a defined password is considered to have a NULL password. From the operating system's perspective, this is a match and the user is validated and able to connect to the database.


User and group accounts created by and required for a DB2 UDB installation

Partitioned database environments

In a partitioned database environment using the DB2 UDB data partitioning feature (DPF), each partition of the database must have the same set of users and groups defined. If the definitions are not the same, a user may not be authorized to perform required actions on some partitions. Consistency across all partitions is recommended.

A DB2 UDB installation requires a user account with administrative rights to perform the installation. The specific privileges required by this user depend on the platform DB2 UDB is being installed on. By default, DB2 UDB creates several user and group accounts during an installation. These accounts are used to "own" and start the various DB2 UDB services and processes running on the server.

In this section we describe the user privileges required to perform a DB2 UDB installation in Windows, Linux, and UNIX environments. We also highlight the various user and group accounts created during a default DB2 UDB installation.

Required user and group accounts on Microsoft Windows operating systems

If you are installing DB2 UDB on Windows operating systems, you will require the following user accounts:

  • Installation user account
  • DB2 Administration Server (DAS) user account
  • DB2 UDB instance owner user account

Installation user account

Before running the DB2 Setup wizard, you need to define an installation user account. This account can be a local or domain user account. The account must belong to the Administrators group on the machine where the installation is taking place. When using domain accounts, the installation account must belong to the Domain Administrators group on the domain where the other setup user accounts are going to be created. You may also use the built-in LocalSystem account to run the installation for all products except DB2 UDB Enterprise Server Edition.

You can define the other user accounts prior to installation, or you can have the DB2 Setup wizard create them for you. All user account names must adhere to your system naming rules as well as to DB2 UDB naming rules.

DB2 Administration Server user account

A local or domain account is required for the DB2 Administration Server (DAS). The DAS is a special service that supports the GUI tools and assists with administration tasks. The DAS has an assigned user account that is used to start the service when DB2 UDB is loaded. In the DB2 Setup wizard, one of the screens (shown in Figure 3) prompts you to select a user account that can be used to start up the DAS service.

Figure 3. Specifying the DAS user account in the DB2 Setup wizard
DB2 Setup wizard asking about DAS configuration

You can create this user account before installing DB2 UDB and specify it in this screen, or you can have the DB2 Setup wizard create it for you. By default, the wizard creates a new account called db2admin (you can call it anything you like though). The DAS user account must also belong to the Administrators group on the machine where you will perform the installation. If you want to have the DB2 Setup wizard create a new domain user account, the user account you use to perform the installation with must have authority to create domain user accounts.

The following user rights are automatically granted to this account when it is created by the Setup wizard:

  • Act as part of the operating system
  • Debug programs
  • Create token object
  • Increase quotas
  • Lock pages in memory
  • Log on as a service
  • Replace a process level token

If you created or specified a different account, make sure that account also has the above user rights. To ensure these rights are set correctly, open the Windows Control Panel (Start > Settings > Control Panel) and double-click the Administrative Tools icon. Double-click the Local Security Policy icon. For each of the policies listed above, ensure that the user is included in the list of authorized users and groups. Supplying an incorrect password will cause a service to fail to load during DB2 UDB start-up, and potentially prevent you from performing certain operations.

During the installation, you can also instruct the DB2 Setup wizard to use this same account (the one you specified for the DAS) to own and start up all the other DB2 UDB services as well. To do this, make sure the "Use the same user name and password for the remaining DB2 UDB services" box is checked (see Figure 3). If you leave this box unchecked, the wizard will prompt you to select a user account to own and start the default instance created. All other DB2 UDB services will use the built-in Windows LocalSystem account to start up.

You should also ensure that the DAS user account has SYSADM authority on each of the DB2 UDB systems in your environment, so that it can start or stop other instances if required. By default, after installing DB2 UDB in the Windows environment, any user that is part of the Administrators group has SYSADM authority. The DAS service name is called DB2DAS - DB2DAS00.

DB2 UDB instance owner user account

A local or domain account is required to own and start up a DB2 UDB instance. You can create the DB2 UDB instance owner user account before installing DB2 UDB, or you can have the DB2 Setup wizard create it for you. If you want to have the DB2 Setup wizard create a new domain user account for you, when you perform the installation you must use a user account with the authority to create domain user accounts. The user account must also belong to the Administrators group on the machine where you will perform the installation. The following user rights are automatically granted to this account when created by the DB2 Setup wizard:

  • Act as part of the operating system
  • Debug programs
  • Create token object
  • Increase quotas
  • Lock pages in memory
  • Log on as a service
  • Replace a process level token

If you created or specified a different account, be sure to grant the above user rights to that account. To ensure these rights are set correctly, open the Windows Control Panel (Start > Settings > Control Panel) and double-click the Administrative Tools icon. Double-click the Local Security Policy icon. For each of the policies listed above, ensure that the user is included in the list of authorized users and groups. Supplying an incorrect password will cause a service to fail to load during DB2 UDB startup, and potentially prevent you from performing certain actions.

In a default Windows installation, an instance called DB2 is automatically created. You are able to create other instances or drop existing ones using the db2icrt and db2idrop utilties, respectively. These utilities can be found in the DB2PATH\sqllib\bin directory, where DB2PATH is the location where you installed DB2 UDB. To run these utilities, you must use a user account with local Administrator authority. If you provide a user account and password as parameters when you create the instance, that user account will be used to own and start the service. If you do not provide a user account and password, the LocalSystem account will be used instead. For example, the following command creates a new instance called devinst, and assigns the LocalSystem account to own and start up the instance process:

Listing 5. Creating a new DB2 UDB instance in Windows
db2icrt devinst

Changing account information

After installation, you are able to change the user account associated with each DB2 UDB service. Also, beginning in DB2 UDB Version 8.2, you can use the built-in LocalSystem Account (LSA) to start a DB2 UDB service. Using the LSA can be advantageous on systems where strict password policies are enforced, since the LSA is not associated with a password. For example, in some environments, users are required to change their passwords every two months, or their account is temporarily locked out. Such a policy would therefore require changing the password for the user account designated to start the DB2 UDB services. If the user account configured to start the services becomes locked out, or the password was changed for the user account, but not updated in the service startup properties, the services will fail to start.

Before switching any DB2 UDB services from a dedicated user account to the LocalSystem account, you should consider that the LocalSystem account cannot have access to LAN shares, because it cannot be authenticated on another computer. You must therefore ensure that your DB2 UDB system does not have any of its file resources on another machine.

Applications running under the context of the LocalSystem account (LSA) use local implicit DB2 UDB connections. If you are developing an application that will run under this account, be aware that DB2 UDB has restrictions on objects with schema names starting with "SYS". If your applications contain statements that create DB2 UDB objects, they should be written such that:

  • For static SQL, they should be bounded with a value for the QUALIFIER options other than the default one.
  • For dynamic SQL, the objects to be created should be explicitly qualified with a schema name supported by DB2 UDB, or the CURRENT SCHEMA register must be set to a schema.

Figure 4 shows a list of some of the DB2 UDB processes running on an active DB2 UDB system. As you can see, each process is associated with an owner (user name). For a description of all the processes associated with DB2 UDB, refer to "Everything You Wanted to Know About DB2 Universal Database Processes" (developerWorks, April 2003).

Figure 4. Viewing running DB2 UDB processes
Viewing running DB2 UDB processes

To change the user account being used to start a DB2 UDB service, open the Services panel (Start > Settings > Control Panel > Administrative Tools > Services). Right-click a DB2 UDB service to see its properties. Click the Log On tab at the top of the Service properties window (Figure 5). In this tab, you can specify the user account you would like the service to use when it is started, or you can specify the LSA. Click OK to save your selections and close the properties window for that service.

Figure 5. DAS Service Log On Properties window
DAS Service Log On Properties window

In general, you should use a separate, identifiable user account to start the DB2 UDB services. However, if user accounts are controlled more tightly in your environment, you have the option of using existing user accounts or using the LocalSystem account to start these services. Once an account has been set up to run a service, that account cannot be deleted or else the services associated with that account will fail to start. This is true even if you re-create the account using the same name. In such cases, you must manually go into the services panel as previously described and re-configure the user account used to start the service.

The DB2USERS and DB2ADMNS groups

Beginning in DB2 UDB Version 8.2, additional security was added to DB2 UDB in the Windows environment. A new option exists as part of the DB2 UDB installation to create two additional groups in the operating system, DB2USERS and DB2ADMNS. Once these groups are created, only user accounts that are members of these groups will have access to the DB2 UDB files on the system (including commands as well as user data files created by DB2 UDB).

These group names can be customized, but you should accept the default names if possible. If there is a conflict with existing group names, you will be prompted to change the group names. If you don't select this option during the original DB2 UDB installation, you can always enable it at a later time by running the db2secv82.exe program. This program can be found in the DB2PATH\SQLLIB\BIN\ directory, where DB2PATH is the location where DB2 UDB was installed. You should enable this option in order to secure your server to the greatest extent.

Figure 6 shows the result of having this additional security enabled. All DB2 UDB folders on the server's filesystem now require users to be a member of one of these two groups to have access to DB2 UDB folders and files.

Figure 6. Folder permissions when new Windows security is enabled
Folder permissions when new Windows security is enabled

Once you enable this extra security feature using the db2secv82.exe command, you have two options for backing it out:

  • Run the db2secv82.exe command again immediately WITHOUT making any additional changes to the system. If there have been any changes at all made to the system you must use the next option.
  • Add the EVERYONE group to the DB2ADMNS and DB2USERS groups.

When extended security is enabled, the DB2 UDB registry variable DB2_EXTSECURITY is set to YES. This tells DB2 UDB that additional security checking is required from its part. If you change the DB2_EXTSECURITY value to NO after the DB2ADMNS and DB2USERS groups have been created, the permissions to files and folders will still be owned by these groups; however, DB2 UDB will not perform additional security checking.


Required user and group accounts on Linux and UNIX operating systems

NIS/NIS+ considerations

If NIS/NIS+ or similar security software is used in your environment, you must manually create the required DB2 UDB user and group accounts before you install DB2 UDB. Refer to the NIS topic in the DB2 UDB documentation (see Resources) before you begin.

In Linux and UNIX operating systems, several user and group accounts are typically required to install and operate DB2 UDB:

  • Installation user account
  • DB2 Administration Server (DAS) user account
  • DB2 UDB instance owner user account
  • DB2 UDB fenced routine user account

By default, the DB2 Setup wizard creates these user and group accounts automatically during a DB2 UDB server installation. Alternatively, you can specify an existing user account during the installation process.

Installation user account

You must install DB2 UDB using the "root" account. This is the only user account with sufficient privileges to perform the installation.

Instance owner user account

A DB2 UDB instance is created in the instance owner's home directory. This user account controls all DB2 UDB processes and owns all file systems and devices used by the databases contained within the instance. The default user ID used for the DB2 UDB instance owner during a DB2 UDB installation is db2inst1, and the default group is db2iadm1. If the user name already exists, the DB2 Setup wizard appends a number from 1-99 to the default user name, until a user ID that does not already exist can be created. For example, if the users db2inst1 and db2inst2 already exist during a new DB2 UDB installation, the wizard creates the user db2inst3. If the user already exists, the wizard continues its search (db2inst4, db2inst5, and so on) until it finds an available user. This naming algorithm also applies to the creation of the fenced user account and DB2 administration server user account.

A good practice is to restrict the instance owner user account to be a member of only the instance owner group, and not to use it in any other group. This helps control the number of user accounts and groups that can modify the instance, or any object within the instance.

In a default Linux or UNIX installation, you have the option of creating an instance as part of the installation process. You can also create other instances or drop existing ones at a later time, using the db2icrt and db2idrop utilties, respectively. These utilities can be found in the DB2PATH/instance directory, where DB2PATH represents /usr/opt/db2_08_01 on AIX, and /opt/IBM/db2/V8.1 on all other Linux and UNIX-based systems. To run these utilities, you must use the root user account. When creating a new instance in Linux and UNIX using the db2icrt utility, you must specify the fenced user account to be associated with the instance. The instance name you choose must map to the user account name that will be the instance owner. For example, if an existing user account called prodinst is to be the owner of the new instance, you would specify prodinst as the instance name in the db2icrt command. The instance will be created in the home directory of the instance-owning user. For example, the following command creates a new instance called devinst, which will be owned by the devinst user account, and uses the existing user account called devfenc for the fenced user account:

Listing 6. Creating a new DB2 UDB instance in Linux and UNIX
db2icrt -u devfenc devinst

DB2 UDB does not support the root account acting directly as a database administrator. You should use the su - <instance owner> command to switch to the the database administrator (instance owner) account.

DB2 Administration Server user account

The user account for the DB2 Administration Server (DAS) is used to run the DAS process on the system. The default user ID created during a default installation is dasusr1 and default group is dasadm1. The DAS account is also used by the DB2 UDB GUI tools to perform administration tasks against the local server instances and databases. Only one DAS is required per machine. It can manage all the instances defined on the server. The DAS user account must be different from the instance owner user account.

Once the DAS process is started using this account, it must also be stopped with this account. Therefore, in Linux or UNIX, you must use the su - <DAS user> command to switch to the DAS user account in order to start and stop the DAS process.

Fenced user account

The fenced user account is used to run user-defined functions (UDFs) and stored procedures outside of the address (memory) space used by the DB2 UDB engine. Sometimes, if a procedure or function is unstable or is being tested, it will be defined as FENCED so that it can be run in its own process address space. This way, if the function or procedure crashes or terminates abnormally, it will not affect any of the other instance processes. The default user account created for the fenced user is db2fenc1 and the default group is db2fadm1. For security reasons, we recommend that you do not use the instance owner account for the fenced user account. If you do not need this level of security -- for example, if you are running in a test environment, or you are not planning to use fenced UDFs or stored procedures -- you can use the instance owner account instead of creating another user account. When creating new instances, the fenced user account must be specified as part of the instance creation command (db2icrt ... -u <Fenced_ID>).

Setting up the DB2 UDB environment for other Linux and UNIX users

Verifying DB2 UDB path location

Use the which db2 command to ensure that your search path has been set up correctly. This is especially important when you have multiple instances running on the same server. This command returns the absolute path of the command line processor (CLP) executable. Verify that it is located under the correct instance's sqllib directory.

Linux and UNIX environments enforce strong security at the user level; the files and processes associated with one user account are not directly accessible by other users. By default, when a new DB2 UDB instance is created, a special DB2 UDB environment script called db2profile is also added to the instance owner's system profile, so that it is sourced each time the instance owner logs in to the system. These scripts set up access to the database environment and allow the instance owner to execute DB2 UDB commands. In order for other users on the system to have access to the instance and DB2 UDB environment, they must also run the same scripts. One way of doing this is to "source" the DB2 UDB instance owner's .profile file (or the db2profile file referenced by that .profile file). If you are using the Bourne or Korn shell, you can edit the .profile file of a target user account so that the db2profile script runs automatically when the user logs in to the system. For users of the C shell, you can edit the .login file so that it runs the db2shrc script file. To choose which instance you want to use, add one of the following statements to the .profile or .login script files, or issue the statements from a terminal window where a user needs to access DB2 UDB from (note that the period (.) and the space are required):

  • for Bourne or Korn shell:
    . INSTHOME/sqllib/db2profile
  • for C shell:
    source INSTHOME/sqllib/db2cshrc

where INSTHOME is the home directory of the instance that you wish to use.

For users who have a customized version of the script in their home directory:

  • for Bourne or Korn shell:
    . USERHOME/db2profile
  • for C shell:
    source USERHOME/db2cshrc

where USERHOME is the home directory of the user.

If you want to work with more than one instance at the same time, run the script for each instance that you want to use in separate terminal windows.


The scheduler user account

The scheduler user account is needed when the scheduling facilities of DB2 UDB are enabled. This account is used by DB2 UDB scheduler to connect to the tools catalog database when it resides on a remote server. The tools catalog database contains all the metadata about the tasks that are scheduled to run on the DB2 UDB server (for example, a backup task scheduled to run every Sunday at 1:00 a.m.). The scheduling facilities are designed such that the tools catalog database does not need to reside on the same DB2 UDB server where the tasks are scheduled to run. In this case, the scheduler on the DB2 UDB server needs to connect to the tools catalog database in order to retrieve any required information about the tasks to be run.

The scheduler account is controlled by the DAS configuration parameter SCHED_USERID. This parameter is only relevant if the tools catalog database does not reside on the same machine as the DB2 administration server. You can view the value of this parameter by issuing the db2 get admin cfg command.

To change the value of this parameter, you can issue the db2admin setschedid <user_ID> <password> command where <user_ID> and <password> are the user ID and password you want the scheduler to use to connect to the remote tools catalog database.

Listing 7 shows the results after setting the scheduler ID to a value of xtradba.

Listing 7. A list of the configurable DAS parameters and their values
C:\>db2 get admin cfg

            Admin Server Configuration

 Authentication Type DAS                (AUTHENTICATION) = SERVER_ENCRYPT

 DAS Administration Authority Group Name  (DASADM_GROUP) =

 DAS Discovery Mode                           (DISCOVER) = DISABLE
 Name of the DB2 Server System               (DB2SYSTEM) = TEDWAS

 Java Development Kit Installation Path DAS   (JDK_PATH) =
                                   C:\Program Files\IBM\SQLLIB\java\jdk\
 Java Development Kit Installation Path DAS   (JDK_64_PATH) =

 DAS Code Page                            (DAS_CODEPAGE) = 0
 DAS Territory                           (DAS_TERRITORY) = 0

 Location of Contact List                 (CONTACT_HOST) =
 Execute Expired Tasks                   (EXEC_EXP_TASK) = NO
 Scheduler Mode                           (SCHED_ENABLE) = ON
 SMTP Server                               (SMTP_SERVER) =
 Tools Catalog Database                    (TOOLSCAT_DB) = TOOLSDB
 Tools Catalog Database Instance         (TOOLSCAT_INST) = DB2
 Tools Catalog Database Schema         (TOOLSCAT_SCHEMA) = TOOLSDB
 Scheduler User ID                                = xtradba

Conclusion

This article has reviewed DB2 UDB's primary interactions with user and group accounts. It summarized how DB2 UDB performs user authentication and user/group authorization, as well as how super users are defined for a DB2 UDB instance. You learned about the default user and group accounts required and/or created for a DB2 UDB installation and how they could be configured. With a better understanding about how user and group accounts interact with DB2 UDB, you can plan and implement the optimal user and group account configuration for your environment.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=92552
ArticleTitle=DB2 UDB security, Part 1: Understand how user and group accounts interact with DB2 UDB
publish-date=08252005