Here is an interesting challenge I faced recently. I was going to replace (rebuild) a view with new DDL and I got error code SQL0551N which meant I didn’t have authorization to do so. How could this happen? I was using instance owner’s user id (db2inst1) which meant 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: "DB2INST1". Operation: "REPLACE VIEW". Object: "INFO.AUTO". SQLSTATE=42501
Could this be because DB2INST1 is not the owner of this object INFO.AUTO? Let’s check.
db2 "select char(owner,20) as owner, char(viewschema,20) as schema, char(viewname, 60) as viewname from syscat.views where viewname = 'VW_CAPITAL_PLAN_DOMESTIC_AUTO'" OWNER SCHEMA VIEWNAME ------ ----------- --------------- PAVAN INFO AUTO 1 record(s) selected.
And we are right. From the above the user id “PAVAN” owns the object INFO.AUTO.
This explains why “DB2INST1” could not replace the view. Although “DB2INST1” has the highest privilege at the database and instance level, it still would not be able to rebuild a view as it is owned by some other user.
In lower (non-production) environments, it is usually acceptable for users to be able to create their own objects. In production environments, things get more restrictive. Exceptions are vendor applications for which the application user id requires elevated privileges for the application to function.
Assuming, the app user id (in this case ‘PAVAN’) does not need an elevated privilege, our next step should be to understand how to fix this.
For a user to be able to create and own a database object, the user id should have ‘CREATEIN’ authorization in a given schema. In this case, user id ‘PAVAN’ should have been granted ‘CREATEIN’ in schema ‘INFO’. 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 = 'INFODV' " GRANTOR GRANTEE SCHEMA CREATEINAUTH ----- ----- ------- ----- SYSIBM DB2INST1 INFO Y DB2INST1 PAVAN INFO Y 2 record(s) selected.
From the above, it is clear that two users ‘DB2INST1’ and ‘PAVAN’ (identified by the column ‘grantee’) have ability to create objects in the schema ‘INFO’.
As a side note, instead of focusing on a specific schema, if you would like to fix this kind of problem in the entire database, modify the ‘WHERE’ clause like below.
What grantee <> ‘DB2INST1’, does is it lets DB2 to filter out rows that pertain to Instance Owner (DB2INST1). We do this to focus on users that are not Instance Owner.
db2 "select char(grantor,20) as grantor, char(grantee,20) as grantee, char(SCHEMANAME,20) as schema, CREATEINAUTH from SYSCAT.SCHEMAAUTH where grantee <> 'DB2INST1'"
Now, let’s get back to our original goal of tightening user privileges. As discussed earlier, we might NOT want the user id ‘PAVAN’ to actually have ability to create objects in the schema ‘INFO’. It could be simply because we do not want un-necessary objects to be created and populated in production database.
So, how do we fix this? By revoking ‘PAVAN’ ability to create database objects in ‘INFO’.
db2 "REVOKE CREATEIN ON SCHEMA INFO FROM USER PAVAN" DB20000I The SQL command completed successfully.
Now, let’s check again to see if it worked.
db2 "select char(grantor,20) as grantor, char(grantee,20) as grantee, char(SCHEMANAME,20) as schema, CREATEINAUTH from SYSCAT.SCHEMAAUTH where schemaname = 'INFODV' " GRANTOR GRANTEE SCHEMA CREATEINAUTH ----- ----- ------- ----- SYSIBM DB2INST1 INFO Y 1 record(s) selected.
That did it. The user id ‘PAVAN’ does not privilege to create database objects in the schema ‘INFO’ any more. Only user id ‘DB2INST1’ has privilege to do so.
Now that we have plugged security holes, let’s go back and rebuild the view. But, first we would have to drop the view.
db2 "drop view INFO.AUTO" DB20000I The SQL command completed successfully.
Now, let’s create the view.
db2 "CREATE OR REPLACE VIEW INFO.AUTO (CUSTOMER_NAME, STATE) AS SELECT NAME, STATE FROM DB2INST1.T2 WHERE STATE = 'NY'" DB20000I The SQL command completed successfully.
In this blog post, we have looked at an example where you could be denied permission to rebuild a view even if you were using Instance Owner’s user id.
We also looked at how to plug one of the security holes by revoking ability to create database objects from user ids that actually do not need such privilege.