Error during stored procedure creation in DB2 database

Question

I am struggling with schemas while creating a stored procedure in DB2 database ( 10.5 version ). My user name is XYZ but I have to create a Stored procedure for schema ABC. When I am trying to execute the create procedure sql I get error message which looks like Schema related

Create procedure ABC.customInsert(
    IN temp INTEGER
)
BEGIN   
        INSERT INTO ABC.One_Column_table VALUES ( temp );       
END

Error Message:

Error:DB2 SQL error:SQLCODE:-551, SQLSTATE: 42501, SQLERRMC:XYZ;INSERT;ABC.One_Column_table

My current schema was showing XYZ earlier. ( result of select current_Schema from sysibm.sysdummy1). I have changed it to ABC. ( using SET CURRENT SCHEMA ABC). But still the same problem.

I am able to insert, select, create UDT etc in ABC schema but the problem exists only during stored procedure creation. Any idea what am I doing wrong ?

It seems you do not have the privileges to perform the action. Grant the role/privileges to the user and then try again read more here — Pirate X

mustaccio mustaccio · Accepted Answer · 2016-05-04T14:16:43

Based on your error message, SQLCODE -551 means that the user "XYZ" does not have the "INSERT" privilege on the table "ABC.One_Column_table".

Since you imply that you, when connected as XYZ, can insert into the table by issuing simple INSERT statements, it is possible that you possess the INSERT privilege indirectly, via a group membership. Group privileges are ignored for SQL statements in stored procedures, functions or triggers, as explained in this IBM technote.

You have two options:

Grant the required privileges on ABC.One_Column_table to the user XYZ directly.
Create a role (using the CREATE ROLE statement), grant the table privileges to that role, then grant the role to the user XYZ.

If you are curious, such behaviour is caused by the fact that static SQL statement (e.g. in a stored procedure) authorization is checked only during compilation, and the compiled code can then be executed without additional authorization checks. Groups are maintained outside the DB2 database, by the operating system, and it is possible that group membership changes after the stored procedure is compiled and without the database security administrator's knowledge. If group privileges were effective for static SQL, it would allow users who weren't originally authorized to run particular statements (i.e. were not members of the authorized group at the compilation time) still execute those statements, thus creating a security risk.

Roles, on the other hand, are maintained within the database itself by the database security administrator and thus are part of the same security landscape.

Error during stored procedure creation in DB2 database

1 Answers