ThatJeffSmith

Trick: Getting All the Privileges (Grants) for a User

Tell Others About This Story:

This challenge has come up a few times recently for me. Just HOW can a SQL Developer user quickly build out a SQL script for a database user that will include ALL of their privileges, roles, and system grants?

I didn’t have a good answer until today. A group of DBAs at a large shipping company exercised my brain just right, such that I was able to figure it out. It’s a great reason to do group presentations – provides lots of opportunities to stretch your though processes in different directions. Anyways, here’s how you do it.

Clone the User

We’re not actually going to clone the user, we’re just going to pretend to.

View > DBA > Security > Users.

Open the user.

I'm splitting the object editor here to show the different panels concurrently.

I’m splitting the object editor here to show the different panels concurrently.

Now you can quickly see the ‘stuff.’ Their privs, grants, roles, etc. There’s even a SQL page. But I want MOAR SQL.

Right click on the user, Create Like.

This is like, um, cool and stuff!

This is like, um, cool and stuff!

Ta-da!

Check the include privs box!

Check the include privs box!

And observe the printed SQL

I LOVE IT when a plan comes together.

I LOVE IT when a plan comes together.

If you don’t want to have to change out the USER name, just leave the original – the dialog won’t complain, but the database will if you try to run it as your user already exists.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , , ,