Wednesday, October 15, 2014

Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';


Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:
GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;

No comments:

Post a Comment