6 Replies Latest reply: Apr 16, 2013 4:20 AM by okKarol RSS

    Privileges needed for Import and export

    832104
      Greetings All,

      I am working on eliminating the DBA role from users in our 11g DB.

      In our system, a backup of a user schema is done via an export using the user ID/PW. A .bat file is generated using the user ID and Password to execute expdp.

      What privileges are needed for a user to export and import only a schema?

      I am aware of EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.

      Are these the roles needed?

      Would a user with these roles be able to export the full database (all schemas?).

      I’d like to limit each user to export/import their own schema.

      Thanks for your time,

      Bob
        • 1. Re: Privileges needed for Import and export
          Srini Chavali-Oracle
          Pl post details of OS and database versions.

          EXP_FULL_DATABASE and IMP_FULL_DATABASE are powerful roles and should be granted with care. Without these roles, users can only export/import schemas they have access to.

          http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2641
          http://docs.oracle.com/cd/E11882_01/network.112/e16543/guidelines.htm#DBSEG499

          HTH
          Srini
          • 2. Re: Privileges needed for Import and export
            832104
            Srini,

            I think your statement “Without these roles, users can only export/import schemas they have access to.” Is false because I tried to export the cm_test schema using the cm_test user ID and password and it failed, see below.


            expdp cm_test/cm_test@local schemas=cm_test directory=SR_BACKUP dumpfile=CM_TEST.dmp logfile=SR_TEST_export.log exclude=STATISTICS



            Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            ORA-31626: job does not exist
            ORA-31633: unable to create master table "CM_TEST.SYS_EXPORT_SCHEMA_05"
            ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
            ORA-06512: at "SYS.KUPV$FT", line 1020
            ORA-01031: insufficient privileges


            The OS is windows 7 Prof.


            Any suggestions?

            Bob
            • 3. Re: Privileges needed for Import and export
              Srini Chavali-Oracle
              Bob

              Pl identify the privileges/roles that this CM_TEST schema has been granted. This MOS Doc may help

              Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]

              HTH
              Srini
              • 4. Re: Privileges needed for Import and export
                Dean Gagne-Oracle
                I don't know what the minimum set of privs are, but I test all the time with

                grant connect,resource, unlimited tablespace to user identified by user;

                I'm not sure if you can eliminate any of these and still have Data Pump work for the schema that is running the Data Pump job.

                Dean
                • 5. Re: Privileges needed for Import and export
                  798133
                  For IMPORT, Whatever objects User need to import, that user should have privileges to create those objects by having privilege “ CREATE <OBJECT TYPE > to import in its own schema itself.

                  Eg: CREATE TABLE, CREATE TRIGGER,CREATE PROCEDURE...etc

                  For EXPORT, only CREATE SESSION privileges is enough to export from its own schema.

                  Minimum is CONNECT & RESOURCE is enough for doing export and import within own schema. To another schema you should need authentication in other schema objects or DBA role

                  Edited by: user12043395 on Feb 26, 2013 12:18 PM
                  • 6. Re: Privileges needed for Import and export
                    okKarol
                    Just need to GRANT DATAPUMP_EXP/IMP_FULL_DATABASE role in 11g or EXP/IMP_FULL_DATABASE full database
                    and do not forget after create the user set as defalut role:
                    ALTER USER DEFAULT ROLE ALL;