Previous Topic: Processing Security InformationNext Topic: Dynamic SQL

Comparing DB2 and CA ACF2 Option for DB2 Features › Processing Security Information › Creating Views

Creating Views

Views are unique entities in DB2 because they are created from other tables or views. Thus, they have unique requirements. To create a view, a DB2 user requires the SELECT authority on the base table or view. When the view is created, DB2 automatically grants the creator of the view only the privileges that he holds on the base tables or views. An authorized user must grant additional privileges on the view later.

DB2 allows users to create views only if the qualifier of the new view is one of their process Ids. These process Ids can different depending on whether the view is created through static or dynamic SQL (see the following sections). To drop a view, DB2 allows users who are owners of the view to drop it.

Corresponding CA ACF2 Option for DB2 Feature

Authorizing the creation of a view in CA ACF2 Option for DB2 is a special process. Like DB2, CA ACF2 Option for DB2 requires that the creator of the view have the SELECT authority on the base tables or views. Unlike DB2 however, CA ACF2 Option for DB2 does not ensure that the qualifier of the view is one of the creator’s process Ids. Instead, CA ACF2 Option for DB2 checks a special privilege called CREATE. CREATE is specified as a keyword on the SERVICE parameter of a view rule set, and is checked when a user creates or drops a view. If the view rule does not grant the CREATE privilege to create a view, CA ACF2 Option for DB2 denies the request. SERVICE(ALL) is equal to the DB2 GRANT ALL ON TABLE… and does not include the CREATE privilege.

If the view rule grants the CREATE privilege to create a view, CA ACF2 Option for DB2 performs additional security checks. These security checks prevent a user from creating a view that exceeds his authority on the base tables or views. In the first check, CA ACF2 Option for DB2 checks the %RCHANGE or %CHANGE statements for change authority to determine if the user’s primary ID can change the view rule set for access to the new view. If the user can change the view rule set, CA ACF2 Option for DB2 then checks to see if the user can change the rule sets associated with the base tables or views. If the user can administer the new view rule set but not the base table or view rule set, CA ACF2 Option for DB2 reports an ADM violation against the base table or view and prevents the creation of the new view.

CA ACF2 Option for DB2 performs additional security checks for each of the privileges that a user can hold on the view being created. If, for any reason, one of the user’s primary or secondary Ids has an access privilege on the view being created that he does not have on the base tables or views, CA ACF2 Option for DB2 fails the request to create the view and reports the violation. The SQL violation returned to the user states that the user has no authority to create the view. The ACFRPTRV report however shows the violation against the base table or view for the exact privilege that failed.

Ownership and Create View Security

In native DB2 security, creating a view automatically makes you the owner of that view. In CA ACF2 Option for DB2, you are not necessarily the owner just because you created the view. However, even if you do own the CA ACF2 Option for DB2 rule governing a view you are creating, you can be denied from creating the view. Also, if you have more administrative authority over the new view rule than the originating view or table rule, you are denied the authority to create the new view. With Create View Security, the following are the checks that are made when a user (USER1) issues an SQL request to create a view (TEST1.VIEW) on a table (TEST2.TABLE) in database TESTDB1:

  1. AUTHORITY TO CREATE A VIEW—To be able to create a view, USER1 must have the CREATE privilege or must be the owner (LIDOWNER or UIDOWNER) in the TEST1.VIEW rule or must have the system DBADM or SYSADM privilege. If not, USER1 cannot create a VIEW with the name TEST1.VIEW. An ACF2 violation record is cut for TEST1.VIEW with the service of CREATE.
  2. RULE CHANGE AUTHORITY—If the user has the ACF2 authority to change the rule for the TEST1.VIEW view (security officer, %CHANGE, or %RCHANGE), they must also have the ability to change the rule for the TEST2.TABLE table. If not, USER1 cannot create a VIEW and a violation record is cut for the TEST2.TABLE with a service of ADM.
  3. AUTHORITY TO SELECT FROM THE TABLE—USER1 must have the SELECT privilege in the rule for the TEST2.TABLE table or must be the owner (LIDOWNER or UIDOWNER) in the TEST2.TABLE rule, or USER1 must have the DBADM privilege in the TESTDB1 database rule or be the owner in the database rule, or USER1 must have the DATAACCESS or SYSADM privilege. If not, USER1 cannot create a VIEW and a violation record is cut for the TEST2.TABLE with a service of SELECT.
  4. AUTHORITY FOR ALL OF THE OTHER TYPES OF TABLE ACCESS—USER1 must have the same or greater access (INSERT, DELETE, and UPDATE for each column that is included in the view) to the table in the TEST2.TABLE rule than they have to the view in the TEST1.VIEW rule. In addition, if USER1 is the owner (LIDOWNER or UIDOWNER) of the view in the TEST1.VIEW rule, they must be the owner in the TEST2.TABLE rule. If not, USER1 cannot create a VIEW and a violation is cut for the TEST2.TABLE table with the service that failed the check. These checks stop a user from creating a view that gives him more access than he already has on the table.

The following rule gives CLERK1 full access to tables and views with his own high‑level qualifier, yet he does not own the tables and views and he cannot create or drop them in DB2.

$KEY(clerk1.******************) TYPE(tbl)
 UID(clerk1) SERVICE(all) ALLOW

CLERK1 would not be allowed to create a view executing the following SQL command:

CREATE VIEW CLERK1.VIEW AS SELECT * FROM CLERK1.TABLE

With the Create View Security, CLERK1 cannot create this view because the rule does not grant CREATE privilege.

The following rule allows CLERK 1 to create and drop the view:

$KEY(clerk1.******************) TYPE(tbl)
 UID(clerk1) SERVICE(create,all) ALLOW

If the following rule was defined to allow CLERK1 to own any tables and views created and CLERK1 had select access to a table and tried to create a view, CLERK1 would not be allowed to create the view. Request is denied because he has more access authority over the new view (he owns it) than he does over the base table.

$KEY(clerk1.******************) TYPE(tbl)
$LIDOWNER(clerk1)
 UID(clerk1) SERVICE(create) ALLOW

Selecting Access

$KEY(clerk2.******************) TYPE(tbl)
 UID(clerk1) SERVICE(select) ALLOW

Creating the View

CREATE VIEW CLERK1.VIEW1 AS SELECT * FROM CLERK2.TABLE

In the case where you have full access or ownership rules and CLERK1 wants to create a “select only view” as in the previous scenario, you can make a simple change in your rule structure to allow it. For example, set up a rule for CLERK1X. Then CLERK1 can create a fully‑qualified view using CLERK1X as the high‑level qualifier. Make sure CLERK1 does not have administrative authority for the CLERK1X rule and the new create view processing approves the creation of the view.

$KEY(clerk1x.******************) TYPE(tbl)
 UID(clerk1) SERVICE(create, select) ALLOW

Now the following command creates the view:

CREATE VIEW CLERK1X.VIEW1 AS SELECT * FROM CLERK2.TABLE

Important! Because you can modify a table’s data using access through a view, you should control who has access to views after they are created and who can change view rule sets.

The %CHANGE and %RCHANGE control statements should be judiciously controlled and perhaps not used with view rule sets, since they can inadvertently give the changer access to table data that the administrator of the view rule did not intent.

In addition, views created through statics SQL statements in an application plan or package have special considerations. Because of the unique processing by plans and packages, secondary IDs are not available during the creation of the view. This peculiarity causes CA ACF2 Option for DB2 to bypass security checks for privileges granted to secondary Ids. Because secondary Ids are not checked during view creation, CA ACF2 Option for DB2 might allow a user to create a view that exceeds his authority on a table. This could enable the user to then use their secondary ID to update the table’s data through the view after it is created. For this reason, we strongly suggest that you ensure adequate administrative controls are in place for creating views through application plans.