X

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

External and Proxy Connection Syntax Examples for node-oracledb

Christopher Jones
Senior Principal Product Manager

Here's a demo showing the connection syntax for node-oracledb in various proxy and external authentication connection scenarios.

The script mentions Oracle 12.2 because that is what I was testing way back at the time I wrote the script. I was also checking an enhancement in 18.3. You may find that most syntaxes are usable with earlier Oracle versions.

We also tweaked some behavior in node-oracleb 3.1, so if you have an older version you will see differences in cases 4 and 14, and will see few message changes in some other cases. You should upgrade!

 

/*

1. Before running this script, set up some schemas:

   In the CDB:

     alter system set remote_os_authent=true scope=spfile;

     Note this is insecure, but is a simple way of allowing OS authentication in
     a development environment.

   In the PDB that the script connects to:

    drop user myproxyuser cascade;
    drop user mysessionuser1 cascade;
    drop user mysessionuser2 cascade;
    drop user ops$oracle cascade;

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

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

    create user ops$oracle identified externally;
    grant connect, resource to ops$oracle;
    alter user ops$oracle default tablespace users;

    create user mysessionuser2 identified by itdoesntmatter;
    grant create session to mysessionuser2;
    alter user mysessionuser2 grant connect through ops$oracle;


2. Run this script with node-oracledb 3.1 or later, when logged in to the OS as
   the user 'oracle'

*/

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");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: null
  //   Session User: MYPROXYUSER
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome" };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(2) Standalone: External Auth");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  try {
    config = { connectString: "localhost/orclpdb1", 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");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser[mysessionuser1]", password: "welcome" };
    conn = await oracledb.getConnection(config);
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }

  console.log("(4) Standalone: External Auth with proxy");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: OPS$ORACLE
  //   Session User: MYSESSIONUSER2
  try {
    config = { connectString: "localhost/orclpdb1", 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 no brackets (expect DPI-1069)");
  // Using 12.2 or later client libraries gives:
  //   DPI-1069: proxy user name must be enclosed in [] when using external authentication
  try {
    config = { connectString: "localhost/orclpdb1", 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");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: null
  //   Session User: MYPROXYUSER
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome" };
    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");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: null
  //   Session User: OPS$ORACLE
  try {
    config = { connectString: "localhost/orclpdb1", 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 create");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser[mysessionuser1]", password: "welcome" };
    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 acquire (Heterogeneous pool)");
  // Using 12.2 or later client libraries gives:
  //   Proxy User: MYPROXYUSER
  //   Session User: MYSESSIONUSER1
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome", 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 acquire (Heterogeneous pool) (Expect ORA-00987)");
  // Using 12.2 or later client libraries gives:
  //   ORA-00987 missing or invalid username(s)
  try {
    config = { connectString: "localhost/orclpdb1", user: "myproxyuser", password: "welcome", 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 create (Expect DPI-1032)");
  // Using 12.2 or later client libraries gives
  //   DPI-1032 user name and password cannot be set when using external authentication
  try {
    config = { connectString: "localhost/orclpdb1", 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 no brackets in create (Expect DPI-1032)");
  // Using 12.2 or later client libraries gives:
  //   DPI-1032: user name and password cannot be set when using external authentication
  try {
    config = { connectString: "localhost/orclpdb1", 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 acquire");
  // Using 18.3 or later client libraries gives
  //   Proxy User: OPS$ORACLE
  //   Session User: MYSESSIONUSER2
  // This was an enhancement in Oracle 18.3
  try {
    config = { connectString: "localhost/orclpdb1", 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 no brackets in acquire (Expect DPI-1069)");
  // Using 12.2 or later Oracle client libraries gives:
  //   DPI-1069: proxy user name must be enclosed in [] when using external authentication
  try {
    config = { connectString: "localhost/orclpdb1", externalAuth: true };
    pool = await oracledb.createPool(config);
    conn = await pool.getConnection({ user: "mysessionuser2" });
    await ShowUserInfo(conn);
  } catch (e) {
    console.log(e.message + "\n");
  }
})();

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.