Printable View
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?
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.
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.
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.
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 :
Good luck......Quote:
"maybe an equivalent definer-rights stored procedure"
http://www.dbaxchange.com
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