Db2 简明教程

DB2 - Database Security

本章介绍数据库安全性。

This chapter describes database security.

Introduction

可以通过两种不同的安全控制模式管理 DB2 数据库和函数：

DB2 database and functions can be managed by two different modes of security controls:

Authentication

认证是确认用户仅根据他授权执行的活动权限登录的过程。用户认证可以在操作系统级别或数据库级别本身执行。通过使用诸如视网膜和指纹等生物特征认证工具来防止数据库遭受黑客或恶意用户的破坏。

Authentication is the process of confirming that a user logs in only in accordance with the rights to perform the activities he is authorized to perform. User authentication can be performed at operating system level or database level itself. By using authentication tools for biometrics such as retina and figure prints are in use to keep the database from hackers or malicious users.

可以在 db2 数据库系统外部管理数据库安全性。以下是一些类型的安全认证流程：

The database security can be managed from outside the db2 database system. Here are some type of security authentication process:

  1. Based on Operating System authentications.

  2. Lightweight Directory Access Protocol (LDAP)

对于 DB2，安全服务作为独立产品成为操作系统的一部分。对于认证，它需要两个不同的凭据，它们分别是用户 ID 或用户名和密码。

For DB2, the security service is a part of operating system as a separate product. For Authentication, it requires two different credentials, those are userid or username, and password.

Authorization

您可以在 DB2 数据库系统中访问 DB2 数据库及其功能，这些功能由 DB2 数据库管理器管理。授权是由 DB2 数据库管理器管理的一个过程。管理器获取有关当前经过认证用户的的信息，以指示用户可以执行或访问哪些数据库操作。

You can access the DB2 Database and its functionality within the DB2 database system, which is managed by the DB2 Database manager. Authorization is a process managed by the DB2 Database manager. The manager obtains information about the current authenticated user, that indicates which database operation the user can perform or access.

以下是可以用于授权的不同权限方式：

Here are different ways of permissions available for authorization:

Primary permission ：直接授予授权 ID。

Primary permission: Grants the authorization ID directly.

Secondary permission ：如果用户是成员，则授予组和角色

Secondary permission: Grants to the groups and roles if the user is a member

Public permission ：公开授予所有用户。

Public permission: Grants to all users publicly.

Context-sensitive permission ：授予受信任上下文角色。

Context-sensitive permission: Grants to the trusted context role.

可以根据以下类别向用户授予授权：

Authorization can be given to users based on the categories below:

  1. System-level authorization

  2. System administrator [SYSADM]

  3. System Control [SYSCTRL]

  4. System maintenance [SYSMAINT]

  5. System monitor [SYSMON]

权威机构对实例级别的功能提供控制。权威机构会提供组权限，以控制维护和权限操作。例如，数据库和数据库对象。

Authorities provide of control over instance-level functionality. Authority provide to group privileges, to control maintenance and authority operations. For instance, database and database objects.

  1. Database-level authorization

  2. Security Administrator [SECADM]

  3. Database Administrator [DBADM]

  4. Access Control [ACCESSCTRL]

  5. Data access [DATAACCESS]

  6. SQL administrator. [SQLADM]

  7. Workload management administrator [WLMADM]

  8. Explain [EXPLAIN]

权限机构在数据库内部提供控制。数据库的其他权限包括 LDAD 和 CONNECT。

Authorities provide controls within the database. Other authorities for database include with LDAD and CONNECT.

  1. Object-Level Authorization: Object-Level authorization involves verifying privileges when an operation is performed on an object.

  2. Content-based Authorization: User can have read and write access to individual rows and columns on a particular table using Label-based access Control [LBAC].

DB2 表和配置文件用于记录与授权名称相关联的权限。当用户尝试访问数据时，记录的权限会验证以下权限：

DB2 tables and configuration files are used to record the permissions associated with authorization names. When a user tries to access the data, the recorded permissions verify the following permissions:

  1. Authorization name of the user

  2. Which group belongs to the user

  3. Which roles are granted directly to the user or indirectly to a group

  4. Permissions acquired through a trusted context.

在使用 SQL 语句时，DB2 授权模型会考虑以下权限的组合：

While working with the SQL statements, the DB2 authorization model considers the combination of the following permissions:

  1. Permissions granted to the primary authorization ID associated with the SQL statements.

  2. Secondary authorization IDs associated with the SQL statements.

  3. Granted to PUBLIC

  4. Granted to the trusted context role.

Instance level authorities

让我们探讨一些与实例相关的权限。

Let us discuss some instance related authorities.

System administration authority (SYSADM)

这是实例级别最高级别的管理权限。拥有 SYSADM 权限的用户可以在实例内执行一些数据库和数据库管理器命令。拥有 SYSADM 权限的用户可以执行以下操作：

It is highest level administrative authority at the instance-level. Users with SYSADM authority can execute some databases and database manager commands within the instance. Users with SYSADM authority can perform the following operations:

  1. Upgrade a Database

  2. Restore a Database

  3. Update Database manager configuration file.

System control authority (SYSCTRL)

这是系统控制权限中最高级别。它用于针对数据库管理器实例及其数据库执行维护和实用操作。这些操作可能会影响系统资源，但它们不允许直接访问数据库中的数据。

It is the highest level in System control authority. It provides to perform maintenance and utility operations against the database manager instance and its databases. These operations can affect system resources, but they do not allow direct access to data in the database.

拥有 SYSCTRL 权限的用户可以执行以下操作：

Users with SYSCTRL authority can perform the following actions:

  1. Updating the database, Node, or Distributed Connect Service (DCS) directory

  2. Forcing users off the system-level

  3. Creating or Dropping a database-level

  4. Creating, altering, or dropping a table space

  5. Using any table space

  6. Restoring Database

System maintenance authority (SYSMAINT)

它是系统控制权限的第二级。它提供对数据库管理器实例及其数据库执行维护和实用操作的功能。这些操作影响系统资源，但不会允许直接访问数据库中的数据。该权限旨在让用户维护包含敏感数据的数据库管理器实例中的数据库。

It is a second level of system control authority. It provides to perform maintenance and utility operations against the database manager instance and its databases. These operations affect the system resources without allowing direct access to data in the database. This authority is designed for users to maintain databases within a database manager instance that contains sensitive data.

只有具有 SYSMAINT 或更高级别系统权限的用户才能执行以下任务：

Only Users with SYSMAINT or higher level system authorities can perform the following tasks:

  1. Taking backup

  2. Restoring the backup

  3. Roll forward recovery

  4. Starting or stopping instance

  5. Restoring tablespaces

  6. Executing db2trc command

  7. Taking system monitor snapshots in case of an Instance level user or a database level user.

具有 SYSMAINT 的用户可以执行以下任务：

A user with SYSMAINT can perform the following tasks:

  1. Query the state of a tablespace

  2. Updating log history files

  3. Reorganizing of tables

  4. Using RUNSTATS (Collection catalog statistics)

System monitor authority (SYSMON)

有了此权限，用户可以监视或获取数据库管理器实例或其数据库的快照。SYSMON 权限允许用户运行以下任务：

With this authority, the user can monitor or take snapshots of database manager instance or its database. SYSMON authority enables the user to run the following tasks:

  1. GET DATABASE MANAGER MONITOR SWITCHES

  2. GET MONITOR SWITCHES

  3. GET SNAPSHOT

  4. LIST LIST ACTIVE DATABASESLIST APPLICATIONSLIST DATABASE PARTITION GROUPSLIST DCS APPLICATIONSLIST PACKAGESLIST TABLESLIST TABLESPACE CONTAINERSLIST TABLESPACESLIST UTITLITIES

  5. RESET MONITOR

  6. UPDATE MONITOR SWITCHES

Database authorities

每个数据库权限都持有执行数据库上某些操作的授权 ID。这些数据库权限与特权不同。以下是一些数据库权限的列表：

Each database authority holds the authorization ID to perform some action on the database. These database authorities are different from privileges. Here is the list of some database authorities:

ACCESSCTRL ：允许授予和撤消所有对象特权和数据库权限。

ACCESSCTRL: allows to grant and revoke all object privileges and database authorities.

BINDADD ：允许在数据库中创建新程序包。

BINDADD: Allows to create a new package in the database.

CONNECT ：允许连接到数据库。

CONNECT: Allows to connect to the database.

CREATETAB ：允许在数据库中创建新表。

CREATETAB: Allows to create new tables in the database.

CREATE_EXTERNAL_ROUTINE ：允许创建应用程序和数据库用户使用的过程。

CREATE_EXTERNAL_ROUTINE: Allows to create a procedure to be used by applications and the users of the databases.

DATAACCESS ：允许访问存储在数据库表中的数据。

DATAACCESS: Allows to access data stored in the database tables.

DBADM ：充当数据库管理员。它授予所有其他数据库权限，但 ACCESSCTRL、DATAACCESS 和 SECADM 除外。

DBADM: Act as a database administrator. It gives all other database authorities except ACCESSCTRL, DATAACCESS, and SECADM.

EXPLAIN ：允许解释查询计划，而不要求他们拥有访问表中数据的权限。

EXPLAIN: Allows to explain query plans without requiring them to hold the privileges to access the data in the tables.

IMPLICIT_SCHEMA ：允许用户通过使用 CREATE 语句创建对象来隐式创建架构。

IMPLICIT_SCHEMA: Allows a user to create a schema implicitly by creating an object using a CREATE statement.

LOAD ：允许将数据加载到表中。

LOAD: Allows to load data into table.

QUIESCE_CONNECT ：允许在数据库静默（暂时禁用）时访问数据库。

QUIESCE_CONNECT: Allows to access the database while it is quiesce (temporarily disabled).

SECADM ：允许充当数据库的安全管理员。

SECADM: Allows to act as a security administrator for the database.

SQLADM ：允许监控和调整 SQL 语句。

SQLADM: Allows to monitor and tune SQL statements.

WLMADM ：允许充当工作负载管理员

WLMADM: Allows to act as a workload administrator

Privileges

SETSESSIONUSER

授权标识符权限涉及授权标识符上的操作。只有一个称为 SETSESSIONUSER 权限的权限。它可以授予用户或组，并允许会话用户将其身份切换到授予了权限的任何授权标识符。此权限由用户 SECADM 权限授予。

Authorization ID privileges involve actions on authorization IDs. There is only one privilege, called the SETSESSIONUSER privilege. It can be granted to user or a group and it allows to session user to switch identities to any of the authorization IDs on which the privileges are granted. This privilege is granted by user SECADM authority.

Schema privileges

此权限涉及对数据库中架构的操作。架构的所有者具有操作架构对象（如表、视图、索引、程序包、数据类型、函数、触发器、过程和别名）的所有权限。可以向用户、组、角色或 PUBLIC 授予以下权限中的任何一种：

This privileges involve actions on schema in the database. The owner of the schema has all the permissions to manipulate the schema objects like tables, views, indexes, packages, data types, functions, triggers, procedures and aliases. A user, a group, a role, or PUBLIC can be granted any user of the following privileges:

  1. CREATEIN: allows to create objects within the schema

  2. ALTERIN: allows to modify objects within the schema.

DROPIN

这允许删除架构中的对象。

This allows to delete the objects within the schema.

Tablespace privileges

这些权限涉及数据库中表空间上的操作。可以向用户授予表空间的 USE 权限。然后这些权限允许他们在表空间中创建表。当创建表空间时，权限所有者可以使用具有 GRANT OPTION 命令的 USE 权限授予权限。而 SECADM 或 ACCESSCTRL 权限具有表空间上 USE 权限的权限。

These privileges involve actions on the tablespaces in the database. User can be granted the USE privilege for the tablespaces. The privileges then allow them to create tables within tablespaces. The privilege owner can grant the USE privilege with the command WITH GRANT OPTION on the tablespace when tablespace is created. And SECADM or ACCESSCTRL authorities have the permissions to USE privileges on the tablespace.

Table and view privileges

用户必须对数据库有 CONNECT 权限才能使用表和视图权限。表和视图的权限如下所述：

The user must have CONNECT authority on the database to be able to use table and view privileges. The privileges for tables and views are as given below:

CONTROL

它提供了表或视图的所有权限，包括删除和授予、撤销向用户授予的单个表权限。

It provides all the privileges for a table or a view including drop and grant, revoke individual table privileges to the user.

ALTER

它允许用户修改表。

It allows user to modify a table.

DELETE

它允许用户从表或视图中删除行。

It allows the user to delete rows from the table or view.

INDEX

它允许用户将行插入到表或视图中。它还可以运行导入实用程序。

It allows the user to insert a row into table or view. It can also run import utility.

REFERENCES

它允许用户创建并删除外键。

It allows the users to create and drop a foreign key.

SELECT

它允许用户从表或视图中检索行。

It allows the user to retrieve rows from a table or view.

UPDATE

它允许用户在表、视图中更改条目。

It allows the user to change entries in a table, view.

Package privileges

用户必须对数据库具有 CONNECT 权限。包是一个数据库对象，其中包含数据库管理器以在特定应用程序中以最有效方式访问数据的信息。

User must have CONNECT authority to the database. Package is a database object that contains the information of database manager to access data in the most efficient way for a particular application.

CONTROL

它为用户提供了重新绑定、删除或执行包的权限。拥有此权限的用户将被授予 BIND 和 EXECUTE 权限。

It provides the user with privileges of rebinding, dropping or executing packages. A user with this privileges is granted to BIND and EXECUTE privileges.

BIND

它允许用户绑定或重新绑定该包。

It allows the user to bind or rebind that package.

EXECUTE

允许执行包。

Allows to execute a package.

Index privileges

此权限自动在索引上接收 CONTROL 权限。

This privilege automatically receives CONTROL privilege on the index.

Sequence privileges

序列会自动在序列上接收 USAGE 和 ALTER 权限。

Sequence automatically receives the USAGE and ALTER privileges on the sequence.

Routine privileges

它涉及数据库中例程（例如函数、过程和方法）的操作。

It involves the action of routines such as functions, procedures, and methods within a database.