view procedures on other schema

[Roshan]

Hi Erman,

I have created a user GAIA_RO on a database. After connecting to sqldeveloper/toad, I want to view the procedures/tables (readonly) of another schema(OPS$IMCC_5X). I have granted DBA to GAIA_RO but I cannot view the objects.

As shown below, on clicking on procedures, I  cannot see the procedures of user OPS$IMCC_5X
test.png

Thanks,

Roshan

[ErmanArslansOracleBlog]

The grants that are delivered by the DBA role, differs according to your db version.
The method that is used by tools to display the procedure code, may differ according to your tool and its version, as well.
This is a tool related question, but I will try to answer.
1)Try granting "SELECT ANY CATALOG" to the user.
If not solved ->
2)Try granting "execute grant" on the procedures you want to the user.
If not solved ->
3)Try granting "debug grant" on the procedures you want to the user.
If not solved ->
Send me the Toad version, RDBMS version and the output of the following SQL:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = <YOUR_USER>
UNION
SELECT PRIVILEGE
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <YOUR_USER>
 ORDER BY 1;

[Roshan]

hi,

issue has been solved. On sqldeveloper, there is an option filter schemas and filter packages

test.png

Thanks,
Roshan