IBM Support

Restrictions on the use of group privileges when executing DDL statements or binding packages

Troubleshooting


Problem

User privileges that are derived from group memberships are not considered when you create views, materialized query tables (MQTs), SQL routines, triggers; execute certain other DDL statements; or bind packages that contain static SQL. The result is that users are unable to perform these operations because they appear to be missing privileges on the database, even when the privileges have been granted to groups to which the users belong.

Symptom

Problem symptom 1:

A security group named DEVGROUP that is used primarily by developers has been granted select privilege on a table T1. The same group has also been granted the necessary privileges for creating an SQL procedure. When a user that is a member of group DEVGROUP attempts to create an SQL procedure SP1 that selects data from table T1, they are unable to do so. They receive an error (SQLSTATE 42501, SQLCODE -551), which indicates that they are not authorized to select data from table T1.

This appears to contradict that the group that the user is a member of has been granted the privileges required to do so.

Problem symptom 2:

A user SALLY develops a static SQL application that queries table T1. SALLY belongs to a group that has been granted the privilege to query table T1, but when SALLY attempts to bind or precompile the application, the bind step fails with SQLCODE -551 (SQL0551).

When SALLY is explicitly granted the same privilege to query table T1, SALLY is able to successfully prepare the application.

Cause

Group membership can be managed with operating system groups, or if you use customized security group membership plugins, in a separate database or LDAP directory. Operating system group membership is not managed directly within the database and changes to OS group membership are not communicated to Db2. For this reason, user privileges that are derived from group memberships are not considered when users execute certain DDL statements or bind packages.

To successfully execute DDL statements that contain references to other database objects or to bind packages containing static SQL, users must have explicit privileges on all of the database objects referenced through their authorization ID or the database group PUBLIC.

Database privileges can be granted and revoked explicitly to user authorization IDs or to the database group PUBLIC by execution of the GRANT and REVOKE statements. Privilege changes that are affected by the execution of the GRANT and REVOKE statements take effect immediately.

As of Db2 UDB Version 8.2:

For the privilege to directly execute a dynamic DML SQL statement in a dynamicrules (RUN) package, the following are checked:

  • The authorization ID of the current user
  • The group privileges of the group that the current user belongs to
  • The database group PUBLIC

For the following DDL SQL statements:

  • ALTER TABLE ADD CHECK CONSTRAINT
  • CREATE FUNCTION
  • CREATE INDEX EXTENSION
  • CREATE METHOD
  • CREATE PROCEDURE
  • CREATE SCHEMA
  • CREATE TABLE
  • CREATE TRANSFORM
  • CREATE TRIGGER
  • CREATE VIEW
  • DECLARE GLOBAL TEMPORARY TABLE

The specific privileges required to reference another database object within the DDL statement, that is, to reference a database object other than the one being directly created or altered by the DDL statement, are checked for against the following:

  • The authorization ID of the current user
  • The database group PUBLIC

For packages containing static DML SQL statements, at package bind time, the privileges to reference database objects within the static SQL statements the following are checked:

  • The authorization ID of the current user
  • The database group PUBLIC

When you precompile static forms of DDL statements, it is important to note that the privileges required to create the database objects and to reference other database objects within these objects are not checked then. Instead these privileges are checked at package run-time when the DDL is executed and the database objects are created or altered.

To successfully reference database objects in SQL statements, users must have the specific privileges database privileges to do so. These privileges must be explicitly granted to the user's authorization ID or to the database group PUBLIC. If users have privileges only on database objects referenced in a static DDL statement, they will not be able to successfully reference the database object in the statement.

The specific privilege or privileges required to reference a database object within a DDL statement or a static SQL statement can include one of the following:

  • EXECUTE on the function that is referenced
  • EXECUTE on the procedure that is referenced
  • EXECUTE on the method that is referenced
  • USAGE on the sequence that is referenced
  • CONTROL on the table, view, or nickname that is referenced
  • DELETE on the table or view that is referenced
  • INSERT on the table or view that is referenced
  • UPDATE the table or view that is referenced
  • SELECT on the table or view that is referenced

When it is successfully created, a database object is considered to be valid (versus invalid or inoperative) for as long as the definer of the database object continues to hold the necessary privileges on the database objects referenced within the definition.

If a required privilege on a database object referenced within the DDL definition of it is revoked from the definer of an object, the database object becomes invalid.

If a required privilege on a database object referenced within static DML SQL statement within a package is revoked from the binder of the package, the package becomes invalid.

Invalid database objects are marked as such within the database catalog tables. Invalid database objects are unavailable for use until they are successfully validated.

Examples

Note: For the specific privileges required to execute an SQL statement, refer to the SQL Reference topic for the specific statement. Some of the following examples make reference to the fact that users must have the necessary privileges as a prerequisite.

Example 1: Group membership privileges not adequate for executing SQL statements


A security group, DEVGROUP, used primarily by developers has been granted select privilege on a table T1. The same group has also been granted the necessary privileges for creating an SQL procedure. When a user that is a member of group DEVGROUP attempts to create an SQL procedure SP1 that selects data from table T1, they are unable to do so. They receive an error (SQLSTATE 42501, SQLCODE -551), indicating that they are not authorized to select data from table T1. This appears to contradict that the group that the user is a member of has been granted the privileges required to do so.
The cause of this relates to the fact that the user exclusively has the SELECT privilege on T1 through membership in the group DEVGROUP, however group level privileges on T1 are not checked when the containing database object, stored procedure SP1, is created. Since the user does not also have SELECT privilege on table T1 through their authorization ID or the group PUBLIC, the user will not be allowed to access the table and therefore as a consequence will not be able to create stored procedure SP1.

Example 2: Group membership privilege not adequate to bind application

A user SALLY develops a static SQL application that queries table T1. SALLY belongs to a group that has been granted the privilege to query table T1, but when SALLY attempts to bind or precompile the application, the bind step fails with SQLCODE -551 (SQL0551N). However, when SALLY is explicitly granted the same privilege to query table T1, SALLY is able to successfully prepare the application.

To successfully bind the package associated with her embedded SQL application, user SALLY must first have the select privilege on table T1 granted to her authorization ID or have that privilege granted to group PUBLIC.

Example 3: CREATE TABLE statement referencing another table

Consider the following SQL statement that creates a table T2 that is identical to table T1 and that is populated with the same row data values found in table T2:

CREATE TABLE T2 AS SELECT * FROM T1

To successfully execute this SQL statement, the current user must have:

  • Privileges required to execute the CREATE TABLE statement through one of:
    • The authorization ID of the current user
    • The group privileges of the group that the current user belongs to
    • The database group PUBLIC
  • Privilege to select from table user1.t1 through one of:
    • Their authorization ID
    • Database group PUBLIC

Even though the select privilege on user1.t2 might have been already granted to a group that the user is a member of the database manager, for security reasons, does not take this privilege into consideration.

Example 4: CREATE VIEW statement referencing a table

Consider a view defined as:

CREATE VIEW user2.v1 AS SELECT * FROM USER1.T2

To successfully execute this SQL statement, the current user must have:

  • Privileges required to execute the CREATE VIEW statement
  • Privilege to select from table user1.t2 through one of:
    • Their authorization ID
    • Database group PUBLIC

Even though the select privilege on user1.t2 might have been already granted to a group that the user is a member of, the database manager, for security reasons, does not take this privilege into consideration.

Example 5: CREATE PROCEDURE statement referencing a table

Consider an SQL stored procedure defined as:

CREATE PROCEDURE user2.proc1 (i integer) LANGUAGE SQL SPECIFIC ax
BEGIN
INSERT INTO USER1.T1 values(i + 1000);
END%

To successfully execute this SQL statement, the current user must have:

  • Privileges required to execute the CREATE PROCEDURE statement through one of:
    • The authorization ID of the current user
    • The group privileges of the group that the current user belongs to
    • The database group PUBLIC
  • Privileges required to select from table user1.t2 through one of:
    • Their authorization ID
    • Database group PUBLIC

Even though the insert privilege on user1.t1 might have been already granted to a group that the user is a member of, the database manager, for security reasons, does not take this privilege into consideration.

Example 6: CREATE FUNCTION statement referencing a function

CREATE FUNCTION user2.sourced1(parm1 short)
RETURNS short
SPECIFIC sourced1
SOURCE user1.f1(short)%

To successfully execute this SQL statement, the current user must have:

  • Privileges required to execute the CREATE FUNCTION statement through one of:
    • The authorization ID of the current user
    • The group privileges of the group that the current user belongs to
    • The database group PUBLIC
  • Privilege to execute function user1.f1 through one of:
    • Their authorization ID
    • Database group PUBLIC

Even though the execute privilege on user1.f1 might have been already granted to a group that the user is a member of the database manager, for security reasons, does not take this privilege into consideration.

Example 7: ALTER TABLE statement where new check constraint references a function

ALTER TABLE T1
ADD CONSTRAINT C1
CHECK ( user1.f2(DEPTNAME))
NOT ENFORCED%

To successfully execute this SQL statement, the current user must have:

  • Privileges required to execute the ALTER TABLE statement through one of:
    • The authorization ID of the current user
    • The group privileges of the group that the current user belongs to
    • The database group PUBLIC
  • Privilege to execute function user1.f2 through one of:
    • Their authorization ID
    • Database group PUBLIC

Even though the execute privilege on user1.f2 might have been already granted to a group that the user is a member of the database manager, for security reasons, does not take this privilege into consideration.

Resolving The Problem

To ensure that users can successfully create views, materialized query tables (MQTs), SQL routines, triggers, and bind packages containing static SQL, you must do one of the following:
  • Explicitly grant the privileges that are required to reference the other database objects to the individual user ID.
  • Explicitly grant the privileges that are required to reference the database object being referenced to PUBLIC. Keep in mind, however, that the database group PUBLIC includes all connected users.
  • DB2 9.x has enhanced ROLE functionality. Administrators can consider using a DB2 ROLE to manage memberships directly within DB2, as opposed to Operating System groups. While privileges and authorities granted to groups are not considered for these types of operations, administrators can avoid this restriction by using roles instead.
  • Roles granted to groups are not considered for packages containing SQL, views, materialized query tables (MQT), triggers and SQL routines.

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlOAAU","label":"Security and Plug-Ins->Authorization and Privilege"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]

Document Information

Modified date:
07 February 2022

UID

swg21224422

Page Feedback