Controlling datapump export access
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Controlling datapump export access

  1. #1

    Question Controlling datapump export access

    I'm looking for suggestions on how to allow a user the ability to datapump export from a certain schema without granting them the ability to log in to that schema. This is to allow a development team to fetch arbitrary sets of tables periodically from the production database, but specifically not allow them to log in and perform any DML. Any thoughts on how this might be accomplished?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    People does not log into a schema.
    People logs into the instance and has the ability of doing what specific privs allows them

    Just be sure the export-only account you create for them do not have any ins/del/upd/alt privs on the target schema.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    My understanding is that a user must have either EXP_FULL_DATABASE or log in as the owner of the schema in question, so it isn't enough to just give them an account and SELECT on certain tables. I wanted to give the user access to export all tables in a certain schema without granting them the ability to export the whole database.

  4. #4
    One thought I had would be to write a shell script invoked via sudo that takes as input a set of table names, validates them against some ruleset, then performs the datapump job on their behalf, or maybe an equivalent definer-rights stored procedure.

  5. #5
    Join Date
    Nov 2005
    Posts
    32
    One possible option for you would be to have a datapump admin user who will be given the exp_full_database role. Have this user own a package (..or procedure or whatever) that accepts schema names and performs the data export for you from within the package or procedure with calls to the dbms_datapump API. Grant execute privilege on this package to the ids that your developers would be using and that's all they would need (...along with create session ofcourse).

    Maybe that's what you were referring to below when you said :

    "maybe an equivalent definer-rights stored procedure"
    Good luck......

    http://www.dbaxchange.com

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, another idea
    Data Pumn has PL/SQL Interface (see dbms_datapump). so u can wrire a stored procedure, which does the export and is owned by the schema object owner, but grant execute on this procedure to the developers.
    If u want to have an idea how such procedure will look like, just make an expdp using the database control and at the last step click "Show script" or whatever it's called button

    Good Luck
    Boris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width