Oracle 19c Permissions & Role Management Support

Job ID: 38934699

Budget: $10 – $30 USD

Hi I'm using Oracle 19c database.

I created a new user/schema called MM_UAT and given the required grants. I want the MM_UAT user only to see the DB_OBJECTS of other schemas or specific schema. My required DB_OBJECTS like tables, views, functions and procedures seems OK. but there's a problem. If I right click and open a package like PRODN.PKG_REPORTS and i click "view spec & body", i cannot see the body i see only the specs. I have given extra permissions as well to check if they work:

GRANT EXECUTE ANY PROCEDURE TO MM_UAT;

GRANT DEBUG CONNECT SESSION TO MM_UAT;

GRANT SELECT ANY DICTIONARY TO MM_UAT;
GRANT SELECT_CATALOG_ROLE TO MM_UAT;
GRANT SELECT ON DBA_SOURCE TO MM_UAT;
GRANT EXECUTE ON PRODN.PKG_REPORTS TO MM_UAT;

CREATE OR REPLACE VIEW SYS.PRODN_PKG_BODY_VIEW AS
SELECT *
FROM DBA_SOURCE
WHERE OWNER = 'PRODN' AND TYPE = 'PACKAGE BODY';

GRANT SELECT ON SYS.PRODN_PKG_BODY_VIEW TO MM_UAT;

But still no luck.

I can see the spec and body perfectly only if I give:
GRANT CREATE ANY PROCEDURE TO MM_UAT;

But I cannot give this grant, because with GRANT CREATE ANY PROCEDURE TO MM_UAT; it will allow the user MM_UAT to edit and save anything in other schemas.

Can you please support