This discussion is archived
2 Replies Latest reply: Oct 12, 2012 4:12 PM by theaking1 RSS

APEX Admin Has No Privilege To Create Database Link - SOLVED

theaking1 Newbie
Currently Being Moderated
Application Express
Product Build: 4.1.1.00.23
Schema Compatibility: 2011.02.12
Last DDL Time: 09/10/2012 10:35:00 PM
Last DDL Time: 09/10/2012 10:43:41 PM
Host Schema: ANONYMOUS
Application Owner: APEX_040100

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

I have created the database link in sqlplus and it works fine there. Used sys login.

From Apex, in SQL Workshop - Object Browser or SQL Commands
The following is the error:
Create Database Link Failed.
ORA-01031: insufficient privileges
In Apex Admin login was used.

Is there a setting to "enable" the Create Database Link in Apex?

Edited by: user13459255 on Oct 12, 2012 10:29 AM
  • 1. Re: APEX Admin Has No Privilege To Create Database Link
    jariola Guru
    Currently Being Moderated
    user13459255 wrote:
    Application Express
    Product Build: 4.1.1.00.23
    Schema Compatibility: 2011.02.12
    Last DDL Time: 09/10/2012 10:35:00 PM
    Last DDL Time: 09/10/2012 10:43:41 PM
    Host Schema: ANONYMOUS
    Application Owner: APEX_040100

    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

    I have created the database link in sqlplus and it works fine there. Used sys login.

    From Apex, in SQL Workshop - Object Browser or SQL Commands
    The following is the error:
    Create Database Link Failed.
    ORA-01031: insufficient privileges
    In Apex Admin login was used.

    Is there a setting to "enable" the Create Database Link in Apex?
    Hi,

    This might help
    http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/advnc_db_link.htm#sthref2669
    >
    Note:
    By default, the CREATE DATABASE LINK system privilege is not granted to a provisioned workspace or database user. To use this feature, a DBA or administrator must grant this specific privilege to the database user in the user's workspace. See "Creating Database Links" in Oracle Database Administrator's Guide
    >

    From what you did post, I just wonder have you link APEX schema or ANONYMOUS to workspace?
    That is not good idea at all.

    Regards,
    Jari
    -----
    My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
    Twitter: http://www.twitter.com/jariolai
  • 2. Re: APEX Admin Has No Privilege To Create Database Link
    theaking1 Newbie
    Currently Being Moderated
    Thank you for the assistance. Had to do some more searching to arrive at solution.

    The problem was interpretation of the term +"user"+. As seen in the documentation the command was GRANT CREATE DATABASE LINK TO +"<user>"+;

    SOLUTION
    1. From sqlplus
    SQL> GRANT CREATE DATABASE LINK TO +"schema"+;
    schema is the Workspace Schema that will be used to create database link.

    2. Create Link in Apex
    I connected to an MSSQL database and used the SQL commands window to create the link.

    CREATE DATABASE LINK +"linkname"+
    CONNECT TO "sa" IDENTIFIED BY +"password"+
    USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SID = +"link SID"+ )
         )
         (HS=OK)
    )'
    /

    NOTE: The section after USING is taken from the TNSNAMES.ORA file. The +"link SID"+ is the same in the LISTENER.ORA file.

    All the best

    Edited by: theaking1 on Oct 12, 2012 4:00 PM

Legend

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