0

[![enter image description here]2]2I made my user with my tables etc. Then I created a new user with privileges but the tables are not there in the new user... how can I link the two users so both users can make use of the tables that they have access to?

I used 'Grant all on tablex to user2;' however, when I connect to user2, I am unable to view the table tales (this table x is on the User1 account). My question is how do I fix this problem?

Annon
  • 123
  • 1
  • 9
  • Hint: `GRANT` to give permissions for users to access objects. – Gordon Linoff May 09 '20 at 11:28
  • Yes, I tried that, I used 'Grant all on tablex to user2;' however, when I connect to user2, I am unable to view the table tales (this table x is on the User1 account). My question is how do I fix this problem? – Annon May 09 '20 at 12:35

1 Answers1

1

We aren't looking over your shoulder, but I'll bet a cookie that you are not qualifying the table name.

If you query like this:

select * from mytable;

oracle will only look in your own name space -- your own tables - for a table named MYTABLE.

If you want to see someone else's tables, you have to qualify with the owner name:

select * from otheruser.mytable;

If you don't want to qualify the name, you need to create a synonym in your own schema, pointing to the table in the other schema.

As USER2:

create synonym mytable on otheruser.mytable;
EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • I altered the image in the question. But does your explanation still stand? Also, when i run the 'create synonym mytable on otheruser.mytable;' i get 'ORA-00905: missing keyword' – Annon May 09 '20 at 14:10
  • Your sketch changes nothing. So you got an error on createing the synonym. I was just writing it off the top of my head. Since it obviously has a syntax error, why don't you check the Oracle SQL Reference section on CREATE SYNONYM? https://docs.oracle.com/database/121/SQLRF/statements_7001.htm#SQLRF01401 – EdStevens May 09 '20 at 14:21
  • It is not `on`, but `for`, i.e. `create synonym mytable FOR otheruser.mytable` – Littlefoot May 09 '20 at 18:55