Forum Stats

  • 3,826,604 Users
  • 2,260,675 Discussions
  • 7,897,026 Comments

Discussions

Issue during Node.js deployment on Application CLoud ORA-01045: user lacks CREATE SESSION privilege

Smiers
Smiers Member Posts: 67
edited Nov 22, 2016 7:10AM in Node.js

Hello,

I started working with Node.js and oracledb in order to access the database.

I'm testing this on Application Container Cloud and the Database Cloud.

I followed the tutorial : Oracle Application Container Cloud Service: Building a RESTful Node.js Web Service with Oracle Database Cloud Service

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/apaas/node-cloud-REST-DB/nodecloud-RESTDB.html#section4

It looks there are some errors in the tutorial

Amongs others the connection properties. Below the described connection setup from the tutorial, deployment in this way is not possible,

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

<span class="cm-keyword">var</span> <span class="cm-variable">connectionProperties</span> = {<br/>  <span class="cm-property">user</span>: <span class="cm-variable">process</span>.<span class="cm-property">env</span>.<span class="cm-property">NODE_ORACLEDB_USER</span> || <span class="cm-string">"oracle"</span>,<br/>  <span class="cm-property">password</span>: <span class="cm-variable">process</span>.<span class="cm-property">env</span>.<span class="cm-property">NODE_ORACLEDB_PASSWORD</span> || <span class="cm-string">"oracle"</span>,<br/>  <span class="cm-property">connectString</span>: <span class="cm-variable">process</span>.<span class="cm-property">env</span>.<span class="cm-property">NODE_ORACLEDB_CONNECTIONSTRING</span> || <span class="cm-string">"localhost/xe"</span><br/>};<br/><br/>I changed the connection to the following (I copied the connection string from the DB Cloud instance)<br/>

var connectionProperties = {

  user: process.env.NODE_ORACLEDB_USER || "MyUser",

  password: process.env.NODE_ORACLEDB_PASSWORD || "MyPassword",

  connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "[IP ADDRESS DB SERVER]:1521/[PDB].[XXXX]"

};

Now the application deployment fails one step further

     ORA-01045: user [DEMO-USER] lacks CREATE SESSION privilege; logon denied

I created this DB user with the following privilges/roles

     CONNECT, CREATE SESSION, RESOURCE

I am able to connect from SQL Developer towards the DB Cloud instance.

What could be the reason of this issue?

I even tried adding DBA privileges to the DB user, same result.

Thanks

Léon

Christopher Jones-Oracle

Best Answer

  • Smiers
    Smiers Member Posts: 67
    edited Nov 20, 2016 5:57AM Answer ✓

    Hi Chirs,

    Thanks!

    On my end I found the following that might help

    When you define a routing rule in your Node app that applies to "/", it will be executed during deployment by ACCS. This looks like a bug in ACCS.

    app.get("/", function(req,res) {    oracledb.getConnection(        {          user          : process.env.DBAAS_USER_NAME,          password      : process.env.DBAAS_USER_PASSWORD,          connectString : process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR     },      function(err, connection)      {

    Changing the routing rule to another URI (for instance "/test, see below) will solve the problem and results in a successfull deployment

    app.get("/test", function(req,res) {

    Now I run into the next issue

    In the Cloud Oracle Database I defined a user with the below roles.

    I am able to connect through SQL Developer and create my tables.

    grant CONNECT to C##TEST;grant CREATE ANY TABLE to C##TEST;grant CREATE SESSION to C##TEST;grant DROP ANY TABLE to C##TEST;grant UNLIMITED TABLESPACE to C##TEST;

    After deployment of the Node.js app and adding the Service Binding I try to call the API on "/test" in Postman. This results in the below error in the logging.

    Even though I added the CREATE SESSION privilege, it does not connect.

    Any ideas?

    Thanks

    Léon

    Nov 19 22:41:43 UTC: total 12Nov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas   48 Nov  8 11:55 ..Nov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas  822 Nov 16 18:38 node_modulesNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas  111 Nov 16 18:57 manifest.jsonNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas  341 Nov 18 10:32 package.jsonNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas 3775 Nov 19 17:57 server.jsNov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas   92 Nov 19 17:58 .Nov 19 22:41:43 UTC: Server started in port:8080, using connection: Nov 19 22:41:49 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:41:49 UTC: Nov 19 22:43:35 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:43:35 UTC: Nov 19 22:45:35 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:45:35 UTC: 

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited Nov 17, 2016 9:47PM

    In the app, use something like this:

    oracledb.getConnection( 
    {
         user          : process.env.DBAAS_USER_NAME,
         password      : process.env.DBAAS_USER_PASSWORD,
         connectString : process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR
    },
      . . .

    In ACCS for your app, add a service binding, choosing the DB service you want, and using the DB username & password (the pop up doesn't make this clear - don't use your cloud login credentials).  ACCS will create the environment variables DBAAS_* for your app with the values you need.

  • Smiers
    Smiers Member Posts: 67
    edited Nov 18, 2016 5:37AM

    Hi Chris,

    I removed the double entries for the Connection parameters and the Port.

    I'm now getting the following error:

    Initialized application creation...Acquired resources for instance(1G) web.1...Failed to deploy application(v1) for instance(1G) web.1...Application failed bind to specified port[env variable $PORT]. Application logs Nov 18 05:55:56 UTC: ^^^...Nov 18 05:55:56 UTC: SyntaxError: Unexpected identifier

    Code:

    var express = require('express');var bodyParser = require('body-parser');var oracledb = require('oracledb');var PORT = process.env.PORT;var app = express();oracledb.autoCommit = true;oracledb.getConnection({     user          : process.env.DBAAS_USER_NAME,     password      : process.env.DBAAS_USER_PASSWORD,     connectString : process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR},

    I get the impression that the Application Container Cloud wants to map the process.env settings even before I have the chance to define them.

    Any ideas?

    Thanks

    Léon

  • Smiers
    Smiers Member Posts: 67
    edited Nov 18, 2016 5:46PM

    Here a bit more info from the log files

    It looks like directly after deploying it start executing the server.js for every route defined in the file.

    In the log-file below it can be seen that an empty connection is chosen.

    Directly after it starts executing the first GET operation, etc. And since no connection is defined yet, it fails.

    I need to able to enter the connection entries, but see no way to do that from the ACCS console before it start executing the code.

    Nov 18 22:33:56 UTC: total 16Nov 18 22:33:56 UTC: drwxr-xr-x 1 apaas apaas   48 Nov  8 11:55 ..Nov 18 22:33:56 UTC: drwxr-xr-x 1 apaas apaas  822 Nov 16 18:38 node_modulesNov 18 22:33:56 UTC: -rw-r--r-- 1 apaas apaas  111 Nov 16 18:57 manifest.jsonNov 18 22:33:56 UTC: -rw-r--r-- 1 apaas apaas  341 Nov 18 10:32 package.jsonNov 18 22:33:56 UTC: -rw-r--r-- 1 apaas apaas 5655 Nov 18 22:28 server.jsNov 18 22:33:56 UTC: drwxr-xr-x 1 apaas apaas   92 Nov 18 22:29 .Nov 18 22:33:57 UTC: Server started in port:8080, using connection: {}Nov 18 22:34:01 UTC: REQUEST:HEAD   /Nov 18 22:34:01 UTC: BODY:{}Nov 18 22:34:01 UTC: GET TOPICSNov 18 22:34:01 UTC: ORA-12162: TNS:net service name is incorrectly specified

    Léon

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited Nov 19, 2016 3:57PM

    The Application failed bind to specified port[env variable $PORT] error greatly frustrated me for a very long time too.  @danmcghan-Oracle gave me a solution:  I needed to change  http.listen(httpPort, "localhost") to http.listen(httpPort) in my app, or do:

    var host = os.hostname() || '0.0.0.0'; 
    ...
    http.listen(httpPort, host);

    You may be basing your test on the node-oracledb driver webapp.js sample, I will update the example in the next release so it won't need modification.  The ACCS team alerted their doc team about needing some doc on this.

    There is still an ACCS quirk about having to deploy before being able to set environment variables.  The ACCS team is aware of it. Hopefully they will resolve it.

  • Smiers
    Smiers Member Posts: 67
    edited Nov 20, 2016 5:57AM Answer ✓

    Hi Chirs,

    Thanks!

    On my end I found the following that might help

    When you define a routing rule in your Node app that applies to "/", it will be executed during deployment by ACCS. This looks like a bug in ACCS.

    app.get("/", function(req,res) {    oracledb.getConnection(        {          user          : process.env.DBAAS_USER_NAME,          password      : process.env.DBAAS_USER_PASSWORD,          connectString : process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR     },      function(err, connection)      {

    Changing the routing rule to another URI (for instance "/test, see below) will solve the problem and results in a successfull deployment

    app.get("/test", function(req,res) {

    Now I run into the next issue

    In the Cloud Oracle Database I defined a user with the below roles.

    I am able to connect through SQL Developer and create my tables.

    grant CONNECT to C##TEST;grant CREATE ANY TABLE to C##TEST;grant CREATE SESSION to C##TEST;grant DROP ANY TABLE to C##TEST;grant UNLIMITED TABLESPACE to C##TEST;

    After deployment of the Node.js app and adding the Service Binding I try to call the API on "/test" in Postman. This results in the below error in the logging.

    Even though I added the CREATE SESSION privilege, it does not connect.

    Any ideas?

    Thanks

    Léon

    Nov 19 22:41:43 UTC: total 12Nov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas   48 Nov  8 11:55 ..Nov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas  822 Nov 16 18:38 node_modulesNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas  111 Nov 16 18:57 manifest.jsonNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas  341 Nov 18 10:32 package.jsonNov 19 22:41:43 UTC: -rw-r--r-- 1 apaas apaas 3775 Nov 19 17:57 server.jsNov 19 22:41:43 UTC: drwxr-xr-x 1 apaas apaas   92 Nov 19 17:58 .Nov 19 22:41:43 UTC: Server started in port:8080, using connection: Nov 19 22:41:49 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:41:49 UTC: Nov 19 22:43:35 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:43:35 UTC: Nov 19 22:45:35 UTC: ORA-01045: user C##TEST lacks CREATE SESSION privilege; logon deniedNov 19 22:45:35 UTC: 
  • Smiers
    Smiers Member Posts: 67
    edited Nov 22, 2016 4:47AM

    Hi Chris,

    Last update. I found out what I did wrong with the database connection. I pointed towards the root database and not the Pluggable database that was created. Now I'm also able to create users without the strange prefix C##.

    I works now.

    Léon

    Christopher Jones-Oracle