Resolving SQLCODE -551 on View Creation

I recently faced an interesting challenge. I was working to replace (rebuild) a view with updated DDL, but when executing the CREATE OR REPLACE VIEW statement, Db2 returned error code SQL0551N. This error means that the user ID doesn’t have authority to perform the operation. How could this happen? I was connected as the instance owner, and it has DBADM authority in the database. Even with the highest level of authority, I should be able to drop and/or re-create database objects… right?

Here was the error:

SQL0551N  The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.  Authorization ID: "LAW803".  Operation: "REPLACE VIEW". Object: "ICIMSLF.EMPLOYEE_VW".  SQLSTATE=42501

I had a feeling that the instance ID was not the owner of this object. To check, I executed the following SQL statement:

$ db2 "select char(owner,20) as owner, \
              char(viewschema,20) as schema, \
              char(viewname, 60) as viewname \
       from   syscat.views \
       where  viewname = 'EMPLOYEE_VW'"

OWNER                SCHEMA               VIEWNAME
-------------------- -------------------- ----------------------------------
DB2USER              ICIMSLF              EMPLOYEE_VW

  1 record(s) selected.

From the above output, the user id “DB2USER” owns the object.

This explains why the Db2 instance ID could not replace the view. Even though it has the highest privilege at the database and instance level, it still would not be able to rebuild a view because it is owned by some other user.

For a user to be able to create and own a database object, the user id must have CREATEIN authority for the given schema. In this case, user id DB2USER should have been granted ‘CREATEIN’ in a schema. Let us check if this is true.

$ db2 "select char(grantor,20) as grantor,
        char(grantee,20) as grantee,
        char(SCHEMANAME,20) as schema,
        CREATEINAUTH
        from SYSCAT.SCHEMAAUTH
        where schemaname = 'ICIMSLF'"

GRANTOR              GRANTEE              SCHEMA               CREATEINAUTH
-------------------- -------------------- -------------------- ------------
LAW803               DB2USER              ICIMSLF              Y
LAW803               DB2USER1             ICIMSLF              Y

  2 record(s) selected.

From the above, it is clear that two users (identified by the column GRANTEE) have ability to create objects in the schema, but only DB2USER can perform the REPLACE VIEW since they own the view as shown below from a db2look of the view.

SET CURRENT SCHEMA = "DB2USER ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2USER";
create view ICIMSLF.EMPLOYEE_VW AS SELECT EMPLOYEE.EMPLOYEE...

Any other user would have to perform a DROP VIEW followed by a CREATE VIEW in order to perform the replace option.