诗朗诵不朽原文

書名： IBM DB2 9.7 Advanced Application Developer Cookbook
作者名： Sanjay Kumar Mohankumar Saraswatipura
本章字數： 1151字
更新時間： 2021-08-20 15:42:21

Granting and revoking instance-level authorities

Authorization is a security mechanism by which DB2 determines whether a user is allowed to perform a certain action or not. DB2 provides various authorities for the administration of databases and their environment. We can grant these authorities to different users to perform a certain set of operations. These operations could be installation, migration, backups, maintenance activities, data loads, so on and so forth. The authorities comprise certain privileges that are necessary to perform a certain task. DB2 provides two levels of authorities:

Instance-level authorities
Database-level authorities

Instance-level authorities allow the user to perform the instance-level activities, such as upgrading databases, instance performance monitoring, managing disk space, and so on. This level of authorization doesn't provide access to data in the database.

DB2 provides four types of instance-level authorities:

SYSADM: This is the highest level of administrative authority in DB2. It is assigned to the group specified by the sysadm_group database manager configuration parameter.
SYSCTRL: This is the highest level of system control authority. It is assigned to the group specified by the sysctrl_group database manager configuration parameter.
SYSMAINT: This is the second level of system control authority. It is assigned to the group specified by the sysmaint_group database manager configuration parameter.
SYSMON: This authority provides only the ability to perform monitoring activities for the instance or its databases. It is assigned to the group specified by the sysmon_group database manager configuration parameter.

Getting ready

We need SYSADM authority to update the database manager configuration parameters, which in turn, are required to grant instance-level authorities.

How to do it...

Let's see how we can grant and revoke instance-level authority. The process is the same for all instance-level authorities.

Granting instance-level authorities

All instance-level authorities can only be granted to groups and not individual users. For each of the four authorities, there is a corresponding database manager configuration parameter. To grant the required authority to any group, this database manager configuration parameter is updated with the group name.

Create a group (or choose an existing group) that you would like to grant an authority to.
- For Windows (Server Edition): Use the following steps to create a group on Windows:
  - i. Right-click on My Computer and click on Manage
  - ii. Under System Tools, expand Local Users and Groups
  - iii. Right-click on Groups and select New group
  - iv. Fill the details and click Create
- For UNIX: Use the groupadd command to create a group on the UNIX operating systems. For example:
```
groupadd devgrp
```
Update the corresponding database manager configuration parameter value as:
```
UPDATE DBM CFG USING <DBM CFG parameter> <group_name> 
```
For example, to grant SYSADM authority to the db2grp1 group, use the following command:
```
UPDATE DBM CFG USING SYSADM_GROUP db2grp1 
```
Add all users to whom you would like to grant the authority to this group. One user could be a part of multiple groups and hence a user can have multiple authorities. The membership in this group is controlled outside DB2 through the security facility provided by the operating system.
You can add users to the group as follows:
- For Windows: Use the following steps to add a user to a group on Windows:
- i. Right-click on My Computer and click on Manage
- ii. Under System Tools, expand Local Users and Groups
- iii. Double-click on the group name to which users are to be added
- iv. Click on Add and enter usernames
Click on Ok
- For UNIX: Use the usermod command to add a user to a group. The following command adds the joe user to the devgrp group:
```
usermod G devgrp joe 
```

Alternatively, we can also add a user to a group manually in the /etc/groups file against the desired group. This file can be edited in any text editor such as the "vi" editor.

Revoking authorities

To revoke any instance-level authority from a user, change its group membership. This can be done by using the security facility of the operating system.

Use the following steps to remove a user from a group:

For Windows: Use the following steps to remove a user from a group on Windows:
- Right-click on My Computer and click on Manage
- Under System Tools, expand Local Users and groups
- Double-click on the user from the Users list
- Go to the Member of tab
- Select the group name that needs to be removed for this user
- Click on the Remove button
- Click Ok.
For UNIX: Use the usermod command to change the group membership of a user. We need to pass all the group names that the user should be part of. Any group which is not in the list, will be removed from that group. For example:

usermod G devgrp, dbctrl, dbmon joe

How it works....

DB2 does not maintain any user itself. It uses the operating system users and groups. To grant or revoke any authority to or from any user, we need to use operating system security facility to create new users, or modify group membership of any user. Once the users and groups have been configured in an operating system, they can be used in DB2.

The users and groups created at the operating system needs to follow certain naming conventions; otherwise, they can't be used in DB2. These are:

Length of group names should be within the SQL limit of 128 characters
Usernames on UNIX systems can have up to 8 characters
Usernames on Windows systems can have up to 30 characters
Usernames and group names cannot begin with IBM, SQL, or SYS
Usernames and group names cannot be USERS, ADMINS, GUESTS, PUBLIC, LOCAL or any reserved SQL word

There's more...

When a user with SYSADM authority creates a database, that user is automatically granted ACCESSCTRL, DATAACCESS, DBADM, and SECADM authority on the database. If we want to prevent that user from accessing the database as a database administrator or a security administrator, then we must explicitly revoke these database authorities from the user.

Tip

On Windows systems, when the sysadm_group database manager configuration parameter is not specified, the Local System account is considered a system administrator (holding SYSADM authority).

What changed in DB2 9.7

In DB2 9.7, the authorization model has been changed to separate the duties of the system administrator, database administrator, and security administrator. As part of this change, the SYSADM authority no longer has implicit DBADM authority as opposed to earlier versions of DB2. If the SYSADM authority needs similar permissions to those in earlier versions of DB2, then SECADM must explicitly grant him DATAACCESS and ACCESSCTRL authorities.

We can use the SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID table function to retrieve the authorities for any user. This function accepts the authorization and the authorization ID type.

For example, to view the authorizations for the ADMINISTRATOR user, we can use following query:

SELECT CHAR(AUTHORITY, 30), D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE FROM TABLE (SYSPROC.AUTH_LIST_ AUTHORITIES_FOR_AUTHID ('ADMINISTRATOR', 'U') ) AS T;

The following table summarizes all activities that can be performed by each authority:

官术网_书友最值得收藏!

IBM DB2 9.7 Advanced Application Developer Cookbook