Solved

linking different user/schema

Posted on 2010-11-30
8
320 Views
Last Modified: 2012-05-10
Hi,
I want to know is a way to linking different user/schema on same database other than dblink?
e.g. abc/abc007@samedb , def/def007@samedb
Will it must using dblink?
or any other way to do linking?
Thank  you!
Francis SZE
0
Comment
Question by:fsze88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Accepted Solution

by:
enachemc earned 200 total points
ID: 34237562
you can use schemas directly, no need to use DB Links if you're on the same DB

you just say:

select * from a,schema.b b
where a.id = b.f_id
0
 
LVL 1

Assisted Solution

by:sunny25
sunny25 earned 200 total points
ID: 34237844
First you need to grant privileges on the objects of abc/abc007 to def/def007 and then can access those objects in def/def007 schema just the way enachemc explained
0
 
LVL 15

Author Comment

by:fsze88
ID: 34237853
what's that grant statement?
Thank YOU!

Francis SZE
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Assisted Solution

by:sunny25
sunny25 earned 200 total points
ID: 34237868
grant privileges like select,insert or excute etc. to other schema
eg:
abc has a table A and you want to access this table in user schema def,
then run below statement in abc schema
grant select on A to def;
and in user schema def you can query: select * from abc.A;
0
 
LVL 12

Assisted Solution

by:enachemc
enachemc earned 200 total points
ID: 34237869
grant select any table to &user;
grant delete any table to &user;
grant update any table to &user;
grant insert any table to &user;
0
 
LVL 15

Author Comment

by:fsze88
ID: 34238417
wait,,
select * from a,schema.b b
where a.id = b.f_id

schema.b is meaning username.b?

select * from a,def.b b
where a.id = b.f_id
0
 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 100 total points
ID: 34238893
Hi,

You need to grant privilges to other schema (user) , so that the other user can
do the
Hope the below examples clears your doubt.

username :  abc
his object  : emp

username : def
his object : employ.

as abc user execute below.
> grant select,insert, update,delete on emp to def;

as def user execute below.
> grant select, insert, update,delete on employ to abc.

Once the privileges are granted you can do the above said dml operation on the tables of the other schema..

Thanks..
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question