DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: privs for create database links

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360

    privs for create database links

    Having some wierd problems creating a db link. I've got the privs but can't seem to create the link:

    SQL> select * from user_sys_privs;

    USERNAME PRIVILEGE ADM
    ------------------------------ ---------------------------------------- ---
    MNBD321 ALTER ANY PROCEDURE NO
    MNBD321 CREATE DATABASE LINK NO
    MNBD321 CREATE PUBLIC DATABASE LINK NO
    MNBD321 CREATE SESSION NO
    MNBD321 DELETE ANY TABLE NO
    MNBD321 EXECUTE ANY
    PROCEDURE NO
    MNBD321 INSERT ANY TABLE NO
    MNBD321 SELECT ANY TABLE NO
    MNBD321 UNLIMITED TABLESPACE NO
    MNBD321 UPDATE ANY TABLE NO

    SQL> create datbase link ice.world connect to batchlnkmi identified by batchlnkmi
    2 using 'iced321.world';
    create datbase link ice.world connect to batchlnkmi identified by batchlnkmi
    *
    ERROR at line 1:
    ORA-00901: invalid CREATE command

    Even if I grant DBA priv I cant seem to create:

    SQL> conn
    Enter user-name: / as sysdba
    Connected.
    SQL> grant dba to mnbd321;

    Grant succeeded.

    SQL> conn mnbd321/fred123
    Connected.
    SQL> create database link ice.world connect to batchlnkmi identified by batchlnkmi
    2 using 'iced321.world';
    create database link ice.world connect to batchlnkmi identified by batchlnkmi
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges


    Its probably something obvious, but its driving me mad an I cant see the wood for the god damn trees anymore!

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    If you look at the select * from user_sys_privs you'll see I already have create database link, create public database link and create session. I even granted DBA to the user and still got the same problem.

  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Right heres something odder:

    Create a new user and grant it DBA priv - can create the DB link OK.

    SQL> create user f1631 identified by f1631
    2 default tablespace users_01
    3 temporary tablespace temp_01;

    User created.

    SQL> grant connect, resource, dba to f1631;

    Grant succeeded.

    SQL> conn f1631/f1631
    Connected.
    SQL> create database link ice.world connect to batchlnkmi identified by batchlnkmi
    2 using 'iced321.world';

    Database link created.

    SQL> drop database link ice.world;

    Database link dropped.

    Then grant DBA to the user I'm having a problem with and try again

    SQL> conn
    Enter user-name: / as sysdba
    Connected.
    SQL> grant dba to mnbd321;

    Grant succeeded.

    SQL> conn mnbd321/fred123
    Connected.
    SQL> create database link ice.world connect to batchlnkmi identified by batchlnkmi
    2 using 'iced321.world';
    create database link ice.world connect to batchlnkmi identified by batchlnkmi
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    ?????????????????????

  5. #5
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Do u have a create session privilege on the remote database ?

  6. #6
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    can u compare both the users privileges and see now?

    SS

  7. #7
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    That doesnt make any difference (although I have the privs on the remote db) - a connection isnt made until the link is actually used - it doesnt test a connection on creation

  8. #8
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Ahhh - at last a result - some cunning b*****d had created a logon trigger that switched the schema if a user had a certain role (which my user does). As you cant create objects in another users schema I was getting this error
    Just to prove it:

    SQL> conn mnbd321/fred123
    Connected.

    SQL> alter session set current_schema = mnbd321;

    Session altered.

    SQL> create database link ice.world connect to batchlnkmi identified by batchlnkmi
    2 using 'iced321.world';

    Database link created.

  9. #9
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width