New User can't see Tables in SQL Developer 4

[dbrs4me]

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

 

[SantaMufasa]

DBRS,

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

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:[ul][li]You are connected as the user DM[/li][li]DM has granted SELECT privileges to the user to which you have connected[/li][li]DM has granted SELECT privileges to PUBLIC[/li][li]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.[/li][/ul]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.

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

 

[dbrs4me]

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]

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.)

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

 

[dbrs4me]

I need guidance on how to effect those GRANTs, please.

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SantaMufasa]

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:

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

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

 

[dbrs4me]

Frederico said:

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]

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.