-
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!
-
-
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.
-
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|