DB2 Script out existing user permissions

DB2 10.5 on Windows

I am getting ready to copy the production database to our test server and then scrub all the sensitive data.  What I would like to do is to script out all the permissions on the test database before I restore the production database.

Using TOAD, I can click on a user and view details and it will script all the grants for me, but there are about 3 dozen users in test and I don't want to do each one by hand.  I would love to capture the SQL that TOAD is generating to get that script and then I could run if for all the users.

Does anyone have a script like this?  That will give me the grant statements for all the users on a given database?  I could do the one at a time method but that will be a real pain.

Thank you!

Jim
Jim YoumansSr Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Jim,

I saw the title and my first thought was that "I haven't heard from Jim in a while".  Then I saw you were the author.  :)

When you say permissions, do you mean all of the object grants?
Jim YoumansSr Database AdministratorAuthor Commented:
Yes, like I said, TOAD will do a nice job of it but you have to click on each user and then save the permissions and then click on next one.  I am looking for a query that will do the same so it will give me the grants for each user.  

Looking at this, I think it is a good start.

SELECT * FROM SYSIBMADM.PRIVILEGES
 WHERE AUTHIDTYPE = 'U'

Open in new window


And then build the grant statement from that but not 100% sure that will capture everything.

Jim
Jim YoumansSr Database AdministratorAuthor Commented:
Here is what I got so far.  I just want to make sure I don't miss anything since once I restore the new database over the old one, it's gone.

SELECT 
'GRANT ' || TRIM(PRIVILEGE) || ' ON ' || TRIM(OBJECTTYPE) || ' ' || TRIM(OBJECTSCHEMA) || '.' || TRIM(OBJECTNAME) || ' TO ' || TRIM(AUTHID) || ';'
FROM SYSIBMADM.PRIVILEGES
WHERE AUTHIDTYPE = 'U' 
ORDER BY AUTHID 

Open in new window

Kent OlsenDBACommented:
Good work!

That's actually a lot easier than the script that I (used to) use.  I'm keeping a copy of yours for future use!  :)

Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim YoumansSr Database AdministratorAuthor Commented:
As always, thank you!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.