Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Export by owner without DBA privilege !!!

Chris Fung
Frequent Advisor

Export by owner without DBA privilege !!!

Hi All,

As I read through some of the metalink article, it seems it is not possible to export DB objects from other schema without the DBA privilege.

When I use DBA account to export the DB objects from individual accounts, it seems that the individual accounts cannot restore their own objects !!!

I am currently encounter this problem as user request me to schedule backup the user schema objects, however, they would like to restore / import the export dmp files created by a DBA account. Is there any workaround for this issue !!

I am thinking setting up cronjob in each individual account to start their own export job, but password file has to be maintained in order for the export to start !! Also it is not a centalized solution, that mean will impose additional administrative effort.

Appreciated for your comments and inputs.

Cheers,

Chris
8 REPLIES
Paula J Frazer-Campbell
Honored Contributor

Re: Export by owner without DBA privilege !!!

Chris

Check ownership etc after you export, I think your problem is there.

Paula
If you can spell SysAdmin then you is one - anon
Graham Cameron_1
Honored Contributor

Re: Export by owner without DBA privilege !!!

Chris.
1.
Contrary to what you have been told, exp needs no special privileges other than the standard SELECT.
If a user can select from tables in another schema, then he can export them.
2.
It is true that only a DBA can import a file which was exported by a DBA. There is no workaround except to import into an intermediate DB (as DBA) and then export (as not DBA).
3.
Exp is not a great tool for backup. You are much better off with a proper backup strategy using Oracle backup facilities, RMAN, etc.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Christian Gebhardt
Honored Contributor

Re: Export by owner without DBA privilege !!!

Hi

there are two roles in the database dealing with export/import.
- imp_full_database
- exp_full_database

the owner of these roles can import/export all objects in the database (including objects from other users), even if a dba has exported the objects.
The user sys/system can only export every object because they are owner of these roles, too.

!!!!! It is not recommended to grant these roles to every user !!!!!!

Chris
Indira Aramandla
Honored Contributor

Re: Export by owner without DBA privilege !!!

Hi Chris,

If the export is generated by a user with the EXP_FULL_DATABASE privilege (e.g. DBA), then it can only be imported by a user with the IMP_FULL_DATABASE privilege. This is a security feature.

One way is to Grant role IMP_FULL_DATABASE to the user who is trying to import the data, but it depends if you want to grant this role to the user.

I would suggest if export is done by the DBA account then import the data as DBA accounts provided the request to restore / import data from backup export is not too frequent.

IA
Never give up, Keep Trying
T G Manikandan
Honored Contributor

Re: Export by owner without DBA privilege !!!

The user can export his own schema.He cannot export other users tables unless there are permissions.

If the dba has done the export only a dba can do a import.

T G Manikandan
Honored Contributor

Re: Export by owner without DBA privilege !!!

you can write a script for export and schedule it on cron.

you can create a user in Oracle which has only exp/imp privileges.

Then you can use this user to take up the exp and imp.

If the user/password is hard coded in the script make sure that only the OS oracle user has read permissions on the file and others have no permissions
Yogeeraj_1
Honored Contributor

Re: Export by owner without DBA privilege !!!

hi,

Based on the restrictions, unfortunately you have not much options left.

So, you'll either

a) import for them
b) do the export in a format that they can use.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
R. Allan Hicks
Trusted Contributor

Re: Export by owner without DBA privilege !!!

IMP/EXP are useful tools for migration, but as others have pointed out, RMAN is the way to go for backup and recovery.

I recommend that you read Tom Kyte's Expert One on One Oracle Chapter 8 published by WROX ISBN 1-861004-82-6 for a through understanding of the ins and outs (no pun intended) of imp and exp.
"Only he who attempts the absurd is capable of achieving the impossible