1

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?

Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60
  • 1
    I would say your last command would be the one to use, but it looks like proxy users aren't supported with database links. See http://stackoverflow.com/questions/32706776/oracle-create-db-link-using-a-proxy-schema for a similar question. – Boneist Nov 09 '15 at 16:36
  • See also MOS note 1477939.1, though that suggests the second syntax should work in 11.2.0.3 (as indeed it does); and related bug/feature-request 14370226; and bug 19191702 for it not working any more in 11.2.0.4, even without the special character. – Alex Poole Nov 09 '15 at 16:48
  • Actually, your second syntax also works in 11.2.0.3, with the special character. Which DB version are you using and does it work for you without the special character? – Alex Poole Nov 09 '15 at 16:55
  • Very interesting. I'm working on 12.1.0.2. I tested 11.2.0.3 right now and in fact it works with and without special characters... – Kombajn zbożowy Nov 09 '15 at 17:14

1 Answers1

2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks for pointing that out. In fact I was doing a test earlier on older Oracle version without special chars and incorrectly assumed it's the special chars that are guilty. – Kombajn zbożowy Nov 12 '15 at 20:43
  • That MOS note implies the old behavior can be enabled by running `ALTER SYSTEM SET EVENTS '19191702 level 1';` but I can't get that to work for me on 12.2 on Windows. Am I reading that note wrong, using the wrong syntax, or something else? – Jon Heller Apr 11 '17 at 04:30
  • @JonHeller - I think that's been added since I looked at it before, since I don't recall seeing it and it only seems to be valid in 12.2. Though it looks like maybe back ports exist? (It's accepted in unpatched 11.2.04 but doesn't seem to do anything.) I don't have a 12.2 instance I can experiment on I'm afraid. I would assume that needs to be done on the remote end, but perhaps it needs to be local too for some reason? – Alex Poole Apr 11 '17 at 08:25