Cannot Create Views Get ORA-01720: Grant Option Does Not Exist And AD_ZD Gives ORA-01926 (Doc ID 2491356.1)

Last updated on MAY 29, 2024

Applies to:

Goal

We have upgraded from 11.5.10 to R12.2.6
Certain requests that are part of the planning data collections request set are completing with error and as a result the complete request set is ending in Error
The following concurrent programs have completed in Error and error message provided alongside

Create Collections Other Views "Creating View MRP_AP_INTER_ORG_TRXS_V ********
  FROM rcv_shipment_lines rsl,
  *
ERROR at line 16:
ORA-01720: grant option does not exist for 'PO.RCV_TRANSACTIONS#'"

Create Collections Supply Views "View MRP_AD_REPT_ITEM_SUPPLIES_V ********
  FROM MRP_AD_REPT_ITEM_SUPPLIES
  *
ERROR at line 6:
ORA-01720: grant option does not exist for 'MRP.MRP_AD_REPT_ITEM_SUPPLIES#' "

Create Collections Demand Views "View MRP_AD_REPT_ITEM_DEMANDS_V ********
  FROM MRP_AD_REPT_ITEM_DEMANDS
  *
ERROR at line 9:
ORA-01720: grant option does not exist for 'MRP.MRP_AD_REPT_ITEM_DEMANDS#'"

Create Collections WIP Views "View MRP_AD_WIP_FLOW_DEMANDS_V ********
  FROM MRP_FLOW_DEMANDS wfd
  *
ERROR at line 12:
ORA-01720: grant option does not exist for 'MRP.MRP_AD_FLOW_SCHDS#' "

Create Collections Routing Views "View MRP_AD_ROUTING_OPERATIONS_V ********
from mrp_ad_opr_seqs dos,
  *
ERROR at line 6:
ORA-01720: grant option does not exist for 'MRP.MRP_AD_OPR_SEQS#'
"
Create Collections BOM Views "Creating View MRP_AP_DEPARTMENT_RESOURCES_V ********
  FROM MRP_PLANNING_EXCEPTION_SETS mpes,
  *
ERROR at line 64:
ORA-01720: grant option does not exist for 'BOM.CST_DEPARTMENT_OVERHEADS#'"

Create Collections Item Views "Creating View MRP_AP_DEFAULT_ITEM_CATEGORY_V ********
  FROM MTL_CATEGORIES_KFV mck,
  *
ERROR at line 7:
ORA-01720: grant option does not exist for 'INV.MTL_ITEM_CATEGORIES#' "

Create Collections Setup Views "Creating View MRP_AP_CAL_YEAR_START_DATES_V ********
 FROM BOM_CAL_YEAR_START_DATES x,
  *
ERROR at line 9:
ORA-01720: grant option does not exist for 'INV.MTL_PARAMETERS#' "

=====================
SUPPORT FINDINGS
1. These are standard SQL statements being run via concurrent request
Every customer who uses our product run these setup requests.

When we check and compare using SQL
SELECT * FROM dba_tab_privs
WHERE TABLE_NAME LIKE 'CST_DEPARTMENT_OVERHEADS%'
ORDER BY TABLE_NAME;

For object CST_DEPARTMENT_OVERHEADS#
They have many custom GRANTEE that are not on our internal systems and they have GRANTABLE - NO
AND they have many different custom GRANTEE roles that have been created on the system
e.g. GRANTEE - SELECT_ROLE and APPS_ROLE

Internally - All GRANTEE roles have GRANTABLE - YES

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

2. Then they ran grants and got error -- syntax looks fine when compared to (Doc ID 1987947.1)

SQL> exec AD_ZD.grant_privs('SELECT','CST_DEPARTMENT_OVERHEADS','SELECT_ROLE','WITH GRANT OPTION');
BEGIN AD_ZD.grant_privs('SELECT','CST_DEPARTMENT_OVERHEADS','SELECT_ROLE','WITH GRANT OPTION'); END;

*
ERROR at line 1:
ORA-01926: cannot GRANT to a role WITH GRANT OPTION
ORA-06512: at "APPS.AD_ZD", line 1193
ORA-06512: at line 1
----------------------------------------------

3. To test this issue you can run the following:
from $MSC_TOP/sql - as APPS user run
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='TRACE_BOM'
max_dump_file_size=unlimited
EVENTS='10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

SQL>@MSCVWBOM.sql

OR any of the following View definition files
MSCVWAHL.sql
MSCVWDEM.sql
MSCVWEAM.sql
MSCVWITM.sql
MSCVWOTH.sql
MSCVWRPO.sql
MSCVWRTG.sql
MSCVWSTP.sql
MSCVWSUP.sql
MSCVWWIP.sql
MSCWIPSN.sql
MSCWSHSN.sql
MSCWSMSN.sql
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.