How to Resolve ORA-39109: Unprivileged users may not operate upon other users' schemas

ORA-39109

Saw error ORA-39109 when we tried to export someone's data from the database.

C:\Users\Administrator>expdp ERPAPP@APPDB1 schemas=FINAPP dumpfile=FINAPP-export.dmp logfile=FINAPP-export.log
...
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas

It looks like that we lack some privilege or role to export or import data. Let's check what roles we have.

SQL> column granted_role format a40;
SQL> select granted_role, default_role from dba_role_privs where grantee = 'ERPAPP' order by 1;

GRANTED_ROLE                             DEF
---------------------------------------- ---
CONNECT                                  YES
DATAPUMP_EXP_FULL_DATABASE               NO
EXECUTE_CATALOG_ROLE                     NO
RESOURCE                                 NO
SELECT_CATALOG_ROLE                      NO

As you can see, we do have DATAPUMP_EXP_FULL_DATABASE which enables us to perform data pump, but a NO means that it's not the default role for the user.

ORA-39109 mainly complains about exporting other schema's data. To solve ORA-39109 and export other's schema, you not only need DATAPUMP_EXP_FULL_DATABASE, but also it should be your default role.

That is to say, 2 of the following conditions must be satisfied.

• DATAPUMP_EXP_FULL_DATABASE

It needs to be granted to the user. Obviously, you need it to be able to export data.

• Default Role

It needs to be the default role because it behaves differently if the role is not a default one. Beside, default roles of an user can be multiple.

Normally, you won't see this error, because the default role for newly created users is ALL. That is to say, each granted role automatically becomes the default one, you don't need to deal with it.

Solution

So the solution is simple, if you lack DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role, please directly grant it to the user.

Grant DATAPUMP_EXP_FULL_DATABASE to User

SQL> show user
USER is "SYS"
SQL> grant DATAPUMP_EXP_FULL_DATABASE to ERPAPP;

Grant succeeded.

For importing data, you can do this:

SQL> grant DATAPUMP_IMP_FULL_DATABASE to ERPAPP;

Grant succeeded.

Please note that, for those who have already have DBA role, you don't need this role. DBA role is good enough.

ALTER USER DEFAULT ROLE

Next, we make ALL to be the default role, just for convenience.

SQL> ALTER USER ERPAPP DEFAULT ROLE ALL;

User altered.

Now, you are ready to migrate data.

If you like to manage default roles individually instead of using ALL, let's see what you should do.

The following SQL can be used to compose the statement you need.

SQL> select 'ALTER USER ' || grantee || ' DEFAULT ROLE ' || listagg(granted_role, ',') || ',DATAPUMP_EXP_FULL_DATABASE;' stmt from dba_role_privs where grantee = 'ERPAPP' and default_role = 'YES' group by grantee;

STMT
--------------------------------------------------------------------------------
ALTER USER CISADM DEFAULT ROLE CONNECT,DATAPUMP_EXP_FULL_DATABASE;

In the above statement, we added DATAPUMP_EXP_FULL_DATABASE to current list of default roles.

Then execute the composed statement.

SQL> ALTER USER CISADM DEFAULT ROLE CONNECT,DATAPUMP_EXP_FULL_DATABASE;

User altered.

Then check what default roles you have.