3

I`m trying to open up for REST API-calls from PL/SQL in an Oracle database, and to do this, I need my user to be able to perform network calls.

After a bit of research, I believe the following approach should work:

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'my-user@my-domain',
                           principal_type => xs_acl.ptype_db));
END;

...and it does seem to work so long as my-user is a single / simple name, such as 'my-user'; The problem occurs when trying to apply this to a username containing an @, as in the example above.

In such cases, I get the error:

ORA-44003: invalid SQL name

When comparing a user containing @ in his username with one without it, the only obvious difference I can see it that the former has Authentication Type = EXTERNAL, whereas the latter has Authentication Type = PASSWORD, but I can't see why that should matter.

Rather it seems to me like this might be a syntax-related issue - can anyone either confirm or deny this, and point out the correct way to pass in the principal parameter in a case like this?

Kjartan
  • 18,591
  • 15
  • 71
  • 96

1 Answers1

2

The principal is always a database user, so it can't have a @ in the name anyway. You need to specify the database user that will be executing the REST calls on behalf of the end user.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Thanks for your input. I'm finding this a little confusing though: When I try to run an http-query using the `@`-user, I get an error: "*network access denied by access control list (ACL)*". This is the same error I got earlier for the other user, which now has access. Is the limited access not related to the `@`-user then? And if the `@`-user`s query is in effect run by another user, then how can I identify which user that is? – Kjartan Sep 18 '19 at 11:51
  • Not sure - maybe the REST calls are being executed by an ORDS account? – Jeffrey Kemp Sep 19 '19 at 00:50
  • ACL problems can be debugged with this: with aclprivs as ( select P.principal, N.acl, P.privilege, P.is_grant, P.invert, N.host, N.lower_port, N.upper_port, X.shared, X.security_class_name, X.security_class_ns, X.description from dba_network_acl_privileges P, dba_network_acls N, xds_acl x where P.aclid = N.aclid and N.aclid = X.aclid(+) ) select * from aclprivs where principal = '...user...' order by acl, privilege; – Jeff Laing Sep 30 '19 at 04:25
  • You'll need to substitute in the relevant user in the principal clause. Access to external host/ports must be done for every user that attempts it. – Jeff Laing Sep 30 '19 at 04:26