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.

Be sure you have ‘Grants’ enabled in your Preferences for Export.

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.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

7 Comments

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

  2. Twisted Wrister Reply

    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

  3. I forgot to include that it shows all privs: sys privs, direct granted privs and privs granted via roles.

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

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

Write A Comment