RE: DB Links management

" If the DBLink is created without a identified by, the user must provide a=20
Password each time it is used."

Not true IF the user id exists on both servers (with of course the same password). And provided the user on remote DB has all the necessary permissions to objects in the remote DB .

Example

DBLOCAL
Userid user1/user1

DBREMOTE
Userid user1/user1

Create public database link DBREMOTE using 'DBREMOTE';

As user1

Select * from dual_at_DBREMOTE;

D
-
X

Tables 'test' exists on DBREMOTE owned by user1 =20  user1 on DBLOCAL executes

Select count (*) from test_at_DBREMOTE;

  COUNT(*)

      2234 =20

Another example

Table 'test1' on DBREMOTE owned by user2

Select count (*) from test1_at_DBREMOTE;

ERROR at line 1:
ORA-00942: table or view does not exist

Because user1 on DBREMOTE does not have any perms on test1 owned by user2, he cannot select from the table

On DBREMOTE user2 does a SQL> grant all on test1 to user1;

NOW when user1 on DBLOCAL does a SQL> select count (*) from test1_at_DBREMOTE; he will get results.

" How do you achieve seperation of schemas and privileges within the one database [whether local or remote database]"

By the use of the object_at_DBLINKNAME

The @DBLINKNAME will tell the database to go to the object in the remote database. YOU can have objects of same name in both DB's...

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg]=20 Sent: Tuesday, December 07, 2004 9:55 AM To: Hollis, Les; Tony.Vecchiet_at_xilinx.com; oracle-l_at_freelists.org Subject: RE: DB Links management

How does the global name help ?

If the DBLink is created without a identified by, the user must provide a=20
password
each time it is used. Also, how can the DB Link be used in automated programs,
views, Snapshots etc
How do you achieve seperation of schemas and privileges within the one database
[whether local or remote database] ?

Hemant

At 10:18 AM Thursday, Hollis, Les wrote:
>Use global names and have only ONE link from DB1 to DB2, etc....
>
>
>Create public database link DB1 using 'DB1';
>
>
>Notice NO connect to/identified by
>
>This means you will have to have a global name for your database.....
>
>
>
>
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Vecchiet
>Sent: Wednesday, December 01, 2004 7:21 PM
>To: oracle-l_at_freelists.org
>Subject: DB Links management
>
>With all the SOX work and password rotations, how do most of you
manage=3D20
>all the dblinks?
>No one wants to know anyone's passwords and we've already rebuilt
the=3D20
>dblinks view to exclude
>the password column. Anyhow, the DBA group got assigned recreating
all=3D20
>the dblinks
>because we can get into all the databases. Any one out there have =
a=3D20
>good method of managing
>this?
>thanks
>--
>http://www.freelists.org/webpage/oracle-l
>--
>http://www.freelists.org/webpage/oracle-l

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l