2

I've just imported an ORACLE database into an instance of Oracle 10 XE running on a Linux system.

The database has tables split across a number of tablespaces, and the sys user is able to access all the tables without problem.

I've created a new user (fred) and I'm unable to access any of the tables whilst logged on as this user. I've set the user's default tablespace in an attempt to be able to access the tables whilst logged on as fred.

alter user fred default tablespace t1;

When I then connect as fred and attempt to look at a table in tablespace t1, I receive the following error:

describe table1;
ERROR:
ORA-04043: object table1 does not exist

How can I configure the user fred, so that he can easily access all the tables in the various tablespaces (t1, t2, t3... t6), like the user sys currently can?

I've never really dabbled with oracle in the past, but have used MS SQL quite extensively.

Bryan
  • 3,224
  • 9
  • 41
  • 58

2 Answers2

4

In Oracle the concept of schemas and users is different than that of other DBMS. In order to get table access for user fred, you will need to create synonyms of those table and sys user needs to issue grants (eg select, update e.t.c) on those tables.

Zohaib
  • 7,026
  • 3
  • 26
  • 35
  • Okay, I've not familiar with synonyms, but will do some reading on the subject. Is it possible that they already exist though, given that sys can already access the tables from any tablespace without me having to specifically refer to the tablespace names? – Bryan Nov 22 '11 at 09:59
  • 1
    I've used [this link](https://forums.oracle.com/forums/thread.jspa?threadID=1016067) to create synonyms for all the tables. Working fine now. Many thanks. – Bryan Nov 22 '11 at 10:09
  • @Bryan I am glad it solved your problem, I was about to post some links for creating synonyms. Oracle can be tricky at times. Happy Oracling.. – Zohaib Nov 22 '11 at 10:11
0

Some useful links.

Managing User Privileges and Roles

GRANT

Oracle/PLSQL: Grant/Revoke Privileges

Yegoshin Maxim
  • 872
  • 2
  • 21
  • 54