by Jeff Stonacek, Principal Architect

In a recent migration activity using Oracle Data Pump, we ran into problems with table permissions on the target database. After working with the software vendor and investigating the issues, it was determined that the source database had table grants directly to user PUBLIC that were missing. After issuing the grants on the target database, the problem was resolved.

This behavior was intriguing as the method for moving the database involved a full Data Pump export and a full Data Pump import. My assumption was that a full Data Pump export/import would issue all grants to the target database, including those to PUBLIC. To my surprise however, direct table grants to PUBLIC do not get imported.

My goal in writing this blog is to provide an example of the issue we experienced along with its solution.

Example

Part of the problem with this scenario is that it is never a good idea to issue grants directly to user PUBLIC (for obvious reasons). As the name implies, once a privilege is granted to PUBLIC, all users immediately inherit that privilege.

In this example, our source database has a user named USER1 with limited privileges.

SQL> show user

USER is "USER1"

SQL> select * from v$session;

select * from v$session

             *

ERROR at line 1:

ORA-00942: table or view does not exist

Once we grant select on v_$session to PUBLIC, then all users have the right to query that view.

SQL> grant select on v_$session to public;

Grant succeeded.

SQL> select owner, table_name, grantee

2 from dba_tab_privs

3 where grantee = 'PUBLIC'

4 and table_name like 'V_$SE%';


OWNER              TABLE_NAME                     GRANTEE

----------------- ------------------------------ ---------

SYS               V_$SESSION_CONNECT_INFO         PUBLIC

SYS               V_$SESSION_LONGOPS              PUBLIC

SYS               V_$SESSION                      PUBLIC



SQL> show user  

USER is "USER1"

SQL> select sid, serial#, username from v$session;

...

       17     36818 USER1

       26     47180

       27     54231

       28     57581

       237    21883 SYS

       238    56494


33 rows selected.

Our target database contains neither user USER1, nor the select privilege for PUBLIC.

SQL> connect user1/******

ERROR:

ORA-01017: invalid username/password; logon denied

connect / as sysdba

select owner, table_name, grantee

from dba_tab_privs

where grantee = 'PUBLIC'

and table_name like 'V_$SE%';

OWNER           TABLE_NAME                   GRANTEE

--------------- --------------------------- -----------

SYS             V_$SESSION_LONGOPS           PUBLIC

SYS             V_$SESSION_CONNECT_INFO      PUBLIC

We perform a full Data Pump export and import, which completes successfully.

Source:

expdp system/****** directory=expdir full=y dumpfile=orcl.dmp logfile=orcl_exp.log
...
*******************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

/opt/oracle/exp/orcl.dmp

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Feb 2 09:46:31 2017 elapsed 0 00:01:21

Target:

impdp system/****** directory=expdir dumpfile=orcl.dmp logfile=orcl_imp.log full=y

...
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Thu Feb 2 10:08:42 2017 elapsed 0 00:01:06

All errors encountered are “Already Exists…” errors.

Now USER1 exists in the target database, but the select privilege to PUBLIC is missing. To replicate the behavior in the source database we would need to manually issue the grants to PUBLIC as they did not come in with the import.

SQL> show user
USER is "USER1"
SQL> select sid, serial#, username from v$session;
select sid, serial#, username from v$session

                                   *
ERROR at line 1:
ORA-00942: table or view does not exist
connect / as sysdba
select owner, table_name, grantee
from dba_tab_privs
where grantee = 'PUBLIC'
and table_name like 'V_$SE%';

OWNER          TABLE_NAME                 GRANTEE
-------------- -------------------------- ---------
SYS            V_$SESSION_LONGOPS         PUBLIC
SYS            V_$SESSION_CONNECT_INFO    PUBLIC

Conclusion

Most DBAs assume that all grants are applied when doing a full export and import with Data Pump. However, if your application, custom or otherwise, is relying on grants to PUBLIC for normal operation, be aware that these grants are not processed during a full Data Pump export/import. Instead, these grants have to be manually processed after the database migration is complete.