9 Replies Latest reply: Jan 18, 2013 4:26 AM by Niket Kumar RSS

    expdp users, roles, grants (privs)

    DanceRat
      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
          what is the actual objective?
          • 2. Re: expdp users, roles, grants (privs)
            TSharma-Oracle
            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
              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
                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
                  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
                    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
                      There are some databases that have 1000+ users and no tables, just rights to tables.
                      • 8. Re: expdp users, roles, grants (privs)
                        DanceRat
                        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
                          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....