Oracle 19c Permissions & Role Management Support
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
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