Comparing DB2 and CA ACF2 Option for DB2 Features › Controlling Access to DB2 Resources › Explicit Assignment of Privileges

Explicit Assignment of Privileges

DB2 uses explicit GRANT and REVOKE statements to give privileges to or remove privileges from a primary or a secondary authorization ID. If you are granted a privilege, you can perform a particular function on a specific object. For example, if USER01 is granted SELECT on the PERSONEL.EMPLPAY table, the GRANT statement might look like:

GRANT SELECT ON TABLE PERSONEL.EMPLPAY TO USER01

This statement enables USER01 to retrieve data from the PERSONEL.EMPLPAY table.

DB2 accepts synonyms to represent table names. For example, you can use the CREATE SYNONYM request to create the synonym PAY for the PERSONEL.EMPLPAY table. Then, when you grant privileges to an ID to access PAY, DB2 translates the privileges against the PERSONEL.EMPLPAY table.

Corresponding CA ACF2 Option for DB2 Feature

CA ACF2 Option for DB2 secures all functions performed on all types of DB2 resources through rule sets. CA ACF2 Option for DB2 ignores explicit GRANT and REVOKE statements. CA ACF2 Option for DB2 does not eliminate privileges from DB2. It enhances how you administer these privileges. In place of GRANT and REVOKE statements, an CA ACF2 Option for DB2 rule set identifies the name and type of DB2 resource and defines other control information that a user must match for CA ACF2 Option for DB2 to allow access to the resource. This means that a rule must exist before any access or function is allowed. CA ACF2 Option for DB2 checks the rule when a user requests a DB2 resource and determines whether that user should be allowed, logged, or prevented from performing the requested function on that particular resource.

For CA ACF2 Option for DB2 rule sets, the type of resource determines the resource name. For most system privileges and utilities, the resource name is the actual authority or command, such as SYSADM or DISPLAY. For other DB2 objects, such as databases, tables, or plans, the resource name is the name of the particular object, such as FINPAY for a particular database, EMPLOYEE.PAY for a particular table, or PLAN01 for a particular application plan.

With CA ACF2 Option for DB2, users can continue to reference synonyms in their programs. Because DB2 translates requests to the true table name before validating the request, CA ACF2 Option for DB2 always receives the true table name to validate. CA ACF2 Option for DB2 rules are written against the true table name, so CA ACF2 Option for DB2 is able to validate requests that use synonyms. As with native DB2, only the originator or creator of a synonym can drop it.

A type code in an CA ACF2 Option for DB2 rule set identifies the type of DB2 resource being controlled. CA ACF2 Option for DB2 assigns type codes to each type of resource as follows:

CA Technologies supplies a conversion utility with CA ACF2 Option for DB2 that converts all catalog entries created by GRANT statements to CA ACF2 Option for DB2 rules. However, no utility can account for all the nuances and differences that exist among sites. Therefore, you must review these rules to ensure that the conversion process did what you intended. See the Getting Started for more information about this utility.

The following is equivalent to the SQL GRANT statement in the previous section.

$KEY(PERSONEL.EMPLPAY)
$TYPE(TBL)
$SYSID(****)
 UID(*****USER014040) SERVICE(SELECT) ALLOW

The UID parameter of the rule specifies the 1‑ to 24‑character user identification string or UID mask. The UID parameter identifies the UIDs of users who can perform a particular function on the named resource.

Note: For the CA ACF2 Option for DB2 conversion utility to create rule entries that do not have to be changed, the UID defined by your site should include the logonid as one of its fields.

To write rules for a group of users, you can mask the UID parameter. To write rules for a group of resources, you can mask the resource name in the $KEY control statement or use resource grouping. Masking means you can represent any character or multiple characters with a single character, such as an asterisk (*) or a dash (–). For example, to grant a privilege to everyone, you specify UID(*) on the rule. UID(*) replaces the need to grant access to PUBLIC. Masking of the resource name entails the same process. When resource names are similar, you can use a masking character to represent any character. When resource names are different, you might use resource grouping to write one rule for a group of resources instead of multiple rules for multiple resources.

Another way to replace the need to use grant to PUBLIC is to put the resource on the SAFELIST. The resource will then be accessible by everyone. There is no need to use UID(*) on the rule if the resource is on the SAFELIST. In fact, a security call will not be made. Authorization will automatically be granted.

The following example uses masking to give all users on all DB2 subsystems full access to a table called USER01.EMPLOYEE_NAMES. The $LIDOWNER control statement indicates that USER99 owns the table and has ownership privileges on it. The SERVICE parameter tells CA ACF2 Option for DB2 to use this rule entry for all functions performed on the table. See the “Implicit Ownership of Resources” section for more information about ownership privileges.

$KEY(USER01.EMPLOYEE_NAMES)
$TYPE(TBL)
$SYSID(****)
$LIDOWNER(USER99)
 UID(*) SERVICE(ALL) ALLOW

In the next example, users with UIDs that begin with CHFAC are permitted to use the DISPLAY system privilege on any DB2 subsystem that starts with DSN. The UID fields might represent the city (CHicago), the department (Finance), and the function (ACcountant). This UID parameter represents any UID that begins with CHFAC. $UIDOWNER and $LIDOWNER do not apply to system privileges because they are not owned. You do not use the SERVICE parameter with system privileges because functions such as UPDATE or DELETE do not make sense in this case. Instead, with system privileges, you are simply allowed, allowed but logged, or prevented from using the privilege.

$KEY(DISPLAY)
$TYPE(SYS)
$SYSID(DSN*)
 UID(CHFAC) ALLOW

You could also specify CH*AC as the UID parameter. A rule with this UID parameter would permit any Chicago Accountant who is a member of any department to use the DISPLAY command on any DB2 subsystem that begins with DSN.

You can also use masking in the $KEY control statement to group resources that are similarly named. For example, you might define a set of tables named as follows:

USER.TABLES
USER.GRAPHS
USER.CHARTS
USER.MEMOS

You can then create a rule set that controls access to any table resource that begins with USER, such as the following rule set:

$KEY(USER.******)
$TYPE(TBL)
$SYSID(****)
$LIDOWNER(USER12)
 UID(USER57) LOG

You must pad the $KEY with asterisks for the maximum number of characters for the resource names being matched.

You can also use resource grouping to create rules that apply to more than one resource. Resource grouping enables you to be more specific about what is being controlled. It also works well with resources that do not follow naming conventions. Resource group records organize the resources that you want to similarly control in one place. This can reduce your administrative overhead substantially. To use resource grouping, first identify the resources to which you want to control access in the same manner. In this example, you might define a resource group called FINTBLS that contains the following tables:

FINANCE.MONTH_BUDGET
ACCTPAY_OCTOBER
AUDIT.OCT_RESULTS

Then you can write one rule set that controls access to all of these resources. For example, the rule set might look like this:

$KEY(FINTBLS)
$TYPE(TBL)
$SYSID(PROD)
 UID(***FINMGUSER59) SERVICE(UPDATE) LOG
 UID(***FINCLUSER01) SERVICE(SELECT) LOG

Your use of resource masking or resource grouping depends on your site preferences and requirements. See “Writing CA ACF2 Option for DB2 Rules,” for information about creating these resource groups and writing rules.