1 2 Previous Next 15 Replies Latest reply: Jul 27, 2013 11:55 AM by yxes2013 RSS

    expdp for non dba

    yxes2013

      HI all,

       

      11.2.0.1

      Aix 6.1

       

      I am a dba and I am used to export tables using "oracle"  ID in aix which has by default dba privilege. The dump will go to by default to the DATA_PUMP_DIR.

      My question is how can I grant access to a non-dba user who has "user01" Aix id to this DATA_PUMP_DIR, So that she can import it to her oracle schema "user01".

      Do I need to member her in dba group on unix? But this can be a security breach since she is a developer only and he can become dba?

       

       

      Thanks a lot,

       

      zxy

        • 1. Re: expdp for non dba
          DK2010

          Hi,

           

          It Required Grant read,write on Directory to user and also   DATAPUMP_IMP_FULL_DATABASE and DATAPUMP_EXP_FULL_DATABASE

          Read Doc:Data Pump Export

           

          HTH

          • 2. Re: expdp for non dba
            sybrand_b

            Your question clearly indicates again you didn't read documentation.

            If you would have done so, you would have known users without the EXP_FULL_DB_ROLE can only export their own data.

             

            I think many people would prefer you, in accordance with the Etiquette of this Forum, would consult documentation first.

             


            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: expdp for non dba
              yxes2013

              Thanks Dk,

               

              I can not understand this process, Can the user who is not member in the OS group can write to the datapump dir at OS side?

              Today she login to aix id "user01" and tried to copy the expat.dmp to the datapump dir but got permission denied. How can the database user grant the OS id to the file level folder for read/write?

              The grant you mentioned is on the database side only. I am confusedddddddddddd.

              • 4. Re: expdp for non dba
                DK2010

                Hi,

                 

                What do  want any user come and check/copy the file generated by oracle.

                You have to use the OS level privilege for that, from DB level you can not give that permission

                • 5. Re: expdp for non dba
                  yxes2013

                  Sybrand  Sir,

                   

                  I can not understand this process, Can the user who is not member in the OS group can write to the datapump dir at OS side?

                  Today she login to aix id "user01" and tried to copy the expat.dmp to the datapump dir but got permission denied.

                  How can the database user grant the OS id to the file level folder for read/write?

                  The grant you mentioned is on the database side only. I am confused ...helpppppppppp

                   

                  It is not like the old exp where I can give the file to the user and she can import it anywhere and has her ownership of it???

                  • 6. Re: expdp for non dba
                    yxes2013

                    Actually this is the situation.

                     

                    The user is a developer who ask me for some  data for testing. I have the prod database and she has the dev database. So I give her na expdat.dmp.

                    How can she import it if she is not a  dba? Before she  can do it using exp & imp. But now she can not do it if its in expdp format?

                     

                     

                    Thanks

                    • 7. Re: expdp for non dba
                      sybrand_b

                      On AIX you can use ACLs (Access Control Lists) to grant privileges beyond the standard privileges.

                       

                      See http://www.ibm.com/developerworks/aix/library/au-acl/

                       


                      Sybrand Bakker

                      Senior Oracle DBA

                      • 8. Re: expdp for non dba
                        yxes2013

                        Well maybe that will need the root sysadmin user to do it.The admin is out of town. So similarly another option is  to chmod 777 the data_pump_dir folder?

                        So every developer can copy file to it? Thanks

                        • 9. Re: expdp for non dba
                          DK2010

                          Hi,

                           

                          Or you(as dba) can copy the dump file at some common location /NFS so any one can access. ane leave the oracle alone from this like change the dump directory permission

                          • 10. Re: expdp for non dba
                            yxes2013

                            Well, there is no issue for anyone to access it . The issue is a developer can not access or copy file to the DATA_PUMP_DIR. I have given her the dump on flash drive, and

                            she copied  it to her local login id. But she  can not import it due to the fact she can  not  put it on the right place.

                             

                             

                            Thanks

                            • 11. Re: expdp for non dba
                              Richard Harrison .

                              Hi,

                              Just to be clear - the developer has the dump on a flash drive that is now plugged in to her local desktop machine? Datapump is a server based process it cannot access the files on a remote users pc so that won't work.

                               

                              If she has managed to copy it to the database server but it is located in /home/user/expdat.dmp (or wherever) but she cannot copt it to the os directory which DATA_PUMP_DIR points at and you can;t change the permissions on that os directory then what you can do is this:

                               

                              CREATE DIRECTORY new_dir as '/home/user';

                               

                              grant all on directory new_dir to username_of_developer_here;

                               

                              then impdp should be able to be run like this:

                               

                              impdp user/pass directory=new_dir

                               

                              The next issue is I'm sure going to be that the objects were exported from live as a certain user and the developer wont be able to load them anyway due to missing database permisssions but lets see if we can get past the first problem first....

                               

                              old exp/imp can still be used though they are missing functionality now and can't copw with some database features (deferred segment creation is a prime example of that)

                               

                              Regards,

                              Harry

                               

                              <moderator edit - removed link to personal blog - pl see FAQ link on top right of this page>

                              • 12. Re: expdp for non dba
                                yxes2013

                                hellooo...why is my answer to this post gone? or not posted and updated?

                                And why is I still have non-read post in my "communications" tab when I already ready them all?

                                The forums seem frozen???

                                • 13. Re: expdp for non dba
                                  yxes2013

                                  Thanks Harry,

                                   

                                  Here are my points:

                                  ==================

                                  Just to be clear - the developer has the dump on a flash drive that is now plugged in to her local desktop machine? Datapump is a server based process it cannot access the files on a remote users pc so that won't work.

                                  ==================

                                  ****YES

                                   

                                  ==================

                                  If she has managed to copy it to the database server but it is located in /home/user/expdat.dmp (or wherever) but she cannot copt it to the os directory which DATA_PUMP_DIR points at and you can;t change the permissions on that os directory then what you can do is this:

                                   

                                  CREATE DIRECTORY new_dir as '/home/user';

                                  ==================

                                  ****We are in AIX unix systems.

                                  "/home/user"  has different ownership and group (users:dev)  compared to oracles (oracle:dba)

                                  How can oracle be able to write to his directory at OS level?

                                   

                                  ==================

                                  >grant all on directory new_dir to username_of_developer_here;

                                  >then impdp should be able to be run like this:

                                  > impdp user/pass directory=new_dir

                                  ==================

                                  **** This is on database side, but how about its permission on the OS side?

                                   

                                  ===================

                                  The next issue is I'm sure going to be that the objects were exported from live as a certain user and the developer wont be able to load them anyway due to missing database permisssions but lets see if we can get past the first problem first....

                                  ===================

                                  ****Ok I will try this first. But the problem is the DBA for  the dev sever is on SL. I do not have access to it as I am

                                  the dba for the PROD server

                                   

                                  ===================

                                  old exp/imp can still be used though they are missing functionality now and can't cope with some database features (deferred segment creation is a prime example of that)

                                  ===================

                                  ****We can not use this, because we have DATABASE VAULT and ASO-TDE (advance security option - transparent data encryption)  implemented. Only expdp is supported

                                   

                                   

                                  Thankssssssssss so many.

                                  • 14. Re: expdp for non dba
                                    Richard Harrison .

                                    HI,

                                    the user should be able to grant oracle s permissions on the directories under /home/user. Just chmod 777 the directory in the os that corresponds to the directory created inside the database. Then oracle shoul be ble to read write and it should all work.

                                     

                                    cheers,

                                    harry

                                    1 2 Previous Next