Node-oracledb: Support for proxy connection

[beltschatsar]

Hi,

I'm trying to make a proxy connection from a mid-tier account connection pool.

I know there is a special CONNECT syntax like this one:
CONNECT hr[scott]/welcome1

Which serves to connect to scott through proxy user hr (mid-tier account) with password welcome1, but this is no use for me because it defeats the connection pool usage.

In java, with the connection just borrowed from the pool, It would be possible to do:

      java.util.Properties userNameProp = new java.util.Properties();
      userNameProp.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME, 
                               "scott");
      Connection conn = ods.getProxyConnection(
                          OracleOCIConnectionPool.PROXYTYPE_USER_NAME, 
                          userNameProp);
     /*      ods is a OracleOCIConnectionPool here */

see this

In that manner, the connection is dedicated to the user for the time of the api call made, so that:
select USER from dual;
Give 'scott'. And when the connection returns to the pool, it will give 'hr'.

But how can I do a getProxyConnection or equivalent call ? I looked at function getConnection(poolAlias: string): IPromise< IConnection >; but it lacks a parameter to pass the userNameProp.

I know it is possible to set the CLIENT_IDENTIFIER (see) to identify the end-user from the database, but this field is already in use for other purpose....

Node.js version: v8.6.0
Node-oracledb version: 11301
Node-oracledb text format: 1.13.1
Oracle Client library version: 1202000100
Oracle Client library text format: 12.2.0.1.0

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Linux MEAN 4.4.0-98-generic #121-Ubuntu SMP Tue Oct 10 14:24:03 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609

[ghost]

I have started to work on this on my own fork, and I'm kind of stumped, I'm not sure I am taking the right approach to this. My initial thoughts was simply to modify the njsPool.cpp file to supply a username and password in the njsPool::Async_GetConnection() function.

This is what I have so far:

//-----------------------------------------------------------------------------
// njsPool::GetConnection()
//   Get a connection from the pool and return it.
//
// PARAMETERS
//   - JS callback which will receive (error, connection)
//-----------------------------------------------------------------------------
NAN_METHOD(njsPool::GetConnection)
{
    // Adds possiblity to specify a proxy user and password through a JS property object
    Local<Object> connProps;

    njsBaton *baton;
    njsPool *pool;

    pool = (njsPool*) ValidateArgs(info, 1, 2);
    if (!pool)
        return;
    // Extract the arguments from the JS property object
    if (!pool->GetObjectArg(info, 0, connProps))
        return;
    baton = pool->CreateBaton(info);
    if (!baton)
        return;
    if (baton->error.empty()) {
        baton->jsOracledb.Reset(pool->jsOracledb);
        njsOracledb *oracledb = baton->GetOracledb();
        baton->connClass = oracledb->getConnectionClass();
        baton->lobPrefetchSize =  pool->lobPrefetchSize;
        baton->SetDPIPoolHandle(pool->dpiPoolHandle);

        // Get the proxyUsername and proxyUserPassword from the connection properties
        baton->GetStringFromJSON(connProps, "proxyUser", 0, baton->proxyUser);
        baton->GetStringFromJSON(connProps, "proxyUserPassword", 0, baton->proxyUserPassword);    
    }
    baton->QueueWork("GetConnection", Async_GetConnection,
            Async_AfterGetConnection, 2);
}


//-----------------------------------------------------------------------------
// njsPool::Async_GetConnection()
//   Worker function for njsPool::GetConnection() method.
//-----------------------------------------------------------------------------
void njsPool::Async_GetConnection(njsBaton *baton)
{
    dpiConnCreateParams params;
    dpiContext *context;

    context = njsOracledb::GetDPIContext();
    if (dpiContext_initConnCreateParams(context, &params) < 0) {
        baton->GetDPIError();
        return;
    }
    if (!baton->connClass.empty()) {
        params.connectionClass = baton->connClass.c_str();
        params.connectionClassLength = baton->connClass.length();
    }
    // If a proxy user was supplied, use that, otherwise fallback to default behavior
    if(!baton->proxyUser.empty()){
        if (dpiPool_acquireConnection(baton->dpiPoolHandle, baton->proxyUser.c_str(), (uint32_t) baton->proxyUser.length(),
            baton->proxyUserPassword.c_str(), (uint32_t) baton->proxyPassword.length(),
            &params, &baton->dpiConnHandle) < 0)
        baton->GetDPIError();    
    } else{
        if (dpiPool_acquireConnection(baton->dpiPoolHandle, NULL, 0, NULL, 0,
            &params, &baton->dpiConnHandle) < 0)
        baton->GetDPIError();
    }    
}

However, that doesn't seem to work. Am I on the right track ?

[anthony-tuininga]

First, in order to have use a proxy user the pool itself is not allowed to be homogeneous. That is the default unless external authentication is enabled, but you don't want that, so the first step is to allow for a non-homogeneous pool when the pool is created.

Second, when you use the proxy user you don't want to specify the password. You first create the pool with a regular user/password combination. Then you acquire the connection specifying a different user name which is the proxy user. You can see this in the following code for cx_Oracle which already has this implemented:

pool = cx_Oracle.SessionPool(USERNAME, PASSWORD, TNSENTRY, min=2, max=8, increment=3,
                homogeneous = False, encoding = ENCODING, nencoding = NENCODING)
connection = pool.acquire(user = PROXY_USERNAME)
cursor = connection.cursor()
cursor.execute('select user from dual')
result, = cursor.fetchone()

[beltschatsar]

Am I wrong or the fix to make things works is small. In similar case, Oracle ( on this project) proposed to make the fix.

Do I miss something?

[cjbj]

@beltschatsar @jeremie-carpentier-roy adding support is on our 'to-do' list. Since there is interest, we can see if we can move it up the list. Although it might seem simple, there are a couple of things we wanted to check, plus of course tests to be created.

[ghost]

Thanks @anthony-tuininga and @cjbj for the replies on this, really appreciated. If you guys can move it up the priority list, that's all good. In the meantime I'll continue working on this slowly (I'm not yet acquainted with the codebase, so I'm threading lightly) and keep this thread updated with my progress.

If it ends up there's a parallel initiative to get this feature merged in the master branch, I'm happy to help in any way I can.

Regards,

[ghost]

Hi everyone,

I succeeded to acquire a pool connection with a proxy user after adding support for non-homogeneous pools and passing only the proxy user without the password as per @anthony-tuininga suggestion.

The current npm test suite runs fine on my local dev environment

  3212 passing (2m)
  33 pending

> [email protected] posttest /Users/UCARP9Z/node-oracledb
> node test/opts/versions.js

Node.js: v9.4.0
Node-oracledb: 2.0.15
Oracle Client library: 12.2.0.1.0
Oracle Database: 12.2.0.1.0

@cjbj mentioned we should be creating some tests to go along this change. Would you guys be okay if I pushed my changes to my fork and then created a pull requests so we can start the review process, identify the tests that I should create and tie up all loose ends necessary to eventually get this pushed to master (including the contributor agreement etc.)?

[anthony-tuininga]

Good job! I would get started on the OCA paperwork right away as it takes a while to process. But in the meantime you can certainly push your code to your own fork and create a pull request. I look forward to seeing what you have implemented.

[cjbj]

@jeremie-carpentier-roy sharing the user interface (and/or code) for review before getting in too deep with details is great.

Can you make sure your OCA is accepted before creating any PR on node-oracledb? The OCA team does handle requests in batches but, if you let me know the email address you submitted it from, I can chase them up to process it sooner.

[cjbj]

@jeremie-carpentier-roy @beltschatsar @electrotype I'd guess you're coming over from JDBC-land. Is there anything else you wish node-oracledb supported?

[cjbj]

We've had an (internal) discussion about this, and tossed around whether to cater for possible full support of all 'heterogeneous' pool features, or just basic proxy usage as mentioned in this issue. The decision point is really what names do we give properties. Full heterogeneous support would allow each session to be authenticated with different usernames & passwords - but this may be uncommon.

There was also some discussion about whether clientId support is what most users really want.

In summary, can you (all) comment on your business uses and what problems you are trying to solve?

[cjbj]

Another topic for discussion is what level of integration with the pool cache and the various oracledb.getConnection() modes there should be: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#getconnectiondb

[ghost]

Hi @cjbj ! It's a great to see this thread has raised some discussions, it's always a good thing. I'm happy to provide more context on what we're trying to achieve.

We have several legacy applications, some of them built with Oracle forms, some others with Oracle Designer. Most of those applications use the built-in audit features coming with those tools (namely: auto-generated auditing triggers). As we are modernizing our infrastructure, we are building several APIs (using NodeJS and this very driver) to be used by all sorts of modern apps (SPAs, Mobile apps, etc) on top of the same databases used by the legacy applications. Our problem lies with currently losing the auditing functionality due to all API calls being made by the mid-tier account (example: a generic account named 'api') all looking the same in the Oracle auditing.

It looks a little bit like that:

We saw two ways of addressing this, the first being manually modifying all the auditing triggers of those legacy applications (I'm sure you can understand why we don't really want to go that route), and the other is to incorporate proxy-authentication to the session pool to ensure the API layer can pass the app user to the Oracle connection, thus leveraging all the auditing triggers as-is.

About the clientId. We currently use that to send in the application identifier, so we know which modern app ended up calling the API. Ideally, for governance and compliance reasons, our need is going to be able to record both informations (the calling app and the user).

I hope this piece of information is enough context to engage in a meaningful discussion.

_On a separate note, it seems I was over-optimistic on the time I thought it would take to sign the OCA. Turns out the process takes longer than I thought: I have to get cleared by Legal and all that. So it doesn't hurt to continue having those discussions, it might take a little while before I can submit a PR, sorry about that._

[beltschatsar]

Just wish to add that we had guidelines (where I'm working at) for those who wish to develop with Oracle in a n-tier architecture that they should consider proxy connect for their development.

[beltschatsar]

@cjbj I'm new in nodejs dev, I've done very few jdbc dev (servlet) as I was developping mostly with PL/SQL. Hard to tell.

[anthony-tuininga]

About the clientId. We currently use that to send in the application identifier, so we know which modern app ended up calling the API. Ideally, for governance and compliance reasons, our need is going to be able to record both informations (the calling app and the user).

If you need to use both bits of information have you considered using another one of the tracing attributes available to you? For example you could use "module" for the application and "clientId" for the user. I'm not sure if that would involve reworking all of your triggers, in which case I understand your reluctance to pursue that option!

[ghost]

Yes, it would involve reworking all the triggers...

[cjbj]

@beltschatsar can you expand more on 'proxy connect'? Who authenticates and when - see below?

@jeremie-carpentier-roy to summarize what I'm getting: you are doing/planning:

• create user midtier identified by midtier;
  grant create session to midtier;
  alter user cj grant connect through midtier;
  

• create pool as midtier/midtier (i.e. authenticate once)

• getConnection({user:'cj'}), getConnection({user:'jeremie'}) etc?

But you do not want to authenticate each time you get a connection from the pool:

• create pool without a user

• getConnection({user:'cj, pw:'cj')

And don't want to do proxy connections like getConnection({user:'scott[cj]'})

Are you using/planning on using external authentication?

Yes, using clientId for the Application user ("jeremie") would have been the best choice and is recommended for other readers of this issue who are starting new applications.

[ghost]

@cjbj That's a pretty good summary of what we're doing/planning.

We don't plan to use external authentication, all legacy application's security is done with in-database users, so to minimize effort and impact, our plan is to keep it that way.

[cjbj]

@jeremie-carpentier-roy thanks.

[krismohan]

@jeremie-carpentier-roy In the above example , which user owns the database objects (tables, views, procedures etc) the application uses? Is that 'midtier'? Or 'cj', 'jeremie',..? Whose password(s) are you willing to supply to the app? 'midtier's? Or that of 'cj' and 'jeremie'?

[beltschatsar]

In our scenario, We use a 3th schema which owns the dB objects (we prefix all objects by this schema name).
The only password to supply is the midtier’s account

[krismohan]

@beltschatsar I assume it is not a case for proxy connection then. You connect as the mid-tier account/password and your sqls use the schema objects with the prefix. Is that correct?

[ghost]

@krismohan I think your statement about our situation is right and you understood the context of where we're coming from. I will try to clarify in separate points.

• It is not a case for proxy connection. You are right; Our APIs do not know the end user's passwords. So we can't use a proxy connection (establish a connection with the following scheme midtier_accountname[proxied_accountname]/proxied_accountname_password). Also for performance reasons, we will not want to establish a new connection for each request but rather take advantage of the driver's session pooling mechanism.

• Our goal is indeed to establish a connection pool with the mid-tier account/password and then be able to get a connection from the pool with a proxy user so columns like _CreatedBy_ and _ModifiedBy_ (which are filled in by auto-generated triggers using the user holding the connection) have the proper username in them (the user which is connected to the app making the call to the api)

• All the schemas are owned by their respective application accounts (which are different than the mid-tier accounts)

I hope this gives you a clearer context about our situation.

Regards,

[cjbj]

@beltschatsar @jeremie-carpentier-roy if you compile the 2.3.0-dev code on the master branch you will get heterogeneous pool and pool proxy support. Doc is at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connpoolproxy Feedback is welcome - particularly before we freeze 2.3.0.

[ghost]

Hi @cjbj, that's great news!

I will recompile from master and test within the proof of concept environment I had setup and give you some feedback. I already looked at the documentation and it seems pretty thorough, good job! Do you have an approximate time frame for when you will want to freeze 2.3.0?

_An update on the whole OCA situation. We finally got the approval from our elected officials for our open-source policy at the City of Montreal which means that we now can officially contribute code in the city's name to open source projects. Even if it means I couldn't sign this project's contributor agreement it in time to contribute my code, being able to contribute code going forward is a big win for us and I wanted to thank you all because you helped make that happen (I was using this issue internally to push this initiative)._

[cjbj]

@jeremie-carpentier-roy that's great news about contributions. It can make your life a lot easier if you get fixes that are important to you merged into projects.

I don't have a firm time for freezing 2.3, but I hope it will be soon - when everyone is back from vacation, code has landed, and some testing has been completed.

[ghost]

@cjbj I have tested with the recompiled master version and it works like a charm as expected! Great work!

[krismohan]

@jeremie-carpentier-roy Cool! What option of the below three do you plan to use?

(1)

oracledb.createPool(
  {
    // no user name or password
    homogeneous   : false,
    . . .  // other pool options
  },
  function(err, pool) {
    pool.getConnection(
      {
        user     : 'myproxyuser[hr]',
        password : 'myproxyuserpassword'
      },

OR (2)

 oracledb.createPool(
  {
    user          : 'myproxyuser',
    password      : 'myproxyuserpassword'
    homogeneous   : false,
    . . .  // other pool options
  },
  function(err, pool) {
    pool.getConnection(
      {
        user : 'hr'  // the session user
      },

OR (3)

 oracledb.createPool(
  {
    user          : 'myproxyuser[hr]',
    password      : 'myproxyuserpassword'
    homogeneous   : true
    . . .  // other pool options
  },
  function(err, pool) {
    pool.getConnection(
      {
        // no user passwd, all connections are of session user "hr"
      },

[ghost]

We are going to use the option 2

[krismohan]

@jeremie-carpentier-roy Sounds good. So you have multiple session users, all granted connect through a single proxy user.

[cjbj]

@jeremie-carpentier-roy thanks for the feedback.

I'm looking forward to some PR's from you, big or small.

[cjbj]

Node-oracledb 2.3 is available on npm with proxy support in the connection pool: https://blogs.oracle.com/opal/node-oracledb-23-with-continuous-query-notifications-is-on-npm