4

I have an Oracle database that has one user that has access to everything. This was fine when we only had one user working on the database. But now that we have more users we want to tighten up security and have different users.

I created each user, gave them DBA access, and they are part of the same tablespace. When the user logs in, there are no tables visible to the users. I've tried to GRANT SELECT to a test table and still nothing. I tired to set up a role for the user and still nothing. Am I missing something or if the user is not the owner of the table other users cannot see the tables or run queries?

Improve this question
7
  • What does "no tables visible to the users" mean? Are you using some sort of front-end GUI? Are you querying all_tables? Or user_tables? Are you running a query against the table? If so, are you using the fully qualified table name?
    – Justin Cave
    Commented Jul 31, 2014 at 20:42
  • I need my users to be able to see all tables from another owner. Run queries from those tables add well.
    – Brandon Wilson
    Commented Jul 31, 2014 at 20:46
  • Post exactly what statements you are executing and what errors, if any, you are getting. If you are actually granting a user select access on a table and that user is using the fully qualified table name to query the table, that would be successful. If your query isn't successful, we'd need to see what you're doing before we can guess where you're going wrong.
    – Justin Cave
    Commented Jul 31, 2014 at 20:49
  • 2
    Check here (1st table element) "DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user". So what you want to do is definitely possible (though why you say you want to "tighten up security" and also give everyone DBA access is, ahem..., puzzling). I feel this is either a tool issue or some sort of scope problem as @JustinCave has mentioned.
    – Vérace
    Commented Jul 31, 2014 at 20:52
  • DBA's should be able to view and run queries without any additional work?
    – Brandon Wilson
    Commented Jul 31, 2014 at 20:58

1 Answer 1

Reset to default
5

It's hard to guess what's going on on your system without code snipets to evaluate, but it seems you're not using the fully qualified table name [schema name].[table name]. If you don't want to use the fully qualified table name, you may create PUBLIC SYNONYMS. Ex from Oracle documentation:

CREATE PUBLIC SYNONYM offices 
   FOR hr.offices;

Having said that, if you are granting DBA privileges to all users, you're not tightening up security at all! If you only want them to query some tables, grant them CONNECT and SELECT privileges. If you're felling really lazy, grant them SELECT ANY TABLE privilege, but don't grant them DBA! DBA privileges give users a lot more power than to just query tables. They'll be able to change system parameters, drop or alter any objects etc etc.

Improve this answer
7
  • Yes it was the fully qualified table name, I was able to create a SYNONYM and it is working just fine. One more thing, I am using SQL Developer and now I can SELECT but viewing those tables in the list is not available. Is there some configuration I need to do in order to see the tables?
    – Brandon Wilson
    Commented Jul 31, 2014 at 22:26
  • Something is not altogether right. You shouldn't have to create synonyms.
    – Vérace
    Commented Jul 31, 2014 at 22:51
  • I probably didn't need to create one, it was working fine using the schema.table. I created one for simplicity. I just need to get the tables to view in SQL Developer now.
    – Brandon Wilson
    Commented Jul 31, 2014 at 23:34
  • After toying around with SQL Developer I come to fine out that the tables you do not own will not show up, you have to go to the specific schema and view the tables that way. When selecting data from the tables I will have to use the fully qualified name or create a synonym.
    – Brandon Wilson
    Commented Aug 1, 2014 at 2:07
  • You are right. On SQL Developer (or any other GUI Oriented app, like Toad or PLSQL Developer), you start browsing your own schema's objects. If you want to browse other schemas objects, then you'll have to select the appropriate schema for that. If you are querying the data_dictionary, it's the difference between user_tables (just your own tables) and all_tables (all the tables you have privileges on).
    – Daniel Stolf
    Commented Aug 1, 2014 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.