Oracle database link - connect to proxy with speci

Question

A user is granted connect by proxy via another user whose name contains special characters:

CREATE USER "A-USER" IDENTIFIED BY "a";
CREATE USER foo IDENTIFIED BY "random-trash";
ALTER USER foo GRANT CONNECT THROUGH "A-USER";

Now I'm struggling to create a database link to this:

CREATE PUBLIC DATABASE LINK dbl CONNECT TO A-USER[FOO] IDENTIFIED BY "a";
-- ... but it complains about '-'

CREATE PUBLIC DATABASE LINK dbl CONNECT TO "A-USER[FOO]" IDENTIFIED BY "a";
-- ... but it treats whole "A-USER[FOO]" as a username

CREATE PUBLIC DATABASE LINK dbl CONNECT TO "A-USER"[FOO] IDENTIFIED BY "a";
-- ... but it expects IDENTIFIED right after closing quote

Apart from obviously getting rid of special characters in usernames, is there any notation I could use for that?

Answer 1

This should work, with or without the special characters; this is in 11.2.0.3, first without:

CREATE USER "AUSER" IDENTIFIED BY "a";
CREATE USER bar IDENTIFIED BY "random-trash";
ALTER USER bar GRANT CONNECT THROUGH "AUSER";
GRANT CREATE SESSION TO "AUSER";
GRANT CREATE SESSION TO "BAR";

CREATE PUBLIC DATABASE LINK dbl CONNECT TO "AUSER[BAR]" IDENTIFIED BY "a" using 'MYDB';
SELECT * FROM dual@dbl;

D
-
X

And with:

CREATE USER "A-USER" IDENTIFIED BY "a";
CREATE USER foo IDENTIFIED BY "random-trash";
ALTER USER foo GRANT CONNECT THROUGH "A-USER";
GRANT CREATE SESSION TO "A-USER";
GRANT CREATE SESSION TO "FOO";

DROP PUBLIC DATABASE LINK dbl;
CREATE PUBLIC DATABASE LINK dbl CONNECT TO "A-USER[FOO]" IDENTIFIED BY "a" using 'MYDB';
SELECT * FROM dual@dbl;

D
-
X

Neither works on 11.2.0.4; with or without the special character it errors:

select * from dual@dbl
                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBL

If you're on 11.2.0.4 or higher then you may be hitting bug 19191702, which seem to have broken the proxy capability through a database link. See MOS note 19191702.8 for more information. This seems to be intentional new behaviour rather than a bug though, and the old behaviour - where this worked - is being described as incorrect.

There may be a patch available to allow a specific event to be set that reverts the behaviour (and it's supposedly available in 12.2), but as "an interim solution to allow existing applications, reliant on the [old] incorrect behaviour, to continue to work". If there isn't a patch for your platform and version or the event doesn't help then you'll need to raise a service request; it may be worth raising one anyway of course.