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.
Ta-da!
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.
7 Comments
Jeff, what is your recommendation for storing this as a template? Using Snippets? Also, do you know if I could do something similar in Modeler? I need to include grants on dbms packages and didn’t see how that could be done there.
Thanks for a constantly evolving product!
Hi Jeff,
I’ve been a long time toad user and now I’m in a shop that only uses sql developer. Overall, the conversion to sql developer has been much smoother than expected. However, I have only one major issue.
When in toad you can right click on a user and see the entire sql statement for that user. When you do that in sql developer only the create statement is displayed and none of the other grants and privs. Having to do the tedious step of cloning a user to see the sql is hopefully a short term solution. Is there any plan to enhance the sql tab to show the entire ddl needed to create a user?
Thanks,
Twisted Wrister
I’d like to, yes.
I forgot to include that it shows all privs: sys privs, direct granted privs and privs granted via roles.
This is what I use. I cleaned up the script a little, but it does everything you’re looking for and all you have to change the username in each union sections. You can also limit it to schemas or privileges. This has come in extremely handy for me with our auditing team. They’re always looking for “privileged” access and that’s why this was created.
select du.username,du.account_status,
nvl(NULL,’system privilege’) granted_role, sp.privilege,NULL OWNER,NULL TABLE_NAME
from sys.dba_users du, dba_sys_privs sp
where sp.grantee = du.USERNAME
AND du.USERNAME = ”
union all
select du.username, du.account_status,
nvl(NULL,’direct grant’) granted_role, listagg(tp.privilege,’,’) within group (order by owner,table_name,grantee) “PRIVILEGE”,OWNER,TABLE_NAME
from sys.dba_users du, dba_tab_privs tp
where tp.grantee = du.USERNAME
–AND OWNER in (‘schema’) — CHANGE AS NEEDED
–AND tp.privilege in (‘INSERT’,’UPDATE’,’DELETE’) — CHANGE AS NEEDED
AND du.USERNAME = ”
group by du.username,du.account_status,owner,table_name
union all
select rp.grantee,du.account_status,
rp.granted_role,listagg(tp.privilege,’,’) within group (order by tp.owner,tp.table_name,rp.grantee) “PRIVILEGE”,tp.owner,tp.table_name
from dba_role_privs rp, dba_tab_privs tp,dba_users du
where rp.granted_role = tp.grantee
and rp.default_role = ‘YES’
and du.username = rp.grantee
–AND tp.OWNER in (”) — CHANGE AS NEEDED
–and tp.privilege in (‘INSERT’,’UPDATE’,’DELETE’) — CHANGE AS NEEDED
AND du.USERNAME = ”
group by rp.grantee,rp.granted_role,du.account_status,tp.owner,tp.table_name
order by 1,3,5,6,4
and surely you’ve turned this into a report, yes? 😉
Not myself specifically, but it was turned into a report. I created a view based on the sql, gave the BI team a very specific query to use (the view could handle the listagg function, but the results worked much better when it was pushed to the query) and let them run with it. Now the audit team can get the results themselves rather than asking me every 2-3 months! 😉 It just took some serious training of our auditing folks to start using the report rather than asking the DBA’s.