Updated 25 May 2022
This is formerly a trick.
We made this just a simple action of opening the User and going to the SQL panel in the DBA, Security section.
If this isn’t working for you, it’s because you’re on an older version of SQL Developer…in which case, read on.
The Trick, if you’re on an older copy of SQLDev
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.
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.
And observe the printed SQL
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.