This discussion is archived
14 Replies Latest reply: Apr 25, 2013 7:44 PM by SalmanQureshi RSS

why should i set  global_name=*'TRUE'*

972355 Newbie
Currently Being Moderated
if I create public database link its should be TRUE, What's the logic here ?

SQL>show parameter global_name;

NAME TYPE VALUE
global_names boolean FALSE

SQL>create public database link mydba connect to scott identified by tiger using 'ORCL';

Database link created.

SQL> select * from emp@mydba

select count(*) from emp@mydba;

COUNT(*)
14

Even i set global_name='FALSE' i can create public database link. then
why should i set global_name=*'TRUE'*
  • 1. Re: why should i set  global_name=*'TRUE'*
    rp0428 Guru
    Currently Being Moderated
    >
    if I create public database link its should be TRUE, What's the logic here ?
    >
    Says who? That isn't true at all. You don't have to see it to TRUE
    >
    Even i set global_name='FALSE' i can create public database link. then
    why should i set global_name=*'TRUE'*
    >
    You do NOT have to set it to true but you can if you want your links to have the same name as the DB they connect to.
    http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams088.htm
    See the Database Reference

    >
    GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

    If the value of GLOBAL_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.
    >
    The CREATE DATABASE LINK section of the SQL Language Doc tells you what that setting means.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm
    >
    If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.
    >
    If global_name is TRUE then, as the doc says, 'the database link must have the same name as the database to which it connection.
  • 2. Re: why should i set  global_name=*'TRUE'*
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    if I create public database link its should be TRUE, What's the logic here ?

    SQL>show parameter global_name;

    NAME TYPE VALUE
    global_names boolean FALSE

    SQL>create public database link mydba connect to scott identified by tiger using 'ORCL';

    Database link created.

    SQL> select * from emp@mydba

    select count(*) from emp@mydba;

    COUNT(*)
    14

    Even i set global_name='FALSE' i can create public database link. then
    why should i set global_name=*'TRUE'*
    When a PUBLIC database link is created, this means that every schema can use this DB LINK.

    wrt GLOBAL_NAME
    when all else fails Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_admin001.htm#ADMIN12149
  • 3. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    My Specific ques is clearly explained here ..
    SQL> show user;
    USER is "XUSER"

    SQL> select count(*) from scott.emp@mydba;

    COUNT(*)

    14

    SQL> conn /as sysdba
    Connected.

    SQL> show parameter global_names;

    NAME TYPE VALUE
    global_names boolean FALSE

    Please understand i don't need any links. I need answer for my specific question.

    Public database link - Everyone can access - This is i know WELL

    +What's the need to set global_name=TRUE , in my example i set global?_name='FLASE' only.+
    but userx can access scott table.
  • 4. Re: why should i set  global_name=*'TRUE'*
    rp0428 Guru
    Currently Being Moderated
    >
    My Specific ques is clearly explained here ..
    >
    And my specific answer was provided in my reply above.
  • 5. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    If global_name is TRUE then, as the doc says, 'the database link must have the same name as the database to which it connection.
    *>> DID you mean following below >>*

    MY DB_name is ORCL

    SQL> create public database link *<orcl>*
    connect to scott
    identified by tiger
    using 'tns_alias_name' ;
  • 6. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    Thanks to both ..


    SQL> create public database link orcl connect user scott identified by tiger using 'orcl';
    create public database link orcl connect user scott identified by tiger using 'orcl'
    *
    ERROR at line 1:
    ORA-02082: a loopback database link must have a connection qualifier

    SQL> create public database link orclx connect to scott identified by tiger using 'orcl'
    2 /
    Database link created.

    I GOT it . Thanks rp and sb ..
  • 7. Re: why should i set  global_name=*'TRUE'*
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    If global_name is TRUE then, as the doc says, 'the database link must have the same name as the database to which it connection.
    *>> DID you mean following below >>*

    MY DB_name is ORCL

    SQL> create public database link *<orcl>*
    connect to scott
    identified by tiger
    using 'tns_alias_name' ;
    >
    If global_name is TRUE then, as the doc says, 'the database link must have the same name as the database to which it connection.
    *>> DID you mean following below >>*

    MY DB_name is ORCL

    SQL> create public database link *<orcl>*
    connect to scott
    identified by tiger
    using 'tns_alias_name' ;
    no, not correct
  • 8. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    I got answer ...
  • 9. Re: why should i set  global_name=*'TRUE'*
    rp0428 Guru
    Currently Being Moderated
    You need to read the documentation to see what a global database name is
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#i1007827
    >
    Global Database Names in Database Links

    To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.

    For example, Figure 29-4 illustrates a representative hierarchical arrangement of databases throughout a network.
    >
    That section has extensive information about your question.
  • 10. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    Already i read many times , Some points came from previous versions ..

    I mean few things are not understand able - *"BLIND"*

    So i am coing to my direct question :
    For remote DB access (Security  Context) which one is recommended  ?
         FIXED USER  CURRENT USER  CONNECTED USER

    Thanks for given link , If anyone provide basic explanation links ... It will be more useful.
  • 11. Re: why should i set  global_name=*'TRUE'*
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    Already i read many times , Some points came from previous versions ..

    I mean few things are not understand able - *"BLIND"*

    So i am coing to my direct question :
    For remote DB access (Security  Context) which one is recommended  ?
         FIXED USER  CURRENT USER  CONNECTED USER

    Thanks for given link , If anyone provide basic explanation links ... It will be more useful.
    as with most things related to Oracle the answer is, "IT DEPENDS!"

    If one of the above was always better, then the other two would not be available!
  • 12. Re: why should i set  global_name=*'TRUE'*
    rp0428 Guru
    Currently Being Moderated
    >
    For remote DB access (Security Context) which one is recommended ?
    >
    Did you look at that diagram in the last link I provided?

    It shows an environment with MANY databases.

    In an environment like that you would use global_name of TRUE so the link name must be the global name. That ensures that you are connecting to the correct database since global names MUST BE unique.

    If your environment only has one or a few DBs with simple names that don't conflict you can use FALSE if you want.
  • 13. Re: why should i set  global_name=*'TRUE'*
    972355 Newbie
    Currently Being Moderated
    YES .. Almost i got it.

    At present from your previous answer , i need correct solution

    If my environment having has one or a few DBs ..

    - Private or Public DB links enough ... - Is this your suggestion ?

    However 'n' number of databases NOT in directory server .. So uniquely no need to identify databases in dir. Server.

    - So Global DB link not recommended. - Am i right ?


    Thanks ... If i guess wrongly ... Please little explanation for US
  • 14. Re: why should i set  global_name=*'TRUE'*
    SalmanQureshi Expert
    Currently Being Moderated
         FIXED USER  CURRENT USER  CONNECTED USER
    These are not directly related to security to your environment, rather it is related to your need. But some of above are more secure than the other.
    If you connect as scott and you want to connect to remote database only as scott, you can go for FIXED USER option. Any other user will not be able to use this database link otehr than SCOTT (both SCOTTs should have same password in both databases because password is not stored with database link and is verified when SCOTT in local database connects to SCOTT in remote database)

    FIXED USER is useful if you there is a user MICHAEL in remote database and you create a database link to connect to remote database with username MICHAEL in local database by providing its password and connect string. When you will use this database link, it will use MICHAEL to connect to remote database and will use all privileges granted to MICHAEL in remote database

    CURRENT USER is the most secure and utilizes Oracle Advance Security (licensed feature).

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#i1007964

    What is global user
    http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm#i1006269


    Salman

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points