Privileges/security for stored procedure in DB2 LUW

Question

UPDATED question: The core of my problem is: The stored procedure I (User1) created is not able to select from the some specific table (table1 created by another user (User2)) due to:

CREATE OR REPLACE PROCEDURE TEST_SCHEMA.TEST_PROCEDURE(OUT r_count INTEGER)
LANGUAGE SQL
BEGIN
       SET r_count = (SELECT COUNT(*) FROM TEST_SCHEMA.TABLE1);
END

OK. No rows were affected SQLWarning: Code: 20480 SQL State: 0168Y --- The newly defined object "TEST_SCHEMA.TEST_PROCEDURE" is marked as invalid because it references an object "TEST_SCHEMA.TABLE1" which is not defined or is invalid, or the definer does not have privilege to access it.. SQLCODE=20480, SQLSTATE=0168Y, DRIVER=4.22.29

However, when I select from table1 in a normal query window there is no problem, hence I thought something was wrong about the security option on the stored procedure

SELECT COUNT(*) FROM TEST_SCHEMA.TABLE1

Table and stored procedure names are fully qualified. The stored procedure is created and executed by user1. The privilege given to the user1, to select from table1 , is a group privilege.

Answer 1

The procedure creator must have the corresponding privilege on statically referenced table either directly or via roles.

CREATE PROCEDURE (SQL) statement:

Authorization
The privileges held by the authorization ID of the statement must include at least one of the following authorities:

• If the implicit or explicit schema name of the procedure does not exist, IMPLICIT_SCHEMA authority on the database.
• If the schema name of the procedure refers to an existing schema, CREATEIN privilege on the schema.
• DBADM authority

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the procedure body.

To replace an existing procedure, the authorization ID of the statement must be the owner of the existing procedure (SQLSTATE 42501).

Group privileges are not considered for any table or view specified in the CREATE PROCEDURE (SQL) statement.