1/31/2016

Performance tuning tools and methodology

Benchmark testing:

Benchmark testing is a normal part of the application development life cycle. It is a team effort that involves both application developers and database administrators(DBAs).

Benchmark testing is performed against a system to determine current performance and can be used to improve application performance. If the application code has been written as efficiently as possible, additional performance gains might be realized by tuning database and database manager configuration parameters.

Different types of benchmark tests are used to discover specific kinds of information. For example:

1/11/2016

File Permimission Requirements for the Instance and Database Directories

The DB2 database system requires that your instance and database directories have a minimum level of permissions.

Note: When the instance and database directories are created by the DB2 database manager, the permissions are accurate and should not be changed.

The minimum permissions of the instance directory and the NODE000x/sqldbdir directory on UNIX and Linux machines must be: u=rwx and go=rx. The meaning of the letters is explained in the following table:

Character Represents:
u User (owner)
g Group
o Other users
r Read
w Write
x Execute

For example, the permissions for the instance, db2inst1, in /home are:
drwxr-xr-x 36 db2inst1 db2grp1 4096 Jun 15 11:13 db2inst1

For the directories containing the databases, each and every directory level up to and including NODE000x needs the following permissions:
drwxrwxr-x 11 db2inst1 db2grp1 4096 Jun 14 15:53 NODE0000/

For example, if a database is located in /db2/data/db2inst1/db2inst1/NODE0000 then the directories: /db2, /db2/data, /db2/data/db2inst1, /db2/data/db2inst1/db2inst1 and /db2/data/db2inst1/db2inst1/NODE0000 need drwxrwxr-x.

Within the NODE000x directory, the sqldbdir directory requires the permissions drwxrwxr-x, for example:
drwx------ 5 db2inst1 db2grp1 256         Jun 14 14:17 SAMPLE/
drwxr-x--- 7 db2inst1 db2grp1 4096 Jun 14 13:26 SQL00001/
drwxrwxr-x 2 db2inst1 db2grp1 256         Jun 14 13:02 sqldbdir/

1/04/2016

Security considerations when installing and using the DB2 database manager

Important to the DB2 administrator from the moment the product was installed are Security Considerations.

A user ID, a group name, and a password are required to complete the installation of DB2 database manager. If you install a GUI-based DB2 database manager that only creates default values for different user IDs and the group.  Depending on the OS platform different defaults will be created.

On UNIX and Linux operating systems, if you choose to create a DB2 instance in the instance setup window, the DB2 database install program creates, by default, different users for the DAS (dasusr), the instance owner (db2inst), and the fenced user (db2fenc). Optionally, you can specify different user names The DB2 database install program 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, the DB2 database install program creates the user db2inst3. If a number greater than 10 is used, the character portion of the name is truncated in the default user ID. For example, if the user ID db2fenc9 already exists, the DB2 database install program truncates the c in the user ID, then appends the 10 (db2fen10). Truncation does not occur when the numeric value is appended to the default DAS user (for example, dasusr24).

On Windows operating systems, the DB2 database install program creates, by default, the user db2admin for the DAS user, the instance owner, and fenced users (you can specify a different user name during setup, if you want). Unlike Linux and UNIX operating systems, no numeric value is appended to the user ID.

For authentication process passwords are very important. If no authentication requirements are set at the operating system level and the database is using the operating system to authenticate users, users will be allowed to connect. For example on Linux and UNIX operating systems, undefined passwords are treated as NULL. In this situation, any user without a defined password will be 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. Use passwords at the operating system level if you want the operating system to do the authentication of users for your database.

When working with partitioned database environments on Linux and UNIX operating systems, the DB2 database manager by default uses the rsh utility (remsh on HP-UX) to run some commands on remote members. The rsh utility transmits passwords in clear text over the network, which can be a security exposure if the DB2 server is not on a secure network. You can use the DB2RSHCMD registry variable to set the remote shell program to a more secure alternative that avoids this exposure. One example of a more secure alternative is ssh.

By default, the installation process grants system administration (SYSADM) privileges to the following users on each operating system:

Linux and UNIX operating systems

1. To a valid DB2 database user name that belongs to the primary group of the instance owner.

Windows environments

1. To members of the local Administrators group.
2. If the DB2 database manager is configured to enumerate groups for users at the location where the users are defined, to members of the Administrators group at the Domain Controller. You use the DB2_GRP_LOOKUP environment variable to configure group enumeration on Windows operating systems.
3. If Windows extended security is enabled, to members of the DB2ADMNS group. The location of the DB2ADMNS group is decided during installation.
4. To the LocalSystem account.

By updating the database manager configuration parameter sysadm_group, the administrator can control which group of users possesses SYSADM privileges.

The administrator should consider creating an instance owner user ID that is easily recognized as being associated with a particular instance.
1. This user ID should have as one of its groups, the name of the SYSADM group created previously.
2. Another recommendation is to use this instance-owner user ID only as a member of the instance owner group and not to use it in any other group.
3. This should control the proliferation of user IDs and groups that can modify the instance.
4. The created user ID must be associated with a password to provide authentication before being permitted entry into the data and databases within the instance.

1/02/2016

Authentication & Authorization process in DB2

Authentication:

The process that verifies user identity at OS level is known as "authentication". The security product can be part of the OS or a separate product.

To authenticate a user security facility requires two items:
1. User ID
2. Password

The user ID identifies the user to the security facility. By supplying the correct password, information known only to the user and the security facility, the user's identity (corresponding to the user ID) is verified.

What will happen after being authenticated:

1. The user must be identified to DB2 using an SQL authorization name or authid.
2. This name can be same as user ID, or a mapped value.
3. A list of groups to which the user belongs is obtained.
4. Group membership may be used when authorizing the user.
5. Groups are security facility entities that must also map to DB2 authorization names. This mapping is done in a method similar to that used for user IDs.

The DB2 database manager uses the security facility to authenticate users in one of two ways:
A successful security system login is used as evidence of identity, and allows:
– Use of local commands to access local data
– Use of remote connections when the server trusts the client authentication.

Successful validation of a user ID and password by the security facility is used as evidence of identity and allows:
– Use of remote connections where the server requires proof of authentication
– Use of operations where the user wants to run a command under an identity other than the identity used for login.

Authorization:

Authorization is performed using DB2 facilities. DB2 tables and configuration files are used to record the permissions associated with each authorization name.

When an authenticated user tries to access data, these recorded permissions are compared with the permissions of:

  1. The authorization name of the user
  2. The groups to which the user belongs
  3. The roles granted to the user directly or indirectly through a group or a role
  4. The permissions acquired through a trusted context
  5. Based on this comparison, the DB2 server determines whether to allow the requested access.


The types of permissions recorded are privileges, authority levels, and LBAC credentials.

A privilege defines a single permission for an authorization name, enabling a user to create or access database resources. Privileges are stored in the database catalogs.

Authority levels provide a method of grouping privileges and control over database manager operations. Database-specific authorities are stored in the database catalogs; system authorities are associated with group membership, and the group names that are associated with the authority levels are stored in the database manager configuration file for a given instance.

LBAC credentials are LBAC security labels and LBAC rule exemptions that allow access to data protected by label-based access control (LBAC). LBAC credentials are stored in the database catalogs.

Groups provide a convenient means of performing authorization for a collection of users without having to grant or revoke privileges for each user individually.

A role is a database object that groups together one or more privileges and can be assigned to users, groups, PUBLIC, or other roles by using a GRANT statement or to a trusted context by using a CREATE TRUSTED CONTEXT or ALTER TRUSTED CONTEXT statement. A role can be specified for the SESSION_USER ROLE connection attribute in a workload definition. When you use roles, you associate access permissions on database objects with the roles. Users that are members of those roles then have the privileges defined for the role with which to access database objects.

During an SQL statement processing, the permissions that the DB2 authorization model considers are the union of the following permissions:
1. The permissions granted to the primary authorization ID associated with the SQL statement
2. The permissions granted to the secondary authorization IDs (groups or roles) associated with the SQL statement
3. The permissions granted to PUBLIC, including roles that are granted to PUBLIC, directly or indirectly through other roles.
4. The permissions granted to the trusted context role, if applicable.

1/01/2016

DB2 Database Security Model


The DB2 database system data and functions have two modes of security controls
1. Authentication (Outside the DB2 database system)
2. Authorization (Managed by database manager)

Authentication: 

1. The process that verifies user identity at OS level is known as "authentication".
2. In this the authentication of user completed by a secure facility outside the DB2 database system         using an authentication security plug-in module.
3. The default authentication will be set when we install the DB2 database system.
4. A DB2 authentication ID will be produced at the time of the authentication process.

Authorization:

1. The database manager will determine the accesses allowed to a user on DB2 data or resources,           after successful completion of user authentication.
2. In this authorization process the DB2 database manager will check that which database operations       that user can perform, and which data objects that the user can access.

The different sources of permissions available to an authorization ID are as follows:
  • Primary permissions: those granted to the authorization ID directly.
  • Secondary permissions: those granted to the groups and roles in which the
  • authorization ID is a member.
  • Public permissions: those granted to PUBLIC.
  • Context-sensitive permissions: those granted to a trusted context role.
Authorization can be given to users in the following categories:

System-level authorization

The system administrator (SYSADM), system control (SYSCTRL), system maintenance (SYSMAINT), and system monitor (SYSMON) authorities provide varying degrees of control over instance-level functions. Authorities provide a way both to group privileges and to control maintenance and utility operations for instances, databases, and database objects.

Database-level authorization

The security administrator (SECADM), database administrator (DBADM), access control (ACCESSCTRL), data access (DATAACCESS), SQL administrator (SQLADM), workload management administrator (WLMADM), and explain (EXPLAIN) authorities provide control within the database. Other database authorities include LOAD (ability to load data into a table), and CONNECT (ability to connect to a database).

Object-level authorization

Object level authorization involves checking privileges when an operation is performed on an object. For example, to select from a table a user must have SELECT privilege on a table (as a minimum).

Content-based authorization

Views provide a way to control which columns or rows of a table specific users can read. Label-based access control (LBAC) determines which users have read and write access to individual rows and individual columns.

ads