Database Link for All users

Kam_oracle_apex wrote:

 

Dear Friends

 

I have a two servers. (server1 and server2). In server1 oracle10g is installed and server2 has oracle 18c. In server1 almost 10 users/schemas are created and several tables are created in different schemas. I want to make reports in server2 and some of the data want to fetch from server1 from different schemas. Can you please tell me, can i make one database link for all schemas?

 

Please help.

 

Regards

Kam

I'm not sure that a link between 18c and 10g will even work at all.  I think the newest database that is interoperable with 10g would be 12.2 (you can look it up on MOS)

If you get the link to work, the only thing you have to "do" to make it for "all schemas" is

1) make sure the user/schema actually being used by the link has necessary privileges on the objects in the other schemas, and

2) qualify any table names with the owning schema.

CREATE  DATABASE LINK "MYLINK"

  CONNECT TO "LKUSER_1" IDENTIFIED BY "APassword"

  USING 'MYLINK';

 

SELECT A.COL1,

              B.COL2

              C.COL3

FROM LOCAL_TABLE A,

          LK_USER1_TABLE@MYLINK B,

          LK_USER2_TABLE@MYLINK C

Connect to server 1 using a reporting user that has only SELECT privilege on all required tables, even from different schemas:

Server 1

CREATE USER rpt IDENTIFIED BY rpt;

GRANT CREATE SESSION TO rpt;

GRANT READ ON schema1.tableA TO rpt;

GRANT READ ON schema1.tableB TO rpt;

GRANT READ ON schema2.tableA TO rpt;

GRANT READ ON schema2.tableB TO rpt;

...

GRANT SELECT ON schema9.tableZ TO rpt;

You may also want to assign a TEMPORARY tablespace, use a robust password or other authentication method, etc etc.

Server 2

Create the DBLINK using "rpt" as user.

Kam, Ed and Fernigrini have pretty much provided an answer.  If you define a public database link that connects with a specified Oracle username that has been granted all necessary privileges then any user in the local database can reference any of the remote objects which is the potential rub as any user defined to the local database will be able to select from the remote objects.  Depending on the data in the remote system tables being accessed and if DML privileges also were or are ever granted then this type of access could be a security issue.

- -

The alternative is to have the database link connect as the local Oracle username which means every potential username in the local database has to be defined in the remote database with the same password and granted all necessary privileges.  This approach can be a pain to maintain, but may be necessary depending on the environment.

- -

We usually define public synonyms on the remote objects: create or replace object_name for owner.object_name@db_link_name to make accessing the remote objects easier.

- -

HTH -- Mark D Powell --

Mark, good point about the local synonyms.  I'd overlooked that part.

You may want to define a naming convention for synonyms, we use something like xxx_yyy_zzzzzzzzz where xxx is the source system abbreviation (CRM, ERP, HR, etc), yyy is the source schema, and zzzzzz is the source table name, or a user friendly table name when tables are not easily identified.,

L. Fernigrini wrote:

 

You may want to define a naming convention for synonyms, we use something like xxx_yyy_zzzzzzzzz where xxx is the source system abbreviation (CRM, ERP, HR, etc), yyy is the source schema, and zzzzzz is the source table name, or a user friendly table name when tables are not easily identified.,

I can't preach enough about the value of good, solid, ENFORCED naming conventions.  "Back in the day" it was common for the IT dept to have a naming standards committee.  They would develop and publish naming standards for EVERYTHING ... usernames, table names, column names, file names, variable names, etc, etc, etc.  When creating a new object, one simply consulted the standards manual and constructed the name accordingly.  Never any collisions with reserved words, never any question about what the name referred to or what it meant, or what it related to.  Never any 'oh gee, what am I going to name this.'  When picking up someone else's code for maintenance, never any question about what was what or what anything referred to.

Today it just seems to be every man for himself.

>> Today it just seems to be every man for himself  <<

- -

I guess that is better than, "what the hell were they on?"

- -

And another favorite of mind is seeing what turns out to be an abbreviation of words in the coder's native language when all the other columns are in English.

- -

Just grumbling.  -- Mark D Powell --

Mark D Powell wrote:

 

>> Today it just seems to be every man for himself <<

- -

I guess that is better than, "what the hell were they on?"

- -

And another favorite of mind is seeing what turns out to be an abbreviation of words in the coder's native language when all the other columns are in English.

- -

Just grumbling. -- Mark D Powell --

Ah yes.

Many years ago (when I was still a Cobol programmer) I took over a system that had been written by a group of Italians.  Naturally a lot of variable and paragraph names were in Italian.  My co-worker was pulling his hair out trying to understand it.  Here's where 'no education is ever wasted' comes in.  While I didn't really speak Italian, I am a classically trained musician (college degree was in Music Ed.) and a lot of standard music terminology is Italian. Not to mention singing a lot of Latin.  With that I was able to parse out meaning of variable and paragraph names where my co-worker failed.