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

Smiers · Nov 17, 2016 12:52PM

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

var connectionProperties = { user: process.env.NODE_ORACLEDB_USER || "oracle", password: process.env.NODE_ORACLEDB_PASSWORD || "oracle", connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING || "localhost/xe" }; I changed the connection to the following (I copied the connection string from the DB Cloud instance)

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

Smiers · Nov 20, 2016 5:57AM

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:

Christopher Jones-Oracle · Nov 17, 2016 9:45PM

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 · 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 · 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 · Nov 19, 2016 3:56PM

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 · Nov 20, 2016 5:57AM

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 · 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

Forum Stats

Quick Links

Categories

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

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

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

Best Answer

Answers