X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Node-oracledb Connection Samples: Proxies and External Authentication

Christopher Jones
Senior Principal Product Manager

We recently reviewed 'external authentication' and 'proxy connections' support in node-oracledb and did a few tweaks that will appear in the future node-oracledb 3.1.

You can use 'external authentication' to connect without requiring a password being stored in your Node.js applications. This is useful, for example, to authenticate via LDAP or use an Oracle Wallet.

The idea of a proxy connection is to create a schema in one database user name. Privilege is granted on that schema to other database users so they can access the schema and manipulate its data. This aids three-tier applications where one user owns the schema while multiple end-users access the data.

You can use external authentication and proxy connections together or separately.

In the 'so I can find it again' category, here are some behaviors of node-oracledb connections.

For ease of testing, my external authentication was via Operating System authentication using an 'OPS$' schema. Since my database was remote to the node-oracledb application I used the INSECURE setting 'alter system set remote_os_authent=true scope=spfile;'. Do not do use this in real life!

SQL:

create user mynormaluser identified by mynormaluserpw;
grant create session to mynormaluser;

create user myproxyuser identified by myproxyuserpw;
grant create session to myproxyuser;

create user mysessionuser1 identified by doesnotmatter;
grant create session to mysessionuser1;
alter user mysessionuser1 grant connect through myproxyuser;

-- I logged into my computer as the 'oracle' OS user:
create user ops$oracle identified externally;
grant connect, resource to ops$oracle;
alter user ops$oracle default tablespace users;
alter user mysessionuser2 grant connect through ops$oracle;

JavaScript:

const oracledb = require('oracledb');

async function ShowUserInfo(conn) {
  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]);
  console.log();
};

(async function() {

  let conn, pool, config, testdesc;

  // -------------------- STANDALONE CONNECTIONS --------------------

  console.log("(1) Standalone: Basic Auth");
  // Gives:
  //   Proxy User: null
  //   Session User: MYNORMALUSER
  try {
    config = { connectString: "localhost/orclpdb", user: "mynormaluser", password: "mynormaluserpw" };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(2) Standalone: External Auth");
  // Gives:
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  try {
    config = { connectString: "localhost/orclpdb", externalAuth: true };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(3) Standalone: Basic Auth with proxy");
  // Gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb", user: "myproxyuser[mysessionuser1]", password: "myproxyuserpw" };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(4) Standalone: External Auth with proxy in brackets");
  // With node-oracledb 3.0 gives:
  //   DPI-1032: user name and password cannot be set when using external authentication
  // With node-oracledb 3.1 gives:
  //   Proxy User: OPS$ORACLE
  //   Session User: MYSESSIONUSER2
  try {
    config = { connectString: "localhost/orclpdb", user: "[mysessionuser2]", externalAuth: true };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(5) Standalone: External Auth with proxy");
  // With node-oracledb 3.0 gives:
  //   DPI-1032: user name and password cannot be set when using external authentication
  // With node-oracledb 3.1 gives:
  //   DPI-1069: proxy user name must be enclosed in [] when using external authentication
  try {
    config = { connectString: "localhost/orclpdb", user: "mysessionuser2", externalAuth: true };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  // -------------------- POOLED CONNECTIONS --------------------

  console.log("(6) Pooled: Basic Auth");
  // Gives:
  //   Proxy User: null
  //   Session User: MYNORMALUSER
  try {
    config = { connectString: "localhost/orclpdb", user: "mynormaluser", password: "mynormaluserpw" };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection();
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(7) Pooled: External Auth");
  // Gives:
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  try {
    config = { connectString: "localhost/orclpdb", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection();
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(8) Pooled: Basic Auth with proxy in pool creation");
  // Gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb", user: "myproxyuser[mysessionuser1]", password: "myproxyuserpw" };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection();
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(9) Pooled: Basic Auth with proxy in connection");
  // Gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb", user: "myproxyuser", password: "myproxyuserpw", homogeneous: false };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ "user": "mysessionuser1" });
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(10) Pooled: Basic Auth with proxy in brackets in connection");
  // Gives:
  //   ORA-00987 missing or invalid username(s)
  try {
    config = { connectString: "localhost/orclpdb", user: "myproxyuser", password: "myproxyuserpw", homogeneous: false };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ "user": "[mysessionuser1]" });
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(11) Pooled: External Auth with proxy in brackets in pool creation");
  // Gives:
  //   DPI-1032 user name and password cannot be set when using external authentication
  try {
    config = { connectString: "localhost/orclpdb", user: "[mysessionuser2]", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection();
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(12) Pooled: External Auth with proxy in pool creation");
  // Gives:
  //   DPI-1032: user name and password cannot be set when using external authentication
  try {
    config = { connectString: "localhost/orclpdb", user: "mysessionuser2", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection();
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(13) Pooled: External Auth with proxy in brackets in connection");
  // Using Oracle 12.2 client libraries gives
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  // Using Oracle 18.3 client libraries gives
  //   Proxy User: OPS$ORACLE
  //   Session User: MYSESSIONUSER2
  // This was an enhancement in Oracle 18.3
  try {
    config = { connectString: "localhost/orclpdb", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "[mysessionuser2]" });
  await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(14) Pooled: External Auth with proxy in connection");
  // With node-oracledb 3.0 gives:
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  // With node-oracledb 3.1 gives:
  //   DPI-1069: proxy user name must be enclosed in [] when using external authentication
  // This change in node-oracledb 3.1 prevents connecting with an unexpected session user
  try {
    config = { connectString: "localhost/orclpdb", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "mysessionuser2" });
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }
})();

Summary:

  • (4) "Standalone: External Auth with proxy": Fixed in node-oracledb 3.1

  • (5) "Standalone: External Auth with proxy no brackets": Error message was improved in node-oracledb 3.1

  • (13) "Pooled: External Auth with proxy in brackets in connection": Works with Oracle Client 18.3+

  • (14) "Pooled: External Auth with proxy in connection": In node-oracledb 3.1 connection fails due to improved validation. In node-oracledb 3.0 connection succeeded despite the options being inconsistent.

We've had some discussions about the use of "[]" brackets - which is a standard Oracle syntax passed through to the Oracle client libraries. We could have done some manipulation in node-oracledb for consistency between pooled and standalone connections, but we decided not to make node-oracledb behave differently than other Oracle language interfaces.

Finally, if you're interested in tracking users or in mid-tier authentication, don't forget to check out the clientId attribute. 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services