IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
Hi, We are having DB2V12 Level 100. We are looking for best approach for two things
1). 'Revoke Sysadm' from existed userids'.
2). Databases are created by userid. so we need to revoke implicitly granted access (like CreateDB, Load, DBADM,..etc).
Can you please give your valuable suggestions if you worked already on the above things.
Life will be much easier for you if you wait until you are in Level 500 - when you can use TRANSFER OWNERSHIP to transfer ownership of databases etc to a continuing id. Otherwise when you revoke the SYSADM then databases (etc) owned by the id will be dropped.
So when you are in FL500 transfer ownership of all objects owned by the id to a continuing id, rebind any packages owned by the id. Then REVOKE NOT INCLUDING DEPENDENT PRIVILEGES.
Personally I have a job that I use to extract the SYS..AUTH and SYSPACKAGE VALID='N' rows, run the revoke, re-extract the rows, ROLLBACK and then compare the two extracts - ensuring that all the differences are expected. This was written several versions ago. On my to-do list is to enable the SYS..AUTH_H tables - I can then see what happened when I did the revoke.
Finally. DO NOT GRANT SYSADM TO AN INDIVIDUAL'S USERID. Just don't. You end up in this situation. Indeed, don't grant DBADM or any other privilege that is used to create objects that are used by other users. (A private database or table is OK, but not one that has to continue past the user's privilege.) Grant SYSADM (etc) to a secondary auth-id and join the users to that id. (different methods depending on RACF, ACF/2, TSS). When the user's privilege needs to be revoked, all you need to do is disconnect them from the shared secondary auth-id.
Thank you so much James. I will wait until FL 500. Please share if you have any queries to validate the objects, packages after revoking SYSADM. So that we can make sure no impact with this process. Once again Thanks..