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 some privilege is required by the user. Does it mean DATAPUMP_EXP_FULL_DATABASE, the role which is used to export by data pump? 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, 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 be able to export other's schema, you not only need DATAPUMP_EXP_FULL_DATABASE, but also it should be your default role.

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 role, please directly grant it to the user.

SQL> show user
USER is "SYS"
SQL> grant DATAPUMP_EXP_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.

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

SQL> ALTER USER ERPAPP DEFAULT ROLE ALL;

User altered.

If you like to manage default roles individually by yourself, you can use the following SQL to compose the adding 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.

Now, you can export the schema again.