This discussion is archived
9 Replies Latest reply: Jan 18, 2013 2:26 AM by Niket Kumar RSS

expdp users, roles, grants (privs)

dancerat Newbie
Currently Being Moderated
So, I think this works - still in the process of testing - but what I want to do is the following:

1) Export ONLY users, passwords, and privs from TEST
2) Clone PROD --> TEST
3) Drop all users in TEST excluding system users.
4) Import original users file from Step 1.

I think I've got it with the following. Am I missing anything? I'm testing, but if someone else has done this, I would be happy to hear from something I may be missing.

JOB_NAME=EXPDP_USERS
DIRECTORY=DTPUMP
REUSE_DUMPFILES=Y
FULL=Y
DUMPFILE=users_test.dmp
LOGFILE=users_test_expdp.log
INCLUDE=SCHEMA:"IN (SELECT USERNAME FROM dba_users where default_tablespace NOT in('SYSAUX','SYSTEM'))",ROLE,USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
  • 1. Re: expdp users, roles, grants (privs)
    Niket Kumar Pro
    Currently Being Moderated
    what is the actual objective?
  • 2. Re: expdp users, roles, grants (privs)
    TSharma-Oracle Guru
    Currently Being Moderated
    Your script will export all the Users with all the users data like tables and other objects. If you want NO tables at all than
    eith you have to download TOAd and go to users and take the script from there and rerun in test OR you have to create the users manually.

    You can run this query in prod and run the output in Test

    select 'create user ' || username || ' identified by values '''' || password || '''' || 'default tablespace ' || default tablespace || from dba_users
  • 3. Re: expdp users, roles, grants (privs)
    dancerat Newbie
    Currently Being Moderated
    I thought that was fairly clear by my four steps:

    Clone a test database from Production but drop the production users and bring the test users (passwords) and privs back in the new clone.
  • 4. Re: expdp users, roles, grants (privs)
    dancerat Newbie
    Currently Being Moderated
    It is my understanding that with expdp if I use an include statement, that leaves out everything else, including tables and table structures unless I implicitly include them.
  • 5. Re: expdp users, roles, grants (privs)
    Niket Kumar Pro
    Currently Being Moderated
    ok,,,

    from step3:

    you are going to delete all users except system.... leads to all your prod data removed.


    second thing if you are cloning from prod to test then test should have same users as of prod and should be like production...

    if you only want prod data then you can take table level dump and import it in your test tables...thats how you need not to drop users ....
  • 6. Re: expdp users, roles, grants (privs)
    dancerat Newbie
    Currently Being Moderated
    Okay I figured it out.

    To import in users, roles, and grants only and exclude any tables associated with them you do have to export out everything associated with the schemas, but when the import is done, exclude tables. I'm still testing, but it looks pretty good so far.

    expdp file should look like this:

    JOB_NAME=EXPDP_USERS
    DIRECTORY=DTPUMP
    FULL=Y
    DUMPFILE=users_privs.dmp
    LOGFILE=users_expdp.log
    INCLUDE=SCHEMA:"IN (SELECT USERNAME FROM dba_users where default_tablespace NOT in('SYSAUX','SYSTEM'))",ROLE,USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA

    impdp file should look like this:

    JOB_NAME=IMPDP_USERS
    DIRECTORY=DTPUMP
    DUMPFILE=users_privs.dmp
    LOGFILE=users_impdp.log
    EXCLUDE=TABLE
  • 7. Re: expdp users, roles, grants (privs)
    dancerat Newbie
    Currently Being Moderated
    There are some databases that have 1000+ users and no tables, just rights to tables.
  • 8. Re: expdp users, roles, grants (privs)
    dancerat Newbie
    Currently Being Moderated
    k,,,

    from step3:

    you are going to delete all users except system.... leads to all your prod data removed.

    -- This is correct - I've also excluded the application user that owns the schema with all the app objects and tables.

    second thing if you are cloning from prod to test then test should have same users as of prod and should be like production...

    -- This is not the way it is in our environment - we have different users in PROD and TEST with different privileges.

    if you only want prod data then you can take table level dump and import it in your test tables...thats how you need not to drop users ....

    -- Can't do that. Too much data. Faster to clone

    The impdp is pretty good. The only issue is that the table grants don't get brought in with the roles, but that's not too bad. The roles will remain intact for the most part, it's the users that need to be brought back in with the correct passwords.
  • 9. Re: expdp users, roles, grants (privs)
    Niket Kumar Pro
    Currently Being Moderated
    why don't you only take dump of those schemas which have data in tables......
    and import them in test enviornment.....
    that's how you save time also....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points