How access to another table in other user?


i have two user in the database (user1 and user2)

The user system can access to table of user1 and user2.

user1 can not see table of user2 and viceversa.

Is there way to permit access the table of user1 to user2

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>Is there way to permit access the table of user1 to user2

As user2:
grant select on table1 to user1;

As user1:
grant select on table2 to user2;

You might even create synonyms so the users don't have to add the schema:
as user 1:
select * from user2.table2;

With a synonym created as user2 user1 can then do:
select * from table2;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***Alex140181***]Software DeveloperCommented:
Just to mention, there is another way ;-)

As user1
alter session set current_schema = user2;

Open in new window

As user2
alter session set current_schema = user1;

Open in new window

slightwv (䄆 Netminder) Commented:
>>Just to mention, there is another way ;-)

Still need the grants...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Alex [***Alex140181***]Software DeveloperCommented:
Still need the grants...
For sure, but you'd get rid of the (public) synonyms...

Moreover, this becomes quite handy when using EBR ;-)
Try using public synonyms within a EBR enabled environment :-))
slightwv (䄆 Netminder) Commented:
I never advocate PUBLIC anything in Oracle.  I wouldn't miss it if Oracle eventually dropped that role completely.
Alex [***Alex140181***]Software DeveloperCommented:
I wouldn't miss it if Oracle eventually dropped that role completely.
Me neither ;-) Good one :-))
Mark GeerlingsDatabase AdministratorCommented:
Public synonyms aren't the only ones available.  You may want to use private ones.  For example, if user1 has a table named "products" and user2 has a table named "customers", and you want the opposite schema to use them easily, as user1:
create synonym customers for user2.customers;

And as user2:
create synonym products for user1.products;

That will allow you to refer to them from the opposite schema without needing to use the full [schema].[table_name] syntax you would need without synonyms.

But, synonyms do not replace the need for grants.  If you have just a synonym in user1's schema for a table in user2's schema, but user2 has not granted select (and/or update and/or delete, depending on what you are trying to do) the synonym will not help you.
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.