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!

hey

check this out....

http://www-eleves-isia.cma.fr/docume...BASE-LINK.html


HTH

Srini

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.

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

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

Do u have a create session privilege on the remote database ?

can u compare both the users privileges and see now?

SS

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

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.

:)

:rolleyes:

Quote:

---

Originally posted by fraze
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).

---

Oh dear thats funny, I hate it turns out to be something daft like that......what a bugger. at least it made me laugh on this cold Wednesday morning.

:D