Forum Stats

  • 3,838,691 Users
  • 2,262,394 Discussions
  • 7,900,734 Comments

Discussions

CREATE easily Database Link for another user

Rodrigo Jorge DBA
Rodrigo Jorge DBA Member Posts: 86 Bronze Badge
edited Jan 11, 2016 6:12PM in Database Ideas - Ideas

Every time I am asked to create a link to another DB schema is the same headache. Sometimes I use the dbms_sys_sql.parse_as_user to execute the create code as the other user. I need to give the user the CREATE DATABASE LINK system privilege, execute the action and than revoke it back. Things get even worse when I'm on a Database Vault enabled DB.

Why Oracle does not simplify the things with a SYNTAX like:

CREATE DATABASE LINK SCOOT.TMP_LINK ......

Please Oracle, give me that syntax! =]

Regards,

Rodrigo Jorge

Franck PachotEduardo SmaniottoUser259623 -OracleRodrigo Jorge DBAuser11980779Hans-Martin RuffTom321Carsten KaftanManish Chaturvedivinaykumar2Andreas Huberuser1050928Pravin Takpireuser10212775Eric_BedardLothar Flatzbhagatsinghabhinivesh.jainborneselRobertOrtelhimmyulohmannAish13sysassysdbatop.gunJ.Schnackenberg991901GiankPkSven W.kulikouskiuser12218407N.B.tonibony7BPeaslandDBABrunoVromanberxAparna Dutta-OracleMike Ripleyuser12156491BlagyHristovaDavidMcWhinnieJames-ISHelpRobert MarzPeter Hraško31242633333717Sulabh GuptaAlbert Nelson Ablessed DBAfloo_baropentuningsAndrewSayerAlex Lamar-Oracle3380050Emad Al-Mousazeddicusshogo.mNorbertKluser11970842sdstuber
67 votes

Active · Last Updated

Comments

  • ajallen
    ajallen Member Posts: 1,796

    With the low frequency of this type of request, it is simple enough to do with the proxy login.

    1. alter user FOO grant create database link;

    2. alter user FOO grant connect through SOMEDBA;

    3. connect SOMEDBA[FOO]@mydb;

    4. create database link ...

    5. connect [email protected];

    6 revoke create database link from FOO;

    Done.

    Sure it is a few steps, but is quite do-able for occasional tasks like creating database links.

    tonibony7
  • Tmicheli-Oracle
    Tmicheli-Oracle Member Posts: 24 Red Ribbon

    We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

    As we move through the process the IDEA will change stages: (not in flow order)

    - Active

    - Already Offered

    - Archived

    - Coming Soon

    - For Future Consideration

    - in Progress

    - Partially Implemented

    - Under Review

    blessed DBA
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    You can CREATE TABLE <schema>.<table> if you have the right privilege -  so yes why not for private DB links......

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown
    edited Aug 6, 2015 9:04AM

    With the low frequency of this type of request, it is simple enough to do with the proxy login.

    1. alter user FOO grant create database link;

    2. alter user FOO grant connect through SOMEDBA;

    3. connect SOMEDBA[FOO]@mydb;

    4. create database link ...

    5. connect [email protected];

    6 revoke create database link from FOO;

    Done.

    Sure it is a few steps, but is quite do-able for occasional tasks like creating database links.

    ajallen wrote:
    
    ...
    
    Sure it is a few steps, but is quite do-able for occasional tasks like creating database links.
    
    
    

    If you work in a global distributed environment then creating database links often is not just occasional. Sometimes is more like a weekly recurring task.

    Also you missed to revoke the "connect through" privilege. This just shows that your workaround is pretty cumbersome.

    A sesnsible workaround would be if we could simply change the current user for the session. This does work for creating tables, but will not work for createing DB links.

    Example

    Log in as DBA.

    sql>alter session set current_schema=SOMEOTHERSCHEMA;
    session SET geändert.
    sql>create database link dblinktest;
    
    SQL-Fehler: ORA-01031: Nicht ausreichende Berechtigungen
    01031. 00000 -  "insufficient privileges"
    
    tonibony7blessed DBA
  • ajallen
    ajallen Member Posts: 1,796
    ajallen wrote:
    
    ...
    
    Sure it is a few steps, but is quite do-able for occasional tasks like creating database links.
    
    
    

    If you work in a global distributed environment then creating database links often is not just occasional. Sometimes is more like a weekly recurring task.

    Also you missed to revoke the "connect through" privilege. This just shows that your workaround is pretty cumbersome.

    A sesnsible workaround would be if we could simply change the current user for the session. This does work for creating tables, but will not work for createing DB links.

    Example

    Log in as DBA.

    sql>alter session set current_schema=SOMEOTHERSCHEMA;
    session SET geändert.
    sql>create database link dblinktest;
    
    SQL-Fehler: ORA-01031: Nicht ausreichende Berechtigungen
    01031. 00000 -  "insufficient privileges"
    

    While I agree with you Sven W, that it would be nice to be able to create database links in other schemas (either by changing the session schemas or prepending the schema name to the db link name), what I posted will get the job done, cumbersome as it may be. Also, I did not miss revoking the "connect through" privilege. Since the privilege is granted to me (DBA), I leave it out there for convenience sake.  As a bonus, here is my little stored SQL that I use to see what accounts I can login to.

    -- --- proxies.sql -----

    SELECT client AS "You may connect as",

           CASE authorization_constraint

             WHEN 'PROXY MAY ACTIVATE ROLE'             THEN 'ROLE '|| role

             WHEN 'PROXY MAY ACTIVATE ALL CLIENT ROLES' THEN 'ALL CLIENT ROLES'

             ELSE authorization_constraint

           END AS "With privileges"

      FROM dba_proxies

    WHERE proxy = USER

    ORDER

        BY 1

    /

    -- -----------------

  • Sven W.
    Sven W. Member Posts: 10,541 Gold Crown

    You can CREATE TABLE <schema>.<table> if you have the right privilege -  so yes why not for private DB links......

    Remember: Database links do follow completly different naming conventions as all other identifiers including tables.

    An identifier for a database link can

    a) have up to 128 characters

    b) can include dots.

    Example for a valid dblink name.

    sales.oracle.com

    So the question now is: is "sales" a schema name or part of the database name?

    And what happens if somebody creates a schema "sales" in the production database.

    The script that you use to run in test would not be able to run in prod, or would create a link "oracle.com" in an unwanted schema.

    That's why I proposed to at least make the creation working when alter session set current schema is issued. There is less ambiquity in such a scenario.

    tonibony7
  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy

    While I agree with you Sven W, that it would be nice to be able to create database links in other schemas (either by changing the session schemas or prepending the schema name to the db link name), what I posted will get the job done, cumbersome as it may be. Also, I did not miss revoking the "connect through" privilege. Since the privilege is granted to me (DBA), I leave it out there for convenience sake.  As a bonus, here is my little stored SQL that I use to see what accounts I can login to.

    -- --- proxies.sql -----

    SELECT client AS "You may connect as",

           CASE authorization_constraint

             WHEN 'PROXY MAY ACTIVATE ROLE'             THEN 'ROLE '|| role

             WHEN 'PROXY MAY ACTIVATE ALL CLIENT ROLES' THEN 'ALL CLIENT ROLES'

             ELSE authorization_constraint

           END AS "With privileges"

      FROM dba_proxies

    WHERE proxy = USER

    ORDER

        BY 1

    /

    -- -----------------

    You both have a point. I can see how this could come in hand, but it would vary from one environment to the next.