ERROR(500): Error executing function 'RunQuery': The current user does not have the required roles for this operation

MoisesZV · September 5, 2017, 2:22pm

Hi I get an error when I try to see the data from my db on runtime, but it works in the designer, this is the error.

	ERROR(500): Error executing function 'RunQuery': The current user does not have the required roles for this operation.
com.inductiveautomation.ignition.gateway.servlets.gateway.functions.GatewayFunctionException: The current user does not have the required roles for this operation.

at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:408)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Server.java:518)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

at java.lang.Thread.run(Unknown Source)

brandon1 · September 5, 2017, 3:32pm

Do you have security set on this table? Are you logged in as the same user in the runtime client?

MoisesZV · September 5, 2017, 3:41pm

I dont have security on the table, I reinstalled ignition and I get the error again. The user is default “admin” “password”.

PGriffith · September 5, 2017, 3:44pm

As of Ignition 7.9.4 there’s a new set of client scoped permissions that prevent even authenticated clients, by default, from changing anything on the gateway.

https://docs.inductiveautomation.com/display/DOC79/Project+Properties#ProjectProperties-ClientPermissions

KathyApplebaum · September 5, 2017, 5:35pm

And the tl;dr here is that security is turned on by default for new projects.

MoisesZV · September 5, 2017, 6:03pm

Thanks, I turned on all the permissions and it works.

joe.hammond · March 25, 2020, 5:16pm

Just to be more clear: In Designer Version 8, choose Project | Project Properties. Once open choose Permissions under Vision then check the box that says Legacy Databases.

bmeyers · June 30, 2020, 8:19pm

Just to clarify things a bit further for everyone…

https://inductiveuniversity.com/video/client-permissions/7.9

This video explains client permissions which are disabled by default after version 7.9.4. Checking the box ‘Legacy Database Access’ allows client database access outside of named queries and transaction groups. You should try to only use named queries and transaction groups where possible for security reasons.

The biggest confusion is the videos aren’t made for version 8.0 in my opinion.

Michael_Frantz · October 31, 2022, 10:40pm

Legacy DB Access got it working immediately. TYVM!

Miguel_Gomez1 · June 20, 2023, 5:03am

Just thank you, I spend like 3 hours trying to solve this issue.

pturmel · June 20, 2023, 11:24am

Turning on Legacy DB access is not a solution. It is a patch that re-enables insecure behavior of your system. The actual solution is to use Named Queries.

Miguel_Gomez1 · June 20, 2023, 3:08pm

Well, thank you. I tried using Named Queries but the data of the database was not showed.

For example in the SQL part I wrote this
SELECT date_, volume_
FROM mydb
WHERE date BETWEEN '{Root Container.initialdate.text}' AND '{Root Container.finaldate.text}'

And it worked with the solution from Joe Hammond.

So I created the same query at Named queries and in the table properties I called in the SQL Named Query but nothing happened, there is no errors but the table is not showing anything. (I'm new in ignition ) Do you know why is this happening?

bkarabinchak.psi · June 20, 2023, 3:11pm

If you left it as {Root Container.initialdate.text} then it will never work. What is the name of the parameters in your named query? You need to use those, and then refer with a colon prrefix, I would imagine something like :initialDate. Show your full named query and how you bind to the named query.

Miguel_Gomez1 · June 20, 2023, 4:49pm

Thank you! I did it like you said and It Works! I turned off the Legacy DB and everything worked nice.
I called the Named Query that I configured and just put the values.

Thank you again!