Granting select access in r12.2.5

[satish]

Dear Erman,

Our apps version R12.2.5
DB version 12.1.0.2

While granting read access to a table,we are facing the below error.Can you pls suggest.

SQL> exec AD_ZD.grant_privs('SELECT','XXABC_AP_MBOOK_AGR_ITEMS_TAB', 'XXMOT');
BEGIN AD_ZD.grant_privs('SELECT','XXABC_AP_MBOOK_AGR_ITEMS_TAB', 'XXMOT'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "APPS.AD_ZD", line 1134
ORA-06512: at line 1

XXMOT is the user to which we are granting access
XXABC_AP_MBOOK_AGR_ITEMS_TAB is the custom table


SQL> select owner,object_name,object_type from dba_objects where object_name='XXABC_AP_MBOOK_AGR_ITEMS_TAB';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
XXABC
XXABC_AP_MBOOK_AGR_ITEMS_TAB
TABLE

APPS
XXABC_AP_MBOOK_AGR_ITEMS_TAB
SYNONYM


We are able to use the same api for standard tables.This instance is fresh installed in 2017 and this is not any upgraded instance.We have registered the custom schema correctly.

Thanks,
Satish

[ErmanArslansOracleBlog]

You try to grant select on XXABC's table to XXMOT user.
So you try to give access rights for other schema's objects..
I review the package code.. It is failing while executing the grant statement itself.

This seems to be caused a lack of grant.. actually the "grant option"

So, as apps schema didn't have select grant "with grant option", the ad_zd.grant_privs fails with ORA-01031.
So you can continue with that..

However; this is interesting. I will check this subject in more details..

[satish]

Dear Erman,

Tried in test instance,created many invalids in system.

grant select on XXABC.XXABC_AP_MBOOK_AGR_ITEMS_TAB to XXMOT.

Not sure if it is due to synonym under apps schema.Please suggest

SQL> select owner,object_name,object_type from dba_objects where object_name='XXABC_AP_MBOOK_AGR_ITEMS_TAB';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
XXABC
XXABC_AP_MBOOK_AGR_ITEMS_TAB
TABLE

APPS
XXABC_AP_MBOOK_AGR_ITEMS_TAB
SYNONYM


Thanks

[ErmanArslansOracleBlog]

This is a direct grant. So you don't use ad_zd.grant_privs here..
This is expected, read -> Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1).. Read the workaround and suggested patch levels.