Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

New User can't see Tables in SQL DeveloperHelpful Member!(2) 

dbrs4me (MIS) (OP)
25 Aug 11 17:02
I created a user that needs read only access to a database. I created this user with the default tablespace that is needed. I granted the CONNECT role to him as well as CREATE SESSION.

When the user connects via SQL Developer and clicks on the + sign in front of the Tables it comes back empty. The user is able to see the contents of the table with a select statement as long as it is prefixed by the TABLESPACE name, i.e.

SELECT * FROM DM.table

I am sure I am missing something simple, a grant statement more than likely. Can someone help me out please.

Much Thanks
Helpful Member!  SantaMufasa (TechnicalUser)
25 Aug 11 23:36
DBRS,

To clear up what might be a misconception, when you say:

CODE

SELECT * FROM DM.table
...the DM is not a tablespace name, it is an Oracle user (a.k.a. schema or owner) name.

The reason that a (DM) table is visible is because either:
  • You are connected as the user DM
  • DM has granted SELECT privileges to the user to which you have connected
  • DM has granted SELECT privileges to PUBLIC
  • The user to which you are connected has broad privileges to read other schemas' tables. For example, that user has been granted a ROLE such as DBA, or has been granted a system privilege such as SELECT ANY TABLE.
I apologize that I am not more familiar with the behaviors of SQL*Developer, but I'm thinking that the "[+]" you are clicking on probably has the functionality of showing tables that the connected user owns. In this case, the connected user owns no tables (itself), but that user certainly has access to data dictionary views, and apparently has at least SELECT access to one or more tables that user DM owns.

I hope this has helped at least a little bit.

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

dbrs4me (MIS) (OP)
26 Aug 11 11:20
Thanks Santa, you are correct, it is the schema name before the table. Also, the plus sign in front of the Tables that I mentioned in SQL Dev is to open the tables in a folder like view. When you are the schema owner you can see all the tables, when you aren't, you can't.

Any ideas on how to make the ME user able to see the tables when connected to the database? There are other schemas available, I just want the user to only see this one schema.

Thanks
SantaMufasa (TechnicalUser)
26 Aug 11 11:56

Quote (DBRS):

Any ideas on how to make the ME user able to see the tables when connected to the database?
If a user should have privileges to objects that another schema owns, then someone connected as the owning user should GRANT the appropriate privileges to the appropriate non-owning user(s). (If you need guidance on how to effect those GRANTs, please post.)

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

dbrs4me (MIS) (OP)
26 Aug 11 11:59
I need guidance on how to effect those GRANTs, please.
Helpful Member!  fredericofonseca (IS/IT--Management)
26 Aug 11 13:57
in SQL Developer the interface has 2 groups - one its the tables of the user connected. the other one is other schemas/users, and on that one, once the user selects the correct schema, will show up the tables to which the user has been granted access.

If the user requires to see the tables under his own schema he will need to create private synonyms pointing to those tables.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

SantaMufasa (TechnicalUser)
26 Aug 11 15:01

Quote (dbrs):

I need guidance on how to effect those GRANTs, please.
While connected as user DM, these statements could occur to give the named privilege(s) for my_table to user, ME:

CODE

GRANT SELECT ON MY_TABLE TO ME;
--    ^--- Grants read-only privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON MY_TABLE TO ME;
--    ^--- Grants all DML privileges

GRANT ALL ON MY_TABLE TO ME;
--    ^--- Grants all DML and DDL privileges

REVOKE <privilege> ON MY_TABLE FROM ME;
--    ^--- Removes GRANTed privilege

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

dbrs4me (MIS) (OP)
26 Aug 11 15:27
Frederico said:

Quote:

in SQL Developer the interface has 2 groups - .... the other one is other schemas/users, and on that one, once the user selects the correct schema, will show up the tables to which the user has been granted access.

How does the user select the correct schema within SQL Developer?

 
dbrs4me (MIS) (OP)
30 Aug 11 10:17
I found the solution that I was looking for on this. If you have select access to another user's tables in their schema and you want to be able to see them in the Tables drop-down in SQL Developer, connect to the SID in SQL Developer. In the pane on the left side scroll down to Other Users and expand it. Scroll down to the user whose tables you want to see and expand the user, then expand the Tables. You will see all the tables to which you have been granted rights.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close