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.  

Views: 96

Reply to This

Replies to This Discussion

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..


Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.


Creating a function in DB2

Started by Jacob Ruchotzke in What's hot ? Jun 18. 0 Replies

With the help of our IT guy i have sort of gotten an example of how to create a function in our system. Can anyone help me with this? Please see the attached SQL file.Thanks in advanceContinue

Tags: function

Conversion of BLOB to String/ Text

Started by Jitesh Audichya in Application Development and DB2. Last reply by Jitesh Audichya Apr 24. 2 Replies

Hi All,Problem Statement:I have a field with BLOB data type in DB2 database, I want to extract this blob and convert it to Text. The text data after the conversion will be in Japanese characters. How can I write a select with the conversion from…Continue

Tags: on, DB2, conversion, text, to

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service