Authorization

In the authorization process, the database manager obtains information about the authenticated user's authorization ID. The authorization ID indicates which database operations the user can perform, including which database objects the user can access. The permissions, which are declared explicitly within Db2, allow users to perform specific actions.

For an authorization ID, permissions can be granted at various levels:

• Primary – Primary permissions are granted to the authorization ID directly.

• Secondary – Secondary permissions are granted to the groups and roles of which the authorization ID is a member.

• Public – The public level is a special Db2 group that represents everyone in the organization.

• Context-sensitive – These permissions are granted to a trusted context role.

Db2 authority levels

Db2 manages authorities at the following levels:

• Instance, or system, level: SYSADM, SYSCTRL, SYSMAINT, SYSMON

  • Database level: DBADM (SQLADM (EXPLAIN), WLMADM), SECADM (ACCESSCTRL), DATAACCESS

    • Schema level: SCHEMAADM (LOAD), ACCESSCTRL, DATAACCESS

    • Additional schema-level privileges

Db2 supports the following additional schema-level privileges:

• SELECTIN allows the user to retrieve rows from all the tables or views defined in the schema.

• INSERTIN allows the user to insert rows and to run the IMPORT utility on all tables or views defined in the schema.

• UPDATEIN allows the user to run the Update statement on all tables or updatable views defined in the schema.

• DELETEIN allows the user to delete rows from all tables or updatable views defined in the schema.

• EXECUTEIN allows the user to run all the user-defined functions, methods, procedures, packages, or modules defined in the schema.

• CREATEIN allows the user to create objects within the schema.

• ALTERIN allows the user to change objects within the schema.

• DROPIN allows the user to drop objects from within the schema.

Authorization ID

A Db2 authorization ID consists of the following:

• Authorization ID type

  • Individual user (U)

  • Group (G)

  • Role (R)

  • Public (P)

• Authorization ID value

An authorization ID, which the authorities and privileges can be associated with, is used for identification along with checking the Db2 authorization.

The following are commonly used primary authorization ID terms in Db2:

• System authorization ID – The authorization ID used to do any initial authorization checking, such as checking for CONNECT privilege during CONNECT processing.

• Session authorization ID – The authorization ID used for any session authorization checking subsequent to the initial checks performed during CONNECT processing. To see the current value of the session authorization ID, use the SESSION_USER special register.

• Statement authorization ID – The authorization ID (along with the associated secondary authorization IDs for groups or roles) used for authorization requirements of an SQL statement. This is also used to determine the object ownership as applicable. It can vary depending on the type of SQL statement and the context in which it is issued.

Authorities and privileges are acquired through explicit mechanisms, such as GRANT and REVOKE statements, or implicit mechanisms, such as the privilege obtained through object ownership. They can also be acquired temporarily through multiple processing contexts. For example, you have been given access to a view, which implicitly gives you access to the objects in that view definition. Also, an EXECUTE privilege on a package will give you access to any static SQL in that package. The other way to obtain temporary authority and privilege is by using trusted context.

Trusted context

A trusted context is a database object that defines a trust relationship between the database and an external application server or another database server. The trust relationship is based on the following trust attributes:

• IP address

• System authorization ID

• Level of encryption for data communication

If a connection matches the trust attributes within a defined trusted context object, a trusted connection is established. There are two types of trusted connection:

• Implicit trusted connection – An implicit trusted connection allows a user to inherit a role that is not available to them outside the scope of that trusted connection definition. The session authorization ID of the connection is given a temporary membership to a role that is declared in the trusted context definition.

  The following diagram shows an example of an implicit trusted connection in which connection from only one IP address is allowed.

   

  

  1. The role dbconnrole is created and granted connection to the database. The trusted context ctximpl is created based on connection using authid ABC with the IP address 10.10.10.1 and the default role dbconnrole.

  2. User ABC can connect to the database from IP address 10.10.10.1.

  3. User ABC can't connect from IP address 10.10.10.2.

• Explicit trusted connection – An explicit trusted connection allows a trusted application server or another Db2 database server to switch the current user ID on the existing connection in an efficient manner. An application server establishes the original connection with an explicit request for trust. After trust is established, the application server can issue requests to the database server to change the session authorization ID for any new unit of work. The ID that is used to perform the initial connect request needs only CONNECT privilege on the server.

  Explicit trusted connections are helpful when you have a middle tier server that needs to issue database requests on behalf of multiple users with different sets of access patterns across various database objects such as tables. The trusted context object created on the database server allows the middle-tier server to establish an explicit trusted connection to the database and then switch the current user ID of the connection to the corresponding user ID without the need to re-authenticate to the database server. Based on the privileges granted on the database objects for the user authorization ID, the user can continue to perform operations, retaining the real identity of the user who performs any specific tasks.

  The following diagram shows an example of an explicit trusted connection in which USER1, USER2, and USER3 all connect through the authid APPID.

  

  1. The trusted context ctxexpl is created based on connection using authid APPID from IP address 10.10.10.1 for users USER1, USER2, and USER3.

  2. The users connect to a middle-tier server.

  3. The trusted connection is established using APPID.