We help IT Professionals succeed at work.

In Oracle Sql Developer, accessing UAT objects while logged in through my own id.

gram77
gram77 asked
on
I login into UAT using my own id: tiger

However, i access objects of UAT.

Therefore, i need to access UAT objects using schemaname extention.


select *
from UAT.table;

However, If i try to access objects such as packagas of UAT user while i am logged in using my id tiger, I am not able to view those objects.

In toad, i can login using my own id, and change the schema to UAT and access all UAT objects. How can i do this in Oracle Sql Developer?
Comment
Watch Question

Top Expert 2011

Commented:
- grant your id tiger with SELECT_ANY_CATALOG, CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE role.

- not sure what is your sql developer version, there is also a similar issue posted in the following link:
http://mikesmithers.wordpress.com/2010/08/06/unable-to-see-package-bodies-in-sqldeveloper-2-1-1/

Author

Commented:
sql developer version is :  Version 3.0.03

- grant your id tiger with SELECT_ANY_CATALOG, CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE role.

assuming that i don't have the above privileges, how is toad able to show all objects of UAT schema just be selecting that schema.
Top Expert 2011

Commented:
- in toad there is an option to use DBA views. this option is not available in sql developer.
Top Expert 2011
Commented:
- click on connection> databasename> other users> uat > see if you can view its procedure? if not you need to get granted the privileged as i have mention earlier.

Author

Commented:
I don't see "other users" under connections. I would see it in version 2.x

Also i don't have access to uat.all_tables, uat.dba_tables or uat.user_tables views. It shows error object does not exist.

Here is another good post regarding the issue:
http://andrewfraserdba.com/2011/08/11/oracle-sql-developer-privileges-for-other-users-procedures-and-package-bodies/


It seems i need the following grant:
grant select_catalog_role to <user> ;

How do i check if i have this grant given to me or not?

Secondly, How is toad able to show me uat contents while i am logged in using my own id?

In toad i change the schema to uat, pick object_type= package, and pick the package from the list given. and bingo i have the package in front of me
Top Expert 2011

Commented:
- mine is 3.0.04.34 and i have "other users"  in the treelist. you might want to download the latest version from Oracle website.

- see the following link for check the grant:
http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

- i've mention earlier that in Toad, there is an option to use "DBA views" that is why it allows you to see  objects belong to other user.
Top Expert 2011

Commented:
- i've not using toad for a very long time, however based on the below links, he able to view others object in toad by granted "select any dictionary" and then change default settings under View -> Options -> StartUp to "Check for Access to DBA Views" (as i've mention ealier).

https://forums.oracle.com/forums/thread.jspa?threadID=324756

Author

Commented:
correction, i am able to see "other users", so that explains why i am able to see other schema code in toad.

but however, why am i not able to see the views uat.dba_source, uat.user_source and uat.all_source
Top Expert 2011

Commented:
- try grant tiger to select on that view:

GRANT SELECT ON view_name TO tiger

Author

Commented:
I will need to login using superuser a/c to grant privileges to me. I don't have that privilege
Top Expert 2011

Commented:
- you need to request to your dba to grant the tiger id to select the view. explain the need for your id to query that view.

Author

Commented:
infact, i don't have access to any oracle views when i am logged into using my id and accessing uat.
Top Expert 2011

Commented:
- you have not granted with the privilege to select/execute other users view. this is the security in Oracle.