I'm working on a database in SQL Developer, create a user named Nagato2 and with it create a connection called test2, in which I put all my tables.
However, I was asked to create a new user within this connection that I called Nagato3. When creating a connection called test3 with the user Nagato3 and reviewing the section of tables I do not see the tables that appeared in test2 (Image 2). How to make the tables appear here?
User ngato3 does not have permissions on user tables ngato2 . If you want ngato3 to see the ngato2 tables, you should start by giving them permissions, with the grant , for example:
connect [email protected]
grant select on miTabla1 to ngato3;
grant all on miTabla2 to ngato3;
grant select, update, delete on miTabla3 to ngato3;
The user ngato3 , you can see the tables of ngato2 , but you will still have to qualify them to use them, for example:
select * from ngato2.miTabla1;
If ngato3 you want, to avoid having to qualify the tables, you can create synonyms to the ngato2 tables, with the name you want, although it is recommended that they have the same name.
connect [email protected];
create synonym mitabla1 for ngato2.mitabla1;
create synonym OtroNombre for ngato2.mitabla1;
In this way, ngato3 can access the ngato2 tables without qualifying them:
select * from mitabla1;
will bring the ngato2.mitabla2 data.
Finally, ngato2 , the sysdba or any other privileged user, can create a public synonym to the tables (or other objects), so that no one has to rate them:
connect [email protected];
create public synonym mitabla1 for ngato2.mitabla1;
This will make any user able to make the judgment on mitabla1 without needing to rate it.
select * from mitabla1;