Export by owner without DBA privilege !!!

[Chris Fung]

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

[Paula J Frazer-Campbell]

Chris

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

Paula

[Graham Cameron_1]

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

[Christian Gebhardt]

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]

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

[T G Manikandan]

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]

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]

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

[R. Allan Hicks]

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.