If you use DataPump as the SYSTEM user, it won’t include grants on SYS objects and won’t include system privileges. I recommend doing nonproduction copies with RMAN instead.

​@durette We have a similar issue in our non-production environment during delivery deployment where we are getting errors like below:

ORA-01720: grant option does not exist for 'SYS.DBA_TABLES'

ORA-01720: grant option does not exist for 'SYS.DBA_TAB_COLUMNS'

ORA-01720: grant option does not exist for 'SYS.DUAL'

ORA-01720: grant option does not exist for 'SYS.DBA_SCHEDULER_JOBS'

ORA-01720: grant option does not exist for 'SYS.GV_$SESSION'

ORA-01720: grant option does not exist for 'SYS.DBA_TAB_COMMENTS'

ORA-01720: grant option does not exist for 'SYS.DBA_MVIEWS'

ORA-01720: grant option does not exist for 'SYS.ALL_ARGUMENTS'

ORA-01720: grant option does not exist for 'SYS.USER_COL_COMMENTS'

Is there a solution that we can apply to the database to resolve this please?

Thanks

Shaun

sqlplus sys/password@database_service as sysdba
GRANT SELECT ON sys.dba_tables         TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.dba_tab_columns    TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.dual               TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.dba_scheduler_jobs TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.gv_$session        TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.dba_tab_comments   TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.dba_mviews         TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.all_arguments      TO ifsapp WITH GRANT OPTION;
GRANT SELECT ON sys.user_col_comments  TO ifsapp WITH GRANT OPTION;

Here's a quick explanation why WITH GRANT OPTION is needed:

https://dba.stackexchange.com/questions/130288/cant-create-a-view-for-other-users-table-despite-having-select-privilege/146198#146198

IFSAPP will have the ability to expose these views to other users, thus indirectly granting data access on SYS to other users. IFSAPP therefore needs WITH GRANT OPTION on the grant.

These look like the grants that get executed in the prepare step, so if you don’t like hacky solutions, I’d start there instead.

​@durette I’ve had to extract each of the Oracle views affected in the IFS error log, and create a drop script for each one of them and then re-run the delivery! 

As you can imagine this is not ideal and very time consuming. 

We didn’t have this issue with Apps 8 and 10, and only since we went to IFS Cloud has this become an issue.

If you fix the grants and recompile the objects, you shouldn’t need to drop them.

​@durette This is the guidance given by IFS Support and our DBA Vendor.  

There is approx 350 DB views that were listed in the error log.

I’m not sure how you would fix any objects missing these grants instead of dropping them.

Are you able to provide guidance of fixing the grants on mass? 

When you type SQL text to define an object like a view or PL/SQL text to define an object like a package, Oracle has to understand that text and convert it to the binary code that it will execute. This is a compilation step just like any compiled language. If you’re used to developing in languages like C, though, it’s a little different. In traditional compiled languages, if you make a mistake, you have to fix your mistake and then tell the computer to make another attempt to compile it, but Oracle is a little smarter. Oracle tracks each object’s dependencies, and if those dependencies change, the dependent object will also get recompiled.

When you create a view, there are two options relevant here that are related to handling missing dependencies.

The “FORCE” option when creating a view will create the view even if it would be created as invalid, so if you create the view as invalid and then fix the reason why it’s invalid, it will be automatically recompiled and fixed once those dependencies are satisfied. (You might need to attempt to use the object to trigger a recompilation.) Most IFS views aren’t created with this option, but this is the way packages get created regardless.

The “OR REPLACE” option will replace the view or package if it already exists, and this is ordinarily used throughout IFS. This means if you fix your dependencies before attempting the upgrade, the views should get replaced correctly. If R&D is asking you to drop a view that has “OR REPLACE” in it, I’d question why, and if it doesn’t have “OR REPLACE” in it, I’d ask them to fix that as a bug rather than drop it as a workaround.

Note that “OR REPLACE” does not imply “FORCE”, so if the new definition is invalid, the old definition will be retained. This means if your old view worked and the new one doesn’t, dropping the view accomplishes little more than exposing the correct errors for further troubleshooting.

When you change an Oracle object, the objects that depend on it will be marked invalid. Oracle doesn’t reattempt to recompile an invalid object until it’s used, so if you query the database for invalid objects, you’re going to get a mixture of both objects that can’t be recompiled and objects that are merely stale due to recompiled dependencies. This means you don’t know where you really stand until you force a recompilation.

If you use PL/SQL Developer by Allround Automations, you can force a recompilation of invalid objects using the menu: Tools → Compile Invalid Objects.

If you’d prefer to use the SYS account, there’s a script available on the database home: $ORACLE_HOME/rdbms/admin/utlrp.sql

You can also call that from SQL*Plus without the wrapper: EXEC UTL_RECOMP.recomp_parallel(NULL, 'IFSAPP');