Node-oracledb: ExternalAuth and Proxy Users

[jacobhobson]

I have the most recently stable version of oracledb. Whenever I use externalAuth of true, I find no way to leverage logging in as a proxy user.

[cjbj]

@jacobhobson there are various permissible combinations of proxying and authentication, and improvements in newer client versions. Can you answer the issue template questions and let us know your environment? Also we need to know to what your goals are (e.g. pooling).

[jacobhobson]

1. What is your Node.js version? Is it 64-bit or 32-bit? Run version.js from https://github.com/oracle/node-oracledb/blob/master/examples/version.js
   Version Number: _8.11.3_
   OS: _Windows 10_
   bit: _64_

2. What is your node-oracledb version?
   _2.3.0_

3. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
   _I'm attempting to login using externalAuth, creating a heterogeneous connection pool. The pool creates successfully. But since I'm using externalAuth, I get the following error message when attempting to log in with the proxy username:_

Error: DPI-1032: user name and password cannot be set when using external authentication errorNum: 0, offset: 0

1. What error(s) you are seeing?
   _See Number 3_

2. What OS (and version) is Node.js executing on?
   _See Number 1_

3. What is your Oracle client (e.g. Instant Client) version? Is it 64-bit or 32-bit? How was it installed? Where is it installed?
   _It should be 64-bit. It's running on a redhat server, but I'm accessing it through a Windows machine._

4. What is your Oracle Database version?
   12.2.1

//My create pool parms:
poolAlias: 'test',
externalAuth: true,
connectString: db_host+ '/' + service,
homogeneous: false,
user: 'proxy_user'

[cjbj]

What's your Oracle Client version? If you're not using 18.3, try it. If I recall correctly, there was an improvement in a recent version regarding external auth. I am waiting to review details.

[jacobhobson]

Thank you! Will check tomorrow and get back with you.

[jacobhobson]

@cjbj, when I run:

  DISTINCT
  s.client_version
FROM
  v$session_connect_info s
WHERE
  s.sid = SYS_CONTEXT('USERENV', 'SID');

I get 12.2.0.1.0, but that seems like the Oracle DB version. What's the quickest way to find my client version?

Also, I would find it odd that I can proxy in using IDEs like Oracle SQL Developer (which uses external auth) and then not do it via nodejs from the same machine. Don't you? Thanks for your help!

[cjbj]

@jacobhobson try console.log("Oracle Client library version:", oracledb.oracleClientVersionString);

SQL Developer typically runs over JDBC which has it's own protocol into the DB. Also there are differences between direct and pooled connections - have you checked standalone connections in node-oracledb?

[jacobhobson]

@cjbj, firstly, major thanks for how responsive you are on this project. Really appreciate getting such immediate feedback.

Response was: Oracle Client library version: 12.2.0.1.0, so guess that's the oracle client after all.

I have not tried without pooling. I'll attempt that and get back with you.

[cjbj]

@jacobhobson check with 18.3 and also share a code snippet so we can see what username/password syntax is being passed.

[jacobhobson]

@cjbj,

Here's my connection attributes object:

{ externalAuth: true,
  connectString: '###/###',
  homogeneous: false,
  user: '[PROXYUSER]'
}

[jacobhobson]

@cjbj, wanted to re-up this if you have a second to look.

If you see my last note, I gave you an example of my connection attributes object. I've tried ever which way, 'user[proxy]' included. I just get an error saying

Error: DPI-1032: user name and password cannot be set when using external authentication errorNum: 0, offset: 0

I currently have to connect with the client library version that I have. But I'm able to proxy in from IDE's like Oracle SQL Developer, all while using external authentication. Thoughts?

[cjbj]

@jacobhobson with the focus on getting node-oracledb 3 out, we didn't circle back on this.

Did you ever try with an Oracle 18.3 client? Can you step back and explain what the goal is from the access and auditing point of view? Who is granted what privileges? What are you trying to create the pool with vs get the connection from the pool with?

SQL Developer is not a useful/fair comparison, see my earlier comment.

[jacobhobson]

@cjbj, I missed that point.

Sorry to not address 18.3. Our corporation requires us to use the current version and will not allow me to install another client version.

The goal here is to authenticate as user X via OS (my Windows credentials). However, I need to access objects under user Y. User Y has all the privileges, but user X has been granted access to proxy in as user Y.

This snippet below is what I use to create the pool:

{ externalAuth: true,
  connectString: '###/###',
  homogeneous: false,
  user: '[PROXYUSER]'
}

However, whenever I run a getConnection, I've tried using both the proxy user as an attribute and without any attributes. I successfully get a connection, but I am only able to access items as user X, not user Y.

[cjbj]

@pvenkatraman @octokyle can you take a look?

[pvenkatraman]

@jacobhobson create heterogeneous-pool +externalAuth=true with no username/password, and provide
{ user : "[PROXYUSER}" } in square brackets as displayed in pool.getConnection(), the session username will be picked up from wallet.

[jacobhobson]

@pvenkatraman, trying this now. Thank you for your response!

[jacobhobson]

@pvenkatraman, I completed the following:

1) Created a heterogenous pool w/externalAuth of true, no username/password.
2) Ran a getPool() and stored that to a variable.
3) passed { user: "[PROXYUSER]" } to my poolVar.getConnection.
4) executed a query w/that connection

The result is "view/table does not exist," when it does under that proxy user.

[pvenkatraman]

@jacobhobson Please refer to https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connpoolproxy, You need to grant "connect-through" access as
ALTER USER sessionuser GRANT CONNECT THROUGH proxyuser;
This allows proxy user to see and operation on sessionuser schema.

[pvenkatraman]

Here is an example:

oracledb.createPool (
{ user : “”, // Proxy User
password : “”,
homogeneous : false,
externalAuth : true }; -> creates heterogeneous pool for externalAuthentication

pool.getConnection (
{ user : “[scott]”} ) -> creates proxy session

Proxy user name is picked from wallet.

[jacobhobson]

Here's my createPool, which looks good and works; I can connect to my personal schema's objects:

oracledb.createPool (
{ poolAlias: 'POOL_ALIAS_HERE',
  externalAuth: true,
  connectString: 'CONNECT_STRING_HERE',
  homogeneous: false,
  user: '',
  password: '' });

Here's my getConnection (the pool being stored into var "pool"):

pool.getConnection(
{ user: '[VERIFIED_WORKING_PROXY_USER_HERE]' });

This sadly is not working. Thoughts?

[jacobhobson]

@pvenkatraman, see my above comment. Please note: I have been granted the CONNECT THROUGH access and successfully confirmed that access using other means (Oracle SQL Developer).

[dmcghan]

@pvenkatraman I ran a basic test and I'm running into the same issue as @jacobhobson. It's as though the externalAuth flag forces a code path that doesn't work with proxy connections.

In other words, if externalAuth is enabled, then the value passed to pool.getConnection seems to be ignored. The connection returned is the user defined in the wallet (I'm using a wallet for external auth my test).

There is this comment in the External Authetication section of the doc:

When externalAuth is set, any subsequent connections obtained using the oracledb.getConnection() or pool.getConnection() calls will use external authentication. Setting this property does not affect the operation of existing connections or pools.

Perhaps that's the code path being described?

[jacobhobson]

@dmcghan @pvenkatraman appreciate the two of you looking into this. My colleagues are very excited to get to use Oracle at this capacity!

[jacobhobson]

@dmcghan, @pvenkatraman, just checking in and re-upping this thread. Anything new here?

[dmcghan]

@jacobhobson It's Oracle OpenWorld this week, a busy time for many Oracle employees. Please be patient, I know @pvenkatraman is working to reproduce your issue.

[easyas314]

@dmcghan, @pvenkatraman, this same issue occurs with a non-pooled connection...

I'm an Oracle DBA and I regularly connect to a database with this: sqlplus [sc0121]/@ORC
(this is a proxy connection using the tns alias ORC and my sqlnet external password store wallet)

So, what about node-oracledb ... here's the version.js output:

Run at: Tue Nov 06 2018 13:05:46 GMT-0500 (Eastern Standard Time)
Node.js version: v10.10.0 (linux x64)
Node-oracledb version: 3.0.0
Oracle Client library version: 12.2.0.1.0
Oracle Database version: 11.2.0.4.0

Here's a segment of code that demonstrates a failure attempt to use a proxy user (the schema to which I want to proxy connect as) with a wallet:

var oracledb = require('oracledb');

// Get a non-pooled connection
oracledb.getConnection(
  {
    externalAuth: true,
    user : "SC0121",
    connectString : "ORC" // tns alias using wallet
  },
  function(err, connection) {
    if (err) {
      console.error(err.message);
      return;
    }
    connection.execute(
      // The statement to execute
      `SELECT sys_context('USERENV','SESSION_USER') as session_user,
            sys_context('USERENV','PROXY_USER') as proxy_user
       FROM dual`,

Result: DPI-1032: user name and password cannot be set when using external authentication

... so just to see it working without the proxy user defined:

var oracledb = require('oracledb');

// Get a non-pooled connection
oracledb.getConnection(
  {
    externalAuth: true,
    //user : "SC0121",
    connectString : "ORC" // tns alias using wallet
  },
  function(err, connection) {
    if (err) {
      console.error(err.message);
      return;
    }
    connection.execute(
      // The statement to execute
      `SELECT sys_context('USERENV','SESSION_USER') as session_user,
            sys_context('USERENV','PROXY_USER') as proxy_user
       FROM dual`,

[ { name: 'SESSION_USER' }, { name: 'PROXY_USER' } ]
[ [ 'OPS$BJS13B', null ] ]

I think this makes the case clearer -- externalAuth doesn't like a user defined. Period.

[cjbj]

With my bleary, jet-lagged eyes, that looks similar to the test cases I sent @pvenkatraman last week.
I noted that after connecting with OS external authentication in node-oracledb a subsequent ALTER SESSION SET CURRENT_SCHEMA works, so there is a work around.

[jacobhobson]

@cjbj, altering the session's current_schema doesn't actually give the session permission to current_schema's objects, which is vital for our application.

[jacobhobson]

@cjbj / @dmcghan / @pvenkatraman , am I missing something on the workaround? If I set current_schema, am I supposed to be able to access objects with the proxy's permissions? I might be doing it wrong, but I have yet to see that workaround work. Thanks, all!

[dmcghan]

@jacobhobson No, you're correct. CURRENT_SCHEMA is just used to set to the default schema when one isn't specified. It doesn't affect permissions to objects.

[jacobhobson]

@dmcghan / @cjbj / @pvenkatraman, is there another node.js library you recommend for use with Oracle for the time-being? This issue is a show-stopper for our internal application's usage. Thanks!

[cjbj]

@jacobhobson I saw @pvenkatraman submitted a code change for internal review yesterday.

[cjbj]

I just played a bit with 3.0.0 on Linux with Oracle DB 18.3, see issue980c.js. The pooled test scenario https://github.com/oracle/node-oracledb/issues/980#issuecomment-429058953 works fine for me, as you can see from the output I added as a comment. I'll catch up with @pvenkatraman when his day starts and see what he gets on Windows. I see he mentioned SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora, which is not a Linux thing :) The proposed fix I talked about in my earlier comment looks like it will help the standalone case in https://github.com/oracle/node-oracledb/issues/980#issuecomment-436356895 which, with 3.0.0, currently gives me the DPI error described in that issue comment.

The summary for the moment is that, for what I understand your scenario to be, the node-oracledb implementation already seems to do what it is supposed to.

[pvenkatraman]

@jacobhobson from your example posted on Oct 12, 2018

oracledb.createPool (
{ poolAlias: 'POOL_ALIAS_HERE',
externalAuth: true,
connectString: 'CONNECT_STRING_HERE',
homogeneous: false,
user: '', // Proxy user will be picked up externally (Wallet or OS).
password: '' });

You have
pool.getConnection(
{ user: '[VERIFIED_WORKING_PROXY_USER_HERE]' });

Please correct this to
pool.getConnection(
{ user: '[SESSION_USER]' }); // Schema Owner or Session user in [].

Let us say your OS user is jacobhobson, on Linux, you will have to create an user externally authenticated as "OPS$JACOBHOBSON" and on Windows, configure your init.ora parameter to use OS_AUTHENT_PREFIX ="" and externally authenticated username should be "JACOBHOBSON". And let us say, JACOBHOBSON has to access SCOTT schema, (assuming you have granted CONNTECT THROUGH access to OS-user created as above), then

pool.getConnection ( { user : "[scott]"} );

will work. Scott's password is not provided, and connect through allows jacobhobson to access the scehma. (Please create username OPS$JACOBHOBSON or JACOBHOBSON in all caps)

The same thing on a stand-alone connection will report DPI-1032.
oracledb.getConnection ( { user : "[scott]", externalAuth : true...|);

[jacobhobson]

@pvenkatraman, the VERIFIED_WORKING_PROXY_USER_HERE is the schema owner. I have connect-through access to USERA. I've verified my external authentication is working successfully via sqlplus and sqldeveloper. However, when I attempt pool.getConnection({user: "[USERA]"}), I cannot access any of USERA's objects, which I can do in both sqlplus and sqldeveloper.

[anthony-tuininga]

@jacobhobson, are you able to test with 18.3 instant client? It appears that this test behaves differently in 18.3 when compared to earlier versions. More specifically, this test works as expected in 18.3 and ignores the "[SESSION_USER]" designation entirely in earlier versions. I think this matches what you are trying to do. Can you confirm?

    config = { connectString: connectString, externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "[SESSION_USER]" });

[jacobhobson]

@anthony-tuininga, correct, that's exactly what I'm trying to do. The party in question will all use the 12.2 Oracle client regularly, so even if I get it working in 18.3, that won't solve the problem. Let me check.

I just ran another test using the following:

var oracledb = require('oracledb');

async function test()
{

var pool = null;
var connection = null;
var result = null;

try 
{
    let parms = {
        externalAuth: true,
        connectString: 'host/service',
        homogeneous: false,
    };

    pool = await oracledb.createPool(parms);

    connection = await pool.getConnection({user: "[SESSION_USER]"});

    let sql = `
        SELECT *
        FROM session_user.table
        WHERE rownum < 10
    `;

    result = await connection.execute(sql);
    console.log(result.rows);

}
catch(err)
{
    console.log(err);
}
finally
{
    if(connection)
    {
        try
        {
            await connection.close();
        }
        catch(err)
        {
            console.log(err);
            return 0;
        }
    }
}

}

main();
// console.log outputs { [Error: ORA-00942: table or view does not exist] errorNum: 942, offset: 31 }
// Logged in w/external auth on other clients, proxy user set as the session user, no problems

[jacobhobson]

@anthony-tuininga, unable to download the Oracle 18.3 client due to workplace machine constraints.

[anthony-tuininga]

To show which users are in place you can use the following code:

let result = await conn.execute(`
        select
            sys_context('USERENV', 'PROXY_USER'),
            sys_context('USERENV', 'SESSION_USER')
        from dual`);
console.log("Proxy User:", result.rows[0][0]);
console.log("Session User:", result.rows[0][1]);

The proxy user will be null if no proxy has been established.

I'm not sure if there is any other way to workaround this issue (besides upgrading to the 18c client). You must have some pretty nasty constraints if you can't download a zip file and extract it! I'm assuming here that you are aware of the instant client and its simplicity in terms of installation and use. :-)

[jacobhobson]

@anthony-tuininga, unable to *install. I can download it, but 12.2 is all that is company approved. At a company where over 1k employees are using 12.2, I'm not sure how to make oracledb work for my application. 12.2 is a stable release of the Oracle client, right? Are you saying there's no way to use oracledb's externalAuth and proxy capabilities w/client 12.2?

I'm going to try to work around getting 18.3 installed, but in the end, it won't matter if that works. I need the client everyone in my company is using to work, or our project will be rendered useless.

I ran the SQL above, and I got:
PROXY_USER: null
SESSION_USER: 'JACOBHOBSON'

[cjbj]

I was testing 12.2 & 18.3 on LInux yesterday. 18.3 client was needed for this scenario to work when connecting to either 12.2 or 18.3 DBs (and yes, I was using the insecure init.ora remote_os_authent setting). My memory in https://github.com/oracle/node-oracledb/issues/980#issuecomment-422586922 is confirmed. @jacobhobson it looks like the ball is in your court for this.
On our side, we need to fix a non-pooled scenario in node-oracledb code (technically it is a relaxation of an ODPI-C check).

[anthony-tuininga]

@anthony-tuininga, unable to *install. I can download it, but 12.2 is all that is company approved. At a company where over 1k employees are using 12.2, I'm not sure how to make oracledb work for my application. 12.2 is a stable release of the Oracle client, right? Are you saying there's no way to use oracledb's externalAuth and proxy capabilities w/client 12.2?

Understood. And yes, that is what we are saying. Using a standalone connection would work (after we relax that constraint as @cjbj mentioned) but a pooled connection does not work. I'm sorry to be the bearer of bad news! Technically the 18.3 client is a "drop-in replacement" for the 12.2 client, but I understand that its never quite that simple. :-)

I ran the SQL above, and I got:
PROXY_USER: null
SESSION_USER: 'JACOBHOBSON'

That output is exactly what I would expect with 12.2 client. You'll see different results with 18.3 client.

[anthony-tuininga]

I'm not sure of the needs of your application and whether caching standalone connections (aka poor man's session pool) is even an option for you, but it is a workaround if upgrading to 18.3 isn't viable.

[jacobhobson]

@anthony-tuininga @cjbj, I _REALLY_ appreciate both of your time. I don't mind losing pool functionality and writing a method that gets the job done. But that's not available yet, correct? I'm sadly stuck with 12.2 (although I'm still going to attempt using 18.3 just to make sure I'm not missing something).

Thanks a ton!

[cjbj]

I'm sure we will post the patch soon - it's likely to be only a few lines long. Let us sync up with @pvenkatraman et al later today first, so we can all approve the expected final change. Heads up: you'll need to compile your own driver until we release 3.1 which is some time off.

[jacobhobson]

@cjbj, I can pull back data using sqlplus commands. Seems to be working fine for now. I'll build a little exec wrapper around sqlplus so I can use the externalAuth/proxy capabilities until 3.1 comes out. Thanks!

[cjbj]

@jacobhobson if we ever bundle Instant Client with node-oracledb (https://github.com/oracle/node-oracledb/issues/572) how will that affect your policies?!

[jacobhobson]

I think you'd make a lot of people happy! Ha. A lot of our work is becoming more node-centralized. It'd be great to pull all of that down at once.

[cjbj]

@jacobhobson the relevant patch is in the ODPI-C layer, see https://github.com/oracle/odpi/commit/61f68d7e3e4fc01559a5bb1ca7a1a06cef49339f

This patch fixes proxies and external authentication with standalone connections:

  console.log("Standalone: External Auth with proxy");
  try {
    config = { user: "[testsessuser]", externalAuth: true };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
      // With node-oracledb 3.0.1 gives:
      //   DPI-1032: user name and password cannot be set when using external authentication
      // With the patch gives:
      //   Proxy User: OPS$ORACLE
      //   Session User: TESTSESSUSER
  } catch (e) {
    console.log(e.message + "\n");
  }

The patch does prevent this case from given an unexpected session user:

  console.log("Pooled: External Auth with proxy no brackets in acquire");
  try {
    config = { externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "testsessuser" });
    await ShowUserInfo(conn);
      // With node-oracledb 3.0.1 gives
      //   Proxy User: null
      //   Session User: OPS$ORACLE
      // With the patch gives:
      //   DPI-1069: proxy user name must be enclosed in [] when using external authentication
  } catch (e) {
    console.log(e.message + "\n");
  }

The patch doesn't change the pooled proxy and authentication scenario, which requires Oracle Client 18.3 libraries.

  try {
    console.log("Pooled: External Auth with proxy in acquire");
    config = { externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "[testsessuser]" });  // note use of brackets []
    await ShowUserInfo(conn);
      // With node-oracledb 3.0.1, using 12.2 gives
      //   Proxy User: null
      //   Session User: OPS$ORACLE
      // With node-oracledb 3.0.1, using 18.3 gives
      //   Proxy User: OPS$ORACLE
      //   Session User: TESTSESSUSER
  } catch (e) {
    console.log(e.message + "\n");
  }

[jacobhobson]

@cjbj, thanks for sharing. Excited to leverage this feature!

[cjbj]

@jacobhobson If you get a chance to test it, let us know how it goes.